Financial Modeling in Excel For Dummies Cheat Sheet
When you’re building financial models in Microsoft Excel, functions are the name of the game. You also need to check your work — and check it again — to make sure no errors slip through the cracks. Finally, to make your work quick and easy, keyboard shortcuts are a lifesaver.
Essential Excel Functions for Building Financial Models
Today well 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.
|Function||What It Does|
|SUM||Adds up, or sums together, a range of cells.|
|MIN||Calculates the minimum value of a range of cells.|
|MAX||Calculates the maximum value of a range of cells.|
|AVERAGE||Calculates the average value of a range of cells.|
|ROUND||Rounds a single number to the nearest specified value, usually to a whole number.|
|ROUNDUP||Rounds up a single number to the nearest specified value, usually to a whole number.|
|ROUNDDOWN||Rounds down a single number to the nearest specified value, usually to a whole number.|
|IF||Returns a specified value only if a single condition has been met.|
|IFS||Returns a specified value if complex conditions have been met.|
|COUNTIF||Counts the number of values in a range that meet a certain single criterion.|
|COUNTIFS||Counts the number of values in a range that meet multiple criteria.|
|SUMIF||Sums the values in a range that meet a certain single criterion.|
|SUMIFS||Sums the values in a range that meet multiple criteria.|
|VLOOKUP||Looks up a range and returns the first corresponding value in a vertical table that matches exactly the specified input.|
|HLOOKUP||Looks up a range and returns the first corresponding value in a horizontal table that matches exactly the specified input. An error is returned if it cannot find the exact match.|
|INDEX||Works like the coordinates of a map and returns a single value based on the column and row numbers you input into the function fields.|
|MATCH||Returns the position of a value in a column or a row. Modelers often combine MATCH with the INDEX function to create a lookup function, which is far more robust and flexible and uses less memory than the VLOOKUP or HLOOKUP.|
|PMT||Calculates the total annual payment of a loan.|
|IPMT||Calculates the interest component of a loan.|
|PPMT||Calculates the principal component of a loan.|
|NPV||Takes into account the time value of money by giving the net present value of future cash flows in today’s dollars, based on the investment amount and discount rate.|
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:
|Ctrl+R||Copy the far left cell across the range. (You have to highlight range first.)|
|Ctrl+D||Copy the top cell down the range. (You have to highlight the range first.)|
|Ctrl+E+L||Delete a sheet.|
|Ctrl+– (hyphen)||Delete selected cells.|
|Arrow keys||Move to new cells.|
|Ctrl+Pg Up/Pg Down||Switch worksheets.|
|Ctrl+Arrow keys||Go to end of continuous range and select a cell.|
|Shift+Arrow keys||Select range.|
|Shift+Ctrl+Arrow keys||Select continuous range.|
|Home||Move to beginning of the line.|
|Ctrl+Home||Move to cell A1.|
|F2||Edit formula, showing precedent cells.|
|Alt+Enter||Start a new line in the same cell.|
|Shift+Arrow keys||Highlight within cells.|
|F4||Change absolute referencing (“$”).|
|Esc||Cancel a cell entry.|
|ALT+= (equal sign)||Sum selected cells.|
|F9||Recalculate all workbooks.|
|Ctrl+[||Highlight precedent cells.|
|Ctrl+]||Highlight dependent cells.|
|F5+Enter||Go back to original cell.|
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.