Building Financial Statements in Excel with Power Pivot

Go back to Courses

Building Financial Statements in Excel with Power Pivot

On-Demand

Author: Ken Puls

Average Rating: (8 Reviews)

This advanced course explores the data structure and measures required to build financial statements in Excel using the DAX language. We'll review the required Chart of Account setup and follow this with key DAX patterns for creating Profit & Loss, Balance Sheet, Statement of Cash Flow, Expected Actuals statements, as well as General Journal, General Ledger and Trial Balance reports.

Course

Building Financial Statements in Excel with Power Pivot

$270 Renews at $69 per year
(All prices in USD)

Buy now

This Course contains:

12h of training content

12 Content Modules

Downloadable files and Handouts

Support and Discussion groups

Certificate of completion

Check our Group Discounts here!

DURATION:

12h

TOOLS:

Excel

Power Pivot

SKILLS:

Data Preparation

Data Modeling

DAX Formulas

PivotTable Reporting

What you'll learn?

Configuring a Power Pivot model for finance solutions

The essentials for a Chart of Accounts table

A layered approach to complex DAX formulae

Methods to build General Ledger, General Journal & Trial Balance reports

How to build Income (P&L), Balance Sheet, Cash Flow statements

Creating a Forecasted Actuals Statement

Formatting tricks to make your statements look great

Protection techniques for your models

Course Overview

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.

Who this course is for?

This is an advanced course which targets users with Power Pivot experience who are struggling with the DAX formulae required to build financial statements. It is geared toward those with:

Experience in working with Power Pivot and the DAX formula language.
Experience using Power Query for reshaping and loading data to the Power Pivot data model.
Understanding of the CALCULATE() function and experience using it to modify filter context.
Working knowledge of financial statements (including Balance Sheet, Profit & Loss and Statement of Cash Flows)

Not sure if this course is right for you?

Visit our Learning Journey page to compare courses by skill level and area of specialization.

Reviews

Building Financial Statements in Excel with Power Pivot

Average Rating

5 / 5

(based on 8 reviews)

 5/5

Course Update Log

2022-08-03

First release of the course.

Course Formats

On-Demand

This is an on-demand course, delivered via pre-recorded video modules that you can consume at your own pace. It includes all required sample files and practice sets.

Course Content

Welcome to the Course!

3 Lessons

Getting Started

Course Introduction

Who’s Ken?

Dimensional Models for Finance

8 Lessons

The Financial “Star” Schema

Getting Started… the Easy Way

Fact Table Design Options

Example: Getting Transaction (Fact) Data

Example: Getting Budget (Fact) Data

Example: Adding Key Dimensions

Example: Adding a Calendar Table

Example: Linking the Model Tables

General Journal

3 Lessons

Goals & Rules of the General Journal

Creating [Debit $] and [Credit $] Measures

Finalizing the General Journal Report

Helper Measures

5 Lessons

Why We Need Helper Measures

Creating a Helper Measure Table

Date Helper Measures

Text Helper Measures

Cash Flow Helper Measures

Income Statement (P&L)

8 Lessons

Goals & Rules of the Income Statement

Review: The VAR Statement

Review: The SWITCH Function

Building the [Actual $ – Selected] Measure

YTD & Revenue Specific Measures

Budget Specific Measures

Variance Measures

Finalizing the Income Statement Report

Balance Sheet

7 Lessons

Goals & Rules of the Balance Sheet

Fixing the [Actual $ – Selected] Measure

Building Lifetime to Date Measures

The Current Earnings Measure

The Retained Earnings Measure

Building the [Actual $ – BS] Measure

Finalizing the Balance Sheet Report

Statement of Cash Flows

7 Lessons

Goals & Rules of the Cash Flow Statement

Understanding the SCF Leaf Table

Creating the Opening Cash Measure

Creating the Cash Change Measure

Creating the Non-Cash Measure

Creating the Cash Effect Measure

Finalizing the Cash Flow Statement

Expected (Forecasted) Actuals

4 Lessons

Goals & Rules of the Expected Actuals Forecast

Creating an EAMonths Table

Building the Expected $ Measure

Finalizing the Expected Actuals Forecast

Trial Balance

6 Lessons

Goals & Rules of the Trial Balance

Trial Balance Helper Measures

Debit -TB Measure Framework

Creating the [Credit – TB] Measure

Fixing the [Debit – TB] Measure

Finalizing the Trial Balance

General Ledger

9 Lessons

Goals & Rules of the General Ledger

General Ledger Helper Measures

Creating the GL Opening Balance

Creating the GL Ending Balance

Creating the [Actual – GL] Measure

Creating a Running Balance Measure

Solving Running Balance Issues

Forcing 0’s on Subtotal Lines Only

Finalizing the General Ledger Report

Preparing for Users

4 Lessons

Hiding Measures & Tables From View

Organizing Measures

Linking Slicers & Timelines

Protecting Your Work

Rate the Course

1 Lesson

Course Feedback

How subscription works?

This course is built on an annual subscription format. Your registration comes with 12 months of access to the course materials, so you can refer back to the videos and example files whenever you like. Your subscription also gives you access to our private Discussion Forum, where you can ask questions related to the course materials, as well as how to apply those teachings to your own work. This forum is an invaluable resource that also allows students to practice their own learnings by helping others with those questions.

