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:

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

---

Post-revolution steps

1. Choose your source files and save over prior month.
2. Process by right clicking a query and refreshing.
3. 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