Excel for Accountants: Tips, Tricks & Techniques PDF
Preview Excel for Accountants: Tips, Tricks & Techniques
® Excel for Accountants Conrad Carlberg CPA911 Publishing, LLC Philadelphia PA Excel for Accountants ISBN Number 978-1-932925-01-2 Published by CPA911 Publishing, LLC 2007 Copyright 2007 CPA911 Publishing, LLC CPA911 is a Registered Trademark of CPA911 Publishing, LLC. All rights reserved. No portion of the contents of this book may be reproduced in any form or by any means without the written permission of the publisher. The publisher and author have used their best efforts to make sure the information in this book is reliable and complete. They make no representations or warranties for the accuracy or completeness of the contents of this book, and specifically disclaim any implied warranties. The publisher and author disclaim any responsibility for errors or omissions or the results obtained from the use of the information in this book. Windows and Microsoft Excel are registered trademarks of Microsoft Corporation, and are used with permission. Screen shots reprinted by permission from Microsoft Corporation. QuickBooks is a registered trademark of Intuit Inc., and is used with permission. Table of Contents Acknowledgments....................................................................................ix From the Author........................................................................................xi Download the Example Files..................................................................xiii Chapter 1: Using Lists in Excel..........................................................1 Defining a List...........................................................................................1 List Layout...........................................................................................................3 Sorting Lists..............................................................................................5 Sorting on One Key.............................................................................................5 Sorting on Multiple Keys.....................................................................................7 Unsorting a List...................................................................................................9 Filtering Lists...........................................................................................11 AutoFiltering Excel Lists....................................................................................11 Using AutoFilter Options...................................................................................13 Entr'acte: Deciding to Use AutoFilter................................................................16 Using the Custom Criteria.................................................................................16 Using Advanced Filters Instead of AutoFilter....................................................18 Getting More from Advanced Filter...................................................................18 Complex Criteria...............................................................................................22 Managing a List.......................................................................................23 Handling the Total Row.....................................................................................25 Using the Totals.................................................................................................26 Totals in the Status Bar.....................................................................................27 Getting Rid of a List..........................................................................................28 Using the Data Form...............................................................................29 Chapter 2: Pivot Tables.....................................................................33 The Purpose of Pivot Tables...................................................................33 Getting Data Summaries.........................................................................35 Data Summaries................................................................................................35 Row Fields........................................................................................................37 Column Fields...................................................................................................45 Page Fields.......................................................................................................46 iv Excel for Accountants Building Pivot Tables...............................................................................48 Creating Multiple Row Fields............................................................................51 Dealing with Subtotals.......................................................................................52 Grouping Numeric Fields..................................................................................53 Too Much Information: The Data Cache.................................................57 What's in the Cache?........................................................................................57 How the Cache Helps.......................................................................................58 How the Cache Hurts........................................................................................61 Refreshing the Cache Automatically.................................................................62 Using Named Ranges as Data Sources.................................................65 Creating a Named Range for an Aging Report.................................................65 Static and Dynamic Named Ranges.................................................................68 Building and Refreshing a Pivot Table From a Dynamic Range.......................75 Chapter 3: Common Sizing Using Worksheets..............................79 The Rationale for Common Sizing..........................................................79 Common Sizing Income Statements.......................................................81 The Mechanics: Using Formulas......................................................................81 The Mechanics: Using Values...........................................................................89 ALengthy Tip....................................................................................................96 Other Uses of Common Sizing...............................................................98 Chapter 4: Charting.........................................................................101 Why Charts?.........................................................................................101 About Standard Charts....................................................................................101 About Pivot Charts..........................................................................................104 Charts that Show Time Series..............................................................105 Pivot Charts vs. Standard Charts..........................................................110 Charting a Pivot Table's Clone........................................................................114 Using the GetPivotData function.....................................................................115 Budget Variances Over Time................................................................117 Problems with a Two-dimensional User Interface...........................................117 Designing the Pivot Table and Pivot Chart......................................................119 Chapter 5: Tools for Accountants..................................................121 What Tools do Accountants Need?.......................................................121 Built-in Lists...........................................................................................122 Table of Contents v Building Custom Lists...........................................................................123 Managing Custom Lists...................................................................................125 Creating Complex Lists...................................................................................126 Using the Macro Recorder....................................................................130 Recording a Macro..........................................................................................130 Running a Macro ............................................................................................132 Editing a Macro...............................................................................................133 Advanced Macro Modifications.......................................................................136 Using Keyboard Shortcuts....................................................................136 Copying, Cutting and Pasting.........................................................................137 Chapter 6: Scenarios In Excel........................................................143 About Scenarios....................................................................................144 Scenarios and Multiple Inputs.........................................................................144 Scenarios and Worksheets.............................................................................145 Creating Scenarios...............................................................................148 Defining an Implicit Intersection............................................................153 More Help from the Scenario Manager................................................154 The Comments Box........................................................................................155 Protecting Scenarios.......................................................................................155 Protected and Locked Cells............................................................................157 Preventing Changes to Scenarios..................................................................158 Miscellaneous Actions with Scenarios............................................................160 Summarizing Scenarios..................................................................................164 Grinding It Out with Goal Seek and Solver...........................................167 Using Goal Seek.............................................................................................168 More Complicated Problems: Using the Solver..............................................171 Wrapping Up Scenarios........................................................................181 Chapter 7: Payment Functions.......................................................183 About Functions....................................................................................184 Using a Function.............................................................................................184 Using the Function Wizard..............................................................................187 Payment Functions...............................................................................192 PMTFunction..................................................................................................193 Positive and Negative Values..........................................................................198 PV Function.....................................................................................................199 vi Excel for Accountants NPER Function...............................................................................................201 RATE Function................................................................................................201 Chapter 8: Excel's Depreciation Functions ..................................203 Family of Depreciation Functions.........................................................204 SLN Function........................................................................................205 Accelerated Depreciation......................................................................207 SYD Function..................................................................................................207 DB Function....................................................................................................208 Supplying a Salvage Value.............................................................................213 DDB Function..................................................................................................216 VDB Function..................................................................................................222 Chapter 9: Excel and QuickBooks.................................................227 About IIF Import Files...........................................................................227 Format of an IIF File.............................................................................228 Exporting Data into an IIF File..............................................................229 Saving the IIF File in Excel.............................................................................229 Opening the IIF File in Excel...........................................................................230 Creating Multiple Lists in One IIF File...................................................231 Importing an IIF File..............................................................................231 IIF File Keywords for Lists....................................................................232 Profile Lists Import Files.......................................................................232 Customer Type List Import File.......................................................................233 Vendor Type List Import File...........................................................................233 Job Type List Import File.................................................................................233 Sales Rep List Import File...............................................................................233 Ship Method List Import File...........................................................................234 Terms List Import File......................................................................................234 Standard Lists Import Files...................................................................235 Chart of Accounts Import File..........................................................................235 Customers & Jobs List Import File..................................................................237 Vendor List Import File....................................................................................241 Items List Import File.......................................................................................242 Employee List Import File...............................................................................243 Other Names List Import File..........................................................................244 Price Level List Import File..............................................................................244 Table of Contents vii Sales Tax Code List Import File......................................................................245 Class List Import File......................................................................................246 Summary of List Headers.....................................................................246 Updating Lists with Excel Import Files..................................................246 Creating Import Files to Update Existing Lists................................................248 Adding and Modifying Data.............................................................................249 Working with Custom Fields............................................................................251 Saving the Import File.....................................................................................252 Importing Updated Data into QuickBooks.......................................................253 Importing Adjusting Entries...................................................................253 Creating a Journal Entry Import File...............................................................254 Adding Accounts for Journal Entries...............................................................256 Importing Auto Reversing JEs.........................................................................257 Canceling an Outstanding Accountant’s Copy................................................257 Appendix A: From the Web to Excel .............................................259 Why Bother?.........................................................................................259 Establishing a Connection....................................................................261 Finding the Data....................................................................................263 Understanding the Macro......................................................................264 Index .................................................................................................269 This page intentionally left blank Acknowledgments Cover Design: Matthew Ericson Production: InfoDesign Services (www.infodesigning.com) Indexing: After Words Editorial Services (www.aweditorial.com)
Description:The list of books you might like
