Finance Dashboard Lab in Excel: From Trial Balance to Exec KPIs

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