3쪽
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.
4쪽
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
5쪽
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 A Lengthy 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
6쪽
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 PMT Function ..................................................................................................193 Positive and Negative Values..........................................................................198 PV Function.....................................................................................................199
7쪽
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
8쪽
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
10쪽
Acknowledgments Cover Design: Matthew Ericson Production: InfoDesign Services (www.infodesigning.com) Indexing: After Words Editorial Services (www.aweditorial.com)
12쪽
From the Author As this book was written, in late 2006, I was still exploring a beta version of Excel 12, which is part of Office 2007. I realized that I had to make a crucial decision: whether or not the book's figures and instructions should take account of Excel 12's radically different user interface.After I regained my composure and got back on my chair, I saw that my decision required a sober explanation, and here it is. Excel 12 – along with the other Office 2007 applications – has dispensed with the menu structure that's been around since Excel was MultiPlan. The thinking at Microsoft was that there just had to be a more intuitive way of grouping tasks than the old menu structure. People were confused by looking for tasks that had to do with files in a File menu; tasks that had to do with editing in an Edit menu; and so on. So Microsoft came up with the Ribbon, a toolbar that stretches across the top of the worksheet. The Ribbon has several tabs, similar to the tabs often found at the top of a dialog box. In Excel's case, the standard tabs are Sheet, Insert, Page Layout, Formulas, Data, Review and View. These tabs organize tasks in a much more intu- itive fashion than the old menus. For example: To select a font, you use the Sheet tab. Or you use the Page Layout tab. It depends. If you want to name a cell or range, you use the Formulas tab. If you want to set Excel options such as whether to recalculate automatically or manually, you don't use a tab – you use something called the File button. We wanted to get this book published before the end of the current presidential administration, so I decided not to cover the interface changes introduced in Excel 12. I'm depending on the fact that most people who might read this book have clients who use an earlier version of Excel. (Many corporations still use Office 97, because it's a major undertaking to move to a more recent version in a networked environment, and the benefits of an upgrade don't clearly justify the costs.) But if you or your clients have acquired Excel 12, you'll still find this book use- ful for accounting matters. The tools, formulas and functions discussed here work in Excel 12 as they always have. You might have a more difficult time finding them in Excel 12, but I'm told they're all still there somewhere. Conrad Carlberg
14쪽
Download the Example Files You can download the Excel workbooks that contain the original data for all the examples and worksheet figures that appear in this book. The files are available for downloading at www.cpa911publishing.com. Click the Navigation Button labeled Downloads. The files are compatible with all versions of Excel from Office 97 through Office 2003. Excel 12 (in Office 2007) will also open the files but will want to save them in a new format, with a different extension; usually .xlsx or .xlsm.
16쪽
Chapter 1 Using Lists in Excel Defining a List Sorting Lists Filtering lists Managing a List Using the Data Form You put data into an Excel worksheet so that you can chart it, or analyze it, orget its total, or turn it into a report, or for any of a dozen different reasons. But you don't do it for fun. So if you're serious about it, you should know how to organize the data: how to lay it out, how to label it, when to keep it separate from other information, how to edit it, and so on. Defining a List Wearing my Excel consultant hat, I've seen some pretty strange ways of laying out data on a worksheet. Granted, the people who designed those worksheets had what they thought were pretty good reasons for their layouts. Sticking totaling rows into the middle of what is in effect a database probably seemed like a good idea at the time.
17쪽
2 Excel for AccountantsExcel for Accountants Still, we all encounter worksheets with strange arrangements of data. One that you've probably run across occurs when someone pastes an existing report from an accounting package into a worksheet. A layout that works well for a report can be spectacularly useless as the basis for an analysis or a chart. See Figure 1-1 for an example. Figure 1-1 Excel doesn't know where to find the values that it should use. Suppose you wanted Excel to show you the total of the figures in Column C for Towels plus those for Tablecloths in the Northwest region. You'd have to create a formula like this one: =C3+C11+C20+C15 You find yourself pointing-and-clicking at cells and ranges instead of using something quick and simple like this: =SUM(C2:C6)
18쪽
Chapter 1 • Using Lists in ExcelChapter 1 • Using Lists in Excel 3 List Layout But you can use a simple formula like that one if you've set up your figures properly — and in Excel, that usually means in the form of a list. Figure 1-2 shows what an Excel list looks like. Figure 1-2: This layout makes it much easier to do something as simple as totaling. The data you see in Figure 1-2 is arranged so that it's easy to total. For exam- ple, to get the total of the sales dollars, just type this in a blank cell outside column D: =SUM( Then, click the D at the top of column D, and press enter. Your formula will now look like this: =SUM(D:D) NOTE: Don’t enter the formula itself in Column D because you'll get a circular reference error: the result of the formula would depend on the formula itself.
19쪽
4 Excel for Accountants NOTE: SUM(D:D) is a quick-and-dirty formula. If you have an extraneous numeric value somewhere else in column D, it will be part of the total that's calculated by the SUM function. It's usually better to specify the cells, not just the column: for example: SUM(D2:D55) to get the total of the values in cells D2 through D55. It happens that the arrangement of data shown in Figure 1-2 conforms to the requirements of an Excel list: • Each row represents a different transaction. (In this case, anyway. You could also use the list to keep track of your kangaroos; in that case, each row would represent a different kangaroo.) • Each column represents a different variable (or field, which is just another term for the information you're putting in the column). • Each column is headed by the name of the variable. That's it. If your data conforms to the requirements bulleted above, you have a list. And Excel agrees with you. What does a list buy you? Quite a bit, in fact: • Some things that you'd like to use Excel for just can't be done with- out data structured as a list; this chapter discusses a couple of exam- ples and you'll find more in later chapters. • Other things can be done without a list, but only with more effort, sometimes a lot more effort. The SUM situation discussed in the prior section is a good example. Still, a list is not really a formal structure in Excel. An Excel workbook has all kinds of formal structures — things that you use and manipulate — such as work- sheets, charts, menus, cells, rows and so on. These structures are built into what's called the Excel object model. NOTE: I won't have much to say about the Excel object model in this book. It's only a little less complicated than a detailed outline of FASB regulations.
20쪽
Chapter 1 • Using Lists in Excel 5 And you won't find an object like a list in that object model. It's informal, and yet something with a list structure is needed if you want to: • Create a pivot table or pivot chart • Use the Data Form • Use Excel's AutoFilter • Use tools like Histogram and Moving Average • Copy data from a worksheet and paste it into a true database (and vice versa) • Look up a value in a table (for example, a commission rate based on product sold and revenue recognized) …as well as do other Excel tasks. So if you set up your data as described in this section, you'll make your life with Excel a lot easier — starting with sorting the data. Sorting Lists Excel enables you to sort on up to three keys simultaneously. This means that you might sort a company's costs first on cost center, then on vendor within cost center, and then on invoice date within cost center and vendor. Each of these (in the exam- ple, cost center, vendor and date) is what is usually termed a key. Sorting on One Key More frequently, though, you find yourself sorting on just one key. The next few fig- ures show how this works. The list in Figure 1-2 started out sorted by Region, then by Product, and then by Date Sold. Suppose you wanted to change the sorting priority, and sort the informa- tion solely by Date Sold. You can ignore Region and Product, and get your date-only sort by taking these steps: 1. Select any cell in the Date Sold column. (This step isn't a necessary one, but it can make things easier in Step 3.) 2. Choose Sort from the Data menu. The dialog box shown in Figure 1-3 appears.
앞 20페이지만 표시됩니다. 전체를 보려면 자료를 다운로드하세요.
댓글 (0)
댓글이 없습니다. 첫 댓글을 남겨보세요!