Advanced Excel Reporting for Management Accountants E-Book

Neale Blackwood

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Serie: Wiley Corporate F&A
• Sprache: Englisch

Seitenzahl: 548

Veröffentlichungsjahr: 2014

Beschreibung

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.

Leseprobe

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, [phone], fax [phone], 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, [phone], fax [phone], 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 [phone], outside the United States at [phone] or fax [phone].

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.

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

Figure 3.41

Default Error Message

Figure 3.42

Customised Error Message

Figure 3.43

Display Office Clipboard

Figure 3.44

Clipboard Options

Chapter 4

Figure 4.1

Table-Based Example

Figure 4.2

Completed Employee Table

Chapter 5

Figure 5.1

Normalised Data Structure

Figure 5.2

Monthly Data Structure

Figure 5.3

Format as Table Example

Figure 5.4

Formatted Table

Figure 5.5

Formatted Table Extended

Figure 5.6

Formatted Table Formulas

Figure 5.7

Table Name on the Design Ribbon

Figure 5.8

Missing Data Structure Example

Figure 5.9

Active Cell

Figure 5.10

Formula Entered in Active Cell

Figure 5.11

Missing Field and Lookup Table

Figure 5.12

Text Dates

Figure 5.13

Leading and Trailing Spaces Examples

Figure 5.14

Trailing Spaces in the Formula Bar

Figure 5.15

Structured Code Examples

Figure 5.16

Structured Codes with Delimiters

Figure 5.17

Convert Text to Columns Wizard Steps 1 and 2

Figure 5.18

Codes Split Using Text to Columns

Figure 5.19

Import Data Dialog Box

Figure 5.20

Connection Properties Dialog Box

Chapter 6

Figure 6.1

Pivot Table Data Process

Figure 6.2

Create Pivot Table Dialog Box

Figure 6.3

Blank Pivot Table

Figure 6.4

Completed Pivot Table

Figure 6.5

Completed Classic Pivot Table

Figure 6.6

Pivot Table Sort and Filter Options

Figure 6.7

Number Format Option

Figure 6.8

Format Cells—Number Tab

Figure 6.9

Insert Slicers Dialog Box

Figure 6.10

Customer Category Slicer—Multiple Items

Figure 6.11

PivotTable Connections Dialog Box

Figure 6.12

Insert Calculated Field Dialog Box

Figure 6.13

Margin % Field

Figure 6.14

Margin % Field Formatted

Figure 6.15

Corrected Margin % Field

Figure 6.16

Expense and Revenue Data Set

Figure 6.17

Insert Calculated Item Dialog Box

Figure 6.18

Profit Calculated via Pivot Table

Figure 6.19

Profit Calculated via Pivot Table Revenue on Left

Figure 6.20

Grouped by States Pivot Table

Figure 6.21

Daily Sales by States Pivot Table

Figure 6.22

Grouping Dialog

Figure 6.23

Grouping by Months and Years

Figure 6.24

Import Data Dialog Box

Figure 6.25

Connection Properties Dialog

Figure 6.26

From Other Sources Drop-Down Selection

Figure 6.27

Pivot Chart

Figure 6.28

Show Values As Icon Options

Figure 6.29

Show Values As Report Examples

Chapter 7

Figure 7.1

Fixed and Mixed Reference Example

Figure 7.2

State Report Layout for WA

Figure 7.3

Summary Report Layout

Figure 7.4

Sheet Tab Structure, a to z

Figure 7.5

Summary Report Layout a, b, and z

Figure 7.6

Summary Report East Region

Figure 7.7

Summary Report Layout a, b, c, and z

Figure 7.8

Subtotaling Example

Figure 7.9

Automated Subtotaling Example

Figure 7.10

Subtotal Dialog Box

Figure 7.11

Subtotal Inserted

Figure 7.12

Range to Apply AGGREGATE Functions

Figure 7.13

Insert Function (fx) Icon

Figure 7.14

Select Arguments Dialog Box

Figure 7.15

Function Arguments Dialog Box

Figure 7.16

Completed Function Arguments Dialog Box

Figure 7.17

Completed AGGREGATE Example

Figure 7.18

SUMIF Example

Figure 7.19

SUMIF Example Completed

Figure 7.20

SUMIF No Range_to_Sum

Figure 7.21

SUMIF Horizontal Example

Figure 7.22

SUMIFS Example

Figure 7.23

SUMIFS Example Completed

Figure 7.24

Find and Replace—Convert Fixed Reference to Relative

Figure 7.25

SUMPRODUCT Standard Use Example

Figure 7.26

TRUE and FALSE Results

Figure 7.27

TRUE and FALSE Used in Calculations

Figure 7.28

SUMPRODUCT Multicriteria SUM

Figure 7.29

Analysis of SUMPRODUCT Formula

Figure 7.30

Date List Summarised

Figure 7.31

Pivot Table Sales Report Extract

Figure 7.32

GETPIVOTDATA Function

Figure 7.33

GETPIVOTDATA Report

Chapter 8

Figure 8.1

Logic Calculation Example

Figure 8.2

