B6015 - Spring 2007 Solution to Practice Final Examination
Decision Models Page 3 of 9
Solution to Practice Final Examination
1. ACE Manufacturing
a) Decision variables: number of units of regular line (R), super line (S), and maxi line (M) to produce.
Objective function to be maximized: profit (in $), 50R+75S+100M.
Constraints:
1.2R + 1.6S + 2.1M <= 1,600 (assembly time)
0.8R+ 1.0S + 1.4M <=700 (paint time)
0.2R+ 0.2S + 0.4M <=300 (inspection time)
R=>150, S=>90, M=>20 (order constraints)
b) Optimal production values are R=150, S=552, M=20, optimal profit value: $50,900.
Sensitivity report for the solution to the problem is shown below
Adjustable CellsFinal / Reduced / Objective / Allowable / Allowable
Cell / Name / Value / Cost / Coefficient / Increase / Decrease
$B$4 / Units to manufacture Regular / 150 / -10 / 50 / 10 / 1E+30
$C$4 / Units to manufacture Super / 552 / 0 / 75 / 1E+30 / 3.571428571
$D$4 / Units to manufacture Maxi / 20 / -5 / 100 / 5 / 1E+30
Constraints
Final / Shadow / Constraint / Allowable / Allowable
Cell / Name / Value / Price / R.H. Side / Increase / Decrease
$E$9 / Assembly Time (hr) Used / 1105.2 / 0 / 1600 / 1E+30 / 494.8
$E$10 / Paint Time (hr) Used / 700 / 75 / 700 / 309.25 / 462
$E$11 / Inspection Time (hr) Used / 148.4 / 0 / 300 / 1E+30 / 151.6
Shadow price of each hour of paint time is $75 which makes an offer at $60 an attractive one. 200 extra hours of paint time is within the allowable increase of 309.25.
2. Money Co.
a) Below is a snapshot of the model. The decision variables are the amounts invested in the 5 different bonds, as well as the amount put into the CD in any given year. The objective function is the amount of available cash at the beginning of year 4. There are two types of constraints. The first set of constraints represents the cash flow constraints in every year: Money going in = Money going out in every year. The second set of constraints represents the requirement that Money Co cannot invest more than 500,000 into any one of the Bonds. Finally, we have non-negativity constraints for all decision variables.
The Solver parameters are:
b) The optimal investment plan is to invest $175,000 into Bond A, $435,000 into Bond B, $391,000 into Bond C, $500,000 into Bond D and $500,000 into Bond E. The maximum amount of cash available in year 4 is $1,479,000.
c) If we count the dollar amounts in multiples of $100,000, and if we put a restriction of integer variables for decision variables B6:F6 (amounts in Bonds), we will guarantee that amounts in Bonds will be multiples of $100,000. Here is how the model looks like:
The optimal investment plan is to put $100,000 into Bond A, $500,000 into Bond B, D and E and $400,000 into Bond C. The optimal amount of cash available in year 4 is $1,470,300.
3. Gotham City REIT
a) Annual profit = $2400*12*65+$2400*12*35-$4000*100-$2,000,000 = $480,000
b) Assumption cell : e - the error term in demand distribution under rent $2400, normal random variable with mean 0 and standard deviation of 12.
Forecast cell 1: profit value under rent of $2400 = ($2400*12-$4000)*65-$2,000,000+ ($2400*12-$4000)*MIN(35+e,35)
Forecast cell 2: profit value under rent of $2700 = ($2400*12-$4000)*65-$2,000,000+ ($2700*12-$4000)*MIN(30+e,35)
c) Rent $2400: expected profit = $352,913, MSE = $5,675.98
Rent $2700: expected profit = $377,277, MSE = $7,983.81
Expected profit is higher for the rent of $2,700.
d) I used “Extract Data” button on CB toolbar with setting corresponding to 5%-percentiles. The results:
95% VaR for (rent = $2400) is -$16,518
95% VaR for (rent = $2700) is -$104,593
e) I introduced 2 new forecast cells with following formulae: =IF(demand<available apartments,1,0), where “demand” depends on rental price and “available apartments” is equal to 35. The means of the new forecast cells represent the estimates of the probabilities that there are vacant apartments. The results of simulation run with n=1000 and seed=123 show that the probability of having vacant apartments is 0.51 for the rental price of $2400 and 0.66 for the rental price of $2700.
4. Trusty BankCo
a) Using the cumulative frequency graph, we can estimate that the payout happens to be higher than $0.30 roughly 90% of the time.
b) Using the simulation statistics, we observe that the expected payout is $0.578.
c) $0.578+/- 1.96*$0.011 =[$0.556,$0.600]
d)
e) On the one hand, Trusty wants to have a 75% probability of having a profit of at least $0.05 per barrel. The profit is C-Payout. Based on the cumulative frequency graph, we see that the Payout is less than $0.71 in roughly 75% of the trials. Therefore, if Trusty charges a cost C of $0.71+$0.05 = $0.76, it will ensure that a profit of at least $0.05 per barrel will be achieved with 75% probability.
On the other hand, by charging C=$0.578+$0.10=$0.678, Trusty will assure that its average profit will be at least $0.10.
Taking the maximum of these two numbers, we get the answer: C=$0.76.