Spreadsheets: Where Fixed Assets Go to Die

Posted by EQ Tech on Fri, Jun 10, 2016

Spreadsheets for Fixed Asset Tracking

You’ve invested thousands into a powerful ERP system, state-of-the-art hardware and talented staff. Yet your accounting department uses Microsoft Excel to track your company’s fixed assets. Sound familiar?

In this article, we discuss the surprisingly common practice of organizations (often otherwise technologically savvy) using spreadsheets to maintain data on fixed assets.

Any organization with accounting needs that are complex enough to justify purchase of an ERP system like Sage 300 is likely to make capital expenditures that will be tracked on the balance sheet as fixed assets.

Fixed Asset Complexity

If a business only has a handful of such assets, gathering basic details and calculating annual depreciation can probably be managed well enough through an Excel spreadsheet.

On the opposite end of the spectrum, some organizations with multiple locations and thousands of assets may struggle to keep pace with new assets coming in and out of service, let alone managing the data and depreciation for existing assets.

Although most accountants would likely concede the importance of accurate fixed asset reporting, it is common that their organizations neglect to allocate sufficient resources to managing this critical data. In the absence of a better solution, using a spreadsheet to list and track assets and calculate depreciation may seem like a reasonable strategy.

Why Spreadsheets Don’t Work

Following are some reasons why the spreadsheet method is inefficient at best, and at worst, a disaster waiting to happen:

Lack of Security - A spreadsheet does not keep sensitive financial data secure and may render your organization non-compliant with various regulatory requirements.

If the spreadsheet is not password protected, anyone with access to where the document is saved will be able to view information about the quantity, value and location of the company’s assets. The spreadsheet could easily be copied and sent elsewhere without anyone knowing. Even if the spreadsheet is password protected, someone motivated enough to access the information could easily download software to decrypt the password.

Easy to Lose the Data - A spreadsheet can easily be misplaced, deleted or become corrupted.

If all of the company’s data is stored in a single file, losing access to that information could be disastrous. Trying to recompile the data would be incredibly time consuming and, depending on the number and age of the assets, may not even be possible.

No Audit Trail - A spreadsheet does not contain an audit trail.

Regulatory rules for certain organizations may require availability of an audit trail. The average spreadsheet will not be able to track all changes made, when they were made, and by whom. Whether someone is deliberately trying to manipulate data or simply trying to undo a change, an audit trail is vital in tracing back steps and identifying responsibility for updates to information.

Inaccurate Depreciation Formulas - Formulas entered to calculate depreciation amounts may be incorrect.

If you are using the spreadsheet to calculate depreciation, using an incorrect formula could create an avalanche of errors from the amounts entered in the accounting system, to financial statements and tax reporting. All it would take would be a minus sign instead of a plus sign or for the formula to become corrupted somewhere in the sheet to create this kind of self-perpetuating catastrophe. The time and work required to identify and fix these errors would be immense, especially if amended taxes had to be filed.

Data Entry Errors - Manual journal entries to record depreciation amounts in the GL increase the risk of errors due to typos or other forms of human error.

The more frequently information has to be entered, the more opportunities arise for mistakes. Even if depreciation is calculated correctly in the spreadsheet, the person responsible for journaling the amounts may make a mistake when posting to the accounting system. Similarly, when new assets are acquired, as the information is re-keyed from the accounting system into a spreadsheet there is a chance that it will be done incorrectly.

Multiple Depreciation Schedules - Additional effort is required to calculate multiple depreciation schedules using a spreadsheet.

Many organizations will need to calculate and maintain multiple depreciation schedules to satisfy tax and financial reporting requirements. This increases the risk of errors or inconsistent calculations if formulas are changed over time.

Tracking Asset Location - A spreadsheet may not prove helpful in tracking the physical location of assets

Organizations have an obvious interest in knowing the exact location of all assets at all times. Although certain types of assets (a building, land, large equipment) may be easy to locate, smaller items like computers or tools are vulnerable to loss or theft. Other than being able to record notes on an item’s location, a spreadsheet cannot effectively track the physical location of assets.

These are just a few of the major risks in relying on a spreadsheet to track fixed assets.

Ditching the Fixed Asset Spreadsheets

Fortunately, organizations using Sage 300 can benefit from the efficiency and reliability of using fixed asset software that is integrated with their ERP system.

Sage Fixed Assets (FAS) software, built for the sole purpose of recording fixed asset data and calculating deprecation, ensures that formulas and calculations are accurate, sensitive data is kept secure, and that there will be a robust record of all changes and updates to satisfy any audit or regulatory requirements.

Although Sage Fixed Assets can be run standalone (meaning it does not share data with an ERP system), the greatest benefit is realized through the built-in workflow integration with Sage 300. Data flows between Sage Fixed Assets and Sage 300 automatically reducing time spent on duplicate entry and the risk of human error.

Benefits of Integration

Following are examples of the many advantages that can be realized by using Sage Fixed Assets integrated with Sage 300.

  • Once assets are created in the Fixed Assets database, depreciation entries can be scheduled to calculate and post to the Sage 300 General Ledger automatically. No duplicate data entry required.
  • Sage Fixed Assets can maintain up to 7 different books for each asset allowing automatic calculation of multiple types of depreciation.
  • Sage Fixed Assets supports more than 50 depreciation methods including MACRS 150 percent and 200 percent (formulas and tables), ACRS, Straight-Line, etc.
  • Companies can setup user-defined fields to track custom data for assets that may be unique to their organization.

Different Versions for Different Needs

In addition to the standard depreciation functions, Report Writer as well as Tracking and Planning modules are available for organizations with more substantial fixed asset management needs. 

Additionally, Sage Fixed Assets is available in several editions. The graphic below reflects the different features available in each edition.

Versions of Sage Fixed Assets

If You MUST Use Spreadsheets, Here Are Some Tips

While a program that’s dedicated to Fixed Asset management will provide the most robust support for asset tracking and reporting, Equation Technologies understands that not every company is in a position to purchase or implement such software immediately.

If you do continue to use spreadsheets to track fixed assets, here are some tips to guard against errors and keep your data secure:

  • Password protect your spreadsheet and store it in a network location where access is limited to only those users who need to see the information.
  • When the spreadsheet is updated, save the changes to a new copy and include the date of the revision in the document title. Save old versions to provide a point of reference if it becomes necessary to revert to a prior version.
  • Keep a backup copy of the current spreadsheet and old versions to guard against data loss if the original becomes lost or corrupted.
  • Check formulas on a regular basis – both for accuracy in the sheet and accuracy in calculation method.
  • When creating Journal Entries in Sage 300, double check that the totals balance to your spreadsheet amounts to guard against accidental key-stroke errors. Ideally, have a second set of eyes review the entries.

If your organization is currently using a spreadsheet to maintain fixed asset data and you are concerned about data security or are interested in a more efficient way to manage these transactions, contact Equation Technologies to learn more about Fixed Asset tracking software and how it can save you time, money and headaches in the years to come.

Get in Touch

 

Topics: Sage 300 (ACCPAC), Fixed Assets

Submit a Comment

Need Help Now?

Request a Call