- 495 Grand Blvd #206, Miramar Beach, FL 32550
- Support 850-545-4923
How to Super Charge Financial Reporting and Analysis with ODBC
Sage 300 Timberline and A-Systems' JobView
Sage 300 or Timberline as it is known by many, has been around well over 3 decades. It is a solid choice for many in the construction industry, especially those large businesses that need a more sophisticated job costing and accounting system than what a basic application, like QuickBooks, will provide. A-Systems’ JobView is another accounting application used by many in the construction industry. It also provides more features for reporting, tracking and classifications than the basic accounting system.
Data Stored is Not the Same as Data Reported
With these and other accounting applications, the real benefit of the data created in, and stored by them is only as good as the reporting and analysis of the data. If the data remains only stored in the system and not reported or analyzed, it is of little benefit.
To super charge your financial reporting and analysis, ODBC can be the tool to power the boost. Simply put, using customized Excel files to report financial data that is synced to your accounting system can be done with ODBC. The customized Excel file access the stored data directly and is updated by using the ‘refresh’ command in Excel. This refresh updates the Excel spreadsheet with data that is in the accounting system at that time. Imagine current, relevant financial data in Excel at the push of a button in the hands of the non-accountant decision makers. Owners and project managers will be able to analyze the reported data in the Excel files at any time. No knowledge of the accounting system is needed, just an understanding of the data. A simple way to use financial data to manage the construction business. You don’t need to know how to run Timberline or JobView, you only need to know how to open Excel, refresh the data and then analyze the data that is reported.
Data Can Be Accessed by the Non-Accountant Executive
Normally, the business owner or project manager is not the expert at operating Timberline, JobView or whatever accounting application the company uses, but they are usually above average users of Excel. And, many times the reports that can be customized in Excel using ODBC are ‘better’ and more sophisticated than what can be created from any report generator used with the accounting application.
Custom Reports for the Construction Company
Construction companies normally like to know real time financial information like cash flow by job. Believe it or not, this is not a standard report for construction accounting applications. Using ODBC, an Excel file can be formatted to report basic financial metrics on a job basis, including:
- Job to date billed
- Job to date cash collections
- Current accounts receivable
- Retainage receivable
- Job to date costs
- Job to date cash paid out
- Current accounts payable
- Retainage payable
- Net current accounts receivable and current accounts payable
- Job cash flow (received less paid)
- Job to date billings >(<) job to date costs
Variations of the above data can be used also. That is the power of ODBC and Excel. If the data is in the accounting system, it can likely be accessed with ODBC and then shown in Excel.
One popular and time savings application of ODBC and Excel is the percentage of complete calculation that is done on a routine basis for internal reporting and reporting to bonding companies. Once set up, the bonding report or percentage of complete report (also known as WIP report) can be updated with all calculations done at the press of the refresh button. That includes current year revenue, earnings in excess of billing, billings in excess of earnings, etc. Plus, having this report in Excel allows for custom calculations, notes, sorting, etc. that can be done for internal and external reporting purposes.
One final example of a simple, yet highly useful application of ODBC and Excel is a liquidity report. The simple version of this report is going to display:
Cash
+Current accounts receivable
– Current accounts payable
= Liquidity
This liquidity report provides a better status of cash and cash flow than looking at the on-line banking portal. The banking portal is only going to display what has cleared the bank. Not what is about to be processed.
A Bit More about ODBC
A great explanation of ODBC can be found in the article here. A pictorial view of how ODBC works:
You’ve heard the line that information is power? Well, it can be power if the information is made available to the decision makers in a timely manner. Using the ODBC tool to supercharge your accounting data is a proven, accepted way to achieve this goal. Talk with your CFO about how to super charge your accounting data. Or, for more information or assistance, contact us at charles@capserv360.com.