Forecast and Trends

[steph44haf]

If my data is cyclical in nature, what is the best function to use to
forecast my next year or two years of sales volume? (I have higher volumes
in certain times of the year and I need to account for this when projecting
my sales.)

Thanks!

[vezerid]

Hi,

If your data is cyclical then you are probably best of to use a
sinusoidal function. If there is an overall upward trend from period to
period you might want to add a linear function. Thus I would recommend
a function like:

f(t) = at + b + c*sin(dt+e)

Problem is the built-in functions for regression in Excel do not
support such functions so you will need the Solver to perform the least
squares method. For this you would need the column representing time to
have numeric values or else you should provide an auxiliary column with
consecutive numeric values (better off with 0, 1, ...). Say this is in
column A:A starting from A2.

You will need five cells for the five constants a-e. Say these are in
F2:J2.

Next to your dependent variable, say in D2, enter and copy down the
formula:

=$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2)

Next to it, in E2, enter the square difference of the dependent
(assumed in column C:C) from the forecasted:

=(D2-C2)^2

Take the sum of column E:E and ask SOlver to minimize it by changing
F2:J2. As this is a nonlinear problem and the built-in solver is not
very industrial strength, your initial values in F2:J2 will have to be
relatively close to the values you expect.

Write back if you need further assistance.

HTH
Kostis Vezerides

[steph44haf]

This stuff is great, but it might be a little over my head. Here is my data,
unfortunately I didn't follow how to do the equations. I sort of figured out
how to use Solver, but I wasn't sure what data I need in what columns, since
I only have two rows right now. If you can't help me any more, I understand
but I want to say thank you for your help already Kostis!

Month Year Default Claims Paid
Jan-04 17,414,897.94
Feb-04 10,699,109.47
Mar-04 18,332,334.50
Apr-04 14,275,140.03
May-04 12,305,352.33
Jun-04 13,907,155.18
Jul-04 11,963,018.44
Aug-04 19,201,480.28
Sep-04 15,623,457.98
Oct-04 7,077,725.63
Nov-04 15,740,422.12
Dec-04 13,761,418.33
Jan-05 21,340,245.83
Feb-05 9,409,514.83
Mar-05 10,572,805.35
Apr-05 12,339,659.95
May-05 11,986,746.47
Jun-05 10,252,392.46
Jul-05 12,416,685.61
Aug-05 17,892,569.26
Sep-05 26,618,694.92
Oct-05 7,581,879.50
Nov-05 15,579,836.07
Dec-05 21,710,331.63
Jan-06 21,665,556.58
Feb-06 13,653,795.27
Mar-06 14,457,680.21
Apr-06 18,774,698.52
May-06 17,775,539.97
Jun-06 16,774,408.35


"vezerid" wrote:

> Hi,
>
> If your data is cyclical then you are probably best of to use a
> sinusoidal function. If there is an overall upward trend from period to
> period you might want to add a linear function. Thus I would recommend
> a function like:
>
> f(t) = at + b + c*sin(dt+e)
>
> Problem is the built-in functions for regression in Excel do not
> support such functions so you will need the Solver to perform the least
> squares method. For this you would need the column representing time to
> have numeric values or else you should provide an auxiliary column with
> consecutive numeric values (better off with 0, 1, ...). Say this is in
> column A:A starting from A2.
>
> You will need five cells for the five constants a-e. Say these are in
> F2:J2.
>
> Next to your dependent variable, say in D2, enter and copy down the
> formula:
>
> =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2)
>
> Next to it, in E2, enter the square difference of the dependent
> (assumed in column C:C) from the forecasted:
>
> =(D2-C2)^2
>
> Take the sum of column E:E and ask SOlver to minimize it by changing
> F2:J2. As this is a nonlinear problem and the built-in solver is not
> very industrial strength, your initial values in F2:J2 will have to be
> relatively close to the values you expect.
>
> Write back if you need further assistance.
>
> HTH
> Kostis Vezerides
>
>

[vezerid]

Stephanie,

now I see... Well, I don't think you should call this data cyclical. At
first I thought you were talking about a product with seasonal behavior
but this is not the case. Judging from the headers and having charted
the data: We have an overall growth pattern but large fluctuations from
month to month, which is to be expected. Problem is, the fluctuations
are rather large and they do not follow a specific pattern.

In this case we have two choices: linear and exponential, unless there
exist some other market-dependent conditions which would dictate a
different type of function, e.g. quadratic. I give you two equations:

Linear:
=122198.98*K2+13264930
Exponential:
=12055159.54*EXP(0.0131488112808613*K2)

In both cases, K2 should contain the number of months between the start
of your data and the month you want the projection for. You can use the
function DATEDIFF(date2,DATE(2004,1,1),"m") to calculate this. For
date2 you should use DATE(yr,month,day), i.e something like
DATE(2007,5,1) for May 2007.