On your annual renewal date, your subscription will be automatically renewed with the credit card information on file, unless you cancel your subscription beforehand. You will be emailed a renewal reminder about one month before the renewal date. Please see the FAQs below for information about subscription renewals and cancellations.

About the author

Ken Puls

Microsoft MVP, FCPA, FCMA

Ken Puls is one of the founding partners of Skillwave.Training, and the President and Chief Training Officer of Excelguru Consulting Inc. in Canada. He is a Chartered Professional Accountant, blogger, author, and trainer with over 20 years of business and IT experience. His passion lies in exploring tools to turn data into information, and teaching others how to use them. Ken is a Microsoft MVP and a leading expert in Excel, Power Query, and Power BI.

FAQ

How do I access the course materials? Are they downloadable?

The videos are streamed on-demand and may be watched as often as you like. However, they are not downloadable. The example files and handouts for each lesson are downloadable.

Where are the course videos hosted?

All the videos for the course are hosted on Vimeo. Please note that some networks block access to Vimeo, in which case the videos will not be watchable. You may want to double-check that your network allows access to Vimeo before registering in the Academy, to ensure that you will be able to see the videos.

What support is included with the course? How do I ask questions?

All users with a current course subscription receive access to our private Discussion Forum, where they can ask questions related to the course materials, as well as how to apply those teachings to their own work. This forum is also intended to act as a place for students to practice their own learnings by helping others with those questions. While the focus of this forum is primarily aimed at allowing students to learn by helping each other, they are monitored by our course authors as well. For technical issues related to the site, course access, or other questions regarding your account, please get in touch through our Contact Us page.

How do I access my Certificate of Completion?

After all the modules and lessons have been completed, you will be able to download the Certificate of Completion from the My Courses area of your Account Dashboard.

How do I renew my subscription?

If you subscription is set to auto-renew, our system will automatically renew your subscription for another year on your renewal date using the billing information on file. If you subscription is set to manual renewal, our system will generate a manual invoice and email it to you on the renewal date. About a month (4 weeks) before your next renewal date, you will receive an email from us reminding you that the renewal is coming up and to confirm your billing details so your access is uninterrupted.

How do I cancel my subscription?

You may cancel your subscription at any time, and you will continue to have access to all of your course materials until your expiry date. To cancel your subscription, go to the Billing page from your Account Dashboard and select the subscription to cancel from the My Subscriptions area. Once in the Subscription Details page, you can then choose to cancel your subscription from the Actions field. After your subscription expires, you will not longer have access to the course materials.

What payment methods do you accept? What currency is used?

We accept credit card payments through our secure online payment portal, Stripe. If you would like to discuss other payment options, please get in touch through our Contact Us page. All transactions are charged in US Dollars. Please note that because Skillwave is registered in Canada, we are required by Canada Revenue Agency to collect the 5% Goods & Services Tax (GST) on registrations made by Canadians.

What is your refund policy for this course?

We think you will love our training; however, if you are unsatisfied with the training for any reason, we offer a 14-day 100% money-back guarantee, as long as you have completed less than 25% of the course. All we ask in return is that you tell us why you were unsatisfied with the training. We do not offer refunds if you have completed over 25% of the course and/or it is more than 14 days after the purchase date.

Can an account be shared with or transferred to another user?

Logins, downloads, and other materials are for the exclusive use of the registered user only. Sharing of credentials is in violation of our site policies and may result in termination of product and/or site access without notice.

Reviews

Building Financial Statements in Excel with Power Pivot

(7 Reviews)

7 reviews for Building Financial Statements in Excel with Power Pivot

Rated 5 out of 5

Marvin Rivas (verified owner) – August 24, 2022

I was excited when I saw it listed in the course catalog in Skillwave.
Rated 5 out of 5

Eric (verified owner) – September 26, 2022

This course is awesome. Ken did a great job. I’m slowly making my way through the first time, knowing I will do a deeper dive afterwards. It is the best course I have found on integrated 3-way statements using power query, power pivot and DAX.
Rated 5 out of 5

Carl (verified owner) – October 11, 2022

I liked the step by step instructions.
Rated 5 out of 5

Tim Arrington (verified owner) – December 21, 2022

Ken Puls is a great instructor, no pauses, no backtracking, he is smooth. It is great that Kens is an accountant and not just an IT geek. Really, really good course!
Rated 5 out of 5

Thomas Allan (verified owner) – January 23, 2023

I liked that it was taught by a CPA with experience in all areas of the course. Many thanks for preparing the level of detail contained in “Building Financial Statements in Excel”.

Prior to starting the course I had successfully completed a set of financial reports for a medical packaging company; had I this course in my background, I would have saved substantial time in upfront research. A key benefit to me in signing up for this course was to watch Ken Puls as he assembled the financial reports while also learning insights and best practices along the way.

If someone wants to be more efficient in building financial reports and they have the following as a starting point:
• Familiarity with the concepts behind the Balance Sheet, Income Statement, and Statement of Cash Flows
• Familiarity with basic bookkeeping rules
• Experience with the basics of Power Query, Power Pivot, and DAX
I believe they will also enjoy and benefit from this course.
Rated 5 out of 5

Rafal Jagniewski (verified owner) – January 31, 2023

I loved the structure and the way it was presented. Very interesting and practical subject.
Rated 5 out of 5

Anne Walsh (verified owner) – February 23, 2023

I loved how Ken walked through the whole set up from raw data to final set of accounts. I’ve already recommended this to accountants in my network. Thank you!