Guide to Using Excel For Basic Statistical Applications

Guide to Using Excel For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 5 th Ed. Chapter 15: Analyzing and Forecasting Time Series Data By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2005

Chapter 15 Excel Examples ® Trend Based Forecasting Taft Ice Cream Company ® Nonlinear Trend Harrison Equipment Company ® Seasonal Adjustment Big Mountain Ski Resort ® Single Exponential Smoothing Humboldt Electronics Company More Examples

Chapter 15 Excel Examples ® Double Exponential Smoothing Billingsley Insurance Company

Trend Based Forecasting Taft Ice Cream Company Issue: The owners of Taft Ice Cream Company considering expanding their manufacturing facilities. The bank requires a forecast of future sales. Objective: Use Excel to build a forecasting model based on 10 years of data. Data file is Taft. xls

Trend Based Forecasting – Taft Ice Cream Company Open File Taft. xls

Trend Based Forecasting – Taft Ice Cream Company First click on Chart Wizard, then select Line Chart

Trend Based Forecasting – Taft Ice Cream Company Click on Series tab and define range for Data Variables. Click on Next.

Trend Based Forecasting – Taft Ice Cream Company Remove unneeded data sets and identify the range for the X Variable.

Trend Based Forecasting – Taft Ice Cream Company Label the axes and graph

Trend Based Forecasting – Taft Ice Cream Company Size and format the graph as needed.

Trend Based Forecasting – Taft Ice Cream Company To develop the linear model, start with the original data.

Trend Based Forecasting – Taft Ice Cream Company Click on Tools, then Data Analysis and finally Regression

Trend Based Forecasting – Taft Ice Cream Company Define the data range for the X and Y Variables. Use the t column for time.

Trend Based Forecasting – Taft Ice Cream Company The regression output determines the slope and intercept of the linear model.

Trend Based Forecasting – Taft Ice Cream Company To visually compare the data with a linear model, return to the graph constructed using the chart wizard.

Trend Based Forecasting – Taft Ice Cream Company Left click on any data point, then right click and select Add Trendline

Trend Based Forecasting – Taft Ice Cream Company Select Linear model then click on Options Tab.

Trend Based Forecasting – Taft Ice Cream Company Select Display equation on chart.

Trend Based Forecasting – Taft Ice Cream Company Format chart as desired.

Trend Based Forecasting – Taft Ice Cream Company To determine both MAD and MSE values, start by selecting the Residuals option in Regression analysis.

Trend Based Forecasting – Taft Ice Cream Company The Predicted values and Residuals become part of the regression output.

Trend Based Forecasting – Taft Ice Cream Company Write and copy a formula to determine the Squared Residual values.

Trend Based Forecasting – Taft Ice Cream Company Also write and copy a formula to find the absolute values of the residuals.

Trend Based Forecasting – Taft Ice Cream Company Sum the squared and absolute values of the residuals.

Trend Based Forecasting – Taft Ice Cream Company Divide both summed values to find the MSE and MAD values.

Nonlinear Trend Harrison Equipment Company Issue: Harrison Equipment is interested in forecasting future repair costs for a crawler tractor it leases to contractors. Objective: Use Excel to develop a nonlinear forecasting model. Data file is Harrison. xls

Nonlinear Trend – Harrison Equipment Company Open File Harrison. xls This tutorial will start by finding the trend line. It will also show to find residuals.

Nonlinear Trend – Harrison Equipment Company First click on Chart Wizard, then select Line Chart

Nonlinear Trend – Harrison Equipment Company Click on Series tab and define range for both Y and X Variables.

Nonlinear Trend – Harrison Equipment Company Format, size and label chart as desired.

Nonlinear Trend – Harrison Equipment Company To add trendline, left click on any data point, then right click and select Add Trendline

Nonlinear Trend – Harrison Equipment Company Choose Linear

Nonlinear Trend – Harrison Equipment Company Trendline appears.

Nonlinear Trend – Harrison Equipment Company To build linear model, click on Tools, then Data Analysis and finally Regression

Nonlinear Trend – Harrison Equipment Company Define the data range for the X and Y Variables. Use the t column for time. Also ask for residuals.

Nonlinear Trend – Harrison Equipment Company The regression output determines the slope and intercept of the linear model.

Nonlinear Trend – Harrison Equipment Company Calculate bye MAD value by first finding the absolute value of the residuals using the ABS function.

Nonlinear Trend – Harrison Equipment Company Sum the absolute value of the Residuals and divide by the count (number) of residuals to find the MAD.

Nonlinear Trend – Harrison Equipment Company To develop non. Linear model define a new variable found by squaring the time values.

Nonlinear Trend – Harrison Equipment Company Develop a new regression model with t 2 value as the independent variables. The model becomes y = a + bt 2. The output gives the new regression coefficients.

