The best magazine
- 1). Type each project's name into the second column for each row. Start with row 6 to leave room at the top for other calculations.
- 2). Title the first column "Decision" and leave its content empty. The "Decision" column will be completed by Solver.
- 3). Enter each project's NPV in the third column for each row.
- 4). Type the budget and labor requirements for the three next years of each project into the next six columns of the spreadsheet. Sum the total of each of these six columns at the top of the columns using the summation button on the toolbar.
- 5). Type the total capital and labor available for all combined projects for each year at the top of each year column.
- 6). Build a formula in cell B2 to total the NPV of all selected projects. Use the formula "=SUMPRODUCT(doit,NPV)" where the "NPV" range refers to all the cells in the NPV column for each project. The "doit" range refers to all the cells in the decision column for each project.
- 7). Copy the formula in cell B2 to cells E2 through J2 so individual year's capital and labor requirements are calculated.
- 8). Launch Solver. Click on the "Tools" menu and choose the "Solver" command.
- 9). Click in the "Set Target Cell" field and then click on cell B2 where the total NPV formula exists.
- 10
Change the "Equal To" field to "Max." This tells Solver that the goal is to maximize the total NPV in cell B2, based on all projects. - 11
Click in the "By Changing Cells" field and then drag over all the cells in the Decision column created earlier. - 12
Click the Add button and enter the "doit" range or its direct cell references ("A6:A25"). Choose the "bin" option from the middle drop-down menu. Solver is now configured to enter a "0" or "1" in those cells only. - 13
Click the "Add" button and choose the cells E2 through J2. Select the "<=" indicator and choose cells E4 through J4 in the final field. This forces Solver to not choose a combination of projects that will exceed company resources. - 14
Press the "Solve" button. Solver will analyze all the data and produce a "1" next to each project that should be implemented to increase revenue during the following three years.
Source: ...