However, I am afraid this is as far as my statistics will go. The
number you will produce with these formulas is an estimate, however
with low confidence. Maybe one of the resident experts, like Jerry
Lewis, will jump in and direct you further so that you can also
calculate the plus-or-minus expected fluctuation from the projection.

HTH
Kostis Vezerides

steph44haf wrote:
> This stuff is great, but it might be a little over my head. Here is my data,
> unfortunately I didn't follow how to do the equations. I sort of figured out
> how to use Solver, but I wasn't sure what data I need in what columns, since
> I only have two rows right now. If you can't help me any more, I understand
> but I want to say thank you for your help already Kostis!
>
> Month Year Default Claims Paid
> Jan-04 17,414,897.94
> Feb-04 10,699,109.47
> Mar-04 18,332,334.50
> Apr-04 14,275,140.03
> May-04 12,305,352.33
> Jun-04 13,907,155.18
> Jul-04 11,963,018.44
> Aug-04 19,201,480.28
> Sep-04 15,623,457.98
> Oct-04 7,077,725.63
> Nov-04 15,740,422.12
> Dec-04 13,761,418.33
> Jan-05 21,340,245.83
> Feb-05 9,409,514.83
> Mar-05 10,572,805.35
> Apr-05 12,339,659.95
> May-05 11,986,746.47
> Jun-05 10,252,392.46
> Jul-05 12,416,685.61
> Aug-05 17,892,569.26
> Sep-05 26,618,694.92
> Oct-05 7,581,879.50
> Nov-05 15,579,836.07
> Dec-05 21,710,331.63
> Jan-06 21,665,556.58
> Feb-06 13,653,795.27
> Mar-06 14,457,680.21
> Apr-06 18,774,698.52
> May-06 17,775,539.97
> Jun-06 16,774,408.35
>

[Mike Middleton]

steph44haf -

Later today I will put a link in the lower left corner of my web site to a
workbook showing a simple method for using trend and seasonality to obtain
your forecasts. And tomorrow evening I'll use your data as an example when I
teach my Exec MBA class. Thanks.

- Mike
www.mikemiddleton.com


"steph44haf" <[email protected]> wrote in message
news:[email protected]...
> This stuff is great, but it might be a little over my head. Here is my
> data,
> unfortunately I didn't follow how to do the equations. I sort of figured
> out
> how to use Solver, but I wasn't sure what data I need in what columns,
> since
> I only have two rows right now. If you can't help me any more, I
> understand
> but I want to say thank you for your help already Kostis!
>
> Month Year Default Claims Paid
> Jan-04 17,414,897.94
> Feb-04 10,699,109.47
> Mar-04 18,332,334.50
> Apr-04 14,275,140.03
> May-04 12,305,352.33
> Jun-04 13,907,155.18
> Jul-04 11,963,018.44
> Aug-04 19,201,480.28
> Sep-04 15,623,457.98
> Oct-04 7,077,725.63
> Nov-04 15,740,422.12
> Dec-04 13,761,418.33
> Jan-05 21,340,245.83
> Feb-05 9,409,514.83
> Mar-05 10,572,805.35
> Apr-05 12,339,659.95
> May-05 11,986,746.47
> Jun-05 10,252,392.46
> Jul-05 12,416,685.61
> Aug-05 17,892,569.26
> Sep-05 26,618,694.92
> Oct-05 7,581,879.50
> Nov-05 15,579,836.07
> Dec-05 21,710,331.63
> Jan-06 21,665,556.58
> Feb-06 13,653,795.27
> Mar-06 14,457,680.21
> Apr-06 18,774,698.52
> May-06 17,775,539.97
> Jun-06 16,774,408.35
>
>
> "vezerid" wrote:
>
>> Hi,
>>
>> If your data is cyclical then you are probably best of to use a
>> sinusoidal function. If there is an overall upward trend from period to
>> period you might want to add a linear function. Thus I would recommend
>> a function like:
>>
>> f(t) = at + b + c*sin(dt+e)
>>
>> Problem is the built-in functions for regression in Excel do not
>> support such functions so you will need the Solver to perform the least
>> squares method. For this you would need the column representing time to
>> have numeric values or else you should provide an auxiliary column with
>> consecutive numeric values (better off with 0, 1, ...). Say this is in
>> column A:A starting from A2.
>>
>> You will need five cells for the five constants a-e. Say these are in
>> F2:J2.
>>
>> Next to your dependent variable, say in D2, enter and copy down the
>> formula:
>>
>> =$F$2*A2+$G$2+$H$2*SIN($I$2*A2+$J$2)
>>
>> Next to it, in E2, enter the square difference of the dependent
>> (assumed in column C:C) from the forecasted:
>>
>> =(D2-C2)^2
>>
>> Take the sum of column E:E and ask SOlver to minimize it by changing
>> F2:J2. As this is a nonlinear problem and the built-in solver is not
>> very industrial strength, your initial values in F2:J2 will have to be
>> relatively close to the values you expect.
>>
>> Write back if you need further assistance.
>>
>> HTH
>> Kostis Vezerides
>>
>>