Advanced Excel Reporting for Management Accountants -- Contents -- Preface -- Acknowledgments -- Introduction -- CHAPTER ONE Management Accounting and Excel -- ASSUMPTIONS -- Australian Conventions -- Versions -- Terminology -- Spelling -- THE GOAL OF REPORTING -- Performance Measurement -- Performance Improvement -- WHY USE EXCEL? -- THE GOAL OF THIS BOOK -- Easy to Operate -- Easy to Maintain -- Self-Validating -- Modular -- Structured -- Flexible -- MONTHLY MANAGEMENT REPORTS -- MACRO POLICY -- CHAPTER TWO Building Reporting Models -- NEEDS ANALYSIS -- SCOPE DEFINITION -- DESIGN -- CONSTRUCTION -- TESTING -- OPERATION -- MAINTENANCE -- TIME, EFFORT, AND COST -- Time -- Effort -- Cost -- PRACTICAL CONSIDERATIONS -- CHAPTER THREE Building Tips -- DISPLAY TIPS -- Wrapped Up with a Ribbon -- Converting from Excel 2003 to 2007 or 2010 -- The Quick Access Toolbar -- Other Screen Changes -- KEYBOARD SHORTCUTS -- The Control Key -- The Shift Key -- The Alt Key -- Useful Function Key Shortcuts -- Ctrl + Shift Shortcuts -- Shift + Alt Shortcuts -- MOUSE SHORTCUTS -- The Fill Handle -- Double-Clicking -- Right-Clicking -- Right-Dragging -- KEYBOARD AND MOUSE SHORTCUTS -- Ctrl + Mouse -- Shift + Mouse -- Alt + Mouse -- GENERAL TIPS -- Using Windows as They Are Meant to Be Used -- Data Entry Tips -- The Clipboard -- Navigation -- Hyperlinks -- CHAPTER FOUR Design and Structure -- STRUCTURE = FLEXIBILITY -- MODULAR SHEET DESIGN -- STANDARDISED REPORT LAYOUT -- Sheet Grouping -- The Pros and Cons of Identical Layouts -- TABLE-BASED SYSTEMS -- SPREADSHEET BEST PRACTICES -- General Practices to Follow -- General Practices to Avoid -- Reporting Practices to Follow -- CHAPTER FIVE Setting the Foundation -- TERMINOLOGY -- DATA RULES -- DATA STRUCTURES -- Normalised Data -- Monthly Data -- FORMAT AS TABLE -- Advantages of Format as Table.

Disadvantages of Format as Table -- Format as Table Example -- DATA CLEANSING TECHNIQUES -- Missing or Incomplete Data -- Extra Field Required -- Date Issues -- Spaces -- Trailing Minus Signs -- Structured Codes -- Text to Columns -- EXTERNAL DATA -- Access Database -- Dedicated Data Cleansing File -- CHAPTER SIX Pivot Tables (Do-It-Yourself Reporting) -- THE PROS AND CONS OF A PIVOT TABLE -- Single Data Source -- Refreshing a Pivot Table -- Formatting a Pivot Table -- Pivot Table Terminology -- Filtering -- Pivot Tables and Exceptions -- CREATING A PIVOT TABLE -- Internal Data -- Pivot Table Layout -- Pivot Table Headings -- Formatting a Pivot Table -- The PivotTable Tools-Options Tab -- Grouping -- The PivotTable Tools-Design Tab -- External Data -- Pivot Charts -- Other Display Options -- POWERPIVOT -- CHAPTER SEVEN Tools of the Trade: Summing Functions -- RANGE NAMES -- USING CELLS AND RANGES IN FORMULAS -- Relative References -- Fixed References -- Mixed References -- Summary of Reference Types -- THE HUMBLE SUM FUNCTION -- ADVANCED SUM AND 3D FORMULAS -- Placeholder Sheet Naming -- 3D Flexibility -- 3D Inflexibility -- SUBTOTALING -- THE SUBTOTAL FUNCTION -- Syntax -- Example -- Automated Subtotals -- THE AGGREGATE FUNCTION -- Syntax -- Comments on Option Usage -- FUNCTION WIZARD -- CONDITIONAL SUMMING -- THE SUMIF FUNCTION -- Syntax -- Examples -- Using Other Operators with SUMIF -- SUMIF USES -- HELPER CELLS -- THE SUMIFS FUNCTION -- Syntax -- Examples -- THE SUMPRODUCT FUNCTION -- Standard Use -- Undocumented Use -- Dates and SUMPRODUCT -- OR versus AND Calculations -- Array Syntax -- Using Other Functions with SUMPRODUCT -- The GETPIVOTDATA Function -- CHAPTER EIGHT Accessories: Other Reporting Functions and Features -- HELPER CELLS -- Advantages of Helper Cells -- Helper Cells Structure -- Centralising Logic -- LOGIC FUNCTIONS.

