*** When I typed this out, the preview did not include the right column. With the right column, my example tables are messed up. ***
How does LibreOffice calculate the FORECAST function?
I am not a math person. I have several different uses of FORECAST within a few different spreadsheets, and I am not able to reverse engineer the formula being used. This very well may be my own formula that is off, but I am too close to see it if it is.
A B 1 Week Quantity 2 1 125 3 2 132 4 3 115 5 4 137 6 5 122 7 6 130 8 7 ___ ?
Using the above example data, this is how LibreOffice Calc is figuring the FORECAST():
=FORECAST(4,B2:B4,A2:A4)
Which yields:
114
When I try to reverse engineer this formula, the closest I can come too is from an Oracle website on forecasting.
A B C D E F G H I 1 Week Quantity 2 1 125 3 2 132 4 3 115 5 4 137 6 5 122 7 6 130 8 7 ___ ? 9 10 11 3wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst 12 124 734 -10 14 12 2 -5 132 114 13
In cell A12, the previous 3 week average:
=(B2+B3+B4)/3
In cell B12, the sum weight:
=(B2*1)+(B3*2)+(B4*3)
In cell C12, the difference between the sum weight and 3 week average:
=B12-A12*(1+2+3)
In cell D12, the ratio number:
=(1^2+2^2+3^2)
In cell E12, the ratio average:
=((1+2+3)/3)^2*3
In cell F12, the ratio:
=D12-E12
In cell G12, value1:
=C12-F12
In cell H12, value2:
=A12-G12*F12
While testing this, I had an additional column which was the “next iteration,” but due to space constraints, I am just going to point to the next iteration cell, within the formula. To clarify, if we are looking at weeks 1, 2 & 3, and want to forecast week 4, then “4” would be the next iteration. If we wanted to forecast week 6, then “6” would be the next iteration.
We will use cell A5, to point to our next iteration of 4.
In cell I12, the rounded forecast:
First the formula:
=A5*G12+H12
Second, rounding it to the nearest whole number, or 1, which is what is in cell I12:
=ROUND(A5*G12+H12,0)
So, after all this, we are manually doing what the FORECAST function does so easily. Let’s change the forecast to look for the forecast for Week 5:
=FORECAST(5,B2:B4,A2:A4)
Which yields:
109
If we change the next iteration within our manual forecast, from cell A5 to cell A6, we get:
=ROUND(A6*G12+H12,0)
Which yields:
109
This seems to show that we have found the sweet spot using a 3 week average. However, if we use a 4 or even 24 more weeks average, it falls apart. I get mixed results. This is what is returned using a 6 week average:
I am being lazy here, not typing this all in, but the manual formula is expanded from 3 cells to 6 cells. For instance, the 6 week average contains, “=(B2+B3+B4+B5+B6+B7)/6”. And so forth…
A B C D E F G H I 1 Week Quantity 2 1 125 3 2 132 4 3 115 5 4 137 6 5 122 7 6 130 8 7 ___ ? 9 10 11 3wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst 12 124 734 -10 14 12 2 -5 132 114 13 14 7wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst 15 126.8333 2672 8.5 91 73.5 17.5 0.48571 118.333 121.7333 16
So, our manual forecast using a 6 week average is:
121.7333
Let’s look at the FORECAST function:
=FORECAST(7,B2:B7,A2:A7)
Which yields:
128.5333
What is the formula, preferably broken down like I have it above, with each “variable” in its own cell, for LibreOffice’s FORECAST function?
