The advanced tools accountants need to build automated, reliable, and scalable reports using Excel
Learn about the functions that work together to automate many of the processes involved in Management Reporting. See how to take advantage of the many new features of Excel 2007 and 2010. Find out how to build validation structures into your spreadsheet reports. Discover how to identify missing or new codes, either in the creation process or in the day-to-day running of the reports. Do it all with Advanced Excel Reporting for Management Accountants.
Explore the structures that simplify the report creation process and make the reports more maintainable
Learn techniques to "cleanse" data so that it is ready for use in Pivot Tables and formula-based reports
Find out the tips and tricks that can make the creation process quicker and easier
Discover all you need to know about Excel's summing functions and how versatile they can be
Written in a hands-on style that works towards the completion of two reporting case studies, Advanced Excel Reporting for Management Accountants explains and demonstrates techniques so that Management Accountants can learn how to automate many aspects of the reporting process.
Frequently asked questions
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Yes, you can access Advanced Excel Reporting for Management Accountants by Neale Blackwood in PDF and/or ePUB format, as well as other popular books in Business & Managerial Accounting. We have over one million books available in our catalogue for you to explore.
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.
WHY USE EXCEL?
Excel is the spreadsheet of choice for most accountants. Virtually all accountants use Excel, with various degrees of skill. Excel is the industry’s standard spreadsheet, and it is constantly being upgraded and updated to handle today’s changing information needs.
Most finance...
Table of contents
Cover
Series
Titlepage
Copyright
Dedication
Preface
Acknowledgments
Introduction
CHAPTER 1 Management Accounting and Excel
CHAPTER 2 Building Reporting Models
CHAPTER 3 Building Tips
CHAPTER 4 Design and Structure
CHAPTER 5 Setting the Foundation
CHAPTER 6 Pivot Tables (Do-It-Yourself Reporting)
CHAPTER 7 Tools of the Trade: Summing Functions
CHAPTER 8 Accessories: Other Reporting Functions and Features
CHAPTER 9 Range Names
CHAPTER 10 Maintenance Issues
CHAPTER 11 Choosing the Right Format
CHAPTER 12 Picture Perfect: Charting Techniques
CHAPTER 13 Quality Control: Report Validation
CHAPTER 14 Case Study One: Month and Year-to-Date Reporting
