Develop a sales forecast for each of the 10 stores for the month of December, us
ID: 3208052 • Letter: D
Question
Develop a sales forecast for each of the 10 stores for the month of December, using:
Key Objective:
Crusty Pizza Executives must forecast December sales for the 10 stores in worksheet
“Time Series”. Use data in worksheet “
Time Series”. Plot the data for each store.
a) A three-month moving average
b) A 2- month weighted moving average, with weights of 0.7 on the most recent month and 0.3 on the
older month.
c) Exponential smoothing with an alpha value of 0.25.
d) Compute measures of
forecast accuracy to recommend the best forecasting technique to use for the
data.
e) Rank the 10 stores based on the forecasts you made
with the technique that you determined (in the above step) to be the best forecasting method.
f) You may use up to 5 pages for the body of report, plus a cover page, and any appendix
Crusty Dough Pizza Company Monthly Sales Data for ten stores Store Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A1 $12,180.49 $15,087.20 $11,350.01 $11,488.42 $13,564.64 $16,609.76 $14,533.54 $13,010.98 $12,042.08 $15,225.62 $14,118.30 ? A2 $6,845.97 $6,465.64 $6,148.69 $5,197.87 $7,606.63 $6,465.64 $6,719.19 $6,338.86 $6,402.25 $6,529.03 $7,226.30 ? A3 ($8,667.03) ($7,614.03) ($9,234.03) ($6,885.03) ($9,315.03) ($7,128.03) ($8,991.03) ($7,290.03) ($9,234.03) ($9,477.04) ($7,290.03) ? A4 $20,278.69 $16,062.33 $16,664.67 $18,270.90 $19,074.01 $23,089.60 $21,081.81 $23,691.93 $21,282.58 $19,676.35 $20,479.47 ? A5 $22,000.07 $18,228.63 $22,628.65 $22,209.60 $19,485.78 $18,438.16 $25,142.94 $24,723.89 $21,161.97 $24,514.37 $19,695.30 ? A6 $12,378.68 $17,813.22 $18,115.14 $17,058.42 $15,548.83 $16,303.63 $13,586.36 $15,397.87 $12,227.72 $15,548.83 $15,548.83 ? A7 $34,925.57 $40,011.82 $30,517.49 $32,891.07 $37,977.32 $32,551.99 $28,143.91 $32,891.07 $40,011.82 $39,333.65 $29,839.32 ? A8 $10,688.45 $11,349.59 $10,908.83 $11,239.40 $12,451.49 $10,688.45 $11,680.16 $12,451.49 $12,120.92 $9,255.98 $10,578.26 ? A9 ($2,842.50) ($2,325.68) ($2,354.39) ($2,325.68) ($3,072.19) ($2,526.66) ($3,359.32) ($2,842.50) ($3,014.77) ($3,359.32) ($2,813.79) ? A10 $22,208.11 $19,785.41 $22,813.78 $18,372.16 $22,813.78 $21,198.65 $17,160.81 $19,179.73 $17,766.49 $23,621.35 $21,400.54 ?Explanation / Answer
Three month moving average 2 month weighted average exponential smoothing A1 $13,795.33 $14,450.50 $13,825.20 A2 $6,719.19 $7,017.12 $6,687.40 A3 ($8,667.03) ($7,946.13) ($8,307.00) A4 $20,479.47 $20,238.53 $20,657.19 A5 $21,790.55 $21,141.02 $21,942.07 A6 $14,441.79 $15,548.83 $14,934.01 A7 $36,394.93 $32,687.62 $34,356.98 A8 $10,651.72 $10,181.58 $10,972.90 A9 ($3,062.63) ($2,977.45) ($2,979.65) A10 $20,929.46 $22,066.78 $20,736.68 a) Three Month Moving average for every store can be calculated by =sum(recent three months value)/3 b) A 2 month weighted average for every store can be calculated by =0.7*recent month + 0.3*older month c) We calculate the exponential smoothing by st = alpha(xt-1) +(1-alpha)st-1 where alpha = 0.25 d) The best prediocted forecast is by two month weighted average
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.