{"product_id":"advanced-excel-reporting-for-management-accountants-isbn-9781118657720","title":"Advanced Excel Reporting for Management Accountants","description":"\u003cb\u003eThe advanced tools accountants need to build automated, reliable, and scalable reports using Excel\u003c\/b\u003e \u003cp\u003eLearn 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 \u003ci\u003eAdvanced Excel Reporting for Management Accountants\u003c\/i\u003e.\u003c\/p\u003e \u003cul\u003e \u003cli\u003eExplore the structures that simplify the report creation process and make the reports more maintainable\u003c\/li\u003e \u003cli\u003eLearn techniques to \"cleanse\" data so that it is ready for use in Pivot Tables and formula-based reports\u003c\/li\u003e \u003cli\u003eFind out the tips and tricks that can make the creation process quicker and easier\u003c\/li\u003e \u003cli\u003eDiscover all you need to know about Excel's summing functions and how versatile they can be\u003c\/li\u003e \u003c\/ul\u003e \u003cp\u003eWritten in a hands-on style that works towards the completion of two reporting case studies, \u003ci\u003eAdvanced Excel Reporting for Management Accountants\u003c\/i\u003e explains and demonstrates techniques so that Management Accountants can learn how to automate many aspects of the reporting process.\u003c\/p\u003e \u003cp\u003ePreface xiii\u003c\/p\u003e \u003cp\u003eAcknowledgments xv\u003c\/p\u003e \u003cp\u003eIntroduction 1\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 1 Management Accounting and Excel 3\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAssumptions 3\u003c\/p\u003e \u003cp\u003eThe Goal of Reporting 5\u003c\/p\u003e \u003cp\u003eWhy Use Excel? 5\u003c\/p\u003e \u003cp\u003eThe Goal of This Book 6\u003c\/p\u003e \u003cp\u003eMonthly Management Reports 7\u003c\/p\u003e \u003cp\u003eMacro Policy 7\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 2 Building Reporting Models 9\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eNeeds Analysis 10\u003c\/p\u003e \u003cp\u003eScope Definition 10\u003c\/p\u003e \u003cp\u003eDesign 11\u003c\/p\u003e \u003cp\u003eConstruction 11\u003c\/p\u003e \u003cp\u003eTesting 12\u003c\/p\u003e \u003cp\u003eOperation 12\u003c\/p\u003e \u003cp\u003eMaintenance 12\u003c\/p\u003e \u003cp\u003eTime, Effort, and Cost 12\u003c\/p\u003e \u003cp\u003ePractical Considerations 13\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 3 Building Tips 15\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eDisplay Tips 16\u003c\/p\u003e \u003cp\u003eKeyboard Shortcuts 26\u003c\/p\u003e \u003cp\u003eMouse Shortcuts 33\u003c\/p\u003e \u003cp\u003eKeyboard and Mouse Shortcuts 42\u003c\/p\u003e \u003cp\u003eGeneral Tips 48\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 4 Design and Structure 57\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eStructure = Flexibility 57\u003c\/p\u003e \u003cp\u003eModular Sheet Design 58\u003c\/p\u003e \u003cp\u003eStandardised Report Layout 60\u003c\/p\u003e \u003cp\u003eTable-Based Systems 62\u003c\/p\u003e \u003cp\u003eSpreadsheet Best Practices 63\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 5 Setting the Foundation 67\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eTerminology 68\u003c\/p\u003e \u003cp\u003eData Rules 68\u003c\/p\u003e \u003cp\u003eData Structures 69\u003c\/p\u003e \u003cp\u003eFormat as Table 70\u003c\/p\u003e \u003cp\u003eData Cleansing Techniques 74\u003c\/p\u003e \u003cp\u003eExternal Data 80\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 6 Pivot Tables (Do-It-Yourself Reporting) 85\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Pros and Cons of a Pivot Table 85\u003c\/p\u003e \u003cp\u003eCreating a Pivot Table 88\u003c\/p\u003e \u003cp\u003ePowerPivot 108\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 7 Tools of the Trade: Summing Functions 111\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eRange Names 112\u003c\/p\u003e \u003cp\u003eUsing Cells and Ranges in Formulas 112\u003c\/p\u003e \u003cp\u003eThe Humble SUM Function 116\u003c\/p\u003e \u003cp\u003eAdvanced SUM and 3D Formulas 117\u003c\/p\u003e \u003cp\u003eSubtotaling 120\u003c\/p\u003e \u003cp\u003eThe SUBTOTAL Function 121\u003c\/p\u003e \u003cp\u003eThe AGGREGATE Function 125\u003c\/p\u003e \u003cp\u003eFunction Wizard 127\u003c\/p\u003e \u003cp\u003eConditional Summing 127\u003c\/p\u003e \u003cp\u003eThe SUMIF Function 131\u003c\/p\u003e \u003cp\u003eSUMIF Uses 135\u003c\/p\u003e \u003cp\u003eHelper Cells 135\u003c\/p\u003e \u003cp\u003eThe SUMIFS Function 136\u003c\/p\u003e \u003cp\u003eThe SUMPRODUCT Function 138\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 8 Accessories: Other Reporting Functions and Features 153\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eHelper Cells 153\u003c\/p\u003e \u003cp\u003eLogic Functions 155\u003c\/p\u003e \u003cp\u003eThe IF Function 157\u003c\/p\u003e \u003cp\u003eThe AND and OR Functions 161\u003c\/p\u003e \u003cp\u003eLookup Functions 164\u003c\/p\u003e \u003cp\u003eThe VLOOKUP Function 164\u003c\/p\u003e \u003cp\u003eThe HLOOKUP Function 168\u003c\/p\u003e \u003cp\u003eAn Alternative to VLOOKUP 170\u003c\/p\u003e \u003cp\u003eThe INDEX and MATCH Functions 170\u003c\/p\u003e \u003cp\u003eThe MATCH Function 170\u003c\/p\u003e \u003cp\u003eThe INDEX Function 172\u003c\/p\u003e \u003cp\u003eThe INDEX-MATCH Combination 174\u003c\/p\u003e \u003cp\u003eError Handling Functions 175\u003c\/p\u003e \u003cp\u003eThe IFERROR Function 175\u003c\/p\u003e \u003cp\u003eHandling Specific Errors 177\u003c\/p\u003e \u003cp\u003eText-Based Functions 180\u003c\/p\u003e \u003cp\u003eThe TEXT Function 181\u003c\/p\u003e \u003cp\u003eLEFT and RIGHT Functions 183\u003c\/p\u003e \u003cp\u003eThe MID Function 184\u003c\/p\u003e \u003cp\u003eFlexible Text Manipulations 185\u003c\/p\u003e \u003cp\u003eThe SEARCH Function 185\u003c\/p\u003e \u003cp\u003eThe LEN Function 187\u003c\/p\u003e \u003cp\u003eFlexible Splitting 187\u003c\/p\u003e \u003cp\u003eThe SUBSTITUTE Function 188\u003c\/p\u003e \u003cp\u003eConverting Text to Numbers 190\u003c\/p\u003e \u003cp\u003eDate Functions 190\u003c\/p\u003e \u003cp\u003eThe DATE Function 191\u003c\/p\u003e \u003cp\u003eOther Useful Functions 192\u003c\/p\u003e \u003cp\u003eArray Formulas 201\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 9 Range Names 209\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAdvantages 210\u003c\/p\u003e \u003cp\u003eDisadvantages 210\u003c\/p\u003e \u003cp\u003eCreating a Range Name 211\u003c\/p\u003e \u003cp\u003eUsing Range Names 213\u003c\/p\u003e \u003cp\u003eName Manager 216\u003c\/p\u003e \u003cp\u003eNaming a Range 218\u003c\/p\u003e \u003cp\u003eCreating Names Automatically 222\u003c\/p\u003e \u003cp\u003eName Intersections 227\u003c\/p\u003e \u003cp\u003eDynamic Range Names 228\u003c\/p\u003e \u003cp\u003eUsing Structure in Range Names 233\u003c\/p\u003e \u003cp\u003eINDIRECT and Range Names 236\u003c\/p\u003e \u003cp\u003eListing Range Names 237\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 10 Maintenance Issues 239\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eMaintenance Instructions 239\u003c\/p\u003e \u003cp\u003eThe Advantages of Using Tables 240\u003c\/p\u003e \u003cp\u003eCommon Issues 241\u003c\/p\u003e \u003cp\u003eRolling the Year 241\u003c\/p\u003e \u003cp\u003eWorking with Days 242\u003c\/p\u003e \u003cp\u003eSimplifying the Interface by Using Controls 244\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 11 Choosing the Right Format 255\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eColour Blindness 255\u003c\/p\u003e \u003cp\u003eFormat Painter 256\u003c\/p\u003e \u003cp\u003eLess Is More 256\u003c\/p\u003e \u003cp\u003eFonts 257\u003c\/p\u003e \u003cp\u003eClear and Start Again 257\u003c\/p\u003e \u003cp\u003eThe Format Cells Dialog Box 257\u003c\/p\u003e \u003cp\u003eStyles 270\u003c\/p\u003e \u003cp\u003eConditional Formatting 272\u003c\/p\u003e \u003cp\u003ePrinting Issues 293\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 12 Picture Perfect: Charting Techniques 299\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eChart versus Graph 300\u003c\/p\u003e \u003cp\u003eChart Basics 300\u003c\/p\u003e \u003cp\u003eCharts for Reports 302\u003c\/p\u003e \u003cp\u003eAutomating Charts 302\u003c\/p\u003e \u003cp\u003eMixing Chart Types 307\u003c\/p\u003e \u003cp\u003eDual-Axis Charts 308\u003c\/p\u003e \u003cp\u003eHandling Missing Data 311\u003c\/p\u003e \u003cp\u003eLabeling Highs and Lows 313\u003c\/p\u003e \u003cp\u003eTrendlines and Moving Averages 315\u003c\/p\u003e \u003cp\u003ePlotting the Variance 316\u003c\/p\u003e \u003cp\u003eDashboard Techniques 317\u003c\/p\u003e \u003cp\u003eText in a Chart 331\u003c\/p\u003e \u003cp\u003eThe Data Series Formula 332\u003c\/p\u003e \u003cp\u003eBefore and After Charts 333\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 13 Quality Control: Report Validation 337\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIdentifying Errors 337\u003c\/p\u003e \u003cp\u003eValidations 338\u003c\/p\u003e \u003cp\u003eError Tracking 340\u003c\/p\u003e \u003cp\u003eIdentifying New Codes 346\u003c\/p\u003e \u003cp\u003eConditional Formatting 347\u003c\/p\u003e \u003cp\u003eSuggested Validation Structure 347\u003c\/p\u003e \u003cp\u003eReasonableness Checks 349\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 14 Case Study One: Month and Year-to-Date Reporting 351\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eScenario 351\u003c\/p\u003e \u003cp\u003eData Requirements 352\u003c\/p\u003e \u003cp\u003eProcesses 352\u003c\/p\u003e \u003cp\u003eStructure 354\u003c\/p\u003e \u003cp\u003eDesign 354\u003c\/p\u003e \u003cp\u003eReport Layout 355\u003c\/p\u003e \u003cp\u003eThe Creation Process 355\u003c\/p\u003e \u003cp\u003eThe Reports 363\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 15 Case Study Two: 12-Month Reporting 379\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eScenario 379\u003c\/p\u003e \u003cp\u003eData Requirements 380\u003c\/p\u003e \u003cp\u003eProcesses 381\u003c\/p\u003e \u003cp\u003eStructure 381\u003c\/p\u003e \u003cp\u003eDesign 382\u003c\/p\u003e \u003cp\u003eThe Creation Process 382\u003c\/p\u003e \u003cp\u003eThe Reports 387\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 16 Final Thoughts 407\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eFeedback 408\u003c\/p\u003e \u003cp\u003eLast Words 408\u003c\/p\u003e \u003cp\u003eAbout the Author 409\u003c\/p\u003e \u003cp\u003eAbout the Companion Website 411\u003c\/p\u003e \u003cp\u003eIndex 413\u003c\/p\u003e \u003cp\u003e\u003cb\u003eNeale Blackwood\u003c\/b\u003e 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.\u003cbr\u003eAn 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.\u003cbr\u003eHe 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.\u003c\/p\u003e  \u003cp\u003e\u003cb\u003ePraise for \u003ci\u003eAdvanced Excel Reporting for Management Accountants\u003c\/i\u003e\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e“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.”\u003cbr\u003e \u003cb\u003e—John Walkenbach, Author of \u003ci\u003eExcel 2013 Bible\u003c\/i\u003e\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e“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.”\u003cbr\u003e \u003cb\u003e—Jeff Robson, CEO \u0026amp; Principal Business Analyst, Access Analytic\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e“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!”\u003cbr\u003e \u003cb\u003e—Danielle Stein Fairhurst, Financial Modelling Specialist \u0026amp; Presenter, Plum Solutions\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e“Neale is very well known for his practical Excel skills and knowledge. His new book, \u003ci\u003eAdvanced Excel Reporting for Management Accountants,\u003c\/i\u003e is highly recommended and will be useful to both accounting students and professionals alike.”\u003cbr\u003e \u003cb\u003e—William Beattie, CPA, Business \u0026amp; Finance Analyst\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e“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.”\u003cbr\u003e \u003cb\u003e—Eve Blackall, Smart Accounting – the Science of Business Success\u003c\/b\u003e\u003c\/p\u003e","brand":"Wiley","offers":[{"title":"Default Title","offer_id":47988666892517,"sku":"NP9781118657720","price":63.0,"currency_code":"USD","in_stock":false}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/1842\/7735\/files\/9781118657720.jpg?v=1761781186","url":"https:\/\/k12savings.com\/es\/products\/advanced-excel-reporting-for-management-accountants-isbn-9781118657720","provider":"K12savings","version":"1.0","type":"link"}