ADM 2302 Lecture Notes - Net Present Value, Microsoft Excel, Contin
3 May 2018
School
Department
Course
Professor
Assignment 3
Business Analytics – ADM2302
Professor: Rim Jaber
Student: Sophia Didier (8658623)
March 25, 2018
find more resources at oneclass.com
find more resources at oneclass.com
Problem 1
Yakima Construction Corportation (YCC) is considering a number of different development
projects. The cash outflows that would be required to complete each project are indicated in the
table below, along with the expected net present value of each project (all values in millions of
dollars).
Project
1
2
3
4
5
Year 1
$8
$10
$12
$4
$14
Year 2
6
8
6
3
6
Year 3
3
7
6
2
5
Year 4
0
5
6
0
7
NPV
12
15
20
9
23
Each project must be done in full (with the corresponding cash flows for all four years) or not
done at all. Furthermore, there are the following additional considerations. Project 1 cannot be
done unless project 2 is undertaken, and project 3 and 4 would compete with each other, so they
should not both be chosen. YCC expects to have the following cash available to invest in these
projects: $40 million for year 1, $25 million for year 2, 16 million for year 3, and $12 million for
year 4. Any available money not spent in a given year is then available to spend the following
year. YCC’s policy is to choose their projects so as to maximize their total expected NPV.
a) Formulate algebraically a BIP model for this problem. Define the decision variables,
objective function, and constraints.
Let Li = the amount of investment ($) left over after year i (i = 1,2,3,4)
Max: total NPV = 12p1 + 15p2 + 20p3 +9p4 +23p5
Subject to:
Year 1: 8p1 + 10p2 + 12p3 + 4p4 + 14p5 + L1 = 40
Year 2: 14p1 + 18p2 + 18p3 + 7p4 + 20p5 + L2 – L1 = 25
Year 3: 17p1 + 25p2 + 24p3 + 9p4 + 25p5 + L3 – L2 = 16
Year 4: 17p1 + 30p2 + 30p3 + 9p4 + 35p5 + L4 – L3 = 12
Project 1 only if project 2: p1 <= p2 -> p1 - p2 <= 0
Project 3 and Project 4: p3 + p4 <= 1
pi = Binary
pi >= 0
Li >= 0
find more resources at oneclass.com
find more resources at oneclass.com
b) Formulate this same BIP problem on a spreadsheet and SOLVE using Excel solver.
(Provide a printout of the corresponding “Excel spreadsheet” and the “Answer
Report”)
Excel Spread Sheet:
P1
P2
P3
P4
P5
L1
L2
L3
L4
Solution
1
1
0
1
1
4
6
5
5
Total
Max Value
12
15
20
9
23
59
LHS
RHS
Year 1
8
10
12
4
14
1
40
=
40
Year 2
6
8
6
3
6
-1
1
25
=
25
Year 3
3
7
6
2
5
-1
1
16
=
16
Year 4
0
5
6
0
7
-1
1
12
=
12
P1 only if P2
1
-1
0
<=
0
Either P3 or
P4
1
1
1
<=
1
Excel Answer Report:
Microsoft Excel 16.11 Answer Report
Worksheet: [Assignment 3.xlsx]Problem 1
Report Created: 2018-03-25 5:28:29 PM
Result: Solver found a solution. All constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 481036418.822 Seconds.
Iterations: 2 Subproblems: 4
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume
NonNegative
Objective Cell (Max)
Cell
Name
Original
Value
Final Value
$K$5
Max Value Total
59
59
Variable Cells
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Yakima construction corportation (ycc) is considering a number of different development projects. The cash outflows that would be required to complete each project are indicated in the table below, along with the expected net present value of each project (all values in millions of dollars). Each project must be done in full (with the corresponding cash flows for all four years) or not done at all. Project 1 cannot be done unless project 2 is undertaken, and project 3 and 4 would compete with each other, so they should not both be chosen. Ycc expects to have the following cash available to invest in these projects: million for year 1, million for year 2, 16 million for year 3, and million for year 4. Any available money not spent in a given year is then available to spend the following year.