Logic Calculation Simplified Example

Figure 8.3

Nested IF Example

Figure 8.4

Alternate IF Function Layout Using Alt + Enter

Figure 8.5

Logic Table Comparing AND and OR Functions

Figure 8.6

Table to Be Filtered

Figure 8.7

Table to Be Filtered—Column Added

Figure 8.8

Table and Extraction Range

Figure 8.9

Table and Populated Extraction Range

Figure 8.10

Commission Table and Extraction Range

Figure 8.11

Commission Table and Populated Extraction Range

Figure 8.12

Monthly Table and Populated Extraction Range

Figure 8.13

Interest Rate Table and Populated Extraction Range

Figure 8.14

Ascending and Descending Range with MATCH Results

Figure 8.15

Lookup Table and MATCH Formula to Calculate Column Numbers

Figure 8.16

Statistical Table with Current Month and YTD

Figure 8.17

Colour Lookup Solution Using INDEX-MATCH

Figure 8.18

IS Functions Demonstrated

Figure 8.19

Date Shown as a Number in a Heading

Figure 8.20

Formats Cells—Number Tab—Custom Formats

Figure 8.21

TEXT Function Examples

Figure 8.22

Example of Combining Text and TEXT Functions

Figure 8.23

LEFT and RIGHT Examples

Figure 8.24

MID Function Examples

Figure 8.25

SEARCH Function Examples

Figure 8.26

Codes to Be Split Into Three Sections

Figure 8.27

Codes Split into Three Sections

Figure 8.28

SUBSTITUTE Examples

Figure 8.29

Character Count Examples

Figure 8.30

Statistical Data Requiring Month and YTD

Figure 8.31

Name Box Trick—Range Formula

Figure 8.32

CHOOSE Function Helper Cells Structure

Figure 8.33

State Sheet Structure

Figure 8.34

Summary Sheet Structure

Figure 8.35

Completed Summary Sheet Structure

Figure 8.36

Modified Summary Sheet Structure

Figure 8.37

MAXIF Results

Figure 8.38

Formula Bar MAXIF Results

Figure 8.39

Formula Bar MAXIFS Results

Figure 8.40

Unique Array Formula Example

Chapter 9

Figure 9.1

The Name Box

Figure 9.2

Named Cell

Figure 9.3

Resize the Name Box

Figure 9.4

Range Name Error Dialog Box

Figure 9.5

Name Box Drop-Down Arrow

Figure 9.6

Formula with Range Name

Figure 9.7

AutoComplete for a Range Name

Figure 9.8

Insert Hyperlink—Defined Names

Figure 9.9

Formulas Tab—Defined Names Section

Figure 9.10

Name Manager Dialog Box

Figure 9.11

Using a Row-Based Range Name

Figure 9.12

Updated Range Name and List

Figure 9.13

Departments Range Name Amended

Figure 9.14

Missing Department Noted

Figure 9.15

Stock Table and Lookup

Figure 9.16

State Holiday Dates

Figure 9.17

Create Names from Selection Dialog Box

Figure 9.18

Amended Create Names from Selection Dialog Box

Figure 9.19

Name Manager Listing New Range Names

Figure 9.20

Workday Table by Month and State

Figure 9.21

Completed Workday Table by Month and State

Figure 9.22

Head Count Table

Figure 9.23

States List

Figure 9.24

Table Name Box—Design Ribbon

Figure 9.25

New Name Dialog Box for States

Figure 9.26

Data Validation List Using States Range Name

Figure 9.27

Drop-Down List Showing States

Figure 9.28

Drop-Down List with TAS

Figure 9.29

New Name Dialog Box Formula-Based Name

Figure 9.30

Drop-Down List with NT

Figure 9.31

Statistics Table

Figure 9.32

Blank Report Structure Table

Figure 9.33

Find and Replace to Amend Formulas

Figure 9.34

Current Month Report Complete

Figure 9.35

Completed Statistics Report

Figure 9.36

Completed Statistics Report with Helper Cells

Chapter 10

Figure 10.1

Reports and Lists Sheets

Figure 10.2

Amended Reports and Lists Sheets

Figure 10.3

Weekend Numbers for NETWORKDAYS.INTL Function

Figure 10.4

Weekdays and Workdays Table

Figure 10.5

Insert Check Box Form Control

Figure 10.6

Selected Check Box Control

Figure 10.7

Linked Cell—Formula Bar

Figure 10.8

Check Box Results

Figure 10.9

Custom Number Format to Hide Cell Entries

Figure 10.10

Inflation Rates

Figure 10.11

Amended Inflation Rates

Figure 10.12

Option Button Layout

Figure 10.13

Budget Option Button Chosen

Figure 10.14

Last-Year Option Button Chosen

Figure 10.15

Combo Box and Data Validation Comparison

Figure 10.16

Combo Box on the Insert Drop-Down List

Figure 10.17

Format Control Dialog—Completed

Figure 10.18

Completed Combo Box

Figure 10.19

Combo Box Range Change

Chapter 11

Figure 11.1

Format Painter Icon

