Topic Specific Assignment #1

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

Part Number	Supplier	Category	Annual Usage	Unit Price
1	Smithson	Mechanical	1,000	$10.00
2	Robertson	Stationary	10,000	0.75
3	Tennant	Production	500	2.50
4	Dryell	MRO	100	9.00
5	Minline	Electrical	250	3.00
6	Leader	Facility	25	450.00
7	Systematic	Repair	75	1,525.00
8	Forward	Automation	10	750.00
9	Outward	Chemical	2,500	1.25
10	Onward	Chemical	12	850.00
11	Smithson	Mechanical	750	2.50
12	Robertson	Stationary	5,000	1.00
13	Tennant	Production	375	.95
14	Dryell	MRO	38	525.00
15	Minline	Electrical	650	3.55
16	Leader	Facility	12	2,400.00
17	Systematic	Repair	15	1,950.00
18	Forward	Automation	150	36.00
19	Outward	Chemical	300	48.00
20	Onward	Chemical	200	62.50
21	Leader	Facility	65	85.00
22	Systematic	Repair	50	75.50
23	Forward	Automation	750	6.75
24	Outward	Chemical	650	9.25
25	Onward	Chemical	900	75.95

Assignment:

Complete the following Analyses and answer the two questions.

ABC Analysis by Annual Dollar Amount

Step 1:

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

Step 2:

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

Step 3:

Calculate the cumulative annual dollar usage

Step 4:

Calculate the percentage usage

Step 5:

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

Step 1:

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

Step 2:

Sort the items according to supplier

Step 3:

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

Step 4:

Click on 2 so that it only shows the subtotals.

Step 5:

Sort the Annual $ Usage subtotals from Largest to smallest

Step 5:

Calculate the cumulative annual dollar usage for each supplier

Step 6:

Calculate the percentage usage

Step 7:

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:

Step 1:

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

Step 2:

Sort the items according to category

Step 3:

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

Step 4:

Click on 2 so that it only shows the subtotals.

Step 5:

Sort the Annual Usage subtotals from largest to smallest

Step 6:

Calculate the cumulative annual dollar usage for each category

Step 7:

Calculate the percentage usage

Step 8:

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?