Business Tips | Dashboards and Data Viz | Data Strategy | Finance | Tips & Tricks

My background 

As the Program Manager for Implementation and Solutions Consulting for iDashboards, I’m focused on the success of our clients through onboarding and professional services. During my time at iDashboards, I’ve held several roles, starting as a Pre-Sales Engineer, building proofs of concept dashboards for prospective clients, then moving into a Product Analyst role where I focused on partnerships, launching and maintaining the iDashboards Data Integrator (iDI) before moving into a Technical Account Manager role. The TAM role positioned me squarely into client services for current customers and I focused on client retention and gained a lot of solutions consulting experience. In addition to my role at iDashboards, I also lead the Michigan Scholastic Cycling Association (MiSCA), which is a 501c3 nonprofit youth mountain biking league serving nearly 700 kids supported by more than 250 volunteer coaches and 10+ part-time staff. I completed my MBA in 2018 and began teaching 300-600 level marketing courses at the college level in 2019.

How I got started with QuickBooks

My first exposure to QuickBooks was during my time as Product Analyst. I was tasked to build a dashboard pack for both the desktop and online versions of QuickBooks. With limited QuickBooks and accounting knowledge, we leaned on some customers to provide KPIs and help finding the data we needed. I truly became knowledgeable of QuickBooks in 2018 when implementing QuickBooks Online (Advanced) for MiSCA. I continue to maintain the day-to-day books for the organization and we leverage the tool for AR/AP, donor tracking, budgeting, and classes to track the success of our events. I’ve connected several clients to their QuickBooks data through iDashboards, building custom dashboards and data jobs in many cases. I’m planning to migrate my Homeowner’s Association books to QuickBooks Online later this year and recently completed the certification to become a QuickBooks ProAdvisor for Online.

The Data and how we get it

Managing-Financial-Metrics-and-QuickBooks-Dashboard

iDashboards and QuickBooks

Enough about me… let’s talk about QuickBooks and iDashboards! In 2016, we developed a partnership with CDATA for their Sync application, which connects to various APIs and moves the data directly to a database. From there, I built dashboards from the data in database. The Sync app was much like the Data Hub is today – you connect to an API and schedule the data to be moved, but this was all done circa-Data Hub. It was pretty limiting that the pack required an additional investment for the Sync app, so it wasn’t highly used, though I helped a handful of clients implement this solution that worked well.

Now that we have the Data Hub and a native (free) API connection for QuickBooks Online, it was time to rebuild the packs! I was really excited to take on this project again, now with 4 more years of experience in QuickBooks since the first time I completed the packs. I used the 2016 pack as a guide, but recreated all of the data work in Data Hub that was originally done via custom SQL. Also, having the Data Hub eliminated the need for clients to download/install a 3rd party Sync application and the updates are all handled by the server instead of someone’s local machine.

Challenges in working with the API

I identified several challenges while working with the QuickBooks API. The most prominent was that we had no line-level details for income or expense transactions like invoices, sales receipts, expenses, and bills. Our development team was able to write new calls to the API and included the update in version 10.3 of iDashboards. So, one requirement is that you must be on v10.3 or higher to use the new QBO pack. Once I had access to the lines, I was able to visualize data by Product, Class, and income/expense account. The next challenge was dealing with the data structure. Some columns are not named very well and it took some hunting to figure out where the data was located. One huge time saver that’s included with the pack is simply the data hub jobs to move the API data to the data store. This process saves all the configuration you would normally need to do to pull data from the most popular API objects and reports available.

Data Hub jobs

Once the data sets were created to pull the needed data from the API, it was time to build out the ETL jobs in the Data Hub. This was a lengthy process, as QuickBooks stores all the data in different entities based on the object, e.g. invoices and sales receipts are in 2 different “tables” and the line-level details are in 2 more tables yet. This meant many joins were required to get all the needed data together for visualization. The next step was to get a hierarchy view of the accounts, to roll up the income/expenses for each of these levels. I also did some data cleanup, replacing NULLs with N/A for a better experience for dashboard viewers, calculating whether or not invoices were past due, and more! The 4 Data Hub jobs I created are scheduled to run nightly by default and are organized by income, expenses, reports, and miscellaneous.

Insights from the data

Quickbooks Online Dashboard

1. QuickBooks Scorecard

The Quickbooks Scorecard is a single dashboard that showcases a high-level view of all your financial metrics. Here you can see the total checking account balances, net income (current fiscal year), total income and balance due by month, count of past due invoices and balance due, total accounts receivable (AR), AR aging, revenue by customer (total, paid, balance due, past due), total accounts payable (AP), AP aging, expenses by vendor (total, paid, balance due, past due). Each chart drills down to dashboards specific to the metric, but you can also drilldown to the amount paid and balance due by customer for a specific month before going to the customer details dashboard. This dashboard is a 1-stop shop that will allow you to assess the general health of the organization, plan for upcoming costs, and start calling on unpaid invoices.

2. Income Statement (P&L)

A profit and loss statement (P&L), summarizes the revenues and expenses incurred. These records provide information about a company’s ability to generate profit by increasing revenue, reducing costs, or both. Metrics include revenue/expenses/profit by month, income/expenses by account (down to fully qualified name), and income/expenses/profit compared to last year (drilldowns through 3 account levels). Your users will also be able to apply filters for class and a custom date range. If you only had one report to run your business, this is it! You can tell which products/accounts are profitable and others that should be evaluated. The ability to compare to last year graphically is also something that QuickBooks doesn’t offer without add-ons.

3. Accounts Receivable and Customer Income

