Assignment
Work through the problems in Excel. Enter your answers in the following attachment labeled questions. Work the
Unit2 Linear Regression
problem in excel also then enter answers on word doc. Must have both excel and word docs.
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.
Question 1 2 pts
National Scan, Inc., sells radio frequency inventory tags.
Month
ly sales for a seven-month period were as follows.
| Month |
Sales (000) |
||
|
Feb |
19 |
||
|
Mar |
18 |
||
|
Apr |
15 |
||
|
May |
20 |
||
|
Jun |
|||
|
Jul |
22 |
||
|
Aug |
a. Plot the monthly data in Excel. Copy your graph from Excel and paste it in the space below.
HTML Editor
Keyboard Shortcuts
0 words
Question 2 6 pts
b. Forecast September sales volume using each of the following. Round all answers to two decimal places.
1. the naive approach
2. a five-month moving average
3. a weighted average using 0.60 for August, 0.30 for July, and 0.10 for June
4. Exponential smoothing with a smoothing constant equal to 0.20, assuming a March forecast of 19(000)
5. a linear trend equation (Use Excel to generate the equation of the trend line and then calculate the forecast.)
For September x=8
Forecast for September=
Question 3 2 pts
c. Which method seems least appropriate? Why?
Five month moving average because the data appears to vary around average of 19000 (18857.14)
d. What does the use of the term “sales” rather than “demand” presume?
The use of sales instead of demand implies that the sales adequately reflect demand.
Provide answers to both of these questions in the space below.
HTML Editor
Keyboard Shortcuts
0 words
Question 4 10 pts
A dry cleaner uses exponential smoothing to forecast equipment usage at its main plant. August usage was forecasted to be 88% of capacity; actual usage was 89.6% of capacity. A smoothing constant of 0.1 is used. Round all answers to two decimal places.
a. Prepare a forecast for September.
b.. Assuming actual September usage of 92%, prepare a forecast October usage.
Question 5 10 pts
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. Round all answers to two decimal places.
a. naive
b. a four-month moving average
c. exponential smoothing with alpha=0.30; use 20 for week 2 forecast.
Question 6 3 pts
Air travel on Mountain Airlines for the past 18 weeks was:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
405
410
420
415
412
420
424
433
438
440
446
451
455
464
466
474
476
482
a. Explain why an averaging technique would not be appropriate for forecasting.
HTML Editor
Keyboard Shortcuts
0 words
Question 7 7 pts
b. Use an appropriate technique to develop a forecast for the expected number of passengers for the next three weeks. Explain your rationale as well as provide your forecasts.
HTML Editor
Keyboard Shortcuts
|
Week |
Forecast demand |
0 words
Question 8 20 pts
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. Use the data analysis toolpak function within Excel to generate the equation of the line.
a. linear equation of trendline y = x + (Round to three decimal places.)
y =29.525 + 1.585x
b. forecast for June of year 2 (Round to two decimal places.)
c. number of customer service representatives needed for June of year 2