Teacher Resources
UNIT02 - Lesson 6
Advanced Automation: Closing Entries & VBA Basics
45 minutes
Lesson Overview
Lesson Focus
Introduction to Excel automation tools and closing entry procedures
Key Unit Objectives
Enduring Understandings:
- Automation reduces human error and increases efficiency in financial processes
- GAAP compliance requires systematic tracking of accruals, deferrals, and adjusting entries
- User interface design determines the usability and adoption of financial tools
- Time savings through automation creates competitive advantage for businesses
Lesson Activities
Activity 1: Closing Entries: The Final Step
15 minutesDirect instruction on closing entry purpose and execution
Details:
- Purpose: Zero out temporary accounts (revenue, expenses) at period end
- Process: Close revenues to retained earnings, close expenses to retained earnings
- Post-closing trial balance: Only permanent accounts (assets, liabilities, equity) remain
- Connection to automation: Perfect candidate for macro automation
Closing Entry Sequence
Systematic process to reset temporary accounts
- Step 1: Close revenue accounts (debit revenues, credit retained earnings)
- Step 2: Close expense accounts (debit retained earnings, credit expenses)
- Step 3: Close dividend account if applicable (debit retained earnings, credit dividends)
- Result: Income statement accounts reset to zero for new period
Activity 2: Excel Automation Options: Macro Recorder vs. VBA
15 minutesIntroduction to Excel automation tools and student choice pathways
Details:
- Macro recorder: Point-and-click recording of manual actions
- Basic VBA: Simple code procedures for repetitive tasks
- Student choice: Teams decide between no-code recorder or basic coding approach
- Demonstrate both approaches using simple closing entry example
Automation Pathway Choice
Choose the approach that matches your team's technical comfort
- Macro Recorder: No coding required, records your manual actions
- Basic VBA: Simple IF statements and loops for more flexibility
- Hybrid Approach: Start with recorder, edit code for improvements
- Focus on functionality over complexity
Activity 3: Hands-On Automation Practice
10 minutesTeams begin building their chosen automation solution
Details:
- Macro recorder teams: Record closing entry sequence for their focus area
- VBA teams: Write basic procedure using provided templates
- Test automation with sample data to verify functionality
- Troubleshoot common issues with instructor support
Activity 4: Milestone 2 Assessment & Preview
5 minutesCheck progress toward macro functionality goal
Details:
- Teams demonstrate their automation progress
- Verify: Does macro successfully insert closing entries for their focus area?
- Preview Day 7: Time-to-close simulation and full system testing
Required Materials
- Closing entry procedure guide
- Macro recorder step-by-step tutorial
- Basic VBA templates and cheat sheet
- Sample datasets for automation testing
Differentiation Strategies
For Struggling Students
- • Step-by-Step Macro Guides: Visual walkthroughs for macro recorder usage
- • Pre-built Templates: Partial VBA code with clear modification instructions
- • Simplified Scenarios: Focus on one adjusting entry type before expanding
- • Peer Support: Pair with students strong in Excel automation skills
For Advanced Students
- • Advanced VBA Functions: Error handling routines and user input validation
- • Cross-Sheet Integration: Link multiple workbooks for comprehensive systems
- • Custom Functions: Write UDF (User Defined Functions) for complex calculations
- • Mentoring Role: Support other teams with technical troubleshooting
For English Language Learners
- • Visual Interface Design: Use icons and graphics to supplement text instructions
- • Technical Vocabulary Support: Accounting and Excel terms with visual definitions
- • Collaborative Documentation: Partner with native speakers for user instruction writing
- • Multiple Demonstration Formats: Video tutorials with visual emphasis over verbal explanation