Question

You are doing an internship with Macy’s and are tasked with forecasting sales so that you can make inventory orders. You have weekly sales data for men’s dress shirts in your store from each of the past six weeks. You decide to try both a 3-period moving average and exponential smoothing with λ = 0.7 to see which model is more accurate and then use that one to make forecasts. The sales data is shown below.

Week	# of Shirts Sold
1	423
2	448
3	437
4	361
5	395
6	416

What is the calculated MSE for the exponential smoothing method? Round intermediate calculations and final answer to 4 decimals.

What is the calculated MSE using the 3-period moving average? Round intermediate calculations and final answer to 4 decimals.

Answer 1

Simple Exponential Smoothing alpha- 0.7 Simple Percent Squared Error Exponential SalesSmoothing N/A 423.0 440.5 438.1 384.1 391.7 Period Error Abs. Error Error 423 448 437 361 395 416 25.0 3.5 77.1 10.9 24.3 25.01 5.58% 3.5 0.80% 77.11 21.34% 10.9 2.76% 24.31 5.83% 625.0 12.3 5936.7 118.5 588.8 2 3 4 6 -4.08 BIAS 28.14 7.26% 1456.2500 MADMAPE MSE

Formula:

AutoSave ExpSmcothxls Compatibility Mxle Excel Prabhu Pandia' ajan File Home Insert Page Laycut Data Review View Developer dd-ins Help Prabhu Power Pvot Tell me what you want to do Trace Precedents Show Formulas 氓lrace Dependents Yb Error Checking E Define Name i- Calculate Now Watch Calculation Calculate Sheet Use in Formula Insert AutoSum Recently Financial Logical Text Date & Lokup & Math & Mr Nan Manager Create from SelectionRemove Arrows -vormula Window Options Function Used--Time Reference Trig Functions Functian Library Defred Names Formula Auditing 18 3 Simple Exponenti 4 alpha 0.7 6 Simple Exponential Smoothin Percent Error Squared Error 8 Period Sales Error Abs. Error 423 448 437 361 395 416 NIA -B9 E10/B10 E11/B11 E12/B12 E13/B13 E14/B14 =D1012 D11 2 D12 2 D1342 D14 2 10 2 11 3 12 4 13 5 14 6 15 16 17 18 19 20 B10-C10 SE$4 B10+(1-SES4) C10 B11-C11 -$E$4*B11+(1-SE$4) C11 B12-C12 $ES4 B12+(1-SE$4) C12 B13-C13 SES4*B13+(1-SES4) C13 B14-C14 ABS(D10) ABS(D11) ABS(D12 ABS(D13 ABS(D14 -AVERAGE(D10:D18-AVERAGE(E10:E18) |#AVERAGE(F10:F18-AVERAGE(G10:G14 MSE BIAS MAD MAPE

Moving Average Model (1) Simple MA models period month sales 423 448 437 361 395 416 3-month MA forecast error ABS(error) 2 3 4 436.00 415.33 397.67 -75.00 20.33 18.33 75.00 20.33 18.33 6 25.67 BIAS 2124.8519 MSE MAD

Formula Ref:

AutoSave Off timeseriesmodels- forecastxls Compatibility Mode Excel File Home Insert Page Layout Formulas Data Review View Developer Add-ins Help Prabhu Power Pivot Tell me what you want to Define Name a Trace Precedents Show Formulas Use in Formula '- Trace Dependents Error Checking Insert Function AutoSum Recently Financial Logical Text Date &Lookup & Math & More Name Remove ArrowsEvaluate Formula Time Reference Trig Functions ManagerCreate from Defined Names Used ▼ Function Library Formula Auditing F26 =SUMSQ(E9:E25)/COUNT(E9:E17) 1 Moving Average M 3 (1) Simple MA mode period month sales 3-month MA forecast error ABS(error) 423 437 361 395 416 -AVERAGE(C6:C8) AVERAGE(C7: C9) AVERAGE(C8 C10) C9-D9 -C10-D10 -C11-D11 ABS(E9) ABS(E10) ABS(E11) 12 13 14 16 17 18 19 20 23 24 25 26 27 28 AVERAGE(E9:E25) BIAS MAD MSE