# 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)*

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

**(1 + i)**

_{t}

^{-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

**(1 + i)**

_{t}

^{-t}PV** _{t=0}** = ($115,000)(1 + 0.25)

**= $47,104**

^{-4}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)*

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

- Histograms in Excel
- Bar Chart in Excel
- Combinations & Permutations in Excel
- Normal Distribution in Excel
- Overview of the Normal Distribution
- Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
- Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
- Solving Normal Distribution Problems in Excel 2010 and Excel 2013
- Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
- An Important Difference Between the t and Normal Distribution Graphs
- The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
- Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An Easy-To-Understand Way

- t-Distribution in Excel
- Binomial Distribution in Excel
- z-Tests in Excel
- Overview of Hypothesis Tests Using the Normal Distribution in Excel 2010 and Excel 2013
- One-Sample z-Test in 4 Steps in Excel 2010 and Excel 2013
- 2-Sample Unpooled z-Test in 4 Steps in Excel 2010 and Excel 2013
- Overview of the Paired (Two-Dependent-Sample) z-Test in 4 Steps in Excel 2010 and Excel 2013

- t-Tests in Excel
- Overview of t-Tests: Hypothesis Tests that Use the t-Distribution
- 1-Sample t-Tests in Excel
- 1-Sample t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Normality Testing For the 1-Sample t-Test in Excel 2010 and Excel 2013
- 1-Sample t-Test – Effect Size in Excel 2010 and Excel 2013
- 1-Sample t-Test Power With G*Power Utility
- Wilcoxon Signed-Rank Test in 8 Steps As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
- Sign Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013

- 2-Independent-Sample Pooled t-Tests in Excel
- 2-Independent-Sample Pooled t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Variance Tests: Levene’s, Brown-Forsythe, and F Test For 2-Sample Pooled t-Test in Excel 2010 and Excel 2013
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro Wilk Tests For Two-Sample Pooled t-Test
- Two-Independent-Sample Pooled t-Test - All Excel Calculations
- 2- Sample Pooled t-Test – Effect Size in Excel 2010 and Excel 2013
- 2-Sample Pooled t-Test Power With G*Power Utility
- Mann-Whitney U Test in 12 Steps in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013
- 2- Sample Pooled t-Test = Single-Factor ANOVA With 2 Sample Groups

- 2-Independent-Sample Unpooled t-Tests in Excel
- 2-Independent-Sample Unpooled t-Test in 4 Steps in Excel 2010 and Excel 2013
- Variance Tests: Levene’s Test, Brown-Forsythe Test, and F-Test in Excel For 2-Sample Unpooled t-Test
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk For 2-Sample Unpooled t-Test
- 2-Sample Unpooled t-Test Excel Calculations, Formulas, and Tools
- Effect Size for a 2-Independent-Sample Unpooled t-Test in Excel 2010 and Excel 2013
- Test Power of a 2-Independent Sample Unpooled t-Test With G-Power Utility

- Paired (2-Sample Dependent) t-Tests in Excel
- Paired t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Normality Testing of Paired t-Test Data
- Paired t-Test Excel Calculations, Formulas, and Tools
- Paired t-Test – Effect Size in Excel 2010, and Excel 2013
- Paired t-Test – Test Power With G-Power Utility
- Wilcoxon Signed-Rank Test in 8 Steps As a Paired t-Test Alternative
- Sign Test in Excel As A Paired t-Test Alternative

- Hypothesis Tests of Proportion in Excel
- Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
- 1-Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
- 2-Sample Pooled Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
- How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer

- Chi-Square Independence Tests in Excel
- Chi-Square Goodness-Of-Fit Tests in Excel
- F Tests in Excel
- Correlation in Excel
- Pearson Correlation in Excel
- Spearman Correlation in Excel
- Confidence Intervals in Excel
- z-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
- t-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
- Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
- Confidence Interval of Population Proportion in 2 Steps in Excel 2010 and Excel 2013
- Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013

- Simple Linear Regression in Excel
- Overview of Simple Linear Regression in Excel 2010 and Excel 2013
- Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013
- Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013
- Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression
- Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
- All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
- Prediction Interval of Simple Regression in Excel 2010 and Excel 2013

- Multiple Linear Regression in Excel
- Basics of Multiple Regression in Excel 2010 and Excel 2013
- Complete Multiple Linear Regression Example in 6 Steps in Excel 2010 and Excel 2013
- Multiple Linear Regression’s Required Residual Assumptions
- Normality Testing of Residuals in Excel 2010 and Excel 2013
- Evaluating the Excel Output of Multiple Regression
- Estimating the Prediction Interval of Multiple Regression in Excel
- Regression - How To Do Conjoint Analysis Using Dummy Variable Regression in Excel

