Founded in 1807, John Wiley & Sons is the oldest independent publishing company in the United States. With offices in North America, Europe, Asia, and Australia, Wiley is globally committed to developing and marketing print and electronic products and services for our customers' professional and personal knowledge and understanding.

The Wiley Corporate F&A series provides information, tools, and insights to corporate professionals responsible for issues affecting the profitability of their company, from accounting and finance to internal controls and performance management.

Advanced Excel Reporting for Management Accountants

NEALE BLACKWOOD

Cover image: © iStockphoto/Jess_Yu
Cover design: Wiley

Copyright © 2014 by John Wiley & Sons, Inc. All rights reserved.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey.
Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600, or on the Web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

Microsoft and Excel are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

Blackwood, Neale.
    Advanced Excel reporting for management accountants / Neale Blackwood.
        pages cm. — (Wiley corporate F & A ; 651)
    ISBN 978-1-118-65772-0 (paperback)—ISBN 978-1-118-65818-5 (ePDF)—ISBN 978-1-118-65819-2 (ePub)—ISBN 978-1-118-77716-9 (o-Book) 1. Managerial accounting—Computer programs. 2. Electronic spreadsheets—Computer programs. 3. Microsoft Excel (Computer file) I. Title.
    HF5657.4.B59 2014
    005.54024′6581511—dc23

2013046695

Thank you to the two women who have helped shape the person I am today.

To my mother, who instilled in me a love of books.

To my wife, Jan, you filled in all the missing colours in my
paint-by-numbers dreams.

CONTENTS

Preface

Acknowledgments

Introduction

CHAPTER 1 Management Accounting and Excel

Assumptions

The Goal of Reporting

Why Use Excel?

The Goal of This Book

Monthly Management Reports

Macro Policy

CHAPTER 2 Building Reporting Models

Needs Analysis

Scope Definition

Design

Construction

Testing

Operation

Maintenance

Time, Effort, and Cost

Practical Considerations

CHAPTER 3 Building Tips

Display Tips

Keyboard Shortcuts

Mouse Shortcuts

Keyboard and Mouse Shortcuts

General Tips

CHAPTER 4 Design and Structure

Structure = Flexibility

Modular Sheet Design

Standardised Report Layout

Table-Based Systems

Spreadsheet Best Practices

CHAPTER 5 Setting the Foundation

Terminology

Data Rules

Data Structures

Format as Table

Data Cleansing Techniques

External Data

CHAPTER 6 Pivot Tables (Do-It-Yourself Reporting)

The Pros and Cons of a Pivot Table

Creating a Pivot Table

PowerPivot

CHAPTER 7 Tools of the Trade: Summing Functions

Range Names

Using Cells and Ranges in Formulas

The Humble SUM Function

Advanced SUM and 3D Formulas

Subtotaling

The SUBTOTAL Function

The AGGREGATE Function

Function Wizard

Conditional Summing

The SUMIF Function

Sumif Uses

Helper Cells

The SUMIFS Function

The SUMPRODUCT Function

CHAPTER 8 Accessories: Other Reporting Functions and Features

Helper Cells

Logic Functions

The IF Function

The AND and OR Functions

Lookup Functions

The VLOOKUP Function

The HLOOKUP Function

An Alternative to VLOOKUP

The INDEX and MATCH Functions

The MATCH Function

The INDEX Function

The INDEX-MATCH Combination

Error Handling Functions

The IFERROR Function

Handling Specific Errors

Text-Based Functions

The TEXT Function

LEFT and RIGHT Functions

The MID Function

Flexible Text Manipulations

The SEARCH Function

The LEN Function

Flexible Splitting

The SUBSTITUTE Function

Converting Text to Numbers

Date Functions

The DATE Function

Other Useful Functions

Array Formulas

CHAPTER 9 Range Names

Advantages

Disadvantages

Creating a Range Name

Using Range Names

Name Manager

Naming a Range

Creating Names Automatically

Name Intersections

Dynamic Range Names

Using Structure in Range Names

INDIRECT and Range Names

Listing Range Names

CHAPTER 10 Maintenance Issues

Maintenance Instructions

The Advantages of Using Tables

