Assignment
Spend Analysis
Please see the following data set for a small NGO (non-governmental organization) dedicated to humanitarian (medical) assistance for impoverished population in Africa. The data represent spend $ for a one month period related to medical consumables (band-aids, sutures, gauzes), pharma, and durable medical equipment. The SKU number constitutes the commodity category "A", "B", "C", etc. followed by the unique part number. As example, "F" designates Pharma and "M" designates all medical durable equipment. (Note that the categories go from A,B,C,D,E, F, and M). You don't have much more information other than the spend data by vendor, by commodity, and by number of items (purchases).
With this data, you are to conduct a spend analysis for insights and recommendations. The template tab shows two areas where you create two simple spreadsheets using the data set. The top portion will show total dollars by vendor in rank descending order and total dollars by commodity category. Within each commodity category column, show dollars by vendor by commodity.
Prepare the bottom spreadsheet similar to the top but, in this instance, display by volume (number of purchased items) rather than dollars. Again, this bottom spreadsheet will show vendor volume in descending rank order.
Please provide totals for all your columns and highlight your spreadsheet in such a way that the grader can easily see your answers.
In next worksheet tab, carry your spreadsheet results over to build an ABC Classification spreadsheet. This ABC chart will be used to construct a Pareto Graph showing the vendor spend in bar graph form, accumulated spend curve and an 80% line such that we can see where the accumulated curve crosses over the 80% line. The bars up to that point are your "A" classification vendors. Greater than 80% to 95% represent your "B" vendors. Greater than 95% will represent your "C" classification vendors.
Note that grade points are assigned to each question with total = 70 points. This is in lieu of a specific rubric.
