Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 04 Aug 2016 23:04:15 +0200How LibreOffice Calculates FORECAST Functionhttps://ask.libreoffice.org/en/question/74614/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.
<pre>
<b>A</b> <b>B</b>
<b>1</b> Week Quantity
<b>2</b> 1 125
<b>3</b> 2 132
<b>4</b> 3 115
<b>5</b> 4 137
<b>6</b> 5 122
<b>7</b> 6 130
<b>8</b> 7 ___ ?
</pre>
Using the above example data, this is how LibreOffice Calc is figuring the FORECAST():
<pre>=FORECAST(4,B2:B4,A2:A4)</pre>
Which yields:
<pre>114</pre>
When I try to reverse engineer this formula, the closest I can come too is from an Oracle website on forecasting.
<pre>
<b>A</b> <b>B</b> <b>C</b> <b>D</b> <b>E</b> <b>F</b> <b>G</b> <b>H</b> <b>I</b>
<b>1</b> Week Quantity
<b>2</b> 1 125
<b>3</b> 2 132
<b>4</b> 3 115
<b>5</b> 4 137
<b>6</b> 5 122
<b>7</b> 6 130
<b>8</b> 7 ___ ?
<b>9</b>
<b>10</b>
<b>11</b> 3wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst
<b>12</b> 124 734 -10 14 12 2 -5 132 114
<b>13</b>
</pre>
In cell A12, the previous 3 week average:
<pre>=(B2+B3+B4)/3</pre>
In cell B12, the sum weight:
<pre>=(B2*1)+(B3*2)+(B4*3)</pre>
In cell C12, the difference between the sum weight and 3 week average:
<pre>=B12-A12*(1+2+3)</pre>
In cell D12, the ratio number:
<pre>=(1^2+2^2+3^2)</pre>
In cell E12, the ratio average:
<pre>=((1+2+3)/3)^2*3</pre>
In cell F12, the ratio:
<pre>=D12-E12</pre>
In cell G12, value1:
<pre>=C12-F12</pre>
In cell H12, value2:
<pre>=A12-G12*F12</pre>
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:
<pre>=A5*G12+H12</pre>
Second, rounding it to the nearest whole number, or 1, which is what is in cell I12:
<pre>=ROUND(A5*G12+H12,0)</pre>
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:
<pre>=FORECAST(5,B2:B4,A2:A4)</pre>
Which yields:
<pre>109</pre>
If we change the next iteration within our manual forecast, from cell A5 to cell A6, we get:
<pre>=ROUND(A6*G12+H12,0)</pre>
Which yields:
<pre>109</pre>
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...
<pre>
<b>A</b> <b>B</b> <b>C</b> <b>D</b> <b>E</b> <b>F</b> <b>G</b> <b>H</b> <b>I</b>
<b>1</b> Week Quantity
<b>2</b> 1 125
<b>3</b> 2 132
<b>4</b> 3 115
<b>5</b> 4 137
<b>6</b> 5 122
<b>7</b> 6 130
<b>8</b> 7 ___ ?
<b>9</b>
<b>10</b>
<b>11</b> 3wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst
<b>12</b> 124 734 -10 14 12 2 -5 132 114
<b>13</b>
<b>14</b> 7wkavg sumwght diffval ratio# ratioavg ratio value1 value2 fcst
<b>15</b> 126.8333 2672 8.5 91 73.5 17.5 0.48571 118.333 121.7333
<b>16</b>
</pre>
So, our manual forecast using a 6 week average is:
<pre>121.7333</pre>
Let's look at the FORECAST function:
<pre>=FORECAST(7,B2:B7,A2:A7)</pre>
Which yields:
<pre>128.5333</pre>
What is the formula, preferably broken down like I have it above, with each "variable" in its own cell, for LibreOffice's FORECAST function?timesenemyThu, 04 Aug 2016 23:04:15 +0200https://ask.libreoffice.org/en/question/74614/