Forecasting and Business Analysis

Follow the Example Go to the sheet ''Your Try'' and use the Tools - Data Analysis menu to exponentially smooth the data in the "actual" column, using a smoothing constant of .5. The input dialog box on the left shows the inputs needed. You should forecast a value for the first month in 1996. Remember that the Excel exponential smoothing function applies the dampening to the forecast value not the actual value. This means that the dampening factor is equal to 1 minus alpha. For example if you want an alpha value of .3 then you need a dampening factor of 1-.3 = .7. You will find a solution and a fully worked example on other worksheet tabs.

The Excel Exponential Smoothing Function You can automatically exponentially smooth data in Excel using the Exponential Smoothing function from the Data Analysis menu. First check that the Analysis ToolPak is turned on. Click on the Tools menu. The bottom item should be Data Analysis. If it isn't then you will need to turn the ToolPak on. Go to the Tools Menu, click on Add-Ins then check the box next to Analysis ToolPak. After a short period you should now be able to access Data Analysis under the Tools menu. When you click on Data Analysis you will see a long list of statistical methods that you can access. Go down the list and click Exponential Smoothing. A screen will appear a little like that below. (It may vary depending upon the version of Excel you are using). The screen below shows the input for smoothing with a damping factor of .5. This is similar to the alpha figure referred to in your text. In fact this is 1minus the alpha refered o in the text. You need to enter the cell range for the input data and also the destination of the output. If you include a label in the first row of your data, you should check the Labels in First Row box. When it is all entered - click OK and the smoothed data is calculated.

Peter Rossini: Forecast this value

Actual

Forecast

Year and Month

Index

Simple Exponential Smoothing Forecast of the Index of Consumer Sentiment

Finding the Optimal Value of Alpha using SOLVER SORITEC and similar forecasting software will automatically find the optimal value for Alpha by finding the value which minimises the RMS Error. This can also be done using solver in Excel. The Solver function enables the user to maximise or minimise a value (or function) by changing other cells until the optimal solution is found. This is the process used in optimising methods such as linear, non-linear, integer or dynamic programming. For this example it is quite simple. Minimise the value of the RMS Error by changing the value of Alpha. To do this click Tools, then Solver. The dialog box to your left should appear. In this case we input to minimuse the value in cell F28 which is the RMS Error by changing the value of Alpha (or cell D27). Click solve and you will find the same solution that you would get through using SORITEC. NOTE: IN this spreadsheet the value for Alpha has been named rather than using a cell reference. To find out how to use names I suggest you consult the Excel help system.

Change the value of alpha and see what happens. Try to find the value for alpha that minimises the RMS error. Then go to the next sheet to find out how to do this easily

Actual

Forecast