In this Excel for Accounting & Finance Course
I have taught people how to utilize Excel for business since 1997. My focus has always been on the applications needed in a business environment.
Because of years of teaching the application’s extensive possibilities, I have found that systematic and hands-on approaches are the most effective.
When you get to the heart of the matter, you will find that the author is speaking to the reader, as I am doing now. This book was voluntarily written in a very informal tone, to keep the learning process as dynamic as it can be in a classroom or workshop setting.
I wanted to create a book that appeals to students, as well as practitioners and instructors. All the techniques in this book have been developed first with the professors and students who attended my classes and workshops. What is in this book is the distillation of the feedback of many years’ teaching.
I have learned what people really want to know about Excel. One thing is certain: They don’t want to read long, complicated explanations. They want a simple answer with visual figures or pictures; therefore, this book is filled with short explanations and screen shots. It is organized in a way to help you hit the ground running, and quickly at that!
I have verified the application of this book’s subject matter outside of the classroom—in putting together projects, generating business plans, and other decision- making instances. I have applied the various techniques time after time in a variety of consulting projects. All the examples in this book are the direct result of real-life, hands-on experience.
Most people who use Excel want and need fast answers to their problems. The special features of the Excel software are covered in a manner inviting the user to apply the knowledge immediately.
While the book is written with Excel 2010 and 2007 in mind, most chapters have an appendix for Excel 2011 for Mac, Excel 2003 or earlier versions, when the latest version implied different manipulations of the software.
Every chapter has a few review questions at the end (with the answers!) to ensure that you really understand what you have read, by applying the knowledge to different problems to be solved than the ones covered in the chapter. I cannot encourage you enough to do these exercises.
WHO SHOULD READ THIS BOOK?
You will find this book useful if you . . .
- Spend more than 10 hours a week using Excel—you will save hundreds of hours a year applying this knowledge.
- Are a decision maker at any level—you will improve your decision-making ability.
- Are an analyst in the areas of accounting, finance, marketing & sales, HR, or strategy—you will learn to handle your analysis in a more efficient and creative way.
- Have to make quantitative or graphical presentations.
- Are an executive and have to train your staff in time-saving process applications.
- Are an executive assistant—you can better support management with your improved skills.
- Are an entrepreneur and need to create business plans.
- Are dealing with accounting, marketing/sales, HR, finance, or other business disciplines.
- Are a student or plan to become one.
- Will be in a position to have to train people to use Excel.
OVERVIEW OF THE CONTENTS
This book has eight parts. Read it in order or feel free to jump directly to any of the parts.
Part One explains how to use Excel efficiently. It covers the AutoFill, efficient selection, and highlighting functions in Excel. You will also learn how to use keyboard selection shortcuts. The second topic covered shows how to insert formulas or functions and the use of absolute versus relative addressing. The last two chapters describe the naming of cells and ranges and how one creates charts. You will also find a chapter on conditional formatting and a chapter about advanced charts and sparklines.
Part Two covers two commonly needed skills: the use of the IF functions, which make Excel an invaluable tool for decision-making purposes, and the Text manipulation functions.
Part Three introduces Statistical Tools. Statistics in Excel provide the user with a set of tools helpful in sorting out and solving a variety of problems. This part covers descriptive statistics and simple regressions. You will also find a new chapter covering Multi Regression.
Part Four is called What-If Analysis. What-if analysis enables the user to find out what will be the impact of change. This part of the book shows you how to take advantage of the what-if tools in the decision-making process. It demonstrates features such as naming cells for modeling, the goal seek, one- and two-way data tables, and the effective use of scroll bars.
Part Five covers two chapters, Multi-Page Systems and Lookups. Most Excel users either keep their entire model or information on one worksheet or—when they use a number of sheets—do not take advantage of structuring the workbook/system so that they can use Excel more effectively. Studying the Multi-Page chapter will remedy this shortcoming.
The second portion of Part Five discusses lookup functions. It demonstrates how to perform an exact lookup and how to perform range lookups. After you understand the lookup function described, you will be able to perform any of the other lookup functions.
Part Six looks at the Data menu and ribbon. This part of the book deals with the Data menu features of Excel. It covers all the following topics: Sorting data, Filters, Creating and Using Data Forms, Grouping Data, Subtotals, and Pivot Tables. This edition has a new chapter covering the Pivot Table Slicer.
Part Seven deals with the variety of Financial Tools Excel comes equipped with. What are the most frequently used financial formulas available, including those in the Analysis ToolPak, and how do we apply them using Excel efficiently?
Part Eight explains how to use the Solver Add-in. Solver is an Excel Add-in, which, in very simple terms, is a software tool for “solving” mathematical systems of equations for optimizations. This part of the book does not attempt to teach the mathematical aspect of using the Solver, but it does demonstrate how to put it to good use for three different applications.
The first application explains how to use the Goal Seek when you want to have more than one changing cell or decision variable. The second application demonstrates efficient use of the Solver for a linear optimization problem. The last case explains its use in a nonlinear optimization problem.
Appendix A has a comprehensive Summary Case Study to review and practice many of the topics covered in the book.
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.
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.
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.
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.
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.
You may want to try it yourself. As shown in Figure 1.6, all of the columns selected are highlighted and the crosshair handle appears at the lower right corner of the final column. In Figure 1.7, you can see the results after using the AutoFill double-click.
More features of the AutoFill function are discussed in the context of regressions in Chapter 12. I also explain the concept of Time in Excel in Chapter 12.
Creating Custom Lists in Excel enables you to use these lists as demonstrated with the AutoFill function. Custom Lists let you use them when you sort in Excel. In addition to sorting in numerical or alphanumeric order, you can also sort with these Custom Lists or with the ones you create. You can use the list created here later to sort a database.
To create a Custom List in Excel, you need to type the list in a range on a sheet as shown in Figure 1.8. I used a list of the 10 largest cities in the United States where your company may be doing business as an example.
Refer to Figure 1.2 for how to access the Custom Lists menu. In Excel 2010, go to the File icon to get to the Excel Advanced Options menu and the Custom Lists menu. For Excel 2007, go to the Office icon, click on Excel Options, and click on the Edit Custom Lists button. The result is shown in Figure 1.9. For Excel 2003 or the Mac 2011 version, see the Appendix to this chapter.
Now all you have to do to make the list of the 10 largest cities in the United States, sorted by population size, part of your Excel Custom List, is click on the empty cell on the menu to the left of the Import button and select the cells on the sheet. Click on the Import button and the list is now part of your Custom Lists. See Figure 1.10.