90.2K

Verified Solution

Link Copied!

Spend Analysis is defined as an annual review of the organizations total purchases, which provides answers to a series of pertinent questions. In this assignment you are required to analysis the data provided below and develop a series of charts and explanations as to the meaning of the data analyzed. This analysis is often referred to as an ABC Analysis. You are required to produce this in an Excel Spreadsheet Part Supplier Category Annual Unit Price Number Usage 1 Smithson Mechanical 1,000 $10.00 2 Robertson Stationary 10,000 0.75 3 Tennant Production 500 2.50 4 Drvell 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 Drvell MRO 38 525.00 15 Minline Electrical 650 3.55 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 Analysis and answer the two questions. A) 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 B) 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 C) 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: 1.For each of the three analyses done above, what do they tell you as the buyer for that company? Answer: A) By Annual Dollar Amount: B) By Supplier: C) By Category: 2. What can be done with this ABC Analysis information to improve your purchase process in the future

Answer & Explanation Solved by verified expert

See Answer