Question

Ray’s Satellite Emporium wishes to determine the best order size for its best-selling satellite dish (Model TS111). Ray has estimated the annual demand for this model at 1,000 units. His cost to carry one unit is $10 per year per unit, and he has estimated that each order costs $25 to place. The purchase cost is $500 per unit.

For the order quantities Q = {25, 50, 75, 100, 125, 150}, find the annual setup ordering cost (S*D/Q), holding cost (H*Q/2), purchasing cost (C*D), and the total annual cost (including the purchase cost) Report the results for order quantity 25 in Answersheet (3 points).

Report the Q value with the minimum total cost (select from the list in cell B12) and the corresponding total cost in Answersheet (2 points).

Answer 1

To be calculated:

For each given order quantities, Q = {25, 50, 75, 100, 125, 150}

Annual Setup cost

Holding cost

Purchasing cost

Total annual cost

Given values:

Annual demand = 1,000 units

Holding cost = $10 per year per unit

Setup or Ordering cost, S = $25

Purchase cost, C = $500 per unit

Solution:

(a) Order quantity, Q = 25

Annual Setup cost (S*D/Q) = 25 x (1000 / 25) = $1000

Holding cost (H*Q/2) = (10 x 25) / 2 = $125

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $1000 + $125 + $500,000

Total annual cost = $501,125

(b) Order quantity, Q = 50

Annual Setup cost (S*D/Q) = 25 x (1000 / 50) = $500

Holding cost (H*Q/2) = (10 x 50) / 2 = $250

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $500 + $250 + $500,000

Total annual cost = $500,750

(c) Order quantity, Q = 75

Annual Setup cost (S*D/Q) = 25 x (1000 / 75) = $333.33

Holding cost (H*Q/2) = (10 x 75) / 2 = $375

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $333.33 + $375 + $500,000

Total annual cost = $500,708.33

(d) Order quantity, Q = 100

Annual Setup cost (S*D/Q) = 25 x (1000 / 100) = $250

Holding cost (H*Q/2) = (10 x 100) / 2 = $500

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $250 + $500 + $500,000

Total annual cost = $500,750

(e) Order quantity, Q = 125

Annual Setup cost (S*D/Q) = 25 x (1000 / 125) = $200

Holding cost (H*Q/2) = (10 x 125) / 2 = $625

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $200 + $625 + $500,000

Total annual cost = $500,825

(f) Order quantity, Q = 150

Annual Setup cost (S*D/Q) = 25 x (1000 / 150) = $166.67

Holding cost (H*Q/2) = (10 x 150) / 2 = $750

Purchasing cost (C*D) = 500 x 1000 = $500,000

Total annual cost = Annual Setup cost + Holding cost + Purchasing cost

Total annual cost = $166.67 + $750 + $500,000

Total annual cost = $500,916.67

Answer:

Order quantity with minimum total cost, Q = 75

Minimum total cost = $500,708.33