Gross profit by customer in QuickBooks Online for distributors

How do you run gross profit by customer in QuickBooks Online?

1. Let costs carry a customer. Go to Settings > Account and settings > Expenses, turn on Track expenses and items by customer, then select Save and Done. On each bill or expense, pick the customer in the Customer column.

2. Run the report. Go to Reports > Standard reports. In Find report by name, select Profit and Loss, then Customize. In the Columns dropdown, select Customers, then Run report. Profit and Loss by Customer is also listed as a standard report.

3. Use accrual. Under Accounting method on the report, select Accrual. Intuit tells cash-basis businesses that track inventory to switch to accrual “in order to see the correct Gross Profit amount.”

4. Add margin percentages. In Customize, open the Rows/Columns dropdown and check % of income.

5. Save it as a custom report so you can rerun it each month with the same settings.

6. Export it. Select the Export/Print dropdown, then Export to Excel. If data is missing when the file opens, select Enable Editing in Excel.

Which costs never reach a customer’s column?

Intuit says to select the customer’s name on each transaction, or it won’t show under that customer. The usual gaps for a distributor:

• Stocked items are fine. “COGS is debited only when you sell inventory items on invoices or sales receipts,” so the cost lands on the customer you invoiced.
• Drop-ship and non-inventory purchases reach a customer only if the bill line names one, and they post in the month of the bill.
• Outbound freight usually arrives as one carrier bill for many shipments, with no customer on any line.
• Rebates paid by check or journal entry carry no customer. Credit memos do: “Credit memos impact sales reports, even if you don’t apply them to invoices.”
• Journal entries without a customer on each line.
• Duplicate customers split one account across two or three columns. The duplicate customer finder lists likely pairs from your customer export.

The report also can’t see how long each customer takes to pay.

How do you add freight, rebates and slow payment in Excel?

1. Turn the export on its side. Copy the range with its labels (Cut won’t work), right-click an empty cell and choose Transpose under Paste Options. If the data is an Excel table, convert it to a range first.

2. Keep one row per customer with income, cost of goods sold and gross profit.

3. Add freight. Sum outbound freight by ship-to customer from your carrier invoices or shipping software.

4. Add rebates and allowances paid outside a credit memo, from your rebate agreements.

5. Add the cost of slow payment. Go to Reports > Standard reports, and in Who owes you select Accounts receivable aging summary. Run it as of each month-end, average each customer’s open balance, and multiply by your annual borrowing rate.

6. Sort by adjusted margin, lowest first.

Example

	Customer A	Customer B
Income	480,000	120,000
Cost of goods sold	384,000	90,000
Gross profit in QuickBooks	96,000 (20.0%)	30,000 (25.0%)
Outbound freight	21,600	2,400
Rebates paid outside credit memos	9,600	0
Slow payment (average balance × 9%)	8,100	900
Adjusted gross profit	56,700 (11.8%)	26,700 (22.3%)

Customer A has four times the sales and about half the margin once freight, rebates and payment days are counted.

For the slow payers, the AR aging chase list tool turns the aging export into a call list, and chasing overdue invoices in QuickBooks Online covers reminders and credit holds.

How do you check the numbers are right?

• The Total column ties out to your regular Profit and Loss for the same dates and basis.
• The gap is your to-do list. Costs with no customer stay in the Total, so the customer columns add up to less.
• Two customers you know check out against one month of their invoices, credit memos and bills.
• Freight adds up to the freight account on the Profit and Loss.
• New bills carry a customer. Spot-check a week of drop-ship and freight bills each month.

To put the same numbers on a dashboard, see rebuilding a QuickBooks Online dashboard after the Power BI connector change.

What are your options?

Option	Good for	Watch for
Do it yourself with Profit and Loss by customer and Excel	A yearly review of your top accounts	Freight and rebates added by hand every time
Buy a tool: a reporting app connected to QuickBooks, or Spreadsheet Sync on QuickBooks Online Advanced	Running the same report each month with less copying	It still sees only costs that carry a customer
Hire your bookkeeper or a freelancer	Tagging bills to customers as they’re entered	Freight split rules and rebate data come from you
Bring in an outside team (Reports & answers)	Adjusted margin by customer from your books and carrier data	Read access to QuickBooks and your rebate terms

FAQ

Is there a gross profit by customer report in QuickBooks Online? Use Profit and Loss with Columns set to Customers, or the standard Profit and Loss by Customer report. Gross profit is the line after cost of goods sold in each column.

Why does a customer show 100% margin? Its costs were never recorded against it. Drop-ship and freight bills without a customer on the line are the usual cause.

Should I run it on cash or accrual? Accrual. Intuit says cash-basis businesses that track inventory need accrual to see correct gross profit.

Do credit memos lower a customer’s gross profit? Yes. Intuit says credit memos affect sales reports even when they aren’t applied to an invoice.