How to Create a Report that Shows How Payments Were Applied in QuickBooks
The reporting capabilities in QuickBooks are somewhat limited. However, you can sometimes modify reports to get the information you are looking for.
There are programs you can buy called “add-ons” that are created to work with QuickBooks and do things the software cannot do, or doesn’t do the way you’d like! You can visit the Intuit Marketplace to search for them.
There are two built in reports in QuickBooks that will show the links between transactions-the Check Detail and Deposit Detail reports. Let’s modify the Check Detail report to see how payments are applied:
Call us at Quickbooks Our Toll- Free Number
+1(855)548 3394
-From the menu bar, go to Reports > Banking > Check Detail
– Click on Modify Report button in upper left
– Click on the Filters tab
– Click down arrow next to Account box, choose All Accounts
– Click on Amount, change to All
– Click on Transaction Type, choose Payment
– Click on Detail Level, choose All except summary
You can add some additional columns from the display tab to further modify this report. For example, add in Pay Method and then you could filter for checks only. You can also filter by customer on the Filters tab, select Name.
This is not a perfect report. It will show the deposit that the payment is in (linked to). You can always export to excel and delete the deposit information. After exporting to excel, create a static column number so that, after you delete the deposit lines, you can re-sort to original order:
– Click the Export button and export your report to excel
– Insert a new 1st column “A”
– Insert a #1 in cell A1
– In cell A2, enter this formula without brackets [=A1+1]
– Copy cell A2, Ctrl+C
– Highlight cells in column A-A3 to A?? (to end of your rows)
– Paste the formula into highlighted cells, Ctrl+V
– Highlight the entire column and copy it, Ctrl+C
– Right click, choose Paste Special > Values, this overwrites the formula
– You have just “hard coded” that column
– Next, sort by the Type column
– Delete all the deposit lines
– Re-sort by column A
You have deleted the deposit lines and put the list back in original order. The report is still not perfect, but you can see what you can do with it. Hard coding column A is one of my favorite excel tricks when working with exported reports.
Using QuickBooks Sales Reports
The QuickBooks Sales Reports can give you more information on your billing and sales processes. In addition to viewing amounts due from customers, open invoices and average days to pay in the Sales Reports section, you can view sales by customer and by sales representatives. The sales report in this section is useful for determining your largest customers as well as giving you better insights into your sales representatives’ performances.
You will find a list of different QuickBooks Sales Reports along with a description of the information contained in the report below.
Sales by Customer
QuickBooks Sales by Customer Reports will give you more information about how much you’re selling to each of your customers.
Sales by Customer Summary: The Sales by Customer Summary Report will tell you what the total sales for each customer as well as the total sales for each job.
Sales by Customer Detail: The Sales by Customer Detail Report is similar to the Customer Sales by Summary report discussed above, except that this detailed report breaks down the sales of each item by each transaction.
Sales by Ship to Address: The Sales by Ship to Address Report displays the sales by the shipping address, including a breakdown of each transaction by ship to address.
Pending Sales: The Pending Sales Report will show you all sales that are in pending status.
Sales Graph: The Sales Graph gives you a visual display of sales revenue by time period, customer, item, or sales representative.
Sales by Item
QuickBooks Sales by Item Reports will show you basic and detailed information about how each of your product or services are selling.
Sales by Item Summary: The Sales by Item Summary will tell you how many of each item or service you have sold, the total dollar sales, and the profitability of each product or service.
Sales by Item Detail: The Sales by Item Detail will show you the sales of each item with a detailed listing of each transaction.
Sales by Rep
QuickBooks Sales by Rep Reports will give you better insights into the performance of your sales representatives.
Sales by Rep Summary: The Sales Rep by Summary Report will show you the total sales for each of your sales representative.
Sales by Rep Detail: The Sales by Rep Detail Report will show you a detailed listing of sales for each of your sales representatives.
Open Sales Orders
QuickBooks Open Sales Orders Reports will give you more information about your small business’ open sales orders.
Open Sales Orders by Customer: The Open Sales Orders by Customer Report will show you the open sales order for each customer or job.
Open Sales Orders by Item: The Open Sales Orders by Item Report will show you the open sales order for each item.
More QuickBooks Reports
All QuickBooks versions have numerous pre-built reports, which includes the following:
Company & Financial Reports – these financial reports tell you how your company is doing financially.
Customer & Receivable Reports – these reports tell you how much your customers owe you.
Sales Reports – these reports give you information about sales rep, sales orders, and pending sales.
Jobs, Time & Mileage Reports – these reports give you information about your job estimates, including time, the amount spent, and mileage for each job.
Vendor & Payable Reports – these reports tell you how much money your company owes to its vendors.
Purchase Reports – these reports give you information about your company’s purchases and its open purchase orders.
Inventory Reports – these reports give you information about inventory value, stock, and work-in-progress.
Employees & Payroll Reports – these reports give you information about your employees and payroll expenses.
Banking Reports – these reports give you information about your banking transactions.
Accountant & Taxes Reports – these reports give you information about your basic accounting reports and information needed to prepare your income tax return.
Budgets and Forecast Reports – these reports give you information to compare your actual results to your budgeted amounts.
Lists Reports – These reports will give you phone, contact, and customer lists that you will find useful.
For most small businesses, you will only use a handful of reports on a regular basis, but you may have unique reporting needs that will require additional information. You should be sure to save any customized reports you make in QuickBooks by using the “memorize” feature in the program so that you do not have to spend time recreating reports from scratch on a regular basis. QuickBooks is a powerful program for small business owners, so if you take some time to learn these reports, you will get more out of this small business accounting software program.
Call us at Quickbooks Our Toll- Free Number
+1(855)548 3394
You can do WHAT in QuickBooks? Customizing Reports
In my role at Intuit®, I frequently hear feedback on ways we can make our products even better. I LOVE when I am able to say, “Guess what? You can do that in QuickBooks® Online (QBO) today!”
Many questions and comments revolve around customizing reports in QBO, and I often find that it’s just a matter of learning where the option is to configure the report, especially for long-time QuickBooks Desktop users. Here are a few things you may not know you can do in QBO to easily customize, save and share reports.
Before we get started, did you know that accountant users can quickly search for, and run, a report using QBO’s search? Start typing name of a report and QBO will filter any matching results, including any custom reports you have saved. It’s a quick way to jump straight to the report you want. You can also type “r:” to scroll through a list of all reports available in the client’s file!
If you have questions on which reports are available in the three tiers of QBO, here’s a Knowledge Base article.
Customization options vary based on the report you run. We surface common preferences along the top of each report to make it easier to customize quickly, such as choosing your report period or switching between cash/accrual basis. Always click Run Report to reflect your changes.
Along the top of the report, you can click Collapse/ Expand to collapse or expand your subaccounts, click Sort to arrange rows in ascending or descending order, and click the company name or report title to make changes to the Header.
Click Customize in the upper right to see the full options for the report, which will open in the drawer. There are several sections you can expand, depending on the report:
General
In addition to being able to change the reporting period and accounting method, you are also able to configure how numbers are displayed.
You can change the number format to divide by 1,000, display without cents and/or except zero amount. You can change the display of negative numbers to be -100, (100) or 100-.
Check the box to show negative numbers in red
In the My Custom Reports list, you can run your reports, export to PDF, change your sharing settings and view reports others have shared with you.
Additionally, you can schedule a single report, or group of reports, to email automatically on a recurring schedule. This is one of my favorite lesser-known features of QuickBooks Online!
Customers are the lifeblood of everyUntitled_design_6-4.jpg business. Making sure that the Customer Center in QuickBooks appears as it should is essential in managing a business. A regular run through of your Customer list will make it easier to identify things that need to be corrected and it will definitely help you avoid confusion in the future.
By opening the list of Customers, one can easily identify a customer with a zero balance, an unpaid invoice, or an available credit. It is the best starting point to validate whether customer balances are correct. Some other accounts receivable reports can also be helpful. Depending on the amount, if it is a collectible or an amount to be refunded, below are some tips to make sure that zero customer balances will appear as zero.
Credits and Refunds
There are instances when customers return items that are either paid or unpaid, or sometimes, as a result of unforeseen events such as failure to deliver the merchandise or that the product is not what the customer expected. This is when you need to decide if a Credit Memo or a Refund will be used. A Credit Memo can be used to offset an outstanding balance. It is most applicable if a customer returns or cancels an item even before paying for it, or for when they don’t want to get their money back now but want use it as a payment for future purchases.
On the other hand, the Refund option can be used to reimburse customers for overpayment or other paid services or items. In this regard, if there is an available credit or a negative amount showing in the Open Balance column for a customer, it can be dealt with by processing a refund.
First thing to bear in mind is that in QuickBooks Online, a refund can either affect the accounts receivable or just the bank balance. Second, there are two ways of processing a refund according to purpose: to offset an available credit or just to reimburse the customer for returned products. The Refund Receipt can be used without affecting the customer’s balance (that is, when it is already zero). To do this:
Open the Refund Receipt screen (Plus sign → Refund Receipt).
Fill out necessary fields and save the transaction.
However, when accounts receivable is affected, as is the case with an overpayment; an advance payment with no invoice or sales receipt; or simply just to offset an available credit, a Check or an Expense must be created.
Open the Check screen.
Fill out the fields, make sure to select Accounts Receivable for the Account.
Save the transaction.
Link the check to the open credit or unapplied payment.
Open the Receive Payment screen
Fill out the Customer and Date fields or the Memo field if necessary, but leave the other fields (Amount, Payment Method, and Reference Number) blank, including the Deposit too.
Put a check mark on both the Check and the Unapplied Payment.
Save the transaction.
Entering a Credit Memo affects the customer’s balance, but it still needs to be applied manually against an invoice. Once you have created a Credit Memo, link it to the invoice by recording a payment as above.
Fixing Incorrectly Applied Payments
Mistakes can happen! The more open invoices there are, the bigger the chance of applying a payment to the wrong invoice either for the same customer or to a different one. In some instances, the payment amount was accidentally modified, which can cause a discrepancy on the open balance. Other times, a payment is not linked to any invoice. To correct these, either edit or delete and re-create the transaction.
Unapplied Payment – You can get this status if a payment is not linked to an invoice or that the amount is an available credit. Make sure that there is an invoice to apply it to. Open the payment and link it to the invoice.
Incorrect amount – Simply edit the payment if it does not match the actual amount paid. However, if the payment has been deposited and reconciled, you need to first remove the deposit from the account register and take note if it has the ‘R’ mark. Edit the payment and redeposit it, making sure that the reconciled status has been put back.
Write Off Small Discrepancies
When the overpayment or underpayment is a matter of few cents and it would cost more resources to collect or pay the said amount, simply writing it off is justifiable. That is, of course, provided that you and your customer agree on this option.
Edit the invoice. This is the simplest way to get rid of those small amounts in QuickBooks Online. Add another line item to the original invoice using the same product/service that was already used or just create a new item for this purpose. Put a memo that can best describe the adjustment. If it is an underpayment, enter the amount as a negative number. For an overpayment, record it as a positive number.
Journal Entry. This option is commonly used in QuickBooks Desktop, but also works with QBO. Pull up the Journal Entry screen and check the date and the journal number. In the Account field, select Accounts Receivable. Enter the amount as Debit for overpayment, or Credit for underpayment and attach it to the customer. Select an offsetting account in the next row, which you may call Minor A/R Charge Off. This can be set up as an Income or an Other Income account. QuickBooks will automatically enter the amount either in the Credit or Debit column to counteract the entry in the first row. Save the transaction and apply it by receiving a payment.
The Customer Balance and the A/R Aging reports also provide lists of the transactions that need some attention. However, we recommend that whenever applicable, you record a transaction in Quickbooks the way that it actually happened. When in doubt, it is still best to seek advice from your accountant, especially for unusual scenarios where there is a need to create a new account.