Question

Consider the following data. We want to monitor the forecasts.

Period..............Demand............Forecasts
......1...................274....................--
......2...................261....................274
......3...................294....................261
......4...................294....................294
......5...................307....................294

Calculate the UCL and LCL for the appropriate control chart?
Calculate the cumulative error, MAD, and tracking signals.

-Error of period 2

-Error of period 3

-Error of period 4

-Error of period 5

-Overal MSE:

-UCL to monitor the magnitude of the errors

-LCL to monitor the magnitude of the errors

- Are the errors in control?

Yes or No

-Cumulative error up to period 2

-Cumulative error up to period 3

-Cumulative error up to period 4

-Cumulative error up to period 5

-MAD up to period 2

-MAD up to period 3

-MAD up to period 4

-MAD up to period 5

-Tracking signal of period 2

-Tracking signal of period 3

-Tracking signal of period 4

-Tracking signal of period 5

- Based on your tracking signals, are the forecasts biased?

Yes or No

Answer 1

Below is the screenshot of the formula applied to get the result -

Book1 Excel File Home nsert Page Layout Formulas Data Review View Help Tell me what you want to do nWidth: Automatic Gridlines Headings Aall Colors- Fonts . ckground PrintHeight: Automatic Bring Forward-Back Themes Margins Orientation Size Print Breaks Background Print Area Effects 전 Scale: 100% | Print Sheet Options Titles Print Themes Page Setup Scale to Fit Period 1 Period Demand Forecast Error Absloute Error Cumulative Error MAD uared Error Tracking Signal 274 261 294 274 261 294 B3-C3 FABS(D3) B4-C4 FABS(D4 B5-C5 FABS(D5 B6-C6-ABS(D6) D3 D4+F3 D5+F4 D6+F5 -AVERAGE(SES3:E3) |=D312 AVERAGE(SE$3:E4)D4A2 -AVERAGE(SES3:E5) |=D512 AVERAGE(SE$3:E6) D642 G3/F3 G4/F4 G5/F5 -G6/F6 4 3 6 5 307 -AVERAGE(E3:E6) MAD -AVERAGE(H3:H6) MSE UCL LCL 10 -0-H7PL1/2) 12

Below is the required calculation result -

Book1 Excel File Home nsert Page Layout Formulas Data Review View Help Tell me what you want to do Colors nWidth: Automatic Gridlines Headings Al Fonts Margins Orientation Sz Effects A Fonts Themes ckground PrintHeight: Automatic Margins Orientation Size Print Breaks Background Print Area Titles -kScale 100% Print L| Print Themes Page Setup Scale to Fit Sheet Options A1 Period 1 Period|Demand Forecast Error Absloute Error Cumulative Error MAD uared Error Tracking Signal 274 261 294 274 261 13 13 13 20 20 13.00 23.00 15.33 14.75 169.00 1089.00 0.00 169.00 356.75 MSE 18.89 18.89 1.00 1.15 0.77 0.45 4 4 13 13 14.75 MAD 307 UCL LCL 10 12 13