Recently we ran across a new, lesser known sales channel called Tophatter. This was an interesting one. The layout of the download breaks the sales down into individual line items. One for the sale itself, and another for each fee.
For accounting purposes, we needed to know the following:
- What was the Gross Sale Amount
- How Much was Shipping
- All other fees we’ll just call Tophatter Fees
Tophatter has several “Fee Types:”
- Success Fee
- Processing Fee
- Buyer Fee
I don’t really care about these fees other than, they add up to what Tophatter is charging me to sell products on their platform.
Seriously WTF is a “success fee” any way?
To get these sales into QuickBooks Online, I need it broken down as I described above. This means I need a conditional statement in Excel that will do it quickly. I could set up a lookup table, and look it up by the “Fee Type” but if tophatter adds in a “successful Processing Fee” on the next one, it will fail and I will need to add that to my lookup table.
I wanted a catch-all on this one, and that means an “If/Then” that checks for BuyerFee (which is actually the Gross Sale), the Shipping Fee, and then anything else goes to Tophatter Fees.
Of course I set these items up in QuickBooks Online, so that once my spreadsheet looks them up, I am ready to import.
This is how we get this done with both extreme efficiency, and extreme accuracy.
In fact, when we’ve had errors (other than a clipped settlement report – see last week’s email) it has been the result of some tool that we’ve used, instead of an import process like this one. With this process there are so many ways to verify the amounts.
Here’s how to write the nested conditional statement, broken down to make it easy.
The first test is whether or not the current line item is the gross sale (BuyerFee).
Let’s get a basic if/then written – just get the condition in there with a “1” if true, and a “2” if false:
We write that formula which is simply checking – Is this the buyer fee?
At this stage, if it is, then we’ll get a 1. Otherwise we’ll get a 2.
Copy this down the rows, and make sure it works.
If the result is 1, then we need to replace that 1 in the formula with a “vlookup” that looks up the product ID in our master product list, and brings back the product name that appears in QuickBooks Online.
The trick is to write the Vlookup formula separately. Forget the conditional statement.
Anywhere on the spreadsheet, write the vlookup referencing row 2, because that’s where you will eventually copy the formula exactly as you’ve written it.
The assumption here is that you have the master product list in your template (see previous lessons in this course).
The logic here is that it is much easier to write each part of the formula on it’s own and then copy and paste it, replacing the appropriate number in the destination formula.
=VLOOKUP(G2,’Product List – Master’!A:D,4,false)
Where “‘Product List – Master’!A:D” is the tab called “Product List Master” and the range in that tab is columns A-D. Notice there are no row numbers in the reference. That means we’re looking in the entire column. This way now matter how many rows you may add, and where, they will be included in the formula.
The “4” means the result will be pulled from the 4th column in the range, which in this case is column D because I chose to end the lookup range right where the result will be.
The product list master has the Product ID (or SKU) in column A.
So we’re looking up the product ID in column A of the Product List Master, and pulling in whatever is in Column D if there is a match).
Once the formula is working, we copy and paste everything but the “=” into the original formula, replacing the number “1.”
Notice Google Sheets gives me a preview of what the result will be – nice right?
Now we paste what we’ve just copied, replacing the “1” in our formula:
The number 2 is still there at the end of the formula. We’ve just pasted in the Vlookup exactly as we wrote it, replacing the number 1.
Hit Enter and the result should be the product.
Then copy it down, and every row that has “BuyerFee” should now show the product in this column. Every other row should show the number 2.
Now in place of # 2, we need to put another condition.
If the Fee Type is “Shipping” then we want “Shipping Fees” in there. That would be what we call the Shipping Fees product in QuickBooks Online.
If it is ANYTHING ELSE, then we just want “Tophatter Fees.”
“Tophatter Fees” will be a fee item in QuickBooks Online mapped to a “Selling Fees Account. Technically this is NOT COGS (Cost of Good Sold) but for reporting purposes, many of you will want to see it there. The reason is that by definition COGS happen BEFORE the sale is made. Everything after the sale is a selling expense.
So let’s write another conditional formula. Again we will do this anywhere else on the spreadsheet – not inside the formula we’re trying to build. It gets too confusing:
This one is easier to write. There are no cell references. Just a simple test – if the “Fee Type (D3)” is “ShippingFee” then put “Shipping Fees” in my result. Otherwise put “Tophatter Fees.”
The D3 reference has to be exact of course, otherwise it won’t find it.
Notice also I reference row 3, where the result is “ShippingFee.” This way I will get a True result, so I know for sure the formula is working.
Next we copy and paste this formula to replace the #2 in our destination formula:
Everything but the “=” sign.
Note: This has to be pasted into the formula in Row 3 since that is the one we referenced in this formula
Look all the way to the top right. Notice I have the #2 selected. That is where you want to be when you paste the formula. The number 2 has to be replaced by the formula. If you don’t have it highlighted like I do in the screenshot above it will not work.
I’ve highlighted the formula within the formula so you can see the portion that I just pasted.
When you hit enter you should get the right result “Shipping Fees.”
Next you copy the formula from row 3 back up to row 2 and then all the way down. If you’ve written it correctly it should look like this (I’ve bolded the shipping fees to make them stand out):
This will take a while to develop, but once it is developed, every other settlement you ever process for your sales channel will take minutes.
You save your spreadsheet out as a template, so all you need to do is copy your settlement in there, and as quickly as you can press CTRL + V your spreadsheet will do all of the heavy lifting for you.
Then with a powerful import tool like SaasAnt Excel Transactions, you can have your sales imported in minutes, and with 100% accuracy!