Creating Cash V/s Accrual reports after export of data from Quickbooks

I'll share some information on how QuickBooks generates cash basis reports, @GirishB.

When running a cash basis report, QuickBooks shows you the income and the money you've received in cash and expenses you've paid in cash.

There's a possibility some transactions were not added, thus, causes the numbers to mismatch.  To isolate this, you'll want to check and ensure you've included all transactions to avoid any confusion. 

You can also check this link for more details about the cash basis report in QuickBooks Desktop (QBDT): Differentiate Cash and Accrual basis.

Additionally, I've added this article that serves as your guide in modifying your reports in QBDT: Customize reports in QuickBooks Desktop.

I'm just a post away if you have other follow-up questions about your reports. It's always my pleasure to help you anytime.

Hi,

Thank you for such a quick reply.

I think I need to explain my issue in more detail. Firstly, I have exported all the data from QB into a SQL Server Database and using SQL queries to generate the reports.

In the exported data, there is a table called transactions which has all the debits and credits in all the accounts. I am using this table to create reports. So, lets say it is a balance sheet report for 12/31/2020, I take all transactions from accounts which affect BS till 12/31/2020 and sum the debits and credits for each account to get the report (Net income and Retained Earnings have different logic but that has nothing to do with the issue at hand). When I do this, my report matches BS report from QB when run in accrual mode. For cash mode there is a different logic. It mostly affects Accounts payables and receivables.

Let assume there is an invoice booked on 12/30/2020 and payment was received on 01/10/2021. When the report is run for 12/31/2020, in accrual mode this transaction will be picked but in cash mode it won't be as the money was not yet received till 12/31/2020. For me to figure out if money was received or not or when it was received, I have to link the 12/30/2020 and 1/10/2021 transactions. QB has tables for that called linkedTxn tables (BillLinkedTxn, InvoiceLinkedTxn, etc.). I use those and some differences go away but not all. There are still some transactions which the cash report is ignoring but I can't find any linked transactions for them. Which lets me to believe there more to this logic or my logic is completely flawed.

So, I want to know how QB calculates cash based reports in terms of exported tables like transactions, linked transactions, etc.

Thanks,

Girish.

Let me provide some information about Cash Basis reporting, GirishB.

The Cash basis report only shows income if you have received cash and expenses or if you have paid cash. However, when paying the invoice before the invoice date, it will not reflect in the Profit and Loss Cas Basis report.

If only the invoice is dated within the same period as the income statement, the dates matter for the reporting. Where the payments applied to invoices result in the Cash Basis reporting for the payment date and not the invoice date.

You can check this website that'll help you more about exporting data from QuickBooks to SQL Server Database: Intuit Developer Community

For more details about Cash and Accrual basis, check out this article: Differentiate Cash and Accrual basis.

I've got a link here where you can find articles about managing your reports: Reports gathers info from setting up reports to customizing, running, and sharing reports.

Fille me in if you need a hand with your reports. I'll be here to ensure your success. Keep safe and healthy. 

Hi,

The problem I have is how do I know when the cash is being received for a particular invoice or paid for a particular bill?

For example if i have multiple invoices in the month of 12/2020 and multiple payment received transactions, some in the same month while others in 01/2021. How do I know which ones are received in 2020 and which ones aren't? How do I link the invoice/bill transactions with payment received/paid transactions? The transaction ID for these transactions are different, also certain invoices are paid in parts so the amounts are not exactly same.

If I am running a BS report for 12/31/2020, how do I know which invoices have received payment by that date (partially or fully)?

Thanks for your help. I really appreciate it.

Regards,

Girish.

I appreciate you for sharing more details of your concern, GirishB. I'd be delighted to ensure you're able to view all the info needed.

Our Intuit Developer Support can help you view all the details in your sales transactions and their payments. With this, I'd suggest reaching out to them. They can provide accurate steps to pull up the data.

Here's how:

1. Go to https://help.developer.intuit.com/s/.
2. Choose QuickBooks Desktop under What do you need help with?. 
3. Click Ask a Question.
4. Log into your existing Intuit Developer Account or create a new one. 
5. Enter your concern. 
6. Click Ask. 

You can also run the Transaction List by Customer in QuickBooks Desktop to compare it to your exported data in SQL Server Database. Here how:

1. Select Reports from the top menu bar.
2. Choose Customers & Receivables, then select Transaction List by Customer.
3. Click the Customize Report button.
4. In the Display tab, set the appropriate reporting date.
5. In the Filters tab, select Transaction Type from the Filter menu.
6. Choose Multiple Transaction Types from the Transaction Type drop-down menu.
7. From the pop-up window, select Invoice and Payment and then click OK.
8. Click OK again to complete the process.

You can double-click the payments to know which invoice it was linked to.

Additionally, I've also included this article that'll help you in tracking how your business is doing: Customize Company and Financial Reports.

Please leave a comment below if you have any other QuickBooks-related questions or concerns, GirishB. I'm always here to help. Have a good one!

Hi,

I have posted my question to developers forum:

https://help.developer.intuit.com/s/question/0D54R00008KAgEISA1/creating-cash-vs-accrual-reports-aft...

Thanks,

Girish.