Question

EuroWatch Company assembles expensive wristwatches and then sells them to retailers throughout Europe. The watches are assembled at a plant with two assembly lines. These lines are intended to be identical, but line 1 uses somewhat older equipment than line 2 and is typically less reliable. Historical data have shown that each watch coming off line 1, independently of the others, is free of defects with probability 0.98. The similar probability for line 2 is 0.99. Each line produces 500 watches per hour. The production manager has asked you to answer the following questions.

1. Finally, EuroWatch has a third order for 100 watches. The customer has agreed to pay $50,000 for the order—that is, $500 per watch. If EuroWatch sends more than 100 watches to the customer, its revenue doesn’t increase; it can never exceed $50,000. Its unit cost of producing a watch is $450, regardless of which line it is assembled on. The order will be filled entirely from a single line, and EuroWatch plans to send slightly more than 100 watches to the customer.

2. If the customer opens the shipment and finds that there are fewer than 100 defect-free watches (which we assume the customer has the ability to do), then he will pay only for the defect-free watches—EuroWatch’s revenue will decrease by $500 per watch short of the 100 required—and on top of this, EuroWatch will be required to make up the difference at an expedited cost of $1000 per watch. The customer won’t pay a dime for these expedited watches. (If expediting is required, EuroWatch will make sure that the expedited watches are defect-free. It doesn’t want to lose this customer entirely.)

3. You have been asked to develop a spreadsheet model to find EuroWatch’s expected profit for any number of watches it sends to the customer. You should develop it so that it responds correctly, regardless of which assembly line is used to fill the order and what the shipment quantity is. (Hints: Use the BINOM.DIST function, with last argument 0, to fill up a column of probabilities for each possible number of defective watches. Next to each of these, calculate EuroWatch’s profit. Then use a sUMPRODUCT to obtain the expected profit. Finally, you can assume that EuroWatch will never send more than 110 watches. It turns out that this large a shipment is not even close to optimal.)

Answer 1

Solution

Let

D = number of watches despatched to the customer.

X = expected number of good watches in the despatched batch

Then, X = 0.98D for Line 1 and 0.99D for Line 2 ........................................................................................(1)

Y = expected number of expedited watches in the despatched batch

Then, Y = (100 - 0.98D) if 0.98D < 100 for Line 1 and (100 - 0.98D) if 0.99D < 1000 for Line 2 ................(1a)

For every good watch, profit per watch = 500 – 450 = 50 .............................................................................(2)

For every expedited watch, profit per watch = - 10000 (i.e., loss) ................................................................(3)

So, expected frofit = 5000 if x ≥ 100 irrespevctive of the line.......................................................................(4)  

= (50 x 0.98D) – 1000(100 – 0.98D) = 1029D - 100000 for Line 1, when x < 100 ......................................(4a)

= (50 x 0.99D) – 1000(100 – 0.99D) = 1039.5D - 100000 for Line 2 .........................................................(4b)

D	X		Y		Expected Profit
	Line 1	Line 2	Line 1	Line 2	Line 1	Line 2
100	98	99	2	1	2900.00	3950.00
101	98.98	99.99	2	1	3929.00	4989.50
102	99.96	100.98	2	1	4958.00	5000.00
103	100.94	101.97	2	1	5000.00	5000.00
104	101.92	102.96	2	1	5000.00	5000.00
105	102.9	103.95	2	1	5000.00	5000.00
106	103.88	104.94	2	1	5000.00	5000.00
107	104.86	105.93	2	1	5000.00	5000.00
108	105.84	106.92	2	1	5000.00	5000.00
109	106.82	107.91	2	1	5000.00	5000.00
110	107.8	108.9	2	1	5000.00	5000.00

So,

irrespective of the Line, the profit is maximum when 103 watches are despatched to the customer. Answer

DONE