Course Description

If you’ve worked with Excel Power Pivot for any amount of time, you know that it has incredible potential to change your reporting game. But while building a simple sales report may be easy, have you struggled to re-create a full set of financial statements in DAX?

Let’s face it, generating your current month’s sales may be easy, but how do you display that next to year to date, or same period prior year? Then comes the balance sheet. While calculating a year-to-date balance for assets and liabilities is not super difficult, what about current earnings? How about retained earnings to the end of the prior period?

Next up comes everyone’s most dreaded statement: the Statement of Cash Flows. Many users find this hard enough to work out with a regular Excel spreadsheet, but how about using DAX Measures?

Finally, we’ll explore the Forecasted Actual statement, which allows you to dynamically pull from historical actual values – where they exist – and report budget (or forecast) for the months that haven’t happened yet.

In this course we will explore creating the key requirements behind building a robust set of financial statements. Concepts that will be covered include:

• Correctly laying out the chart of accounts for financial statement reporting.
• Building P&L measures which display Revenues and Expenses as positive values, subtotal correctly and include comparative period columns.
• Creating Balance Sheet measures which display as positive values, subtotal correctly, and calculate current earnings and retained earnings.
• Generating measures to correctly display the Statement of Cash Flows metrics, grouping them appropriately into their cash, non-cash and financing sub-groups.
• Architecting a forecasted actual statement that switches to display actual vs forecasted values.
• Following key DAX patterns for creating General Journal, General Ledger and Trial Balance reports.

This training is an expert level, hands-on Excel course which leverages Power Query and Power Pivot. Please see the “Who is the Course For” section below to ensure that you have the background knowledge required before attending.