Teacher Resources
Month-End Wizard
What automation can cut our month-end closing time from two days to two hours without sacrificing GAAP accuracy?
Duration: 2-3 weeksDifficulty: Intermediate
Driving Question
Students will experience the real cost of slow month-end closes and learn to build automated solutions that maintain accounting accuracy while dramatically improving efficiency.
What automation can cut our month-end closing time from two days to two hours without sacrificing GAAP accuracy?
Performance Task
This mirrors real-world business scenarios where CFOs seek automation solutions to improve efficiency and reduce the risk of manual errors in critical financial processes.
Innovation Fair Demo: Month-End Wizard v1.0
Present your automated month-end closing system to Innovation Fair visitors, demonstrating how it reduces closing time from days to hours while maintaining GAAP compliance.
- Live Excel demonstration showing macro automation in action
- Clear explanation of time savings achieved (target: under 2 hours)
- Professional presentation to diverse audience (teachers, parents, entrepreneurs)
- User-testing feedback collection and analysis
- Documentation of GAAP compliance maintained throughout automation
Assessment Milestones
Aligned to implemented student lessons
Day 3: Four Scenarios Mapped
MilestoneSuccessfully map four adjusting-entry scenarios into ledger format
- All four adjusting-entry scenarios correctly identified and categorized
- Proper debit/credit mappings following GAAP principles
- Clear documentation of accruals, deferrals, and depreciation entries
- Peer review validation using provided rubric
Day 5: Scenario Engine + Validation Installed
MilestoneAdvanced month-end automation built with XLOOKUP mapping, SWITCH/IFS routing, and robust validation
- XLOOKUP maps AccountID to Method with if_not_found handling
- SWITCH/IFS logic selects correct method and computes adjusted values
- Validation flags missing/invalid mappings and out-of-range amounts
- Aggregations (SUMIF/SUMPRODUCT) accurately summarize by criteria
Day 8: Time < 2 Hours & UI Button
MilestoneComplete system achieving target efficiency with professional interface
- Month-end close simulation completed in under 2 hours
- User-friendly button interface for macro execution
- Professional formatting and error-flag system implemented
- System handles edge cases and provides clear user feedback
Lessons (10)
Click a day to open the detailed lesson plan
Day 1
Launch & Explore: CFO's Month-End Challenge
Introduction to month-end closing problems through authentic business perspective
45 minutes
Day 2
Skill Introduction: Accruals, Deferrals & SLN Depreciation
Direct instruction on GAAP-compliant adjusting entries and depreciation calculations
45 minutes
Day 3
Guided Practice: Four Scenarios into Ledger
Application of adjusting entry knowledge to systematic ledger recording
45 minutes
Day 4
Excel Tables & SUMIF: Building the Month-End Wizard Foundation
Hands-on Excel Tables with structured references and SUMIF automation
45 minutes
Day 5
Advanced Month-End Automation: Scenario Engine & Validation
Build XLOOKUP mapping, SWITCH/IFS routing, and robust validation
45 minutes
Day 6
Advanced Automation: Closing Entries & VBA Basics
Introduction to Excel automation tools and closing entry procedures
45 minutes
Day 7
Time-to-Close Simulation: Testing Automation
Full system testing and time measurement against 2-hour target
45 minutes
Day 8
PBL Milestone 1: Project Definition
Define problem, scope, success metrics; plan Excel model and risks
55 minutes
Day 9
PBL Milestone 2: Prototype + Rehearsal
Working prototype, validations passing, documented tests; rehearsal and peer feedback
60 minutes
Day 10
PBL Milestone 3: Presentations + Peer Review
Final demo delivery with rubric‑aligned evaluation and reflection
45 minutes
Prerequisite Resources
Files referenced in student lessons
Unit 2 Advanced Practice CSVdownload
Adjusting-Entry Scenarios PDFdownload
Macro Recorder Guidedownload
VBA Cheat-Sheetdownload
User-Testing Survey Formexternal