Whether you own a small business or work for a large corporation-whether you are looking for help making financial and business decisions-this book is for you. Business Analysis with Microsoft (R) Excel, Third Edition, provides in-depth information that will streamline your use of the tools within Excel. Professional advice and guidance from an experienced author provide the answers to your most pressing questions:
* What's the relationship between my cost of goods sold and my inventory?
* How do I get Excel to keep these values up-to-date on my income statement and my balance sheet?
* I have to track service quality over time. How can I automate that using Excel charts?
* How can I forecast future demand for my products, based on prior sales results?
* What's the difference between financial leverage and operating leverage? How do I calculate them using my financial records?
* I need to project my financials for next year. Does Excel have a tool for that? What do I need to know to use it effectively?
* How do I do all these things using the new Ribbon in Excel 2007?
* What are the best ways to automate a connection between Excel and an external database?
Category Spreadsheets
Covers Applicable for versions of Microsoft Excel 97 to 2007
User Level Intermediate-Advanced
More great stuff... is just a click away!
* Sample journals and ledgers; examples of trial balances, income statements, and balance sheets
* Custom functions, such as FIFO and LIFO, for inventory management
* VBA routines that automate the creation of forecasts, quality control charts, and sales and marketing analysis
* Statistical process control charts: P charts, X-and-S charts, X-and-MR charts
* Forecasting tools: Seasonal smoothing and ARIMA model identification
quote from the front cover
"Conrad takes the time to give the readers an easy-to-follow step-by-step understanding of the material, accompanied by clear illustrations, making this an excellent book to learn the material. I recommend this book to anyone wanting to gain more expertise in using and manipulating business data from within Excel."
-Bob Umlas, Excel MVP
Auflage
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Für höhere Schule und Studium
Maße
Höhe: 180 mm
Breite: 230 mm
Dicke: 30 mm
Gewicht
ISBN-13
978-0-7897-3664-2 (9780789736642)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Klassifikation
Conrad Carlbergis president of Network Control Systems, Inc., a software-development and consulting firm that specializes in statistical and database applications. 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
About the Platform............................................................................ 3
How This Book Is Organized............................................................. 4
Two Special Skills: Named Ranges and Array Formulas..................... 4
Assigning Names....................................................................... 5
Using Array Formulas................................................................ 6
Conventions Used in This Book......................................................... 6
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.................................................. 13
Measuring the Operating and Nonoperating Segments............... 17
Moving from the General Journal to the Income Statement................. 18
Getting the General Journal into Excel........................................ 18
Understanding Absolute, Relative, and Mixed References.......... 19
Getting the Journal Data to the Ledger...................................... 20
Getting the Ledger Data to the Income Statement..................... 23
Managing the Financial Analyses with Accrual Accounting................ 24
Organizing with Traditional vs. Contribution Approaches.................. 29
Summary......................................................................................... 30
2 Balance Sheet: Current Assets............................................................. 31
Designing the Balance Sheet............................................................. 32
Understanding Balance Sheet Accounts.................................... 32
Understanding Debit and Credit Entries.................................... 33
Getting a Current Asset Cash Balance.............................................. 34
Using Sheet-Level Names........................................................ 35
Getting a Cash Balance for Multiple Cash Accounts................. 37
Handling Restricted Cash Accounts.......................................... 39
Getting a Current Asset Accounts Receivable Balance....................... 40
Allowing for Doubtful Accounts............................................... 41
Using the Aging Approach to Estimating Uncollectibles............. 42
Using the Percentage of Sales Approach to Estimating Uncollectibles 44
Getting a Prepaid Expenses Balance................................................. 44
Dealing with Insurance as a Prepaid Expense............................ 45
Getting a Current Asset Balance....................................................... 47
Understanding the Inventory Flow............................................ 48
Closing the Inventory Account................................................. 48
Closing the Revenue and Expense Accounts............................. 49
Summary......................................................................................... 50
3 Valuing Inventories for the Balance Sheet............................................. 51
Valuing Inventories.......................................................................... 51
Using Specific Identification..................................................... 52
Using Average Cost................................................................. 58
Using FIFO............................................................................. 62
Using LIFO............................................................................. 69
Comparing the Four Valuation Methods........................................... 70
Handling Purchase Discounts........................................................... 72
Using Perpetual and Periodic Inventory Systems............................... 73
Calculating Turns Ratios................................................................... 75
Summary......................................................................................... 77
4 Summarizing Transactions: From the Journals to the Balance Sheet....... 79
Understanding Journals.................................................................... 80
Understanding Special Journals................................................ 81
Structuring the Special Sales Journal......................................... 81
Structuring the Special Purchases Journal................................. 83
Building Dynamic Range Names............................................... 83
Using Dynamic Range Names in the Journals............................ 85
Structuring the Cash Receipts Journal....................................... 86
Structuring the Cash Payments Journal..................................... 88
Understanding Ledgers.................................................................... 89
Creating the General Ledger.................................................... 90
Creating Subsidiary Ledgers.................................................... 92
Automating the Posting Process............................................... 93
Opening the Workbooks....................................................... 100
Getting a Current Liabilities Balance............................................... 102
Summary....................................................................................... 103
5 Working Capital and Cash Flow Analysis........................................... 105
Matching Costs and Revenues....................................................... 105
Broadening the Definition: Cash vs. Working Capital...................... 107
Determining the Amount of Working Capital........................... 108
Determining Changes in Working Capital................................ 113
Analyzing Cash Flow..................................................................... 116
Developing the Basic Information........................................... 118
Summarizing the Sources and Uses of Working Capital.......... 120
Identifying Cash Flows Due to Operating Activities................. 121
Combining Cash from Operations with Cash from Nonoperating Transactions 122
Summary....................................................................................... 123
6 Statement Analysis............................................................................. 125
Understanding a Report by Means of Common-Sizing.................... 126
Using Common-Sized Income Statements.............................. 126
Using Common-Sized Balance Sheets.................................... 128
Using Comparative Financial Statements................................ 129
Using Dollar and Percent Changes in Statement Analysis................ 131
Assessing the Financial Statements......................................... 132
Handling Error Values............................................................ 133
Evaluating Percentage Changes.............................................. 134
Common-Sizing for Variance Analysis............................................ 135
Common-Sizing by Headcount....................................................... 139
Summary....................................................................................... 141
7 Ratio Analysis.................................................................................... 143
Interpreting Industry Averages and Trends..................................... 144
Comparing Ratios Within Industries........................................ 145
Analyzing Ratios Vertically and Horizontally........................... 146
Getting a Basis for Ratios....................................................... 147
Analyzing Profitability Ratios.......................................................... 150
Finding and Evaluating Earnings Per Share.............................. 150
Determining Gross Profit Margin............................................ 151
Determining Net Profit Margin............................................... 153
Determining the Return on Assets (ROA)............................... 154
Determining the Return on Equity (ROE)................................ 155
Analyzing Leverage Ratios............................................................. 156
Determining the Debt Ratio.................................................... 157
Determining the Equity Ratio.................................................. 157
Determining the Times Interest Earned Ratio........................... 158
Analyzing Liquidity Ratios.............................................................. 159
Determining the Current Ratio................................................ 159
Determining the Quick Ratio.................................................. 160
Analyzing Activity Ratios................................................................ 161
Determining the Average Collection Period............................. 161
Determining Inventory Turnover............................................. 162
Summary....................................................................................... 163
II Financial Planning and Control
8 Budgeting and Planning Cycle............................................................ 167
Creating Pro Forma Financial Statements....................................... 167
Forecasting by Percentage of Sales........................................ 169
Performing Sensitivity Analysis............................................... 175
Moving from the Pro Forma to the Budget..................................... 176
Projecting Quarterly Sales...................................................... 176
Estimating Inventory Levels.................................................... 177
Fitting the Budget to the Business Plan.................................... 180
Summary....................................................................................... 181
9 Forecasting and Projections............................................................... 183
Making Sure You Have a Useful Baseline...................................... 184
Moving Average Forecasts............................................................ 185
Creating Forecasts with the Moving Average Add-In............. 188
Dealing with the Layout of Excel's Moving Averages.............. 189
Creating Moving Average Forecasts with Excel's Charts........ 190
Forecasting with Excel's Regression Functions............................... 192
Making Linear Forecasts: The TREND Function.................... 192
Making Nonlinear Forecasts: The GROWTH Function........... 194
Creating Regression Forecasts with Excel's Charts................. 198
Forecasting with Excel's Smoothing Functions................................ 199
Projecting with Smoothing...................................................... 199
Using the Exponential Smoothing Add-In............................... 200
Choosing a Smoothing Constant............................................. 202
Making Smoothed Forecasts Handle Seasonal Data............... 204
Using the Box-Jenkins ARIMA Approach: When Excel's Built-In Functions Won't Do 206
Understanding ARIMA Basics............................................... 207
Charting the Correlograms..................................................... 208
Starting with Correlograms to Identify a Model....................... 208
Identifying Other Box-Jenkins Models.................................... 210
Summary....................................................................................... 212
10. Measuring Quality............................................................................ 215
Monitoring Quality Through Statistical Process Control................... 215
Using Averages from Samples................................................ 216
Using X-and-S Charts for Variables....................................... 217
Interpreting the Control Limits................................................ 220
Using P-Charts for Dichotomies............................................. 224
Choosing the Sample Size...................................................... 226
Determining That a Process Is Out of Control......................... 227
Using X-and-MR Charts for Individual Observations.............. 231
Creating SPC Charts Using Excel.......................................... 231
Performing Acceptance Sampling................................................... 234
Charting the Operating Characteristic Curve........................... 235
Using Worksheet Functions for Quality Control.............................. 240
Sampling Units from a Finite Population.................................. 240
Sampling Units from a Nonfinite Population............................ 242
Sampling Defects in Units....................................................... 244
Using the CRITBINOM Function.......................................... 246
Summary....................................................................................... 250
III Investment Decisions
11. Examining a Business Case: Investment............................................ 253
Developing a Business Case........................................................... 253
Getting Consensus for the Plan............................................... 254
Showing Your Work............................................................. 256
Developing the Excel Model.......................................................... 257
Summary....................................................................................... 264
12. Examining Decision Criteria for a Business Case............................... 265
Understanding Payback Periods..................................................... 265
Understanding Future Value, Present Value, and Net Present Value 270
Calculating Future Value........................................................ 270
Calculating Present Value....................................................... 270
Calculating Net Present Value................................................ 271
Shortening the Payback Period...................................................... 273
Summary....................................................................................... 277
13. Creating a Sensitivity Analysis for a Business Case........................... 279
Reviewing the Business Case......................................................... 279
Managing Scenarios....................................................................... 280
Saving a Scenario for the Base Case...................................... 282
Developing Alternative Scenarios........................................... 284
Developing Scenarios That Vary Expenses............................. 286
Summarizing the Scenarios..................................................... 287
Measuring Profit............................................................................ 288
Calculating Internal Rate of Return......................................... 288
Calculating Profitability Indexes.............................................. 290
Estimating the Continuing Value.............................................. 291
Varying the Discount Rate Input..................................................... 292
Using the Goal Seek Tool.............................................................. 294
Summary....................................................................................... 296
14. Planning Profits................................................................................ 297
Understanding the Effects of Leverage............................................ 297
Analyzing Operating Leverage........................................................ 298
Planning by Using the DOL.................................................... 307
Analyzing Financial Leverage......................................................... 308
Determining the Debt Ratio.................................................... 309
Determining the Times Interest Earned Ratio........................... 310
Summary....................................................................................... 310
15. Making Investment Decisions Under Uncertain Conditions................ 311
Using Standard Deviations............................................................. 311
Understanding Confidence Intervals............................................... 315
Using Confidence Intervals in a Market Research Situation..... 315
Refining Confidence Intervals................................................. 318
Using Regression Analysis in Decision Making................................ 319
Regressing One Variable onto Another................................... 319
Avoiding Traps in Interpretation: Association vs. Causation..... 325
Regressing One Variable onto Several Other Variables: Multiple Regression 326
Estimating with Multiple Regression........................................ 334
Summary....................................................................................... 338
16. Fixed Assets.................................................................................... 339
Determining Original Cost.............................................................. 339
Determining Costs................................................................. 340
Choosing Between Actual Cost and Replacement Cost.......... 341
Depreciating Assets....................................................................... 342
Using Straight-Line Depreciation............................................ 344
Using the DB Function........................................................... 345
Using the DDB Function to Calculate Depreciation................. 348
Using Variable Declining Balance Depreciation....................... 349
Using Sum-of-Years'-Digits Depreciation.............................. 351
Summary....................................................................................... 352
IV Sales and Marketing
17. Revenue Recognition and Income Determination............................... 355
Revenue Recognition in Theory...................................................... 355
The Earning Process...................................................................... 356
Recognizing Revenue During Production......................................... 358
Using the Percentage of Completion Method.......................... 358
The Completed Contract Method.......................................... 360
Recognizing Revenue at Completion of Production......................... 360
Recognizing Revenue with the Point of Sale Method....................... 361
Making Sales on Credit......................................................... 362
Incurring Costs After the Point of Sale.................................... 363
Allowing for Return Privileges................................................ 363
Recognizing Revenue During the Cash-Collection Process.............. 364
Using the Installment Method................................................. 364
Using the Cost Recovery Method.......................................... 365
Recognizing Revenue in Different Industries.................................... 366
Recognizing Revenue in Service Industries.............................. 366
Recognizing Revenue in Manufacturing Industries.................... 366
Understanding the Impact of Revenue-Recognition Methods........... 366
Summary....................................................................................... 367
18. Importing Business Data into Excel................................................... 369
Creating and Using ODBC Queries................................................ 370
Preparing to Import Data....................................................... 370
Specifying Data Sources........................................................ 371
Creating Queries with the Query Wizard................................ 374
Creating Queries with Microsoft Query.................................. 377
Creating Parameterized Queries in Microsoft Query............... 380
Using Joins in Microsoft Query.............................................. 380
Working with External Data Ranges............................................... 381
Managing Security Information............................................... 382
Arranging Automatic Refreshes.............................................. 384
Setting Other Data Range Options......................................... 385
Importing Data to Pivot Tables and Charts............................. 385
Creating and Using Web Queries................................................... 389
Using Parameterized Web Queries................................................. 391
Using VBA to Update an External Database.................................. 393
Structuring the Worksheet...................................................... 394
Editing the Record's Values................................................... 395
Adding New Records to the Recordset.................................. 399
Choosing to Use DAO.......................................................... 401
Updating the Database Using ADO................................................ 402
Choosing to Use ADO.......................................................... 402
Using ADO to Update a Database......................................... 403
Summary....................................................................................... 406
19. Analyzing Contributions and Margins................................................ 407
Calculating the Contribution Margin................................................ 408
Using Unit Contribution.................................................................. 411
Increasing the Contribution Margin......................................... 412
Creating an Operating Income Statement................................ 413
Finding the Break-Even Point......................................................... 413
Calculating Break-Even in Units............................................. 414
Calculating Break-Even in Sales............................................. 415
Calculating Break-Even in Sales Dollars with an Expected Level of Profit 416
Charting the Break-Even Point............................................... 417
Choosing the Chart Type....................................................... 419
Making Assumptions in Contribution Analysis................................. 421
Linear Relationships............................................................... 421
Assignment of Costs.............................................................. 422
Constant Sales Mix................................................................ 422
Worker Productivity.............................................................. 422
Determining Sales Mix................................................................... 423
Analyzing Segment Margin............................................................. 424
Summary....................................................................................... 425
20. Pricing and Costing.......................................................................... 427
Using Absorption and Contribution Costing.................................... 428
Understanding Absorption Costing......................................... 428
Understanding Contribution Costing....................................... 433
Applying the Contribution Approach to a Pricing Decision...... 436
Using Contribution Analysis for New Products............................... 438
Allocating Expenses to Product Lines..................................... 440
Varying the Inputs.................................................................. 441
Estimating the Effect of Cross-Elasticity.......................................... 442
Summary....................................................................................... 444
Appendixes
A What's on the Web Site?................................................................... 449
B Glossary............................................................................................ 451
0789736640, TOC, 5/22/2007
