The Art of Modelling and Integer Programming
6.1 The Art of Spreadsheet Modelling: Everglade Cash Flow
This section is not about one model but about the act of modelling itself: how to start, build, test and analyse. The case concerns Everglade’s 10-year cash flow and loan decisions.
Case: Everglade’s Cash-Flow Problem
Everglade is developing new retirement communities. A weak market and low occupancy previously held the company back, but the market has recovered and prospects are strong. Everglade plans to build more projects, yet near-term cash outflows exceed inflows. Without borrowing, it cannot meet short-term cash requirements.
- It can apply for a 10-year long-term loan at an interest rate of 5%.
- It may also take one-year short-term loans in individual years at an expected rate of 7%.
Question: Which loan type or combination, and how much, should the company choose to maximise its cash balance after 10 years?
| Year | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 |
|---|---|---|---|---|---|---|---|---|---|---|
| Project cash flow (USD millions) | −8 | −2 | −4 | 3 | 6 | 3 | −4 | 7 | −2 | 10 |
Four Phases of Modelling
- Plan anchor the final objective: What must ultimately be achieved? What information should the report contain? In what form should the conclusion appear? Which key figures must support the recommendation?
- Build start with a simple version, make sure the logic is correct, and then extend it into the full model.
- Test enter known values in the changing cells: all zeros, all ones, very large values and values from manual calculations.
- Analysis analyse the model before running Solver, then solve for the optimum and finally interpret the results.
Calculation basis: the long-term loan is received once in 2018, principal is repaid at the end of 2027, and interest is paid annually at 5%; each short-term loan is received in its year and repaid with 7% interest the following year.
Build, Test and Analyse
Start complex models with a simple version first: confirm the logic, copy the formulas across later years, and extend to the complete model. Test with known values (all zeros, all ones, very large values and manual calculations), then use Solver.
Two Values from the Manual Trial
Using the assumptions above—long-term loan USD 6 million, 2018 short-term loan USD 2 million and 2019 short-term loan USD 5 million—enter year-end cash balances for 2018 and 2019 (USD millions; 5% tolerance).
6.2 Modelling Standards: What Makes a Good Model?
Modelling Guidelines
| Guideline | How to apply it |
|---|---|
| Enter base data first | Let data drive the model |
| Organise and label data clearly | Group data into tables, describe each item and state units |
| Enter each data item once | Reference the original data so a change is made in one place |
| Separate data and formulas | Do not embed constants in formulas; put values in data cells |
| Keep the model simple | Avoid overly complex functions; break long formulas into intermediate steps |
| Use range names | Use Define Name to improve readability, without overusing names |
| Use relative and absolute references appropriately | Write the base formula once and fill it across to reduce manual errors |
| Use colour to distinguish cell types | Blue fill for data, yellow for variables, orange bold border for the objective cell |
| Make the complete model visible | Keep all data and constraints visible in the sheet; group constraints in contiguous rows or columns |
Criteria for a High-Quality Model
- Users should quickly distinguish data, changing and objective cells.
- Every model element, including all constraints, should be visible in the worksheet without opening Solver.
- Every formula should be easy to understand without clicking through output cells.
6.3 Integer Programming: TBA Airlines’ Purchasing Problem
Why Linear Programming Is Not Enough: The Divisibility Assumption
Linear programming includes a divisibility assumption: decision variables may take any value, including fractions, as long as constraints are satisfied. But an airline cannot buy 3.33 aircraft—the quantity must be an integer, which violates divisibility. Restricting variables to integers gives an integer programme.
Case and Data
TBA Airlines is a small regional carrier focused on short-haul routes. It is deciding whether to expand by buying more small aircraft for short-haul routes, buying large aircraft to enter national routes, or both.
| Item | Small aircraft | Large aircraft | Available capital |
|---|---|---|---|
| Annual net profit per aircraft | USD 7 million | USD 22 million | — |
| Purchase cost per aircraft | USD 25 million | USD 75 million | USD 250 million |
| Maximum number | 5 | — | — |
Maximise Profit = 7S + 22L (USD millions)
Constraints: 25S + 75L ≤ 250; S ≤ 5; S ≥ 0, L ≥ 0 S and L are integers
Find the Optimal Aircraft Purchase Plan
Enter the optimal numbers of small and large aircraft and maximum annual net profit (USD millions).
6.4 Applications of Binary Variables
Binary variables take only two values (0 or 1) and are ideal for “do it or not” decisions. This section presents two cases: scheduling and setup costs.
- Should we undertake a project?
- Should we make a fixed-size investment?
- Should we build a plant at a particular site?
Case 1: Crew Scheduling at Southwestern Airways
A company must assign three crews to 11 flights based at San Francisco (SFO). Each “flight sequence” is an ordered set of flights operated by one crew.How can three crews be assigned to three sequences so that all 11 flights are covered at minimum cost?
The table below maps 11 flights to 12 feasible sequences. A number shows the flight’s position in a sequence; a blank means the sequence does not include that flight.
Write Your Scheduling Plan
Enter the three selected sequence numbers (ascending, separated by spaces or commas) and total cost (USD thousands). There are two optimal solutions; either one is acceptable.
Case 2: Wyndor with Setup Costs
Now modify the original Wyndor problem in two ways. First, each product incurs a one-time setup cost regardless of quantity (doors USD 700; windows USD 1,300). Second, only one production batch is made this week, so D and W represent total output rather than production rates and must be integers.
| Production quantity | Net profit from doors | Net profit from windows |
|---|---|---|
| 0 | 0 | 0 |
| 1 | 1×300 − 700 = −400 | 1×500 − 1,300 = −800 |
| 2 | 2×300 − 700 = −100 | 2×500 − 1,300 = −300 |
| 3 | 3×300 − 700 = 200 | 3×500 − 1,300 = 200 |
| 4 | 4×300 − 700 = 500 | 4×500 − 1,300 = 700 |
| 5 | Infeasible (Plant 1 has only 4 hours) | 5×500 − 1,300 = 1,200 |
| 6 | Infeasible | 6×500 − 1,300 = 1,700 |
Maximise P = 300D + 500W − 700y₁ − 1,300y₂
Constraints: D ≤ 4; 2W ≤ 12; 3D + 2W ≤ 18 D ≤ 99y₁ W ≤ 99y₂ D and W are integers; y₁ and y₂ are binary
The constraints containing 99 implement the rule that setup costs occur only when production occurs: if y = 0, output is forced to zero; if y = 1, the output cap is loose enough to be non-binding.
Optimal Solution with Setup Costs
Enter optimal door output, window output and maximum net profit (USD).
6.5 AI and Model Standards
Task: ask AI for a scheduling recommendation, then verify every item.
① Describe the Southwestern Airways scheduling problem to AI and ask for crew assignments.
② First verify that each crew operates only one sequence, all 11 flights are covered, and no more than three crews are used.
③ Ask what changes if one crew is understaffed.
④ Discuss fairness, fatigue and regulatory limits in addition to efficiency.
⑤ Record which constraints AI ignored and how you detected the omissions.
The model finds the minimum-cost schedule, but real scheduling must also consider crew rest, fatigue, seniority and labour law—factors often absent from the objective. This section therefore stresses:an optimised result is not automatically an implementable decision.
Model-Standards Checklist
| Check | Requirement | Additional step when AI generates the model |
|---|---|---|
| Separate data and formulas | Do not hard-code values in formulas | Check whether parameters are embedded in formulas |
| Group and label data with units | Describe every data item | Verify units against the case |
| Enter each data item once | Reference original data elsewhere | Check for duplicate entry |
| Distinguish variables and objective by colour | Blue data, yellow variables, orange objective | Tell AI not to change the colour convention |
| Keep the model readable | Avoid stacked complex functions | Require formulas to be built step by step |
Find the Problems in This AI Scheduling Recommendation
Below is an AI scheduling recommendation (a teaching example). Select a sentence to mark it as “problematic,” then select Check. It contains four problems.
Verification framework: crew limit, complete coverage, cost definition and omitted human factors.
6.6 Summary and Assignment
- Four modelling steps:plan, build, test and analyse; when stuck, return to the question “what final objective must be achieved?”
- Three standards for a good model: clear data, simple formulas and visible structure.
- Integer and binary variables turn “whether,” “how many aircraft” and “how many crews” into mathematical expressions.
- Binary variables express setup costs, scheduling and location decisions:D ≤ 99y is the formulation for “output only if the activity occurs.”
- Fractional linear-programming relaxation solutions are often infeasible, and rounding does not necessarily produce an optimum.
≤ 99y constraints.
Assignment: compare AI’s scheduling recommendation with the model, verify every item, record ignored constraints and add a one- to three-sentence AI-use statement.
Appendix: Interactive Demonstrations and Classroom Exercises
| Location | Type | Content |
|---|---|---|
| 6.1 | Demonstration | Everglade manual-trial calculator: loan plan and year-end cash balances |
| 6.2 | Demonstration | Nine-item model self-check |
| 6.3 | Demonstration | TBA integer combinations compared with the LP relaxation |
| 6.4 | Demonstration | Southwestern Airways coverage matrix (select three sequences) |
| 6.4 | Demonstration | Setup Costs and Binary Variables |
| 6.1 | Exercise | Two values from the Everglade manual trial |
| 6.3 | Exercise | Find the Optimal Aircraft Purchase Plan |
| 6.4 | Exercise | Write Your Scheduling Plan |
| 6.4 | Exercise | Optimal Solution with Setup Costs |
| 6.5 | Exercise | Problems in an AI scheduling recommendation |
Data sources: model parameters from TBA.xlsx, SW Airways.xlsx and WyndorExtension.xlsx. Every optimum is computed by enumeration on the page: 19 feasible TBA plans, 12-sequence combinations for Southwestern and the integer combinations with setup costs.
Unit Glossary (English–Chinese)
| English | 中文 |
|---|---|
| Integer programming | 整数规划 |
| Binary variable | 二元变量 |
| Setup cost | 启动成本 |
| Covering constraint | 覆盖约束 |
| Scheduling | 排班 |
| Solver | 规划求解 |
| Sensitivity report | 敏感性报告 |
| Divisibility assumption | 可分性假设 |