Friday, May 30, 2014

Optimal Investment Selection With Excel Solver

Optimal Investment

Selection With Excel Solver

 

Maximizing Investment Return

Through Optimal Investment

Selection Using the Binary

Constraint

The Solver can be used to maximize return when selecting investments with a limited amount of investment capital. Projected cash flows for each investment are Solver inputs. Binary Decision Variables determining whether or not an investment will be made. The Solver calculates the Net Present Value of each possible combination of investments and determines the investment combination that maximizes Net Present Value (NPV) of invested cash flows.

The Problem

A venture capitalist with limited funds can make annual investments over the next 2 years. He has 6 investment opportunites to choose from. 3 of the investment opportunities will occur at the start of year 1. The other 3 investment opportunities will occur at the start of year 2. He doesn’t have to invest anything if he chooses not to. If he does choose to invest in any opportunity, he must invest upfront 100% of start-up capital required by that investment. The venture capitalist has only enough capital to make 2 investments during any one year.

The projected annual cash flows for each investment are shown below. These projected cash flows include the upfront investment that the venture capitalist would have to make.

The goal is to select the investments that would maximize Net Present Value at year 0 of all cash flows of all selected investments.

A discount rate of 25% will be used because the investments are considered risky.

The projected annual cash flows for each investment, including all upfront investments, are shown as follows:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

The 6-Step Excel Solver Problem

Solving Process

 

Excel Solver Step 1 – Determine the Objective

In this case, the objective is to maximize the Net Present Value at Year 0 of all cash flows of all selected investments. The cell in which the Net Present Value at Year 0 is calculated is the Objective Cell.

 

Excel Solver Step 2 – Determine the Decision Variables

We are trying to select the investments which will produce the highest Net Present Value of all cash flows at Year 0. The Decision Variables are binary variables (taking values of 1 or 0) which indicate whether an investment opportunity was chosen.

 

Excel Solver Step 3 – Build the Excel Equations That Combine the Objective With All Decision Variables

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

On the following pages are expanded views of the left and right sides of the preceding Excel model for better clarity.

Shown as follows is the left side of the Excel model. The green Decision Variables (C17 to E17, F18 to H18) are binary variables occurring in Years 1 and 2. The light blue Constraints cell (C24 and F24) limit the maximum number of investments that can be made during Year 1 and Year 2.

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

The right side of the model shown as follows contains 1) the combined annual cash flows for all selected investments (Column I), 2) the Present Value at Year 0 of each combined annual cash flow (Column J), 3) and the yellow Objective cell (K17) containing the Net Present Value at Year 0 of all combined annual cash flows (the sum of the Year 0 Present Values). Each year’s combined cash flows (I17 to I22) were copied from cells I31 to I36. The Excel formula for calculating the Present Values at Year 0 in column J is also shown.

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

Calculation of Net Present Value (NPV) of All Cash Flows At Year 0

The Net Present value (NPV) of all cash flows at year 0 equals the sum of the Present Value (PV) of each total annual cash flow at Year 0.

The Present Value at Year 0 of a cash flow that occurs in Year t is:

PVt=0 = Ct(1 + i)-t

Ct = Cash flow C that occurs in year t

I = Discount rate – We are discounting all cash flows back using a discount rate of 25% because the investment is considered risky, as most venture capital investments are. The Discount Rate is user-controlled.

For example, the Present Value at Year 0 of the Year 4 Cash flow, $115,000, would be calculated as follows:

PVt=0 = Ct(1 + i)-t

PVt=0 = ($115,000)(1 + 0.25)-4 = $47,104

The yellow Objective cell displays the Net Present Value of all annual cash flows of all selected investments. This Objective cell will be maximized. The green Decision Variable cells are binary and indicate whether or not a particular Year 1 or Year 2 investment opportunity has been chosen.

 

How To Display Only the Cash flows of the Selected Investments

The Objective of this problem is to select the combination of investments that produce the highest Net Present Value at Year 0. In order to calculate the NPV at Year 0 of all projected cash flows in the selected investments, we must find a method to display only the projected cash flows from the selected investments so that we can perform analysis on only these cash flows.

Here is one simple way to do that. First, we must list the projected cash flows of all 6 possible investment opportunities, as shown as follows:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

Now we need to display the cells containing the binary Decision Variables which indicate whether or not an investment has been selected. This is shown as follows, along with an expanded view of those Decision Variables:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

An expanded view of those binary Decision Variables is shown as follows:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

