Linear Regression
Forecasting
Your boss comes in “with their hair on fire”, and says, “just came from a staff meeting and the CEO announced the building next door is coming available at a really good price per square foot.” He needs you to tell him if the forecasted demand for the next 36 months is high enough that we may need some of the new space. He follows up with, “If we miss this opportunity, and later we figure out that we need more space then the only option is to rent space in another building about 5 miles away at almost double the price per square foot”.
As the operations manager, the last thing you want to do is to split your staff across two different buildings.
That is going to be a staffing and scheduling headache, not to mention morale will take a hit. Do you shoot from the hip and tell your boss, “no doubt, forecasted demand indicates we can use the space”, or do you say, “let me run the forecast and get back to you later today”? Which response has the most risk? Are you willing to bet your job on your answer?
Forecasting is both an art and a science. In the assignments for Unit 2 you will get the opportunity to practice forecasting by solving end of chapter problems and completing a linear trending assignment. In order to determine if it makes sense to rent additional space, it will be important to assess the level of operational efficiency of existing processes associated with the delivery of products to consumers. Improving process efficiencies could result in a reduction in labor requirements. Maybe the company can support greater demand with a leaner labor force. In this unit, in addition to applying forecasting models to scenarios, you will also gain a more in-depth understanding of the concept and practices used to create efficient organizations.
Unit Learning Outcomes
- Develop a plan for forecasting impacts to an organization’s bottom line. (CLO 1, 2, 4, and 7)
- Demonstrate how to perform forecasting using data and statistics. (CLO 4 and 5)
- Identify trends and patterns in data as they apply to forecasting. (CLO 1, 3, 5, and 7)
- Develop a data collection plan that will permit the creation of an accurate and reliable forecasting model. (CLO 3, 4, and 5)
Directions
1. End of Chapter Problems (40 points): Answer the following end of chapter problems from the textbook:
Chapter 3 – problems 2, 3, 4 and 8 (pages 125-126, 10 points each).
View the following example videos before working the problems:
C3 Example 03-03
Play media comment.
(mp4, 37MB)
C3 Example 03-04
Play media comment. (mp4, 60MB)
C2 Example 03-08
Play media comment. (mp4, 82MB)
Video files are also available in
Files
for download.
2. Linear Regression Forecasting Assignment: Watch the video and complete the assignment below (20 points). You must use the data analysis tool pak method in Excel (see second part of video) to perform the linear regression, and include the regression table in your spreadsheet. Simply adding a trend line to the data graph does not meet this requirement.
Download the file
Unit 2 – Individual Assignment – Linear Regression
, follow the directions, and submit when complete. Document is also located in Files on the Student View folder.
MBA576 Unit 2 Individual Assignment
You just finished your MBA degree and your boss wants “to show you off” in an upcoming staff meeting because she feels you have a lot of potential. Needless to say, you feel some pressure.
She says the company’s machines have required “high number of repairs” over the past year and feels the repair service staff needs to be “beefed up”, but she is not sure how many new staff are needed. The repair department was stood up one year ago, and she tasks you to come up with staffing estimates to make sure there are enough repair staff to meet the demand. Below is the data she gives you for the past 16 months, and then she asks, “How many repair staff positions would you forecast we need in the next 6 months?”
Year 1 – # of repair calls received. The repair staff is open from 7am to 7pm, seven days a week, and repair staff work the entire 12-hour shift. You can assume a single repair takes an average of 30 minutes for each repair.
Month
Average # of repair calls received per day for each month the repair office was open in year 1
Jan
25
Feb
19
Mar
28
Apr
32
May
42
Jun
47
Jul
50
Aug
49
Sep
52
Oct
54
Nov
53
Dec
59
Jan
49
Feb
40
Mar
42
Apr
47
a. Using Excel, create a linear regression equation to model the number of repairs per month.
b. How many repair calls does the regression equation predict for June of year 2?
c. Given your prediction for June of year 2, how many repair staff members do you forecast will be needed per 12-hour shift to handle the load?
Submit your findings in a Word document and include the Excel spread sheet.
PROBLEMS
Question 2. National Scan, Inc., sells radio frequency inventory tags. Monthly sales for a seven-month period were as follows:
Month Sales
(000 units)
Feb. 19
Mar. 18
Apr. 15
May 20
Jun. 18
Jul. 22
Aug. 20
Plot the monthly data on a sheet of graph paper.
Forecast September sales volume using each of the following:
(1) The naive approach
(2) A five-month moving average
(3) A weighted average using .60 for August, .30 for July, and .10 for June
(4) Exponential smoothing with a smoothing constant equal to .20, assuming a a March forecast of 19(000)
(5) A linear trend equation
Which method seems least appropriate? Why? (Hint: Refer to your plot from part a.)
What does use of the term sales rather than demand presume?
Question 3. A dry cleaner uses exponential smoothing to forecast equipment usage at its main plant. August usage was forecasted to be 88 percent of capacity; actual usage was 89.6 percent of capacity. A smoothing constant of .1 is used.
Prepare a forecast for September.
Assuming actual September usage of 92 percent, prepare a forecast for October usage.
Question 4. An electrical contractor’s records during the last five weeks indicate the number of job requests:
Week 1 2 3 4 5
Requests 20 22 18 21 22
Predict the number of requests for week 6 using each of these methods:
Naive
A four-period moving average
Exponential smoothing with α = .30; use 20 for week 2 forecast
Question 8. Air travel on Mountain Airlines for the past 18 weeks was:
Week Passengers
1 405
2 410
3 420
4 415
5 412
6 420
7 424
8 433
9 438
10 440
11 446
12 451
13 455
14 464
15 466
16 474
17 476
18 482
Explain why an averaging technique would not be appropriate for forecasting.
Use an appropriate technique to develop a forecast for the expected number of passengers for the next three weeks.