Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Forecasting a figure in Excel
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 |
#2
|
|||
|
|||
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 Jasemary wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
How do I update Excel 2000 macros to work in Excel 2002? | Excel Discussion (Misc queries) | |||
How do figure excel formula out? | Excel Discussion (Misc queries) |