Here is another one for you Nancy Carol2

profilemarhal5
ex._11.1_and_11.6_templates.xls
Home>Business & Finance homework help>Operations Management homework help>Here is another one for you Nancy Carol2

EOQ 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
Excel template for Exercises 11.1 and 11.6
Note on using the Excel templates: Use the automated Excel tables in Sheets 1 and 3 to perform calculations, and Sheets 2 and 4 to record your answers. For an explanation of the automated Excel tables, refer to Quantitative Methods, pp. 281–284. Save the template to your desktop before using. Make sure macros are enabled.
Any existing numbers in green cells in the automated tables are for examples, and show where you should enter the data. You must delete or overwrite these numbers to perform the calculations. The yellow cells of templates are protected to prevent users from accidentally deleting or overwriting the formulas. There is no need to unprotect the templates.
&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.
B
A

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.