Business Analysis

Conrad Carlberg is president of Network Control Systems, Inc., a software-development and consulting firm that specializes in statistical and database applications, and Beyond the Ledgers, a joint venture that develops and markets software coupling Excel to QuickBooks™. He holds a Ph.D. in statistics and is a many-time recipient of Microsoft’s Most Valuable Professional Award. He lives near San Diego.

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