Math for Business Operations

Applied Accounting with Excel

UNIT03 - Lesson 8

Presentation Prep: KPI Dashboard Design

45 minutes
Lesson Overview

Lesson Focus

Design interactive KPI dashboard with charts, sparklines, and financial ratios

Key Unit Objectives

Enduring Understandings:

  • Financial statements tell the complete story of business performance and position
  • Integrated financial statements reveal connections between profit, balance sheet strength, and cash flow
  • Dynamic dashboards transform raw financial data into actionable business insights
  • Professional financial communication builds investor confidence and trust

Lesson Activities

Activity 1: KPI Selection Strategy
10 minutes

Choose 3-4 key performance indicators most relevant to business story

Details:

  • Review common financial ratios: Current Ratio, Return on Assets, Debt-to-Equity
  • Connect to TechStart context: Which ratios best tell Sarah's business story?
  • Consider investor perspective: What metrics demonstrate business health and growth potential?
  • Teams select their 3-4 KPIs based on their chosen industry focus
Investor-Focused KPI Selection

Choose KPIs that tell compelling story about business performance and potential

  • Profitability: Gross Margin, Net Margin, Return on Assets
  • Liquidity: Current Ratio, Quick Ratio, Cash Runway (months)
  • Efficiency: Asset Turnover, Receivables Days, Inventory Turnover
  • Growth: Revenue Growth Rate, Customer Acquisition trends
Activity 2: Dashboard Design & Charts
20 minutes

Create visual dashboard with charts and sparklines for selected KPIs

Details:

  • Build charts showing KPI trends over time using Excel charting tools
  • Create sparklines for compact visualization of performance trends
  • Implement conditional formatting for KPI status indicators (red/yellow/green)
  • Design professional layout with clear headings and intuitive organization
Activity 3: Interactive Features Implementation
12 minutes

Add interactive elements like dropdown menus and scenario analysis

Details:

  • Create dropdown menus for scenario selection (best case, worst case, most likely)
  • Build data validation lists for interactive filtering
  • Add dynamic chart titles that update based on user selections
  • Test all interactive features to ensure smooth user experience
Activity 4: Dashboard Usability Review
3 minutes

Test dashboard functionality and prepare for storyboard layout

Details:

  • Teams test all dashboard features with sample data changes
  • Verify charts update correctly when underlying data changes
  • Check professional appearance and investor-appropriate formatting
  • Preview Day 9: Storyboard layout and investor presentation preparation
Required Materials
  • KPI dashboard design guide with chart examples
  • Excel charting and sparkline tutorials
  • Interactive features implementation guide
  • Professional dashboard design examples
Differentiation Strategies

For Struggling Students

  • • Formula Templates: Provide INDEX/MATCH syntax with specific cell references pre-filled
  • • Step-by-Step Guides: Visual walkthroughs for each statement construction process
  • • Simplified KPIs: Focus on 2-3 basic ratios rather than complex dashboard features
  • • Peer Support: Pair with students strong in Excel for collaborative construction

For Advanced Students

  • • Advanced Features: Implement dropdown menus, scenario analysis, and dynamic charting
  • • Industry Customization: Research industry-specific KPIs and benchmarking data
  • • Teaching Roles: Support struggling classmates with formula construction and troubleshooting
  • • Extension Projects: Create automated reporting features or basic VBA functionality

For English Language Learners

  • • Financial Vocabulary Support: Glossary with visual definitions of key accounting terms
  • • Template Scaffolding: Pre-structured templates with clear headings and organization
  • • Visual Learning Aids: Flowcharts showing statement relationships and data flow
  • • Collaborative Support: Mixed-language team structures for peer translation and explanation