Calulate Sales for Previous Year

[CaptainPugwash]

I am trying to calulate sales for previous months where the data is not
available.

I have sales for months 15 to 48 but am missing 1- 14. I have looked at
several stats like groth and trend etc but can't seem to get a reasonable
result.

Any help greatly appreciated.

 

[J Sedoff comRemove>]

Do you mean you want to sum or total the results based on a month-to-month
basis, or a year-to-year basis?

Please provide more details like: what are you trying to accomplish (more of
a how.. are you summing, averaging, getting the difference between numbers,
using a crazy financial function, etc), what version of Excel (2000, 2003,
2007, etc), how your spreadsheet is set up..

Jim

 

[CaptainPugwash]

I have Excel 2003.

I have two columns one contains 1 to 48 representing months from November
2003 to October 2007. The other column has sales in volume from October 2004
to October 2007. So I am missing the sales Nov 2003 to Sept 2004.
I need to best guess what the sales were for the missing months. I thought I
could use the average percentage growth of months available or even growth or
forecast function but the last two seemed geared towards future sales.

I am not well versed in Statistics but the guessestimate has to be
reasonable given data avaiable since it will be used to generate an invoice.
So if you can help it would be greatly appreciated.

Thanks,

 

[J Sedoff comRemove>]

You could try TREND()..
=trend(known y's, known x's, new x's)
For A1,
=trend(B2:B48,A2:A48,A1)
For A2,
=trend(B3:B48,A3:A48,A1)
....etc, although you could just keep the same formula, go from row 12 for
each, rather than start at the next row.. whatever you feel works for you.

The problem with sales is that they tend to change depending on the time of
year. You may want to try creating a monthly average, that is, average each
september and make that the sales for the missing september, etc; although
you probably want to incorporate some sort of annual growth as well... You
could try to accomplish this with a yearly average or the trend of those
averages to get a picture of the growth/decline of sales, then use that as a
modifier/multiplier to your "monthly" average.

If that is more of what you are looking for, I could help you there too..

Hope this helps, Jim

 

[CaptainPugwash]

I decided to go with the average sales increase and work backwards.

Thanks for your sugesstions.