Excel Forecast vs history of unit sales
Discussion
Hi all,
I am currently trying to forecast future sales for production of units for sale over the next few months. However using the =FORECAST calculation the units don't seem to make sense or account for the 'spikes' in previous trend - overall the units tend to slope in a downward future trend.
I have attached graphs / data for reference. How am I able to stabilise this and show an upward turn instead of a downward slope? Ultimately unit sales will grow - although not guaranteed but showing growth would be helpful! The drop over time does not seem correct as currently average number for data selection in yellow is 19 units.
I am using the wrong formula or is my data selection accurate? Tried to track this down on Google to no avail.
I am currently trying to forecast future sales for production of units for sale over the next few months. However using the =FORECAST calculation the units don't seem to make sense or account for the 'spikes' in previous trend - overall the units tend to slope in a downward future trend.
I have attached graphs / data for reference. How am I able to stabilise this and show an upward turn instead of a downward slope? Ultimately unit sales will grow - although not guaranteed but showing growth would be helpful! The drop over time does not seem correct as currently average number for data selection in yellow is 19 units.
I am using the wrong formula or is my data selection accurate? Tried to track this down on Google to no avail.
Edited by PD9 on Friday 5th June 08:22
I think TREND might help you more here: http://www.cimaglobal.com/Thought-leadership/Newsl...
Though personally I'd always work out one projection based on a worst case scenario (so maybe your worst month), and then maybe another with your average month.
Though personally I'd always work out one projection based on a worst case scenario (so maybe your worst month), and then maybe another with your average month.
Wrong tool for the job.
The Excel FORECAST gives you a forecast value "y" for a given value of "x" based on known (past values) of "x" and "y". The Excel help explains it quite well.
To build a Sales Forecast you really need to either:
1. Apply % growth to know past values, perhaps using a moving average or manual intervention to smooth out spikes; or
2. Build the forecast from the ground up .. ie we plan to sell x each month @ price y, factoring in known sales targets and promotions along with expected price increases.
Obviously lots of permutations of 1 and 2.
The Excel FORECAST gives you a forecast value "y" for a given value of "x" based on known (past values) of "x" and "y". The Excel help explains it quite well.
To build a Sales Forecast you really need to either:
1. Apply % growth to know past values, perhaps using a moving average or manual intervention to smooth out spikes; or
2. Build the forecast from the ground up .. ie we plan to sell x each month @ price y, factoring in known sales targets and promotions along with expected price increases.
Obviously lots of permutations of 1 and 2.
Jammy: I did have a read at the CIMA site last night - thanks for the link!
Sgrim: I did think FORECAST was the wrong tool as over time units reduce and it's not realistic. I can go back 5 years with my data however there is no trend and sales / spikes are spread across the year for each set of products, no seasonality either. Manual intervention will smooth this out with a realistic view to compensate for a forecasted 'high month'.
I guess quarterly bucket forecasts are the best way of looking at this over the bigger picture taking the average sales of each unit over each quarter, finding that number adding the growth versus previous FY growth and getting a number of expected units - reducing stock risk and enables building to stay stable +/- a few units which can always run over into the next month if surplus.
I seem to be on the right tracks.. I think!!
Thanks for the comments - any other suggestions welcome.
Sgrim: I did think FORECAST was the wrong tool as over time units reduce and it's not realistic. I can go back 5 years with my data however there is no trend and sales / spikes are spread across the year for each set of products, no seasonality either. Manual intervention will smooth this out with a realistic view to compensate for a forecasted 'high month'.
I guess quarterly bucket forecasts are the best way of looking at this over the bigger picture taking the average sales of each unit over each quarter, finding that number adding the growth versus previous FY growth and getting a number of expected units - reducing stock risk and enables building to stay stable +/- a few units which can always run over into the next month if surplus.
I seem to be on the right tracks.. I think!!
Thanks for the comments - any other suggestions welcome.
Gassing Station | Business | Top of Page | What's New | My Stuff