Question

The annual demand for a certain product sold in a department store is 1,000 units. Ordering cost per order is $50, holding rate is 40%, and unit cost is $200. The department store currently is ordering every two months, but now is offered the following quantity discount scheme: For an order of up to ‘L-1’ units no discount. For an order from L units up to 299 units-a discount of 25%, and for a larger order - a discount of 30% . As mentioned, the discount break points are: 1, ‘L’, and 300. The total cost for Level 1 (that starts at L and ends at 299) is 153500 dollars

1.

a. As mentioned, the discount break points are: 1, ‘L’, and 300. The total cost for Level 1 (that starts at L and ends at 299) is 153500 dollars. Find ‘L’. Use the template and Data Table to find L (this is a one dimensional Data Table). Start at L=25 with increments of 25. You can also use Goal Seek instead of Data Table. If you use Data Table, show it in the Answer sheet

b. Would the store qualify for any discount under the current ‘2-month ordering’ policy? Assume the discount break points are 1, 200, and 300.

Answer 1

Annual demand, D = 1000 units

Order cost, S = $ 50

Holding cost rate, i = 40%

a)

For Level 1, discount is 25%,

So, effective unit price = 200*(1-25%) = $ 150

Holding cost, H = 150*0.4 = $ 60

Total annual cost = Purchase cost + Holding cost + Ordering cost

= D*c + H*Q/2 + S*D/Q

= 1000*150 + 60*Q/2 + 50*1000/Q

Equating this to 153,500

1000*150 + 60*Q/2 + 50*1000/Q = 153500

30Q+50000/Q = 3500

30Q²-3500Q+50000 = 0

This is a quadratic equation, we can either use Goal seek function in excel or using algebraic formula.

There are two values of Q which satisfy this quadratic equation,

Q = (-(-3500)+sqrt(3500^2-4*30*50000))/(2*30) and  (-(-3500)-sqrt(3500^2-4*30*50000))/(2*30)

Q = 100  and 16.67

[NOTE:16.67 is not feasible, because EOQ is greater than that. EOQ = sqrt(2*1000*50/60) = 41]

Therefore, value of L = 100

---------------------------------------------------------------

b)

Order Quantity under current 2-month ordering policy = 2*1000/12 = 167

This quantity is between 100 and 299

Therefore, store would qualify for 25 % discount