Financial Statements, Ratios, & Financial Analytics in Excel

Learn to build a profit and loss statement in Power Pivot using pivot tables, hierarchies, and filters to separate P&L from balance sheet, with grand totals and subtotals.

Slice the profit and loss by country using dynamic Excel slicers, filtering a consolidated P&L by territory to compare countries and add quarter or month slicers for detailed analysis.

Learn to calculate gross profit and EBITDA using pivot tables and Power Pivot, applying subclass level filters to isolate sales, cost of sales, and operating expenses.

Calculate operating profit, PBIT, and net profit using trading and operating accounts with chart of accounts and P&L filters. Verify results with balances and pivot tables.

Learn to synchronize slicers with multiple pivot tables using report connections, so selecting Australia updates the profit and loss figures and all pivots.

Discover how DAX opens advanced financial analysis in Power Pivot by creating measures, referencing tables and columns, and performing sums beyond standard value field options.

Learn to use the calculate function in DAX to create measures like total sales, gross profit, and EBITDA by applying filters such as subclass = sales in a pivot table.

Apply the calculate function to create a gross profit measure filtered to the trading account in the chart of accounts, then add it to the pivot table alongside sales.

Learn to compute EBITDA from gross profit minus operating expenses, using Power Pivot measures and subclass filtering, then extend to operating profit using depreciation and amortization.

Calculate PBIT and net profit from operating profit by deducting non-operating items using DAX measures. Build a cleaner dashboard with class-based filtering and pivot tables.

Calculate gross profit margin, operating profit margin, and net profit margin using DAX measures in Excel, format results as two-decimal percentages, and apply to pivot tables.

Create measures for operating profit margin and net profit margin, format as percentages with two decimals, and analyze results in pivot table in Excel using slicers for country and quarter.

Advance toward balance sheet preparation by mastering assets, capital employed, and equity, and applying DAX formulas to connect figures from the profit and loss statement to the balance sheet.

Explore the pivot table’s default behavior with dates, highlighting period values and total-to-date balance sheet totals, and learn how DAX can adjust calculations.

Create a pivot table with sales data, organizing dates by year and month. Apply YTD, QTD, and MTD with DAX and a calendar table for total-to-date balance sheet values.

Apply DAX to calculate the minimum and maximum dates per quarter in a pivot table, creating new measures in Power Pivot and using a calendar date column.

Learn to compute the minimum date across all data using DAX, removing row level filters with the all function to analyze from inception to date for balance sheet calculations.

Write a DAX total to date value measure for balance sheet assets using dates between, min and max dates from the calendar table, and filter the pivot to assets.

Prepare a balance sheet in Excel using a pivot table with chart of accounts and report filters, arranging data by date and by class, subclass, account, and sub account.

Learn to calculate asset turnover by building a ratios sheet with pivot tables, defining total assets via DAX, and computing sales over assets to analyze profitability.

Compute return on capital employed by dividing PBIT by capital employed, defined as shareholder's equity plus long-term liabilities (excluding current liabilities) using Power Pivot measures in Excel.

Compute return on equity by dividing net profit by total equity, using a DAX measure in Power Pivot with SubClass filter for Owner's Equity and format ROE as a percentage.

Compute gearing in Excel by calculating total debt from liabilities and dividing by equity, then format as a two-decimal percentage in the pivot table.

Calculate the interest cover ratio in Excel using PBIT divided by interest expense, creating a Power Pivot measure at the subclass level and validating the result against the 2018 P&L.

Learn to calculate the current ratio by defining current assets and current liabilities in Power Pivot, then add the ratio to a pivot table for analysis.

Explore calculating the quick ratio in excel by using current assets minus inventory divided by current liabilities, with inventory defined at the account level and proper formatting.

Learn to compute the receivables collection period with closing receivables or average, by dividing receivables by total sales and multiplying by 365, using trade receivables by subaccount in Excel.

