Building Financial Models with Microsoft® Excel®: A Guide for Business Professionals, Second Edition

Building Financial Models with Microsoft® Excel®: A Guide for Business Professionals, Second Edition

by K. Scott Proctor
Released December 2009
Publisher(s): Wiley
ISBN: 9780470481745

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

A proven guide to building financial models from scratch

The Second Edition of Building Financial Models with Microsoft Excel + CD-ROM provides beginning and intermediate level computer users with step-by-step instructions on building financial models using Microsoft Excel 2007-the most popular spreadsheet program available. The accompanying CD-ROM contains Excel worksheets that track the course of the book and allow you to build your own financial models. This comprehensive resource also covers important topics such as the concepts of valuation, sensitivity analysis, and contribution margin.

  • Offers accessible guidance on building financial models using Excel 2007

  • Illustrates how to integrate financial statements such as the balance sheet, income statement, and statement of cash flows

  • Covers the basics of building and using a Capitalization Table

  • Discusses how to best present a financial model

Incorporating financial models into business decisions has become an essential element of good business practice, and this book will show you how to excel at this endeavor.

Table of contents

  1. Copyright
  2. WARNING AND DISCLAIMER
  3. PERMISSIONS
  4. ADDITIONAL INFORMATION
  5. Foreword
  6. Preface
    1. PURPOSE OF THIS BOOK
    2. NEED FOR THIS BOOK
    3. TARGET AUDIENCE FOR THIS BOOK
    4. SUMMARY OF CONTENTS
    5. A FINAL NOTE
  7. Acknowledgments
  8. I. The Master Budget
    1. 1. Overview of Budgets and Financial Models
      1. 1.1. BUDGETS
        1. 1.1.1. Operating Budget
          1. 1.1.1.1. Sales and Collections Budget
          2. 1.1.1.2. Cost-of-Goods-Sold Budget
          3. 1.1.1.3. Inventory and Purchases Budget
          4. 1.1.1.4. Operating Expenses Budget
          5. 1.1.1.5. Budgeted Statement of Income
        2. 1.1.2. Financial Budget
          1. 1.1.2.1. Capital Budget
          2. 1.1.2.2. Cash Budget
          3. 1.1.2.3. Budgeted Balance Sheet
      2. 1.2. FINANCIAL MODELS
        1. 1.2.1. Master Budget—Operating Budget
          1. 1.2.1.1. Sales and Collections Budget
          2. 1.2.1.2. Cost-of-Goods-Sold Budget
          3. 1.2.1.3. Inventory and Purchases Budget
          4. 1.2.1.4. Operating Expenses Budget
          5. 1.2.1.5. Budgeted Statement of Income
        2. 1.2.2. Master Budget—Financial Budget
          1. 1.2.2.1. Capital Budget
          2. 1.2.2.2. Cash Budget
          3. 1.2.2.3. Budgeted Balance Sheet
        3. 1.2.3. Additional Components of a Master Budget
        4. 1.2.4. Consolidated Financial Statements
          1. 1.2.4.1. Balance Sheet
          2. 1.2.4.2. Income Statement
          3. 1.2.4.3. Statement of Cash Flows
        5. 1.2.5. Free Cash Flow Analysis
        6. 1.2.6. Sensitivity Analysis
        7. 1.2.7. Contribution Margin Analysis
        8. 1.2.8. Financial Ratios Analysis
        9. 1.2.9. Valuation Analysis
        10. 1.2.10. Capitalization Chart
      3. 1.3. QUESTIONS
    2. 2. Operating Budget—Assumptions, Sales, and Collections
      1. 2.1. NAPAVALE—BACKGROUND INFORMATION ON EXAMPLE COMPANY
      2. 2.2. STEP 1: ASSUMPTIONS AND DASHBOARD WORKSHEET
      3. 2.3. STEP 2A: UNIT SALES AND PRICE BUDGET
      4. 2.4. STEP 2B: SALES COMPOSITION BUDGET
      5. 2.5. STEP 2C: CASH COLLECTIONS FROM CUSTOMERS BUDGET
      6. 2.6. STEP 2D: ACCOUNTS RECEIVABLE BUDGET
      7. 2.7. QUESTIONS
    3. 3. Operating Budget—Cost of Goods Sold, Inventory, and Purchases
      1. 3.1. STEP 3: COST-OF-GOODS-SOLD BUDGET
      2. 3.2. STEP 4A: INVENTORY BUDGET
      3. 3.3. STEP 4B: PURCHASES BUDGET
      4. 3.4. STEP 4C: DISBURSEMENTS FOR PURCHASES BUDGET
      5. 3.5. STEP 4D: ACCOUNTS PAYABLE BUDGET
      6. 3.6. QUESTIONS
    4. 4. Operating Budget—Operating Expenses
      1. 4.1. STEP 5A: HEADCOUNT BUDGET
        1. 4.1.1. Headcount Overview Worksheet
        2. 4.1.2. Headcount Cost Worksheet
      2. 4.2. STEP 5B: OPERATING EXPENSES BUDGET
      3. 4.3. STEP 5C: DISBURSEMENTS FOR OPERATING EXPENSES BUDGET
      4. 4.4. QUESTIONS
    5. 5. Operating Budget—Income Statement
      1. 5.1. STEP 6: BUDGETED STATEMENT OF INCOME
      2. 5.2. QUESTIONS
    6. 6. Financial Budget—Capital Budget and Cash Budget
      1. 6.1. STEP 7A: CAPITAL EXPENDITURES BUDGET
      2. 6.2. STEP 7B: DISBURSEMENTS FOR CAPITAL EXPENDITURES BUDGET
      3. 6.3. STEP 7C: DEPRECIATION BUDGET
      4. 6.4. STEP 8: CASH BUDGET
      5. 6.5. QUESTIONS
    7. 7. Financial Budget—Balance Sheet
      1. 7.1. STEP 9: BUDGETED BALANCE SHEET
      2. 7.2. QUESTIONS
  9. II. Financial Statements and Free Cash Flows
    1. 8. Consolidated Financial Statements
      1. 8.1. BALANCE SHEET
      2. 8.2. INCOME STATEMENT
      3. 8.3. STATEMENT OF CASH FLOWS
        1. 8.3.1. Cash Flows from Operating Activities
        2. 8.3.2. Cash Flows from Investing Activities
        3. 8.3.3. Cash Flows from Financing Activities
      4. 8.4. QUESTIONS
    2. 9. Free Cash Flows and Dashboard
      1. 9.1. CALCULATION OF FREE CASH FLOWS
      2. 9.2. DASHBOARD
        1. 9.2.1. Balance Sheet Status
        2. 9.2.2. Statement of Cash Flows Status
      3. 9.3. QUESTIONS
  10. III. Analysis of a Financial Model
    1. 10. Sensitivity Analysis
      1. 10.1. DASHBOARD
      2. 10.2. DATA TABLES
        1. 10.2.1. Revenues Data Table
        2. 10.2.2. Net Income Data Table
        3. 10.2.3. Free Cash Flows Data Table
      3. 10.3. QUESTIONS
    2. 11. Contribution Margin Analysis
      1. 11.1. FIXED AND VARIABLE COSTS
      2. 11.2. CONTRIBUTION MARGIN
      3. 11.3. OPERATING LEVERAGE
      4. 11.4. BREAKEVEN POINTS IN UNITS
      5. 11.5. BREAKEVEN POINT IN DOLLARS
      6. 11.6. QUESTIONS
    3. 12. Financial Ratios Analysis
      1. 12.1. PROFIT MARGINS—FINANCIAL RATIOS
      2. 12.2. INVESTMENT RETURNS—FINANCIAL RATIOS
      3. 12.3. MANAGEMENT EFFICIENCY—FINANCIAL RATIOS
      4. 12.4. QUESTIONS
    4. 13. Valuation
      1. 13.1. DISCOUNTED CASH FLOW
      2. 13.2. PUBLIC COMPANY COMPARABLES
      3. 13.3. MERGERS AND ACQUISITIONS COMPARABLES
      4. 13.4. WEIGHTED VALUATION
      5. 13.5. QUESTIONS
    5. 14. Capitalization Chart
      1. 14.1. FOUNDING CAPITALIZATION CHART
      2. 14.2. EQUITY INVESTMENT'S EFFECT ON CAPITALIZATION CHART
      3. 14.3. STOCK OPTIONS' EFFECT ON CAPITALIZATION CHART
      4. 14.4. QUESTIONS
  11. Answers to Chapter Questions
    1. CHAPTER 1: Overview of Budgets and Financial Models
    2. CHAPTER 2: Operating Budget—Assumptions, Sales, and Collections
    3. 14.7. CHAPTER 3: Operating Budget—Cost of Goods Sold, Inventory, and Purchases
    4. 14.8. CHAPTER 4: Operating Budget—Operating Expenses
    5. 14.9. CHAPTER 5: Operating Budget—Income Statement
    6. 14.10. CHAPTER 6: Financial Budget—Capital Budget and Cash Budget
    7. 14.11. CHAPTER 7: Financial Budget—Balance Sheet
    8. 14.12. CHAPTER 8: Consolidated Financial Statements
    9. 14.13. CHAPTER 9: Free Cash Flows and Dashboard
    10. 14.14. CHAPTER 10: Sensitivity Analysis
    11. 14.15. CHAPTER 11: Contribution Margin Analysis
    12. 14.16. CHAPTER 12: Financial Ratios Analysis
    13. 14.17. CHAPTER 13: Valuation
    14. 14.18. CHAPTER 14: Capitalization Chart
  12. A. General Overview of Microsoft Excel 2007 Features and Functionality
    1. A.1. BASIC FUNCTIONALITY
      1. A.1.1. Keyboard Shortcuts
      2. A.1.2. New, Open, Close, and Save
      3. A.1.3. Viewing Information
      4. A.1.4. Entering Information into Cells
      5. A.1.5. Copy, Cut, and Paste
      6. A.1.6. Undo and Redo
      7. A.1.7. Insert, Remove, and Move Columns and Rows
      8. A.1.8. Naming Worksheets
      9. A.1.9. Naming Cells and Cell Ranges
    2. A.2. FORMATTING
      1. A.2.1. Cell Height and Width
      2. A.2.2. Alignment
      3. A.2.3. Number Format
      4. A.2.4. Font Format
      5. A.2.5. Border Format
      6. A.2.6. Fill Format
      7. A.2.7. Copy Formatting
    3. A.3. FORMULAS AND FUNCTIONS
      1. A.3.1. Formulas
      2. A.3.2. Functions
      3. A.3.3. Referencing Other Cells
    4. A.4. ADVANCED FEATURES
      1. A.4.1. Data Tables
  13. About the CD-ROM
    1. INTRODUCTION
    2. CD-ROM TABLE OF CONTENTS
    3. MINIMUM SYSTEM REQUIREMENTS
    4. CDA
      1. For Windows:
      2. For Macintosh:
    5. USING THE CD WITH WINDOWS
    6. USING THE CD WITH THE MAC OS
    7. TROUBLESHOOTING
    8. USING THE SOFTWARE
    9. CUSTOMER CARE
  14. About the Author

Product information

  • Title: Building Financial Models with Microsoft® Excel®: A Guide for Business Professionals, Second Edition
  • Author(s): K. Scott Proctor
  • Release date: December 2009
  • Publisher(s): Wiley
  • ISBN: 9780470481745