Figure 11.2

Clear Formats Icon

Figure 11.3

Format Cells Dialog Box

Figure 11.4

Number Section—Home Ribbon

Figure 11.5

Number Section—Drop-Down Options

Figure 11.6

Currency versus Accounting Formats

Figure 11.7

Comma Format Comparison

Figure 11.8

Entering Custom Number Format

Figure 11.9

Examples of Custom Number Formats

Figure 11.10

Example of Text in a Number Format

Figure 11.11

Excel Option to Suppress Zeros on a Worksheet

Figure 11.12

Merge & Center Icon

Figure 11.13

Merged Cell Error Dialog Box

Figure 11.14

Center Across Selection

Figure 11.15

Select Cell within Center Across Selection

Figure 11.16

Wrap Text Icon

Figure 11.17

Border Options on the Ribbon

Figure 11.18

All Borders and Thick Box Border

Figure 11.19

Styles on the Home Ribbon Tab—Wide Screen

Figure 11.20

Cell Styles on the Home Ribbon Tab—Small Screen

Figure 11.21

Styles Options—Small Screen

Figure 11.22

Highlight Cells Rules Options

Figure 11.23

Greater Than Dialog Box

Figure 11.24

Date Parameters

Figure 11.25

Duplicate Values Dialog Box

Figure 11.26

Top/Bottom Rules Options

Figure 11.27

Items and Percentage Changes

Figure 11.28

Data Bars Options

Figure 11.29

Gradient Fill and Solid Fill Compared

Figure 11.30

Negative Numbers and Zero Data Bars

Figure 11.31

Color Scales Options

Figure 11.32

Icon Sets Options

Figure 11.33

Conditional Formatting Rules Manager Dialog Box

Figure 11.34

Conditional Formatting Rules Manager Drop-Down List

Figure 11.35

Edit Formatting Rule Dialog Box—Data Bars

Figure 11.36

Negative Value and Axis Settings Dialog Box

Figure 11.37

Amended Report Examples

Figure 11.38

Webinar Feedback Scores

Figure 11.39

Editing Formatting Rule—Color Scale

Figure 11.40

Parameter Drop-Down Options

Figure 11.41

Amended Parameters

Figure 11.42

Amended Feedback Report

Figure 11.43

Relative Reference Error Message

Figure 11.44

Variance Report with Format Settings Below

Figure 11.45

Variance Report with Amended Format Settings Below

Figure 11.46

Variance Report with Helper Cells

Figure 11.47

Report with Conditional Format Settings

Figure 11.48

Format Cells Dialog Box—Limited Tabs

Figure 11.49

Report Using Helper Cells

Figure 11.50

NOT Function Example Formula

Figure 11.51

Pivot Table Conditional Format Options

Figure 11.52

Page Break Preview with Right-Click Menu

Figure 11.53

Page Layout View

Figure 11.54

Print Option—Narrow Custom Margins

Figure 11.55

Print Option—Fit All Columns on One Page

Chapter 12

Figure 12.1

Drop-Down List Showing Chart Objects

Figure 12.2

Monthly Chart Example

Figure 12.3

Updated Monthly Chart Example

Figure 12.4

Monthly Chart Example—Month Added

Figure 12.5

Automated Rolling 13-Month Chart

Figure 12.6

Automated Rolling 13-Month Chart Monthly Update

Figure 12.7

Line Chart Plotting Zeros

Figure 12.8

Line Chart Not Plotting Zeros

Figure 12.9

Changing Chart Type

Figure 12.10

Chart Data—Dollars and Percentages

Figure 12.11

Select Series from Drop-Down List

Figure 12.12

Axis Change

Figure 12.13

Axis Settings

Figure 12.14

Dual-Axis Chart

Figure 12.15

Missing Data Options

Figure 12.16

Select Data Source Dialog Box—Hidden and Empty Cells Button

Figure 12.17

Hidden and Empty Cell Settings Dialog Box

Figure 12.18

Text Axis Option

Figure 12.19

Hidden Date Rows

Figure 12.20

Show High and Low Labels on a Line Chart

Figure 12.21

Selecting a Data Label

Figure 12.22

Format Trendline Dialog Box

Figure 12.23

Variance Charts

Figure 12.24

Excel Options—Camera Icon

Figure 12.25

Summary Report

Figure 12.26

Summary Report Copy

Figure 12.27

Reduced-Size Picture Report

Figure 12.28

Paste Picture Link Range Reference

Figure 12.29

Dynamic Picture Link System

Figure 12.30

INDIRECT Function Used with Range Name

Figure 12.31

Completed Dynamic Picture Link System

Figure 12.32

Dynamic Picture Link Updated

Figure 12.33

Linked Text Box

Figure 12.34

Linked Text Box with a Line Break

Figure 12.35

Drawing Tools Format Ribbon Tab

Figure 12.36

Text Box Contents (Top), Text Box (Bottom)

Figure 12.37

Select Objects Icon

Figure 12.38

Grouping Two Text Boxes Together

Figure 12.39

Weekly Production Report

Figure 12.40

