1. Business
  2. Operations Management
  3. use excel to answer all of the following questions 1...

Question: use excel to answer all of the following questions 1...

Question details

Use Excel to answer all of the following questions:

#1.       ABC company records during the past six weeks indicate the number of jobs requests:

WEEK

1

2

3

4

5

6

Requests

45

38

36

42

46

43

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

       a.      Naïve

   b.      A four-period moving average.

       c.      Exponential smoothing with a = .30. Assume that the forecast for Week 2 = 43. Keep in mind that to apply this method, you must start forecasting in Week 2!

#2.

A.   Obtain the linear trend equation (first, plot the data and the regression line on the same graph, or alternatively use Data Analysis tool to obtain the “a” and the “b” values of the equation) for the following data on new checking accounts for Fair Savings Bank and use it to predict expected new checking accounts for periods 15 through 20. Enter the data on Excel using Period (X) as Column 1 and New Accounts (Y) in the second column.

  B.     Determine the correlation coefficient and interpret it.

  C.     What percentage of variation in new accounts is explained by the independent variable (time)?

Period

1

2

3

4

5

6

7

8

9

10

11

12

13

14

New Accounts

208

215

210

230

237

232

248

250

253

267

280

275

292

308

#3. Two independent methods of forecasting based on judgment and experience have been prepared each month for the past 10 months.  The forecasts and actual sales are as follows:

Month

SALES (ACTUAL)

FORECAST I

FORECAST II

1

780

772

768

2

789

785

788

3

794

790

792

4

780

784

790

5

776

770

774

6

772

768

770

7

765

761

759

8

775

771

775

9

786

784

788

10

790

788

788

REQUIRED:         Compute the MAD and MSE for each forecast.  Does either forecast seem superior?  Explain.

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution