Financial Modeling in Excel For Dummies Cheat Sheet

Essential Excel functions for building financial models

There are now over 400 functions are available in Excel, and Microsoft keeps adding more with each new version of the software. Many of these functions aren’t relevant for use in finance, and most Excel users only use a very small percentage of the available functions.

If you’re using Excel for the purpose of financial modeling, you need a firm grasp on the most commonly used functions, at the very least.

Although there are many, many more that you’ll find useful when building models, here’s a list of the most basic functions that you can’t be without.

There’s a lot more to being a good financial modeler than simply knowing lots of Excel functions. A skilled modeler can select which function is best to use in which situation. Usually, you can find several different ways to achieve the same result, but the best option is always the function or solution that is the simplest, clearest, and easiest for others to understand.

What to look for when checking or auditing a financial model

If you’ve been using Excel for a while, you probably prefer to build your own spreadsheets or financial models from scratch. In a corporate environment, however, people rarely get this opportunity. Instead, they’re expected to take over an existing model that someone else has built.

Maybe you’re stepping into a role that you’re taking over from someone else and there’s an existing financial reporting model that you’ll need to update every month. Or you’ve been told to calculate sales commissions every quarter based on a monstrous 50-tab spreadsheet that you don’t really like the looks of. Not only do you inherit others’ models together with the inputs, assumptions, and calculations the original modeler has entered, but you also inherit the modeler’s mistakes.

If you’re going to be taking responsibility for someone else’s model, you need to be prepared to take it over and make it your own. You have to be accountable for the workings of this model and confident that it’s working correctly. Here’s a checklist of things you should check for when you first inherit someone else’s financial model:

• Get familiar with its look and feel. Look through each sheet to see what color schemes have been used. Read through any documentation. Is there a key to help see which cells are which? Has the modeler differentiated between formulas and hard-coded assumptions?
• Take a good look at the formulas. Are they consistent? Do they contain any hard-coded values that won’t automatically update and will, therefore, cause errors?
• Run an error check. Press the Error Checking button on the Formula Auditing section of the Formulas tab in the Ribbon to see at a glance whether there are any Excel errors on the sheet that might cause problems.
• Check for links to external files. External links might be a valid part of the working operational process, but you need to know if this file gets any inputs from external workbooks to make sure no one inadvertently changes sheet or filenames, causing errors in your model. Find external links by pressing the Edit Links button on the Connections section of the Data tab in the Ribbon.
• Review the named ranges. Named ranges can be useful in a financial model but they sometimes harbor errors due to redundant names, as well as external links. Review the named ranges in the Name Manager, which is in the Defined Names section of the Formulas tab on the Ribbon. Delete any named ranges that contain errors or aren’t being used, and if they contain links to external files take note and make sure they are needed.
• Check automatic calculations. Formulas should calculate automatically, but sometimes when a file is very large, or a modeler likes to control the changes manually, the calculation has been set to manual instead of automatic. If you see the word Calculate in the lower-left status bar this means that the calculation has been set to manual, so you’re probably in for some complex investigation! Press the Calculation Options button on the Calculation section of the Formulas tab on the Ribbon to change between manual and automatic workbook calculation.

In addition to these steps, here are some handy audit tools in Excel you can use to check, audit, validate, and, if necessary, correct an inherited model so that you can be confident in the results of your financial model:

• Inspect Workbook. Get to know the hidden features of your model and identifying potentially problematic features that might otherwise be very difficult to find with this little-known tool. To use it, open the workbook, click on the File button on the Ribbon; on the Info tab, click the Check for Issues button.
• F2: If a formula’s source cells are on the same page, the F2 shortcut puts the cell into edit mode, so this shortcut is a good way of seeing visually where the source data is coming from.
• Trace Precedents/Dependents: Excel audit tools trace the relationships visually with tracer line arrows. You can access these tools in the Formula Auditing section of the Formulas tab on the Ribbon.
• Evaluate Formula: Take apart long and complex formulas using the Evaluate Formula tool, in the Formula Auditing section of the Formulas tab on the Ribbon.
• Error checking tools: If you make a mistake — or what Excel thinks is a mistake — a green triangle will appear in the upper-left corner of the cell. This will happen if you omit adjacent cells, or if you enter an input as text, which looks like it should be a number.
• Watch Window: If you have output cells you’d like to keep an eye on, this tool will display the result of specified cells in a separate window. You can find this tool in the Formula Auditing section of the Formulas tab on the Ribbon. It’s useful for testing formulas to see the impact of a change in assumptions on a separate cell or cells.
• Show Formulas: To see all the formulas at a glance rather than the resulting values, press the Show Formulas button in the Formula Auditing section of the Formulas tab on the Ribbon (or use the Ctrl+` shortcut). Show Formulas is also a very quick and easy way to see if any hard-coded values exist.

Excel keyboard shortcuts for financial modelers

If you’re spending a lot of time modeling in Excel, you can save yourself time by learning some keyboard shortcuts. Much of the modeler’s skills are about speed and accuracy, and by practicing these shortcuts until they become muscle memory, you’ll be a more rapid and accurate modeler.

Here’s a list of the most useful shortcuts that should be part of your everyday keyboard use if you’re a financial modeler:

To find the shortcut for any function, press the Alt key and the shortcut keys will show the Ribbon. For example, to go to the Name Manager, press Alt+M+N, and the Name Manager dialog box appears.

In the upper-left corner, you’ll find the Quick Access Toolbar. You can change the shortcuts that appear on the Quick Access Toolbar by clicking the tiny arrow to the right of the toolbar and selecting what you want to add from the drop-down menu that appears. For example, if you add Paste Special to the Quick Access Toolbar, Paste Special can be accessed with the shortcut Alt+4. Note that this only works when the Quick Access Toolbar has been customized, and whatever you put in the fourth position will be accessed by the shortcut Alt+4.