Advanced Excel Reporting for Management Accountants

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