Excel for Accountants: Tips, Tricks & Techniques

289 Pages·2007·7.21 MB·English

by  Conrad Carlberg

®
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)