Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for help to build a predictive sales model
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 ;-) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for help to build a predictive sales model
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 "Keith K" wrote in message 58... 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 ;-) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for help to build a predictive sales model
"Mike Middleton" wrote in
: 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 "Keith K" wrote in message 58... 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 thanks "I'll need to knock some rust off and spend a little time to see where this takes me. Greatly appreciate your time |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Sales Goals to Sales Report in Pivot Table | Excel Discussion (Misc queries) | |||
figuring commissions based on gross sales | Excel Worksheet Functions | |||
sales needed to cover costs that increase as sales do | Excel Worksheet Functions | |||
Calculate Total Sales from a Database | Excel Worksheet Functions | |||
Re-arrangement | Excel Discussion (Misc queries) |