Praise for Advanced Excel Reporting for Management Accountants "Neale Blackwood, a long-time spreadsheet user and trainer, shares his knowledge of Excel reporting in a manner that's both thorough and easy to follow. He starts with the basics, and gradually introduces key topics that utilize commonly accepted best practices. This book will make your Excel reporting more useful and less prone to errors." --John Walkenbach, Author of Excel 2013 Bible "This book is essential reading for anyone involved in Excel reporting. It provides great insights about not only the most relevant and useful…mehr
Praise for Advanced Excel Reporting for Management Accountants "Neale Blackwood, a long-time spreadsheet user and trainer, shares his knowledge of Excel reporting in a manner that's both thorough and easy to follow. He starts with the basics, and gradually introduces key topics that utilize commonly accepted best practices. This book will make your Excel reporting more useful and less prone to errors." --John Walkenbach, Author of Excel 2013 Bible "This book is essential reading for anyone involved in Excel reporting. It provides great insights about not only the most relevant and useful functions of Excel for reporting and management accounting, but also how to apply these in practical ways that will improve your thinking and make your reporting more efficient. Neale knows more about management accounting and Excel reporting than anyone I know, and this book allows you to benefit from his experience so you can avoid the pitfalls and dramatically improve your Excel reports." --Jeff Robson, CEO & Principal Business Analyst, Access Analytic "Neale has brilliantly applied his considerable experience and expertise in writing this book. With clear descriptions and straightforward analogies, he gets inside the mind of the reporting analyst. With useful guidance on Excel reporting best practices, the reader does not waste time on unnecessary features but instead is guided to invaluable time-saving techniques. By applying the simple yet effective methodology described in this comprehensive book, even an experienced management reporting analyst will improve the efficiency, integrity, and robustness of their reporting tools. Definitely a career-booster!" --Danielle Stein Fairhurst, Financial Modelling Specialist & Presenter, Plum Solutions "Neale is very well known for his practical Excel skills and knowledge. His new book, Advanced Excel Reporting for Management Accountants, is highly recommended and will be useful to both accounting students and professionals alike." --William Beattie, CPA, Business & Finance Analyst "This book is chock-full of well-explained good ideas. With hundreds of tips, even if you simply choose any three to consistently implement, your management reports (and all your spreadsheets in general) will be greatly improved. Apply more than three and your work will significantly reduce the likelihood of both qualitative and quantitative errors. Although the book uses the title 'Advanced, ' the name relates more to the outputs than the user techniques; the language is approachable, and the book builds through from improving Excel program usability to improving the Excel spreadsheets. A great go-to manual to keep and re-read time and again." --Eve Blackall, Smart Accounting - the Science of Business Success
Neale Blackwood has been using spreadsheets since the late 80's. Starting with Lotus and moving to Excel in the mid 90's. His roles have included Accountant, Project Accountant, Financial Controller and Management Accountant. He developed most of his advanced Excel skills as a Management Accountant. 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.
Inhaltsangabe
Preface
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