Guide to Using Excel 2007 For Basic Statistical
Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 8 th Ed. Chapter 16: Analyzing and Forecasting Time-Series Data By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2011
Chapter 16 Excel Examples n n Trend Based Forecasting Taft Ice Cream Company Nonlinear Trend Harrison Equipment Company Seasonal Adjustment Big Mountain Ski Resort Single Exponential Smoothing Dawson Graphic Design More Examples
Chapter 16 Excel Examples n Double Exponential Smoothing Billingsley Insurance Company
Trend Based Forecasting Taft Ice Cream Company Issue: The owners of Taft Ice Cream Company are considering expanding their manufacturing facilities. The bank requires a forecast of future sales. Objective: Use Excel 2007 to build a forecasting model based on 10 years of data. Data file is Taft. xls
Trend Based Forecasting – Taft Ice Cream Company Open the file Taft. xls
Trend Based Forecasting – Taft Ice Cream Company • Select the Insert tab • Select Line Chart • Select the desired chart
Trend Based Forecasting – Taft Ice Cream Company To Format Chart • Click Select Data • Under Horizontal (categories) Axis Label select Edit and input Year column data. • Click Layout > Chart Title and enter desired title • Click Layout > Axis Titles and enter vertical and horizontal axes titles. Using the Chart Tools Design and Layout Tabs, format the chart
Trend Based Forecasting – Taft Ice Cream Company • To develop the Linear model: • Select the Data tab • Select Data Analysis • Select Regression
Trend Based Forecasting – Taft Ice Cream Company • Use Sales for the Y range • Use t (time) for the X range
Trend Based Forecasting – Taft Ice Cream Company • Linear Trend Equation: Sales = 277333. 33 + 14575. 76(t)
Trend Based Forecasting – Taft Ice Cream Company • Open file Taft. xls • Select Sales data • Click on Insert > Line Chart • On the Chart Tools Design Tab, click Select Data • Under Horizontal (categories) Axis Label select Edit and input Year column data. • Click Layout > Chart Title and enter desired title • Click Layout > Axis Titles and enter vertical and horizontal axes titles.
Trend Based Forecasting – Taft Ice Cream Company • Select Line Plot • Right Click and select Add Trendline • Linear
Trend Based Forecasting – Taft Ice Cream Company Format the chart as desired Linear trend line: Ft = 277, 333. 33 + 14, 575. 76(t)
Trend Based Forecasting – Taft Ice Cream Company To compute MAD and MSE rerun the Regression with the residuals option selected.
Trend Based Forecasting – Taft Ice Cream Company 1. In Column D Compute the Square of the residual (=c 25^2). Copy Down to D 34. 2. In Column E compute the absolute value of the Residuals (=abs(c 25)). Copy Down to E 34. 3. Sum Columns D and E
Trend Based Forecasting – Taft Ice Cream Company Divide both summed values by 10 (Number of observations) to compute MSE and MAD
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 2007 to develop a nonlinear forecasting model. Data file is Harrison. xls
Nonlinear Trend – Harrison Equipment Company • Open file: Harrison. xls • Highlight the Repair cost (Column D) • Select Insert Tab, Click Line • Add x-axis labels and titles as explained previously.
Nonlinear Trend – Harrison Equipment Company • Left click on any of the data points on the chart • Right click • Select Add Trendline • Select Linear • Close
Nonlinear Trend – Harrison Equipment Company The Trendline is plotted against the data
Harrison Equipment Linear Trend Regression Model • Set up Regression as before. • Calculate Durbin-Watson statistic using Excel formulas Durbin-Watson statistic
Transformed Regression Model for Harrison Equipment • Open file Harrison. xls • Create a new column using Excel formulas to create t 2 variable. • Select PHStat. • Select Regression • Select Multiple Regression • Define dependent variable (repair cost) • Define independent variables t and t 2 • Check ANOVA • Check Durbin-Watson statistic • Select OK
Transformed Model for Harrison Equipment Fitted Values
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 2007 to develop a forecasting model for seasonal data. Data file is Big Mountain. xls
Seasonal Adjustment – Big Mountain Quarterly Sales • Open file Big. Mountain. xls • Select Sales data • Click Insert Tab, click Line • Format Line Chart adding x-axis labels and titles as displayed
Seasonal Adjustment – Big Mountain Ski Resort • Compute Column E as the average of Cells D 2, D 3, D 4, and D 5 • Copy and paste this formula down
Seasonal Adjustment—Big Mountain Ski Resort • Compute Column F as the average of the two values from Column E (E 3 and E 4) • Copy and paste this formula down • Compute Column G as the ratio of columns D/F
Seasonal Adjustment – Big Mountain Ski Resort To find the seasonal Index values: • Group the ratio-tomoving average values by season for summer, fall, winter, spring. • Compute the average for each quarter. Seasonal indexes: Summer = 1. 3231 Fall = 0. 6256 Winter = 1. 4412 Spring = 0. 6077
Seasonal Adjustment – Big Mountain Ski Resort Deseasonalize sales by dividing each sales value by its corresponding seasonal index. Click the Insert Tab, Click Line and graph the deseasonalized sales values. Format Chart as displayed
Regression Trend Line of Big Mountain Deseasonalized Data Setup and run a regression with the y variable as the deseasonalized sales and the x variable as the quarter number.
Big Mountain Regression Using Dummy Variables • Open file Big Mountain. xls • Create Dummy Variables for the Winter, Spring, and Summer Quarters • Use Excel’s Regression tool to run model • The y-variable is sales • The x-variables are quarter, winter dummy, spring dummy, and summer dummy.
Single Exponential Smoothing Dawson Graphic Design Issue: The company needs to develop a forecasting model to help forecast future customer calls, and wants the model to give more weight to recent values. Objective: Use Excel 2007 to develop a single exponential smoothing forecasting model. Data file is Dawson. xls
Single Exponential Smoothing – Dawson Graphic Design Open the file Dawson. xls
Single Exponential Smoothing – Dawson Graphic Design Using Excel’s Insert tab, create a line chart. Format Chart as explained earlier.
Single Exponential Smoothing – Dawson Graphic Design To develop the exponential smoothing model, return to the original data. Add two new column labels as shown. Set initial forecast to 400(cell C 3) and create the formula for single exponential smoothing forecasts (Equation 16. 16) for period t+1.
Single Exponential Smoothing – Dawson Graphic Design • Calculate forecast errors and absolute forecast errors using Excel formulas • Sum the column of absolute forecast errors. • Calculate the MAD using the average function • Create line chart of actual and single smoothed forecast values Single smoothed forecast
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 2007 to develop a double exponential smoothing model. Data file is Billingsley. xls
Double Exponential Smoothing – Billingsley Insurance • Open the file Billingsley. xls Select Claims data Click on Insert Tab, Click Line , Select a style Format Chart as displayed.
Double Exponential Smoothing – Billingsley Insurance Create new column headings as shown. Use Equation 16. 18 to populate the Constant column. Use Equation 16. 19 to populate the Trend Column. Calculate Forecast using Equation 16. 20 Compute Forecast Error. Compute Absolute Forecast Error using the ABS function. Calculate the MAD value, using the AVERAGE function.
- Slides: 39