Create Sparklines Dialog Box

Figure 12.41

Sparkline Axis Issue

Figure 12.42

Sparkline Axis Settings

Figure 12.43

Conditional Format in a Sparkline Cell

Figure 12.44

Sparkline Options—Right-Click

Figure 12.45

Chart and Text Box

Figure 12.46

SERIES Formula Example

Figure 12.47

Column Chart Comparison

Figure 12.48

Line Chart Comparison

Figure 12.49

Column versus Bar Chart

Chapter 13

Figure 13.1

F9 Used in the Formula Bar

Figure 13.2

Evaluate Formula Example

Figure 13.3

Evaluate Box Results

Figure 13.4

Trace Precedents Results

Figure 13.5

Trace Dependents Results

Figure 13.6

External Trace Precedents Results

Figure 13.7

New Codes Example

Figure 13.8

Red Fill for FALSE Entries

Figure 13.9

Suggested Validation Structure

Chapter 14

Figure 14.1

Budget Layout

Figure 14.2

System-Generated P&L Report Layout

Figure 14.3

Data Cleansing Columns, P&L Report Layout

Figure 14.4

Statistics Sheet Structure

Figure 14.5

Statistics Sheet Totals

Figure 14.6

Forecast Statistics Sheet

Figure 14.7

State Report Extract—Completed

Figure 14.8

State Report—Helper Cells Displayed

Figure 14.9

Department Report—Completed

Figure 14.10

Department Report—Helper Cells Displayed

Figure 14.11

Total_State Report 3D Sum

Figure 14.12

Total_Check Report Based on State Sheet

Figure 14.13

Chart_Data Sheet

Figure 14.14

Chart_Data Top Helper Cells

Figure 14.15

Chart_Data Left Side Helper Cells

Figure 14.16

Dashboard Sheet—Completed

Figure 14.17

Create Sparklines Dialog Box

Figure 14.18

Linked Picture Formula

Figure 14.19

Text Boxes on the Dashboard Sheet

Chapter 15

Figure 15.1

Actual GL Data Layout

Figure 15.2

Actual Statistics Data Layout

Figure 15.3

Control Sheet Layout

Figure 15.4

Data Cleansing Columns

Figure 15.5

Data Validation Columns

Figure 15.6

New Name Dialog Box

Figure 15.7

Current-Month, YTD, and Full-Year Report Structure

Figure 15.8

Monthly Report Structure

Figure 15.9

Helper Cells Structure

Figure 15.10

Tables Sheet

Figure 15.11

Budget Layout of the Report Sheet

Figure 15.12

Forecast Layout of the Report Sheet

Figure 15.13

Previous-Year Layout of the Report Sheet

Figure 15.14

Grouping Buttons on Report Sheet

Figure 15.15

Dashboard Sheet

Figure 15.16

Tolerance of –2%

Figure 15.17

Dashboard _Data Sheet—Column Chart Data

Figure 15.18

Dashboard _Data Sheet—Bar Charts Data

Figure 15.19

Dashboard _Data Sheet—Line Charts Data

Figure 15.20

Dashboard _Data Sheet—Actuals and Comparison Tables

Figure 15.21

Dashboard_Data Sheet—Budget and Previous-Year Tables

Figure 15.22

Select Objects Icon

Figure 15.23

Format Ribbon Tab

Figure 15.24

State Report Sheet

Guide

Cover

Table of Contents

Preface

Introduction

Chapter

Pages

ii

iii

iv

v

xiii

xv

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

411

413

414

415

416

417

418

419

420

421

422

423

424

Preface

SPREADSHEETS HAVE BEEN A MAJOR part of my work life for nearly 25 years, and most of that time has been spent using Excel. Throughout that period I have learned many techniques that can simplify the reporting process. This book captures that knowledge and shares it in a format that you can learn and then apply to your reporting needs. You can benefit from my successes and, just as important, avoid my mistakes.

Of course, how long you have been using a piece of software isn’t always a good indicator of how good you are with it. I’ve heard it said, “He’s used Excel for 10 years, but it’s been the same year repeated 10 times.”

Unfortunately, when Excel gets upgraded these days, you get little training in the new features. The training you do get always focuses on showing you how to do the things you were doing in the old version. That’s a good start, but it’s like giving a man a two-story house and not showing him the stairs because their old house was a single-story house and didn’t have stairs.

I’ve been lucky in my career, because I have been given the opportunity to train and share my knowledge for many years. Training is a great way to improve the trainer’s knowledge. I love training, and I love answering questions and sharing my knowledge. Questions tend to open one’s mind to how differently people think about the same topic.

Being a fairly persistent person has helped me in my Excel journey. I don’t give up easily. That’s a very useful trait for any spreadsheet developer. Excel can be frustrating at times, and I hope this book will help to remove some of the frustration for you.

Having an enquiring mind has led me to always look for better ways to do things. I don’t like to waste my time, so if a task is repeatedly taking me a long time, I will look for a better way to do it. Typically I will look for a built-in method to improve the task. If that fails, I will usually resort to a macro.

