Advanced Excel Reporting for Management Accountants

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 thats 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

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 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