Logic Cell Calculation Example -- THE IF FUNCTION -- Syntax -- Examples -- IF Functions and Helper Cells -- Shorter IF Functions -- Nested IF Functions -- IF Function Complexity -- THE AND AND OR FUNCTIONS -- Syntax -- Examples -- Array Syntax -- Multiple IF Functions versus a Lookup Function -- LOOKUP FUNCTIONS -- THE VLOOKUP FUNCTION -- Syntax -- Exact Match Example -- Approximate Match Example -- Whole Columns as Table Range -- THE HLOOKUP FUNCTION -- Syntax -- Exact Match Example -- Approximate Match Example -- AN ALTERNATIVE TO VLOOKUP -- THE INDEX AND MATCH FUNCTIONS -- THE MATCH FUNCTION -- Syntax -- Examples -- VLOOKUP and MATCH -- THE INDEX FUNCTION -- Syntax -- Examples -- Value versus Reference -- THE INDEX-MATCH COMBINATION -- INDEX-MATCH Flexibility -- ERROR HANDLING FUNCTIONS -- THE IFERROR FUNCTION -- Syntax -- Examples -- Excel's Error Messages -- HANDLING SPECIFIC ERRORS -- Syntax -- Example -- The ISERR Function -- IS Functions -- Structural Errors -- Circular Reference Errors -- TEXT-BASED FUNCTIONS -- Joining Text -- Spaces -- Automating Headings -- THE TEXT FUNCTION -- Syntax -- Examples -- Splitting Text -- LEFT AND RIGHT FUNCTIONS -- Syntax -- Examples -- THE MID FUNCTION -- Syntax -- Examples -- FLEXIBLE TEXT MANIPULATIONS -- THE SEARCH FUNCTION -- Syntax -- Examples -- THE LEN FUNCTION -- FLEXIBLE SPLITTING -- The First Section -- The Second Section -- The Third Section -- THE SUBSTITUTE FUNCTION -- Syntax -- Examples -- Counting Specific Characters -- CONVERTING TEXT TO NUMBERS -- DATE FUNCTIONS -- Syntax -- Examples -- THE DATE FUNCTION -- Syntax -- The First and Last Days of the Month -- OTHER USEFUL FUNCTIONS -- The OFFSET Function -- The CHOOSE Function -- The COUNTIF Function -- The INDIRECT Function -- ARRAY FORMULAS -- Entering Array Formulas -- Two Types of Array Formulas -- Array Syntax.