I’ve answered thousands of questions from Australian CPAs over the years, and I must admit that some of the solutions can be achieved only through macros. A macro is necessary when the data set or the report has been built in a way that doesn’t permit the use of Excel’s built-in features.

For the purposes of this book, macros are mentioned only as a side issue. The models and examples in this book are macro-free. The companion website has a file that includes some helpful macros. These are meant to simplify and speed up the developmental process.

Acknowledgments

I’D LIKE TO ACKNOWLEDGE a few people whose books and websites have helped me along my Excel journey.

John Walkenbach: What can I say that hasn’t already been said about the man? His books set the benchmark for Excel books. Thank you for your huge contribution to the Excel knowledge base. Your books gave me the confidence to experiment, and that is when my skills started to expand.

David and Raina Hawley: These are the people behind the Ozgrid.com website. I learned many great techniques there.

Michael Alexander: Another great Excel author with far-reaching knowledge and a relaxed and entertaining writing style.

Bill Jellen: Thanks for the MrExcel website, your podcasts, and your books.

Once the Internet became more available, my skills were boosted by the Excel-G and Excel-L e-mail lists. These lists are like a forum via e-mail. Lots of knowledgeable contributors freely help others and share their knowledge.

Thanks to CPA Australia, which published my first Excel article in its monthly magazine in May 2002. This started my love of writing, and I have been contributing to this magazine for more than 11 years. Thanks to the magazine’s staff over the years: Jackie Blondell, Prue Moodie, Matthew Dillon, and John Hampshire. You supported me and allowed me to do a few feature articles that let me cover Excel topics in more depth. One of those articles was the inspiration for this book.

Thanks also to CPA Australia for allowing me to present Excel sessions at its events, where I could teach large audiences.

Thanks to my former bosses, Richard Hyland and Jeff Robson. They both supported me and encouraged me to push Excel to its limits.

To all the CPAs over the years who have e-mailed me questions, thanks. Besides providing material for my articles, you also expanded my Excel knowledge by asking things I had never thought of.

To Wiley, thanks for the opportunity to write this book, and a big thanks to the editing team for improving the finished product.

Finally, a special thanks to my wife, Jan, who has always loved, supported, and encouraged me. Her patience whilst I wrote this book was legendary.

Introduction

If you only have a hammer, you tend to see every problem as a nail.

—Abraham Maslow

THE FOCUS OF THIS BOOK is to provide you with a blueprint that allows you to build spreadsheet reporting models. The techniques are advanced, but they are explained and demonstrated so that you can easily apply them to your particular situation. This is not a beginner’s book; it assumes you have a good working knowledge of Microsoft Excel.

The book is aimed at management accountants and business analysts because they tend to have to report on many different aspects of a business, and they also frequently perform ad hoc reporting.

The reports created are finance-based reports. The techniques used can be applied to any type of reporting. My background is in accounting, and that is what I focus on.

In terms of versions, the examples and pictures are all based on Excel 2010 unless otherwise specified. The content applies directly to Excel 2007 and Excel 2013. Many of the techniques covered can also be achieved in Excel 2003. Indeed many of the Excel 2003 models that I built using these techniques are still running at the time of this writing.

If you know how to use only a few basic functions, then the reporting models you build will be basic and likely to have many manual steps.

I provide you with many different tools to improve your reporting spreadsheets. These tools are extremely flexible, and as you learn and implement them, you will no doubt develop your own applications for them. These tools can also be used for budgeting and financial modeling.

One of my greatest satisfactions as an Excel developer is when a user takes a model I have built and uses it to do something I had never envisaged (in a good way). This means I have done my job well enough to build in the flexibility to allow users to employ their imaginations in arriving at solutions I had never thought of.

I compare the construction of a spreadsheet to the construction of a building. They have many things in common. One thing that they don’t have in common, however, is that buildings are not created by one person, whereas spreadsheets typically are. In this respect you will need to put on different hats at different stages of the development process.

You most likely already have a reporting model, and the ideas in this book provide some renovation ideas for you. Changing an existing spreadsheet, especially one that you have not built, can be a daunting prospect. When you see the advantages of using these advanced techniques, I hope you will be inspired to tackle the renovation project.

Creating a reporting spreadsheet is serious business, and if you do it well it can save you hours of work in creating the monthly report, which then frees up time to spend analysing and adding value to the reporting process.

The spreadsheet designs you will be introduced to address many criticisms of spreadsheets as reporting models. Let’s be honest: A spreadsheet is just another piece of software. People make mistakes, and mistakes are part of life. Ideally, you learn from your mistakes and improve. I’ve made many mistakes, and I’ve learned from them and improved because of them.

I am passing on my experience here so that you can avoid my mistakes. You will no doubt make your own mistakes, but the validation techniques described will help you identify those mistakes early and allow you to fix them.

I’ve been lucky enough to have a few supportive bosses over the years who have let me “play” with Excel. By experimenting and trying new things, you can learn a lot. Don’t be afraid to experiment, and don’t be afraid to make mistakes. The Undo command can fix most things. Saving files regularly and using version numbers can make going back to a previous version easier.

