Looking for help to build a predictive sales model

[Keith K]

I am looking for help to determine how to approach this problem. I am
trying to develop/ improve on our monthly sales prediction model for
cars. I have queried the appropriate data for the past 3 years month by
month , day by day business group by business group (there ae only 5).

Question?? is there a function in excel which can help me smooth out
the data or develop and equation which could be used to improve the
accuracy of our sales forecast. I will share the data if someone has
some thoughts

The data I am trying to use to build the model is cumulative sales by
day by month. the layout is as follows

-5 -4 -3 -2 -1 end of month EOM DOW

62.5% 67.8% 71.2 % 75.2 % 81.4% 100% Tues


There is due to the nature of the business a substantial difference in
the cumulative % figures based on number of selling days and day of
week the month ends on.

I have the data set up so that all month ends 100% appear in the same
column in the spreadsheet. The current model in use which I have been
asked to try to fix / improve tends to return an higher prediction
versus (final actual) sales results. In addition the prediction
improves in accuracy as the month progresses ( not surprising since more
of the result is actual) but the prediction heads towards the final
result as a line which slopes negatively from left to right. Simply put
the model shows us doing far better than actual early in the month and
doing better but less so daily till the end. eg 7850, 7734, 7640
final result 7500.

TIA KK ;-)

 

[Mike Middleton]

Keith K -

You'll have to decide on the appropriate level of aggregation, but it looks
like you could use seasonal modeling methods to describe the day-of-the-week
(or month-of-the-year) systematic variation (after you verify that a
repetitive pattern exists).

One such method is implemented in the workbook
LinearTrendSeasonalForecast.xls, available for download from
http://www.mikemiddleton.com. The descriptions of the methods in the
workbook are terse, since it's an example of the methods described in much
more detail in the Time Series Seasonality chapter of my book Data Analysis
Using Microsoft Excel: Updated for Office XP, but it may help you to get
started in your analysis.

- Mike

 

[Knopksi]

Keith K -

You'll have to decide on the appropriate level of aggregation, but it
looks like you could use seasonal modeling methods to describe the
day-of-the-week (or month-of-the-year) systematic variation (after you
verify that a repetitive pattern exists).

One such method is implemented in the workbook
LinearTrendSeasonalForecast.xls, available for download from
http://www.mikemiddleton.com. The descriptions of the methods in the
workbook are terse, since it's an example of the methods described in
much more detail in the Time Series Seasonality chapter of my book
Data Analysis Using Microsoft Excel: Updated for Office XP, but it may
help you to get started in your analysis.

- Mike

Mike thanks "I'll need to knock some rust off and spend a little time
to see where this takes me. Greatly appreciate your time