Next Generation Excel

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.

Foreword xv

Preface xvii

Acknowledgments xxi

PART ONE Using Excel Efficiently 1

CHAPTER 1 AutoFill 3

AutoFill Options 8

Right-Drag AutoFill 8

Appendix: AutoFill in Excel 2003 and Excel Mac 2011 10

Review Questions 13

Answers 14

CHAPTER 2 Selecting Efficiently in Excel 15

Review Questions 18

Answers 19

CHAPTER 3 Formulas, Functions, and Relative and Absolute Addressing 21

Relative and Absolute Addressing 22

Other Functions 25

Appendix: Doing It in Mac Excel 2011 28

Review Questions 29

Answers 30

CHAPTER 4 Naming Cells and Ranges 31

Naming a Single Cell 31

Naming a Range of Cells 33

Using the Name Menu to Create Names 34

Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 38

Review Questions 41

Answers 42

CHAPTER 5 Conditional and Advanced Conditional Formatting in Excel 45

Simple Conditional Formatting; Adding a Rule 45

New Conditional Formatting Features 46

Advanced Conditional Formatting 49

Appendix: Using the Conditional Formatting in Excel 2003 52

Review Questions 53

Answers 55

CHAPTER 6 Excel Charts 57

Quick/Instant Chart 57

Creating a Chart Using the Menu 57

Adding More Data to an Existing Chart 61

More about Charts 64

Three-Dimensional Charts—Column and Pie 66

Pie Charts 70

Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 73

CHAPTER 7 Sparklines and Advanced Topics in Excel Charts 81

Sparklines 81

More about Charts 83

Review Questions 90

Answers 91

PART TWO IF Functions and Text Manipulations 93

CHAPTER 8 IF Functions 95

Simple IF Functions 95

Nested IF Functions 98

Nested IF—Payroll Example 99

Appendix: Using the IF Function in Mac Excel 2011 101

Review Questions 102

Answers 102

CHAPTER 9 Text Manipulation 103

Text to Columns 103

Appendix: Using Text to Column in Excel 2003 108

Review Questions 111

Answers 112

PART THREE Statistical Tools 113

CHAPTER 10 Descriptive Statistics 115

Descriptive Statistics 115

Appendix: Descriptive Statistics with the Mac Excel 2011 117

Review Questions 119

Answers 119

CHAPTER 11 Frequency Distributions 121

One More Example 126

Appendix: Frequency Distributions with Mac Excel 2011 129

Review Questions 129

Answers 130

CHAPTER 12 Statistical Regression 131

Using the Scatter Chart in Excel 131

Linear Regression—Using Excel Functions 133

Appendix: Using the Chart Feature to Create a

Trend Line in Excel 2003 and Mac Excel 2011 137

Review Questions 140

Answers 140

CHAPTER 13 Data Analysis—The Excel Easy to Use Statistics Add-In 141

Descriptive Statistics 141

Frequency Distribution Using Histogram 143

Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 148

Review Questions 148

Answers 149

CHAPTER 14 Data Analysis—Multi-Regression 151

Simple or Single Variable Regression 151

Multi-Regression 152

Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 160

Review Questions 160

Answers 161

PART FOUR What-If Analysis 165

CHAPTER 15 Naming Cells—For Meaningful Decision Making and Modeling 167

One More Example—If You Have a Ready Model 168

Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011 170

Review Questions 175

Answers 175

CHAPTER 16 What-If Analysis and Goal Seek 177

Goal Seek 178

Appendix: Goal Seek in Excel 2003 and Mac Excel 2011 182

Review Questions 183

Answers 183

CHAPTER 17 Sensitivity Analysis—One- and Two-Way Data Tables 185

Two-Way Table 187

Data Tables—One More Example 189

Break-Even Point (BEP) Chart 190

Appendix: Sensitivity Analysis—One- and Two-Way Data Tables for Excel 2003 and Mac Excel 2011 192

Review Questions 193

Answers 195

CHAPTER 18 Using Scroll Bars for Sensitivity Analysis 199

