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

 

 

Lean in: Women, Work, and the Will to Lead

 I have been devouring Sheryl Sandberg’s book: “Lean in: Women, Work, and the Will to Lead”

I have found it exhilarating and thoughtful. She talks about gender equities and inequities  in the workplace and home.  Her passion for this topic clearly comes through and her observations keen.  She tells on herself  with examples from her life. She is honest about her mistakes and successes.  A  risk most of us would not take. She is passionate about bringing about more women into high leadership positions.  Women making powerful choices that encourage them to live life passionately and to its fullest. She describes the struggles between juggling work, kids and home while maintaining a career that she loves and is demanding.  She celebrates the differences between genders and wishes that it becomes OK with the choices that are made. For example, women tend to be celebrated for prioritizing staying home with the kids, while men are generally not. 

 As COO of Facebook, she is playing at a level in the high tech world most of us can’t even imagine. Sheryl is in a position to help us as women in the workplace to make a change. Her career is nothing but stellar and for most of us unimaginable at the opportunities she has been a part of. 

But even with her high powered encounters the lessons learned and observations still relate to all women as we go through life negotiating our way through the workplace and with life.  
Men and women handle situations differently and her point is to recognize these differences and not to make women be more like men but to learn to strategically play in their world. And for men to not consider these differences to be signs of weakness or frailty. Gender bias is all around us and it is so deep in our culture we don’t even notice it many times. 
So, I just wanted to share this book with youall,  as consultants, bookkeepers,  accountants and entrepreneurs many of us are women. Her points can help us negotiate/recognize the various levels of bias we encounter in our business.  Finding the right persons to trust and mentor with,  learning to “lean in” to our work when necessary and when to set on the sidelines.  The main questions being not “Can I do it all” but “Can I do what’s important for me and my family?”  
In some ways our jobs are a bit tougher, because as consultants, we are placed in many differing situations where we must alter our strategies to accomplish our goals as we deal with different small business owners. 
This book was written before her husband Dave (CFO at SurveyMonkey) died. And her love and devotion to this man and her family clearly show through.  Since my husband works at SurveyMonkey, and we live in “the valley” ,this relationship has been close to us. 
Would love to hear her speak at QB Connect.

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.

Excel and BI Videos at PASS Conference website

Thought I would share this youall…..  These are great free webinars on Excel and Business Analytics.

I was watching the one by Bill Jelen (Mr. Excel) the other day and it is outstanding.   Learned a lot of neat tricks.   http://passbaconference.com/2015/Learn/AnalyticsWebinarSeries.aspx

Since the conference is in my backyard, I am emptying out my piggybank and planning on attending. Let me know if any of youall are also.

Enjoy…

Fran

200x51

– See more at: http://www.clearify.com/spaces/3/qqube-solution-providers/articles/press-releases/73/excel-and-bi-videos-at-pass-conference-website#sthash.i2rq4D5C.dpuf

TechCorner: Understanding the differences between MethodCRM Classic and MethodQBO

As of 3/4/2015 – these are the main differences between MethodCRM desktop and Method QBO as far as treatment of tables and processes go..  

This document was developed by the  Not So West Coast Method Partner  group.   If you would like more information about this group, please send an email to Fran Reed at Fran@freedupdataoptics.com or Steve Blaustein at steveb@mnvsoftware.com

 

Working with Method Classic and QBO

2/9/2015 update 3/4/2015 by Fran

 

As Intuit pushes more and more clients towards QBO. We would expect to see more and more requests to hook up with Method.Method helps to fill in the gaps where QBO is lacking but there are also lots of other differences that a Method developer needs to be aware of.

 

We consider the two to be similar, but QBO and QuickBooks desktop are built on different platforms. What’s available for syncing and handling of data  is just different. And on top of this, how Method handles can also be different from desktop to QBO version. This guide is intended to outline the differences and to help the Solution Provider with Method QBO projects.

 

General and very important differences:

QBO and Method do not sync:

  • Invoice Lines generated from Time Ticket entries don’t always sync and sometimes not at all … there is an open ticket to resolve this issue
  • Purchase Orders – Method issue
  • Bills – Method (but its coming) – half built in Method  –  Bills can now be entered
  • Bill Payments – not integrated
  • Expenses – not integrated
  • Checks – not integrated
  • Custom Fields on sales forms – IPP issue (only available on Sales forms anyway)
  • Sales Orders concept does not not exist in QBO –
    • but there are estimates in ‘plus’
    • Estimates do sync
  • QBO does not require Items or vendor names but Method does. And this is a bad habit for clients not to enter in Item names or vendor names anyway. QBO allows ‘missing items’ as additional description lines, however, should prohibit entry of rates and quantity, but doesn’t — IPP assumes lines without items are just description and won’t retrieve the remainder of the record
  • QBO does not have a concept of SalesRep.  To get around this, Method uses an Assigned To Field. This is based on the user logged in, not a Sales Rep list.

 

Treatment of Names and imports are different.  Test out some sample names. Start in QBO then see how syncs to Method. Likewise start in Method and review name in QBO. Then resync to see how “round trip” looks. (sometimes it changes). Confer with Client on whether they want contact name to be  “display name” or company name.  QBO only requires a first name, Method prefers full name.  If first name only present, Method will populate in  Last name. Display Name is the required Name field.    (fullname is required field in desktop; update name which fills in fullname)

 

