Business Analysis: Microsoft Excel 2010 (MrExcel Library)

✍ Scribed by Conrad Carlberg

• Year: 2010
• Tongue: English
• Leaves: 528
• Edition: 1
• Category: Library

✦ Synopsis

ANSWER KEY BUSINESS QUESTIONS CONTROL COMPANY FINANCES FORECAST SALES PREPARE BUSINESS CASES MAKE BETTER INVESTMENT DECISIONS IMPROVE QUALITY   USE EXCEL 2010 TO GAIN DEEPER INSIGHTS, MAKE SMARTER DECISIONS, AND EARN MORE PROFITS   Using real-world examples, Carlberg helps you put Excel’s features and functions to work and get the power of quantitative analysis behind your management decisions. Excel expert Conrad Carlberg shows how to use Excel 2010 to perform the core financial tasks every manager and entrepreneur must master: analyzing statements, planning and controlling company finances, making investment decisions, and managing sales and marketing. Using real-world examples, Carlberg helps you get the absolute most out of Excel 2010’s newest features and functions. Along the way, you’ll discover the fastest, best ways to handle essential tasks ranging from importing business data to analyzing profitability ratios. Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions, insider insights, complete step-by-step projects, and more. It’s all complemented by an extraordinary set of web-based resources, from sample journals and ledgers to business forecasting tools.   •  Use Excel analysis tools to solve problems throughout the business •  Build and work with income statements and balance sheets •  Value inventories and current assets, and summarize transactions •  Calculate working capital and analyze cash flows •  Move from pro formas to operating budgets that help guide your management decisions •  Prepare business cases incorporating everything from discount rates to margin and contribution analysis   About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel skills, and presents focused tasks and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:   •   Dramatically increase your productivity–saving you 50 hours a year, or more •   Present proven, creative strategies for solving real-world problems •   Show you how to get great results, no matter how much data you have •   Help you avoid critical mistakes that even experienced users make      

✦ Table of Contents

Cover......Page 0
Contents at a Glance......Page 2
Copyright......Page 3
Acknowledgments......Page 12
We Want to Hear from You !......Page 13
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