Assignment 1: Forecasting Cases

Unit 5: Module 5 – M5 Assignment 1 Discussion Assignment 1: Forecasting Cases These assignments will help you learn about statistical models for forecasting. You will learn about time series methods, such as the moving average, exponential smoothing, and linear trend line and, regression, which is used for forecasting by establishing a mathematical relationship between two or more variables. I. Read Chapters 6 and 7 in your text and solve the following problems found in the Problems and Applications section: o Chapter 6: 1 through 3 o Chapter 7: 1 part a & b Use Excel to solve these problems. Post the spreadsheets in the M5: Assignment 1 Dropbox. The facilitator will provide additional instructions, as necessary, to help you learn this material and apply Excel models to solve these problems. II. Discuss the forecasting case Forecasting at State University posted in the Doc Sharing area, see file ″Forecasting Cases″. Present your response in a Word file and post it in the Discussion area. Participate in a group discussion about this case. Forecasting at State University By Week 5, Day 3, post your analysis and spreadsheets to the M5: Assignment 1 Dropbox. Assignment 1 Grading Criteria Maximum Points Problem #1 10 Problem #2 10 Problem #3 10 Problem #1 a & b 10 Forecasting at State University 10 Total: 50 Problems and Applications • 1. Using the data in the Excel file Demographics, determine if a linear relationship exists between unemployment rates and cost of living indexes by constructing a scatter chart and adding a trendline. What is the regression model and R2? • 2. Using the data in the Excel file Student Grades, construct a scatter chart for midterm versus final exam grades and add a linear trendline. What is the regression model and R2? If a student scores 85 on the midterm, what would you estimate her grade on the final exam to be? • 3. The managing director of a consulting group has the following monthly data on total overhead costs and professional labor hours to bill to clients:2 Total Billable $340,000 3,000 $400,000 4,000 $435,000 5,000 $477,000 6,000 $529,000 7,000 $587,000 8,000 • Develop a regression model to identify the fixed overhead costs to the consulting group. o a. What is the constant component of the consultant group’s overhead? o b. If a special job requiring 1,000 billable hours that would contribute a margin of $38,000 before overhead was available, would the job be attractive? • 1. The Excel file Closing Stock Prices provides data for four stocks over a one-month period. o a. Develop spreadsheet models for forecasting each of the stock prices using single moving average and single exponential smoothing. o b. Using MAD, MSE, and MAPE as guidance, find the best number of moving average periods and best smoothing constant for exponential smoothing.