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.
- 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.