Please See Attached Excel Ddba 8303 Purdue Univer

Please See Attached Excel Ddba 8303 Purdue Univer

Use Excel to answer all of the following questions. Label all output accordingly.

3-1. A sample of 20 automobiles was taken, and the miles per gallon, MPG, horsepower, and total weight were recorded.

Please see attached excel document

You may assume X1 = HorsPwr, and X2 = Weight. Or you may define using different variables, but be consistent.

(a). Develop a linear regression model to predict MPG, using horsepower as the only independent variable. Develop another model with weight as the independent variable. Which of the two models is better? Why? Make sure you appropriately label all Excel outputs.

(b). Using the same data given, develop multiple regression model. How does this compare with each of the models in (a)? Just all the relevant information to support your responses.

(c).

(i) Use just the multiple regression model you developed in (b) to predict MPG if HrsPwr is set at 150 and Weight is 4400. (ii) Next use the regression models for HrsPwr and Weight separately (independently) for HrsPwr= 150 and Weight= 4400 and predict MPG.

(iii). Based on you analyses above c (i & ii), and information gathered from a & b, which forecast do you think would be more accurate/realistic and why?

3-2.
(a). ABC company records during the past six weeks indicate the number of jobs requests:

WEEK

1

2

3

4

5

6

Requests (Actual)

46

40

38

42

44

45

Required: Predict the number of requests for Week 7 using each of the following methods:

iA four-period moving average.

ii. Exponential smoothing with a = .40. Forecast for Week 1 was 50.

2. (b). Two independent methods of forecasting based on judgment and experience have been prepared each month for the past 10 months

Compute MAD and MSE {sum of squared errors divided by (n-1)} for each forecast. Does either forecast seem superior? Explain.

The forecasts and actual sales are as follows:

Month

SALES (ACTUAL)

FORECAST I

FORECAST II

1

780

770

778

2

789

785

790

3

794

790

792

4

780

784

785

5

778

770

774

6

772

768

770

7

765

761

759

8

775

771

775

9

786

784

791

10

790

788

788

Please use the QM add in when answering questions for each and explain how the answer was computed.