Please include all excel formulas and calculations A manufacturing operation mus
ID: 329848 • Letter: P
Question
Please include all excel formulas and calculations
A manufacturing operation must periodically purchase bulk quantities of washers. The washers are purchased in boxes of 1000 and are consumed at a constant rate. The operation expects to purchase 20,000 boxes over the coming year. Each box costs $120, the annual holding cost per box is $15, and the cost of placing an order is $120 (regardless of the quantity ordered). For the following questions, use the basic economic order quantity model (without quantity discounts).
1. What is the economic order quantity (in boxes)? (2pts)
2. Calculate the annual inventory holding costs based on the average inventory level and annual holding cost per box. (2pts)
3. Calculate the annual inventory ordering costs based on the number of orders expected to be placed during the coming year. (2pts)
4. Create a data table showing the total inventory costs (only) for order quantities varying from 100 to 1200 (use a step size of 50). You must use a data table structure to receive full credit for this problem. If you have any doubt as to what a data table is, please ask your lab TA. (8pts)
5. Create a scatter chart (use the one with markers and smooth lines) showing how total inventory costs are a function of the order quantity. Be sure to label your axes appropriately. (6pts)
Explanation / Answer
Please find below answers to first 4 questions :
Answer to question 1 :
Given are the following data:
Annual demand = D = 20,000 boxes
Order placement cost = Co = $120
Annual holding cost per box = Ch = $15
Economic order quantity ( EOQ )
= Square root ( 2 x Co x D / Ch )
= Square root ( 2 x 120 x 20,000/15)
= 565.68 ( 566 rounded to nearest whole number )
ECONOMIC ORDER QUANTITY = 566 BOXES
Answer to question 2 :
Annual inventory holding cost
= annual unit holding cost x average inventory
= Ch x EOQ/2
= $15 X 566/2
= $4245
Answer to question 3 :
Annual ordering cost
= Order placement cost x number of orders
= Co x annual demand / EOQ
= 120 x 20,000/566
= $ 4240.28
Answer to question 4 :
Following formula have been used for preparation of below data table:
Annual ordering cost = Ordering cost x Annual demand /Order quantity = $ 120 x 20000/ Annual ordering cost = $2400000/Annual ordering cost
Annual holding cost = Annual unit holding cost x Order quantity/2 = $15 x Order quantity /2 = $7.5 x Order Quantity
Total inventory cost = Annual ordering cost + annual holding cost
Order quantity
Annual ordering cost ( $)
Annual holding cost ( $)
Total inventory cost ( $)
100
24000.00
750
24750.00
150
16000.00
1125
17125.00
200
12000.00
1500
13500.00
250
9600.00
1875
11475.00
300
8000.00
2250
10250.00
350
6857.14
2625
9482.14
400
6000.00
3000
9000.00
450
5333.33
3375
8708.33
500
4800.00
3750
8550.00
550
4363.64
4125
8488.64
600
4000.00
4500
8500.00
650
3692.31
4875
8567.31
700
3428.57
5250
8678.57
750
3200.00
5625
8825.00
800
3000.00
6000
9000.00
850
2823.53
6375
9198.53
900
2666.67
6750
9416.67
950
2526.32
7125
9651.32
1000
2400.00
7500
9900.00
1050
2285.71
7875
10160.71
1100
2181.82
8250
10431.82
1150
2086.96
8625
10711.96
1200
2000.00
9000
11000.00
ECONOMIC ORDER QUANTITY = 566 BOXES