Excel spreadsheet help!

Greebo · 19 Apr 2012 at 19:09

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!

semi-pro waster · 19 Apr 2012 at 19:23

Do you just need the figures to base themselves on the previous months data? If you do then it should be a simple enough referencing exercise. i.e. suppose that cell A3 has the first figure then you just need to enter =A3*1.05 into cell B3 then drag along until you reach 12 months. You might want to make it stick to one row which would be =A$3*1.05 but it's possibly not necessary.

If you need it to reference previous years then you'd need the table with that data but it's still not particularly difficult - you'd need the amount it grew by as a seasonal factor.

//edit that's not a very elegant way of doing it and it may not be quite what you want but if you've got a dummy set of data then I could have a play about with it and see what I can do. Graphing it shouldn't be too difficult but it depends what you want to do.

dazzerd · 19 Apr 2012 at 20:42

Have a look at the trend function in excel, it basically does a regression analysis exactly the same as a trend line in a chart, but a little more complex to understand and explain. Or you can go mad and calculate it yourself using r squared values......... most simple way is to simply plot a line chart and then add a trend line and tell it to show values though.

Greebo · 19 Apr 2012 at 20:49

Thanks semi and dazzard.

Semi: It's more that I want Excel to look at the last 12 months or 36 months data, calculate the trend and then post suitable numbers in for the next 12 months.

Dazzerd. The trend functions sounds like the trick. I know i could do a graph with a trand line and get the values from that and manually input them but that seems a long winded way of doing.

Is the trand fucntion easy/hard?

Greebo · 19 Apr 2012 at 21:03

Trend seems to be the key but won;t accept "jan, feb" etc and I have to turn them into 1,2,3 etc

Am I doing something wrong or is there a way of using the months as one of my axis?

dazzerd · 19 Apr 2012 at 21:08

Edit: I don't think this is right so deleted just grabbed my misses machine, see using forecast below I think this is more appropriate.

dazzerd · 19 Apr 2012 at 21:21

dazzerd · 19 Apr 2012 at 21:54

OK this has intrigued me so I got an old stats book out, drop using trend and use forecast instead.

Assume you have your data like this B1 is Jan, C1 is Feb, D1 is Mar etc etc all the way up to Jul.

In row 2 are the corresponding actual sales for those month. You need to find out what figure you think you will have in August. In cell I2 put:

=FORECAST(H2,C2:H2,B2:G2)

If you drag this to the right you will always be basing your prediction on 7 month discrete periods. Just change the range if you want to look at longer periods.

This should match what excel charts will show you.

Greebo · 20 Apr 2012 at 14:15

You're a star. I will give that a go. Thanks

dazzerd · 20 Apr 2012 at 22:42

Greebo said:
You're a star. I will give that a go. Thanks

Hope it goes well. You probably know this but you need to be careful with your projections especially if your business has seasonal trends or spikes. It may be better for instance to forecast what sales will be like in Jan - 13 by using Dec 12, Dec 11, Dec 10 sales rather than the whole of 2012.

Obviously this depends on your business and your understanding of the nuances.

Greebo · 22 Apr 2012 at 19:09

Its just to give a starting point on over 100 lines. I will then assess each one for seasonal variations or things like planned prices rises or drops, competitor launching a rivel product etc.

It just seemed hard work to manually fill in the starting figures!