IT HAS BEEN MY EXPERIENCE that management accountants tend to be the power users of Excel within their organisations. They usually end up being the unofficial Excel help desk. For this reason, I assume the reader has a good working knowledge of Excel and its built-in features.
Position titles vary between organisations, and some of the titles I consider closely related to management accountants are business analysts and cost accountants.
Although many of the topics covered are advanced, I include some basic and intermediate topics when they help explain the more advanced topics. I have found over the years that even advanced Excel users can learn new tips and tricks that improve their Excel productivity.
Many users are self-taught and haven’t always learned the best ways to do things. I will share many useful Excel shortcuts in Chapter 3.
ASSUMPTIONS
The premise behind the book is that you have an accounting system but its reporting package does not provide the flexibility you need to create the reports you need. Excel can extract data from most accounting systems and databases. So you can use your existing source data to build your reports.
You may also need to bring together data from other sources and incorporate those values with the financial data. This is an area that has the most scope for providing value-adding reports.
Excel is ideally suited to combining data, financial and non-financial, from different data sources into a single reporting model.
Given that some data resides outside of databases, Excel can also provide the ability to incorporate other small databases that may be held in other spreadsheets.
Please note Excel is not a database. Excel can be used as the data repository for small statistical-type data that may not warrant a database system.
You may also develop your budgets and forecasts in Excel and these can be integrated into your reporting model.
If your database systems do not allow direct connection to Excel, then most database systems have the ability to create files that Excel can read. In general you should aim to have all your data in databases.
Australian Conventions
Examples in this book may include Australian tax terminology, such as GST (goods and services tax).
The Australian financial year is from July 1 to June 30, which spans two calendar years.
Australia has six states and two territories. These are often considered regions for reporting. Laws and holidays can vary amongst the states, and it may be necessary to report differently state by state.
Versions
The instructions and images all relate to Excel 2010 unless otherwise noted. Most instructions will also apply to Excel 2007 and 2013. Some of the new features in Excel 2013 will be noted but not explained in detail.
Many of the techniques can be applied to Excel 2003, but no instructions are included for Excel 2003.
Terminology
There are two terms used frequently in the following chapters that require definition.
- Parentheses. Parentheses is the correct term for the symbols used with Excel functions to enclose the function arguments (see the following term). On the Internet and in general conversation, these are commonly referred to as brackets. In this book, the term used is parentheses.
- Arguments. In this book, the parts of an Excel function are referred to as arguments. Many functions accept a single argument, such as the SUM function. Others require more arguments; for instance, the VLOOKUP function requires at least three arguments to return a result. Between the parentheses, arguments are separated by commas. Argument is the term Microsoft uses in its Excel help system.
Spelling
The book uses UK spelling, except when referring to Excel features. Excel uses U.S. spelling and all the Excel terms will be spelled as they appear in Excel.
THE GOAL OF REPORTING
Management accounting reports are generally created for performance review. Compliance reports that satisfy a company’s legal reporting requirements tend to be reasonably similar across industries. Performance reporting varies depending on the industry and the sector. Excel provides the flexibility to develop performance reports that meet your needs.
Performance Measurement
Measuring your daily, weekly, and monthly performance against the budget, a forecast, or the previous year is the main focus of management reporting. This variance analysis is an important part of the reporting process.
Businesses already have accounting system reporting structures in place. These reports tend to be created by the database system that contains the data and are often limited in their layout structures.
In production systems and other systems in which volumes are important, comparing performance with the total dollar amounts may not be an accurate measure, and per-unit calculations are often required.
Charts can identify relationships and trends and are an important part of the reporting process. Many database systems have limited charting capabilities.
Performance Improvement
Another type of reporting focuses on improving performance. This can involve benchmarking and comparing performance among branches or divisions.
This type of reporting can be ad hoc. Areas may be identified for review and reports created to measure the relevant metrics to assist that review.
The techniques discussed throughout this book can be applied to ad hoc reports. Pivot tables, discussed in Chapter 6, are especially suited to ad hoc reporting.
