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.

18

20

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

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