Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Automated Custom P&Ls in Power BI with Quickbooks data
Rating: 4.2 out of 5(11 ratings)
64 students

Automated Custom P&Ls in Power BI with Quickbooks data

Step-By-Step build Financial Statements in Power BI with your QuickBooks Data
Created byDawn King
Last updated 9/2024
English
English [Auto],

What you'll learn

  • Learn an easy way to get QuickBooks Data into Power BI that you don't need to be a database engineer to master. Any business professional can learn this!
  • Learn tips for setting up your QuickBooks Data in a way that will give you the greatest flexibility when you build reports in Power BI
  • Learn the basics of how to connect data together in Power BI, beginning with your General Ledger.
  • Learn how to navigate in Power BI and how to build basic financial reports in it using your QuickBooks financial data
  • Learn how to enhance your Chart of Accounts in Power BI so you can customize different versions of your Profit & Loss that all tie back to QuickBooks Data

Course content

1 section • 12 lectures • 4h 41m total length

  • Introduction10:54

    Learn to use Power BI to extract QuickBooks data, build a general ledger data model, and create refreshable P&L and Ebida reports with multiple versions for managers.

  • Setting up your Power BI license18:43

    Identify the appropriate licenses for Power BI viewers and creators, including Power BI Pro and Office 365 E5 with supercharged Excel, then purchase, assign, and sign in via admin portals.

  • Formatting and Extracting your QuickBooks Data for use in Power BI33:05
  • Loading your Chart of Accounts into Power BI17:03

    Learn to load your chart of accounts from QuickBooks into Power BI using CSV exports, clean data with Power Query, ensure unique account numbers, remove blanks, and prepare for reporting.

  • Loading your GL Transactions into Power BI31:24
  • Creating your Calendar Table31:26

    Learn to create a calendar date table in Power BI from a CSV Excel date list, deriving year, quarter, and year-month dimensions with Power Query and DAX.

  • Connecting your tables together8:32

    Connect and relate your QuickBooks data in Power BI by configuring account number relationships, managing cross-filter directions, and building a pivot matrix to create an integrated p&l.

  • Building your Profit & Loss Headers36:49

    Learn to build your profit and loss headers in Power BI using QuickBooks data, including separating balance sheet vs P&L accounts, creating explicit measures, and replicating the P&L structure.

  • Customizing your Profit & Loss Headers36:16

    Explore how to customize P&L headers in Power BI with QuickBooks data, creating multiple header levels and segments like passthroughs and EBITDA to analyze gross profit.

  • Building your Profit & Loss Measures28:50

    Build dynamic profit and loss measures in Power BI using a date table and fiscal year calendar to compare month-to-date, year-to-date, and last-year metrics with QuickBooks data.

  • Presenting your Reports in Power BI Online and pdf Versions19:43

    Publish PNL reports in Power BI online and PDF, manage access across accounting and management workspaces, and leverage a semantic, golden data model.

  • Presenting your Power BI-connected Reports in Excel8:22

    Learn to present Power BI reports in Excel by exporting data in the current layout or as summarized data, then create refreshable pivot tables connected to the data model.

Requirements

  • Basic QuickBooks skills and a QuickBooks data file that you want to use to create your custom Profit and Loss Statement
  • Experience with making pivot tables in excel will be helpful, but not required
  • No programming experience needed. You will learn everything you need to know

Description

I will be using a QuickBooks Desktop sample file to show you how to begin modeling your financial data in PowerBI.

Starting with a Profit and Loss Statement we customize in three ways: Comparative time periods, a Gross Profit Analysis Version and an EBIDTA version.

You'd need to be an IT professional (or pay one) in order to create a set of Power BI Financial statements that has a direct connection into the QuickBooks back-end.  Whether it is QuickBooks Online or QuickBooks Desktop, when you access the data from the back-end you'll find over 100 tables of data that need to be interrelated back together in order to yield any sensible reports!

My step-by-step method works around all that. I let the software do a lot of the work and extract the data from the front end, saving a ton of time and complexity in Power BI - getting you using the software to automate your reports FAST and EASY.

This method is something ANY ADMINISTRATIVE PROFESSIONAL can learn to create and maintain. When you see how easy it is to create a completely customizable Profit and Loss statement and automate some of your complex calculations and reports, you will want to learn more. This method will give you an introduction to Power BI that delivers results right away. It will empower you with the skills to automate some of your most time-consuming tasks and give you a base to build on as you go on to learn more.

Who this course is for:

  • Users of QuickBooks who want to learn how to make their lives easier by automating their complex reports and calculations
  • Accountants who want to turbo-charge their PowerBI skills

Report abuse