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