# How LibreOffice Calculates FORECAST Function

*** 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.

AB1Week Quantity21 12532 13243 11554 13765 12276 13087 ___ ?

Using the above example data, this is how LibreOffice Calc is figuring the FORECAST():

=FORECAST(4,B2:B4,A2:A4)Which yields:

114When I try to reverse engineer this formula, the closest I can come too is from an Oracle website on forecasting.

ABCDEFGHI1Week Quantity21 12532 13243 11554 13765 12276 13087 ___ ?910113wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst12124 734 -10 14 12 2 -5 132 11413

In cell A12, the previous 3 week average:

=(B2+B3+B4)/3In 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*3In cell F12, the ratio:

=D12-E12In cell G12, value1:

=C12-F12In cell H12, value2:

=A12-G12*F12While 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+H12Second, 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:

109If we change the next iteration within our manual forecast, from cell A5 to cell A6, we get:

=ROUND(A6*G12+H12,0)Which yields:

109This 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 ...