Scroll Bar Limitations 203

Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011 205

Review Questions 207

Answers 208

PART FIVE Multi-Page Systems and Lookups 211

CHAPTER 19 Multi-Page Budgets—Going to the Third Dimension 213

A Payroll Example 213

A Second Example—Bakers’ Supplies 217

Review Questions 219

Answers 220

CHAPTER 20 Lookup Tables 221

Range—Approximate Match Lookup 221

An Exact Match Lookup 224

Review Questions 227

Answers 228

PART SIX The Data Menu and Ribbon 229

CHAPTER 21 Sorting Data 231

Sorting by Multiple Parameters 233

Appendix: Sorting in Excel 2003 and Mac Excel 2011 236

Review Questions 238

Answers 239

CHAPTER 22 AutoFilter 241

Dates 241

Below and Above Average 243

Filter by Color 243

Appendix: AutoFilter in Excel 2003 244

Review Questions 246

Answers 247

CHAPTER 23 Data Forms and Features Eliminated in Excel 2007 and 2010 249

Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011 251

Review Questions 252

Answers 253

CHAPTER 24 Group and Outline Data 255

Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011 258

Review Questions 260

Answers 261

CHAPTER 25 Excel Subtotals 263

One More Example—Function Applied to Multiple Categories 265

Copying Grouped Data 265

Second Example—Larger Database 269

Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011 269

Review Questions 273

Answers 273

CHAPTER 26 Pivot Tables 275

PivotTable Example 275

Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011 282

Review Questions 286

Answers 286

CHAPTER 27 Data Mining Using Pivot Tables 289

Appendix: Advanced PivotTable Techniques in Excel 2003 296

Review Questions 297

Answers 298

CHAPTER 28 Using Slicers to Filter Pivot Tables 301

Format a Slicer 302

Sharing Slicers for More Than One Pivot Table 304

Review Questions 305

Answers 306

PART SEVEN Excel Financial Tools 307

CHAPTER 29 NPV and IRR—Evaluating Capital Investments 309

The Time Value of Money 309

IRR—Internal Rate of Return 311

Review Questions 313

Answers 313

CHAPTER 30 Unconventional Financial Functions: XNPV and XIRR 315

Excel XNPV—The Net Present Value Function for Uneven Intervals 315

Excel XIRR—The Internal Rate of Return Function for Uneven Intervals 316

Review Questions 318

Answers 318

CHAPTER 31 Frequently Used Financial Functions 319

Similar Excel Functions 320

Review Questions 323

Answers 323

CHAPTER 32 Amortization Tables 325

Amortization Example 325

Review Questions 334

Answers 334

CHAPTER 33 Accounting Depreciation Functions 337

SLD Straight Line Depreciation 337

SYD Sum of the Years Digits 337

Review Questions 339

Answers 339

PART EIGHT Using the Solver Add-In 341

CHAPTER 34 Beyond the Goal Seek—More Than One Changing Cell? Use the Solver 343

Example—Break-Even Point 343

Using the Solver 343

Appendix: Using the Solver in Excel 2003 and Mac Excel 2011 349

Review Questions 350

Answers 352

CHAPTER 35 The Solver Add-In—Optimizer 353

Solver Example 353

Nonlinear Example Using the Solver 355

Appendix: Using the Solver in Excel 2003 and Mac 2011 360

Review Questions 360

Answers 360

Appendix Summary Case Study—Supply Chain Management Example 365

About the Author 385

Index 387

Dr. Isaac D. Gottlieb, PhD, is a professor of Management and Statistics at the Fox Business School of Temple University and Director of the China and Singapore programs. Also, he is a former director of the International Executive MBA at Rutgers University Business School. The recipient of numerous excellence-in-teaching awards, Dr. Gottlieb has taught how to use Excel and apply it effectively to various business disciplines to thousands of MBA and Executive MBA students at Rutgers, Temple, New York University, Columbia, and other universities. Also, he has instructed Excel for business professionals to over 20,000 participants in corporate seminars, conferences and workshops, including staff from Johnson & Johnson, Merck, Pfizer, Procter & Gamble, Microsoft, Intel, Boeing, Chrysler, JP Morgan, Morgan Stanley, 3M and Hewlett-Packard. Dr Gottlieb has an "Excel Tip Of The Month" newsletter that goes to over 50,000 recipients.

