digest
Short Summary
Main recommendation: Use binary decision variables (1 = take the project, 0 = reject it) in an Excel Solver linear program to pick the best combination of four projects under yearly capital limits. The video's example, from JAL Boomerangs, sets all four projects to 1 and lets Solver switch off the ones that break the budget, selecting the CNC router, new van, and research project while rejecting the new paint room equipment.
Key Points
- A binary variable is a yes/no switch: 1 means the project is taken, 0 means it is not, so the model can exclude projects outright.
- The objective function is the total of each project's amount multiplied by its binary variable, computed with SUMPRODUCT. He notes all four projects have positive net present value, which is why the goal is to take as many as the budget allows while maximizing the objective.
- Each yearly constraint uses the same SUMPRODUCT pattern, with the binary range locked by F4 so it stays fixed when copied down, and limits total capital for years one to four to 40, 50, 40, and 35 thousand.
- The decision variables must be restricted to binary values in Solver, which is the key step that distinguishes this model from continuous or integer linear programming.
Details Worth Noting
- Concrete instructions:
- Define one binary cell per project (for example C, V, R, P) and enter a starting value of 1.
- Build the objective function as =SUMPRODUCT(project amounts row, binary variables row), with both ranges one row by four columns.
- For each year's constraint, enter =SUMPRODUCT(project amounts for that year, binary variables), lock the binary range with F4 (absolute reference), and copy the formula down to all four years.
- Open Data, Solver. Set the objective cell to maximize by changing the binary variable cells. Check "Make unconstrained variables non-negative" and select the Simplex LP method.
- Add the constraints as one array: the four constraint cells less than or equal to the four capital limits (40,000; 50,000; 40,000; 35,000).
- Add a binary constraint on the decision variables, click Add, then Cancel to finish, and click Solve.
- To verify, change one binary value to 0 and confirm the objective updates (the example shows 157,000 falling with the change), then sum the selected projects' values in the status bar (the example shows 27,000 for the chosen total).
- The example's selected solution takes the CNC router, the new van, and the research project; the new paint room equipment is rejected.
- Net present value is the stated selection criterion, and the video notes that all four projects have positive NPV, so the model's job is to maximize total value within the capital constraints.
- The workbook (BI 348 Chapter 8) is linked below the video and covers Chapters 8 and 9 of the course on linear programming with Excel Solver.
Bottom Line
Binary variables let Excel Solver make true yes-or-no project selections under resource limits, and setting them up correctly with SUMPRODUCT and the binary constraint turns a budget-allocation problem into an automatically solved decision.
