Using the mouse, select the menu path TOOLS/DATA ANALYSIS35 and

choose the procedure “Exponential Smoothing.”

Damping: The factor you want to use as the exponential smoothing

constant. The damping factor is a corrective factor that minimizes the

instability of data collected across a population.

The default value for the damping factor is 0.3. Values of 0.2 to 0.3 are

reasonable smoothing constants. These values indicate that the current

forecast should be adjusted 20 to 30 percent for error in the prior forecast.

If you do not see this option, then use TOOLS / ADD-INS to activate the Add-In for

35

data analysis. Refer to section 41.4.

233

Statistical Analysis with Excel

Larger constants yield a faster response but can produce erratic

projections. Smaller constants can result in long lags for forecast values.

Figure 166: Exponential Smoothing

Data Requirement: A single column or row with four or more cells with

valid data.

Output: The output range must be on the same worksheet as the data in

the input range. Enter the range reference for the upper” left cell of the

output table (for example, “AD4”). You can obtain a column of Standard

Errors by selecting the option “Standard Errors.” If you want to chart the

procedure's output ” the actual values and forecasts “, select the option

“Chart Output.”

Moving Average analysis

This tool projects values in the forecast period based on “the average

value of the series over a specific number of preceding periods.” A moving

average provides trend information that a simple average of all historical

data would mask.

234

Chapter 13: Other Tools for Statistics

Select the option TOOLS/DATA ANALYSIS36 and choose the procedure

“Moving Average.”

Interval: Number of values you want to include in the moving average.

The default is three.

Figure 167: Moving Average

Data Requirement: A single column or row with four or more cells with

valid data.

Output: The output range must be on the same worksheet as the data in

the input range. Enter the range reference for the upper“left cell of the

output table (for example, “AD4”). You can obtain a column of Standard

Errors by selecting the option “Standard Errors.” If you want to chart the

procedure's output ” the actual values and forecasts “, select the option

If you do not see this option, then use TOOLS / ADD-INS to activate the Add-In for

36

data analysis. Refer to section 41.4.

235

Statistical Analysis with Excel

“Chart Output.”

236

Page for Notes

Statistical Analysis with Excel

CHAPTER 14

THE SOLVER TOOL FOR CONSTRAINED LINEAR

OPTIMIZATION

This chapter teaches:

” DEFINING THE OBJECTIVE FUNCTION (CHOOSING THE

OPTIMIZATION CRITERION)

” ADDING CONSTRAINTS

” OPTIONS

DEFINING THE OBJECTIVE FUNCTION (CHOOSING

14.1

THE OPTIMIZATION CRITERION)

The problem of constrained optimization: