Business Analysis Microsoft Excel 2010

Contents......Page 4
Introduction......Page 14
Taking It on Faith......Page 15
Renamed and Improved Functions in Excel 2010......Page 16
Consistency......Page 17
Two Special Skills: Named Ranges and Array Formulas......Page 19
Assigning Names......Page 20
Using Array Formulas......Page 21
Conventions Used in This Book......Page 22
Keeping Score......Page 24
Using the Income Statement......Page 26
Moving from the General Journal to the Income Statement......Page 33
Managing the Financial Analyses with Accrual Accounting......Page 41
Organizing with Traditional Versus Contribution Approaches......Page 47
2 Balance Sheet: Current Assets......Page 50
Designing the Balance Sheet......Page 51
Getting a Current Asset Cash Balance......Page 54
Getting a Current Asset Accounts Receivable Balance......Page 59
Getting a Prepaid Expenses Balance......Page 65
Getting a Current Asset Balance......Page 67
3 Valuing Inventories for the Balance Sheet......Page 72
Understanding Perpetual and Periodic Inventory Systems......Page 73
Valuing Inventories......Page 77
Comparing the Four Valuation Methods......Page 102
Handling Purchase Discounts......Page 104
Calculating Turns Ratios......Page 105
4 Summarizing Transactions: From the Journals to the Balance Sheet......Page 108
Understanding Journals......Page 110
Excel Tables and Dynamic Range Names......Page 117
Understanding Ledgers......Page 123
Getting a Current Liabilities Balance......Page 134
Matching Costs and Revenues......Page 136
Broadening the Definition: Cash Versus Working Capital......Page 138
Analyzing Cash Flow......Page 150
6 Statement Analysis......Page 158
Understanding a Report by Means of Common-Sizing......Page 159
Using Dollar and Percent Changes in Statement Analysis......Page 165
Common-Sizing and Comparative Analyses in Other Applications......Page 169
Common-Sizing for Variance Analysis......Page 173
Common-Sizing by Headcount......Page 177
7 Ratio Analysis......Page 182
Interpreting Industry Averages and Trends......Page 183
Comparing Ratios Within Industries......Page 184
Analyzing Profitability Ratios......Page 189
Analyzing Leverage Ratios......Page 196
Analyzing Liquidity Ratios......Page 199
Analyzing Activity Ratios......Page 201
Creating Pro Forma Financial Statements......Page 204
Using Excel to Manage the Analysis......Page 212
Moving from the Pro Forma to the Budget......Page 214
9 Forecasting and Projections......Page 220
Making Sure You Have a Useful Baseline......Page 221
Moving Average Forecasts......Page 223
Forecasting with Excel’s Regression Functions......Page 229
Forecasting with Excel’s Smoothing Functions......Page 238
Using the Box-Jenkins ARIMA Approach: When Excel’s Built-In Functions Won’t Do......Page 247
10 Measuring Quality......Page 254
Monitoring Quality Through Statistical Process Control......Page 255
Performing Acceptance Sampling......Page 275
Using Worksheet Functions for Quality Control......Page 281
Sampling Units from a Nonfinite Population......Page 284
11 Examining a Business Case: Investment......Page 298
Developing a Business Case......Page 299
Developing the Excel Model......Page 302
12 Examining Decision Criteria for a Business Case......Page 312
Understanding Payback Periods......Page 313
Understanding Future Value, Present Value, and Net Present Value......Page 317
Reviewing the Business Case......Page 328
Managing Scenarios......Page 329
Measuring Profit......Page 338
Varying the Discount Rate Input......Page 343
Using the Goal Seek Tool......Page 345
Understanding the Effects of Leverage......Page 348
Analyzing Operating Leverage......Page 350
Analyzing Financial Leverage......Page 361
15 Making Investment Decisions Under Uncertain Conditions......Page 366
Using Standard Deviations......Page 367
Understanding Confidence Intervals......Page 370
Using Regression Analysis in Decision Making......Page 375
Determining Original Cost......Page 396
Depreciating Assets......Page 399
17 Importing Business Data into Excel......Page 412
Creating and Using ODBC Queries......Page 413
Working with External Data Ranges......Page 425
Creating and Using Web Queries......Page 437
Using Parameterized Web Queries......Page 439
Using VBA to Update an External Database......Page 442
Editing the Record’s Values......Page 446
Using With Blocks......Page 449
Adding New Records to the Recordset......Page 452
Choosing to Use ADO......Page 455
19 Analyzing Contributions and Margins......Page 458
Calculating the Contribution Margin......Page 459
Using Unit Contribution......Page 462
Finding the Break-Even Point......Page 465
Making Assumptions in Contribution Analysis......Page 472
Determining Sales Mix......Page 474
20 Pricing and Costing......Page 478
Using Absorption and Contribution Costing......Page 479
Using Contribution Analysis for New Products......Page 490
Estimating the Effect of Cross-Elasticity......Page 494
A......Page 498
C......Page 500
D......Page 502
E......Page 503
G......Page 504
L......Page 505
M......Page 506
O......Page 507
P......Page 508
R......Page 509
S......Page 511
U......Page 513
W......Page 514
A......Page 516
C......Page 517
D......Page 519
E......Page 520
I......Page 521
L......Page 522
O......Page 523
P......Page 524
R......Page 525
S......Page 526
V......Page 527
X-Y-Z......Page 528