Define payables payment period by dividing payables value by cost of sales, using account-level measures and total value in Power Pivot with period filters.

Compute the inventory turnover period in Excel by dividing inventory by cost of sales, adjusting the sign, and formatting to two decimals. Explore year and country effects with a slicer.

Create a trial balance from the general ledger in Excel by using a data model pivot table and a TB value measure that combines P&L and balance sheet data.

Double-click a value in a power pivot report to drill down into the transactions, using slicers to filter by year, account, and country, and balance sheet transactions.

Build a ledgers report in Excel using a data model pivot table to replicate the general ledger, including entry number, date, subaccount, and amount, with country and year slicers.

Refine the cash flow statement structure with the indirect method, starting from profit before tax and adjusting for non-cash items and working capital changes across operating, investing, and financing activities.

Define a cash flow statement structure in Excel by aligning the chart of accounts with balance sheet and profit and loss, then connect to the GL and map cash flows.

Explore how to build cash flow reports with period and total-to-date values, sign adjustments, and opening/closing balances, alongside profit and loss statements and the balance sheet.

Build a data model in Excel with Power Pivot to support a cash flow structure by linking the cash flow statement to chart of accounts and GL via account keys.

Learn to set up a cash flow statement in Excel with a pivot table, arranging start and end cash and the operating, investing, and financing sections.

Create a new cash flow measure in power pivot using the previous ftp value, linked tables, and P&L checks.

Define and validate positive and negative FTP cash flow measures using period calculations, filters on GL amount, and a structured cash flow statement for dynamic financial reporting.

Explore building a cash flow statement value measure in Power Pivot, defining FTP and CS adjustments, with positive and negative FTP values, excluding opening and closing balances.

Calculate the closing balance of cash and cash equivalents for the cash flow statement using a dynamic measure with dates between and a balance sheet verification.

Demonstrate calculating the opening balance for a cash flow statement using the minimum date minus one to pull the prior year ending balance in Power Pivot.

Learn to verify cash flow values by quickly checking closing balances across operating, investing, and financing activities and cross-checking with the profit and loss statement and balance sheet.

Style the cash flow statement in a pivot table by hiding panels and gridlines, adding subtotals for operating, investing, and financing, and enabling slicer responsiveness through report connections.

Learn how to prepare and present a statement of changes in equity, detailing opening and closing balances, share capital, share premium, retained earnings, dividends, and reserves, using advanced excel.

Define the soce structure to feed into your data model for statement of changes in equity in Excel, detailing balance at the beginning, policy changes, income, dividends, and retained earnings.

Add the statement of changes in equity to the data model with Power Pivot, and link it to the chart of accounts to enable filtering and cross-table interaction in Excel.

Set up a pivot table on a new worksheet to build statement of changes in equity, arranging balance at beginning and end, changes in accounting policy, profit, and share capital.

Define new DAX measures for the statement of changes in equity using Power Pivot, verify ftb and dtd values, and confirm closing balances against the balance sheet.

Create a master measure in Power Pivot to derive associated values for the statement of changes in equity, applying filters for closing, opening, negative, and positive balances under FTB conditions.

Add opening balances to the statement of changes in equity by building an opening balance SOCE measure in Excel, using filters, min date logic, and pivot table options.

Add slicers to the statement of changes in equity to filter by country and time, connect them to the dashboard, and verify consistency with the balance sheet.

Discover how to transform financial data into visual dashboards that reveal profitability, cost management, and trends across countries and years, enabling quick performance insights.

build a dynamic financial performance dashboard in excel by creating a pivot table, applying conditional formatting, and calculating year-on-year growth with sales, gross profit, and margins.

Extend the dashboard with EBITDA, operating profit, and margins, format margins as percentages with two decimals, apply conditional formatting, and add asset turnover and net profit trend charts.

Create a dynamic financial performance dashboard in Excel by adding pivot chart visualizations, switching to line charts for country trends, and configuring region- and time-based slicers with report connections.