Sales Invoice Matching
Creating invoices that represent your Square sales allows you to get more powerful insights, on Ambrook, into what you actually sold by taking advantage of Invoices exports. It also allows you to match your card and check deposits (and cash transactions) to an invoice instead of tagging them.
To get started, login into your Square profile and download both the “Category Sales” and “Sales by Payment Method” reports. You should set the date range on the export to whatever period you plan to do bulk tagging for. This can be monthly, quarterly, or even annually depending on how up to date you want your books to be over the course of the year. Here is an example of what the CSVs for both exports should look like.
The next thing you will do is create an invoice in Ambrook by using data from the two exports. From the Category Sales export the columns you will be using are Net Sales and Taxes.
In Ambrook you will use this information to create an invoice with line items tagged according to how your chart of accounts has been set up, for instance, the above export could be entered in as follows:
Notice that we lumped Pumpkins, Carrots, and Peaches into one income line called Produce in this example, but you could break those out into individual line items you want to.
To complete the invoice, you will also need to enter in data from the Sales by Payment Method export, specifically the Tips (if applicable) and Total Fees. You will add the totals of those columns into the invoice as line items - with tips as a positive amount and fees as a negative amount. Those two line items would look look like:
You will end up with an invoice that matches the Net Collected total from your Sales by Payment Method export, but breaks the sales up into items or categories.
Once the invoice has been created and saved, the matching steps are:
Tag credit card deposits:
Click the three-dots next to your invoice and select “Match payment”
Type Square into the search bar.
Match every deposit in the period to the invoice, with following exception:
If the last sales day of the previous month was on a holiday, do not match the deposits on the first day of the period. You may need to split that deposit into line items to account for sales in the previous period.
Once you have matched all the credit card deposits to the invoice, stop and check if the amount matched to the invoice is the same as the total net collected amount from credit cards on your Sales by Payment Report. You can find this on the invoice details page.
If these do not match, there are likely portions of the payouts at the beginning of the period or right after the period that you need to account for due to weekends or holidays. If this happens and you want to discuss, reach out to Ambrook Support or your bookkeeper for more details.
Tip - For a walkthrough of how matching looks in Ambrook you can watch this guided video. |
Tag check deposits:
Identify all the checks you deposited that you believe were related to Square sales. This may be individual checks or it may be portions of a bundle of checks you deposited at the bank.
Match all the checks you can identify to the sales invoice you created.
Assuming you successfully matched the credit card deposits, figure how much check volume you matched by seeing the change in “matched amount” on the invoice. The number should match the total for the checks row in the Sales by Payment Method export.
Tag cash deposits:
If your business uses a “cash on hand” balance sheet account, create a new cash transaction for $244.00 and use the same splits as above.
If your business just tags cash when it’s deposited in the bank, you should determine when the cash was deposited and tag that entry with the predetermined splits.
If the deposit was for a larger amount than the cash sales (e.g. there was cash from other sources) you should make a line item for $244.00 and apply the tags to that, similar to how checks were handled.
Frequently Asked Questions
How do I account for Taxes?
Tax collections are managed directly in your point of sale system, so you’ll need to get in touch with Square or your accountant to determine if you need to be collecting taxes.
How do I account for tips?
If you want to track tips, you will need to create an income category tag for them and then include that total when you are calculating your category tag splits. This can be a little more complicated, so reach out to Ambrook Support if you have questions.
How do I account for payments with the method “other”?
This varies on a case by case basis, but these are often payments you marked as completed over Venmo or Cash app. If this is the case, you need to transfer those funds to your bank account, and then tag that deposit in your ledger.