More on the Single-Cell Array Formula -- More on the Multi-Cell Array Formula -- CHAPTER NINE Range Names -- ADVANTAGES -- DISADVANTAGES -- CREATING A RANGE NAME -- Naming a Cell -- Naming Rules -- Naming Recommendations -- USING RANGE NAMES -- Navigation -- Formulas -- NAME MANAGER -- NAMING A RANGE -- Row-Based Names -- Column-Based Names -- Two-Dimensional Names -- CREATING NAMES AUTOMATICALLY -- Syntax -- Example -- NAME INTERSECTIONS -- DYNAMIC RANGE NAMES -- Table-Based Dynamic Range Names -- Formula-Based Dynamic Range Names -- USING STRUCTURE IN RANGE NAMES -- INDIRECT AND RANGE NAMES -- LISTING RANGE NAMES -- CHAPTER TEN Maintenance Issues -- MAINTENANCE INSTRUCTIONS -- THE ADVANTAGES OF USING TABLES -- COMMON ISSUES -- ROLLING THE YEAR -- WORKING WITH DAYS -- Syntax -- Example -- SIMPLIFYING THE INTERFACE BY USING CONTROLS -- Example -- Cell Links -- The Linked-Cell Format -- Check Box -- Option Buttons -- Combo Box -- CHAPTER ELEVEN Choosing the Right Format -- COLOUR BLINDNESS -- FORMAT PAINTER -- LESS IS MORE -- FONTS -- CLEAR AND START AGAIN -- THE FORMAT CELLS DIALOG BOX -- Number Formats -- Alignment -- Borders -- STYLES -- Multiple Styles -- Styles and Borders -- CONDITIONAL FORMATTING -- Highlight Cells Rules -- Top/Bottom Rules -- Data Bars -- Color Scales -- Icon Sets -- Manage Rules -- Formula-Based Conditional Formats -- Conditional Formats and Validation Cells -- Conditional Formatting and Pivot Tables -- PRINTING ISSUES -- Set Print Area -- Use Print Preview -- Set Print Titles -- File Print Options -- CHAPTER TWELVE Picture Perfect: Charting Techniques -- CHART VERSUS GRAPH -- CHART BASICS -- Chart Objects -- Charts Made Easy -- CHARTS FOR REPORTS -- AUTOMATING CHARTS -- Formula-Based Chart Automation -- Avoiding Zeros -- MIXING CHART TYPES -- DUAL-AXIS CHARTS -- Example -- HANDLING MISSING DATA -- LABELING HIGHS AND LOWS.

TRENDLINES AND MOVING AVERAGES -- PLOTTING THE VARIANCE -- DASHBOARD TECHNIQUES -- Paste Picture Link -- Dynamic Pictures -- Linked Text Boxes -- Working with Graphics -- Conditional Formats -- Sparkline Charts -- Small Charts -- TEXT IN A CHART -- THE DATA SERIES FORMULA -- Syntax -- Example -- BEFORE AND AFTER CHARTS -- CHAPTER THIRTEEN Quality Control: Report Validation -- IDENTIFYING ERRORS -- TRUE and FALSE -- Centralised Validations -- VALIDATIONS -- Rounding -- Tolerances -- Data Validations -- ERROR TRACKING -- Debugging Techniques -- IDENTIFYING NEW CODES -- CONDITIONAL FORMATTING -- SUGGESTED VALIDATION STRUCTURE -- REASONABLENESS CHECKS -- CHAPTER FOURTEEN Case Study One: Month and Year-to-Date Reporting -- SCENARIO -- DATA REQUIREMENTS -- PROCESSES -- STRUCTURE -- DESIGN -- REPORT LAYOUT -- THE CREATION PROCESS -- The Control Sheet -- Structured Range Names -- Actual Data -- THE REPORTS -- The State Report -- The Department Report -- The Total Report -- The Dashboard Report -- CHAPTER FIFTEEN Case Study Two: 12-Month Reporting -- SCENARIO -- DATA REQUIREMENTS -- PROCESSES -- STRUCTURE -- DESIGN -- THE CREATION PROCESS -- The Control Sheet -- Table Names -- Actual Data -- Structured Range Names -- Budget, Forecast GL, and Statistics Data -- THE REPORTS -- The 12-Month Report -- The Current-Month, YTD, and Full-Year Report -- The Dashboard Report -- The State Report -- CHAPTER SIXTEEN Final Thoughts -- FEEDBACK -- LAST WORDS -- About the Author -- About the Companion Website -- Index.