We can now display only the annuals projected cash flows that are connected with selected investments. This can be done very efficiently in Excel with an If-Then-Else statement.

The end result of using If-Then-Else statements is shown as follows. Each of the cells in the following spreadsheet portion contains an If-Then-Else statement. Each of these If-Then-Else statements copies the cash flow from the same location in the list of all projected cash flows above only if that respective opportunity has been selected.

In other words, the cash flow will be copied down only if the Decision Variable cell for that investment opportunity contains a 1, meaning that investment has been selected.

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

For example, in cell C31 of the Excel model in the preceding image is the following If-Then-Else code:

=if($C$17=1,C5,“ ”)

This Excel statement copies the cash flow from C5 into C31 only Decision Variable cell C17 is set to 1, which indicates that this investment has been selected.

This formula is copied from Cell 31 into all cells down and over to Cell C36. Note that Cell $C$17 is made to be an absolute reference because of the dollar signs. All of the copied formulas from cell C31 to Cell C36 will depend on whether Cell C17 contains a 1 or a 0.

The formula is also copied over and down to Cell I36. In each column, the absolute reference is move over. For example, all Cells from D31 to D36 now have absolute references on the Cell $D$17. Take a look at the contents of those cells in the downloadable spreadsheet containing this example. This concept is probably easier to understand when scrolling through the actual Excel spreadsheet.

If this investment was not selected, its Decision Variable cell C17 would be set to 0 and nothing (“ ”) would be copied into cell C31. In this way, only cash flows of selected investment appear once again in the image on the following page:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

All annual cash flows for the selected investments are summed up in the right column in Cells I31 to I36 as just shown.

These combined cash flows are copied back into the right side (Cell I31 to I36 are copied into I17 to I22) of the spreadsheet section containing the Decision Variable cells shown as follows:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

Here is an expanded view of the left side of the previous spreadsheet:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

Shown as follows is an expanded view of the right side of the Excel model. You can see the cash flows in I17 to I22 that have been copied from I31 to I36:

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

The user can change the Discount Rate here and watch the spreadsheet calculations immediately change to reflect the new Discount Rate. The greater the Discount Rate, the greater that the investment risk is believed to be and the lower will be the NPV of the cash flows at Year 0 of the selected investments.

The PV and NPV calculations are performed on these cash flow figures to attain the Objective, which is the NPV at Year 0 of all projected cash flows of all selected investments.

 

Excel Solver Step 4 – List All Constraints

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

Excel Solver Step 5 – Test the Excel Spreadsheet

Test the Excel spreadsheet completely before adding information to the Solver dialogue box. Make sure that any changes to Decision Variables produce the correct results in the Objective cell.

 

Excel Solver Step 6 – Insert All Data into the Solver Dialogue Box

Input the Objective cell, Decision Variable cell, and all Constraints into the Solver dialogue box. Note that the Binary Constraint was applied to the 6 Yes-or-No Decision Variables. The inequality Constraints limit the maximum number of investments to 2 in each of Year 1 and Year 2.

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

All equations on the Excel spreadsheet are linear (1st order) so we can use the Simplex LP (Linear Programming) Solver engine for this optimization problem.

Step 3 shows the completed problem with Decision Variables that have been optimized by the Solver to maximize the Objective while staying within the problem’s Constraints.

 

Excel Solver Answer Report

Part 1

Note:

- The Solver Result

- How long Solver took to solve the problem

- The Solver Engine that was used and the Solver Options settings

- Where the Objective Cell was labeled in the Excel model for its name to appear as it does in Part 1 of the Answer Report

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

Part 2

- Note that the Variable Cells contain the Decision Variables

- Note where the labels for each Decision Variable are placed in the Excel model so that the Decision Variable’s name will appear here in Part 2 of the Answer Report as it does

- Note the type of variable - Either Continuous or Integer (Integer, Binary, or Alldifferent)

- Note the Before and After values of each Decision Variable

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

Part 3

- Note how each Constraint is labeled in the Excel model in order for the Constraint’s name to appear here in Part 3 of the Answer Report as it does

- Note which Constraints are binding (had their limits hit) and which aren’t.

- Note how much slack is still available in any Constraint that has not had its limit hit.

- Note any Integer Constraints (Integer, Binary, Alldifferent)

solver,excel solver,optimization,excel, excel 2010,excel 2013, statistics,investment selection,venture capital (Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

No comments:

Post a Comment