Gottlieb I. Next Generation Excel: Modeling In Excel For Analysts And MBAs

2nd ed. — Wiley, 2013. — 304 p. — ISBN: 1118469100, 9781118469101For MS Windows and Mac OS
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.Using Excel Efficiently
AutoFill
AutoFill Options
Right-Drag AutoFill
Appendix: AutoFill in Excel 2003 and Excel Mac 2011
Review Questions
Answers
Selecting Efficiently in Excel
Review Questions 18Answers
Formulas, Functions, and Relative and Absolute Addressing
Relative and Absolute Addressing
Other Functions
Appendix: Doing It in Mac Excel 2011
Review Questions
Answers
Naming Cells and Ranges
Naming a Single Cell
Naming a Range of Cells
Using the Name Menu to Create Names
Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011
Review Questions
Answers
Conditional and Advanced Conditional Formatting in Excel
Simple Conditional Formatting; Adding a Rule
New Conditional Formatting Features
Advanced Conditional Formatting
Appendix: Using the Conditional Formatting in Excel 2003
Review Questions
Answers
Excel Charts
Quick/Instant Chart
Creating a Chart Using the Menu
Adding More Data to an Existing Chart
More about Charts
Three-Dimensional Charts — Column and Pie
Pie Charts
Appendix: Generating Charts in Excel 2003 and Mac Excel 2011
Sparklines and Advanced Topics in Excel Charts
Sparklines
More about Charts
Review Questions
Answers
IF Functions and Text Manipulations
IF Functions
Simple IF Functions
Nested IF Functions
Nested IF — Payroll Example
Appendix: Using the IF Function in Mac Excel 2011
Review Questions
Answers
Text Manipulation
Text to Columns
Appendix: Using Text to Column in Excel 2003
Review Questions
Answers 112
Statistical Tools
Descriptive Statistics
Descriptive Statistics
Appendix: Descriptive Statistics with the Mac Excel 2011
Review Questions
Answers
Frequency Distributions
One More Example
Appendix: Frequency Distributions with Mac Excel 2011
Review Questions
Answers
Statistical Regression
Using the Scatter Chart in Excel
Linear Regression — Using Excel Functions
Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011
Review Questions
Answers
Data Analysis — The Excel Easy to Use Statistics Add-In
Descriptive Statistics
Frequency Distribution Using Histogram
Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003
Review Questions
Answers
Data Analysis — Multi-Regression
Simple or Single Variable Regression
Multi-Regression
Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003
Review Questions
Answers
What-If Analysis
Naming Cells — For Meaningful Decision Making and Modeling
One More Example — If You Have a Ready Model
Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011
Review Questions
Answers
What-If Analysis and Goal Seek
Goal Seek
Appendix: Goal Seek in Excel 2003 and Mac Excel 2011
Review Questions
Answers
Sensitivity Analysis — One- and Two-Way Data Tables
Two-Way Table
Data Tables — One More Example
Break-Even Point (BEP) Chart
Appendix: Sensitivity Analysis — One- and Two-Way Data Tables for Excel 2003 and Mac Excel 2011
Review Questions
Answers
Using Scroll Bars for Sensitivity Analysis
Scroll Bar Limitations
Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011
Review Questions
Answers
Multi-Page Systems and Lookups
Multi-Page Budgets — Going to the Third Dimension
A Payroll Example
A Second Example — Bakers’ Supplies
Review Questions
Answers
Lookup Tables
Range — Approximate Match Lookup
An Exact Match Lookup
Review Questions
Answers
The Data Menu and Ribbon
Sorting Data
Sorting by Multiple Parameters
Appendix: Sorting in Excel 2003 and Mac Excel 2011
Review Questions
Answers
AutoFilter
Dates
Below and Above Average
Filter by Color
Appendix: AutoFilter in Excel 2003
Review Questions
Answers
Data Forms and Features Eliminated in Excel 2007 and 2010
Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011
Review Questions
Answers
Group and Outline Data
Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011
Review Questions
Answers
Excel Subtotals
One More Example — Function Applied to Multiple Categories
Copying Grouped Data
Second Example — Larger Database
Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011
Review Questions
Answers
Pivot Tables
PivotTable Example
Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011
Review Questions
Answers
Data Mining Using Pivot Tables
Appendix: Advanced PivotTable Techniques in Excel 2003
Review Questions
Answers
Using Slicers to Filter Pivot Tables
Format a Slicer
Sharing Slicers for More Than One Pivot Table
Review Questions
Answers
Excel Financial Tools
NPV and IRR — Evaluating Capital Investments
The Time Value of Money
IRR — Internal Rate of Return
Review Questions
Answers
Unconventional Financial Functions: XNPV and XIRR
Excel XNPV — The Net Present Value Function for Uneven Intervals
Excel XIRR — The Internal Rate of Return Function for Uneven Intervals
Review Questions
Answers
Frequently Used Financial Functions
Similar Excel Functions
Review Questions
Answers
Amortization Tables
Amortization Example
Review Questions
Answers
Accounting Depreciation Functions
SLD Straight Line Depreciation
SYD Sum of the Years Digits
Review Questions
Answers
Using the Solver Add-In
Beyond the Goal Seek — More Than One Changing Cell? Use the Solver
Example — Break-Even Point
Using the Solver
Appendix: Using the Solver in Excel 2003 and Mac Excel 2011
Review Questions
Answers
The Solver Add-In — Optimizer
Solver Example
Nonlinear Example Using the Solver
Appendix: Using the Solver in Excel 2003 and Mac 2011
Review Questions
Answers
Appendix Summary Case Study — Supply Chain Management Example