How to create non-linear forecast data

[DavE8tor]

I'm a bit of a Noob, but I think this is a fairly simple question.

I need to generate month-to-data based on some history, but also a forecast. I know the end goal at the end of the series. I need to figure out how to generate the forecast data in the middle, and perhaps be able to tweak the curve in the formula as well.

Here's an example (Empty cells are what I need to fill in):

forecast2.JPG

Thanks!!!
Dave

[JeteMc]

"...and perhaps be able to tweak the curve in the formula as well."
This would indicate that a formula was used to calculate the 100 and 175 values. It would help to know what that formula is, if it exists.
Let us know if you have any questions.

[joeu2004]

I think you are asking how to determine a non-linear formula that would generate 50, 100, 175 and 5000 for the months Jan, Feb, Mar and Dec, as well as the estimated data for the intermediate months.

Of course, the curve could have any shape; we cannot know.

But to KISS, we might assume a polynomial curve of order n-1 (3), which fits the exisiting n (4) data points exactly. Beware that such a polynomial is "reliable" only for interpolating intermediate data points, as you require, not for extrapolating before or after the given data. Such formulas tend to "blow up" within a few data points before and after the given data.

The following table demonstrates how to accomplish that.

	A	B	C	D	E	F	G	H
1					x^3	x^2	x^1	x^0
2	jan	50	50		3.055556	-5.833333	46.111111	6.666667
3	feb	100	100
4	mar	175	175		1	50
5	apr		293		2	100
6	may		473		3	175
7	jun		733		12	5000
8	jul		1092
9	aug		1567
10	sep		2177
11	oct		2940
12	nov		3875
13	dec	5000	5000

Copy the existing data (50, 100, 175, 5000) into F4:F7. For flexibility, we could enter the formulas =B2, =B3, =B4 and =B13 respectively.

Enter the corresponding month numbers (1, 2, 3, 12) into E4:E7.

Select E2:H2 and array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(F4:F7, E4:E7^{1,2,3})

These are the coefficients (a, b, c, d) for the polynomial a*x^3 + b*x^2 + c*x + d.

Enter the following formula into C2 and copy down through C13 [1]:

=SERIESSUM(ROWS($A$2:A2), 3, -1, $E$2:$H$2)

The formulas in C5:C12 could be moved into B5:B12. The formulas in C2:C4 and C13 are just to demonstrate the correctness of the polynomial.


-----
[1] More generally, the formula in C2 should be =SERIESSUM(ROWS($A$2:A2), 3, -1, $E$2:$G$2) + $H$2 , just in case H2 is zero (unlikely).

[kvsrinivasamurthy]

Try this. Pl see file.
In D3 then copied down

=$B$3*((10^(LOG10($B$14/$B$3)/($A$14-$A$3)))^(A3-$A$3))

I have used Geometric progression method.
In this formula start value , end value and number of months can be changed.

[DavE8tor]

Perfect! I like how tweaking the baseline data effects the curve!

Thank You!!!