Spreadsheets for Making Tax Digital – The universal language of accounting

Spreadsheets
istock_Rawpixel Ltd
David Lyford-Smith
Columnist
Share this content

ICAEW’s David Lyford-Smith responds to the news that spreadsheets’ are being parachuted back into HMRC’s Making Tax Digital plans.

This last month has been dominated by news about HMRC’s Making Tax Digital (MTD) for me and many of my ICAEW colleagues. 

The consultation responses were published on the last day of January and the fallout from that has not shown any sign of slowing down. This isn’t the time or the place to rehash the stories from MTD, but I did want to use this blog to highlight one particular titbit and its place in all of this – and that’s the admission of spreadsheets.

According to the consultation papers, one recurrent theme in responses (and I’m including myself here) is that the humble but ubiquitous spreadsheet should be allowed as a way of making the new ‘quarterly updates’ that HMRC will require from April next year. And they have apparently heard that theme, as the consultation responses have this to say:

“Businesses will be able to continue to use spreadsheets for record keeping, but they must ensure that their spreadsheet meets the necessary requirements of Making Tax Digital for Businesses. This is likely to involve combining the spreadsheet with software.”

In practice this likely means that spreadsheet records will have to pass through some sort of tool to format them for the reporting APIs that MTD will use, but still it is welcome clarification from HMRC that the records themselves can remain in spreadsheets where that’s desirable.  Certainly it seems that businesses of all sizes keep some or all of their records in one spreadsheet or another.

The spreadsheet is the often-unspoken layer of grease that keeps large parts of the business machine moving, helping users of all levels of computing experience to organise their thoughts and keep track of their data. I wonder if Dan Bricklin, who recently gave this fascinating TED talk, ever envisaged this when he invented the electronic spreadsheet with the first version of VisiCalc.

Tip of the Week highlight – Tables are great

I’m a huge evangelist for the Excel tables feature – I think I make more use of it than any other Excel expert I know – so I think talking about the feature and why I find it so useful is well worth devoting a little time to. 

This section shows a few highlights of what I covered in detail in the ICAEW Excel Tip of the Week in Tips #163 and #164.

You can apply the special table formatting to a set of data (with nice column headings) with a quick Ctrl + T, or with ‘Format as Table’ from the Home menu. This will add filters, make some nice formatting, and also add some useful functionality to your data. For instance, you can now add new rows or columns just by typing below or to the right of the existing ones respectively:

David Lyford Smith

What’s more, you can also name the table, and then use the clever structured formula language to write name-driven functions:

  • Use the table name to refer to all the data
  • Use TableName[ColumnName] to refer to a column by name (or just ColumnName if the formula is being written inside the table itself)
  • =TableName[[#Totals],[ColumnName]] - Total of the named column (where a total row has been added)
  • =TableName[[#Headers],[ColumnName]] - Header cell of the named column
  • =TableName[[#All],[ColumnName]] - Header, total, and data for the named column
  • =TableName[[ColumnName1]:[ColumnName2]] - All data between the two named columns, inclusive
  • =Tablename[[#Data],[#Totals],[ColumnName]] - The data and total row in the specified column
  • =TableName[@ColumnName] – The value from the specified name and on the same row as the function
  • =SUM(INDIRECT("TableName["&A1&"]")) – A dynamic SUM that can switch to sum up whichever column is named in cell A1

There’s plenty of power and flexibility in tables, and they’re well worth exploring. Just remember that the functionality is incompatible with shared workbooks.

Principle of the Month

This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #2:

Adopt a standard for your organisation and stick to it.

This is one of the fundamental ideas that drives the Twenty Principles. Good standardisation is no use if it isn’t considering in the context of your organisation and what makes sense for what you’re trying to achieve with spreadsheets. It’s equally no good if each person comes to their own conclusions on layout, formatting, formula types, and so on, and works in a silo separate from their colleagues.

There isn’t one correct way of doing most things in Excel – it depends on the organisational context and who is using a workbook – but many of the important decisions are better made collectively and hewn to consistently. Taking the time to have the conversation about spreadsheet standards is what’s important.

 

David Lyford-Smith is a Technical Manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community.  The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more.  Find out more at this link.

About David Lyford-Smith

David Lyford Smith

David Lyford-Smith is the technical manager for ICAEW, where he works in the IT Faculty and manages and blogs for the Excel Community.

 

Replies

Please login or register to join the discussion.

avatar
By Hayk S
29th Apr 2017 17:58

This software can serve as a proof of how helpful can excel be in accounting and bookkeeping. Along with recording day-to-day transactions, this soft helps to carry out analysis and VAT accounting.
Download https://www.excel-accounting-budget-analysis.com

Thanks (0)