Answer Questions 1 & 2:
- For each of the three analyses done below, what do they tell you as the buyer for that company?
Answer:
- By annual dollar amount
- By supplier
- By category
- What can be done with this ABC analysis information to improve your purchase process in the future?
Here the Excel Spreadsheet:
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 Part Number Supplier Category Annual Usage Unit Price Annual Dollar Usages 96 dollar Usages Cummulative 96 Classification 7 Systematic Repair 75 1,525.00 $114,375.00 30.47 30.47 A 25 Onward Chemical 900 75.9 $68,310.00 18.20 48.67 A 17 Systematic Repair 15 1,950.00 $29,250.00 7.79 56.46 A 16 Leader Facility 12 2,400.00 $28,800.00 7.67 64.13 A 14 Dryell MRO 38 525 $19,950.00 5.31 69.45 A 19 Outward 300 48 $14,400.00 3.84 73.28 A 20 Onward Chemical Chemical Facility 200 62.5 $12,500.00 3.33 76.61 A 6 Leader 25 450 3.00 79.61 A 10 Onward Chemical 12 850 272 82.33 B 1 $10.00 $11,250.00 $10,200.00 $10,000.00 $7,500.00 $7,500.00 266 84.99 B 2 0.75 200 86.99 B 8 750 200 88.99 B 24 9.25 $6,012.50 1.60 90.59 B 21 85 $5,525.00 1.47 92.06 B 18 150 36 $5,400.00 144 93.50 B 23 6.75 $5,062.50 1.35 94.85 B 12 Smithson Mechanical 1,000 Robertson Stationary 10,000 Forward Automation 10 Outward Chemical 650 Leader Facility 65 Forward Automation Forward Automation 750 Robertson Stationary 5,000 Systematic Repair 50 Outward Chemical 2,500 Minline Electrical 650 Smithson Mechanical 750 Tennant Production 500 Dryell MRO 100 Minline Electrical 250 1 $5,000.00 1.33 96.18 C 22 75.5 $3,775.00 1.01 97.19 9 125 $3,125.00 0.83 98.02 C 15 3.55 0.61 98.63 C $2,307.50 $1.875.00 11 2.5 0.50 99.13 3 2.5 $1,250.00 0.33 99.47 C 4 9 $900.00 0.24 99.71 5 3 S750.00 0.20 99.91 13 Tennant Production 375 0.95 $356.25 0.09 100.00 Sum $375,373.75 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 Part Number Supplier Category Annual Usage Unit Price Annual Dollar Usages 96 dollar Usages Cummulative 96 Classification 7 Systematic Repair 75 1,525.00 $114,375.00 30.47 30.47 A 25 Onward Chemical 900 75.9 $68,310.00 18.20 48.67 A 17 Systematic Repair 15 1,950.00 $29,250.00 7.79 56.46 A 16 Leader Facility 12 2,400.00 $28,800.00 7.67 64.13 A 14 Dryell MRO 38 525 $19,950.00 5.31 69.45 A 19 Outward 300 48 $14,400.00 3.84 73.28 A 20 Onward Chemical Chemical Facility 200 62.5 $12,500.00 3.33 76.61 A 6 Leader 25 450 3.00 79.61 A 10 Onward Chemical 12 850 272 82.33 B 1 $10.00 $11,250.00 $10,200.00 $10,000.00 $7,500.00 $7,500.00 266 84.99 B 2 0.75 200 86.99 B 8 750 200 88.99 B 24 9.25 $6,012.50 1.60 90.59 B 21 85 $5,525.00 1.47 92.06 B 18 150 36 $5,400.00 144 93.50 B 23 6.75 $5,062.50 1.35 94.85 B 12 Smithson Mechanical 1,000 Robertson Stationary 10,000 Forward Automation 10 Outward Chemical 650 Leader Facility 65 Forward Automation Forward Automation 750 Robertson Stationary 5,000 Systematic Repair 50 Outward Chemical 2,500 Minline Electrical 650 Smithson Mechanical 750 Tennant Production 500 Dryell MRO 100 Minline Electrical 250 1 $5,000.00 1.33 96.18 C 22 75.5 $3,775.00 1.01 97.19 9 125 $3,125.00 0.83 98.02 C 15 3.55 0.61 98.63 C $2,307.50 $1.875.00 11 2.5 0.50 99.13 3 2.5 $1,250.00 0.33 99.47 C 4 9 $900.00 0.24 99.71 5 3 S750.00 0.20 99.91 13 Tennant Production 375 0.95 $356.25 0.09 100.00 Sum $375,373.75
