Question

QUESTION 2 Use scratch paper as needed. Be sure to label the answer if it is on the paper. (Turn in the answers to the proctor to be mailed to the instructor.) 02. (10 POINTS) Calculate the seasonal index for the following data, and forecast sales for each quarter in 2013, if sales for the ar 2013 is forecasted to be 3000 wwwwwww ww www w SHOW ALL CALCULATIONS Year Qtrl Qtr2 Qtr3 Qtr4 2007 400 420 430 500 2008 520 530 560 600 2009 550 520 570 680 2010 560 570 570 600 2011 550 590 600 650 620 2012 600 630 670 2013

Answer 1

A 1. Qtrl Average sales =AVERAGE (B2:E2) =AVERAGE(B3:E3) AVERAGE(B4:E4) =AVERAGE(B5:E5) =AVERAGE(B6:E6) FAVERAGE(B7:E7) Year Qtr2 Qtr3 Otr4 400 420 500 2007 430 530 560 520 600 2008 520 570 550 680 2009 570 570 560 600 2010 600 650 6 2011 550 590 7 2012 620 630 670 600 Seasonal indices 9 Qtrl Qtr2 Qtr3 Qtr4 Yea C2/$F2 EC3/$F3 -E2/$F2 -E3/$F3 E4/$F4 -E5/$F5 E6/ $F6 -E7/$F7 -AVERAGE (E10:E15) 10 2007 11 2008 -B2/$F2 - B3 / $F3 - B4 / SF4 -D2/$F2 D3/$F3 D4/$F4 -D5/$F5 -D6/$F6 12 2009 13 2010 14 2011 15 2012 C4/$F4 C5/$F5 C6/SF6 -C7/$F7 -AVERAGE(C10:C15) -B5/SF5 -B6 /SF6 - B7 / SF7 -AVERAGE ( B10:B15) D7/$F7 -AVERAGE (D10:D15) 16 Average index 17 Total forecasted sales for 2013 3000 18 19 Qtrl Qtr2 Qtr3 Otr4 20 2013 $D$18*B16)/SUM($B$16:$E$16) ($D$18*C16)/SUM($B$16:$E$16) ($D$18*D16)/SUM($B$16:$E$16) SD$18 E16)/SUM($B$16:$E$16) 21 22 22

A C G 1 Average sales Year Qtrl Qtr2 Qtr3 Qtr4 2 2007 400 420 430 500 437.5 3 2008 520 530 560 600 552.5 4 2009 550 520 570 680 580 5 2010 560 570 570 600 575 6 2011 550 590 600 650 597.5 7 2012 600 620 630 670 630 Seasonal indices Year Qtrl Qtr2 Qtr3 Qtr4 10 2007 0.914 0.960 0.983 1.143 0.941 1.014 11 2008 0.959 1.086 0.897 1.172 12 2009 0.948 0.983 0.991 0.991 13 2010 0.974 1,043 0.921 14 2011 0.987 1.004 1.088 15 2012 0.952 0.984 1.000 1.063 16 Average index| 0.942 0.963 0.996 1.099 17 Total forecasted sales for 2013 18 3000 19 Qtr1 Qtr2 Qtr3 Qtr4 706.32 746.83 20 2013 722.34 824.51 21 22 CO st