Name(s): This HW is comprised of 3 problems (a network problem, integer problema and non-linear problem) Use this spreadsheet as a template and submission file (add your name(s) to the file) It is due April 9th, 2017 end of day. A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 B C D E F G H I J K L M N Travel time per call from each district to each hospital District Bayview Lake shore 1 5 8 2 6 9 3 7 10 4 5 7 5 6 8 6 7 9 7 9 5 8 10 6 9 11 7 10 7 3 11 8 4 12 9 5 Calls/hour 0.5 0.6 0.4 0.3 0.4 0.6 0.7 0.9 1 0.2 0.6 0.1 The City of Port Charles has two hospitals. (Bayview Hospital and Lakeshore Hospital) Bayview Hospital has 4 ambulances and Lakeshore Hospital has 2 ambulances. Queueing theory (done outside of this problem)indicates that Bayview can be assigned up to 4.9 calls per hour and that Lakeshore can be assigned up to 1.5 calls per hour. Port Charles has been divided into 12 districts. The travel time per call in each district and the average number of calls per hour emanating from each district are given in table to the left. The objective is to minimize the average travel time needed to respond to a call. Determine the proper assignment of districts (NOT individual ambulances) to hospitals. A given district's calls do not all have to be assigned to the same hospital. Since these are averages, fractional assignments are expected. Create the written model and formulate in solver. (written model can be a diagram if you wish) 17 18 19 Bayview 20 4 ambulances Assigned 4.9 calls/hr 21 Lakeshore 22 2 ambulances Assigned 1.5 call/hr 23 Variables: 24 Hospital assignments to districts 25 Constraints: 26 Number of ambulances Calls per hour per hospital (4.9 Bay./1.5 Lake) Number of districts to hospitals 27 28 Bayview 4.9 Lakeshore1 .5 29 Objective: MIN average travel time 30 31 Hospital to Ambulance 32 Districts 33 Amb 1 2 3 4 5 6 7 8 9 10 11 12 34 35 1 2 5 5 6 6 7 7 5 5 3 4 5 5 6 6 7 7 5 5 7 7 7 7 9 9 9 9 10 10 10 10 11 11 11 11 7 7 7 7 8 8 8 8 9 9 36 37 6 6 6 6 40 41 Amb 1 1 5 2 3 4 5 6 7 8 9 10 11 12 42 43 2 3 3 5 44 45 4 38 39 46 O Ambulance service 9 9 Number of calls 1 2 8 8 4 3 8 9 <= 4.9 Problem 4.13 P Q Ambulance service Travel time per call from each district to each hospital District Bayview Lake shore Calls/hour 1 5 8 0.5 2 6 9 0.6 3 7 10 0.4 4 5 7 0.3 5 6 8 0.4 6 7 9 0.6 7 9 5 0.7 8 10 6 0.9 9 11 7 1 10 7 3 0.2 11 8 4 0.6 12 9 5 0.1 Place written model and solutions below: Bayview 4 ambulancAssigned 4.9 calls/hr Lakeshore 2 ambulancAssigned 1.5 call/hr Variables: Hospital assignments to districts ConstraintsNumber of ambulances Calls per hour per hospital (4.9 Bay. Number of districts to hospitals Objective: MIN average travel time Hospital to Ambulance Districts Amb 1 2 3 4 Amb 1 2 3 4 1 5 5 5 5 2 6 6 6 6 Number of Calls 1 2 5 3 5 8 5 TRAVEL TIME 3 4 7 5 7 5 7 5 7 5 5 6 7 8 9 6 6 6 6 7 7 7 7 9 9 9 9 10 10 10 10 11 11 11 11 3 4 5 6 7 8 9 8 4 8 9 8 9 8 4 2 2 0 0 and solutions below: Assigned 4.9 calls/hr Assigned 1.5 call/hr ssignments to districts f ambulances hour per hospital (4.9 Bay./1.5 Lake) f districts to hospitals age travel time 10 11 7 7 7 7 8 8 8 8 12 9 9 9 9 10 11 12 1 3 8 8 1 3 A 1 Meeting cash requirements 2 3 Initial cash 4 5 Fixed cost of withdrawal 6 7 Opportunity cost/$1/year 8 9 B C D E F G H I $3,000 $10 $0.20 Mon Tue Wed Thur Fri Sat Sun $5,000 $6,000 $9,000 $2,000 $7,000 $2,000 $3,000 10 11 Amount withdrawn 12 13 14 15 Cash requirement J K L M N O P Q R On Monday morning, you have $3000 in cash on hand. For the next seven days, the following cash requirements must be met: Monday, $5000; Tuesday, $6000; Wednesday, $9000; Thursday, $2000; Friday, $7000; Saturday, $2000; Sunday, $3000. At the beginning of each day, you must decide how much money (if any) to withdraw from the bank. It costs $10 to make a withdrawal of any size. You believe that the opportunity cost of having $1 of cash on hand for a year is $0.20. Assume that opportunity costs are incurred on each day's ending balance ($0.20/365). Determine how much money you should withdraw from the bank during each of the next seven days. Problem 5.37 Meeting cash requirements Initial cash Fixed cost of withdrawal Opportunity cost/$1/year $3,000 $10 $0.20 Mon Tue Wed Thur Fri Sat Sun $5,000 $6,000 $9,000 $2,000 $7,000 $2,000 $3,000 Amount withdrawn Cash requirement Place written model and solutions below. Feel free to modify spreadsheet as needed. A 1 2 Spending on advertising 3 4 Cost per minute to advertise 5 6 7 B C TV $3,000 Radio $1,000 D E F G 11 12 13 Minutes purchased 14 TV C 0.8 D 5 I J K L M N A firm is planning to spend $75,000 on advertising. It costs $3000 per minute to advertise on television and $1000 per minute to advertise on radio. If the firm buys X minutes of television advertising and Y minutes of radio advertising, its revenue in thousands of dollars is given by -0.3x^20.4y^2+0.8xy+5x+10y. How can the firm maximize its revenue? Formulate the written model and solver model. Revenue (in $1000s) from advertising x minutes on TV, y minutes 8 on radio is of form -Ax2-By2+Cxy+Dx+Ey, where 9 A B 10 0.3 0.4 H E 10 Radio Problem 6.18 O Spending on advertising Cost per minute to advertise TV $3,000 Radio $1,000 A 0.3 B 0.4 TV Radio Revenue (in $1000s) from advertising x minutes on TV, y minutes on radio is of form -Ax2-By2+Cxy+Dx+Ey, where Minutes purchased C 0.8 Place written model and solutions below. D 5 E 10