Don’t be overwhelmed by the amount of content in the book. As you read, take note of those techniques you can benefit from immediately and start to incorporate them in your Excel files as soon as possible. The sooner you start to use new techniques, the sooner you will master them.

Chapters 14 and 15 each contain a case study. These case studies provide a framework to demonstrate the combination of techniques from the other chapters in providing a reporting solution.

When sharing Excel knowledge, you need to explain techniques in isolation so that you can focus on the important parts of each technique. Unfortunately, it’s not always obvious how the techniques can be combined.

The case studies provide a bridge between the individual techniques and how they can be combined to provide a seamless reporting solution.

CHAPTER 1Management Accounting and Excel

IT HAS BEEN MY EXPERIENCE that management accountants tend to be the power users of Excel within their organisations. They usually end up being the unofficial Excel help desk. For this reason, I assume the reader has a good working knowledge of Excel and its built-in features.

Position titles vary between organisations, and some of the titles I consider closely related to management accountants are business analysts and cost accountants.

Although many of the topics covered are advanced, I include some basic and intermediate topics when they help explain the more advanced topics. I have found over the years that even advanced Excel users can learn new tips and tricks that improve their Excel productivity.

Many users are self-taught and haven’t always learned the best ways to do things. I will share many useful Excel shortcuts in Chapter 3.

ASSUMPTIONS

The premise behind the book is that you have an accounting system but its reporting package does not provide the flexibility you need to create the reports you need. Excel can extract data from most accounting systems and databases. So you can use your existing source data to build your reports.

You may also need to bring together data from other sources and incorporate those values with the financial data. This is an area that has the most scope for providing value-adding reports.

Excel is ideally suited to combining data, financial and non-financial, from different data sources into a single reporting model.

Given that some data resides outside of databases, Excel can also provide the ability to incorporate other small databases that may be held in other spreadsheets.

Please note Excel is not a database. Excel can be used as the data repository for small statistical-type data that may not warrant a database system.

You may also develop your budgets and forecasts in Excel and these can be integrated into your reporting model.

If your database systems do not allow direct connection to Excel, then most database systems have the ability to create files that Excel can read. In general you should aim to have all your data in databases.

Australian Conventions

Examples in this book may include Australian tax terminology, such as GST (goods and services tax).

The Australian financial year is from July 1 to June 30, which spans two calendar years.

Australia has six states and two territories. These are often considered regions for reporting. Laws and holidays can vary amongst the states, and it may be necessary to report differently state by state.

Versions

The instructions and images all relate to Excel 2010 unless otherwise noted. Most instructions will also apply to Excel 2007 and 2013. Some of the new features in Excel 2013 will be noted but not explained in detail.

Many of the techniques can be applied to Excel 2003, but no instructions are included for Excel 2003.

Terminology

There are two terms used frequently in the following chapters that require definition.

Parentheses.

Parentheses

is the correct term for the symbols used with Excel functions to enclose the function arguments (see the following term). On the Internet and in general conversation, these are commonly referred to as

brackets

. In this book, the term used is

parentheses

.

Arguments.

In this book, the parts of an Excel function are referred to as

arguments.

Many functions accept a single argument, such as the SUM function. Others require more arguments; for instance, the VLOOKUP function requires at least three arguments to return a result. Between the parentheses, arguments are separated by commas.

Argument

is the term Microsoft uses in its Excel help system.

Spelling

The book uses UK spelling, except when referring to Excel features. Excel uses U.S. spelling and all the Excel terms will be spelled as they appear in Excel.

THE GOAL OF REPORTING

Management accounting reports are generally created for performance review. Compliance reports that satisfy a company’s legal reporting requirements tend to be reasonably similar across industries. Performance reporting varies depending on the industry and the sector. Excel provides the flexibility to develop performance reports that meet your needs.

Performance Measurement

Measuring your daily, weekly, and monthly performance against the budget, a forecast, or the previous year is the main focus of management reporting. This variance analysis is an important part of the reporting process.

Businesses already have accounting system reporting structures in place. These reports tend to be created by the database system that contains the data and are often limited in their layout structures.

In production systems and other systems in which volumes are important, comparing performance with the total dollar amounts may not be an accurate measure, and per-unit calculations are often required.

Charts can identify relationships and trends and are an important part of the reporting process. Many database systems have limited charting capabilities.

Performance Improvement

Another type of reporting focuses on improving performance. This can involve benchmarking and comparing performance among branches or divisions.

This type of reporting can be ad hoc. Areas may be identified for review and reports created to measure the relevant metrics to assist that review.

The techniques discussed throughout this book can be applied to ad hoc reports. Pivot tables, discussed in Chapter 6, are especially suited to ad hoc reporting.

WHY USE EXCEL?

Excel is the spreadsheet of choice for most accountants. Virtually all accountants use Excel, with various degrees of skill. Excel is the industry’s standard spreadsheet, and it is constantly being upgraded and updated to handle today’s changing information needs.

Most finance staff members have at least rudimentary skills in Excel.

