Question

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.
• Develop a sales forecast for each of the 10 stores for the month of December, using:

1. A three month moving average
2. A 2- month weighted moving average, with weights of 0.7 on the most recent month and 0.3 on the older month.
3. Exponential smoothing with an alpha value of 0.25. Assume February forecast is January’s actual.
4. Compute measures of forecast accuracy to recommend the best forecasting technique to use for the data.
5. 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.

Monthly Profit Data for ten stores
Store	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
A1	$26,811.50	$27,322.19	$21,193.85	$22,725.94	$27,066.85	$28,854.28	$25,024.06	$27,066.85	$23,491.98	$29,109.63	$26,300.80	?
A2	$973.59	$1,021.09	$1,009.21	$1,329.79	$1,163.56	$1,116.07	$1,211.06	$1,282.29	$1,377.28	$1,068.58	$1,163.56	?
A3	$10,424.42	$8,687.02	$8,380.42	$8,176.02	$8,584.82	$8,789.22	$8,278.22	$9,709.02	$8,993.62	$8,380.42	$11,344.22	?
A4	$28,513.55	$27,513.08	$28,263.44	$25,762.25	$20,009.51	$24,761.77	$21,510.23	$22,010.46	$23,261.06	$20,259.63	$28,513.55	?
A5	$27,107.93	$21,234.54	$26,656.13	$21,234.54	$24,171.24	$22,364.04	$21,686.34	$24,171.24	$20,330.95	$18,523.75	$21,008.64	?
A6	$16,897.72	$19,009.93	$16,321.66	$16,321.66	$22,658.30	$20,546.09	$22,274.26	$15,361.56	$21,506.18	$22,466.28	$16,129.64	?
A7	$15,456.55	$20,964.06	$21,141.72	$15,989.54	$16,344.86	$19,720.43	$19,365.10	$14,212.92	$19,187.44	$20,608.73	$20,253.41	?
A8	$11,021.75	$8,502.49	$9,762.12	$11,441.62	$10,286.96	$10,496.90	$10,286.96	$11,756.53	$12,491.31	$11,546.59	$12,071.44	?
A9	$10,338.61	$10,232.03	$10,338.61	$12,257.11	$12,683.45	$11,937.36	$11,511.03	$11,937.36	$8,739.86	$9,485.94	$12,043.95	?
A10	$6,643.92	$5,979.53	$7,507.63	$6,245.28	$7,839.83	$7,042.56	$5,913.09	$5,315.14	$7,906.26	$6,378.16	$7,042.56	?

Answer 1

Ans a 3 Month Moving Average Forecasting for Apr sum of Profit Data for Jan, Feb, Mar divided by 3 (Jan +Feb+ Mar)/3 Forecasting for May -sum of Profit Data for Feb, Mar, Apr divided by 3 (Feb + Mar + Apr)/3 Feb Store Jan Mar Apr $25,109.18 $23,747.33 $23,662.21 $26,215.69 $26,981.73 $26,981.73 $25,194.30 $26,556.15$26,300.80 $1,001.30$1,120.03 $1,167.52$1,203.14 $1,163.56$1,203.14$1,290.21 $1,242.72$1,203.14 May Jun Jul Aug Sep Oct Nov Dec A2 $9,163.95 $8,414.49 $8,380.42$8,516.69 $8,550.75$8,925.49 $8,993.62$9,027.69$9,572.75 АЗ $28,096.69 $27,179.59 $24,678.40 $23,511.18 $22,093.84 $22,760.82 $22,260.58 $21,843.72$24,011.41 A4 $24,999.53 $23,041.74 $24,020.64 $22,589.94 $22,740.54 $22,740.54 $22,062.84 $21,008.65 $19,954.45 $17,409.77 $17,217.75 $18,433.87 $19,842.02 $21,826.22 $19,393.97 $19,714.00 $19,778.01 $20,034.03 A5 A6 $19,187.44 $19,365.11 $17,825.37 $17,351.61 $18,476.80 $17,766.15 $17,588.49 $18,003.03$20,016.53 A7 $9,762.12$9,902.08 $10,496.90 $10,741.83 $10,356.94 $10,846.80 $11,511.60 $11,931.48$12,036.45 A8 $10,303.08 $10,942.58 $11,759.72 $12,292.64 $12,043.95 $11,795.25 $10,729.42 $10,054.39 $10,089.92 A9 $6,710.36$6,577.48$7,197.58$7,042.56 $6,931.83$6,090.26$6,378.16$6,533.19$7,108.99 A10

