Source vs. Target in QBO Plus and Why Fox Mulder is Not at Peace by Woody Adams
Thursday, as most of you know, Stacy and I resurrected the QBO Show, after a 6 month retirement. We could have just left it alone, but the joy of doing the show along with creating a free resource to solve various QBO and QBO related issues/topics was just too appealing. I mean one freakin’ hour a week? Certainly, we can take that on. In order to commemorate the return, we decided to discuss one of my personal favorite enigmas in the QB-verse, Source vs. Target. The reporting paradigm can be found riddled inside the code of both QBD and QBO, and is there to provide guardrails and a formula for how reports pull and display data.
Before we dig into this mystery, here’s a video that might help understanding Source vs. Target in QBO Plus: http://bit.ly/1Ufptlq
Though I would love it if Source vs. Target vanished most days when I am trying to run a certain report and having no luck, the removal of such reporting rules would only lead to utter chaos and our biggest, fear, QuickBooks Anarchy. It is comforting to know Source vs. Target is there, roaming the forest behind the numbers, preventing the larger, more violent animals from devouring gentle big data and spitting it out into something nonsensical.
That being said, Intuit can make some adjustments to the long time reporting structure to tame the crazier beasts into more harmonized, kinder, furry woodland creatures. It is our duty as consultants to come to grips and fully comprehend Source vs. Target in a fundamental way, eventually, in order to navigate Reporting in QBO. If you understand a thing, you can bear the frustration when you hit a wall. If you do not understand a thing, you just get pissed off and risk giving up.
The below focuses on the former, and all this info I gleaned is from Laura Redmond’s QBO Advanced Certification Genius pdf, that is worth however many hours you one day pour into that exam. Really I know no one on the earth today that possesses such a deep and enviable grasp of the reporting concept, and was able to put it in such a way that a whiny Luke Skywalker of QBO like me can absorb to relay intelligibly. Clint Eastwood said it best, “A man’s got to know his limitations”. It is glorious when you have friends that are able to help you break through those limitations, even if for a brief time. On a lighter note, I hear that Chris Carter of the X-Files is planning a one hour show (well, more like 48 mins) on Source vs. Target in QuickBooks, mostly focused on the main nemesis, The Balance Sheet by Class report. Once you read and understand the below, you will not need to watch the episode, but you might anyway as it will be really funny.
The below fundamentals from Laura Redmond uncover what is source (summary) and target (detail), where is it, and how do you know so you can make better decisions when customizing reports in QBO. The below is not focused on QBD, however, many of the rules apply there as well. The 3 big differences between S v T in QBO vs QBD are:
- The top line of a JE in QBD is Source. Every line of a JE in QBO is Target.
- Class tracking can be both Source or Target in QBD, in QBO it is solely Target (why the BS by Class fails)
- The Detail Level filter is only supported in QBD. QBO desperately needs this filter when customizing reports. It is one of the more humorous aspects of QBD. Kind of like the Previous button on transactions…I always assumed it was broken but there are many who have far greater luck than I in using it….I need to stop making fun of it…
- The source of a transaction is the summary information about the transaction and includes the transaction date, transaction type, reference number, name, memo, account, location, job and the total amount of the entire transaction.
- Each element of source data occurs only once in the transaction. On an invoice, the source data is found in the top header section of the screen and the source account for an invoice is Accounts Receivable.
- The targets of a transaction provide the information about how the transaction is distributed in the general ledger, and include memos, accounts, amounts, location, class and job.
- Each element of the target data set can appear multiple times in the transaction. On an invoice, the target data is found on the line item grid in the middle of the screen and the target accounts for an invoice are related to each of those produce/service items and any related inventory costs.
Determining what is Source and what is Target
- You can determine the source and target information of an existing transaction by viewing a Transaction Journal report—click the More tab at bottom of invoice, then Transaction Journal.
- The information contained in the first line of the report is the source data. See the Accounts Receivable category and the total amount of the invoice? This is considered the source of the transaction.
- The rest of the lines are target data. They show how the transaction was distributed to the general ledger.
- Some of the source data is copied to the target fields. This Transaction Journal report doesn’t show it, see how the target lines do not show a transaction date, type, reference number or name?
From a P&L, you can drill down on an income account to view a detail transaction report. Here you’ll see transaction targets distributed to this income account. We can see the target data that was copied from the source, such as transaction date, transaction type, reference number and name, even though the Transaction Journal didn’t show it on the target line. This is because the P&L report pulls both Source and Target data. The X-Files part is the fact I am filtered down on a Distribution account, which is Target or Detail, and yet, I can see the AR split! What????! Again, proves my theory here that the P&L looks at both Source and Target, even though in its heart, it is a detail or target leaning report…
- Note: Every line of a JE is target in QBO. So, it is possible to show class on the BS for AR or AP line if use a JE. But not ideal for workflow and sales reports will be incomplete. Neither Stacy or I (nor Lulu J) encourage using journal entries just to solve for Balance Sheet by Class.
Now, how does this paradigm impact a Balance Sheet by Class in QBO Plus?
- When you set up classes to assign one class to the entire transaction, all target lines are assigned to the specified class
- When you set up classes to assign to each row in a transaction, each line item represents a target that is associated to the office supplies expense account and a different class
- Class is never associated to a source, only targets, even when you assign one class to the entire transaction.
- Since BS activity is usually Source, BS by Class is mostly not viable.
- Most balances will be reported under the “Not Specified” class column since sources are typically recorded to cash, bank, credit card, A/R and A/P accounts and do not contain the class field.
- Targets recorded to Balance Sheet accounts, such as fixed asset purchases or loan repayments, will show in their class column on the Balance Sheet by Class report, if the class was specified.
- However, since sources are posted to the unspecified class column and targets are posted to the specified class column, the BS will NOT balance per class.
- You will see your classes you specified on the grid lines of a bill or invoice as Unspecified on the BS by Class, even though it is clearly specified. Thus, your AR and AP columns by class on the BS will be blank. Ouch…
- PL activity is always Target, so PL by Class is perfect, except when you do not specify classes on transactions. The only reason I have amounts in Not Specified on my P&L by Class is I didn’t specify a class on those transactions. Easy fix. The Balance sheet above, when you drill into the Unspecified amount and to the bill or invoice, whatever, the class is in fact specified in the class field, but the report is blind to it. The CRUX of the issue…This is where the Smoking Man wants to kill Agent Mulder, however a colleague much wiser than he prevents the killing, as he wants Fox to spend all is time trying to Specify a class that was specified way back in 1947. It cannot be specified, so either way, the clandestine, insidious secret powerful group running things behind the scenes of our country wins again. Agent Mulder knows it is there, he is so sure he can reach out and grasp, but it will forever be outside his grip, till the day Class is considered not only target in QBO Plus, but source too.
Locations are a better alternative, assuming you don’t have purchases and sales for different locales on same transaction…
- With locations, you categorize the entire transaction to one location. This assumes that you do not have purchases and sales for different locations on the same transaction. Each location tracks money in and money out on separate transactions for each location.
- When a location is saved on a transaction, it is saved on the source and the targets. This makes it possible to run a Balance Sheet by Location report and get correct results.
- Note below, had I specified a class on all invoices, there would be no Not Specified column on the below BS by Location. Location is the answer, HOWEVER, only if your client does not need to SPLIT lines on a bill or invoice across multiple locations.
Bonus X-Files Intrigue—Finding Unapplied Cash Payment or Income on the Cash Basis Profit & Loss report…
- On a cash-basis Balance Sheet, the Accounts Receivable and Accounts Payable balances should be zero, no accruals. On a cash-basis Profit & Loss statement, only income received and expenses paid are accounted for.
- When a cash basis report is generated, QuickBooks Online disregards open accrual transactions such as unpaid customer invoices and unpaid vendor bills.
- If you record a customer payment via the Receive Payment screen and do not apply it to an invoice, QuickBooks Online credits the AR account. When you run a cash-basis Balance Sheet and P&L, QBO has to report this cash receipt somewhere, it can’t show it as AR, so it reports it as Unapplied Cash Payment Income.
- Likewise, a bill payment that is debited to the AP account but is not associated to a bill, has to report the cash expenditure somewhere on cash-basis reports, and it can’t show it as AP, so QBO reports it on a cash basis P&L as Unapplied Cash Bill Payment Expense.
- These transactions are not actually posted to these GL accounts, they are merely calculated and displayed as balances in these unapplied cash accounts for purposes of the cash-basis report you are running.
- Similarly, if a bill payment is dated earlier than the bill it is applied to and the cash-basis P&L is dated in between — after the bill payment and before the bill date, the bill payment is shown as Unapplied Cash Bill Payment Expense.
- Same for customer payments received earlier than the invoice date, the payment is shown as Unapplied Cash Payment Income when the report is generated for a date prior to the invoice date.
- In addition, the inventory adjustment to COGS is not recorded on this report, because it is recorded by the invoice and the report is dated earlier than the invoice date.
How to give Agent Fox Mulder peace
I have two suggestions for my development friends at Intuit re how to solve in QBO Plus. One, either allow for multiple locations per transaction. Two, add the 3 custom fields on sales forms to expense forms, and support a drop down list for these fields
Of course we could go after the big enchilada, the worm at the bottom of the Mescal, the sludge at the bottom of my Turkish coffee…Make Class accessible to Source as well as Target.
The Truth is Out There.
3/4/16, Woody Adams, Tucson