Decision Analysis

Sheet1

ORDERING NCAA T-SHIRTS It is March, and the annual NCAA Basketball Tournament is down to the final four teams. Randy Kitchell is a T-shirt vendor who plans to order T-shirts with the names of the final four teams from a manufacturer and then sell them to the fans. The fixed cost of any order is $750, the variable cost per T-shirt to Randy is $8, and Randy’s selling price is $18. However, this price will be charged only until a week after the tournament. After that time, Randy figures that interest in the T-shirts will be low, so he plans to sell all remaining T-shirts, if any, at $6 each. His best guess is that demand for the T-shirts during the full-price period will be 1500. He is thinking about ordering 1450 T-shirts, but he wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity. How should he proceed? Objective To build a spreadsheet model in a series of stages, with all stages being correct but each stage being more readable and flexible than the previous stages.

Sheet2

BREAKEVEN ANALYSIS AT QUALITY SWEATERS The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: 1. How does a change in the response rate affect profit? 2. For what response rate does the company break even? Objective To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools.

0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 -37200 -29400 -21600 -13800 -6000 1800 9600.0000000000582 17400 25200 33000 40800 48600 56400 64200.000000000058

Sheet3

CALCULATING NPV AT ACRON Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Niagra, is worth pursuing. Niagra is in the final stages of development and will be ready to enter the market one year from now. The final cost of development, to be incurred at the beginning of year 1, is $15 million. Acron estimates that the demand for Niagra will gradually grow and then decline over its useful lifetime of 20 years. Specifically, the company expects its gross margin (revenue minus cost) to be $1.5 million in year 1, then to increase at an annual rate of 6% through year 8, and finally to decrease at an annual rate of 5% through year 20. Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the ends of the respective years. (To simplify the model, taxes are ignored.) Using an annual discount rate of 7.5% for the purpose of calculating NPV, the drug company wants to answer the following questions: 1. Is the drug worth pursuing, or should Acron abandon it now and not incur the $15 million development cost? 2. How do changes in the model inputs change the answer to question 1?

Sheet4

We are producing washers and dryers. For the production each machine requires some processing hours in Production, Assembly, and Packaging departments. For each washer and dryer required hours are given as:                             WASHER                    DRYER      Production              2 hrs                          3 hrs       Assembly                2 hrs                            1 hr Packaging               4  hrs                           4 hrs Currently the company has weekly capacity of 120 hours of Production Hours , 80 Assembly hours and 400 Packaging hours. Each washer can be sold  with 60 SAR profit and each Dryer can be sold with 70 SAR profit. a) What is the problem we should be focusing here? b) What would you do? c) What is the max amount of profit this company can make based on current conditions? d) Create LP model on paper e) Create model in Excel f) Use solver to solve it g) Interpret the results and help decision makers with your comments