Math for Business Operations

Applied Accounting with Excel

UNIT02 - Lesson 8

PBL Milestone 1: Project Definition

55 minutes
Lesson Overview

Lesson Focus

Define problem, scope, success metrics; plan Excel model and risks

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: Framing & Objectives
8 minutes

Connect unit driving question to milestone goals

Details:

  • Business Objectives: speed, accuracy, investor/client trust
  • Excel Objectives: XLOOKUP/SWITCH, named ranges, validation, dashboard plan
Activity 2: Plan Drafting
20 minutes

Teams write problem statement, scope, stakeholders, success metrics

Details:

  • Define acceptance criteria
  • List data sources and file naming convention
  • Outline workbook tabs and validation strategy
Activity 3: Workbook Skeleton
15 minutes

Create tabs, named ranges, and initial validations

Details:

  • Tabs: data, mapping, journal, reports, dashboard
  • Named ranges for inputs and lookups
  • Basic validation rules and error flags
Activity 4: Dataset Assignment & Check-In
12 minutes

Download g1–g6 datasets; instructor milestone review

Details:

  • Each team selects one dataset (consistent schema)
  • Checklist verification: plan + skeleton + risks
  • Rubric reviewed: technical accuracy, rationale, clarity, timing, Q&A
Required Materials
  • Milestone 1 checklist template
  • Unit datasets (g1–g6, CSV)
  • Workbook skeleton guide (tabs/named ranges)
  • Risk/assumption log
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