A 2- month weighted moving average, with weights of 0.7 on the most recent month and 0.3 on the older month Forecasting for Mar for perticular store (0.3*Jan +0.7*Feb)/sum of weights (0.3*an 0.7*Feb)/(0.3+0.7 Forecasting for Apr for perticular store (0.3*Feb+0.7*Mar)/sum of weights- (0.3*Feb+0.7"Mar)/(0.3+0.7 $27,168.98 $23,032.35 $22,266.31 $25,764.58 $28,318.05 $26,173.13 $26,454.01 $24,564.44$27,424.34 $27,143.45 $1,006.84 $1,012.77$1,233.62$1,213.43 $1,130.32 $1,182.56$1,260.92$1,348.78 $1,161.19 $1,135.07 2 $27,813.22 $28,038.33 $26,512.61 $21,735.33 $23,336.09 $22,485.69 $21,860.39 $22,885.88 $21,160.06 $26,037.37 0 $22,996.56 $25,029.65 $22,861.02 $23,290.23 $22,906.20 $21,889.65 $23,425.77 $21,483.04$19,065.91 $20,263.17 $19,311.81 $21,088.42 $17,535.19 $16,238.26 $18,707.76 $19,471.70 $15,758.57 $17,695.08 $20,182.34 $20,360.01 3 3 $10,264.00 $10,306.64 $11,681.56 $12,555.55 $12,161.19 $11,638.93 $11,809.46 $9,699.11$9,262.12 $11,276.55 2

Ans c Exponential smoothing with an alpha value of 0.25. Assume February forecast is January's actual Formula for forecating F(t+1) Ft) + alpha*(A(t) -F(t) F(t+1) Forcated value for period t+1 F(t) Forcated value for period t A(t)Actual value for period t Feb Store Jan Mar May Jun Jul Aug Sep Oct Nov Dec Apr $26,811.50 $26,939.17 $25,502.84 $24,808.62 $25,373.17 $26,243.45 $25,938.60 $26,220.66 $25,538.49 $26,431.28 $26,398.66 973.59$985.47$991.40$1,076.00$1,097.89$1,102.43 $1,129.59 $1,167.77$1,220.14$1,182.25$1,177.58 A2 $10,424.42$9,990.07 $9,587.66 $9,234.75 $9,072.27$9,001.50 $8,820.68 $9,042.77 $9,030.48 $8,867.97 $9,487.03 АЗ $28,513.55 $28,263.43 $28,263.43 $27,638.14 $25,730.98 $25,488.68 $24,494.07 $23,873.16 $23,720.14$22,855.01 $24,269.65 A4 $27,107.93 $25,639.58 $25,893.72 $24,728.92 $24,589.50 $24,033.14 $23,446.44 $23,627.64 $22,803.47 $21,733.54 $21,552.31 A5 $16,897.72 $17,425.77 $17,149.74 $16,942.72 $18,371.62 $18,915.24 $19,754.99 $18,656.63 $19,369.02 $20,143.34 $19,139.91 A6 $15,456.55 $16,833.43 $17,910.50 $17,430.26 $17,158.91 $17,799.29 $18,190.74 $17,196.29 $17,694.08 $18,422.74 $18,880.41 A7 $11,021.75 $10,391.94 $10,234.48 $10,536.27 $10,473.94 $10,479.68 $10,431.50 $10,762.76 $11,194.90 $11,282.82 $11,479.97 A8 $10,338.61 $10,311.97 $10,318.63 $10,803.25 $11,273.30 $11,439.31 $11,457.24 $11,577.27 $10,867.92 $10,522.42 $10,902.81 A9 $6,643.92 $6,477.82 $6,735.27 $6,612.78 $6,919.54 $6,950.29 $6,690.99 $6,347.03 $6,736.84 $6,647.17$6,746.02 A10

Ans d We are using MAPE (Mean absolute percentage Error) to measure forecast error MAPE (absolute(Actual- Forecast)/Actual) *100 Error Table for 3 Period Moving Average Feb Store Jan Apr May Jun Aug Sep Oct Dec MAPE Mar Jul Nov 5% 13% A1 10% 1% 9.39% 15% 4% 5% 1% 21% A2 25% 10.39% 5% 3% АЗ 2% 196 7.75% 2% 10% 23% 11.29% 5% 4% 19% 6% 8.86% AS A6 2496 10% 11% 23% 17.33% 1096 10% 30% 15.21% A7 15% 11% 496 12% 13% 1% 6.17% A8 15% 1% 1% A9 14% 13% 12.93% 16% 35% 17% 16% 30% 13.18% A10

Error Table for wighted Moving Average Feb Jul Store May Jan Jun Sep Oct MAPE 4% A1 196 11% 13% 3% 16% 11.88% 24% 6% 9% 9.79% A2 26% 10% 4% 4% 5% АЗ 496 25% 8.60% 33% 2% 13% 26% 12.29% 14% 4% 15% 5% 996 16% 10.74% 13% 5% 196 5% 19% 37% A6 18.00% 9% 3% 37% 14% A7 15.36% 5% 196 196 2% 6.87% A8 1% 16% 5% 35% 10.93% A9 23% 5% A10 13% 16% 31% 15.18%

Error Table for Exponential Smoothing Feb StoreJan May Jul Sep Oct MAPE Jun A1 5% 496 9.50% 5% 25% 15% 14% 9.35% A2 1% АЗ 1796 11.53% 4% 10% 18% 11% 3% 17% A4 12.47% 11% 16% 4% 2% 3% 3% 12.23% 10% 23% 1196 5% 11% 14% 15.44% 25% 15% 25% А6 13% 14% 14.79% A7 26% 11% 2% 2% 14% 8.58% А8 11% 1% 16% 15% 196 496 32% 13% 10.18% 15% 11% 16% 2% 6% 6% А 10 14% 26% 12.45%

Ans e MAPE from MAPE fromMAPE from Expone 3 period Rank Store wighted moving average Smoothing Average 9.4% 11.9% 9.5% 4 A1 9.8% 10.4% 9.3% АЗ 11.5% 8.6% A4 11.3% 12.3% 12.5% 8.9% 10.7% 12.2% A5 17.3% 18.0% 15.4% 10 A6 14.8% 15.2% 15.4% A7 6.2% 6.9% A8 8.6% 10.9% 10.2% A9 12.9% 13.2% 15.2% 12.4% A10 Average Store Error We are taking average of Store Error and it shows that 3 period Moving average have less Error percentage as compaire to others.