Ebook: Next Generation Excel: Modeling in Excel for Analysts and MBAs

Overview
"Dr. Isaac Gottlieb personally trained 35 of our top analysts and finance professionals in a customized advanced Excel seminar. Many of the tools covered in this book were part of this well-received training. For the past five years, Isaac’s monthly Excel-Tip-Of-The-Month newsletter has been a personal favorite of mine. In an age where email overload is common, I always look forward to his email and many of my associates and team members around the world are part of his over 50,000 monthly recipients. This compilation is a must-have for any finance professional who wants to be a better business partner and truly add value in a more efficient manner!"
Anthony N. Caspio, CMA
Vice President - Finance Sealed Air Corporation

"Excel has been one of the most powerful applications of recent times, but most users, unfortunately, only scratch its surface. Prof. Gottlieb’s book leverages his years of Excel workshops in the US, Europe and Asia, and he unleashes the tremendous potential and power of Excel. His to-the-point explanations and his innovative screenshots bring Excel alive to readers who may just have a working knowledge of the software. This amazing volume is an application-oriented book for Excel users who want answers fast, and who need to maximize the application’s awesome potential."
Prof. Farrokh Langdana, Ph.D.
Director, Rutgers Executive MBA Professor, Rutgers Business School

"If I should thank Microsoft for inventing Excel, then I would like to thank Dr. Isaac Gottlieb more for teaching me how to use it to solve the real problems in my real work. Keep going Isaac!"
Wu Haowen
Director of International Programs School of Management, Dalian University of Technology

"Isaac Gottlieb's Next Generation Excel is an invaluable resource. The fruit of many years of teaching Excel to university business students, Gottlieb’s book covers the topics that the Excel user needs to become more efficient and sophisticated. It is easy to read and follow. A wonderful book!"
Simon Benninga
Professor of Finance, Faculty of Management, Tel Aviv University Author of Principles of Finance with Excel

"As an avid user of Excel, several years ago I came across Isaac's Excel-Tip-Of-The-Month newsletter. I quickly realized that Isaac exploits Excel to the “next usability level.” Like his monthly newsletters, Isaac has composed a book that is clear and concise similarly reflecting his teaching methods. This book helps intermediate users to rapidly acquire Excel-pro skills, while allowing advanced users to grasp concepts in one single pass-through. Furthermore, Isaac takes day-to-day challenges and provides elegant ease of use solutions."
Moshe Castiel
Manager, IT – Technology & Operations TIAA-CREF

"Next Generation Excel presents an interesting perspective on the resourcefulness of Microsoft Excel like never before. This book is an excellent and a much-needed resource to MBA students, educators and Excel enthusiasts. In reviewing this book, the principal criteria included content, organization and Excel add-on files. The book is neatly organized into sections; starting with basic Excel tools and functions, commonly used statistical tools, what-if-analysis and then moving on to advanced data features, solver-add ins and various other ToolPaks, nicely complemented with the Excel snapshots to provide a user friendly experience. As a result, one can jump directly to the relevant sections and use the Excel files provided to get a more hands-on experience. The right mix of theory and practice, is the unique selling point (USP) of this book!"
Anirudha "Andy" Vaidya, MBA
CEO AVS System

Table of Contents
A Word from the Author xi

Acknowledgments xiii

Introduction and Overview xv

PART ONE Using Excel Efficiently 1

CHAPTER 1 AutoFill 3

CHAPTER 2 Selecting Efficiently In Excel 14

CHAPTER 3 Formulas, Functions, Relative and Absolute Addressing 19

CHAPTER 4 Naming Cells and Ranges 28

CHAPTER 5 Excel Charts 38

PART TWO IF Functions and Text Manipulations 63

CHAPTER 6 IF Functions 65

CHAPTER 7 Text Manipulation 72

PART THREE Statistical Tools 83

CHAPTER 8 Descriptive Statistics 85

CHAPTER 9 Frequency Distributions 90

CHAPTER 10 Statistical Regression 100

CHAPTER 11 Data Analysis—The Excel Easy to Use Statistics Add-in 109

PART FOUR What-if Analysis 119

CHAPTER 12 Naming Cells—for Meaningful Decision Making and Modeling 121

CHAPTER 13 What-if Analysis and Goal Seek 129

CHAPTER 14 Sensitivity Analysis—One- and Two-way Data Tables 136

CHAPTER 15 Using Scroll Bars for Sensitivity Analysis 149

PART FIVE Multi-Page Systems and Lookups 159

CHAPTER 16 Multi-Page Budgets—Going to the Third Dimension 161

CHAPTER 17 Lookup Tables 170

PART SIX The Data Menu and Ribbon 179

CHAPTER 18 Sorting Data 181

CHAPTER 19 AutoFilter 190

CHAPTER 20 Data Forms and Features Eliminated in Excel 2007 198

CHAPTER 21 Group and Outline Data 204

CHAPTER 22 Subtotals 209

CHAPTER 23 Pivot Tables 219

CHAPTER 24 Data Mining Using Pivot Tables 229

PART SEVEN Excel Financial Tools 241

CHAPTER 25 NPV and IRR—Evaluating Capital Investments 243

CHAPTER 26 Unconventional Financial Functions: XNPV and XIRR 249

CHAPTER 27 Frequently Used Financial Functions 253

CHAPTER 28 Amortization Tables 258

CHAPTER 29 Accounting Depreciation Functions 268

PART EIGHT Using the Solver Add-In 271

CHAPTER 30 Beyond the Goal Seek—More than One Changing Cell? Use the Solver 273

CHAPTER 31 The Solver Add-In—Optimizer 282

Index 291