Advanced Excel Reporting for Management Accountants

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.

• Preface xiiiAcknowledgments xvIntroduction 1Chapter 1 Management Accounting and Excel 3Assumptions 3The Goal of Reporting 5Why Use Excel? 5The Goal of This Book 6Monthly Management Reports 7Macro Policy 7Chapter 2 Building Reporting Models 9Needs Analysis 10Scope Definition 10Design 11Construction 11Testing 12Operation 12Maintenance 12Time, Effort, and Cost 12Practical Considerations 13Chapter 3 Building Tips 15Display Tips 16Keyboard Shortcuts 26Mouse Shortcuts 33Keyboard and Mouse Shortcuts 42General Tips 48Chapter 4 Design and Structure 57Structure = Flexibility 57Modular Sheet Design 58Standardised Report Layout 60Table-Based Systems 62Spreadsheet Best Practices 63Chapter 5 Setting the Foundation 67Terminology 68Data Rules 68Data Structures 69Format as Table 70Data Cleansing Techniques 74External Data 80Chapter 6 Pivot Tables (Do-It-Yourself Reporting) 85The Pros and Cons of a Pivot Table 85Creating a Pivot Table 88PowerPivot 108Chapter 7 Tools of the Trade: Summing Functions 111Range Names 112Using Cells and Ranges in Formulas 112The Humble SUM Function 116Advanced SUM and 3D Formulas 117Subtotaling 120The SUBTOTAL Function 121The AGGREGATE Function 125Function Wizard 127Conditional Summing 127The SUMIF Function 131SUMIF Uses 135Helper Cells 135The SUMIFS Function 136The SUMPRODUCT Function 138Chapter 8 Accessories: Other Reporting Functions and Features 153Helper Cells 153Logic Functions 155The IF Function 157The AND and OR Functions 161Lookup Functions 164The VLOOKUP Function 164The HLOOKUP Function 168An Alternative to VLOOKUP 170The INDEX and MATCH Functions 170The MATCH Function 170The INDEX Function 172The INDEX-MATCH Combination 174Error Handling Functions 175The IFERROR Function 175Handling Specific Errors 177Text-Based Functions 180The TEXT Function 181LEFT and RIGHT Functions 183The MID Function 184Flexible Text Manipulations 185The SEARCH Function 185The LEN Function 187Flexible Splitting 187The SUBSTITUTE Function 188Converting Text to Numbers 190Date Functions 190The DATE Function 191Other Useful Functions 192Array Formulas 201Chapter 9 Range Names 209Advantages 210Disadvantages 210Creating a Range Name 211Using Range Names 213Name Manager 216Naming a Range 218Creating Names Automatically 222Name Intersections 227Dynamic Range Names 228Using Structure in Range Names 233INDIRECT and Range Names 236Listing Range Names 237Chapter 10 Maintenance Issues 239Maintenance Instructions 239The Advantages of Using Tables 240Common Issues 241Rolling the Year 241Working with Days 242Simplifying the Interface by Using Controls 244Chapter 11 Choosing the Right Format 255Colour Blindness 255Format Painter 256Less Is More 256Fonts 257Clear and Start Again 257The Format Cells Dialog Box 257Styles 270Conditional Formatting 272Printing Issues 293Chapter 12 Picture Perfect: Charting Techniques 299Chart versus Graph 300Chart Basics 300Charts for Reports 302Automating Charts 302Mixing Chart Types 307Dual-Axis Charts 308Handling Missing Data 311Labeling Highs and Lows 313Trendlines and Moving Averages 315Plotting the Variance 316Dashboard Techniques 317Text in a Chart 331The Data Series Formula 332Before and After Charts 333Chapter 13 Quality Control: Report Validation 337Identifying Errors 337Validations 338Error Tracking 340Identifying New Codes 346Conditional Formatting 347Suggested Validation Structure 347Reasonableness Checks 349Chapter 14 Case Study One: Month and Year-to-Date Reporting 351Scenario 351Data Requirements 352Processes 352Structure 354Design 354Report Layout 355The Creation Process 355The Reports 363Chapter 15 Case Study Two: 12-Month Reporting 379Scenario 379Data Requirements 380Processes 381Structure 381Design 382The Creation Process 382The Reports 387Chapter 16 Final Thoughts 407Feedback 408Last Words 408About the Author 409About the Companion Website 411Index 413