GRAPH THE THREE YEAR DATA SET:

$-$Create a line graph displaying the three data sets.

$-$Be sure it has an appropriate title $\backslash$& legend.

COMPLETE TABLE ONE $($Use Cell Formulas$)$

$-$Sum the total units by year.

$-$Sum the total historical units for all three years.

$-$Calculate the overall average for this historical data.

HINT: Divide by the total number of data points in the original data.

$-$Calculate the percent change year over year.

HINT: $\backslash \%$ Change $=($New Data $-$ Old Data$)/$Old Data

$-$Be sure the cells display percentage format with minimum one decimal place.

$-$Calculate the Year Four aggregate forcast with a $\backslash (1\backslash \%\backslash )$ increase.

$-$Calclate the new Year Four overall average.

HINT: Divide by the number of months in year four.

COMPLETE TABLE TWO $($Use cell formulas where appropriate$)$:

$-$Reviewing the graph, find which months have similar ranges of units sold. Copy the months $\backslash$& data that go together for each season into Table $\backslash$#$2.$

HINT: There are only $4$ seasons.

$-$Total all the data for each season.

$-$Calculate the seasonal average.

HINT: All the months grouped together represent a seasonal average. Divide by the number of data points for that group.

$-$Calculate each seasonal index using the historical average from Table $\backslash$#$1.$

COMPLETE TABLE THREE $($Use cell formulas$)$:

$-$Multiply the Year $4$ overall average from Table $\backslash$#$1$ times each seasonal index to determine the Year Four breakdown of sales by month.

HINT: Be sure to use the appropriate seasonal index that matches the month $\backslash$& season where it is grouped.

NOTE: Round all data to nearest whole number. Instructor must be able to view FORMULAS used for cell calculations.

Typed in final answers will not be accepted. HOOVER BOARD MONTHLY DEMAND IN UNITS

TABLE ONE:

$\backslash$begin$\{$tabular$\}\{|$c$|$c$|$c$|$c$|$c$|$c$|\}$

$\backslash$hline Month & Year $1$ Units & Year $2$ Units & Year $3$ Units & & $\backslash \backslash$

$\backslash$hline January & $357$ & $365$ & $375$ & & $\backslash \backslash$

$\backslash$hline February & $357$ & $365$ & $375$ & & $\backslash \backslash$

$\backslash$hline March & $454$ & $485$ & $500$ & & $\backslash \backslash$

$\backslash$hline April & $454$ & $485$ & $500$ & & $\backslash \backslash$

$\backslash$hline May & $598$ & $610$ & $620$ & & $\backslash \backslash$

$\backslash$hline June & $598$ & $610$ & $620$ & & $\backslash \backslash$

$\backslash$hline July & $598$ & $610$ & $620$ & & $\backslash \backslash$

$\backslash$hline August & $454$ & $485$ & $500$ & & $\backslash \backslash$

$\backslash$hline September & $357$ & $360$ & $366$ & & $\backslash \backslash$

$\backslash$hline October & $454$ & $485$ & $500$ & & OVERALL $\backslash \backslash$

$\backslash$hline November & $810$ & $820$ & $850$ & & HISTORICAL $\backslash \backslash$

$\backslash$hline December & $810$ & $820$ & $850$ & & AVERAGE $\backslash \backslash$

$\backslash$hline TOTAL UNITS BY YEAR: & $6,301$ & $6,500$ & $6,676$ & $19,477$ & $541\mathrm{.}0277778\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{2\}\{|$r$|\}\{$PERCENT INCREASE:

$($YEAR OVER YEAR$)\}$ & $3\mathrm{.}2\backslash \%$ & $2\mathrm{.}7\backslash \%$ & & $\backslash \backslash$

$\backslash$hline $\backslash$multicolumn$\{4\}\{|$r$|\}\{\backslash$begin$\{$tabular$\}\{$l$\}$

YEAR FOUR TOTAL UNIT FORECAST $\backslash \backslash$

$(1\backslash \%$ Increase over Year $3)$

$\backslash$end$\{$tabular$\}\}$ & & $\backslash \backslash$

$\backslash$hline & $\backslash$multicolumn$\{3\}\{|$r$|\}\{$NEW YEAR FOUR OVERALL AVERAGE:$\}$ & & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$

TABLE TWO:

$\backslash$begin$\{$tabular$\}\{$l$|$l$|$l$|$l$|$l$|$l$|$l$|$l$|\}$

$\backslash$cline $\{2-8\}$ & Month & & & & $\backslash$begin$\{$tabular$\}\{$c$\}$

TOTALOFALL $\backslash \backslash$

UNITS SOLD $\backslash \backslash$

WITHINASEASON

$\backslash$end$\{$tabular$\}$ & $\backslash$begin$\{$tabular$\}\{$c$\}$

SEASONAL $\backslash \backslash$

AVERAGE

$\backslash$end$\{$tabular$\}$ & SEASONAL INDEX

$\backslash$end$\{$tabular$\}$

TABLE THREE:

$\backslash$begin$\{$tabular$\}\{|$r$|$r$|\}$

$\backslash$hline MONTH & $\backslash$begin$\{$tabular$\}\{$r$\}$

YEAR FOUR $\backslash \backslash$

FORECAST

$\backslash$end$\{$tabular$\}\backslash \backslash$

$\backslash$hline January & $\backslash \backslash$

$\backslash$hline February & $\backslash \backslash$

$\backslash$hline March & $\backslash \backslash$

$\backslash$hline April & $\backslash \backslash$

$\backslash$hline May & $\backslash \backslash$

$\backslash$hline June & $\backslash \backslash$

$\backslash$hline July & $\backslash \backslash$

$\backslash$hline August & $\backslash \backslash$

$\backslash$hline September & $\backslash \backslash$

$\backslash$hline October & $\backslash \backslash$

$\backslash$hline November & $\backslash \backslash$

$\backslash$hline December & $\backslash \backslash$

$\backslash$hline

$\backslash$end$\{$tabular$\}$