Problem 4: Using Solver to solve capital budgeting problems 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 is 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. During Year 1, $50 million in capital is available for projects, and $20 million is available during Year 2. - If you can't undertake a fraction of a project but must undertake either all or none of a project, how can you maximize the NPV? - Suppose that if Project 4 is undertaken, Project 5 must be undertaken. How can you maximize the NPV? Solver Skills/Tips: 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 , you do the project. If a binary changing cell that corresponds to a project equals 0 , you 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 choose Bin from the list in the Add Constraint dialog box. For example, if you add a column called "doit" in the above problem to indicate a solver decision in binary, you can code this constraint in solve as: Problem 4: Using Solver to solve capital budgeting problems 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 is 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. During Year 1, $50 million in capital is available for projects, and $20 million is available during Year 2. - If you can't undertake a fraction of a project but must undertake either all or none of a project, how can you maximize the NPV? - Suppose that if Project 4 is undertaken, Project 5 must be undertaken. How can you maximize the NPV? Solver Skills/Tips: 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 , you do the project. If a binary changing cell that corresponds to a project equals 0 , you 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 choose Bin from the list in the Add Constraint dialog box. For example, if you add a column called "doit" in the above problem to indicate a solver decision in binary, you can code this constraint in solve as