Monthly close always overruns? Just like 90% of finance staff

As a response to last week's post on the automation of management accounts, I was approached by Adra Match, Data Matching and Reconciliation Software experts with an offer of a blog post about their research on the month-end process, which makes for some interesting reading.

Whether it is automation of the accounts preparation process (as per last week's post) or automation of reconciliations (as provided by Adra Match's software), from these results, it would appear that we need all of the help we can get!

Here's the post...

Monthly close always overruns? Just like 90% of finance staff

A guest post by Alan Bagnall of Adra Match

There is always a compromise between speed and accuracy, rarely can they co-exist. More speed means less quality – and the month end close is the epitome of this.

Pressure seems to be on for finance staff to complete the month end close process faster, according to a new Adra Match survey of European and US financial accounting professionals. The monthly close process was voiced as “a mad dash’ by a number of the finance professionals surveyed, a sentiment most, if not all financial accounting professionals can relate to.

That “mad dash’ also means that in over 70% of cases financial reports aren’t being filed for at least four days past the deadline. If manually processed accounts are open longer than they should be, your business might be in for trouble: The business becomes exposed to risk, it will certainly run into problems with your local regulator, and it has the risk of having material misstatements which is a heftier challenge today than before. It’s now illegal to rely on your auditor to spot errors!

One finance processional who took part in the survey voiced the significant pressure faced by finance staff :

"my objective is to achieve a faster, more reliable close, make it efficient".  

>>>Download the free financial close report infographic for an overview of the survey results  <<<

As well as a speedier turnaround of month end figures, participants also listed the following as being among the top priorities for the finance department:

• Increased reliability
• Reduced risk
• Segregation of duties 
• And a transparent audit trail

Of those points, reliability of the close process appears to be the main challenge for most US and European financial accounting professionals: A mere 28% of finance staff trust the reported numbers.

So most financial staff working directly with the raw data don’t trust their own reported numbers. These are horrible facts. Horrible, because the numbers reported in our balance sheet and profit & loss statements, are believed to be correct. While this survey proves exactly the opposite.

The report also reveals that one other key area for concern is risk in the account reconciliation process. One reason for this may be that a vast amount of finance staff with too many reconciliations to perform only use spreadsheets to reconcile their accounts. While spreadsheets when used correctly can work with you, often they do not track which user has made which changes, making the separation of duties difficult, if not impossible, to monitor.

Doesn’t it seem strange that while finance departments use technology to automate a lot of their processes, the use of technology in the account reconciliation process has hardly progressed since the 1980s? Why?

• Isn’t it time to bring the arduous account reconciliation challenge into the digital age too? With an automated “Balance Sheet Reconciliation Process” solution?

Don't forget, if you want to read the full survey results you can at the link below:

Adra Match financial benchmark survey results.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Are you responsible for producing Management Accounts or Monthly Reports?

I know that many of you, like me, are accountants and a large number of you, no doubt, are responsible for producing monthly management accounts. I'm sure that many of you that aren't accountants, as Excel users, are responsible for producing some kind of regular monthly, weekly or daily report.

This week's post is more of a plea, to take a moment out of your busy schedule to stop and have a think about how you go about this regular reporting.

How long do you spend on this each month/week/day?

How long do you think you should be spending?

Let me give you some food for thought.

Is the data for your reports in the same place each month?

Are the reports you're required to produce in the same format every month/week/day?

It is very rare that the answer to either of these questions is no. For example, if we are talking about management accounts, the data is held in the accounting system in exactly the same format as it was last month - and the management accounts are produced in the same format as last month, usually in an Excel template.

Given Excel's ability to manipulate data - and perform the same manipulations time and time again, why would the process of producing these reports take more than a few seconds?

Simply create an Excel template that converts the raw data into the final report, and just paste or import the new raw data in each month!

Whichever way you go about it, I urge you to stop and think. Logically, it should only take a few seconds. Any longer means that you are manually doing things every month/week/day that Excel could be doing for you.

If you need any help, I have covered this in more detail previously.

My very first post on this blog (back in September 2008!) was on this subject and gives a brief overview of how you can go about this for a set of management accounts:

Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

