You are previewing Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition.
O'Reilly logo
Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Book Description

Take Excel to the next level in accounting and financial modeling

In this new Second Edition of Next Generation Excel, Isaac Gottlieb shows financial analysts how to harness the full power of Excel to move forward into the new world of accounting and finance. Companies of all sizes use financial models to analyze their finances and plan business operations, as well as to create financial accounting reports like balance sheets, income statements, and statements of cash flows.

While many businesspeople are quite familiar with the reports created with financial models, most are not as familiar with the creation of the models themselves. This book shows them how to build an accurate and effective financial model using the solid functionality and easy usability of Excel.

  • Fully updated and revised to include support for Apple users

  • Written by a professor of management and statistics who has taught the discipline for fifteen years

  • Appropriate for professional financial analysts, as well as MBA students

For professionals and students whose responsibilities or studies include a full understanding of financial modeling, Next Generation Excel, Second Edition offers comprehensive training.

Table of Contents

  1. Cover
  2. Contents
  3. Title
  4. Dedication
  5. Copyright
  6. Foreword
  7. Preface
  8. Acknowledgments
  9. Part One: Using Excel Efficiently
    1. Chapter 1: AutoFill
      1. AutoFill Options
      2. Right-Drag AutoFill
      3. Appendix: AutoFill in Excel 2003 and Excel Mac 2011
      4. Review Questions
      5. Answers
    2. Chapter 2: Selecting Efficiently in Excel
      1. Review Questions
      2. Answers
    3. Chapter 3: Formulas, Functions, and Relative and Absolute Addressing
      1. Relative and Absolute Addressing
      2. Other Functions
      3. Appendix: Doing It in Mac Excel 2011
      4. Review Questions
      5. Answers
    4. Chapter 4: Naming Cells and Ranges
      1. Naming a Single Cell
      2. Naming a Range of Cells
      3. Using the Name Menu to Create Names
      4. Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011
      5. Review Questions
      6. Answers
    5. Chapter 5: Conditional and Advanced Conditional Formatting in Excel
      1. Simple Conditional Formatting; Adding a Rule
      2. New Conditional Formatting Features
      3. Advanced Conditional Formatting
      4. Appendix: Using the Conditional Formatting in Excel 2003
      5. Review Questions
      6. Answers
    6. Chapter 6: Excel Charts
      1. Quick/Instant Chart
      2. Creating a Chart Using the Menu
      3. Adding More Data to an Existing Chart
      4. More about Charts
      5. Three-Dimensional Charts—Column and Pie
      6. Pie Charts
      7. Appendix: Generating Charts in Excel 2003 and Mac Excel 2011
    7. Chapter 7: Sparklines and Advanced Topics in Excel Charts
      1. Sparklines
      2. More about Charts
      3. Review Questions
      4. Answers
  10. Part Two: IF Functions and Text Manipulations
    1. Chapter 8: IF Functions
      1. Simple IF Functions
      2. Nested IF Functions
      3. Nested IF—Payroll Example
      4. Appendix: Using the IF Function in Mac Excel 2011
      5. Review Questions
      6. Answers
    2. Chapter 9: Text Manipulation
      1. Text to Columns
      2. Appendix: Using Text to Column in Excel 2003
      3. Review Questions
      4. Answers
  11. Part Three: Statistical Tools
    1. Chapter 10: Descriptive Statistics
      1. Descriptive Statistics
      2. Appendix: Descriptive Statistics with the Mac Excel 2011
      3. Review Questions
      4. Answers
    2. Chapter 11: Frequency Distributions
      1. One More Example
      2. Appendix: Frequency Distributions with Mac Excel 2011
      3. Review Questions
      4. Answers
    3. Chapter 12: Statistical Regression
      1. Using the Scatter Chart in Excel
      2. Linear Regression—Using Excel Functions
      3. Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011
      4. Review Questions
      5. Answers
    4. Chapter 13: Data Analysis—The Excel Easy to Use Statistics Add-In
      1. Descriptive Statistics
      2. Frequency Distribution Using Histogram
      3. Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003
      4. Review Questions
      5. Answers
    5. Chapter 14: Data Analysis—Multi-Regression
      1. Simple or Single Variable Regression
      2. Multi-Regression
      3. Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003
      4. Review Questions
      5. Answers
  12. Part Four: What-If Analysis
    1. Chapter 15: Naming Cells—For Meaningful Decision Making and Modeling
      1. One More Example—If You Have a Ready Model
      2. Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011
      3. Review Questions
      4. Answers
    2. Chapter 16: What-If Analysis and Goal Seek
      1. Goal Seek
      2. Appendix: Goal Seek in Excel 2003 and Mac Excel 2011
      3. Review Questions
      4. Answers
    3. Chapter 17: Sensitivity Analysis—One- and Two-Way Data Tables
      1. Two-Way Table
      2. Data Tables—One More Example
      3. Break-Even Point (BEP) Chart
      4. Appendix: Sensitivity Analysis—One- and Two-Way Data Tables for Excel 2003 and Mac Excel 2011
      5. Review Questions
      6. Answers
    4. Chapter 18: Using Scroll Bars for Sensitivity Analysis
      1. Scroll Bar Limitations
      2. Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011
      3. Review Questions
      4. Answers
  13. Part Five: Multi-Page Systems and Lookups
    1. Chapter 19: Multi-Page Budgets—Going to the Third Dimension
      1. A Payroll Example
      2. A Second Example—Bakers’ Supplies
      3. Review Questions
      4. Answers
    2. Chapter 20: Lookup Tables
      1. Range—Approximate Match Lookup
      2. An Exact Match Lookup
      3. Review Questions
      4. Answers
  14. Part Six: The Data Menu and Ribbon
    1. Chapter 21: Sorting Data
      1. Sorting by Multiple Parameters
      2. Appendix: Sorting in Excel 2003 and Mac Excel 2011
      3. Review Questions
      4. Answers
    2. Chapter 22: AutoFilter
      1. Dates
      2. Below and Above Average
      3. Filter by Color
      4. Appendix: AutoFilter in Excel 2003
      5. Review Questions
      6. Answers
    3. Chapter 23: Data Forms and Features Eliminated in Excel 2007 and 2010
      1. Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011
      2. Review Questions
      3. Answers
    4. Chapter 24: Group and Outline Data
      1. Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011
      2. Review Questions
      3. Answers
    5. Chapter 25: Excel Subtotals
      1. One More Example—Function Applied to Multiple Categories
      2. Copying Grouped Data
      3. Second Example—Larger Database
      4. Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011
      5. Review Questions
      6. Answers
    6. Chapter 26: Pivot Tables
      1. PivotTable Example
      2. Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011
      3. Review Questions
      4. Answers
    7. Chapter 27: Data Mining Using Pivot Tables
      1. Appendix: Advanced PivotTable Techniques in Excel 2003
      2. Review Questions
      3. Answers
    8. Chapter 28: Using Slicers to Filter Pivot Tables
      1. Format a Slicer
      2. Sharing Slicers for More Than One Pivot Table
      3. Review Questions
      4. Answers
  15. Part Seven: Excel Financial Tools
    1. Chapter 29: NPV and IRR—Evaluating Capital Investments
      1. The Time Value of Money
      2. IRR—Internal Rate of Return
      3. Review Questions
      4. Answers
    2. Chapter 30: Unconventional Financial Functions: XNPV and XIRR
      1. Excel XNPV—The Net Present Value Function for Uneven Intervals
      2. Excel XIRR—The Internal Rate of Return Function for Uneven Intervals
      3. Review Questions
      4. Answers
    3. Chapter 31: Frequently Used Financial Functions
      1. Similar Excel Functions
      2. Review Questions
      3. Answers
    4. Chapter 32: Amortization Tables
      1. Amortization Example
      2. Review Questions
      3. Answers
    5. Chapter 33: Accounting Depreciation Functions
      1. SLD Straight Line Depreciation
      2. SYD Sum of the Years Digits
      3. Review Questions
      4. Answers
  16. Part Eight: Using the Solver Add-In
    1. Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver
      1. Example—Break-Even Point
      2. Using the Solver
      3. Appendix: Using the Solver in Excel 2003 and Mac Excel 2011
      4. Review Questions
      5. Answers
    2. Chapter 35: The Solver Add-In—Optimizer
      1. Solver Example
      2. Nonlinear Example Using the Solver
      3. Appendix: Using the Solver in Excel 2003 and Mac 2011
      4. Review Questions
      5. Answers
  17. Appendix: A Summary Case Study
    1. Appendix Summary Case Study—Supply Chain Management Example
  18. About the Author
  19. Index