In: Operations Management
A small toy store has organized its 10 inventory items on an annual dollar-volume basis. The information below shows the items, their annual demands, and unit costs. How should the store classify these items into groups A, B, and C?
Item Number Annual Volume (Units) Unit Cost ($)
Item 1 300 $10
Item 2 1000 $30
Item 3 500 $60
Item 4 100 $2
Item 5 1500 $20
Item 6 600 $50
Item 7 2000 $1.50
Item 8 900 $70
Item 9 1200 $2.00
Item 10 700 $40
Please use the Excel Solver to solve the above exercise question
Step-1: Multiply the Annual Volume (Units) and the Unit Cost ($) to get the Annual Usage in $
Item Number | Annual Volume (Units) | Unit Cost ($) | Annual usage in $ |
Item 1 | 300 | $10.0 | $3,000.0 |
Item 2 | 1000 | $30.0 | $30,000.0 |
Item 3 | 500 | $60.0 | $30,000.0 |
Item 4 | 100 | $2.0 | $200.0 |
Item 5 | 1500 | $20.0 | $30,000.0 |
Item 6 | 600 | $50.0 | $30,000.0 |
Item 7 | 2000 | $1.5 | $3,000.0 |
Item 8 | 900 | $70.0 | $63,000.0 |
Item 9 | 1200 | $2.0 | $2,400.0 |
Item 10 | 700 | $40.0 | $28,000.0 |
Calculations:
Step-2: Sort the items as per the ascending order of Annual Usage in $
Output:
Item Number | Annual Volume (Units) | Unit Cost ($) | Annual usage in $ |
Item 8 | 900 | $70.0 | $63,000.0 |
Item 2 | 1000 | $30.0 | $30,000.0 |
Item 3 | 500 | $60.0 | $30,000.0 |
Item 5 | 1500 | $20.0 | $30,000.0 |
Item 6 | 600 | $50.0 | $30,000.0 |
Item 10 | 700 | $40.0 | $28,000.0 |
Item 1 | 300 | $10.0 | $3,000.0 |
Item 7 | 2000 | $1.5 | $3,000.0 |
Item 9 | 1200 | $2.0 | $2,400.0 |
Item 4 | 100 | $2.0 | $200.0 |
Step-3: Compute the percentage of dollar usage as follows:
Item Number | Annual Volume (Units) | Unit Cost ($) | Annual usage in $ | Percent of dollar usage |
Item 8 | 900 | $70.0 | $63,000 | 28.7% |
Item 2 | 1000 | $30.0 | $30,000 | 13.7% |
Item 3 | 500 | $60.0 | $30,000 | 13.7% |
Item 5 | 1500 | $20.0 | $30,000 | 13.7% |
Item 6 | 600 | $50.0 | $30,000 | 13.7% |
Item 10 | 700 | $40.0 | $28,000 | 12.8% |
Item 1 | 300 | $10.0 | $3,000 | 1.4% |
Item 7 | 2000 | $1.5 | $3,000 | 1.4% |
Item 9 | 1200 | $2.0 | $2,400 | 1.1% |
Item 4 | 100 | $2.0 | $200 | 0.1% |
Total | $219,600 | 100% |
Calculations:
Step-4: Assign A, B, and C class, We have used the following scheme.
However, you can choose your own scheme.
Item Number | Annual Volume (Units) | Unit Cost ($) | Annual usage in $ | Percent of dollar usage | Category |
Item 8 | 900 | $70.0 | $63,000 | 28.7% | A |
Item 2 | 1000 | $30.0 | $30,000 | 13.7% | A |
Item 3 | 500 | $60.0 | $30,000 | 13.7% | A |
Item 5 | 1500 | $20.0 | $30,000 | 13.7% | B |
Item 6 | 600 | $50.0 | $30,000 | 13.7% | B |
Item 10 | 700 | $40.0 | $28,000 | 12.8% | B |
Item 1 | 300 | $10.0 | $3,000 | 1.4% | C |
Item 7 | 2000 | $1.5 | $3,000 | 1.4% | C |
Item 9 | 1200 | $2.0 | $2,400 | 1.1% | C |
Item 4 | 100 | $2.0 | $200 | 0.1% | C |
Total | $219,600 | 100% |