Next Generation Excel

Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS)

Isaac Gottlieb

• Publisher: Wiley
• Year: 2013
• Print ISBN:
  9781118469101
• Edition: 2
• eBook ISBN:
  9781118469088
• Topic: Business
• Subtopic: Finance
• Index: Business

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.

Part One

Using Excel Efficiently

Part One describes how Excel, the widely used spreadsheet software, can be used efficiently to help build your spreadsheet for a variety of purposes. As an MBA student, an analyst, or an executive, you will develop enough expertise to perform the same tasks you were performing before—using other means—much faster and in a more efficient way. This part of the book demonstrates tools, shortcuts, and techniques for carrying out some common tasks quickly and efficiently.

This part will not turn you into an Excel expert in a short time, but by the end you should improve the tasks you can do—the types of tasks that make Excel into such an incredibly powerful and flexible tool for modeling, finance, statistics, and data manipulation.

In Part One: Using Excel Efficiently, we cover the AutoFill feature, efficient selecting, and highlighting in Excel. You will also learn how to use keyboard selection shortcuts. The next topic covered is how to insert formulas, activate functions, and use absolute and relative addressing. The last three parts are the naming of cells and ranges, formatting and conditional formatting, and creating simple as well as complicated charts and Sparklines.

Chapter 1

AutoFill

The AutoFill feature in Microsoft Excel can automatically fill in cells with commonly used series (numbers, months, and days of the week) or with custom lists you can create. This chapter demonstrates how to use the drag handle and other ways to fill in information. These operations work in all directions: top down, down up, left to right, and right to left. Figure 1.1 demonstrates this feature.

FIGURE 1.1 Using the Drag Handle

Select two adjacent cells and release the mouse. When you hover again over the lower right corner, your mouse pointer should change shape to a crosshair (+) called a drag handle. You can click and drag down the column and Excel will continue the initial two-cell series for you.

You can AutoFill several types of data including, but not limited to, numbers, dates, days, and annual quarters by selecting cells and dragging the handle as shown in Figure 1.1.

By default, a number of AutoFill lists are pre-installed in the program. For a list of the available AutoFill series, in the 2010 version we use the File icon to get to the Excel Advanced Options menu and the Custom Lists menu. See Figure 1.2. For 2007 go to the Windows icon, click on Excel Options, and click on the Edit Custom Lists button. See Figure 1.2.

FIGURE 1.2 Custom Lists

You may add your own lists as needed to Custom Lists. This is explained at the end of the chapter.

Try to use the following example for using the Custom Lists shown in Figure 1.3. We filled in the information in the sheet. You may want to open the AutoFill sheet in the Excel file for Chapter 1 on the web page. The example illustrates the use of the AutoFill feature in Excel.

FIGURE 1.3 Drag Handle and AutoFill

Select the first two values in column B (B2 and B3), click on the lower right drag handle of cell B3, and pull the drag handle down toward cell B10.

Dragging down the information created the desired AutoFill effect of continuing with the same series of numbers: 6, 9, 12, 15, . . ., 30. Try to drag down the information shown in columns C and D. You will create the information shown in Figure 1.4.

FIGURE 1.4 “Dragging” Down the Information

After you experiment with a couple of columns, try a more efficient way: select two vertical adjacent cells E2:E4. Release the mouse for a moment. Go to the drag handle. This time, do not drag—just double-click. Excel will drag it down for you. See Figure 1.5. It will complete filling for you to the end of the adjacent column on the left. You may try double-clicking with more than one column selected at a time. Double-click works only in one direction: down.

...

Table of contents

• Cover
• Contents
• Title
• Dedication
• Copyright
• Foreword
• Preface
• Acknowledgments
• Part One: Using Excel Efficiently
• Part Two: IF Functions and Text Manipulations
• Part Three: Statistical Tools
• Part Four: What-If Analysis
• Part Five: Multi-Page Systems and Lookups
• Part Six: The Data Menu and Ribbon
• Part Seven: Excel Financial Tools
• Part Eight: Using the Solver Add-In
• Appendix: A Summary Case Study
• About the Author
• Index