Right I have 3 years data for monthly sales and forecast for that month for over 100 products and I need to predict how many of each we will sell for the next 12 months.
It's in the format:
jan forecast jan sales feb forecast feb sales etc
Is there a formula or way in Excel to get it to fill the next 12 months in based on the trend for that existing product? Eg widget x sold 100,000 units in March and from the data the avarage monthly increase for the last year was 5% therefore we could reasonably expect to sell 105,000 units in April etc?
I know I could quickly knock up a graph for the current data for each product and work out the current trend and I also think you can then get the graph to predict the data forward (but not sure how to even do this!)
It would be great if there was a formula I could put in for each month column going forward which would base itself on the previous data.
I might need to be able to select whether it uses the last year, 2 years or 3 years of data for its trend though. Reason being you might have a had a product which grew 5% in year 1, 5% in year 2 but because of a sales push or competitor failing, grew 20% in year 3. The average growth would be 10% per annum but obviously more suited to just use the 20% growth for the last year. Or a better example is one which grew 6% in the first year, dropped 10% in year 2 but then bounced back 10% in year 3. Trend would suggest only a 2% growth for the coming year but much more likely tobe the 10% as per last year.
I will obviously then have to look at each predicted result and make sure no external factors are likely to alter it eg new competitor product launching at cheaper price etc.
Hope this makes sense to somebody. Please help, Greebo needs you!
It's in the format:
jan forecast jan sales feb forecast feb sales etc
Is there a formula or way in Excel to get it to fill the next 12 months in based on the trend for that existing product? Eg widget x sold 100,000 units in March and from the data the avarage monthly increase for the last year was 5% therefore we could reasonably expect to sell 105,000 units in April etc?
I know I could quickly knock up a graph for the current data for each product and work out the current trend and I also think you can then get the graph to predict the data forward (but not sure how to even do this!)
It would be great if there was a formula I could put in for each month column going forward which would base itself on the previous data.
I might need to be able to select whether it uses the last year, 2 years or 3 years of data for its trend though. Reason being you might have a had a product which grew 5% in year 1, 5% in year 2 but because of a sales push or competitor failing, grew 20% in year 3. The average growth would be 10% per annum but obviously more suited to just use the 20% growth for the last year. Or a better example is one which grew 6% in the first year, dropped 10% in year 2 but then bounced back 10% in year 3. Trend would suggest only a 2% growth for the coming year but much more likely tobe the 10% as per last year.
I will obviously then have to look at each predicted result and make sure no external factors are likely to alter it eg new competitor product launching at cheaper price etc.
Hope this makes sense to somebody. Please help, Greebo needs you!
Last edited: