Forecast

traceyjackson

New Member
Joined
Mar 1, 2004
Messages
1
Can anybody help me? I'm new to using excel and I have to forecast. I have the sales numbers monthly from 2002 and 2003, now I need to forecast each month for 2004. I've tried using the forecast function, but just don't seem to understand how it works.....
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
With the caveat that forecasting 2004 from just 2002 and 2003 is a high-risk proposition...

Given no additional information about the data other than the year and month, lay out the data as:

Year Mth Value
2002 01 120
2002 02 124
...
2003 01 134
2003 02 130
...

Now, use the LINEST function to estimate the coefficient for the month and for the year. This is similar to the real estate example in in XL help for LINEST. Once you have the coefficients and the constant, you can forecast the number of 2004-mm.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Another couple of options are:
1) use a series 1,2,3 etc as the known x values
2) use a series 1,2,3 and the squares 1,4,9... as the known values
3) Graph the actuals, insert a trend line and output the trendline equation


Tony
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Tracy,
Tracy_Forecasting.xls
BCDEF
1Sales
2Actuals1Jan-021,000
3Actuals2Feb-02750
4Actuals3Mar-02800
5Actuals4Apr-021,200
6Actuals5May-021,300
7Actuals6Jun-021,000
8Actuals7Jul-02900
9Actuals8Aug-021,300
10Actuals9Sep-021,200
11Actuals10Oct-021,400
12Actuals11Nov-021,000
13Actuals12Dec-02900
14Actuals13Jan-031,200
15Actuals14Feb-031,300
16Actuals15Mar-031,400
17Actuals16Apr-031,200
18Actuals17May-031,000
19Actuals18Jun-03900
20Actuals19Jul-031,000
21Actuals20Aug-031,200
22Actuals21Sep-031,100
23Actuals22Oct-03950
24Actuals23Nov-031,000
25Actuals24Dec-031,200
26ForecastJan-041,135=FORECAST(D26,$E$2:$E$25,$D$2:$D$25)
27ForecastFeb-041,139
28ForecastMar-041,142Copydown
29ForecastApr-041,146
30ForecastMay-041,149
Demo


Notes:
1. The dates in column D must be real dates i.e. dates recognized by Excel e.g. Jan-2002, not just the month as text like Jan, 2002.

2. Use the FORECAST function with care. The function assumes a linear relationship (a linear problem would be represented by a straight line on a graph. These include problems using simple arithmetic operations such as: SUM, FORECAST and TREND). A problem becomes non-linear when one or more elements share a disproportionate relationship to one another. For example, sales are growing exponentially or there has been a major disruption to sales (a factory fire, lost of a key market, change in government regulations etc). As an example, say your sales for Feb 03 were badly effected by a factory fire. If you use that sales figure in your forecast, you will get a misleading forecasts for your 2004 sales. In this case, you should “normalize” the Feb 03 sales by factoring out the effects of the fire. Of course, if these extraneous types of factors are permanent, then leave the sales data as is.

3. If sales are doubling or trebling, you are seeing an exponential growth. Use Excel’s GROWTH function instead of FORECAST (post back if you need help with this).

HTH

Mike
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,147
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top