Using Method’s Import Tool for Names:

 

Customer or Vendor table:

  • “Name” is required field. If “blank”, Method will fill in FName and LastName.  First Name and Last name need to be included in import.  There is no ContactName (concatenated first and Last name)  in the entity table as there is in QBD  In QBO “Name” field is “display as” field.
  • If you wish to populate the Sales Tax code associated with a Customer. QBO allows full text. Method will only allow 3 digit code as in the desktop version.  Keep this in mind when setting up new file.
  • Street Address is imported into Billing Address1

 

Contacts table:

  • Name is contact name, FullName is Customer or Company Name.  Primary Contact has a special Type of “Contact” whereas other contacts do not. (Its populated when importing customers).
  • ComanyName field is NOT populated for additional contacts.
  • Entity is RecordID of “Name”.
  • Fullname is required field in Contacts table. This joins it with Customer Table.
  • Contact Title is limited to 25 characters.
  • ParentRefName is not populated unless add a “Sub Customer”

 

 

Advanced QuickBooks Reporting and QuickBooks Advanced Reporting (not redundant) by Mario

Good article by Mario on QQube and QB Advanced Reporting….

Are you getting the reports you want out of QuickBooks? Many times, the limitations of QuickBooks’ predefined reports make us reach outside and use complex data access tools or reporting add-ons that may have limitations of their own.Enter QQube (by Clearify). It is a technology that eliminates the need to know connections, tables, and relationships. No more having to be a reporting expert. You can use Excel, Crystal Reports, or the data analysis tool of your choice. Included is an Excel add-in that allows to drag and drop information into a spreadsheet for simple data lists or for use in powerful pivot tables. The ease of use allows anyone to create and manipulate data and reports on their own. Imagine being able to simply drag and drop fields into Excel and have QQube build the pivot table. Sample pivot tables are included as a starting point, or you can create them from scratch.

Reports that would take hours or days before, now take minutes to create. A good example is inventory/sales reporting. We created a Sales by Item Summary for multiple periods showing at the same time as well as gross margin by product and the ability to filter by item range (as opposed to by selecting “multiple items” as you do in QuickBooks). This report took us no more than five minutes to create.

Another area where QQube is powerful is job costing. For another client, we built an Estimates vs Actuals report with additional data fields not available in QuickBooks built-in reports, and the ability to report multiple selected time periods (not necessarily adjacent) together. This also took minutes instead of hours to build. As an added bonus, because of QQube’s ability to combine data from multiple QuickBooks company files, the client can use the exact same spreadsheet and toggle/filter by company name to display each company’s underlying report. And further, the spreadsheet can be sent for others to see even if they don’t have QuickBooks.

QQube works with all current desktop versions of QuickBooks (Pro, Premier, Enterprise Solutions). QQube is the creation of Chuck Vigeant, who has worked in the accounting and database technology field longer than anyone in the industry. I know no one outside or inside of Intuit who understands the QuickBooks database structure better than he does.
QQube is what we use today for our clients’ advanced QuickBooks reporting needs. Another versatile tool is the new “Advanced Reporting” module in QuickBooks Enterprise Solutions 15.0 (not available in previous QuickBooks Enterprise Solutions versions). It provides enhanced searching and filtering, including pre-built charts and search boxes. You can start from a group of reports you commonly use or create your own unique report to meet a specific business need. Advanced Reporting “lives” in its own window launched from inside QuickBooks and does not support reporting on multiple company files. While a vast improvement over QuickBooks built-in reports, it does require some understanding of database tables and relationships.
Two very good reporting solutions, one used for virtually unlimited advanced QuickBooks reporting, and the other is the QuickBooks Advanced Reporting module built in QuickBooks Enterprise. Hopefully this did not confuse anyone… and now you know how I was not being redundant in this article’s title (!!)

…MARIO

Sales Orders and Estimates in Method:CRM

Fran Reed

1/3/2015

 

It seems like there is confusion about the use of Sales Orders and how they relate to Estimates. This blog hopefully shed light on the differences.

 

Often times an Estimate and a Sales Order are intermingled or their use may overlap, but there is a difference.

Sales Orders are generally used for product sales whereas Estimates are used more for custom, labor or unique product or service sales.

  Continue Reading →

Top Turkey Dashboard Design Mistakes

Design Tips to Avoid in Creating Dashboards

By Heather Peterson

Original Post location – http://blog.easy-commission.com/

 

ParadeNothing says Thanksgiving like the Macy’s Thanksgiving Day parade. Everyone grows up waking up and turning the TV on to watch the procession of beloved characters and entertainers on floats or in the form of a balloon. Some even camp out for a spot along the parade route to see the festivities live. Throughout the years, balloons mostly stay the same while new ones are added each year. Floats on the other hand differ from year to year in appearance. From Sesame Street to the Hess Corporation, floats come in all shapes and sizes. Some became the star of the show with a pretty design, key features of emphasis, and a nice balance of flowers and decoration. Other floats can get overlooked with too much to look at and an unidentifiable theme.

Continue Reading →