The Accounts Receivable and Customer Income dashboard shows the outstanding balances and profitability for each of your customers. Metrics include open balance by customer, profitability by customer (fiscal year-to-date), and accounts receivable (AR) aging by customer. You may be surprised to find that your “best customer” is one of your least profitable or they require additional accounting efforts due to late payments. This dashboard can be helpful when renegotiating contracts of determining future pricing – maybe offering a discount for on-time payment would be better than simply giving a discount at the current terms.

4. Customer Detail

After reviewing the AR/Customer Income dashboard, the Customer Detail dashboard is a natural next step. Here you will see details for 1 customer at a time, including total open balance, accounts receivable aging, profitability (fiscal year-to-date), contact information, revenue and balance due by month, revenue and balance due by transaction with drilldown showing line level details including the items sold, classes, quantity, and price. The dashboard also includes user-driven filters with a dropdown to choose a customer name and date pickers for start and end date to build a custom date range. Typically you are here when a customer owes you money, but it’s a good place to see trending for your best/worst customers and try to get ahead of a bad trend.

5. Income (Sales)

Whether you call it “sales” or “revenue”, it’s all income! This dashboard includes percent open balance for invoices with a balance due, past due balance amount, total income and balance due by month with drilldown to amount paid and balance due by customer for month, amount paid and balance due by class, amount paid and balance due by income account with drilldowns through the various income account levels to see total paid and balance due by item. Users can also control filters with dropdowns to choose multiple customers by name, classes, and whether invoices are current or past due and date pickers for start and end date to build a custom date range. This dashboard allows you to get a good general picture of money coming in but also allows you to drill in to a certain customer or class to pinpoint issues or inconsistencies – for example, maybe a good customer usually orders every 3 months, but they haven’t placed an order in 5 months – now’s a good time to call them before they cozy up with a competitor.

6. Purchases (Expenses)

Shifting gears from income, the purchases (expenses) dashboard highlights your spending. Metrics include purchases by class, purchases by month with a drilldown to see purchases by day for month, purchases by expense account with drilldowns through the various expense account levels, purchases by vendor, and upcoming bills due by vendor and due date. The dashboard also includes filters with dropdowns to choose multiple vendors by name and classes and date pickers for start and end date to build a custom date range. Here you’ll see the purchase history for certain vendors, visualize price/spending increases, and have a list of bills that are coming due.

7. AR and AP

This dashboard compares your receivables (AR) to your payables (AP) and displays a 12-month cash flow history. While these metrics are available on other dashboards, this dashboard puts them side by side, so you’ll see how much money you owe and how much money is owed to you. At this point, you may start writing off accounts that are 3-6 months past due. Metrics include total, current, and overdue accounts receivable and payable, balance by customer, AR and AP aging with a drilldown to see balances by customer, and cash flow by month.

8. Budget vs Actuals

Built off the income statement data, the budget vs actuals report is the most important in the dashboard pack. While all the dashboards tell you what is currently happening, and possibly provide comparison to prior years, this dashboard tells you if you’re on track to meet your budget/goals. Metrics include the budget vs actuals report by account with details down to the fully qualified name, net income showing revenue, expenses and profit compared to current budget and last year actuals with drilldowns through all income and expense accounts. To fully tell the story, the dashboard shows last year vs this year vs budget. Often, the budget may be a lofty goal, so being able to ground that by comparing to last year can be helpful. There are no visuals for the budget vs actuals in QuickBooks, so this report can be quite eye-opening.

Budget-and-Accounting-QuickBooks-Dashboard-Metrics

Giving access to QBO data to non-QBO users

One of the most exciting parts of my job is delivering information to people that wouldn’t normally see it and the QuickBooks pack is no exception! QBO has some nice built-in charts, but that’s not helpful if they’re reserved for the accounting team only. While QBO does offer the ability to create your own charts in the more advanced subscription levels, it was quite complicated and I’m still figuring out how to make the charts I was looking for in their system.

Blending QBO data with other systems

Another benefit of pulling your QuickBooks data into iDashboards is the ability to blend your financials with other systems. For example, we use QuickBooks and Salesforce internally, and now we can blend data for each client account to see what is being sold/offered and how that matches up with billings. It can also aid in cash flow projections as you compare your pipeline to the bank account, even before an invoice is created!

Client implementation

Whether your iDashboards enterprise server is hosted in the Cloud or on-premises, the QBO Pack can be configured to meet your needs. We recently implemented the pre-built dashboards and ETL jobs with an iDashboards-hosted client that had 3 different QBO accounts. The jobs were duplicated and configured to combine the data from all 3 companies and we modified the dashboards, adding a dropdown filter for Company. This allowed them to share this data with non-QuickBooks users, which is even more impactful when not requiring a different login to each company account. They’re hoping to spark positive change by making this data available to more people throughout the organization. This is just an example, but the pack is flexible enough to pull and share data with specific people or everyone.

If you’re interested to learn how your organization can initiate plans to optimize your QuickBooks data or have questions on where to start, connect with us today for a conversation.

Alternative Text

Sean Warren Program Manager for Implementation and Solutions Consulting @iDashboards

Sean has helped hundreds of clients transform their rows and columns into beautiful dashboards and is currently focused on client on-boarding, consulting, and retention. He has a passion for non-profit success with experience in QuickBooks, Microsoft SQL Server, and various data-prep tools. When he's not building dashboards, you'll find him on a mountain bike with his son (Arthur) or vacuuming fur from 2 Siberian Huskies (Nymeria and Sven).

Comments

Your email address will not be published. Required fields are marked *