Now Australia's Biggest Toy Store

Sell Your Old Stuff for Cash. It's Easy & Free to List. Get Started Now.

Business Analysis with Microsoft Excel
By

Rating
New or Used: 2 copies from $19.86
Product Details

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 Statements1 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......................................................................................... 302 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......................................................................................... 503 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......................................................................................... 774 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....................................................................................... 1035 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....................................................................................... 1236 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....................................................................................... 1417 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 Control8 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....................................................................................... 1819 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....................................................................................... 21210. 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 Decisions11. 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....................................................................................... 26412. 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....................................................................................... 27713. 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....................................................................................... 29614. 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....................................................................................... 31015. 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....................................................................................... 33816. 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 Marketing17. 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....................................................................................... 36718. 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....................................................................................... 40619. 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....................................................................................... 42520. 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....................................................................................... 444AppendixesA What's on the Web Site?................................................................... 449B Glossary............................................................................................ 4510789736640, TOC, 5/22/2007

Promotional Information

Bookstore shelves are overflowing with books on Microsoft Excel, however there are few books specifically written for business users offering professional techniques to analyze revenues and profits, fixed assets, growth, investments and other financial interests. Over the past eleven years however, Business Analysis with Microsoft Excel has been one of those rare books and has proven to be a steady seller. Furthermore, the book is a topic book, not a version specific book, so periodic, new releases of Microsoft Excel have less impact on its sales than books that focus just on the latest version. But the new version of Excel brings several new enhancements that need to be covered. Business Analysis with Microsoft Excel, Third Edition adopts a broader perspective on business analysis than does its competition. While it discusses, for example, some optimization problems in manufacturing contexts, it also covers problems that are of greater interest to Sales and Marketing, and in the boardroom, such as investment decisions and contribution analysis. In contrast, competitive books appear to reflect the authors' interests more than those of the readers. The topics to be covered include, among others: Analyzing financial statements: income statements, balance sheets and cash flows. Forecasting, particularly sales. Covered are both regression and smoothing approaches. Quality management Business cases and pro formulas. Marketing and product support.

About the Author

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.

Ask a Question About this Product More...
Write your question below:
How Fishpond Works
Fishpond works with suppliers all over the world to bring you a huge selection of products, really great prices, and delivery included on over 25 million products that we sell. We do our best every day to make Fishpond an awesome place for customers to shop and get what they want — all at the best prices online.
Webmasters, Bloggers & Website Owners
You can earn a 5% commission by selling Business Analysis with Microsoft Excel on your website. It's easy to get started - we will give you example code. After you're set-up, your website can earn you money while you work, play or even sleep! You should start right now!
Authors / Publishers
Are you the Author or Publisher of a book? Or the manufacturer of one of the millions of products that we sell. You can improve sales and grow your revenue by submitting additional information on this title. The better the information we have about a product, the more we will sell!
Back to top