Question

A TV manufacturing company uses speakers at the rate of 8000/mo. When it places an order for speakers it incurs a fixed cost of $1200. The monthly interest rate for keeping a speaker in stock is assessed at 1%/mo. The cost of the speaker depends on the order size. If less than 1000 speakers are ordered the cost is $11 each. When the order size is between 1000 and 10,000 the cost is $10.50/unit. For order sizes between 10,000 and 30,000 the cost is $10 per unit. For order quantities between 30,000 and 80,000 the cost drops to $9.50. Beyond 80,000 the cost is $9.25. Determine the optimum order size and time between orders if shortages are not allowed.

Please use excel to solve this problem. Show all cell formulas and parameters used.

Answer 1

1. Ordering quantity 500 1000 10000 30000 80000

2. No. of orders 16 8 0.8 0.27 0.1

3. Average inventory 250 500 5000 15000 40000

4. Value of average 2750 5250 50000 142500 370000

inventory

5. Monthly total cost

a). Cost of material 88000 84000 80000 760000 740000

b). Ordering cost 19200 9600 960 320 120

c). Carrying cost 27.5 52.5 500 1425 3700

Total monthly cost 107227.5 93652.5 81460 77745 77820

Among the total monthly cost, $ 77,745 is the least cost.

Therefore, the optimum order size of quantity = 30,000

The number of orders per month = 8000/30000 = 0.267

Time between two consecutive orders = 30000/8000 = 3.75 months