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.

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