Introduction .................................................................................................... 1
Taking It on Faith ............................................................................................................................ 2
Renamed and Improved Functions in Excel 2010 ........................................................................... 3
Compatibility ............................................................................................................................ 4
Consistency ............................................................................................................................... 4
How This Book Is Organized ............................................................................................................ 6
Two Special Skills: Named Ranges and Array Formulas .................................................................. 6
Assigning Names ...................................................................................................................... 7
Using Array Formulas ............................................................................................................... 8
Conventions Used in This Book ....................................................................................................... 9
PART I ANALYZING FINANCIAL STATEMENTS
1 Working with Income Statements ...............................................................11
Keeping Score ............................................................................................................................... 11
Choosing the Right Perspective .............................................................................................. 12
Defining Two Purposes for Accounting ................................................................................... 12
Using the Income Statement ........................................................................................................ 13
Choosing a Reporting Method ................................................................................................ 14
Cells in Excel ........................................................................................................................... 15
Measuring the Operating and Nonoperating Segments ......................................................... 19
Moving from the General Journal to the Income Statement ......................................................... 20
Getting the General Journal into Excel .................................................................................... 20
Understanding Absolute, Relative, and Mixed References ...................................................... 22
Getting the Journal Data to the Ledger .................................................................................. 23
Getting the Ledger Data to the Income Statement ................................................................. 27
Managing the Financial Analyses with Accrual Accounting .......................................................... 28
Using Straight-Line Depreciation............................................................................................ 32
Preparing the Trial Balance .................................................................................................... 33
Moving Information into an Income Statement ..................................................................... 33
Organizing with Traditional Versus Contribution Approaches ...................................................... 34
2 Balance Sheet: Current Assets .....................................................................37
Designing the Balance Sheet ........................................................................................................ 38
Understanding Balance Sheet Accounts ................................................................................. 38
Understanding Debit and Credit Entries ................................................................................. 39
Getting a Current Asset Cash Balance ........................................................................................... 41
Using Sheet-Level Names ....................................................................................................... 41
Getting a Cash Balance for Multiple Cash Accounts ................................................................ 44
Handling Restricted Cash Accounts ......................................................................................... 46
Getting a Current Asset Accounts Receivable Balance ................................................................... 46
Allowing for Doubtful Accounts .............................................................................................. 47
Using the Aging Approach to Estimating Uncollectibles ......................................................... 48
Using the Percentage of Sales Approach to Estimating Uncollectibles ................................... 51
Getting a Prepaid Expenses Balance ............................................................................................. 52
Dealing with Insurance as a Prepaid Expense ......................................................................... 53
Getting a Current Asset Balance.................................................................................................... 54
Understanding the Inventory Flow ......................................................................................... 55
Closing the Inventory Account ................................................................................................ 56
Closing the Revenue and Expense Accounts ........................................................................... 56
3 Valuing Inventories for the Balance Sheet ...................................................59
Understanding Perpetual and Periodic Inventory Systems ........................................................... 60
Perpetual Inventory Systems .................................................................................................. 61
Periodic Inventory Systems .................................................................................................... 62
Valuing Inventories ....................................................................................................................... 64
Valuation Methods Summarized ............................................................................................ 64
Using Specific Identification ................................................................................................... 66
Using Average Cost ................................................................................................................. 72
Using the Moving Average Method ........................................................................................ 77
Calculating the Moving Average and Weighted Average ....................................................... 79
Using FIFO .............................................................................................................................. 81
Using LIFO .............................................................................................................................. 87
Comparing the Four Valuation Methods ....................................................................................... 89
Specification Identification ..................................................................................................... 89
Average Cost ........................................................................................................................... 89
FIFO ....................................................................................................................................... 90
LIFO ....................................................................................................................................... 90
Handling Purchase Discounts ........................................................................................................ 91
Calculating Turns Ratios ................................................................................................................ 92
4 Summarizing Transactions: From the Journals to the Balance Sheet .............95
Understanding Journals ................................................................................................................ 97
Understanding Special Journals .............................................................................................. 98
Structuring the Special Sales Journal ...................................................................................... 98
Structuring the Special Purchases Journal ............................................................................ 100
Structuring the Cash Receipts Journal .................................................................................. 101
Structuring the Cash Payments Journal ................................................................................ 103
Excel Tables and Dynamic Range Names .................................................................................... 104
Building Dynamic Range Names ........................................................................................... 106
Using Dynamic Range Names in the Journals ....................................................................... 108
Choosing Between Tables and Dynamic Range Names ......................................................... 108
Understanding Ledgers............................................................................................................... 110
Creating the General Ledger ................................................................................................. 110
Creating Subsidiary Ledgers ................................................................................................. 112
Automating the Posting Process........................................................................................... 113
Getting a Current Liabilities Balance ........................................................................................... 121
5 Working Capital and Cash Flow Analysis ....................................................123
Matching Costs and Revenues .................................................................................................... 123
Broadening the Definition: Cash Versus Working Capital............................................................ 125
Determining the Amount of Working Capital ....................................................................... 126
Determining Changes in Working Capital ............................................................................. 133
Analyzing Cash Flow ................................................................................................................... 137
Developing the Basic Information ........................................................................................ 138
Summarizing the Sources and Uses of Working Capital ........................................................ 140
Identifying Cash Flows Due to Operating Activities .............................................................. 141
Combining Cash from Operations with Cash from Nonoperating Transactions ..................... 142
6 Statement Analysis ..................................................................................145
Understanding a Report by Means of Common-Sizing ............................................................... 146
Using Common-Sized Income Statements............................................................................ 146
Using Common-Sized Balance Sheets ................................................................................... 148
Using Comparative Financial Statements ............................................................................. 149
Using Dollar and Percent Changes in Statement Analysis ........................................................... 152
Assessing the Financial Statements ...................................................................................... 152
Handling Error Values ........................................................................................................... 154
Evaluating Percentage Changes ........................................................................................... 155
Common-Sizing and Comparative Analyses in Other Applications ............................................. 156
Working in Excel with a Profit & Loss from QuickBooks ........................................................ 156
Working in Excel with a QuickBooks Balance Sheet .............................................................. 158
Common-Sizing for Variance Analysis ........................................................................................ 160
Ratio to Ratio Comparisons .................................................................................................. 163
Common-Sizing by Headcount ................................................................................................... 164
7 Ratio Analysis ..........................................................................................169
Interpreting Industry Averages and Trends ................................................................................ 170
Comparing Ratios Within Industries ........................................................................................... 171
Analyzing Ratios Vertically and Horizontally ........................................................................ 172
Getting a Basis for Ratios ...................................................................................................... 173
Analyzing Profitability Ratios ...................................................................................................... 176
Finding and Evaluating Earnings Per Share .......................................................................... 176
Determining Gross Profit Margin .......................................................................................... 177
Determining Net Profit Margin ............................................................................................. 179
Determining the Return on Assets ........................................................................................ 180
Determining the Return on Equity ........................................................................................ 182
Analyzing Leverage Ratios .......................................................................................................... 183
Determining the Debt Ratio.................................................................................................. 184
Determining the Equity Ratio ............................................................................................... 184
Determining the Times Interest Earned Ratio ....................................................................... 185
Analyzing Liquidity Ratios .......................................................................................................... 186
Determining the Current Ratio ............................................................................................. 186
Determining the Quick Ratio ................................................................................................ 187
Analyzing Activity Ratios ............................................................................................................ 188
Determining the Average Collection Period .......................................................................... 188
Determining Inventory Turnover .......................................................................................... 190
PART II FINANCIAL PLANNING AND CONTROL
8 Budgeting and Planning Cycle ..................................................................191
Creating Pro Forma Financial Statements ................................................................................... 191
Forecasting by Percentage of Sales....................................................................................... 193
Using Excel to Manage the Analysis ............................................................................................ 199
Performing Sensitivity Analysis ............................................................................................ 200
Moving from the Pro Forma to the Budget ................................................................................. 201
Projecting Quarterly Sales..................................................................................................... 201
Estimating Inventory Levels ................................................................................................. 202
Fitting the Budget to the Business Plan................................................................................ 205
9 Forecasting and Projections ......................................................................207
Making Sure You Have a Useful Baseline .................................................................................... 208
Moving Average Forecasts .......................................................................................................... 210
Creating Forecasts with the Moving Average Add-In ........................................................... 212
Dealing with the Layout of Excel's Moving Averages ............................................................ 213
Creating Moving Average Forecasts with Excel's Charts ....................................................... 215
Forecasting with Excel's Regression Functions ........................................................................... 216
Making Linear Forecasts: The TREND Function ..................................................................... 217
Making Nonlinear Forecasts: The GROWTH Function ............................................................ 220
Creating Regression Forecasts with Excel's Charts ................................................................ 223
Forecasting with Excel's Smoothing Functions ........................................................................... 225
Projecting with Smoothing................................................................................................... 225
Using the Exponential Smoothing Add-In ............................................................................ 226
Choosing a Smoothing Constant........................................................................................... 228
Making Smoothed Forecasts Handle Seasonal Data ............................................................. 229
Using the Box-Jenkins ARIMA Approach: When Excel's Built-In Functions Won't Do ................. 234
Understanding ARIMA Basics ................................................................................................ 234
Charting the Correlograms ................................................................................................... 235
Starting with Correlograms to Identify a Model ................................................................... 236
Identifying Other Box-Jenkins Models .................................................................................. 237
10 Measuring Quality ....................................................................................241
Monitoring Quality Through Statistical Process Control .............................................................. 242
Using Averages from Samples .............................................................................................. 242
Using X-and-S Charts for Variables ....................................................................................... 243
Interpreting the Control Limits ............................................................................................. 247
Manufacturing ...................................................................................................................... 247
Using P-Charts for Dichotomies ............................................................................................ 251
Choosing the Sample Size ..................................................................................................... 253
Determining That a Process Is Out of Control ....................................................................... 255
Using X-and-MR Charts for Individual Observations ............................................................. 258
Creating SPC Charts Using Excel ............................................................................................ 259
Performing Acceptance Sampling ............................................................................................... 262
Charting the Operating Characteristic Curve ......................................................................... 263
Using Worksheet Functions for Quality Control .......................................................................... 268
Sampling Units from a Finite Population .............................................................................. 269
Using HYPGEOM.DIST in Excel 2010 .................................................................................. 270
Sampling Units from a Nonfinite Population .............................................................................. 271
Using NORMSDIST to Approximate BINOMDIST ............................................................... 271
Sampling Defects in Units ..................................................................................................... 277
Using the CRITBINOM Function.......................................................................................... 279
PART III INVESTMENT DECISIONS
11 Examining a Business Case: Investment ....................................................285
Developing a Business Case ........................................................................................................ 286
Getting Consensus for the Plan ............................................................................................. 286
Showing Your Work .............................................................................................................. 288
Developing the Excel Model ........................................................................................................ 289
Developing the Inputs .......................................................................................................... 290
Identifying the Costs ............................................................................................................. 292
Moving to the Pro Forma ...................................................................................................... 293
Preparing the Cash Flow Analysis ......................................................................................... 296
12 Examining Decision Criteria for a Business Case .........................................299
Understanding Payback Periods ................................................................................................. 300
Understanding Future Value, Present Value, and Net Present Value .......................................... 304
Calculating Future Value....................................................................................................... 305
Calculating Present Value ..................................................................................................... 305
Calculating Net Present Value .............................................................................................. 306
Optimizing Costs ................................................................................................................... 308
13 Creating a Sensitivity Analysis for a Business Case .....................................315
Reviewing the Business Case ...................................................................................................... 315
Managing Scenarios ................................................................................................................... 316
Saving a Scenario for the Base Case ...................................................................................... 318
Developing Alternative Scenarios ......................................................................................... 320
Developing Scenarios That Vary Expenses ............................................................................ 323
Summarizing the Scenarios .................................................................................................. 324
Measuring Profit ......................................................................................................................... 325
Calculating Internal Rate of Return....................................................................................... 325
Calculating Profitability Indexes ........................................................................................... 327
Estimating the Continuing Value .......................................................................................... 327
Varying the Discount Rate Input ................................................................................................. 330
Using the Goal Seek Tool ............................................................................................................ 332
14 Planning Profits .......................................................................................335
Understanding the Effects of Leverage ....................................................................................... 335
The Effect of Business Risk .................................................................................................... 336
Analyzing Operating Leverage .................................................................................................... 337
Evaluating the Financial Implications of an Operational Change .......................................... 338
Evaluating Fixed Expenses .................................................................................................... 339
Evaluating Effect of Increasing Fixed Costs ........................................................................... 345
Planning by Using the DOL ................................................................................................... 347
Analyzing Financial Leverage ..................................................................................................... 348
Distinguishing Business from Financial Risk ......................................................................... 348
Determining the Debt Ratio.................................................................................................. 349
Determining the Times Interest Earned Ratio ....................................................................... 350
15 Making Investment Decisions Under
Uncertain Conditions .............................................................................353
Using Standard Deviations .......................................................................................................... 354
Using Excel's Standard Deviation Functions.......................................................................... 356
Understanding Confidence Intervals ........................................................................................... 357
Using Confidence Intervals in a Market Research Situation .................................................. 358
Calculating a Confidence Interval ......................................................................................... 359
Interpreting the Interval ....................................................................................................... 360
Refining Confidence Intervals ............................................................................................... 361
Using Regression Analysis in Decision Making ............................................................................ 362
Regressing One Variable onto Another ................................................................................. 362
Interpreting the Trendline .................................................................................................... 364
Avoiding Traps in Interpretation: Association Versus Causation ........................................... 367
Regressing One Variable onto Several Other Variables: Multiple Regression ........................ 368
Using Excel's Regression Add-In ........................................................................................... 373
Interpreting Regression Output ............................................................................................ 375
Estimating with Multiple Regression .................................................................................... 377
Using Excel's TREND Function ............................................................................................... 377
16 Fixed Assets .............................................................................................383
Determining Original Cost .......................................................................................................... 383
Determining Costs ................................................................................................................ 384
Choosing Between Actual Cost and Replacement Cost ......................................................... 385
Depreciating Assets .................................................................................................................... 386
Understanding the Concept of Depreciation ......................................................................... 387
Matching Revenues to Costs ................................................................................................. 387
Using Straight-Line Depreciation.......................................................................................... 389
Using the Declining Balance Method .................................................................................... 390
Using the Double Declining Balance Function to Calculate Depreciation .............................. 393
Using Variable Declining Balance Depreciation .................................................................... 395
Using Sum-of-Years'-Digits Depreciation ............................................................................. 397
PART IV SALES AND MARKETING
17 Importing Business Data into Excel ...........................................................399
Creating and Using ODBC Queries ............................................................................................... 400
Preparing to Import Data...................................................................................................... 401
Specifying Data Sources ........................................................................................................ 401
Creating Queries with the Query Wizard ............................................................................... 405
Creating Queries with Microsoft Query ................................................................................. 407
Creating Parameterized Queries in Microsoft Query ............................................................. 410
Using Joins in Microsoft Query .............................................................................................. 411
Working with External Data Ranges ........................................................................................... 412
Include Row Numbers .......................................................................................................... 412
Adjust Column Width ........................................................................................................... 412
Preserve Column Sort/Filter/Layout ..................................................................................... 413
Preserve Cell Formatting ...................................................................................................... 413
Insert Cells for New Data, Delete Unused Cells ..................................................................... 414
Insert Entire Rows for New Data. Clear Unused Cells ............................................................ 415
Overwrite Existing Cells with New Data, Clear Unused Cells ................................................. 416
Managing Security Information ............................................................................................ 416
Arranging Automatic Refreshes ............................................................................................ 418
Setting Other Data Range Options ........................................................................................ 419
Importing Data to Pivot Tables and Charts ........................................................................... 420
Creating and Using Web Queries ................................................................................................ 424
Using Parameterized Web Queries.............................................................................................. 426
18 Exporting Business Data from Excel ..........................................................429
Using VBA to Update an External Database ................................................................................ 429
Getting at VBA ...................................................................................................................... 430
Structuring the Worksheet ................................................................................................... 431
Establishing Command Buttons ............................................................................................ 432
Editing the Record's Values......................................................................................................... 433
Using Database Objects ........................................................................................................ 435
Using With Blocks ....................................................................................................................... 436
Finding the Right Record ...................................................................................................... 437
Editing the Record ................................................................................................................ 438
Adding New Records to the Recordset ........................................................................................ 439
Choosing to Use ADO .................................................................................................................. 442
Back Ends Perform Data Management ................................................................................. 442
19 Analyzing Contributions and Margins ........................................................445
Calculating the Contribution Margin........................................................................................... 446
Classifying Costs ................................................................................................................... 447
Estimating Semivariable Costs .............................................................................................. 448
Using Unit Contribution .............................................................................................................. 449
Producing Digital Video Discs (Continued) ........................................................................... 449
Increasing the Contribution Margin ...................................................................................... 450
Creating an Operating Income Statement ............................................................................ 451
Finding the Break-Even Point ..................................................................................................... 452
Calculating Break-Even in Units ............................................................................................ 453
Calculating Break-Even in Sales ............................................................................................ 453
Calculating Break-Even in Sales Dollars with a Specified Level of Profit ............................... 454
Charting the Break-Even Point ............................................................................................. 455
Choosing the Chart Type ....................................................................................................... 457
Making Assumptions in Contribution Analysis ............................................................................ 459
Linear Relationships ............................................................................................................. 459
Assignment of Costs ............................................................................................................. 460
Constant Sales Mix ............................................................................................................... 460
Worker Productivity .............................................................................................................. 461
Determining Sales Mix ................................................................................................................ 461
20 Pricing and Costing ..................................................................................465
Using Absorption and Contribution Costing ................................................................................ 466
Understanding Absorption Costing ....................................................................................... 466
Understanding Contribution Costing .................................................................................... 472
Applying the Contribution Approach to a Pricing Decision ................................................... 475
Using Contribution Analysis for New Products ............................................................................ 477
Allocating Expenses to Product Lines ................................................................................... 479
Varying the Inputs ................................................................................................................ 480
Estimating the Effect of Cross-Elasticity ...................................................................................... 481
Glossary .......................................................................................................485
TOC, 9780789743176, 5/12/10