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" wrote in message
...
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