Excel 2007 Directions for Ken Black S Text

Excel 2007 Directions for Ken Black’s Text

Chapter 17

• Excel has the capability of forecasting using several of the techniques presented in this chapter. Two of the forecasting techniques are accessed using the Data Analysis tool, and two other forecasting techniques are accessed using the Insert Function.
• To use the Data Analysis tool, begin by selecting the Data tab on the Excel worksheet. From the Analysis panel at the right top of the Data tab worksheet, click on Data Analysis. If your Excel worksheet does not show the Data Analysis option, then you can load it as an add-in following directions given in Chapter 2.
• To do exponential smoothing, select Exponential Smoothing from the Data Analysis pull-down menu. In the dialog box, input the location of the data to be smoothed in Input Range. Input the value of the dampening factor in Damping factor. Excel will default to .3. Input the location of the upper left cell of the output table in the Output Range space. The output consists of forecast values of the data. If you check Standard Errors, a second column of output will be given of standard errors.
• To compute moving averages, select Moving Average from the Data Analysis pull-down menu. In the dialog box, input the location of the data for which the moving averages are to be computed in Input Range. Record how many values you want to include in computing the moving average in Interval. The default number is three values. Input the location of the upper left cell of the output table in Output Range. The output consists of the moving averages. If you check Standard Errors, a second column of output will be given of standard errors.
• To use the Insert Function (fx) to compute forecasts and/or to fit a trend line, go to the Formulas tab on an Excel worksheet (top center tab). The Insert Function is on the far left of the menu bar. In the Insert Function dialog box at the top, there is a pull-down menu where it says Or select a category. From the pull-down menu associated with this command, select Statistical.
• To compute forecasts using linear regression, select FORECAST from the Insert Function’s Statistical menu. In the first line of the FORECAST dialog box, place the value of x for which you want a predicted value in X. An entry here is required. On the second line, place the location of the y values to be used in the development of the regression model in Known_y’s. On the third line, place the location of the x values to be used in the development of the regression model in Known_x’s. The output consists of the predicted value.
• To fit a trend line to data, select TREND from the Insert Function’s Statistical menu. On the first line of the TREND dialog box, place the location of the y values to be used in the development of the regression model in Known_y’s. On the second line, place the location of the x values to be used in the development of the regression model in Known_x’s. Note that the x values can consist of more than one column if you want to fit a polynomial curve. To accomplish this, place squared values of x, cubed values of x, and so on as desired in other columns, and include those columns in Known_x. On the third line, place the values for which you want to return corresponding y values in New_x’s. In the fourth line, place TRUE in Const if you want to get a value for the constant as usual (default option). Place FALSE if you want to set b0 to zero.