Common Issues

Rolling the Year

Working with Days

Simplifying the Interface by Using Controls

CHAPTER 11 Choosing the Right Format

Colour Blindness

Format Painter

Less Is More

Fonts

Clear and Start Again

The Format Cells Dialog Box

Styles

Conditional Formatting

Printing Issues

CHAPTER 12 Picture Perfect: Charting Techniques

Chart versus Graph

Chart Basics

Charts for Reports

Automating Charts

Mixing Chart Types

Dual-Axis Charts

Handling Missing Data

Labeling Highs and Lows

Trendlines and Moving Averages

Plotting the Variance

Dashboard Techniques

Text in a Chart

The Data Series Formula

Before and After Charts

CHAPTER 13 Quality Control: Report Validation

Identifying Errors

Validations

Error Tracking

Identifying New Codes

Conditional Formatting

Suggested Validation Structure

Reasonableness Checks

CHAPTER 14 Case Study One: Month and Year-to-Date Reporting

Scenario

Data Requirements

Processes

Structure

Design

Report Layout

The Creation Process

The Reports

CHAPTER 15 Case Study Two: 12-Month Reporting

Scenario

Data Requirements

Processes

Structure

Design

The Creation Process

The Reports

CHAPTER 16 Final Thoughts

Feedback

Last Words

About the Author

About the Companion Website

Index

End User License Agreement

List of Tables

Chapter 3

Table 3.1

Table 3.2

Table 3.3

Chapter 7

Table 7.1

Table 7.2

Table 7.3

Table 7.4

Table 7.5

Table 7.6

Table 7.7

Table 7.8

Table 7.9

Chapter 8

Table 8.1

Table 8.2

Table 8.3

Chapter 9

Table 9.1

Chapter 10

Table 10.1

Chapter 14

Table 14.1

Table 14.2

Table 14.3

Table 14.4

Table 14.5

Chapter 15

Table 15.1

Table 15.2

Table 15.3

Table 15.4

Table 15.5

List of Illustrations

Chapter 3

Figure 3.1 Extra Options Icon

Figure 3.2 Customize the Quick Access Toolbar

Figure 3.3 Categories List

Figure 3.4 Modify Button Icon Gallery

Figure 3.5 Customize the Ribbon

Figure 3.6 New Tab (Custom)

Figure 3.7 Reporting (Custom) Tab and New Groups

Figure 3.8 Adding Icons to a Group

Figure 3.9 Customised Reporting Tab

Figure 3.10 Minimize Ribbon Icon

Figure 3.11 Formula Bar Resize Double-Headed Arrow

Figure 3.12 Formula Bar Resize Drop-Down

Figure 3.13 Name Box Resize Double-Headed Arrow

Figure 3.14 Status Bar Options

Figure 3.15 View Icons on the Status Bar

Figure 3.16 Alt Key Values on the Ribbon

Figure 3.17 Format Cells (Alignment)—Underlined Options

Figure 3.18 Go To Special Dialog Box

Figure 3.19 Find & Select Options—Home Ribbon

Figure 3.20 The Fill Handle

Figure 3.21 Auto Fill Options Icon

Figure 3.22 Auto Fill Options Number Options Displayed

Figure 3.23 Auto Fill Options Date Options Displayed

Figure 3.24 Structure to Copy

Figure 3.25 Closing Balance Tool Tip

Figure 3.26 Column and Row Double-Headed Arrows

Figure 3.27 Whole Sheet Icon

Figure 3.28 Editing Cell Option

Figure 3.29 Cell Right Click Menu

Figure 3.30 Row Right Click Menu

Figure 3.31 Column Right Click Menu

Figure 3.32 Sheet Right Click Menu

Figure 3.33 Sheet Navigation Buttons Right Click Menu

Figure 3.34 Right Drag Arrow Menu

Figure 3.35 Right Drag Fill Handle Menu

Figure 3.36 Ctrl Key Plus Signs

Figure 3.37 View Tab Window Options

Figure 3.38 Screen Name Change

Figure 3.39 Data Validation with Entered List

Figure 3.40 Example of Input Message Tab

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!

Lesen Sie weiter in der vollständigen Ausgabe!