Forecasting

Please see the attchment below. 

Homework on Forecasting

Directions: For this homework you need to upload two files. One is a Word file containing solutions to this homework. Second is an Excel file showing your analysis.. Upload to Moodle by Nov 2.

Problem

1

:

Janet owns a Mexican restaurant which sells lunch buffets and has kept track of the following historical data about its unit price of buffets, advertising expenses, and buffets sold.

Unit Price (in dollars)

Advertising Expenses (in dollars)

Buffets Sold (Units)

7.50

1500

2000

7.00

1550

2200

6.75

1450

2210

7.25

1800

2100

6.50

1600

2500

7.00

1750

2150

6.00

1500

2600

Janet would like to use linear regression to forecast demand (buffets sold) for the next time period. Please help her by doing the following analysis. The Excel sheet you upload must contain solution to this problem. Tab 1 (call it ‘Regression data’) must contain data, Tab 2 (call it ‘Unit Price’) must contain regression results for part a, and Tab 3 (call it ‘Adv Expenses’) must contain regression results for part c. Round to two decimals places for all your answers.

a. Run linear regression and report the regression line using buffets sold as dependent variable and unit price as independent variable. (Provide answer below) (10 points)

b. What is the coefficient of determination for this model? What is the standard error of the estimate? (Provide answer below) (10)

c. Run linear regression and report the regression line using buffets sold as dependent variable and advertising expenses as independent variable. (Provide answer below) (10)

d. What is the coefficient of determination for this model? What is the standard error of the estimate? (Provide answer below) (10)

e. Which do you think is the better model? Model in part a or the model in part c? Explain. (Provide answer below) (10)

f. Using the better model forecast buffets sold using one of the following: unit price = 5.50 or advertising expenses = 1700. (Note: pick either unit price or advertising expenses based on your answer to question e). (Provide answer below) (10)

Problem 2:

The past demands at a medical clinic follow:

Week

Demand

Week

Demand

1

400

15

383

2

380

16

402

3

411

17

387

4

415

18

410

5

393

19

398

6

375

20

433

7

410

21

415

8

395

22

380

9

406

23

394

10

424

24

412

11

433

25

439

12

391

26

416

13

396

27

395

14

417

28

419

(Note: There are 28 weeks of data. Third and fourth columns are just a continuation of data in the first two).

The clinics administration is considering the following forecasting methods. Provide forecasts using each of the following methods. To be consistent, start your forecasts beginning with period 4. Answers for part a-d must be provided only in the Excel sheet not here. Tab 4 of your Excel sheet (call it ‘Forecast Errors’) must contain data, forecasts for all four methods. (Refer to appendix). Round to two decimals places for all your answers.

a. Naïve (1-period moving average) (5)

b. Simple moving average (3-period moving average) (15)

c. Three period weighted moving average, using weights 0.70, 0.20, and 0.10, with more recent data given more weight. (15)

d. Exponential smoothing, with α = 0.10. Use 400 as the initial forecast (for period 3). (15)

The administration would like to know which method is performing well.

a. Calculate MSD, MAD, TS, MSE, MAPE measures for all four methods. (Answer provided in Excel sheet only. Tab 4 must contain answers to this question. Refer to appendix).

b. Provide MAD values for all four methods. Fill the following table and provide your recommendation for the best method. (Answer here). (10)

Method

MAD

Interpret it

Naïve

Moving Average

Weighted Moving Average

Exponential Smoothing

Recommendation for best method:

c. Provide MSE values for all four methods. Fill the following table and provide your recommendation for the best method. (Answer here). (10)

Method

MSE

Naïve

Moving Average

Weighted Moving Average

Exponential Smoothing

Recommendation for best method:

d. Provide MAPE values for all four methods. Fill the following table and provide your recommendation for the best method. (Answer here). (10)

Method

MAPE

Naïve

Moving Average

Weighted Moving Average

Exponential Smoothing

Recommendation for best method:

e. Provide Tracking Signal values in the following table and fill it out. (Answer here). (10)

Method

TS

Comments on systematic bias

Naïve

Moving Average

Weighted Moving Average

Exponential Smoothing

Appendix:

Your Excel sheet must contain Regression data in the first tab. Name the first tab “Regression data.” See the image below.

The second tab must contain the results of regression using Unit price as independent variable. Call it “Unit Price.” Here is how the second tab looks like:

The third tab must contain the results of regression using Advertising expenses as independent variable. Call it “Adv Expenses.” Here is how the third tab looks like:

The fourth tab must contain the results of forecasts for all four methods including the forecast error measures. Call it “Forecast Errors.” Here is a screen shot:

1