QuickBooks applies a customer payment to an invoice with multiple line items by applying the proportion of the pre-tax subtotal paid to each individual line item and applying the remainder to sales tax on the invoice.
If an invoice is paid in full, this obviously results in every individual line item and the applicable sales tax marked as paid in full.
If the invoice is only partially paid, individual line items on the invoice will be partially paid in the proportion that the total payment bears to the pre-tax subtotal.
Let’s review an example using the sample data for Castle Rock Construction from QuickBooks 2009.
As of 12/4/2012, customer Teschner (an example name only) has 2 invoices outstanding: invoice # 1064 dated 10/5/2012 for $760 (the balance remaining) and invoice # 1085 dated 11/28/2012 for $8305.95. On 12/5/2012, Teschner makes a payment of $5000 on check # 56321. A portion of this payment, $760, is applied to the older invoice, # 1064, paying that invoice in full. That leaves $4240 (i. e., $5000 – $760) to partially pay invoice # 1085.
Invoice # 1085 contains 8 line items with a pre-tax subtotal of $7920 and sales tax of $385.95, for a total of $8305.95. To determine how QuickBooks applies the partial payment of $4240 to the line items on this invoice, compute the proportion of the payment to be applied ($4240) to the pre-tax subtotal ($7920), or 0.510477429 ($4240/$7920).
Next, multiply this ratio by the pre-tax subtotal of each line item on the invoice. The first line item has a subtotal of $600. Therefore, the amount paid on this line item is $306.28, or 0.510477429 X $600. If we continue this process for each line item on the invoice and sum the amounts paid, that total will be $4042.98. Since we started with a total of $4240 to apply to this invoice, the remainder of $197.02 (or $4240 – $4042.98) is applied to sales tax.
By creating a cash basis Custom Transaction Detail Report, we can see exactly how QuickBooks applied the payment to an invoice. We’ll use the date parameters and the Filters on the Modify Report… button to highlight the exact application of the original payment of $5000. Remember, a portion of this amount, $760, was applied to an older invoice. This appears as the second line on the report. The amounts in the debit column correspond to the exact application of the check. For invoice # 1085, the invoice with multiple line items that was partially paid with this check, the amounts in the debit column represent the payment applied to each line item on the invoice.
(0 rating, 2 votes)
Loading...
Your example is not calculating the math correctly. It says that 4240/7920 is 0.510477429, but in fact, it is .535354 and if you calculate out all the pre-tax line items at that rate, you use the full $4,240, leaving no remainder for sales tax. Instead they are using the rate of $4,240/$8,305.95 the full invoice amount. So which is it? Does it calculate on the pre-tax invoice amount or the total?
I am an HOA manager and in my case, I do need to apply partial payments to specific amounts owing. The HOAs have a collections policy that payments be applied to dues first, legal fees second and to then to any late fees/fines. That application will also affect their different income accounts (dues income, fines income). It can’t just be applied to the oldest invoice first.
Thanks
Kathy, to satisfy your requirements, you’ll need to have a unique invoice for each type of fee. That way, you can apply payments to comply with your HOA’s policy. Our article deals with applying a payment to a single invoice with multiple lines in the situation where the invoice is not paid in full. QB does not give you the control to select which line items will be paid; the application is automatic. Therefore, if you were to put dues and legal fees on the same invoice, and an HOA member made a partial payment on that invoice, QB would automatically allocate the payment among those line items. That allocation would not comply with your policy. As long as you never mix charges of different types with a different application schedule on the same invoice, you’ll be able to choose which invoice – and therefore which type of charge – is paid with a particular member payment.
Do you think there is any chance that Intuit will enhance the receive payment functionality to allow a payment to be specifically applied to a line item on the invoice?
This is important because otherwise at the end of a reporting period (think fiscal year) the income apportioned to items on the invoice will be wrong and lead to odd P&L results.
Here is an example:
The customer invoice is for two line items. $50 for a widget-service and $10,000 for a gadgets. The widget item is tied to WidgetServices income, while the gadget item is tied to GadgetProduct income.
We receive payment in December of $50 for the widget service and payment in January for the Gadget service. My P&L in January now shows that I have widget service income this year, and the paper trail of payments is confuisng at best.
Assuming that Quickbooks is not going to be enhanced in this way (since I have been requesting it for 5 years now), What alternate way do you recommend to handle this?
My actual usage scenario is more complicated and has to do with receiving payments on invoices associated with a client that are paid by multiple third party payers. More specifically, we get commission income each month from multiple insurance carriers for each client. I create a single recurring memorized transaction for each client that includes all of the expected commission income for the month. Each invoice may have up to 15 lines of coverage all paid by different carriers for each policy that is in place. There may be multiple medical, dental, life, 401K, vision etc line items each paid by a different company with multiple payments from each third-party company possible each month.
Creating separate invoices for each third-party and somehow tying them to the client seems intractable. While allowing payments to be applied directly to line items seems intuitive.
What does the community recommend?
Unless I misunderstand specific implications of what you’re describing, I don’t it’s a problem in your situation, but I will provide an example where it could be more of a problem.
First, QB supports both cash and accrual basis reports, and the reporting basis can be changed on any given report. The application of a customer payment is only meaningful to cash basis reporting, not accrual basis. Using your circumstances, your commission income from 15 different lines or insurance products is mapped exactly to the revenue accounts associated with the items on the invoice when you produce an accrual basis report.
Second, cash basis reporting is most commonly used for tax reporting. But let’s imagine that for your internal management purposes, you want to track income on a cash basis. Law firms often want to track cash basis revenue for sharing income among partners, so it’s easy to see the importance of cash basis reports even for internal use. One simple solution: record a sales receipt when payment is received, and record it exactly for which product or service was paid. Perhaps you need a “triigger” document to prompt those paying your commission to remit. How about a non-posting document like an Estimate? The form itself can be modified to identify itself as an Invoice. When payment is received for a line item, the items paid in full could be invoiced or included on a sales receipt. That would cause cash basis reporting to produce the same outcome as accrual reporting, namely that revenue reported would match the items on the invoice or sales receipt. You could have a report of the open items from Estimates, and this in effect would be your unpaid A/R. Of course, all of this assumes that you really want to manage (rather than pay taxes) on cash basis reports.
In a world of billing for services, the argument for changing allocating payments to line items is compartively weaker than for goods. Consider a company that sells a physical product but pays license fees or royalties based on the cash basis sales of the products. If there are line items on a given invoice that require license fees to different vendors, QB’s application of customer payments is a problem. Such a firm needs accrual accounting to record what goes out the door as a shipment, but the cash basis reporting won’t produce the right amounts to pay license fees if customers selectively pay line items on an invoice. If they pay as selectively as you describe, license payments could be incorrect – and in a material way. Fortunately, these instances are very rare.
My perception is that this isn’t a mainstream requirement, and Intuit’s response has been that it can’t cover every usage scenario in a mainstream accounting product. I’m not aware of another mainstream accounting product that allows customer payment application by invoice line item. Therefore, I wouldn’t expect to see this in QB, at least in the near future. However, QB 2011 is due out shortly. Last year, QB 2010 came out in late September. I’m sure there will pleasant surprises on the new feature front; likewise, there will be those who are still left waiting for progress on their wish lists.
That’s probably not the response you wanted. Maybe I am missing some particular need for internal cash basis reporting. Let me know what I missed. If you can make a strong argument for it being a mainstream requirement, you might see it in QB. One final thought: have you checked industry-specific QB add-ons?
You raise a thought-provoking question, and there’s always a balance of addressing a use scenario vs changing the way millions of business owners record payments. We’re moving our forum content to our main site, so keep following the discussion there.
Thank you for your detailed answer.
Where do I look for this on the main site? I went to the Knowledge base there but could not find this thread.
Thanks,
-robert
Robert – We’re moving over content at a steady pace, so for about another week or so, content will be in both places until we’re done. When this article gets moved, the comments will follow along with it.
By the way, we do want to help you figure out a way to get your reporting needs met. Let us know if any of the suggestions are helpful. If not, we’ll try to come up with something else.