Question

Help solve ONLY (b) with excel. Please show step by step

2-40. Fox Enterprises is considering six projects for possible construction over the next four years. Fox can undertake any of the projects partially or completely. A partial undertaking of a project will prorate both the return and cash outlays proportionately. The expected (present value) returns and cash outlays for the projects are given in the following table. Cash outlay ($1000) Year 2 Year 3 Project Year 1 Year 4 22 10.5 8.3 10.2 3.1 42 12.6 14.2 10.5 10.1 7.8 Return ($1000) 32.40 35.80 17.75 14.80 18.20 12.35 5.0 6.3 12.3 9.2 8.3 6.9 5.1 Available funds ($1000) 60.0 70.0 35.0 20.0 (a) Formulate the problem as a linear program, and determine the optimal project mix that maximizes the total return using AMPL, Solver, or TORA. Ignore the time value of money. (b) Suppose that if a portion of project 2 is undertaken, then at least an equal portion of project 6 must be undertaken. Modify the formulation of the model, and find the new optimal solution.

Answer 1

Excel setup:

I E F B Proj-1 C Proj-2 D Proj-3 G Proj-6 Proj-4 Proj-5 1 2 Proportion 3 Return 32.4 35.8 17.75 14.8 18.2 12.35 =SUMPRODUCT(B2:G2,B3.G3) 4 Available 5 OUTLAY 6 Year-1 7 Year-2 8 Year-3 9 Year-4 - Proj-1 10.5 14.4 2.2 2.4 Proj-2 8.3 12.6 9.5 3.1 Proj-3 10.2 14.2 5.6 4.2 Proj-4 7.2 10.5 7.5 Proj-5 12.3 10.1 8.3 6.3 Proj-6 9.2 7.8 6.9 5.1 Consumed =SUMPRODUCT($B$2:$G$2.B6:G6) =SUMPRODUCT($B$2:$G$2,B7 G7) =SUMPRODUCT($B$2:$G$2.B8:G8) =SUMPRODUCT($B$2:$G$2.89:69) 70 35 20

Solver input (the new constraint is highlighted)

Set Objective: $H$3 To: Max O Min value of: By Changing Variable Cells: $B$2:$G$2 Subject to the Constraints: $B$2:$G$2 <= 1 $G$2 >= $C$2 $H$6: SH$9 <= $I$6: $1$9 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Solution:

	Proj-1	Proj-2	Proj-3	Proj-4	Proj-5	Proj-6
Proportion	100.00%	100.00%	100.00%	100.00%	3.17%	100.00%
Return	32.40	35.80	17.75	14.80	18.20	12.35	113.68
OUTLAY	Proj-1	Proj-2	Proj-3	Proj-4	Proj-5	Proj-6	Consumed	Available
Year-1	10.5	8.3	10.2	7.2	12.3	9.2	45.79	60
Year-2	14.4	12.6	14.2	10.5	10.1	7.8	59.82	70
Year-3	2.2	9.5	5.6	7.5	8.3	6.9	31.96	35
Year-4	2.4	3.1	4.2	5.0	6.3	5.1	20.00	20