Using Solver for capital budgeting

Applies to
Microsoft Office Excel 2003

Book cover


This article was adapted from Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston. Visit Microsoft Learning to learn more about this book.

This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared — you may need to put your thinking cap on.

In this article

How can a company use Solver to determine which projects it should undertake?

Handling other constraints

Problems

Sample files    You can download the sample files that relate to excerpts from Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online. This article uses the files CapBudget.xls and PressData.xls.

How can a company use Solver to determine which projects it should undertake?

Each year, a company such as Eli Lilly needs to determine which drugs to develop; a company like Microsoft, which software programs to develop; a company like Proctor and Gamble, which new consumer products to develop. Microsoft Office Excel Solver can help a company make these decisions.

Most corporations want to undertake projects that contribute the greatest net present value (NPV), subject to limited resources (usually capital and labor). Let’s say that Microsoft is trying to determine which of 20 software projects it should undertake. The NPV (in millions of dollars) contributed by each project, as well as the capital (in millions of dollars) and the number of programmers needed during each of the next three years, is given on the worksheet named Basic Model in the file CapBudget.xls, which is shown in the following figure. For example, project 2 yields $908 million. It requires $151 million during year 1, $269 million during year 2, and $248 million during year 3. Project 2 requires 139 programmers during year 1, 86 programmers during year 2, and 83 programmers during year 3. (In the file CapBudget.xls, I’ve hidden the information for projects 14–18 by selecting rows 19–23, and then clicking Row Hide on the Format menu. To display these rows again, select rows 19–23, and then click Row Unhide on the Format menu.) Cells E4:G4 show the capital (in millions of dollars) available during each of the next three years, and cells H4:J4 indicate how many programmers are available. For example, during year 1 up to $2.5 billion in capital and 900 programmers are available.

Worksheet with data to be used with Solver

For each project, Microsoft must decide whether it should undertake the project. Let’s assume that we can’t undertake a fraction of a software project; if we allocate .5 of the needed resources, for example, we would have a nonworking program that would bring us $0 revenue!

The trick in modeling situations in which you either do or don’t do something is to use binary changing cells. A binary changing cell always equals 0 or 1. When a binary changing cell that corresponds to a project equals 1, we do the project. If a binary changing cell that corresponds to a project equals 0, we don’t do the project. You set up Solver to use a range of binary changing cells by adding a constraint — select the changing cells you want to use, and then click bin in the drop-down list in the Add Constraint dialog box.

With this background, we’re ready to solve Microsoft’s project selection problem. As always with a Solver model, we begin by identifying our target cell, changing cells, and constraints.

  • Target cell    Maximize the NPV generated by selected projects.
  • Changing cells    A 0 or 1 binary changing cell for each project. I’ve located these cells in the range A6:A25 (and named the range "doit"). For example, a 1 in cell A6 indicates that we undertake project 1; a 0 in cell A6 indicates that we don’t undertake project 1.
  • Constraints    We need to ensure that for each year t (t = 1, 2, 3), year t capital used is less than or equal to year t capital available, and year t labor used is less than or equal to year t labor available.

As you can see, our spreadsheet must compute for any selection of projects the NPV, the capital used annually, and the programmers used each year. In cell B2, I use the formula SUMPRODUCT(doit,NPV) to compute the total NPV generated by selected projects. (The range name NPV refers to the range C6:C25.) For every project with a 1 in column A, this formula picks up the NPV of the project, and for every project with a 0 in column A, this formula does not pick up the NPV of the project. Therefore, we’re able to compute the NPV of all projects, and our target cell is linear because it is computed by summing terms that follow the form (changing cell)*(constant). In a similar fashion, I compute the capital used each year and the labor used each year by copying from E2 to F2:J2 the formula SUMPRODUCT(doit,E6:E25).

I now fill in the Solver Parameters dialog box as shown in the following figure.

Solver Parameters dialog box

Our goal is to maximize the NPV of selected projects (cell B2). Our changing cells (the range named doit) are the binary changing cells for each project. The constraint E2:J2<=E4:J4 ensures that during each year the capital and labor used are less than or equal to the capital and labor available. To add the constraint that makes the changing cells binary, I click Add in the Solver Parameters dialog box, and then click bin in the drop-down list in the middle of the dialog box. The Add Constraint dialog box should now appear as shown in the following figure.

Add Constraint dialog box with Bin option selected

Our model is linear because the target cell is computed as the sum of terms that have the form (changing cell)*(constant), and because the resource usage constraints are computed by comparing the sum of (changing cells)*(constants) to a constant.

With the Solver Parameters dialog box filled in, click Solve, and we have the results. Microsoft can obtain a maximum NPV of $9,293 million ($9.293 billion) by choosing projects 2, 3, 6–10, 14–16, 19, and 20.

Top of Page Top of Page

Handling other constraints

Sometimes project-selection models have other constraints. For example, suppose that if we select project 3, we must also select project 4. Because our current optimal solution selects project 3 but not project 4, we know that our current solution can’t remain optimal. To solve this problem, simply add the constraint that the binary changing cell for project 3 is less than or equal to the binary changing cell for project 4.

You can find this example on the worksheet named IF 3 Then 4 in the file CapBudget.xls, which is shown in the following figure. Cell L9 refers to the binary value related to project 3, and cell L12 to the binary value related to project 4. By adding the constraint L9<=L12, if we choose project 3, L9 equals 1, and our constraint forces L12 (the project 4 binary) to equal 1. Our constraint must also leave the binary value in project 4’s changing cell unrestricted if we do not select project 3. If we do not select project 3, L9 equals 0, and our constraint allows the project 4 binary to equal 0 or 1, which is what we want. The new optimal solution is shown in the following figure.

Worksheet with new optimal solution

Now suppose that we can do only four projects from among projects 1 through 10. (See the worksheet titled At Most 4 Of P1-P10, shown in the following figure.) In cell L8, we compute the sum of the binary values associated with projects 1 through 10 by using the formula SUM(A6:A15). Then we add the constraint L8<=L10, which ensures that at most 4 of the first 10 projects are selected. The new optimal solution is shown in the following figure. The NPV has dropped to $9.014 billion.

Worksheet with optimal solution limited to four projects

Top of Page Top of Page

Problems

  1. A company has nine projects under consideration. The NPV added by each project, and the capital required by each project during the next two years, are shown in the following table. (All numbers are in millions.) For example, project 1 will add $14 million in NPV and require expenditures of $12 million during year 1 and $3 million during year 2. $50 million is available for projects during year 1, and $20 million is available during year 2.
NPV Year 1 Expenditure Year 2 Expenditure
Project 1 14 12 3
Project 2 17 54 7
Project 3 17 6 6
Project 4 15 6 2
Project 5 40 30 35
Project 6 12 6 6
Project 7 14 48 4
Project 8 10 36 3
Project 9 12 18 3
    • If we can’t undertake a fraction of a project, but must undertake either all of a project or none of a project, how can we maximize NPV?
    • Suppose that if project 4 is undertaken, project 5 must also be undertaken. How can we maximize NPV?
  1. Microsoft Press is trying to determine which of 36 books it should publish this year. The file PressData.xls gives the following information about each book:
    • Projected revenue and development costs (in thousands of dollars)
    • Pages in each book
    • Whether each book is geared toward an audience of software developers (indicated by a 1 in column E)

Microsoft Press can publish books totaling up to 8,500 pages this year, and must publish at least 4 books geared toward software developers. How can Microsoft Press maximize its profit?

Top of Page Top of Page

 
 
Applies to:
Excel 2003