Excel deliverables
The brief requires it: "Your report should include tables and/or figures developed using MS
Excel to visualize data and support your arguments." Rubric item 8 marks the use of visuals.
This is not optional decoration.
Build the workbook
py .claude/skills/excel-deliverables/scripts/build_workbook.py
Writes 02-Analysis/DXB-Calculations.xlsx with seven sheets:
| Sheet |
Contents |
1-Data |
The provided series — the only hard-typed numbers in the file |
2-Passengers |
Six forecasting methods, full error columns, CFE/MAD/MSE/MAPE/TS, accuracy comparison, 2026 forecast, two charts |
3-Movements |
The same for flight movements |
4-Associative |
Passengers regressed on movements; SLOPE/INTERCEPT/RSQ/STEYX; scatter with fitted line; expected traffic at 500k/550k/600k |
5-Long-Term |
Projections to 2036 on three fitting bases, with a comparison chart |
6-Weighted-Factor |
Location decision template with SUMPRODUCT scoring |
7-Capacity-Gap |
Forecast demand against the 115m design capacity, with gap and utilisation columns |
Options: --capacity, --long-to, --at, -o.
Open it in Excel and press F9 before using anything from it. openpyxl writes formulas but
does not evaluate them; the cached values are empty until Excel calculates. A screenshot taken
before recalculation will show zeros or blanks.
Why live formulas, not pasted values
- The brief says the visuals must be developed using Excel. A sheet of typed numbers is not
development; a sheet of formulas is
- Change one input — an alpha, a weight, the capacity assumption — and everything downstream
updates. During revision this saves hours and prevents the classic error of a report whose
tables no longer agree with each other
- A marker who opens the workbook can follow the arithmetic. Auditability is credibility
- It removes transcription error, which is the most common source of a number in the prose
that does not match the number in the appendix
The Excel functions this report needs
| Purpose |
Function |
| Moving average |
=AVERAGE(B2:B4) |
| Weighted moving average |
=$B$23*B4+$C$23*B3+$D$23*B2 — weights in named parameter cells, never typed inline |
| Exponential smoothing |
=C4+$alpha*(B4-C4) |
| Linear trend forecast |
=TREND(known_y, known_x, new_x) or =FORECAST.LINEAR(x, known_y, known_x) |
| Regression slope / intercept |
=SLOPE(y,x) · =INTERCEPT(y,x) |
| Correlation and fit |
=CORREL(x,y) · =RSQ(y,x) |
| Standard error of estimate |
=STEYX(y,x) |
| CFE |
=SUM(error_range) |
| MAD |
=AVERAGE(abs_error_range) |
| MSE |
=AVERAGE(squared_error_range) |
| MAPE |
=AVERAGE(ape_range) |
| Tracking signal |
=CFE_cell/MAD_cell |
| Weighted factor total |
=SUMPRODUCT(weights, scores) |
| Best model by a metric |
=INDEX(names, MATCH(MIN(metric_range), metric_range, 0)) |
Put every parameter — alphas, weights, capacity, the effective-capacity ratio — in its own
labelled cell and reference it absolutely. Parameters buried inside formulas cannot be
sensitivity-tested, and sensitivity testing is where the marks are.
Charts for the report
Four earn their place. More than that and the 2,500 words disappear under captions.
| Figure |
Type |
Shows |
| Actual passenger traffic 2013–2025 |
Line |
The series and, unmistakably, the COVID break |
| Forecast accuracy by method |
Column |
MAPE or MAD side by side — makes the model choice visual |
| Passengers against flight movements |
Scatter + trendline |
The associative relationship, with r² displayed |
| Forecast demand vs 115m design capacity to 2036 |
Line, capacity as a horizontal reference |
The gap and the crossing year — this is the money chart for the COO |
Chart discipline:
- Title every chart with what it shows, not "Chart 1"
- Label both axes, with units
- Show the trendline equation and R² on the scatter (Excel: Add Trendline → Display equation
and R-squared)
- Keep the palette restrained — two or three colours. Screenshots go into a black-and-white-
printable report
- No 3D effects, no gradient fills, no chart junk
Getting Excel output into the report
- Recalculate (F9)
- Copy the chart or table range
- Paste into Word as a picture — Paste Special → Picture (Enhanced Metafile). It will not
reflow, break, or lose its formatting when the document is converted to PDF
- Add a numbered caption below: Figure 3: Forecast passenger demand against DXB design
capacity, 2026–2036. Source: author's calculations from Dubai Airports (2026).
- Refer to it in the text: "Figure 3 shows…". An unreferenced figure is decoration
For tables that must remain editable text — the weighted factor model, the accuracy
comparison — rebuild them as native Word tables so they are searchable and so the borders
follow the document standard. Keep the numbers identical to the workbook.
What goes in the appendices
Main text: the summary comparison table, and the three or four figures above.
Appendices: full method-by-method workings, every error column, the complete long-term
projection table on all three bases, the full weighted factor matrix with justifications,
and any sensitivity runs. Appendices do not count toward the 2,500 words — use them.
Label appendices A, B, C and refer to each from the main text: "the full workings are at
Appendix B".
The weighted factor sheet is a template
6-Weighted-Factor ships with placeholder weights and every score set to 5. Replace all of
it. The factors listed are plausible starting points, not answers, and the justification
column is empty on purpose. Weights that arrive unexamined from a template are exactly what
the brief warns against when it says weightings "must reflect Dubai Airports' specific
strategic priorities, not generic ones".
Before the workbook is used
1---2name: excel-deliverables3description: Building the MS Excel workbook the MGT4897 brief requires — live-formula forecasting sheets, error-metric comparisons, the associative regression, the weighted factor model, and the capacity gap analysis, plus the charts that go into the report. Covers the Excel functions to use, how to present tables and figures in the report, and why formulas beat pasted values. Use when producing any table or figure for the report.4---56# Excel deliverables78The brief requires it: *"Your report should include tables and/or figures developed using MS9Excel to visualize data and support your arguments."* Rubric item 8 marks the use of visuals.10This is not optional decoration.1112## Build the workbook1314 py .claude/skills/excel-deliverables/scripts/build_workbook.py1516Writes `02-Analysis/DXB-Calculations.xlsx` with seven sheets:1718| Sheet | Contents |19|---|---|20| `1-Data` | The provided series — the only hard-typed numbers in the file |21| `2-Passengers` | Six forecasting methods, full error columns, CFE/MAD/MSE/MAPE/TS, accuracy comparison, 2026 forecast, two charts |22| `3-Movements` | The same for flight movements |23| `4-Associative` | Passengers regressed on movements; SLOPE/INTERCEPT/RSQ/STEYX; scatter with fitted line; expected traffic at 500k/550k/600k |24| `5-Long-Term` | Projections to 2036 on three fitting bases, with a comparison chart |25| `6-Weighted-Factor` | Location decision template with SUMPRODUCT scoring |26| `7-Capacity-Gap` | Forecast demand against the 115m design capacity, with gap and utilisation columns |2728Options: `--capacity`, `--long-to`, `--at`, `-o`.2930**Open it in Excel and press F9 before using anything from it.** openpyxl writes formulas but31does not evaluate them; the cached values are empty until Excel calculates. A screenshot taken32before recalculation will show zeros or blanks.3334## Why live formulas, not pasted values35361. The brief says the visuals must be *developed using Excel*. A sheet of typed numbers is not37 development; a sheet of formulas is382. Change one input — an alpha, a weight, the capacity assumption — and everything downstream39 updates. During revision this saves hours and prevents the classic error of a report whose40 tables no longer agree with each other413. A marker who opens the workbook can follow the arithmetic. Auditability is credibility424. It removes transcription error, which is the most common source of a number in the prose43 that does not match the number in the appendix4445## The Excel functions this report needs4647| Purpose | Function |48|---|---|49| Moving average | `=AVERAGE(B2:B4)` |50| Weighted moving average | `=$B$23*B4+$C$23*B3+$D$23*B2` — weights in named parameter cells, never typed inline |51| Exponential smoothing | `=C4+$alpha*(B4-C4)` |52| Linear trend forecast | `=TREND(known_y, known_x, new_x)` or `=FORECAST.LINEAR(x, known_y, known_x)` |53| Regression slope / intercept | `=SLOPE(y,x)` · `=INTERCEPT(y,x)` |54| Correlation and fit | `=CORREL(x,y)` · `=RSQ(y,x)` |55| Standard error of estimate | `=STEYX(y,x)` |56| CFE | `=SUM(error_range)` |57| MAD | `=AVERAGE(abs_error_range)` |58| MSE | `=AVERAGE(squared_error_range)` |59| MAPE | `=AVERAGE(ape_range)` |60| Tracking signal | `=CFE_cell/MAD_cell` |61| Weighted factor total | `=SUMPRODUCT(weights, scores)` |62| Best model by a metric | `=INDEX(names, MATCH(MIN(metric_range), metric_range, 0))` |6364Put every parameter — alphas, weights, capacity, the effective-capacity ratio — in its own65labelled cell and reference it absolutely. Parameters buried inside formulas cannot be66sensitivity-tested, and sensitivity testing is where the marks are.6768## Charts for the report6970Four earn their place. More than that and the 2,500 words disappear under captions.7172| Figure | Type | Shows |73|---|---|---|74| Actual passenger traffic 2013–2025 | Line | The series and, unmistakably, the COVID break |75| Forecast accuracy by method | Column | MAPE or MAD side by side — makes the model choice visual |76| Passengers against flight movements | Scatter + trendline | The associative relationship, with r² displayed |77| Forecast demand vs 115m design capacity to 2036 | Line, capacity as a horizontal reference | The gap and the crossing year — this is the money chart for the COO |7879Chart discipline:8081- Title every chart with what it shows, not "Chart 1"82- Label both axes, with units83- Show the trendline equation and R² on the scatter (Excel: Add Trendline → Display equation84 and R-squared)85- Keep the palette restrained — two or three colours. Screenshots go into a black-and-white-86 printable report87- No 3D effects, no gradient fills, no chart junk8889## Getting Excel output into the report90911. Recalculate (F9)922. Copy the chart or table range933. Paste into Word **as a picture** — Paste Special → Picture (Enhanced Metafile). It will not94 reflow, break, or lose its formatting when the document is converted to PDF954. Add a numbered caption below: *Figure 3: Forecast passenger demand against DXB design96 capacity, 2026–2036. Source: author's calculations from Dubai Airports (2026).*975. Refer to it in the text: "Figure 3 shows…". An unreferenced figure is decoration9899For tables that must remain editable text — the weighted factor model, the accuracy100comparison — rebuild them as native Word tables so they are searchable and so the borders101follow the document standard. Keep the numbers identical to the workbook.102103## What goes in the appendices104105Main text: the summary comparison table, and the three or four figures above.106107Appendices: full method-by-method workings, every error column, the complete long-term108projection table on all three bases, the full weighted factor matrix with justifications,109and any sensitivity runs. Appendices do not count toward the 2,500 words — use them.110111Label appendices A, B, C and refer to each from the main text: "the full workings are at112Appendix B".113114## The weighted factor sheet is a template115116`6-Weighted-Factor` ships with placeholder weights and every score set to 5. **Replace all of117it.** The factors listed are plausible starting points, not answers, and the justification118column is empty on purpose. Weights that arrive unexamined from a template are exactly what119the brief warns against when it says weightings *"must reflect Dubai Airports' specific120strategic priorities, not generic ones"*.121122## Before the workbook is used123124- [ ] Opened in Excel and recalculated — no `#REF!`, `#DIV/0!`, `#VALUE!` anywhere125- [ ] Weights on the weighted factor sheet sum to exactly 1.00126- [ ] Every placeholder score and justification replaced127- [ ] Numbers in the report match the workbook exactly128- [ ] Charts titled, axes labelled, sources stated129- [ ] Assumptions labelled as assumptions on the sheet, not presented as data130- [ ] Submit the workbook alongside the report if the module accepts supporting files —131 check the Turnitin link, since Turnitin usually accepts only one document
Faaz17