Spend Analysis

Spend Analysis is defined as an annual review of the organization's total purchases, which provides answers to a series of pertinent questions. In this assignment, you are required to analyze the data provided below and develop a series of charts and explanations to the meaning of the data analyzed.

This analysis is often referred to as the ABC Analysis.

You are required to produce this in an Excel Spreadsheet

Complete the following Analyses and answer the two questions.Assignment:

ABC Analysis by Annual Dollar Amount

Calculate the dollar amount of the annual usage for part by multiplying annual unit usage by unit cost

Sort the items according to their annual dollar usage (from largest to smallest)

Calculate the cumulative annual dollar usage

Calculate the percentage usage

Classify the part as A,B, or C where A is about 20% of items that account for 80% of dollar usage, B is about 30% of items that account for 15% of dollar usage, C is about 50% of items that account for 5% of dollar usage

ABC Analysis by Supplier

Calculate the dollar amount of the annual usage for part by multiplying annual unit usage by unit cost

Sort the items according to supplier

Subtotal for each supplier by going to the Data tab and clicking on the subtotal icon and have it subtotal at each change in supplier and subtotal the annual usage

Click on 2 so that it only shows the subtotals.

Sort the Annual $ Usage subtotals from Largest to smallest

Calculate the cumulative annual dollar usage for each supplier

Calculate the percentage usage

Classify the part as A,B, or C where A is about 20% of the suppliers that account for 80% of dollar usage, B is about 30% of the suppliers that account for 15% of dollar usage, C is about 50% of the suppliers that account for 5% of dollar usage

ABC Analysis by Category:

Calculate the dollar amount of the annual usage for part by multiplying annual unit usage by unit cost

Sort the items according to category

Subtotal for each category by going to the Data tab and clicking on the subtotal icon and have it subtotal at each change in category and subtotal the annual usage

Click on 2 so that it only shows the subtotals.

Sort the Annual Usage subtotals from largest to smallest

Calculate the cumulative annual dollar usage for each category

Calculate the percentage usage

Classify the part as A,B, or C where A is about 20% of the categories that account for 80% of dollar usage, B is about 30% of the categories that account for 15% of dollar usage, C is about 50% of the categories that account for 5% of dollar usage

Questions:

For each of the three analyses done above, what do they tell you as a buyer?

What can be done with information to improve your purchase process in the future?