Advanced Excel Reporting for Management Accountants
34%
off

Advanced Excel Reporting for Management Accountants

4 (2 ratings by Goodreads)
By (author) 

Free delivery worldwide

Available. Dispatched from the UK in 3 business days
When will my order arrive?

Description

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.
show more

Product details

  • Paperback | 448 pages
  • 175.26 x 251.46 x 27.94mm | 771.1g
  • New York, United States
  • English
  • New
  • 1118657721
  • 9781118657720
  • 12,240

Back cover copy

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

Table of contents

Preface xiii


Acknowledgments xv


Introduction 1


Chapter 1. Management Accounting and Excel 3


Assumptions 3


The Goal of Reporting 5


Why Use Excel? 5


The Goal of This Book 6


Monthly Management Reports 7


Macro Policy 7


Chapter 2. Building Reporting Models 9


Needs Analysis 10


Scope Definition 10


Design 11


Construction 11


Testing 12


Operation 12


Maintenance 12


Time, Effort, and Cost 12


Practical Considerations 13


Chapter 3. Building Tips 15


Display Tips 16


Keyboard Shortcuts 26


Mouse Shortcuts 33


Keyboard and Mouse Shortcuts 42


General Tips 48


Chapter 4. Design and Structure 57


Structure = Flexibility 57


Modular Sheet Design 58


Standardised Report Layout 60


Table-Based Systems 62


Spreadsheet Best Practices 63


Chapter 5. Setting the Foundation 67


Terminology 68


Data Rules 68


Data Structures 69


Format as Table 70


Data Cleansing Techniques 74


External Data 80


Chapter 6. Pivot Tables (Do-It-Yourself Reporting) 85


The Pros and Cons of a Pivot Table 85


Creating a Pivot Table 88


PowerPivot 108


Chapter 7. Tools of the Trade: Summing Functions 111


Range Names 112


Using Cells and Ranges in Formulas 112


The Humble SUM Function 116


Advanced SUM and 3D Formulas 117


Subtotaling 120


The SUBTOTAL Function 121


The AGGREGATE Function 125


Function Wizard 127


Conditional Summing 127


The SUMIF Function 131


SUMIF Uses 135


Helper Cells 135


The SUMIFS Function 136


The SUMPRODUCT Function 138


Chapter 8. Accessories: Other Reporting Functions and Features 153


Helper Cells 153


Logic Functions 155


The IF Function 157


The AND and OR Functions 161


Lookup Functions 164


The VLOOKUP Function 164


The HLOOKUP Function 168


An Alternative to VLOOKUP 170


The INDEX and MATCH Functions 170


The MATCH Function 170


The INDEX Function 172


The INDEX-MATCH Combination 174


Error Handling Functions 175


The IFERROR Function 175


Handling Specifi c Errors 177


Text-Based Functions 180


The TEXT Function 181


LEFT and RIGHT Functions 183


The MID Function 184


Flexible Text Manipulations 185


The SEARCH Function 185


The LEN Function 187


Flexible Splitting 187


The SUBSTITUTE Function 188


Converting Text to Numbers 190


Date Functions 190


The DATE Function 191


Other Useful Functions 192


Array Formulas 201


Chapter 9. Range Names 209


Advantages 210


Disadvantages 210


Creating a Range Name 211


Using Range Names 213


Name Manager 216


Naming a Range 218


Creating Names Automatically 222


Name Intersections 227


Dynamic Range Names 228


Using Structure in Range Names 233


INDIRECT and Range Names 236


Listing Range Names 237


Chapter 10. Maintenance Issues 239


Maintenance Instructions 239


The Advantages of Using Tables 240


Common Issues 241


Rolling the Year 241


Working with Days 242


Simplifying the Interface by Using Controls 244


Chapter 11. Choosing the Right Format 255


Colour Blindness 255


Format Painter 256


Less Is More 256


Fonts 257


Clear and Start Again 257


The Format Cells Dialog Box 257


Styles 270


Conditional Formatting 272


Printing Issues 293


Chapter 12. Picture Perfect: Charting Techniques 299


Chart versus Graph 300


Chart Basics 300


Charts for Reports 302


Automating Charts 302


Mixing Chart Types 307


Dual-Axis Charts 308


Handling Missing Data 311


Labeling Highs and Lows 313


Trendlines and Moving Averages 315


Plotting the Variance 316


Dashboard Techniques 317


Text in a Chart 331


The Data Series Formula 332


Before and After Charts 333


Chapter 13. Quality Control: Report Validation 337


Identifying Errors 337


Validations 338


Error Tracking 340


Identifying New Codes 346


Conditional Formatting 347


Suggested Validation Structure 347


Reasonableness Checks 349


Chapter 14. Case Study One: Month and Year-to-Date Reporting 351


Scenario 351


Data Requirements 352


Processes 352


Structure 354


Design 354


Report Layout 355


The Creation Process 355


The Reports 363


Chapter 15. Case Study Two: 12-Month Reporting 379


Scenario 379


Data Requirements 380


Processes 381


Structure 381


Design 382


The Creation Process 382


The Reports 387


Chapter 16. Final Thoughts 407


Feedback 408


Last Words 408


About the Author 409


About the Companion Website 411


Index 413
show more

Rating details

2 ratings
4 out of 5 stars
5 0% (0)
4 100% (2)
3 0% (0)
2 0% (0)
1 0% (0)

Our customer reviews

The book is one of the best excel books I've read. The format is easy to read, understand, and follow. I love that the book offers case studies to look and work on, and walks you through each case and how to perform various functions and actions in Excel. I am extremely impressed and absolutely amazed how Neale wrote and structured this book. I would especially recommend this book for all those who work with Excel on a daily basis, either at work, school, or other places - this book will save a tremendous amount of time by making you more Excel-efficient. I am an accountant, and I just wish I had this book much earlier - would've saved hundreds of hours of Excel frustration and not to mention cut down on time consuming tasks such as sorting through a mountain of data. A true Excel bible for Accountants if there was one!show more
by TRISTAN TRAN
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X