Most Excel users never realize just how powerful the program really is. Featuring more than 400 functions, Excel is packed with an array of complex data mining, analytical, statistical, and graphical representation tools and other powerful features of inestimable value to business professionals. But learning how to exploit those features with confidence can be tricky, even overwhelming, for the casual user.

Enter Next Generation Excel, Second Edition.

Growing out of Dr. Gottlieb's many years of teaching university classes, as well as his extremely popular corporate workshops, Next Generation Excel, Second Edition is based upon his well-founded conviction that, when it comes to teaching people how to use and get the most out of complex software, "show, don't tell" is the golden rule.

Staying true to that philosophy, he eschews the lengthy, often perplexing, verbal explanations typical of such guides in favor of bite-sized descriptive passages, liberally supplemented with Excel-in-action screenshots, skill-building exercises, and review questions. Also, for ease of reference, each subject-specific chapter is self-contained, affording you the option of focusing on only those features of immediate interest to you.

Demystifying the obscure and simplifying the complex, Dr. Gottlieb walks you, step by step, through all of Excel 2010's advanced business and finance functions (with explanations for users of Excel 2007, 2003, and previous versions, as needed), including:

• Financial tools, including NVP and IRR
• Data mining using Pivot Tables
• Charting, sparklines, and other advanced charting options
• Data analysis tools
• What-if analysis
• Statistics for non-statisticians
• Accounting functions, including amortization tables and depreciation functions

Not just another general how-to Excel guide, this fully updated and expanded Second Edition of Isaac Gottlieb's internationally acclaimed Next Generation Excel shows you how to harness the full power of Excel to help you to perform an array of advanced business decision making, financial, and accounting functions. You'll learn how to quickly create models that deliver accurate, relevant information to boost efficiency, improve your forecasting ability, and perform an array of other crucial business and reporting functions.

Advance Praise for Next Generation Excel, Second Edition

"As an avid user of Excel, I rely on Isaac's 'Excel Tip Of The Month' newsletter, which reflects his teaching methods and ability to clearly present ideas and topics. In his newest book, Isaac showcases how the reader can engage with Excel to the 'next level of usability.' This edition helps intermediate users to quickly acquire 'pro Excel' skills, while enabling advanced users to grasp concepts in a single pass-through. Isaac takes day-to-day Excel challenges and provides the reader with elegant ease of use solutions. With this newest edition, Isaac propels practical information even further."
—Moshe Castiel, Vice President, Data Management and Analytics, JPMorgan Chase

"If you thought MS Excel was nothing more than an overpriced calculator with a pretty chart wizard thrown in, then hold your hat and get ready for some fun. Next Generation Excel will revolutionize how you do your work and make you a better manager, at any career level."
—John Tintera, VP, Sales & Publicity, Osprey Group

"A detailed understanding of Excel is critical to the performance of nearly all financial analysts today. Dr. Gottlieb's book not only describes the most important Excel functions, but also dramatically increases the productivity of Excel users. My favorite parts of the book include the sections on Solver, Pivot Tables, and Sensitivity Analysis. Each of these areas is critical to the work done by advanced financial analysts. Companies are expecting more from their MBA hires today, and this book can give recent graduates a leg up on the competition."
—John M. Longo, PhD, CFA, Clinical AssociateProfessor of Finance and Economics, Rutgers Business School; Chief Investment Officer, The MDE Group, Inc.

"Isaac Gottlieb is an Excel master and a teacher par excellence. The second edition of Next Generation Excel improves on an already excellent first edition. New materials like multi regression simplified and the new Excel 2010 features make this an invaluable resource for the Excel user. Mac users (a growing group) will like the chapter appendices dedicated to making their lives easier."
— Simon Benninga, Professor, Faculty of Management, Tel Aviv University