Ask Your Question

How LibreOffice Calculates FORECAST Function [closed]

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():

Which yields:
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

In cell A12, the previous 3 week average:

In cell B12, the sum weight:
In cell C12, the difference between the sum weight and 3 week average:
In cell D12, the ratio number:
In cell E12, the ratio average:
In cell F12, the ratio:
In cell G12, value1:
In cell H12, value2:
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:
Second, rounding it to the nearest whole number, or 1, which is what is in cell I12:
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:
Which yields:
If we change the next iteration within our manual forecast, from cell A5 to cell A6, we get:
Which yields:
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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-10 09:53:38.535211

2 Answers

Sort by » oldest newest most voted

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

Regina gravatar image

It uses a simple linear regression as described in 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

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

Question Tools

1 follower


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

Seen: 3,494 times

Last updated: Aug 05 '16