skip to Main Content

Power Pivot, Power BI, and Microsoft Excel techniques for Accounting and Finance Professionals.

Dear Accountants: PowerPivot is your friend!

Guest Post by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

Read the Rest

Moving Averages, Sums, Etc.

 
Moving Average in Power Pivot

The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend

I realized recently that this topic has never been covered before, in its most straightforward form, on this site!  Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).

To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model:

Read the Rest

Why PowerPivot is Great For Accountants (Guest Post by Robert Stamsnijder)

 
Intro from Rob:  Robert Stamsnijder is one of those people who’s been weaving in and out of my life for awhile.  I’ve had Skype conversations with him in probably four different calendar years.  He surfaces, delivers a visionary sermon with intensity and color, and then disappears below the radar, working fervently on his master plan(s).  Then he resurfaces and reports that project was merely Phase One, and he’s now moved on to Phase Three.  A very interesting, intelligent, and entertaining gentleman.  And I am happy to welcome his first guest post here on the blog.

 

<- by Robert Stamsnijder, aka The Mean Dutchman Smile

After reading Rob’s blog What is PowerPivot? (back in October 2009) the thought came to my mind that PowerPivot would have a gigantic impact on the accounting profession. Why? Well, if one looks at the long, long list of accounting scandals one would think that accountants will surely welcome every kind of technology that can assist them in improving the quality of their work.

There are 2 kinds of accountants

Read the Rest