EuroWatch Company assembles expensive wristwatches and thensells them to retailers throughout Europe. The watches areassembled at a plant with two assembly lines. These lines areintended to be identical, but line 1 uses somewhat older equipmentthan line 2 and is typically less reliable. Historical data haveshown that each watch coming off line 1, independently of theothers, is free of defects with probability 0.98. The similarprobability for line 2 is 0.99. Each line produces 500 watches perhour. The production manager has asked you to answer the followingquestions.
Finally, EuroWatch has a third order for 100 watches. Thecustomer has agreed to pay $50,000 for the order—that is, $500 perwatch. If EuroWatch sends more than 100 watches to the customer,its revenue doesn’t increase; it can never exceed $50,000. Its unitcost of producing a watch is $450, regardless of which line it isassembled on. The order will be filled entirely from a single line,and EuroWatch plans to send slightly more than 100 watches to thecustomer.
If the customer opens the shipment and finds that there arefewer than 100 defect-free watches (which we assume the customerhas the ability to do), then he will pay only for the defect-freewatches—EuroWatch’s revenue will decrease by $500 per watch shortof the 100 required—and on top of this, EuroWatch will be requiredto make up the difference at an expedited cost of $1000 per watch.The customer won’t pay a dime for these expedited watches. (Ifexpediting is required, EuroWatch will make sure that the expeditedwatches are defect-free. It doesn’t want to lose this customerentirely.)
You have been asked to develop a spreadsheet model to findEuroWatch’s expected profit for any number of watches it sends tothe customer. You should develop it so that it responds correctly,regardless of which assembly line is used to fill the order andwhat the shipment quantity is. (Hints: Use the BINOM.DISTfunction, with last argument 0, to fill up a column ofprobabilities for each possible number of defective watches. Nextto each of these, calculate EuroWatch’s profit. Then use asUMPRODUCT to obtain the expected profit. Finally, you can assumethat EuroWatch will never send more than 110 watches. It turns outthat this large a shipment is not even close to optimal.)
