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.

Area Covered in 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

• 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