What you'll learn
- Combine everything from Module 13 into one orchestrator
- Add a styled cover sheet with KPIs
- Schedule it to run on the 1st of each month
Project brief
Build a single script monthly_close.py that, when run, produces close_pack_YYYY-MM.xlsx containing:
- A cover sheet with 6 KPIs.
- Bank reconciliation (matched, GL-only, bank-only).
- Budget vs actual variance (with material-variance flags).
- AR aging by customer × bucket.
- USD-translated P&L by office.
Schedule it to run automatically on the 1st of every month and email the result to your controller.
Suggested file layout
monthly_close/
├── monthly_close.py ← the orchestrator
├── recon.py ← reconciliation function
├── variance.py ← variance function
├── aging.py
├── fx_pnl.py
├── style.py ← shared openpyxl styling
├── inputs/ ← raw files dropped here
└── outputs/ ← finished pack lands here
Acceptance criteria
- Runs end-to-end with one command:
python monthly_close.py.
- Cover sheet headline KPIs match the detail sheets.
- All numeric cells formatted as currency.
- Material variance rows have red fill.
- AR rows over 90 days have red fill; 60-90 yellow.
- Email lands in the controller's inbox automatically every 1st.
Stretch goals
- Add a "previous month vs this month" comparison column on each summary.
- Embed a 12-month revenue trend chart on the cover sheet.
- Save the pack to a SharePoint or Google Drive folder so it's auto-shared.
What to show off
When it's done: a 90-second screen recording showing you double-click the script, the file appears, you open it, scroll the cover sheet, click into one detail tab. That's your portfolio piece.
📹 Video walkthrough
A video walkthrough of this lesson will be embedded here. Until then, the
written walkthrough above mirrors what the video will cover step-for-step.