Excel 2010, which this book is based on, can handle data sets of hundreds of thousands of rows. The built-in data connection systems allow direct connection to most database programs in organisations. Excel 2013 was released in January 2013, and it includes a suite of extra features that can simplify reporting even further.

Excel has the ability to create charts that can summarise large data sets into visual, dashboard reports. There are other reporting packages in the market that can create dashboard reports, but none have the popularity of Excel.

Learning to develop reporting models in Excel is a skill that is transferable between jobs. Other dashboard packages do not have the widespread acceptance of Excel.

Excel has its limitations, but if you are aware of them and build your reporting models accordingly, then you can avoid most issues and create a system that will stand the test of time.

This book encourages you to validate your reports and include validations in all stages of the development process. Reporting models tend to be a work in progress in many organisations. The techniques included in this book make the process of adding to or amending your model easier.

THE GOAL OF THIS BOOK

This book explains and demonstrates many techniques that can be used together to create a reporting system in Excel that has the following characteristics.

Easy to Operate

The reporting model should be easy for the user to operate. This means that there should be a minimum of typing required. The interface should use drop-down selections and check boxes where appropriate to handle selections. Navigation should be straightforward, much like web pages, so you can move quickly and easily between the important sheets.

Easy to Maintain

Reporting models are subject to change. There are the normal day-to-day changes caused by new accounts and new departments; these changes should be easy to handle in your reporting model. Then there are the more drastic changes, in which divisions may be added or removed or the structure of the report has to change to meet new reporting requirements. These changes require more effort but should still be straightforward and logical.

Self-Validating

A number of techniques are demonstrated that make identifying and tracking down errors easier. It’s one thing to identify that the balance sheet doesn’t balance. It’s another to identify why it doesn’t balance and to provide assistance in tracking down any issues.

Modular

Having a modular approach to creating your file will make achieving the other goals easier. Modular spreadsheets have dedicated sheets for instructions, inputs, settings, data, validations, tables, lists, reports, and charts.

Structured

Structure is achieved by setting and following rules in the developmental process. This book recommends that you use a table structure for much of your data and parameters. The use of lists and range names is also encouraged and is covered in depth in this book.

Flexible

Adding structure allows flexibility. This theme is repeated throughout the book. It seems counterintuitive, but structure provides the framework to incorporate flexibility.

MONTHLY MANAGEMENT REPORTS

These techniques work very well with the standard monthly management reports that management accountants typically create. The techniques can also be applied to virtually any type of report. The monthly management reports tend to be the most time-consuming and offer the most benefits for improving.

The techniques described in this book simplify the reporting process by automating many tasks that in the past had to be done manually.

MACRO POLICY

The techniques described in this book allow you to automate your reporting processes without the use of macros. Formulas and functions are used to automate tasks that had to be done manually in the past. No macro will be used to run the reporting models.

My policy on macros is that you should use Excel’s built-in features to their limit and resort to macros only when the built-in features do not provide the functionality that you require.

Macros can be used to replace repetitive tasks, performing those tasks in the blink of an eye and reducing manual processes.

The website for this book includes a number of macros that can assist you in both the model creation phase and the data cleansing phase. I have included instructions on how to install and use the macros. They are not part of the reporting process as such but are basically accessories that can speed up the process of report creation and data cleansing.

Macros are a huge topic in Excel, but this book does not go into depth about them. If you want to learn about macros, I suggest you consult the reading list I have provided on the website.

WARNING: Macros Cannot Be Undone

When you run a macro, you clear Excel’s undo list. This means you can’t undo what the macro does, and you can’t undo anything done before you ran the macro.

Before running a macro it is a good idea to save the file. At least then, if the macro does cause a problem, you can close the file without saving, to revert to the pre-macro version.

CHAPTER 2Building Reporting Models

BUILDING A REPORTING MODEL from scratch is much like building a house from scratch. Amending an existing reporting model can be likened to renovating an existing house.

When creating a report, you need to define your requirements and set out your scope. This allows you to design, construct, test, use, and then maintain your report. These are very much like the steps you take in building a house.

You need to identify what type of house you want and then get someone to design it for you. The architect will quiz you on how many bedrooms you need, the type of kitchen you want, and all the other factors he or she needs to know to design the right house for you. Similarly, you need to find out what you or the Excel users need to have in the reporting model. This can include both current requirements and possible future requirements.

You might not have any children, but you may want to build a four-bedroom house because you plan on having children. A similar dynamic is true for reporting. There are current requirements to be met, but there may be plans for future expansion that you can build into the structure of the reporting model now rather than cause a major change in the future.

Just like a house, a report needs a solid foundation. The data forms the foundation of your report. Your reports are built on top of your data. If the data structure is not laid out well or is incomplete, then creating and running the reports will be time-consuming and more difficult.

You need to get the layout of your house right, which means, in spreadsheet terms, laying out your worksheets correctly. Your house has to look good—not too gaudy and not too bland. Similarly, for your reports, your formatting determines how the reports will look. Colour is another important aspect of the report. You need to use both colour and formatting, sparingly and carefully.

Finally, you need to make sure that your house is easy to maintain. Similarly, your reports shouldn’t require major rewriting just to add a new department. All common changes should be straightforward.

You need to define the reports that you want, and this definition will determine the data you require. You may have that data in a system already, or you may need to create a system to collect it.

Since data determines the types of reports that you can create, the structure of the data is very important. The data is your foundation, and if your foundation is not sturdy, creating the reports can be problematic or difficult. If your data is well structured, you will find that your reports are easy to create.

In an ideal world you would be able to get all the data you need in the structure you require. Unfortunately, in the real world you often have to make do with the data you can get out of a system. If that is the case, then the data will limit the reports you can create.

In many cases it can be worth spending time cleansing your data (see Chapter 5), which then makes it easier to create your reports.

In many of my consulting jobs, my first task is to get the data right. People like to jump right in and start creating the report, but first spending time getting your data structured correctly makes the report creation process so much easier. Chapter 5 is dedicated to getting your foundation right so that your data is ready to use.

Calculation speed will be affected by the design you choose. This includes the functions you decide to use. There is often more than one way to do something in Excel, so you should choose the fastest calculation option. This could mean avoiding certain functions in large files.

When creating reports, follow these steps:

Needs analysis

Scope definition

Design

Construction

Testing

Operation

Maintenance

NEEDS ANALYSIS

Before starting the design phase, you must define what your report is intended to do. Who is going to read it, and what do they want to use it for? Based on what the report is for, you will have to determine where the data comes from or even whether the data exists.

If the data doesn’t exist, you will have to decide whether it is cost-effective to gather and maintain the data.

SCOPE DEFINITION

The scope of the reporting model is defined by your needs, and it will include such things as the data sources and when and how the report is to be created.

Scoping is one area that tends to be a problem with Excel models. It is common for models to start out meeting one requirement, then other reports are added, like extensions to a house. If you build the original model well enough, these additions can be handled easily.

If you are aware of likely changes to a business, you can incorporate a structure into the model that will assist in creating new reports based on possible changes.

DESIGN

Design is an important step that is often overlooked. Users tend to start creating spreadsheets before they have thought things through. The more time you spend on design, the less time you will have to spend on reworking spreadsheets.

You must think ahead and use your needs analysis and scope to help you figure out the various components you need to build into your spreadsheets. In the design phase, you need to think about using the model on an ongoing basis as well as the maintenance aspect of the model. Depending on your reporting time frame, you need to consider the daily, weekly, or monthly maintenance.

You also have to think about what happens at the end of the year and how easy it is to convert to the next year for the reporting model.

By thinking ahead you can avoid having to make major structural changes to your reporting model in the future. You can also design how to handle structural changes—for example, adding a new department or removing an old department. Normal maintenance processes should be straightforward.

When designing, you need to think about who is going to use the model and how you can make it as easy as possible to use.

Table-driven design is recommended in this book because it provides the flexibility required to handle most situations. The use of range names is also recommended because it provides another way to add structure into your reporting files

CONSTRUCTION

Building the reporting model is much easier when you have already prepared the data and detailed the requirements. Completing the design part of the process then allows you to build your spreadsheets just once.

Part of the construction phase involves creating your instructions, which include the normal day-to-day or month-to-month operation of the model. You also need to add maintenance instructions. These should include what happens when you change a reporting year, what happens when you need to change your forecasts, or any other eventualities that you can think of.

During the construction phase, you usually identify most of the validations you will need to run the reporting model. Building validations into the model during the construction phase helps maintain model integrity throughout the process.

This book provides the tools you need to successfully undertake this construction phase.

TESTING

Testing is another area that is usually overlooked or at least given only a cursory glance. You need to set up your tests and think about what could go wrong, then test for those eventualities. You should lean towards pessimistic thinking in this phase.

Testing is also performed as you build. You need to identify all the issues you have to handle as you go along. Some things become apparent only as you build the model.

You will most likely need to expand your validations as you encounter errors that need to be trapped and identified.

OPERATION

Operation should be the easiest part of the whole process, because you have already designed, constructed, and tested the model.

Make sure you consider the user in the earlier phases so that this step is the easiest. You may need to work a little harder in the design and construction phases to make using the model easier. Remember that you design and construct only once, but you have to use the model regularly.

MAINTENANCE

Maintenance complexity tends to be related to the complexity of the model. How well you build your reporting model will determine how easy it is to maintain. You must consider the maintenance issues during the design phase.

You can spare the user many manual processes if you build the model correctly, but sometimes that means the maintenance processes have to be more complex.

Detailed instructions are usually very important for this phase.

TIME, EFFORT, AND COST

The three considerations of time, effort, and cost are interlinked but can be discussed separately.

Time

The month-end schedule is typically driven by tight deadlines. Time has been pared down to get the report out as soon as possible. During the month-end period, time is your most limited resource.

If your reports are daily, you don’t want to spend much of the day creating them, so again, speed is of the essence.

Delegating tasks can help. If you can simplify the processes sufficiently, then delegation can save you vital time. Processes that can be done simultaneously by different people can also save time.

Effort