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

Solution :

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