SamSong sells smart phones and uses a variety of sales forecasting methods. Its been selling its popular Basic model for many years. Basic sales dont vary by time of year so it uses a Naive or 2-Year Moving Average method. One of these two methods is selected based on each methods Mean Absolute Percent Error (MAPE). Below is a spreadsheet analyzing the forecasting performance for each method for the past 6 years, ending in 2019 (sales are in in M phones, where M stands for millions).

a. What Excel formula was entered in C6 to do a Fill Down to C10 and compute Naive forecasts to 2020?

C6: =

b. What Excel formula cell was put into cell D10, the 2-Year Moving Average forecast for 2020?

D10: =

c. What formula is in cell F11 to compute the MAPE for the 2-Year Moving Average forecasts from 2016 to 2019?

F11: =

d. Sales of Student phones vary by month, so a seasonal forecasting method is used. The table below shows the Seasonal Indices analysis (in K[000] phones) that was done for 2018 and 2019 to determine 2020s holiday sales forecast by month. (Note: the months of February through September are hidden.)

d.1 What formula was put into cell D2 to do a Fill Down to D13 and compute each months Monthly Average for 2018 and 2019?

D2: =

d.2 What will be the forecast for Student phone sales in December 2020 if SamSong expects to sell 3600K phones in 2020?

Forecasts Absolute Percent Error Annual sales (M phones) 2-Year Moving Average Naive 2-Year Moving Average Naive 22 25 3 Year 4 2014 5 2015 6 2016 7 2017 B 2018 9 2019 10 2020 11 36% 19% 8% 17% 25 MAPE 20% 8% D 2018 Sales K phones) Monthly Index 2019 Sales (Kphones) 110 250 200 06 1 Month 2 January 11 October 12 November 13 December 14 Total Year Monthly Average 105 200 350 525 175 150 350 500 1800 20 350 550 3.0 2400