Nonlinear Trend – Harrison Equipment Company Using the same SUM and COUNT formula find the MAD for the nonlinear model.

Nonlinear Trend – Harrison Equipment Company To plot the nonlinear model, define a new column of values determined by plugging the values of t 2 into the regression model.

Nonlinear Trend – Harrison Equipment Company Use the Chart Wizard, Line options to develop a graph comparing the observed values with the nonlinear model. You will identify two Series.

Nonlinear Trend – Harrison Equipment Company Format and place chart as needed.

Seasonal Adjustment Big Mountain Ski Resort Issue: The resort wants to build a forecasting model from data that has a definite seasonal component. Objective: Use Excel to develop a forecasting model adjusting for seasonal data. Data file is Big Mountain. xls

Seasonal Adjustment – Big Mountain Ski Resort Open File Big Mountain. xls

Seasonal Adjustment – Big Mountain Ski Resort To develop the graph first click on Chart Wizard button then select Line.

Seasonal Adjustment – Big Mountain Ski Resort Define the range for the Y and X variable values.

Seasonal Adjustment – Big Mountain Ski Resort Size and format the graph as desired.

Seasonal Adjustment – Big Mountain Ski Resort To find the moving average values use the AVERAGE function.

Seasonal Adjustment – Big Mountain Ski Resort Use the AVERAGE function again to find the centered moving average.

Seasonal Adjustment – Big Mountain Ski Resort Write a simple formula to find the Ratio to Moving Average values.

Seasonal Adjustment – Big Mountain Ski Resort To find the season index values click on PHStat, then Data Preparation and then Unstack.

Seasonal Adjustment – Big Mountain Ski Resort To find the seasonal index values start by adding (SUM) the three ratio to moving average values for each season.

Seasonal Adjustment – Big Mountain Ski Resort Divide the total values to find the seasonal index numbers.

Seasonal Adjustment – Big Mountain Ski Resort Write a formula to select the correct seasonal value to use to deseasonalize the data. Copy the formula into all cells.

Seasonal Adjustment – Big Mountain Ski Resort Use the Select Chart Wizard to graph the deseasonalized data.

Seasonal Adjustment – Big Mountain Ski Resort Format and place the chart as desired.

Seasonal Adjustment – Big Mountain Ski Resort Use the Tools, Data Analysis, Regression option to develop a regression model of the deseasonalized data.

Single Exponential Smoothing Humboldt Electronics Issue: The company needs to develop a forecasting model to help make inventory decisions, and wants the model to give more weight to recent values than to regression model do. Objective: Use Excel to develop a single exponential smoothing forecasting model. Data file is Humboldt. xls

Single Exponential Smoothing – Humboldt Electronics Open File Humboldt. xls

Single Exponential Smoothing – Humboldt Electronics Click on the Chart Wizard button then select Line.

Single Exponential Smoothing – Humboldt Electronics Click on the Series tab, then identify ranges for the Y and X variables.

Single Exponential Smoothing – Humboldt Electronics Label the axes.

Single Exponential Smoothing – Humboldt Electronics Format and place graph as desired.

Single Exponential Smoothing – Humboldt Electronics To develop the exponential smoothing model, return to the original data. Set initial forecast to 400 and write formula for following forecasts.

Single Exponential Smoothing – Humboldt Electronics Write similar formula to determine Forecast for Period 11.

Single Exponential Smoothing – Humboldt Electronics To determine MAD start by writing formula to find the forecast error.

Single Exponential Smoothing – Humboldt Electronics Find absolute value of forecast error. Then write a formula to find MAD.

Single Exponential Smoothing – Humboldt Electronics Construct a graph of actual and forecast sales by returning to Chart Wizard, Line option. Identify the two Series to graph.

Single Exponential Smoothing – Humboldt Electronics Format and place graph as desired.

Double Exponential Smoothing Billingsley Insurance Issue: The claims manager has data for 12 months and wants to forecast claims for month 13. But the time series contains a strong upward trend Objective: Use Excel to develop a double exponential smoothing model. Data file is Billingsley. xls

Double Exponential Smoothing – Billingsley Insurance Open file Billingsley. xls

Double Exponential Smoothing – Billingsley Insurance Click on the Chart Wizard button then select Line.

Double Exponential Smoothing – Billingsley Insurance Click on the Series tab, then identify ranges for the Y and X variables.

Double Exponential Smoothing – Billingsley Insurance Label the axes.

Double Exponential Smoothing – Billingsley Insurance Format the graph as desired.

Double Exponential Smoothing – Billingsley Insurance To develop the double exponential smoothing equations, return to the data sheet and determine the initial values for C and T.