A minimum of $40$ items is required from a variety of household expenditures from various areas of spending including groceries, transportation, and entertainment.

$$ Estimate the annual usage per item in dollars.

$$ Sort annual expenditure by item from highest dollar amount to lowest dollar amount. Total the annual dollar spend.

$$ Create a column showing the total accumulated dollar amount and populate that column.

$$ Calculate the cumulative $\%$ to the total annual dollar spend.

$$ Identify, by highlighting in pale yellow shading, the spend categories that account for $80\%$ of total annual spend.

$$ Create a combination bar and line chart $($graph$)$ that shows annual spend, accumulated total dollar spend, and $\%$ of total $($see the Spend Analysis Demo$)$

$$ Next, pick a category that represents a significant dollar spend $($maybe one of the top $10$ categories$)$

$$ Identify your suppliers for this category.

$$ Create a simple plan that outlines each step that you would take to find opportunities to save money in that spend category. Estimate how much money you could save.

$$ Write a paragraph about your thoughts and conclusions about what you have discovered. Did anything surprize you? Did you find the exercise to be useful either professionally or personally?

$$ This can all be done using sheets within one Excel workbook, or a separate Excel workbook and a Word document.

$$ Submit your document$($s$)$ to the appropriate drop box in DC Connect prior to $11$:$59$ pm on the due date.

$$

Additional Information

This exercise will help students to conduct a spend analysis on a category of goods and develop strategies which focus on cost reductions. In this case, the category is household expenditures that include groceries, transportation, and entertainment that your family purchases on an annual basis. In a manufacturing environment, the category could include any group such as MRO, steel, or any other significant group of purchased items.

Students will use a simple Pareto analysis to focus attention on those spend categories that are most significant to them. This approach can help a graduate initiate a cost reduction process for their employer. You will then summarize your findings and create a cost$-$reduction strategy based on your spend analysis.

This exercise may be conducted as an individual, or in pairs.

$$

Rubric

Criteria Marks

$(40$ total$)$ To achieve top marks, students must:

Part $1$

Items $/2$ Identify $30$ to $40$ items or categories of items.

Total Expenditure $/10$ Sort items from highest to lowest annual expenditure.

$$ Accumulate annual expenditures to show running total.

$$ Show that the total accumulated expenditure equals the total annual expenditure.

Top $80\%$ Identified $/3$ Highlight the top $80\%$ of the spend

Spreadsheet $/4$ Design the spreadsheet to look professional.

$$ Title the spreadsheet

$$ Provide a name and date at the top of the spreadsheet

Graph $/1$ Successfully transfer the data to a graph

Part $2$

Supplier List $/1$ Select a meaningful spend category.

$$ List the suppliers that supply most of the items in that category.

Supplier changes $/4$ Comments re: increasing or decreasing the number of suppliers you are currently using or any other changes to the supplier base.

$$ Justification for the increase, decrease or status quo

Opportunity Plan $/5$ Identification of opportunities to reduce costs.

$$ Description of a plan how to reduce costs.

$$ Cost reduction estimate.

Critical Thinking $/10$ Exploration of issues$/$concepts

$$ Summarize issues$/$concepts $/$ learnings.

$$ Comment about findings and usefulness of spend analysis.Item # Item Description Total Weekly $ Total Month $ Total Year $ Annual Accumulated Total $ Percentage of total

$\mathrm{ }$

Answer rating: 100% (QA)

To conduct a spend analysis on household expenditures including groceries transportation and enterta... View the full answer