Question

In: Operations Management

A small toy store has organized its 10 inventory items on an annual dollar-volume basis. The...

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

Solutions

Expert Solution

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.

  • A: 30% of items (i.e. 3 items) with the top 56% contribution
  • B: 40% of items (i.e. next 4 items) with the next 40% contribution
  • C: 30% of items (i.e. next 3 items) with the rest 4% contribution

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%

Related Solutions

A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05...
A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05 each. During the year the toy store purchased 2 at $1.40; 4 at $2.00; 6 at $3.00; and 20 at $4.00. By the end of the year, 25 balls were sold. Calculate the following: the number of plastic bouncy balls in stock at the end of the year, the cost of ending inventory under LIFO, the cost of ending inventory under FIFO, and the...
A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05...
A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05 each. During the year the toy store purchased 2 at $1.40; 4 at $2.00; 6 at $3.00; and 20 at $4.00. By the end of the year, 25 balls were sold. Calculate the following: the number of plastic bouncy balls in stock at the end of the year, the cost of ending inventory under LIFO, the cost of ending inventory under FIFO, and the...
A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05...
A toy store has beginning inventory of 20 plastic bouncy balls at a cost of $1.05 each. During the year the toy store purchased 2 at $1.40; 4 at $2.00; 6 at $3.00; and 20 at $4.00. By the end of the year, 25 balls were sold. Calculate the following: the number of plastic bouncy balls in stock at the end of the year, the cost of ending inventory under LIFO, the cost of ending inventory under FIFO, and the...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,000 items in its inventory...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,000 items in its inventory and has hired Joan Blasco Paul to manage its inventory. Joan has determined that 11% of the items in inventory are A items, 37% are B items, and 52% are C items. She would like to set up a system in which all A items are counted monthly (every 19 working days), all B items are counted quarterly (every 61 working days), and all...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory.
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 11% of the items in inventory are A items, 30% are B items, and 59% are C items. She would like to set up a system in which all A items are counted monthly (every 21 working days), all B items are counted quarterly (every 60 working days), and all C...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,200 items in its inventory and has hired Joan Blasco-Paul to manage its inventory.
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,200 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 11% of the items in inventory are A items, 35% are B items, and 54% are C items. She would like to set up a system in which all A items are counted monthly (every 22 working days), all B items are counted quarterly (every 61 working days), and all C...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory.
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 10% of the items in inventory are A items, 30% are B items, and 60% are C items. She would like to set up a system in which all A items are counted monthly (every 19 working days), all B items are counted quarterly (every 62 working days), and all C...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,500 items in its inventory and has hired Joan Blasco-Paul to manage its inventory
 Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,500 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 11% of the items in inventory are A items, 32% are B items, and 57% are C items. She would like to set up a system in which all A items are counted monthly (every 18 working days), all B items are counted quarterly (every 59 working days), and all C...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory.
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,400 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 12% of the items in inventory are A items, 32% are B items, and 56% are C items. She would like to set up a system in which all A items are counted monthly (every 20 working days), all B items are counted quarterly (every 61 working days), and all C...
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 6,800 items in its inventory and has hired Joan Blasco-Paul to manage its inventory.
Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 6,800 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 8% of the items in inventory are A items, 40% are B items, and 52% are C items. She would like to set up a system in which all A items are counted monthly (every 22 working days), all B items are counted quarterly (every 62 working days), and all C...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT