Details

Advanced Excel Reporting for Management Accountants

Wiley Corporate F&A, Band 652 1. Aufl.

von: Neale Blackwood
40,99 €
Verlag:	Wiley
Format:	PDF
Veröffentl.:	10.04.2014
ISBN/EAN:	9781118658185
Sprache:	englisch
Anzahl Seiten:	448

In den Warenkorb

Als Gutschein

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

Titelbeschreibung

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. <ul> <li>Explore the structures that simplify the report creation process and make the reports more maintainable</li> <li>Learn techniques to "cleanse" data so that it is ready for use in Pivot Tables and formula-based reports</li> <li>Find out the tips and tricks that can make the creation process quicker and easier</li> <li>Discover all you need to know about Excel's summing functions and how versatile they can be</li> </ul> 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.

Inhaltsverzeichnis

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

Autorenportrait

Neale Blackwood has been using spreadsheets since the late 80’s. Starting with Lotus and moving to Excel in the mid 90’s. His roles have included Accountant, Project Accountant, Financial Controller and Management Accountant. He developed most of his advanced Excel skills as a Management Accountant. An Australian CPA and certified Microsoft Office Specialist Expert, he has written for the CPA Australia monthly magazine, INTHEBLACK, since 2002. He has written over 100 of his regular "Excel Yourself" articles and eight feature articles for the magazine. He has freely answered Excel questions from Australian CPA's from many different countries for over nine years. He has presented at numerous CPA Australia events in Australia, from one hour sessions to half-day Master Classes. He writes and presents his own XL@Lunch Excel webinars to people from around the globe.

Back cover copy

Praise for Advanced Excel Reporting for Management Accountants “Neale Blackwood, a long-time spreadsheet user and trainer, shares his knowledge of Excel reporting in a manner that’s both thorough and easy to follow. He starts with the basics, and gradually introduces key topics that utilize commonly accepted best practices. This book will make your Excel reporting more useful and less prone to errors.” —John Walkenbach, Author of Excel 2013 Bible “This book is essential reading for anyone involved in Excel reporting. It provides great insights about not only the most relevant and useful functions of Excel for reporting and management accounting, but also how to apply these in practical ways that will improve your thinking and make your reporting more efficient. Neale knows more about management accounting and Excel reporting than anyone I know, and this book allows you to benefit from his experience so you can avoid the pitfalls and dramatically improve your Excel reports.” —Jeff Robson, CEO & Principal Business Analyst, Access Analytic “Neale has brilliantly applied his considerable experience and expertise in writing this book. With clear descriptions and straightforward analogies, he gets inside the mind of the reporting analyst. With useful guidance on Excel reporting best practices, the reader does not waste time on unnecessary features but instead is guided to invaluable time-saving techniques. By applying the simple yet effective methodology described in this comprehensive book, even an experienced management reporting analyst will improve the efficiency, integrity, and robustness of their reporting tools. Definitely a career-booster!” —Danielle Stein Fairhurst, Financial Modelling Specialist & Presenter, Plum Solutions “Neale is very well known for his practical Excel skills and knowledge. His new book, Advanced Excel Reporting for Management Accountants, is highly recommended and will be useful to both accounting students and professionals alike.” —William Beattie, CPA, Business & Finance Analyst “This book is chock-full of well-explained good ideas. With hundreds of tips, even if you simply choose any three to consistently implement, your management reports (and all your spreadsheets in general) will be greatly improved. Apply more than three and your work will significantly reduce the likelihood of both qualitative and quantitative errors. Although the book uses the title ‘Advanced,’ the name relates more to the outputs than the user techniques; the language is approachable, and the book builds through from improving Excel program usability to improving the Excel spreadsheets. A great go-to manual to keep and re-read time and again.” —Eve Blackall, Smart Accounting – the Science of Business Success