Instructions & Answers:

Complete a thorough spend analysis for the Freight & Transportation and Packaging Materials categories. $($Assignment Total $40$ pts$)$

Prep: Data tab is locked so copy & paste data into new worksheet before working with the information

Part $1$: Use formulas, tables, pivots, charts, and$/$or other techniques to answer the following questions. $(25$ pts$)$

$1)$ What was the annual $\%$ change in spend for each of these categories from $2013$ to $2018($round to $1$ decimal place$)?$

Category $2013-20142014-20152015-20162016-20172017-2018$

Freight & Transportation $61\mathrm{.}3\%-18\mathrm{.}7\%9\mathrm{.}4\%-16\mathrm{.}6\%25\mathrm{.}4\%$

Packaging Materials $145\mathrm{.}6\%50\mathrm{.}9\%93\mathrm{.}9\%17\mathrm{.}2\%43\mathrm{.}7\%$

$2)$ In the Freight & Transporation category, which supplier represented the highest spend over the last $6$ years in each of these account areas?

Account $201320142015201620172018$

LTL

Non$-$LTL

$3)$ In the LTL account, which sub$-$account represented the highest spend each year? What $\%$ of each year's total LTL account spend does this sub$-$account represent?

LTL $201320142015201620172018$

Top Sub$-$Account

$\%$ of Total LTL Spend $($round to $1$ decimal place$)$

$4)$ What $\%$ of annual sales do these categories represent $($round to nearest $1$ decimal place$)?$

Spend Category $201320142015201620172018$

Freight & Transportation

Packaging Materials

$5)$ For the Freight & Transportation spend category, show a distribution of spending across accounts for $2018-$ display in a chart $($bar$,$ pie, combo, etc$)$ and expand the answer space as necessary