Financial Visibility within RetailOps Reporting Tool
We have had a few conversations with different members of the community over the last week or two regarding this topic, so I thought it might be beneficial to have a formal conversation with recommendations for using the RetailOps reporting tool to make journal entries to your P&L and balance sheet.
If that is something users would find value in, please let me know and our team can compile the base reports to use.
-
We have just been working on improving this today. We are using a report based off the items fulfilled table to give us our sales revenue (inc shipping), sales tax and Cost of goods sold to the P&L and are using a report based on the payment transactions table to allow us to post the expected funds to be received (split by payment channel) to the Balance sheet.
Our difficulty is precisely validating the two against each other because of the timing discrepancies between when we take receipt of funds ( on placement of order some channels and on despatch of goods in others) and when we despatch the goods and they reach the items fulfilled report.
Any other examples of how other people have worked around this problem would be very welcome!
-
There are nuances here depending on whether you are cash basis or accrual basis (primarily based on whether you hold inventory or are strictly JIT/DS), so consult with your CFO/CPA to make sure you have the right plan in place for your business.
I would imagine most of RetailOps' customers are accrual basis, which means you recognize the revenue immediately when the product is fulfilled. That is why it is imperative to be able to report on the value of product that leaves your facilities (or 3PLs and dropship vendors) during a specific period of time. That detail goes straight to your P&L for the time frame you are reporting on to give you your revenue, COGS and therefore profitability.
The issue Alisdair is struggling with is how to reconcile the P&L activity to your balance sheet. The inventory side of the sales cycle happens automatically as you have one journal entry to move the value of product fulfilled from your Inventory Asset account on the balance sheet to your COGS expense account on the P&L.
The other side, which is the revenue/cash component is best handled (in our experience) via a holding account. Essentially this is an intermediary account that holds your receivable (or payable) until the funds actually arrive.
The journal entry for us looks like this:
Merchandise Sales $XXXXX.XX
AR Holding $XXXXX.XXThat entry is made each day and records your merchandise sales to the P&L for the value of product fulfilled daily, and records the receivable (as long as you fulfill more product than you return) that you expect to receive from your payment processors.
The next step can be handled one of two ways depending on the visability you want to have. Because of the immaturity of the reporting that was available in RetailOps we added a secondary step to actually capture the activity for each payment processor individually. This extra level of detail may or may not be needed, but we found it beneficial to ensure we were actually capturing funds through a given payment processor for the product we were fulfilling.
Based on the reports we get from each payment process that summarize daily activity, here is the entry we make:
AR Holding $XXXXX.XX
Authorize.net $XXXXX.XX
Paypal $XXXXX.XX
Amazon $XXXXX.XXAnd then finally, one more journal entry for the days actual deposit activity into our checking account from each payment processor:
Authorize.net $XXXXX.XX
Paypal $XXXXX.XX
Amazon $XXXXX.XX
Checking Account $XXXXX.XXDuring any given period, these balances should all be cleared, and if not, it's easy to find the reconciling entries to catch any issues with a specific payment processor.
Hopefully that helps. I'm sure I've skipped over some details, and our Controller Jill will probably come along to correct the errors I most certainly made, but the general concept should be concrete enough to follow.
-
Jason - thanks for that - we are indeed accounting on an accrual basis, and we have gone down the rough route your outlined (our CFO may well want to add clarity here later on however!) - Because of our requirement to account for tax, and to charge for 100% of the shipping fee ( where one exists) only on the first despatch against an order, our report has evolved into a more complex one now - image is below (and I have to thank Daniel here for his vital input).
The difficulty with accounting in this way is the lack of clarity between the two different sides, and if something is going wrong ( either in the reporting or in real life) you are reliant on spotting a trend in the holding account and then diving back into the data to trawl through line by line to find the issue.
Has anyone managed to find a more elegant solution to comparing the two different sides at a granular level to spot individual line errors - without having to go through every line of revenue and every line of sales and tally them against each other?
-
Hi Jason,
We are still trying to refine this process and get it to be within an acceptable level of accurancy. You mentioned above that you may be able to post the other reports you use. If you get a chance could you stick them up here so I can compare with what we are using and see if I can learn from your expertise?
Thanks for your help
Alisdair
-
We are still struggling to reconcile the payments received with the net revenue accurately. Largely this is due to a number of errors within our RO configuration (especially surrounding refunds) that is providing inaccurate data to report on. I have been promised this should be recitified this month, so once that is done, I hope to be able to report accurately and timing issues aside ( between payment and fulfilment) then we should be able to generate reliable and accurate reports.
I look forward to seeing your report configurations - Thank you very much for your help!
-
Just a quick update here - we are now getting very close ( currency issues aside!) - but the final barrier seems to be that none of the FBA orders (Imported via CA) ever reach the fulfilment table in reporting. I have asked RO for their help with this, hopefully they can change the configuration to copy the FBA order data into the fulfilment table to allow us to be able to report sales revenue and tax from a single source. Just a heads up any case anyone else is trying to follow this and finds the same issue!
Please sign in to leave a comment.
Comments
9 comments