Ask Your Question
0

How LibreOffice Calculates FORECAST Function

asked 2016-08-04 23:04:15 +0100

timesenemy gravatar image

updated 2016-08-04 23:05:52 +0100

* 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 ... (more)
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-08-05 12:57:32 +0100

Regina gravatar image

It uses a simple linear regression as described in https://en.wikipedia.org/wiki/Simple_.... The value betaHat in that article corresponds to fSumDeltaXDeltaY / fSumSqrDeltaX in the source code (see link given by erAck). To calculate the forecast it uses y = betaHat * x + alphaHat and substituted y= betaHat * x + (ymean - betaHat * xmean). If you rearrange this you get y = betaHat * (x - xmean) + ymean. That is the formula you see in line#4443 of the source code

edit flag offensive delete link more
0

answered 2016-08-05 00:16:17 +0100

erAck gravatar image

LibreOffice is Free Open Source Software, so see the source code of ScInterpreter::ScForecast() in the LibreOffice OpenGrok source code browser ;-)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-08-04 23:04:15 +0100

Seen: 2,314 times

Last updated: Aug 05 '16