BUS6310 HW3 Instructions: Construct the necessary EXCEL model to solve each problem. Put your interpretations in text boxes in your EXCEL file. This is not a TURN-IT-IN assignment. 1) (20 marks) You are working for a local restaurant that sells lunch boxes near The University of Sydney campus. The materials and labour cost of the lunch box is $3. To keep the lunch boxes fresh, any unsold boxes will be given away to the employees at a discounted price of $1. An appropriate model to mimic the restaurant's operation is a single-period price-inventory joint optimization newsvendor model. The historical data suggests that the mean of the demand equals E(D)=50-2r, where r is the price; and the standard deviation is 3 that is unaffected by the pricing decision. However, the exact demand distribution is known. You wish to apply the max-min decision rule. a) Determine the optimal price and the optimal inventory level of the lunch box. b) How much is the optimal worst-case expected profit? 2) (25 marks) Reebok is considering the use of blank jerseys rather than the printed jerseys for the Golden State Warriors in the upcoming 2015/2016 season. The demand for each player's jerseys is assumed to be independent. The demand forecasts for the top 3 players of the team are the following: Players 30 Stephen Curry 11 Klay Thompson 9 Andre Iguodala Mean 20000 15000 10000 Standard Deviation 8000 5000 3000 When only the blank jerseys are used, the total mean demand of these three players' jerseys is 45000 and the standard deviation is 15000. The distribution centre (DC) pre-stocks the blank jerseys. As soon as the order for a specific play is received, the DC centre prints the name and number of the player on the jersey (i.e., print-to-order). The cost of the printed jersey is $20 each. The cost of the blank jersey is $18 each. The full retail price of the jerseys is $60 each. At the end of the season, the unsold jerseys will be salvaged. The salvage value of each printed jersey is $12 and that of each blank jersey is $15. However, the extra cost to print the jersey at the distribution centre after receiving the retailer order is $2 per jersey. The distribution is assumed to be normal. a) Suppose that only printed jerseys are used. Determine the inventory level for each player's jersey that maximizes the expected profit. Compute the optimal expected profit for this plan. b) Suppose that only blank jerseys are used. The distribution centre prints the player's number and name on the blank jerseys only after a retailer's order is received. Determine the optimal inventory level of the blank jersey Compute the optimal expected profit for this plan. c) Find the break-even value of the printing cost such that using blank jersey is equally profitable as using printed jersey under the max-min decision rule. QBUS6310 HW3 3) (20 marks) A software company is employing 11 engineers. Each engineer is able to handle 12 projects each year. Each project on average yields $25,000 net profit. The demand forecast for the next year is shown in the following table. Demand Probability 100 0.2 120 0.3 140 0.3 160 0.2 If there are not enough manpower to handle the projects, the company has to turn down the customer requests. The software company is consider whether to hire more engineers. The annual salary of each newly hired engineer is $100,000 - Using the min-max regret decision rule, what is the recommended number of new engineers to hire? Hint: The feasible options include: no hiring, hire 1 engineer, hire 2 engineers, and hire 3 engineers. Using the max-min decision rule, what is the recommended number of new engineers to hire? Using the expected value decision rule, what is the recommended number of new engineers to hire? 4) (15 marks) To complete an R&D project, 5 tasks must be performed in any possible sequence. The details of these 5 tasks are shown in the following table. Task ID A B C D E Duration (weeks) 8 4 3 3 12 Cash Flow (thousand dollars) 11 -8 -6 8 20 Dependency Must precede A In any time, only one task can be performed due to the human resource constraint. The annual discount rate for the discrete model is 13%. a. Compute the discount rate for the continuous time model. b. Create an EXCEL model to find the optimal schedule that maximizes the NPV. Submit your EXCEL file using the online system. QBUS6310 HW3 5) (20 marks) You are overseeing the project portfolio for a manufacturer. Index Project 1 HQ 2 2 5 3 3 50 2 Factory 10 10 8 10 10 60 3 Logo 20 20 10 4 IT 5 Warehouse 10 6 Staff 0.5 7 Office 20 8 Canteen 2.5 9 Electrical 15 10 Elevator 18 20 11 Trucks 7 3 12 Rails Annual Budget 2010 2011 2012 8 2013 2014 Net Benefits 70 8 8 15 75 0.5 0.5 20 30 25 50 2.5 2.5 35 17 20 65 17 60 40 4 30 60 25 25 23 25 The expenses in each year cannot exceed the budget available for the year. Unused budget is not carried forward to the next year. In addition, the project office has only 2 full-time staffs (including you). Each staff can only manage two projects in any year. So in each year no more than 4 projects can be taken simultaneously. a. Develop an EXCEL model to maximize the benefit (measured in thousand dollars) of the project portfolio. b. Suppose that the project office receives an extra funding to hire a part-time staff who can oversee one project in each year. The cost to hire this part-time staff is $4,000 per year. Is it justifiable to hire a part-time staff