Management Accounting and Excel spreadsheets

Excel spreadsheets are the most common and indispensable tool used by accountants, enabling them to analyse, report and share financial information. Much of this can be accomplished using only a fraction of the wealth of functions and options within the Excel program.

Basic arithmetic, the SUM() function and some cell border formatting will let you produce useful models and perform some quite complex calculations. By expanding your knowledge with just a few other functions, Excel can become a much more versatile tool. Very occasionally Excel does not contain a function that you may expect, and it is necessary to combine other functions to perform tasks such as calculating weighted averages.

Accountants have a professional responsibility to present accurate data. The IF() and TEXT() functions are essential for incorporating error checking messages to demonstrate that the spreadsheet models have been reconciled and are performing properly. A spreadsheet presentation gives reports a degree of unearned credibility. This is frequently ill-deserved and research shows that a high proportion of large models contain critical errors. Your spreadsheets must be well designed and thoroughly checked. Keep in mind the importance of any decisions which might be based upon your model.

If you wish to be selective with your data and only perform calculations on items that meet certain criteria, both Database and Array functions are useful, but SUMPRODUCT() is the most versatile and wonderful alternative. It is able to extract all sorts of values from a table of data and can be used as an alternative to a great many functions.

Graphs are a great way to present information, but keep them simple. Don't get carried away with 3D formats which can make it difficult to read important axis values. If you update a chart on a regular basis, you are very likely to occasionally forget to manually alter any titles which contain period information. It is best to link chart titles to cell contents which can be automatically updated.

A popular, modern Financial Management technique is that of balanced scorecards which often incorporate traffic light indicators. Conditional formatting for cell ranges is not only useful for highlighting exceptional or incorrect values but can be used to automatically colour cells to show good or bad performance.

In order to report on tables of data with separate columns or rows for each accounting period, the OFFSET() function lets you select data for any single or cumulative period.

There are numerous financial functions for performing interest and investment calculations. These can greatly simplify the long formulae previously required for things such as loan repayments - but always beware. The built in functions may perform differently to your own (textbook) formulas. They may invert the sign and show negative results where you expect positive; they may use a base period of p0 where you assume it is p1. Once you have tested the financial functions using a variety of situations and confirmed the results, they make tasks such as calculating the Net Present Value much easier.

Finally, it is always helpful to make your models friendly to other users. Protecting or restricting the values that can be placed in cells will prevent unforeseen errors. Drop down boxes are a splendid way of getting values from users and hyperlinks can guide them around your models. By incorporating dates and filenames in default headers and footers, anyone can trace the source of your masterpiece.