Week 6 exercise
canmic
ex._11.1_and_11.6_templates.xlsEOQ template
| Economic Ordering Quantity (EOQ) Model | ||
| Demand [D] (week or year) | 800 | |
| Ordering Cost [S] | 30 | |
| Carrying Cost/item [H]* | 50 | |
| Price of the Item [p] | 250 | |
| EOQ = | 31 | |
| Order Cycle = | 25.8 | times (in a week or year) |
| Average Inventory = | 15 | |
| Holding Cost = | $774.60 | |
| Carrying Cost = | $774.60 | |
| Total Cost = | $1,549.19 | |
| Investment Cost = | $7,745.97 | (If price of item is known) |
| * must be calculated as same period length of Demand. |
EX11.1 Answers
| EX11.1 |
| a. Economic Order Quantity |
| b. Length of an Order Cycle |
| c. Total Average Weekly Costs |
| d. Investment Costs |
| e. EOQ if ordering costs increased 50% |
| f. Reorder Point with no safety stock |
| g. Reorder Point if 1000 units safety stock |
&RCarrie Willetts
HADE
problem set 3
Multi Item EOQ & ABC template
| Multi-Item Inventory - EOQ and ABC Classification | ||||||||||||||||
| Total | Total | Percentage | ||||||||||||||
| Annual | Item | Cost of | Length of | Annual | of Total | |||||||||||
| Annual | Item Price/ | Carrying | Ordering | Carrying | Carrying | Ordering | Inventory | Order Cycle | Investment | Costs | Annual | ABC | ||||
| Item No. | Demand | Unit Cost | Rate | Cost | Cost | EOQ | Cost | Cost | Management | in Days | Cost | (Volume) | Cost | Class* | ||
| 1 | 104 | 2225.00 | 12% | 6 | 267.00 | 2.2 | 288.62 | 288.62 | 577.25 | 8 | 4810.41 | 231400.00 | 2.94% | B | ||
| 2 | 260 | 5000.00 | 10% | 5 | 500.00 | 2.3 | 570.09 | 570.09 | 1140.18 | 3 | 11401.75 | 1300000.00 | 16.50% | A | ||
| 3 | 728 | 3550.00 | 8% | 12 | 284.00 | 7.8 | 1113.78 | 1113.78 | 2227.57 | 4 | 27844.57 | 2584400.00 | 32.80% | A | ||
| 4 | 1248 | 3.50 | 12% | 28 | 0.42 | 407.9 | 85.66 | 85.66 | 171.33 | 119 | 1427.73 | 4368.00 | 0.06% | C | ||
| 5 | 13000 | 1.75 | 18% | 18 | 0.32 | 1218.9 | 191.98 | 191.98 | 383.95 | 34 | 2133.07 | 22750.00 | 0.29% | C | ||
| 6 | 790 | 1290.00 | 2% | 35 | 25.80 | 46.3 | 597.23 | 597.23 | 1194.46 | 21 | 59723.11 | 1019100.00 | 12.93% | B | ||
| 7 | 78000 | 2.25 | 14% | 10 | 0.32 | 2225.4 | 350.50 | 350.50 | 701.00 | 10 | 5007.14 | 175500.00 | 2.23% | B | ||
| 8 | 114400 | 0.65 | 17% | 6 | 0.11 | 3524.7 | 194.74 | 194.74 | 389.48 | 11 | 2291.06 | 74360.00 | 0.94% | C | ||
| 9 | 66040 | 0.95 | 21% | 5 | 0.20 | 1819.4 | 181.49 | 181.49 | 362.97 | 10 | 1728.45 | 62738.00 | 0.80% | C | ||
| 10 | 6240 | 12.50 | 12% | 12 | 1.50 | 316.0 | 236.98 | 236.98 | 473.96 | 18 | 3949.68 | 78000.00 | 0.99% | C | ||
| 11 | 11440 | 2.00 | 15% | 28 | 0.30 | 1461.3 | 219.20 | 219.20 | 438.40 | 47 | 2922.65 | 22880.00 | 0.29% | C | ||
| 12 | 18200 | 1.50 | 14% | 18 | 0.21 | 1766.4 | 185.47 | 185.47 | 370.93 | 35 | 2649.53 | 27300.00 | 0.35% | C | ||
| 13 | 910 | 1300.00 | 2% | 25 | 26.00 | 41.8 | 543.83 | 543.83 | 1087.66 | 17 | 54382.90 | 1183000.00 | 15.01% | A | ||
| 14 | 315 | 2700.00 | 2% | 50 | 54.00 | 24.2 | 652.11 | 652.11 | 1304.22 | 28 | 65211.20 | 850500.00 | 10.79% | B | ||
| 15 | 65000 | 3.75 | 22% | 19 | 0.83 | 1730.3 | 713.75 | 713.75 | 1427.50 | 10 | 6488.63 | 243750.00 | 3.09% | B | ||
| Overall Total | $ 12,250.86 | $ 251,971.86 | $ 7,880,046.00 | |||||||||||||
| * Assumes >15% of dollar volume is A , < 1% is C item | ||||||||||||||||
| Investment cost represents the money spent to acquire the items or what has been paid for the inventory in stock. | ||||||||||||||||
| These costs are recovered once items are used, and reinvested in another cycle of ordering. | ||||||||||||||||
| The inventory management costs are the costs involved in managing, maintaining and reordering the | ||||||||||||||||
| stock of items. It includes the holding (carrying) costs (interest, insurance | ||||||||||||||||
| obsolescence,deterioration, spoilage, pilfering and breakage) and order (administrative) costs. |
EX11.6 Answers
| EX11.6 | ||||||||||||||||
| Multi-Item Inventory - EOQ and ABC Classification | ||||||||||||||||
| Total | Total | Percentage | ||||||||||||||
| Annual | Item | Cost of | Length of | Annual | of Total | |||||||||||
| Annual | Item Price/ | Carrying | Ordering | Carrying | Carrying | Ordering | Inventory | Order Cycle | Investment | Costs | Annual | ABC | ||||
| Item No. | Demand | Unit cost | Rate | Cost | Cost | EOQ | Cost | Cost | Management | in Days | Cost | (Volume) | Cost | Class* | ||
| 1 | 104 | $2,225.00 | 0.12 | $6.00 | ||||||||||||
| 2 | 260 | $5,000.00 | 0.1 | $5.00 | ||||||||||||
| 3 | 728 | $3,550.00 | 0.08 | $12.00 | ||||||||||||
| 4 | 1248 | $1,205.00 | 0.12 | $28.00 | ||||||||||||
| 5 | 104 | $11,100.00 | 0.02 | $18.00 | ||||||||||||
| 6 | 1040 | $1,500.00 | 0.2 | $32.00 | ||||||||||||
| 7 | 780 | $1,900.00 | 0.11 | $50.00 | ||||||||||||
| 8 | 884 | $3,700.00 | 0.09 | $12.00 | ||||||||||||
| 9 | 780 | $6,400.00 | 0.02 | $35.00 | ||||||||||||
| 10 | 520 | $2,700.00 | 0.05 | $12.00 | ||||||||||||
| Overall Total | ||||||||||||||||
| * Assumes >15% of dollar volume is A , < 1% is C item | ||||||||||||||||
| Investment cost represents the money spent to acquire the items or what has been paid for the inventory in stock. | ||||||||||||||||
| These costs are recovered once items are used, and reinvested in another cycle of ordering. | ||||||||||||||||
| The inventory management costs are the costs involved in managing, maintaining and reordering the | ||||||||||||||||
| stock of items. It includes the holding (carrying) costs (interest, insurance | ||||||||||||||||
| obsolescence,deterioration, spoilage, pilfering and breakage.) and order (administrative) costs. |
