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]

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

 

[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

 

[Colin Foster]

Mike,
Just checked your two forecasting spreadsheets & they're most informative &
useful - thanks for making them available to the wider community.

I'd checked this thread in the hope that it could resolve an issue that I've
been asked to look into...

Can a trendline forecast seasonal fluctuations? For example, if you have an
ice-cream business, you would expect (Global Warming & current weather
conditions here in the UK notwithstanding!!) higher sales in the summer than
in the winter. At the same time, if the business was growing, you'd expect a
general upward trend. What I've been asked is is it possible to show both the
general upward trend INCLUDING the expected seasonal highs & lows?

I wonder whether, rather than "just" using a trendline applied to the chart,
it would be possible to create a formula or use an existing function within
the Excel data & then simply chart that? Unfortunately, I fell asleep during
statistics lessons at school, hence the posting here!

Regards & thanks, in advance, for any suggestions.

Regards
Colin Foster