and more recently (November 2013) I wrote a far more detailed explanation of the process in a publication published by the IT Faculty of the Institute of Chartered Accountants in England & Wales (ICAEW) entitled:

Automating management accounts production in Excel: a simple approach

(This is free to members of the IT Faculty or the ICAEW's Excel community or £25 to purchase for everyone else, from the ICAEW at the link above).

If you want, I can even set it up for you as this is a job that I have done for many clients over the years.

Whatever you do, I urge you not to just go back to a time-taking and unnecessary monthly/weekly/daily process.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Editing multiple formulae

I've got a simple tip this week that you have probably used elsewhere, but most Excel users I meet do not know that it can be used for this purpose.

This is a nice little trick for that situation when you need to make the same change to many formulae in different cells.

This can be a very time-consuming task, particularly if the spreadsheet has not been well-structured in the first place. If it has, at least all variables, etc. should have been referenced to cells rather than hard-coded in the formula. See my earlier post for some principles on good spreadsheet practice.

Sometimes though, you're stuck with making a change like this. The good news is that a simple tool that is available throughout Microsoft Office can be used.

This tool is the Replace facility.

Many of you will have used this to replace text in a Word document, or maybe you've used it in an Excel document, but did you know that it works exactly the same within formulae?

Yes, say you have a range of formulae that refer to cell A1 for a particular variable and you need them to now refer to cell B3. You can use Replace to make the change.

Simply highlight the range with the formulae that need changing, click the binoculars on the Home ribbon and choose Replace and fill the boxes in as below:

Click Replace All and Excel will replace the text A1 with B3 throughout all of the formulae.

Note of caution:

This is a simple text replace, so is not intelligent. For example:

$A$1 will not be changed
A113 will become B313

Having said that, with a little thought about the consequences beforehand, you can usually do the Replace in such a way that will solve your problem.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using the HYPERLINK function

Two events of note occurred over the weekend:

1. My local football team (Sunderland AFC) beat our bitter rivals (Newcastle United) 3-0 at their place for the second year in a row!

2. I responded to a blog comment asking how to make a URL in Excel that was the result of an IF statement a clickable hyperlink.

I will endeavour to make this post about the latter, but please forgive me if I find it difficult to avoid mentioning the former!

The HYPERLINK formula in Excel allows returns a clickable hyperlink as its result. It is used as follows:

=HYPERLINK(Link,[Friendly name])

Link can be a URL, a file path, or a reference to a cell or range in this or Excel or Office document and should be included in quotes (unless it's a reference to the text rather than the text itself).

[Friendly name] is an optional argument, that allows you to choose what text appears in the cell. If you don't enter this argument, the link text shows. Again, this should be in quotes (unless it's a reference to the text rather than the text itself).

Some examples:

=HYPERLINK("http://www.safc.com","Sunderland AFC") displays the text Sunderland AFC in the cell and links to the team website when you click on it.

=HYPERLINK("C:\Budgets\2014 Budget.xlsx","Open budget spreadsheet") displays the text Open budget spreadsheet and opens the 2014 Budget spreadsheet from the Budgets folder on the C drive when you click on it.

=HYPERLINK("[2014 Budget.xlsx]Assumptions!E46","Edit inflation") displays the text Edit inflation and goes to cell E46 on the Assumptions worskheet of 2014 Budget.xlsx. This format should be used for linking even within the same spreadsheet.

This function really comes into its own though when you populate these arguments with references to results as the links can then be dynamically generated from the contents of the spreadsheet.

For example:

=HYPERLINK(B5,A5) will display whatever text is held in cell A5 and link to the URL or path contained in cell B5

Or potentially more usefully:

=HYPERLINK("http://www.bbc.co.uk/sport/football/teams/"&A5,A5) which will display the football team name held in cell A5 and link to that team's news page on the BBC website. e.g. if A5 contains the text sunderland, the link will go to http://www.bbc.co.uk/sport/football/teams/sunderland whereas if A5 contains newcastle-united it will go to http://www.bbc.co.uk/sport/football/teams/newastle-united.

See my earlier post about manipulating text in Excel to explain use of the Ampersand (&) character.

You could also use VLOOKUP, IF statements or any other functions to populate the arguments of the HYPERLINK function and create dynamic links.

There, I hardly mentioned the score!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".