Business Analysis with Microsoft Excel

Business Analysis with Microsoft Excel

3.8 (15 ratings by Goodreads)

List price: US$33.99

Currently unavailable

Add to wishlist

AbeBooks may have this title (opens in new window).

Try AbeBooks

Description

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 SpreadsheetsCovers Applicable for versions of Microsoft Excel 97 to 2007User 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
show more

Product details

  • Paperback | 504 pages
  • 177.8 x 228.6 x 33.02mm | 748.42g
  • Que Corporation,U.S.
  • Indianapolis, IN, United States
  • English
  • 3rd edition
  • col. Illustrations
  • 0789736640
  • 9780789736642
  • 825,051

Back cover copy

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
show more

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
show more

About Conrad George Carlberg

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.
show more

Rating details

15 ratings
3.8 out of 5 stars
5 20% (3)
4 40% (6)
3 40% (6)
2 0% (0)
1 0% (0)
Book ratings by Goodreads
Goodreads is the world's largest site for readers with over 50 million reviews. We're featuring millions of their reader ratings on our book pages to help you find your new favourite book. Close X