Supplementing Your Accounting System

At a meeting last week, I was asked what software we recommend for accounting.  The owners runs a small business, fewer than 10 employees and sales in about a dozen states.  They are currently using QuickBooks but it doesn’t track everything they want easily, especially their inventory which is often stored in multiple locations.

I suggested keeping QB but supplementing it.  “With Excel?” one of the owners asked.  No, I replied, with Access.

I am a big fan of doing work, especially accounting work, in database systems.  This isn’t to say that spreadsheet’s don’t have a place, they do, but in a lot of cases, a well structured database enables a company to run complementary systems with minimal investment or complexity.

For example, we use Access to manage the complex owner payments for a condominium project we are managing.  The system automatically generated the assessments to each owner and we can receive payment and split the payment up much easier than in Excel.  Plus, we created a series of sub-tables which allow us to set up and create daily ACH files to upload to the processing system.  In the 3 months we have been using the Access system, we have cut our time to record and reconcile deposits about 90%.  And we know so much more about who, when and how owners pay their assessments than you could ever get out of Excel or even QB.

We set up the deposit form with a series of check boxes which allow us to manage payments to the special assessments – and allocate interest to the payments – as well as flagging us to record the deposit in QB.  Every week, I can run a specific query which searches the deposits table for unrecorded deposits and create a matrix table showing me how much to record as a deposit in QB to each owner receivable GL account as well as the amounts which were deposited to the various bank accounts.

We can track significant detail for each owner, including multiple emails and phone numbers as well as setting which method the owner prefers their information.  And, because we know how important it is to know when one owner’s responsibility ends and another’s begins, we created a specific process to track unit sales and split the transaction up to help both the buyer and seller know when and how much they owe at time of closing.

And the best part is, when we need something kicked out to Excel, we can create a routine which gathers the information in a query and automatically creates an Excel workbook.  Doug used this feature to help with the budgeting process to ensure that the allocation percentages were accurate without having to rekey data for each of the various scenarios.  And, once the budget was approved, we could reimport the Excel file to create the new year’s assessment charges for the Owners statements.

Excel is great.  We use it extensively, especially for financial statements and analysis.  But when you need to control data entry and you want to ensure only approved data are used, I strongly suggest you consider Access, or any other database application you like, as it is superior than Excel for multi-user and data management.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s