Your results should be based on Excel formulas, such that the Instructor could change some of the data and your results would update accordingly $($i$.$e$.$ you should not be typing any numbers into any cells$).$ The spreadsheet should also be adequately formatted such that it is easy to understand your work.

Background:

SKUs can be differentiated by annual dollar usage $($demand $*$ dollars per unit$)$ in order to partition into A$,$ B$,$ and C groupings for the sake of determining appropriate inventory system design and level of management attention. A items usually make up about $20\%$ of the SKUs but account for $80\%$ of annual usage. These are the most important, so to speak. B items usually number about $30\%$ of total number of SKUs and $15\%$ of annual dollar usage while C items comprise about $50\%$ of items and $5\%$ of annual dollar usage.

General Instructions:

The 'ABC' worksheet shows demand and cost data for $138$ SKUs. The general process for performing ABC analysis in a spreadsheet is as follows:

For each SKU, determine annual dollar usage of each $($annual demand $*$ value per item$).$

Sort in descending order of annual usage $($highest at the top$).$

For each SKU, determine cumulative annual dollar usage of all SKUs from the first $($highest usage SKU$)$ to the current SKU. Also determine cumulative percentage of total annual dollar usage and cumulative $\%$ of SKU count $($note that the latter is by count, not using the demand$).$

Look for natural $$breaks$$ in the data, for example where the differences in cumulative $\%$ become noticeably less $$ this is likely a good spot to separate the classes. $($You may find it helpful to plot a graph with $\%$ of SKU count on the horizontal and $\%$ of annual dollar value on the vertical and then look for places where the line becomes flatter$).$ As a general guideline, $\text{'}$A$\text{'}$ items are the top $80\%$ of total sales that come from $20\%$ of the SKUs, $\text{'}$B$\text{'}$ items are the next $15\%$ of sales and come from $30\%$ of SKUs, and C items are the lower $5\%$ of sales but make up $50\%$ of SKU count.

classify each SKU as A$,$ B$,$ or C$.$ Also create a summary table to the right of your work $($e$.$g$.,$ see below$)$

Specific Question:

$2)[3$ marks$]$ classify each SKU as A$,$ B$,$ or C and create a summary table $($e$.$g$.,$ something like the table below$).($Note$,$ the specifying each as an $\text{'}$A$\text{'},\text{'}$B$\text{'},$ or $\text{'}$C$\text{'}$ doesn't necessarily require a formula $($although you might want to try to do one$),$ but everything else should be based on formulas.$)$