Business Analysis with Microsoft Excel

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® 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  

Table of Contents:
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