Do you trust your financial statements? Reconciliation tools for Online Retailers

Do you trust your financial statements? Does your cash account in Quickbooks match your bank statement? Would you like to reconcile costs with sales on an order/product levels and see how profitable your business really is?

Practically every company I work with desperately needs real time accurate financial data to make best pricing and marketing decisions and Quickbooks wasn`t simply designed to handle tens of thousands of transactions, follow a "reasonable proof of shipment" revenue recognition rule or accrue for cost of goods invoices due in 30-90 days. So, what`s the best way to do managerial accounting in the high velocity negative cash cycle online retail world?

Periodic audit is one way. This is the best solution if you are entering into a business deal and need an external verification of your numbers. Downsides are:

• A $10-15K fee for something that was obsolete before it was completed;
  
• High internal costs (e.g. having your accounting department paralyzed for 2-4 weeks);
• You have to do everything all over again every time you need accurate financials.

A massive export of transactional and financial data to Excel is a better option. However manual and time consuming, you reconcile tens of thousands of orders with appropriate costs, get to the real margins and can identify the products that either have too much margin built in or too little. Downside - maintenance becomes an issue. Once the person who built the table moves on to other projects - the system becomes less and less accurate and you lose the temporary advantage over competitors.

The very best solution is a database connected to your Order Management, Quickbooks and shippers` billing systems. There are multiple buzzwords for something that stores the data and produces reports: Data Warehouse, Business Intelligence, Business Warehouse etc. For simplicity I will call it by it`s primary function - a reconciliation system.

Benefits of reconciliation system are numerous:

• Verification of your company`s tax return numbers;
  
• Practically real time vendor/ product margin analysis;
  
• Easy way to compare accruals with actual expenses (COGS, Shipping, Marketing etc).

Having built such systems for two clients I highly recommend starting with a standalone solution, replicating your chart of accounts, fine-tuning allocation rules and check consistency of the data. Then test on a large amount of historical data (e.g. FY2008) and compare with your year-end results. Since we have replicated your chart of accounts - comparison is easy, just print P&L statements from QuickBooks and the reconciliation system and compare them account-by-account. I bet there will be a lot of interesting discoveries.

In addition to accurate historical statements such system gives you a real edge over the competitors. You become able to make pricing and investment decisions on the fly.

Let`s say you want to experiment with a free shipping - run it on a product category (or a store) for two weeks. Compare sales/ net margin with prior two weeks and see if free shipping is the right solution for this particular product set.

Or, throw in free gift-wrapping for one week and see if you should keep it for the rest of the holidays season.

Or, reduce your target margin from 30 % to 28 % and see what impact it has on your sales/ total net margin for the specific product line. Opportunities are endless.

I believe that systems should help people manage complex businesses through simple decisions such as Set target margin at X %, add free shipping or spend Y % of sales on PPC marketing. Therefore, every system I build addresses a specific need:

• Reconciliation system to get accurate financials and identify products with excessive/ inadequate margin requirement;
• Shipping script to calculate per product shipping costs using negotiated rates with the shippers;
• Pricing script to automate retail price calculation based on actual costs and target margins; and
  
• Product sourcing solution to enable retailers build "One Store A Day" and double their sales every three months.

If you have questions about one my solutions - please send me a note to max@optimalogica.com or call at 650 209 6570. My clients win - I win.

Have fun growing your business and happy holiday season.