Overview

Creating a financial dashboard in Excel from a trial balance involves several key steps, from data preparation to calculating and visualizing executive-level key performance indicators (KPIs). A well-structured dashboard allows leaders to quickly assess financial health, track performance, and make informed decisions without wading through detailed reports. 

This webinar will show you how to make a dashboard from your financial data.

Areas Covered in the Session

• Prepare the data
  
• Ensure the trial balance is balanced
  
• Categorize accounts
  
• Create a PivotTable for the Income Statement:
  
• Create a PivotTable for the Balance Sheet:
  
• Calculate executive-level KPIs
  
  • Gross Profit Margin: $=(Gross~Profit) / (Total~Revenue)
    
  • Operating Profit Margin: $=(Operating~Income) / (Total~Revenue)
    
  • Net Profit Margin: $=(Net~Income) / (Total~Revenue)
    
  • Liquidity KPIs:
    
  • Current Ratio: $=(Current~Assets) / (Current~Liabilities)
    
  • Quick Ratio: $=(Current~Assets - Inventory) / (Current~Liabilities)
    
  • Efficiency KPIs:
    
  • Days Sales Outstanding (DSO): $=(Accounts~Receivable / Total~Revenue) * Number~of~Days
    
  • Inventory Turnover Ratio: $=(Cost~of~Goods~Sold) / (Average~Inventory) 
    
• Create a PivotTable for the Income Statement:
  
• Create a PivotTable for the Balance Sheet:
  
• Design and build the dashboard
  
  • Charts
    
  • Slicers
    
  • Timelines
    
  • Conditional Formatting
    
  • Form Controls
    
  • KPI’s
    

Who Will Benefit

• Business Owners 
  
• CEO's / CFO's / CTO's
  
• Managers of all levels
  
• Anybody who uses Excel on a regular basis, and want to be more efficient and productive
  
• Administrators
  
• Salespeople
  
• Trainers
  
• Bankers
  
• Office workers
  
• Finance Professionals
  

Speaker Profile