Carlberg C. Excel for Accountants: Tips, Tricks and Techniques

CPA911 Publishing, 2007. - 289 p.Рассматривается версия MS Excel 2007.Accounting professionals learn how to get the information they need fast with this guide to Excel features that manipulate and sort financial data. Comprehensive but concise chapters explain how to automate the entry of common business formulas; how to use pivot tables to extract details; how to develop inventory, depreciation, and financial summaries; and how to set up other standard financial calculations.From the Author
Download the Example FilesiUsing Lists in Excel
Defining a List.
List Layout.
Sorting Lists
Sorting on One Key.
Sorting on Multiple Keys.
Unsorting a List.
Filtering Lists.
AutoFiltering Excel Lists
Using AutoFilter Options.
Entr'acte: Deciding to Use AutoFilter
Using the Custom Criteria.
Using Advanced Filters Instead of AutoFilter
Getting More from Advanced Filter.
Complex Criteria.
Managing a List.
Handling the Total Row.
Using the Totals.
Totals in the Status Bar.
Getting Rid of a List
Using the Data Form.
Pivot Tables.
The Purpose of Pivot Tables.
Getting Data Summaries.
Data Summaries
Row Fields
Column Fields.
Page Fields.
Building Pivot Tables.
Creating Multiple Row Fields
Dealing with Subtotals.
Grouping Numeric Fields
Too Much Information: The Data Cache.
What's in the Cache?
How the Cache Helps.
How the Cache Hurts
Refreshing the Cache Automatically.
Using Named Ranges as Data Sources.
Creating a Named Range for an Aging Report.
Static and Dynamic Named Ranges.
Building and Refreshing a Pivot Table From a Dynamic Range.Common Sizing Using Worksheets
The Rationale for Common Sizing
Common Sizing Income Statements.
The Mechanics: Using Formulas
The Mechanics: Using Values.
A Lengthy Tip
Other Uses of Common Sizing.Charting.
Why Charts?
About Standard Charts
About Pivot Charts
Charts that Show Time Series
Pivot Charts vs. Standard Charts
Charting a Pivot Table's Clone
Using the GetPivotData function.
Budget Variances Over Time
Problems with a Two-dimensional User Interface.
Designing the Pivot Table and Pivot ChartTools for Accountants
What Tools do Accountants Need?
Built-in Lists.
Building Custom Lists.
Managing Custom Lists.
Creating Complex Lists.
Using the Macro Recorder
Recording a Macro
Running a Macro
Editing a Macro.
Advanced Macro Modifications.
Using Keyboard Shortcuts
Copying, Cutting and Pasting.Scenarios In Excel
About Scenarios
Scenarios and Multiple Inputs.
Scenarios and Worksheets.
Creating Scenarios.
Defining an Implicit Intersection
More Help from the Scenario Manager
The Comments Box
Protecting Scenarios.
Protected and Locked Cells
Preventing Changes to Scenarios
Miscellaneous Actions with Scenarios
Summarizing Scenarios
Grinding It Out with Goal Seek and Solver.
Using Goal Seek.
More Complicated Problems: Using the Solver
Wrapping Up ScenariosPayment Functions.
About Functions
Using a Function.
Using the Function Wizard
Payment Functions.
PMT Function
Positive and Negative Values
PV Function.
Table of Contents v
NPER Function.
RATE FunctionExcel's Depreciation Functions
Family of Depreciation Functions.
SLN Function
Accelerated Depreciation
SYD Function
DB Function
Supplying a Salvage Value.
DDB Function
VDB FunctionExcel and QuickBooks.
About IIF Import Files.
Format of an IIF File.
Exporting Data into an IIF File
Saving the IIF File in Excel.
Opening the IIF File in Excel.
Creating Multiple Lists in One IIF File.
Importing an IIF File
IIF File Keywords for Lists
Profile Lists Import Files.
Customer Type List Import File.
Vendor Type List Import File.
Job Type List Import File.
Sales Rep List Import File.
Ship Method List Import File.
Terms List Import File
Standard Lists Import Files.
Chart of Accounts Import File
Customers & Jobs List Import File
Vendor List Import File
Items List Import File.
Employee List Import File.
Other Names List Import File
Price Level List Import File
Sales Tax Code List Import File
Class List Import File
Summary of List Headers.
Updating Lists with Excel Import Files
Creating Import Files to Update Existing Lists
Adding and Modifying Data.
Working with Custom Fields
Saving the Import File.
Importing Updated Data into QuickBooks.
Importing Adjusting Entries.
Creating a Journal Entry Import File.
Adding Accounts for Journal Entries.
Importing Auto Reversing JEs.
Canceling an Outstanding Accountant’s CopyAppendix A: From the Web to Excel.
Why Bother?
Establishing a Connection
Finding the Data
Understanding the Macro