Advanced Excel Reporting for Management Accountants
Wiley Corporate F&A (Series Nr. 1)
1. Edition May 2014
448 Pages, Softcover
Wiley & Sons Ltd
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.
Acknowledgments?
Introduction
Chapter 1: Management Accounting and Excel
Assumptions
The Goal of Reporting
Why Use Excel?
The Goal of the Book
Macro Policy
Chapter 2: Building Reporting Models
Needs Analysis
Scope Definition
Design
Construction
Testing
Operation
Maintaining
Time, Effort and Cost
Practical Considerations
Chapter 3: Building Tips
Display Tips
Keyboard Shortcuts
Mouse Shortcuts
Keyboard and Mouse Shortcuts
General Tips
Chapter 4: Design and Structuring
Structure = Flexibility
Table-Based Systems
Modular Sheet Design
Modular Design
Reporting Structures
Table-Based Solutions
Spreadsheet Best Practices
Chapter 5: Setting the Foundation
Terminology
Data Rules
Data Structures
Format as Table
Data Cleansing Techniques
External Data
Chapter 6: Pivot Tables (DIY Reporting)
Creating a Pivot Table
PowerPivot
Chapter 7: Tools of the Trade--Summing Functions
Range Names
Using Cells and Ranges in Formula
The Humble SUM Function
Advanced SUM
Subtotalling
SUBTOTAL Function
AGGREGATE Function
Function Wizard
Conditional Summing
SUMIF Function
SUMIF Uses
Helper Cells
SUMIFS Function
SUMPRODUCT Function
Chapter 8: Accessories--Other Reporting Functions and Features
Helper Cells
Logic Functions
IF Function
AND and OR Functions
Lookup Functions
VLOOKUP Function
HLOOKUP Function
Alternative to VLOOKUP
INDEX-MATCH Functions
MATCH Function
INDEX Function
Error Handling Functions
IFERROR Function
Handling Specific Errors
Text Functions
TEXT Function
Flexible Splitting
Date Functions
The DATE Function
Other Useful Functions
Array Formulas
Chapter 9: Range Names
Advantages
Disadvantages
Creating a Range Name
Naming Rules
Using Range Names
The Name Manager
Creating Names Automatically
Name Intersections
Dynamic Range Names
Using Structure in Range Names
INDIRECT and Range Names
Listing Range Names
Chapter 10: Maintenance Issues
Maintenance Instructions
Advantages of Using Tables
Common Issues
Rolling the Year
Simplifying the Interface--Using Controls
Chapter 11: Choosing the Right Paint
Colour Blindness
The Format Painter
Less is More
Format Cells Dialog
Report Formatting
Conditional Formatting
Printing Issues
Chapter 12 Picture Perfect: Charting Techniques
Chart vs. Graph
Chart Basics
Charts for Reports
Automating Charts
Chapter 13 Quality Control--Report Validation
Identifying Errors
Reasonableness Checks
Chapter 14: Case Study One--Month and Year to Date Reporting
Scenario
Data Requirements
Processes
Structure
Designing
Reports
Creation Process
Chapter 15: Case Study Two--12 Month Reporting
Scenario
Data Requirements
Processes
Structure
Creation Process
Appendix
About the Author(s)
About the Companion Website
An Australian CPA and certified Microsoft Office Specialist Expert, he has written for the CPA Australia monthly magazine, INTHEBLACK, since 2002. He has written over 100 of his regular "Excel Yourself" articles and eight feature articles for the magazine. He has freely answered Excel questions from Australian CPA's from many different countries for over nine years.
He has presented at numerous CPA Australia events in Australia, from one hour sessions to half-day Master Classes. He writes and presents his own XL@Lunch Excel webinars to people from around the globe.
