Forecasting a figure in Excel

[Jasemary]

Hi

I am trying to get a forecast figure in excel and cannot figure out how to
do it. I have the following info

I have 8 months of sales to date with varying values. ie:
Jan 30 units, Feb 35 units, Mar 32 units, Apr 36 units, May 32 units, June
38 units, July 33 units and Aug 35 units.

Assuming it is 01 Sept and I do not have the figures for Sept - Dec yet I
have a year to date total of 271 units. The cells for Sept - Dec are
currently blank. I have 2 cells following after Dec. The first (Cell N2) is
YTD sales which is =Sum(B2:M2) or 271. The next cell (Cell O2) is where I am
having trouble. Given the sales so far to date I want to forecast what my end
of year sales will be. I want the cell to be able to update itself as I add
figures for Sept and Oct etc as I get them. I would imagine that the formula
should take an average of all the months, excluding those not filled in and
then use that figure to calculate and end of year sales forecast. I am
however having a lot of trouble figuring this out.

If anyone can help I would be most grateful.

Thanks

Jason

 

[Jerry W. Lewis]

Even a statistics package (which Excel isn't) will not do your thinking
for you. What kind of model is appropriate for your data? Is there a
seasonal component to your sales? (you would need data spanning multiple
years to estimate seasonality) ...

There is not much difference between the monthly totals. One possiblity
would be to assume that monthly sales are flat, with random monthly
differences
=AVERAGE(jan_aug_sales)
gives 33.875 as the September forecast.

If you do a moving average of the most recent months, you would get
between 34 and 35, depending on how much history you include.

If you plot the data, there appears to be a slight upward trend, but
unclear whether it might be linear or curved. If you fit a straight
line, then
=FORECAST(9,jan_aug_sales,{1;2;3;4;5;6;7;8})
would predict 35.96 for September, but a quadratic fit
=TREND(jan_aug_sales,{1;2;3;4;5;6;7;8}^{1,2},9^{1,2})
would predict 33.55 for September.

Jerry