Question

How do I create an average days to pay report for one of our clients?

Forum|Forum|7 years ago
January 16, 2019
58 replies
168 views

No text available

QuickBooks Online

Currently, the report to show the Average Days to Pay report is unavailable in QuickBooks Online. However, I can guide you to prepare a similar report as a workaround.

You can send reports to excel to calculate. Note that you can only export a report to excel one at a time.

Here's how you ca do it:

From the left menu, select Reports.
Search for and open the report you'd like to view.
Select the Export icon, then choose Export to Excel.

Also, you can check if there is a third party reporting application that would create this report for you in the Appcenter.

I'll be around if you have any questions about QuickBooks. Have a good one!

G

Gaelwyn

Adding my agreement that QBO needs to have the "Average Days to Pay Report".

QBO users end up paying well more over time in subscription fees than Desktop/Enterprise users, we should have access to the same features and reports. I only use QBO because my team is remote, so access to QBO is easier and more reliable than remotely hosting Desktop/Enterprise.

I would be happy to export a report into Excel and do some magic to figure the payment averages out myself, but honestly, there is not really any workaround listed that will solve the issue for me other than spending hours manually calculating it via the "Invoices and Received Payments" report. Even if the payment date could be added onto the "Invoice List" report, that would be a huge help.

What I am going to use is the "Invoice List" with the "Last Modified" date column added, and make a wild and inaccurate assumption that the invoice is last modified when the payment is posted. That is only true if you always post the payment on the day it is received and never touch the invoice after that for any reason, but it is the closest thing that I have without a lot of extra manual work.

Please add the report for us soon. It should be a top priority for new features.

Cheers!

A

ADM Roofer

This truly is ridiculous. Who is making feature decisions at QB? Obviously no one who has any experience in actually running a business. The excel workarounds are time-consuming, inefficient do-ate-my-homework solutions. What if I have a client with multiple outstanding invoices who pays several at once? How can I do the suggested calculations? Seriously, this needs to be prioritized. I cannot believe that such a basic function EXISTED and was then deleted in the transfer to the online platform.

And what is this garbage about searching for an app? Which app?! Can't you even make a suggestion? Basically what you're saying is we should solve this issue ourselves by doing our own research to fins someone who understands accounting better than you do and then pay them extra for a fix.... not impressive.

J

jbowman1611

Good evening All,

I'm not terribly surprised in QBO's deficiency -- the lack of this report is just another example, but here is my solution:

Three parts are shown: 1) the basic QBO Invoices and payments report (Col A-G), 2) the "Average" calculations (Col I-L), and 3) the formulas used in Col O.

A couple of "helper columns are used to "realign" the payment with the corresponding invoices. The formulas listed in col L, are each copied to the respective columns, then copy & pasted to the bottom of the report -- in my case row 34.

The first problem is in separating and identifying which lines are payments versus invoices. The col I & J formulas check and transfer only the Payment number and date, but for each line/invoice to which they apply. Col K formula returns the number of days between the invoice date (Col B and payment date Col J), but only for invoices -- payments always return -1. Finally (almost), Col L take the average of all payments with the same number (Col I), but does not include the payment (K= -1) in the average -- this happens only when the payment numbers change [caused by the outer if()]. Finally, (for real now) the bottom of Col L a standard "Average()" is used to obtain the average days to pay. Not to be an over achiever (LOL) but since I was here I added Min()/Max() but only on "Invoices", too.

Part of the beauty of this report, is that it can be easily used as a template by running the base report, then copying the report to the left side of the template, there will be some minor clean-up needed on the right side (based on number of rows), but the formulas should only need to be copied/moved down.

Hopefully, this will make the formulas easier to work will

I (eye)	IF(C7="Payment",E7,I5)
J	IF(I8=I7,J7,G8)
K	IF(C7="Invoice",J6-G7,-1)
L	IF(I7=I6,"",AVERAGEIFS(K:K,I:I,I7,K:K, ">=0"))

And the bottom:

L	AVERAGE(L7:L34)
L	MINIFS(F:F,C:C,"Invoice")
L	MAXIFS(F:F,C:C,"Invoice")

Hope that helps.

IamjuViel

Level 8

I am delighted to have you here, @jbowman1611.

I appreciate the steps in generating an average day to pay report that you've shared. The steps will help a lot of customers who are also having the same concern.

Your QuickBooks Online subscription comes with the ability to track income and expenses, send invoices, run payroll and pay and file your taxes. I suggest checking out these articles for tips on how to unlock the software's full potential: QuickBooks Online Help Articles.

You can post anytime if you have questions or want to share your best practices using the product. Have a great rest of your day.

L

ldemichele

I agree. As well this is a must need report. Hopefully it is added only to the "essentials " version of quick books. Please Hurry. Also had an option to put customers on hold.

Thanks

Lisa

J

Joelo

Another example of how awful QBO is. I migrated 3 years ago because I had an off-site employee that required access the program and there was no other reasonable way to achieve this. I won't even get into the number of deficiencies this program has compared to desktop. I have 3 pages of stuff that's missing. Intuit made it really easy to migrate to QBO but left no way to bring data back into desktop. No big surprise there.

Now I have to decide if I want to go back to desktop or a completely new program because QBO is not cutting it for me.

T

tstruminger

3 years later.....still nothing. But they've increased the price a few times since this question was first posted....

D

Dan P11

Yes, this previous, but currently unavailable functionality is just another feature that has been taken from us in QBO, regardless of subscription level... not that I would pay a dime more for this "unaccounting system". Many of us are looking to run for the exits at this point... Nice job Intuit!

C

ChristineJoieR

QuickBooks Team

I heard your sentiments, @Dan P11.

We are committed to providing the best possible service to our customers, and your feedback is advantageous in helping us achieve this. We are constantly working on updates and improvements to enhance the functionality and user experience of QuickBooks Online.

We appreciate your patience and understanding as we strive to make QuickBooks Online as comprehensive and user-friendly as possible, and we would like to send our heartfelt gratitude for being with us and for using QuickBooks Online.

If you are willing to submit your suggestion, check out the steps below:

Go to the Gear icon.
Under the Profile column, select Feedback.
Share your suggestion, then click Next.

You'll want to track your suggestions through this website. You can also visit our blog to keep up with our recent events and developments.

If you have other questions about managing reports, add them below. Please don't hesitate to reach out if you have suggestions or concerns. Your satisfaction is our top priority.

R

Rocker252

This thread was created January 2019. It is now almost July 2024. 5 1/2 years later and STILL NOTHING has been done to create this report that we are all screaming for.

Seriously, who do I have to get fired to get this 1 report built? I know a lot of PE and hedge fund guys who would love to short Intuit stock just to make a few bucks. Customer negligence is the #1 reason companies fail. And when Short Sellers smell blood in the water . . .

Sign up

Login with SSO

Let's get you signed in

Login with SSO