Business Analysis

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