The CPA Revolution: Getting Started

Showing posts with label Getting Started. Show all posts

Hire a PowerBI guru

I've recently hired a PowerBI analyst to join my team in my full-time industry job. In 3 short months, he's built 3 amazing PowerBI dashboards that have given us tremendous insight in to our accounting data such as the composition of our Journal Entries, our fluctuation (flux) explanations, and our Month End Close Status. It's been a tremendous win.

He started his career as a data engineer, and then taught himself PowerBI from materials like this available online. He's already been awarded our company's high honor quarterly CFO award: the first time someone on my team has won in the last three years. I've been trying to teach accountants how to use these Power tools, but hiring someone who has already made the effort, has been a huge win. I can't wait until the market starts to have more people like him available as they are a key part of my accounting strategy for winning.

So my key takeaway, hire a PowerBI guru if you can. Make the case; they will quickly pay for themselves with their insights (and make you look like a rockstar)!

And if you are the guru already, send me a message, and let's talk. Maybe I have a project or work that you can help me with.

2 files, 1 Pivot Table: Power Pivot

New to this blog? Start here for an Intro to Power Query.

What if you could take 2 (or 20) files and analyze them in a single Pivot Table?

Power Pivot provides the ability to analyze multiple files together in one Pivot Table along with additional data analytic tools and reporting functionalities. And, Power Pivot, operates within the Excel and Pivot Tables you already know and love.

Intro to Power Pivot series

From this Intro to Power Pivot series, I hope you will become as enamored with Power Pivot as I have.

The scenario for this exercise series will feel familiar to most accountants and analysts: there is a budget file and an actual file, which use different formats, and you need a report showing the variance by month.

At the end of the series, you will be able to produce the following Pivot Table.

The series will teach the 5 core steps to robust Data Models using Power Pivot:

These 5 core steps are covered over several posts as follows:

Installing Power Pivot
Creating a Date Table [load Lookup Table]
Building relationships [load Data Table][build relationship][Pivot]
2 Data Tables on 1 Pivot Table
Measures [write Measures]

Select the Next post link below to get started.

Prior post: Joins illustration
Next post: Installing Power Pivot

💬 Already using Power Pivot? What advice do you have for new users? Comment below.

Installing Power Pivot

Using Excel 2013 or later? Power Pivot is an Excel add-in.

To enable, in the Excel ribbon, go to File, Options, Add-Ins, and select Microsoft Power Pivot for Excel.
Click Go.
In the pop-up window, select Microsoft Power Pivot and then Enable. If Microsoft Power Pivot is not an option in the pop-up window, close the pop-up window and select Ok.

Back in Excel, on the ribbon, you should now have a new Power Pivot tab. If so, you are all set and ready for the next lesson.

If the Power Pivot tab is not on the ribbon, on the Data tab, find the green data model icon under Data Tools, as pictured below. When hovered over, it should say "Go to the Power Pivot Window". A pop-up will ask if you want to enable data analysis tools. Agree. You should now have the Power Pivot tab on the ribbon and are ready to move on to the next lesson.

Since Office 365 updates frequently, differences may exist. For additional troubleshooting, try this link to Microsoft installation instructions.

Using Excel 2010? You will need to download the Add-in from Microsoft. Follow this link.

Excel iOS and Online? Power Pivot tables can be viewed in these Excel versions: no installation required. However, changes can't be made to the underlying data model in these versions.

Excel for Mac and Android? Microsoft does not support Power Pivot on these platforms. Share your voice on the Microsoft user forum.☹️

Prior post: 2 files, 1 Pivot Table: Power Pivot
Next post: Power Pivot Date Table

Installing Power Query

Using Microsoft Office 365, Excel 2016 or Excel 2019? No installation required.

Power Query (PQ) is part of Excel; it's incorporated in newer versions of Excel, conveniently located in the Get + Transform section of the Data tab of the ribbon.

The lessons on this blog are using the most recent Office 365 version available at the time of posting. Since Office 365 updates frequently, differences may exist.

Using Excel 2010 or Excel 2013? You will need to install the Add-in.
Link to Microsoft for installation.

Excel for Mac? The Microsoft Excel team is actively working on releasing Power Query functionality for Mac users. As of May 30, 2019, Mac users on versions 16.26 and later can refresh queries from Text, CSV, XLSX, XML, and JSON files.

Excel for Android, iOS and Online? Microsoft does not support Power Query on these platforms. ☹️

There are currently 5 Excel versions widely used by accountants. Differences may exist between the screenshots provided and your version of Excel.

I want to empower you to revolutionize your CPA skills. If a difference isn't intuitive to you, please let me know. I'll try and quickly get you on the right track.

Depending on your settings, downloaded exercise files may initially open in Excel Online. They will need to be opened in desktop Excel to enable the Power Query functions saved in the workbook.

In this lesson, you learned how to:

Make sure Power Query is installed on your PC.

Prior post: The Game Changer
Next post: Load data

💬 What version of Excel are you using? Leave a note in the Comments section.

The Game Changer

There's a running joke that CPA stands for Cut, Paste, Assemble based on how much time accountants spend molding data to get it in to reasonable shape. It's time to stop Cutting, Pasting, and Assembling your data monthend after monthend, and Revolutionize your CPA skills.

Game Change your CPA skills by learning how to Choose, Process, and Analyze your data using Power Query (aka Get + Transform).

Power Query can quickly automate routine tasks performed each quarter, week, or day. Microsoft built these Excel business intelligence tools so Excel Power users (like accountants and analysts) can build queries and reporting analytics without an IT background or support within the application they already know and love. Think of all the times you've waited for a report to be built, or wanted to modify a few items, but the IT backlog, or expense, or testing effort, put the change on the backburner. And so, the accountant accepted taking on the task of fixing this one thing, and then one more thing, and then one more.

Let's get started with an example where it takes 17 tedious steps to produce a simple report today, but only 3 steps after using Power Query.

The Accounting Request

The Treasury Manager in your company came to you, an accountant and said "hey, I need this report every month." You produced the following:

Data

The report was produced using two data sources: GL data and a Treasury bank account list.

17 tedious steps

Without Power Query, the following 17-steps were performed to transform the data in to the requested report:

Open the raw GL Balances data file
Insert 3 additional columns between columns A & B
Disassemble the Account column using Text To Columns based on "-"
Cut out the 3 new columns, keeping only the GL Account number
Paste in 1 additional blank column after "Dept"
Disassemble the Dept column using Text To Columns based on "-"
Assemble the remaining data by renaming the "Account" column to "GL Account"
Convert "Dept" to text with three characters so 78 is 078
Right align the data in the Dept column
Format the Dept column to center align
Label the department name column "Dept Name"
Convert "Balance" to currency
Add 3 new columns preceding GL Account, labeled as "Account Type", "Bank", and "Account Number"
Create some kind of lookup to find the Account Type, Bank, and Account Number
Format column header "Balance" to right align
Add Total row to show "Total Available Cash at Monthend
Add Header with current month end date

Post-revolution steps

Choose your source files and save over prior month.
Process by right clicking a query and refreshing.
Analyze like the talented finance professional you are.

It's Month 2: will you be performing the 3-step process or the 17-step?

Learn the step-by-step automation by following this blog.

SOLUTION.
Open in Excel, not Excel Online, to view the complete solution.

Prior post: None
Next post: Installing Power Query