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:

(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

(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.

(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.

(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:

PV_t=0 = C_t(1 + i)^-t

C_t = 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:

PV_t=0 = C_t(1 + i)^-t

PV_t=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:

(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:

(Click On Image To See a Larger Version)

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

(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.

(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:

(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:

(Click On Image To See a Larger Version)

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

(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:

(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

(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.

(Click On Image To See a Larger Version)

All equations on the Excel spreadsheet are linear (1^st 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

(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

(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)

(Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Technorati Tags: solver,excel solver,optimization,excel,excel 2010,excel 2013,statistics,investment selection,venture capital

Become an Excel Statistical Master

Excel Master Series - MBA-level statistics - Over 1,100+ Pages of Easy-To-Follow Instructions in Excel

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

Step-By-Step Optimization With Excel Solver

What's In It?

For anyone who wants to be operating at a high level with the Excel Solver quickly, this is the book for you. Step-By-Step Optimization With Excel Solver is a 200+ page .pdf e-manual of simple yet thorough explanations on how to use the Excel Solver to solve today’s most widely known optimization problems. Loaded with screen shots that are coupled with easy-to-follow instructions, this book will simplify many difficult optimization problems and make you a master of the Excel Solver almost immediately.

Here are just some of the Solver optimization problems that are solved completely with simple-to-understand instructions and screen shots in this e-manual:

• The famous “Traveling Salesman” problem using Solver’s Alldifferent constraint and the Solver’s Evolutionary method to find the shortest path to reach all customers. This also provides an advanced use of the Excel INDEX function.

• The well-known “Knapsack Problem” which shows how optimize the use of limited space while satisfying numerous other criteria.

• How to perform nonlinear regression and curve-fitting on the Solver using the Solver’s GRG Nonlinear solving method.

• How to solve the “Cutting Stock Problem” faced by many manufacturing companies who are trying to determine the optimal way to cut sheets of material to minimize waste while satisfying customer orders.

• Portfolio optimization to maximize return or minimize risk.

• Venture capital investment selection using the Solver’s Binary constraint to maximize Net Present Value of selected cash flows at year 0. Clever use of the If-Then-Else statements makes this a simple problem.

• How use Solver to minimize the total cost of purchasing and shipping goods from multiple suppliers to multiple locations.

• How to optimize the selection of different production machine to minimize cost while fulfilling an order.

• How to optimally allocate a marketing budget to generate the greatest reach and frequency or number of inbound leads at the lowest cost.

Step-By-Step Optimization With Excel Solver has complete instructions and numerous tips on every aspect of operating the Excel Solver. You’ll fully understand the reports and know exactly how to tweek all of the Solver’s settings for total custom use. This e-manual also provides lots of inside advice and guidance on setting up the model in Excel so that it will be as simple and intuitive as possible to work with.

All of the optimization problems in this book are solved step-by-step using a 6-step process that works every time. In addition to detailed screen shots and easy-to-follow explanations on how to solve every optimization problem in the book, a link is provided to download an Excel workbook that has all problems completed exactly as they are in this e-manual.

Step-By-Step Optimization With Excel Solver is exactly the e-manual you need if you want to be optimizing at an advanced level with the Excel Solver quickly.

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

Immediate, Absolute, No-Questions-Asked, Money-Back Guarantee If Not TOTALLY, 100% Satisfied. In Other Words, If Any Excel Master Series eManual That You've Purchased Here Does Not Provide Instructions That Are CRYSTAL CLEAR and EASY TO UNDERSTAND, You Get All Of Your Money Back Immediately and Keep the eManual. Guaranteed!

Meet The Author