- Logistic Regression in Excel
- Logistic Regression Overview
- Logistic Regression in 6 Steps in Excel 2010 and Excel 2013
- R Square For Logistic Regression Overview
- Excel R Square Tests: Nagelkerke, Cox and Snell, and Log-Linear Ratio in Excel 2010 and Excel 2013
- Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
- Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
- Hosmer- Lemeshow Test in Excel – Logistic Regression Goodness-of-Fit Test in Excel 2010 and Excel 2013

- Single-Factor ANOVA in Excel
- Overview of Single-Factor ANOVA
- Single-Factor ANOVA in 5 Steps in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For Each Single-Factor ANOVA Sample Group
- Kruskal-Wallis Test Alternative For Single Factor ANOVA in 7 Steps in Excel 2010 and Excel 2013
- Levene’s and Brown-Forsythe Tests in Excel For Single-Factor ANOVA Sample Group Variance Comparison
- Single-Factor ANOVA - All Excel Calculations
- Overview of Post-Hoc Testing For Single-Factor ANOVA
- Tukey-Kramer Post-Hoc Test in Excel For Single-Factor ANOVA
- Games-Howell Post-Hoc Test in Excel For Single-Factor ANOVA
- Overview of Effect Size For Single-Factor ANOVA
- ANOVA Effect Size Calculation Eta Squared in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Psi – RMSSE – in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Omega Squared in Excel 2010 and Excel 2013
- Power of Single-Factor ANOVA Test Using Free Utility G*Power
- Welch’s ANOVA Test in 8 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
- Brown-Forsythe F-Test in 4 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar

- Two-Factor ANOVA With Replication in Excel
- Two-Factor ANOVA With Replication in 5 Steps in Excel 2010 and Excel 2013
- Variance Tests: Levene’s and Brown-Forsythe For 2-Factor ANOVA in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
- Excel Post Hoc Tukey’s HSD Test For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication – Test Power With G-Power Utility
- Scheirer-Ray-Hare Test Alternative For 2-Factor ANOVA With Replication

- Two-Factor ANOVA Without Replication in Excel
- Randomized Block Design ANOVA in Excel
- Repeated-Measures ANOVA in Excel
- Single-Factor Repeated-Measures ANOVA in 4 Steps in Excel 2010 and Excel 2013
- Sphericity Testing in 9 Steps For Repeated Measures ANOVA in Excel 2010 and Excel 2013
- Effect Size For Repeated-Measures ANOVA in Excel 2010 and Excel 2013
- Friedman Test in 3 Steps For Repeated-Measures ANOVA in Excel 2010 and Excel 2013

- ANCOVA in Excel
- Normality Testing in Excel
- Creating a Box Plot in 8 Steps in Excel
- Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
- Chi-Square Goodness-of-Fit Test For Normality in 9 Steps in Excel
- Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk Normality Tests in Excel

- Nonparametric Testing in Excel
- Mann-Whitney U Test in 12 Steps in Excel
- Wilcoxon Signed-Rank Test in 8 Steps in Excel
- Sign Test in Excel
- Friedman Test in 3 Steps in Excel
- Scheirer-Ray-Hope Test in Excel
- Welch's ANOVA Test in 8 Steps Test in Excel
- Brown-Forsythe F Test in 4 Steps Test in Excel
- Levene's Test and Brown-Forsythe Variance Tests in Excel
- Chi-Square Independence Test in 7 Steps in Excel
- Chi-Square Goodness-of-Fit Tests in Excel
- Chi-Square Population Variance Test in Excel

- Post Hoc Testing in Excel
- Creating Interactive Graphs of Statistical Distributions in Excel
- Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
- Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution’s PDF in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution’s CDF in Excel 2010 and Excel 2013
- Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013

- Solving Problems With Other Distributions in Excel
- Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
- Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
- Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
- Solving Beta Distribution Problems in Excel 2010 and Excel 2013
- Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
- Solving Poisson Distribution Problems in Excel 2010 and Excel 2013

- Optimization With Excel Solver
- Maximizing Lead Generation With Excel Solver
- Minimizing Cutting Stock Waste With Excel Solver
- Optimal Investment Selection With Excel Solver
- Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
- Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
- Optimizing a Bond Portfolio With Excel Solver
- Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers

- Chi-Square Population Variance Test in Excel
- Analyzing Data With Pivot Tables and Pivot Charts
- SEO Functions in Excel
- Time Series Analysis in Excel
- VLOOKUP
- Simplifying Useful Excel Functions

## No comments:

## Post a Comment