Using PowerPivot with QQube For Flexible Financial Statements

Using Power Pivot  with QQube  for Flexible Profit/Loss Statements

Generating financial statements can be tedious due to its standard formatting does not lend itself to being pivot table friendly.  One issue is that Profit/Loss statements often are shown with Revenue and Expenses as positive numbers. When in reality, Revenue is a credit.  Clients want to see Revenue as a positive number as well as expenses. This poses a problem for Traditional Pivot tables as the column Grand Totals and Subtotals only Add.  So a schema where Revenue is a Credit and Expense as a debit (or Revenue is a credit and Expense are Credits) calculates correctly for Net Income .

  Clients don’t like it .

The Other issue that Clients want to usually see Current Month, YTD ,Budget numbers and Budget to Actual Variance.  Without Power Pivot, this would require generating some special calculated columns in your tables to identify these values.

But with Power Pivot, both of these can be resolved.  As the subtotals on a pivot can display separately from the detail lines.  And the “Date” functions in Power Pivot are far more powerful than traditional Excel.

In generating the examples below, I used QQube as my QuickBooks data source. 

With QQube, (www.clearify.com) you can pull QuickBooks data from many different viewpoints.  QQube is the Small Business Analytics source for QuickBooks.

These measures relate to the “GL Detail” subject from QQube. Within the GL Details subject –  GL Line Amount and Budget Line Amount  is shown as a Credit for Revenue and a Debit for Expense.

***Clients want Income and Expense to display as positive numbers.******

 The following Power Pivot measures were developed to meet client needs.

Main Power Pivot measures used:

GL Amount   – This measure flips the sign on Income/Other Expense detail lines and flips the signs for subtotals/grand totals.  This way Income will display positive at the line level and at the subtotal level. Same for Expenses. But for the Gross Profit calculation, Income, under the hood is positive and expenses negative, giving you a correct Gross Profit result.  

=if(hasonevalue(‘GL Details’[Account Type]),

if(values(‘GL Details’[Account Type]) = “Income” || values(‘GL Details’[Account Type]) = “Other Expense”,

       sum([‘GL Details’[GL Line Amount])*-1), sum(‘GL Details’[GL Line Amount])),

                           sum(‘GL Details’[GL Line Amount])*-1)

 

What it means: If  one detail row – Account Type is Income or Other Expense – then Sum(GL Line Amount *-1), otherwise Sum(GL line Amount)…..if at subtotal or Grand Total then (Gl line Amount*-1)

Note:  this is your Month measure

 


 

GL AmountYTD – this measure calculates the YearToDate Amount    I love this measure!

=Calculate([GL Amount],DATESYTD(Calendr[Date]))

 

What it means: Calculate the YTD amount using the Calendr(Date) field   

  This will calculate the correct amount no matter what MONTH or YEAR you choose on Slicers

Note: if client is on a fiscal YTD, then  add the Fiscal Year End at end of  Measure…

=Calculate([GL Amount],DATESYTD(Calendr[Date],”5/31/2015”) then formula will calc correctly for a Fiscal Year end of “5/31″


 

Variance –  Budget less Actual

=sum(‘GL Details’[RowVariance])   To make the ‘Budget – Actual Variance’ come out correctly, I generated a calculated column  in Gl Details called “Row Variance”( Budget Line Amount – GL line Amount)


Budget Amount:  Add Budget measures similar to above for Budget Line Amount.

Result is what you see below:

financial sample rpt powerpivot

 PP image QQube-Logo-251x224www.Clearify.com

 

 

Fran Reed – FreedupDataOptics – 4/5/2016

 

 

Obsessed With Data Demons

vector-colorful-abstract-background_GJq2tHI_

Lessons Learned – Obsessed With Data Demons
Note: In relating this story, I used a lot of terms related to QQube, MS Excel and QuickBooks. QQube is a database tool for QuickBooks and allows users to analyze data in far more detail than you could with QuickBooks.

How many times do you get obsessed with finding an answer to a problem and don’t ask for help?
Don’t let yourself go down this dark path by yourself…. Seek help and save yourself time and sanity… Allow your pride to be humbled. Even just talking out the problem with others may yield insights that you will not come up with on your own.

The consequences of not asking for help came to light the other day as I  spent 3 days attempting to balance an Excel  report between  QQube (www.clearify.com) and QuickBooks.   Numbers, transactions and dates whirling about.  I was determined to find the issue so that I could either solve it or describe it the folks at QQube.
It wasn’t that I didn’t want help – I did but felt that if I didn’t continue to find a solution, then what good am I. Chuck Vigeant, QQube’s founder, has always mentored me and others to stretch and think about your problem – what you are going after and doing, instead of just seeking an answer.

I was obsessed and couldn’t put my spreadsheet down. The numbers were swimming around me. This client had some unusual accounting practices and this made me sure that one of their crazy transactions was causing a problem. I just couldn’t get it to match to QuickBooks(QB). I knew that QQube is extremely reliable and maps to QuickBooks 99.9% of the time. I trust QQube. So why was my report not balancing? What was I doing wrong when searching for the correct answer? My client wanted to know why it didn’t agree with QB. Getting so wrapped up in the numbers and grasping for clues lead me down the wrong path to search. I finally found a reasonable solution that made sense to me, logically, and went with that path. But the numbers still did not match up. And as such, I still kept at it. 

I was getting very obsessed and ignoring my other client work.

About this time, Chuck did reach out to me and we looked at issue together. His approach was different than mine and frankly what we found was something I would not have even thought would’ve been an issue.

The technical part: Chuck opened up the Accounts Receivable report in Excel using QQube’s Pivot Table display . It didn’t balance.  I was starting to feel vindicated. See.. something is wrong! We opened up one of the transactions, half of the entries was missing. The missing entries were Statement Charges. Hmmm…..Chuck did looked deeper by generating some MS Query queries showing the various transaction ids and table links. Where were the Class Id’s.?  It turned out that Class was causing and issue. Class? huh? As far as I knew, this client wasn’t using even using Classes. Chuck had an inkling about this and removed Class from the pivot table. Pivot refreshed and it agreed with QuickBooks. wow! Who would’ve guessed.

We looked closer at the statement charges in the transaction details report in QB. Class was being assigned to  old transactions. Classes were active at one time. However the Statement Charge register was not showing any class field. Checking QB preferences, class tracking had been turned off. As a test, we turned it back on. Did a manual QQube refresh and balances remained good.

In the beginning of this journey , I converted the Open AR QQube pivot table to a table listing. And naturally, since Class was part of the original pivot it followed to the table. So from the beginning, my detail table was appearing to be off. And then this got me off looking into other directions.

To appease my need to be in the table listings and then generating my own pivots, I opened up the detail table listing for the subject. I recreated my pivot using the same fields that Chuck and I had used. And of course, it generated correctly. I can now be happy and add some of the calculated column groupings that I dearly love.

So I relay this story to first, tell you to seek help if you are struggling. (Chuck states you should not spent more than an hour looking – fat chance!) And secondly, to give one ideas about how to sleuth out issues ,to look beyond what’s obvious. And if you find yourself obsessed – back off! Know when you are too close. Find a buddy who knows the software, in this case QQube, and can offer advice.

And finally…
As I pondered the path chosen in resolving this issue, I realized that these lesson’s
are not limited to debugging databases, spreadsheets or the like. These lessons apply to all of LIFE. Too often we try to solve our problems and issues ourselves and end up going in a direction that completely differs from the truth. We come to faulty conclusions and miss our mark.