Plotting the future sales with "FORECAST" Function: Could Apple Numbers be Inferior to Microsoft Excel?

So the way I would do this is:

enter the data you provided into a table using the following steps:

enter the first two months of the series, then select them. Hover the cursor over the lower edge so the yellow dot appears. :

Click, and hold, on the yellow fill control to fill the pattern down as needed.

Now convert the first row to a header row by using the contextual menu for row "1"

Now enter the formula for forecast.

C14=FORECAST(A14,$B$2:$B$13,$A$2:$A$13)

this is shorthand for... select cell C14, then type (or copy and paste from here) the formula:

=FORECAST(A14,$B$2:$B$13,$A$2:$A$13)

select cell C14, copy

select cells C14 thru the end of column C, paste

To plot this, select columns A thru C and add a scatter chart:

Once you have added, then chart, select the chart, then use the series formatter to add lines (if you want)

Hi Daniel,

Scatter charts plot x data against y data—in this case one set of x data (dates) against two sets of y data (sales and projected sales).

Scatter charts require that both x data and y data be recorded in non-header columns. You x data seems to be in a Header column, where it will be treated as labels for the data series, not as data itself.

Click on a cell in column B, then press option-left arrow to insert a new column to the left of the current column B.

Re-enter the dates from column A into the new column B, delete column A, then reattempt construction of your chart.

Regards,

Barry

DanielDay wrote:

Hey guys, I'm just curious, is there a way to "extract" this raw mathematical formula that is the driving force behind this FORECAST function?

…

Since probably none of us have nanophysics degree, is there a better way to represent the above formula? For example: a + b - z, if z = g blah blah blah...🙂

Numbers' FORECAST function uses "Linear least squares" method.

https://en.wikipedia.org/wiki/Linear_least_squares_(mathematics)

It expects data to be on Y = a X + b, where `a' and `b' to be calculated from given data for the best fit.

# `best' means smallest sum of ( a Xᵢ + b - Yᵢ )² — square of difference between actual value and calculated value.

On Numbers, you can get `a' value with SLOPE function and `b' with INTERCEPT function.

If you need more inside, `a' and ’b’ are calculated as below.

No, I don't have nanophysics degree, but I need to check it for related work ( write program to estimate actual value from noisy sample data ) several years ago.

# Σ is symbol to mean "sum". e.g. Σx² = X₁² + X₂² + … + Xn²

DanielDay wrote:

is there a way to "extract" this raw mathematical formula that is the driving force behind this FORECAST function?

You cannot "extract" the underlying math (which isn't rocket science at all, but is nothing more than beginning statistics of the kind that Ian and Wayne eat for breakfast🙂!).

But you can easily replicate the calculations with basic formulas. For example:

The formulas:

In the 'Known Values' table

C2: =A2−AVERAGE(A)

D2: =B2−AVERAGE(B)

E2: =C×D

F2: =C2^2

Filled down their respective columns.

In the 'Slope & Intercept' table:

B2: =SUM(Known Values::E)

B3: =SUM(Known Values::F)

B4: =B2÷B3

B5: =AVERAGE(Known Values::B)−B4×AVERAGE(Known Values::A)

In the 'Forecast' table

B2: =A2×'Slope & Intercept'::$B$4+'Slope & Intercept'::$B$5

Filled down the column.

SG

Also see Ian's (Yellowbox) posts this thread.

Excel is great, but it turns out Numbers has a few tricks unmatched in Excel.

SG

Happy Easter to you too!

Excel has a quarter century of user driven development behind it, Numbers is still a baby.