How to tell when you’ve outgrown that spreadsheet

Lady Looking Frustrated with Her Hands Over Her Face

For years, you’ve lovingly built and curated your fixed asset depreciation spreadsheet. Complete with elegant formulas, inspired macros and beautiful formatting - it’s a wonder to behold.

But, with the growth of your business and the constant introduction of new tax rulings, your brainchild is starting to show some signs of strain. Have you outgrown your spreadsheet?

As our client base has expanded, we have observed that numerous businesses and accounting firms depend on spreadsheets for handling fixed asset depreciation. Although spreadsheets such as Excel possess significant capabilities, they do possess certain limitations. Here are a couple that we have identified.

1. Dimension Limitations

Spreadsheets are very flexible tools, but they are effectively limited to operating in three dimensions (four at a stretch), which are rows, columns, tabs (and more sheets). When you consider the dimensions that actually apply to fixed asset depreciation calculations, you can start to see the limits:

  1. Asset Groups and Assets -> Rows
  2. Periods -> Columns
  3. Financial Years -> New Columns, Tabs or New Sheets?
  4. Tax vs Accounting -> More Columns, Tabs or New Sheets??
  5. Pools -> Separate Tab or Sheet??
  6. Cost Centres??
  7. Multiple Entities??
  8. Components??
  9. Disposals, Valuations and Impairments??

2. Error Detection

Accurate spreadsheets rely on meticulous application of formulas across every cell of the sheet.  It can be incredibly difficult to detect where formulas have failed to fill across or down. 

We often see spreadsheets with assets depreciated below $0 WDV (not hugely popular with the Tax Office!) and we’ve also seen spreadsheets with assets worth hundreds of thousands of dollars that hadn’t been depreciated in years.

How confident are you that every formula in every cell of your spreadsheet is correct?  And, if the dimension constraints above have pushed you towards multiple tabs and sheets, how confident are you that they are consistent across every tab in every worksheet?

AssetAccountant – saving you from spreadsheets since 2019

3. Journaling and Reconciliation

If you rely on your outgrown spreadsheet as your source of truth for depreciation (and perhaps also for acquisitions and disposals), journals are likely going to be a manual task. 

Converting your spreadsheet calcs to an easily imported spreadsheet is difficult, so it’s likely that you’ll enter depreciation entries line by line. This increases the risk of rekeying errors while consuming more of your valuable time. 

Reconciling purchases, disposals and carrying values with your GL is another challenge altogether.  

4. Pooled Assets

Pooling of assets requires special consideration.  Whether you are eligible for the Small Business Pool, the Low Value Pool, Project Pools or whatever pool may be applicable to your circumstances, you now face the challenge of reconciling assets that are depreciated individually vs assets that are depreciated as part of a pool.

More tabs, more sheets.  And, as pool rules change from year to year, it’s incumbent on you to make sure that you and your spreadsheets are across all pool rulings as time goes on.  Edge cases like balancing adjustments for pools that have a sub-zero balance due to a disposal, and pool write offs for pools that dip below the write off threshold in a particular year all need to be detected and implemented manually.

5. Tax Time!

The greatest joy of all.  Depending on your approach, you may encounter:

a. Tax = Accounting

At the risk of offending some readers, we generally regard this as a bad approach as tax depreciation is typically much more aggressive than accounting depreciation and can destroy balance sheets.  We’ve written an article on this which you can find here.

However, if you’ve chosen this approach, your spreadsheet complexity is halved.  The only challenge from here is generating well-formatted reports to accompany your tax return.

b. Tax <> Accounting

While we would suggest that this is the preferred approach to depreciation calculations, if you’re using spreadsheets you now face the additional challenge of keeping a single source of truth for assets, while applying two diverging treatments to their depreciation. 

This is where spreadsheets really become undone.  Depreciation treatments ideally diverge for tax and accounting, but how do you consistently manage common properties of assets, such as their name, code, location, purchase date and cost, first use date, disposal date and proceeds?

Do you manage this by maintaining manual copies of all assets across multiple sheets or tabs?  Or do you write macros to populate common asset characteristics into even more columns of your existing spreadsheet?

Sharing asset characteristics across multiple sheets or tabs while applying differing depreciation rules across these sheets and/or tabs means you have to remember to update each sheet or tab every time an asset changes.

Why our clients love AssetAccountant

6. Politics Time!

The best thing about politics (in our, possibly sarcastic, opinion) is the impact that political decisions can have on tax legislation.  Tax rules generally change year to year but, depending on the circumstances, can change mid year and mid period.  COVID is a prime example of the sort of mid-period incident that can prompt significant changes that accountants need to absorb.  Does everyone remember March 12, 2020?

From a spreadsheeting perspective, this requires that you may need to absorb mid-year rulings changes and rewrite all of your formulas (for eligible assets) midway through a period.  And, this same history tells us that these changes don’t just affect individual assets but they may also affect pool treatment and pool eligibility.

7. You're an Accountant

Here comes another multiplier!  Now, not only do you need to ensure the integrity of a single spreadsheet, but you need to ensure that your spreadsheet’s rules are reliably applied across your entire client base.  And, as different rules can be applicable to different clients, there are a few more combinations and permutations to stay on top of.

8. Sharing and Auditing

If your spreadsheet is managed by more than one person, if you’re considering delegating fixed asset management, if you’re managing a staff transition or just trying to cover a holiday, spreadsheets have very limited ‘blaming’ functionality. 

It’s very hard to detect what changes have been made to the sheet and by whom.  Transaction reversals are hard to track which can make future journals hard to calculate.

9. Macros

A wise friend once said: “As soon as you write your first macro, you’ve outgrown your spreadsheet” 

Macros are incredibly powerful but if you’re trying to make a spreadsheet behave like a database or a software program, you’re starting to push up pretty hard against its limits.  And, of course, macros need to be maintained and consistently applied across your spreadsheet (or sheets).

10. A final, cautionary note: Overdepreciation

While we’re not completely confident that ‘Overdepreciation’ is a word, it is a thing.  One of the most common issues we encounter when importing existing, spreadsheet-based data is overdepreciation of assets or, in other words, depreciating assets beyond zero.  You can ‘formula’ your way around this problem in an outgrown spreadsheet, of course.  Over to you!

Have you outgrown your spreadsheet?

Of course, we’d be remiss if we didn’t suggest that there was a way to avoid all of this pain.  If you’d prefer all of the above to be automated and spend your time on more useful pursuits, feel free to contact us (Ask Us Anything button below).

YOU MAY ALSO ENJOY THESE ARTICLES: