Relative cell referencing

Relative cell referencing is the default in Excel. For example, Figure 6-2 calculates the total price by referring to values in cells B2 and A3. The asterisk is used to multiply the numbers in the two cells.

Although you can manually type out a cell reference, this may introduce hard-to-detect typing errors. To avoid this problem, enter your cell reference by typing the formula up to the point of the cell reference and then select the cell with the value to be used in your formula. You can do this by clicking the cell with your mouse or using the arrow keys on your keyboard. When you select a cell, Excel automatically places the cell name in your formula.

When you copy a formula with relative references to the cell below, the row number of the references adjust accordingly. In the same way, when you copy the formula to the next cell over, the column letters changes. For example, when the formula in cell B3 is copied down the range, the references automatically change, as shown in Figure 6-3, and the results will be wrong!

To get the correct result using relative references, you’d need to re-create the formula five times instead of copying it down, as shown in Figure 6-4.

Although this does give the correct results, it’s not the fastest and easiest way of building this formula. There are several reasons why this solution is not recommended:

• It takes a lot longer to build.
• The formula is prone to error because you’re far more likely to accidentally pick up the wrong cell.
• Updating it will mean having to update each and every formula in the range.
• It’s more difficult for someone else to follow and, hence, more difficult to audit.

Absolute cell referencing

A much better solution would be to use the formula =$B$2*A3 and then copy the formula down the range, as shown in Figure 6-5. Putting one dollar sign before the column letter and another before the row number in the cell reference anchors the reference when it’s copied. This is called an absolute reference.

In this example, the $B$2 part of the formula is an absolute reference, and the A3 part of the formula is a relative reference. As shown in Figure 6-5, the absolute reference remains constant, but the relative reference changes when it’s copied down.

Let’s take a look at a practical example and apply relative and absolute cell referencing to a very simple financial model. You’ve been given the annual salaries for staff members who will work on a particular project. Assume that each person works 260 days in the year, and each person must work 60 days on this project. Calculate the total staff cost of the project.

Download File 0601.xlsx at www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled 6-6, or open a blank workbook and enter and format the data as shown in Figure 6-6. Then follow these steps:

1. In cell C6, enter the formula =B6/B3.

   This formula calculates the daily rate of each person by dividing his annual salary by the number of days he works per year. The formula result is $923.08.

2. Press F4 to apply absolute referencing.

   The formula changes to =B6/$B$3.

3. Copy this formula down the column.

   Now you need to calculate what those project costs are based on the daily rate by multiplying the number of project days by the daily rate.

4. In cell D6, enter the formula =D3*C6.

5. When you copy it down, you want the project days reference to remain constant, so press F4 to fix this reference.

   The formula is =$D$3*C6 with the resulting value of $55,385.

6. Copy this formula down the column.

7. In cell D10, enter the formula =SUM(D6:D9).

   The result $146,769, which is the total project cost.

8. Compare your results to Figure 6-7.

   You can perform some sensitivity analysis by changing the number of project days in cell D3 from 60 to 65. Change the cost for the business analyst in cell B7 from $120,000 to $150,000. Observe the effect it has on the project costs.

Mixed cell referencing

Mixed cell referencing is a combination of relative and absolute referencing; one part of the reference is absolute, and the other is relative. When you add a dollar sign before the row, the row remains anchored when the cell is copied; when you add a dollar sign before the column, the column remains anchored when the cell is copied.

For example, if you create the reference =B2 in a cell, and then copy that reference down, it will change to B3. If you add some anchoring, here are the results in each case:

The dollar sign anchors a row number or column letter when you copy it. You can anchor both the column and the row (absolute referencing), or you can anchor one or the other (mixed referencing).

Mixed cell referencing is a concept critical to good financial modeling practice, so it’s important for a financial modeler to understand this fundamental concept. Used effectively, mixed cell references make your model

• Faster to build and more efficient
• Less prone to error
• Quicker, easier, and cheaper to audit

The easiest way to quickly add absolute and mixed cell referencing is to press F4 immediately after adding the reference to the formula. This keyboard shortcut cycles through combinations of relative and absolute referencing. You can repeatedly press F4 after entering the cell name in a formula to cycle through the mixed references. For example, type =B2 and then press F4 to display =$B$2. Press F4 again to display =B$2. Press F4 again to display =$B2. And press F4 again to display =B2.

Let’s look at a practical example of how to use mixed cell referencing. In the following example, you want to calculate how much you’d receive in interest under three different portfolio amounts and three different interest rates. The most efficient way to perform this calculation is to create a single formula with appropriate references and then copy that formula to other cells. You need to create a formula that multiplies the interest amount in row 1 and the borrowing amount in column A.

Instead of creating nine different formulas, you’re creating only one single formula using mixed cell referencing, which you can then copy across, saving you time and reducing the possibility of error.

Follow these steps:

1. Download File 0601.xlsx from www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled 6-8 or create a blank workbook and enter and format the data as shown in Figure 6-8.
2. In cell B2, type = and then select cell A2 by clicking it or pressing the arrow key.
3. Press F4 three times to display the mixed reference $A2.

4. Type *, select cell B1, and this time press F4 twice to display the mixed reference B$1.

   To anchor the row, put the dollar sign before the row. To anchor the column, put the dollar sign before the column.

5. Press Enter to show the formula =$A2*B$1 in cell B2.

   The result is $18,750 as shown in Figure 6-8.

6. Select cell B2 and copy it across and down the rest of the block of data.

   Copying and pasting can be done in a several different ways. Choose the option that suits you best:

   • Go to the lower-right corner of cell B2 and select the fill handle with the mouse. Drag it across to cell D2 and release the mouse. Then drag down to row 4.
   • Copy cell B2 with the shortcut Ctrl+C, and select the entire block either with the mouse or by holding down the Shift key together with the right arrow and down arrow. Press Enter to paste.
   • Highlight the entire block of data either with the mouse or by holding down the Shift key together with the right arrow and down arrow. Use the shortcuts Ctrl+R and Ctrl+D to copy right and copy down.

7. Review the results.

   Note how your carefully crafted formula generates data for the entire block of cells. Compare your worksheet to the one shown in Figure 6-8.

Maintaining consistency of formulas is a fundamental technique of financial modeling that will save you and anyone else using your model a lot of unnecessary time in building, checking, and auditing the calculations.

When formulas make calculations based on data stored in your spreadsheet, use cell references wherever possible, instead of typing the actual data. Typing numbers into formulas is called hard coding, and it should be avoided unless the numbers are source data — and, if so, documented as such. This rule is also important to follow when building your model because it makes your models much easier to update, both for you and others using your model. If you’ve used cell references instead of hard-coded the numbers into the formula, and you change a value in a cell referenced by a formula, the formula automatically recalculates.

Understanding why you may want to use a named range

You don’t have to include named ranges in a financial model, and some of the best financial models don’t use them at all. Those who haven’t used them before sometimes struggle to see the benefits of including them in financial models. Most of the time, named ranges aren’t really necessary, but there are a few reasons why you should consider using them in a financial model:

• Named ranges can make your formulas easier to follow. A formula containing lots of cell references can be confusing to look at and difficult to edit. But if the cell references are replaced by a range name, it becomes much easier to understand. For example, the formula =SUM(B3:B24)-SUM(F3:F13) could be expressed as =SUM(Revenue)-SUM(Expenses) to calculate profit.
• Named ranges don’t need absolute referencing. By default, a named range is an absolute reference, so you don’t need to add any in.
• Using named ranges is ideal when you’re linking to external files. When the cell reference in the source file changes (such as when someone inserts a row), the formula linking to it will automatically update, even if the file is closed when the update is made.
• If you decide to use macros in your model, you should use named ranges when referring to cell references in the Visual Basic code. As with external links, this practice is more robust than using cell references.

In general, named ranges just make your life easier as a modeler. They make your formulas neat and tidy, easier to read and follow. You aren’t required to use named ranges in your model, but you should know what they are and how to edit them if you come across named ranges in someone else’s model.

Creating a named range

To create a named range, follow these steps:

1. Select cell B2.

2. In the Name box in the upper-left corner (see Figure 6-9), type over the name and call it something else, like Price.

   Note that the name you type must not contain any spaces or special characters. For instance, if you want to call it “Year 1 Price,” you need to name it “Year1Price” or “Year1_Price” or something along those lines.

3. Press Enter.

Named ranges don’t necessarily need to be confined to a single cell; you can also create named ranges for an entire range of cells, and these can be used in formulas. Simply highlight the range instead of a single cell, and type over the name.

Finding and using named ranges

Clicking the drop-down arrow next to the Name box shows all the defined names in the workbook, as shown in Figure 6-10.

Clicking the name in the drop-down box will take you directly to select that cell or range of cells included in the named range automatically. It doesn’t matter what sheet you’re in when you select the name. This can make finding your way around the named ranges in a model much faster. You can also press Ctrl+G to bring up a dialog box with all the names, or press F3 to paste names.

After you’ve created a range name, you can use that name in a formula instead of cell references. In the example shown in Figure 6-11, you can create the named range Price for cell B2 and the named range Units for the range A3:A7. In cell B3, you can use the formula =Price*Units to calculate the price, and then copy it down the column, as shown in Figure 6-11.

You can use a named range in a formula in several different ways:

• Simply type =price in a cell.
• Type = and then select cell B2 with the mouse to pick up the name of the cell.
• Press F3 and then double-click the name to paste it into a cell.
• Select the Formulas tab on the Ribbon and, in the Defined Names section, select the name you want to use from the Use in Formula drop-down list.

If you’re planning to use named ranges in your model, create them first, before you build your formulas. Otherwise, you’ll need to go back and rebuild your formulas to include the named ranges.

A cell does not need to be an input field in order to assign a name to it, although it often is in financial models. The cell can also contain a formula as well as a hard-coded input value.

Named ranges can be useful, but you don’t want to have too many. They can be confusing, especially if you haven’t been consistent in your naming methodology. It’s also quite easy to accidentally name the same cell twice. So in order to keep names neat and tidy, be sure to use the Name Manager to edit or delete any named ranges that are no longer being used. Note that copying sheets into a model can copy named ranges, which can also contain errors as well as external links you’re not aware of. This can slow down the file, so it’s a good idea to look through the Name Manager every now and then to tidy it up.

Editing or deleting a named range

You can manage all the named ranges you’ve created in the Name Manager, which can be found in the Defined Names section on the Formulas tab on the Ribbon. It’s easy to create a named range and forget it’s there, so try to keep your names tidy. If you need to remove a named range or find that you’ve accidentally named the wrong cell, you can add, edit, or delete existing named ranges in the Name Manager.

Internal links

In this section, you have some simple profit-and-loss calculations, and you’re going to create a summary report by linking between sheets. Follow these steps:

1. Download File 0602.xlsx from www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled IS.

   A completed version is also available in File 0603.xlsx, which you can download and use to compare your work.

2. On the IS worksheet, select the cell C4 and calculate the sales revenue by entering the formula =F3*F4.

   The calculated result is $29,502.

3. Go to cell C19 and calculate the manufacturing cost by entering the formula =F19*F3.

   The calculated result is $7,152.

4. Go to cell C20 and calculate the sales commission by entering the formula =F20*C4.

   The calculated result is $1,475.

5. Excel will sometimes put additional decimal places automatically, so change the number formatting to currency with no decimal places if necessary.

   You can do this by pressing the Decrease Decimal icon in the Number section of the Home tab of the Ribbon.

6. Check that the profit margin is calculating correctly.

   The calculated result in cell C25 is 20%.

7. Compare your results to Figure 6-12.

   Now you have your detailed P&L and you can create a summary on the first worksheet (Summary) using links.

8. On the Summary worksheet, select cell B5.

9. Link through the fixed costs by entering the formula =‘IS’!C15.

   Do not type this out. Instead, click cell B5, type =, and select the next tab using the mouse. Click cell C15 on the IS tab using the mouse, and press Enter.

10. Similarly, select cell B6 on the Summary worksheet and link through the variable costs by adding the formula =‘IS’!C21.
11. Go to cell B4, and link through the sales revenue by adding the formula =‘IS’!C4.

12. Compare your results to Figure 6-13.

    A good layout for a financial model is to have assumptions together on a single page, usually at the back. Let’s move the assumptions to a separate sheet at the back of the model. Don’t worry — this is a lot easier than it sounds!

13. Insert a new sheet by clicking the plus sign behind the last tab.
14. Double-click the tab name, and change Sheet1 to Assumptions.
15. Go back to the IS worksheet and highlight the area of the sheet that contains the assumptions (cells F1:G20).
16. Press Ctrl+X to cut the data onto the clipboard.

17. Go to the Assumptions worksheet, select cell A1, and press Ctrl+V or press Enter to paste the data to the new sheet.

    When you have data on the Clipboard, pressing Enter will paste the data and remove it from the Clipboard. Pressing Ctrl+V will leave the data on the Clipboard in case you want to paste it again. Either technique will work in this case.

    The formulas in this model work in exactly the same way as they did before we moved the assumptions to the new sheet. It’s important that you used cut and paste here, not copy and paste, or the links would not have worked properly.

18. Go back to the Assumptions worksheet and tidy it up. Remove the blank rows 5 through 18 by highlighting the rows, right-clicking, and pressing Delete.

Now you have a simple but tidy model. It links, it’s clear, it’s straightforward, and it’s easy for someone else to understand.

External links

So far, I’ve only been looking at creating links from one cell to another, either on the same sheet or on a different sheet within the same file. Sometimes, however, the data you want to link to exists in another file, so you need to link from one file to another. These are called external links. They’re created in a very similar way to internal links; simply type = and then select the cell in the file you want to link to, and press Enter. Working with external links isn’t as straightforward as working with internal links, however, so it does require a lot more care.

External links can be the cause of many problems, such as broken links, incorrect data, and error messages. Your model will be much simpler if you can avoid external links, but if you decide to include them, you should do so with caution. Most problems happen when users

• Change filenames or move the file to another location.
• Change the source file sheet name when the file linking to it is closed.
• Insert rows or columns in the source file when the file linking to it is closed.
• Email files that contain links.

Finding and editing external links

In the Connections group on the Data tab, click Edit Links. The Edit Links dialog box, shown in Figure 6-14, appears. Click the Change Source button to tell your model the new location of the file it has been linked to.

Another handy use for Edit Links is to break all links in a file. If you’re emailing a file, it isn’t recommended that you leave links in it. You could paste the cell values one by one, but breaking links will convert every single formula in the entire file to their hard-coded values. Click Edit Links, and you’ll be able to select the external files and click the Break Links button.

Sometimes your model will have links that simply won’t break! These “phantom links” are most commonly the result of links contained in named ranges. Deleting the names that contain external links from the Name Manager will remove them from your file. If that doesn’t work, other possible reasons could be links in conditional formatting, charts, objects, or PivotTables.

Restricting user data entry

For a practical example of how to use data validation, let’s take the Project Costings Analysis from the “Absolute cell referencing” section earlier in this chapter (refer to Figure 6-6). Your colleague is using the model you’ve built and he can tell by the way in which cell D3 has been formatted (with shading) that you expected people to make changes to it. He’s not sure anymore how many days this project is going to continue, so he types TBA into cell D3 instead. As soon as he types TBA, that really messes things up! As you can see in Figure 6-16, the formulas you’ve already built were expecting a number in cell D3, not text.

Instead of allowing the user to put anything into any cell, you can change the properties of this cell to allow only numbers to be entered. You can also change it to allow only whole numbers or numbers in a given range.

Follow these steps:

1. Download File 0601.xlsx from www.dummies.com/go/financialmodelinginexcelfd and select the tab labeled 6-17.
2. Select cell D3.

3. Go to the Data tab on the Ribbon and press the Data Validation icon in the Data Tools section (see Figure 6-17).

   The Data Validation dialog box appears (refer to Figure 6-17).

4. On the Settings tab, in the Allow drop-down list, select Whole Number; in the Data drop-down list, select Greater Than; and in the Minimum field, enter 0.

Now only allow whole numbers greater than zero can be entered into cell D3. Try entering text such as TBA. Try entering a negative value. Excel won’t allow it, and an error alert will appear.

If you want, you can enter a warning message on the Input Message tab of the Data Validation dialog box. For example, you might want the following message to appear: “Warning! Only enter numerical values.” On the Error Alert tab, you can enter another message that appears if someone ignores the warning and tries to enter invalid text. I’m usually tempted to type something mischievous, such as: “Invalid entry. Your hard drive will now be completely erased.”

Creating drop-down boxes with data validations

Not only does the data validation tool stop users from entering incorrect data into your model, but you can also use it to create drop-down boxes. In the Data Validation dialog box, from the Allow drop-down list, select List, as shown in Figure 6-18. In the Source field, enter the values you’d like to appear in the list with a comma between them such as Yes, No. A simple drop-down list is created in cell B12 with only two options: Yes and No. The user can’t enter anything else.

No one can enter a value in a cell that goes against your data validation rules, but it’s still possible to paste over a cell that is restricted by data validation. In this way, users can inadvertently (or deliberately) enter data into your model that you did not intend.

You can also create a drop-down list that links to existing cells within the model. For example, in Figure 6-19, I don’t want the users to include a region that is not included in the list shown in column F. So I’ve used a data validation list, but instead of typing in the values (which would be very time-consuming), I can link to the range already containing the regions — $F$2:$F$5 — which is a much quicker way of inserting a drop-down list.

Because I’ve linked the drop-down list, this drop-down is now dynamic. If someone edits any of the cells in the range F2:F5, the options in the drop-down list will automatically change.

Protecting and locking cells

You can also add protection to your model by going to the Review tab on the Ribbon and clicking the Protect Sheet button in the Changes section. Enter a password if you want one, and click OK. This will protect every single cell in the entire worksheet, so no one will be able to make any changes at all! If you want users to be able to edit certain cells, you’ll need to turn off the protection, highlight those cells (and only those cells you want to change), go to the Home tab on the Ribbon, and click the Format button in the Cells section. Deselect the Lock Cell option that appears in the drop-down list. Turn the protection back on again, and only the cells that have been selected will be unlocked.

Keep in mind that it’s reasonably easy to crack an Excel password (search the Internet for Excel password cracker), so if someone wants to get in and make changes to your protected model, he can. I recommend that you treat Excel passwords as a deterrent, not a definitive security solution.

Limiting project costs with a goal seek

What a goal seek is and how it works is best demonstrated using a simple model. For a practical example of how to use a goal seek to limit project costings, follow this series of steps as shown.

Again, let’s take the Project Costings Analysis from earlier in the chapter (refer to Figure 6-6). As shown in Figure 6-20, I’ve used simple formulas to calculate the total cost of a project based on the number of days worked, giving a total costing of $146,769. Unfortunately, however, I’ve only budgeted for $130,000 in staff costs. If I want the project to come in under budget, I need to know how much I need to cut the days worked by. I can manually tweak the number of days that has been input in cell D3, but it would take a long time to get the number exactly right. By using a goal seek, I can do it in seconds:

1. On the Data tab of the Ribbon, in the Forecast section, select What-If Analysis and then select Goal Seek.

   The Goal Seek dialog box (shown in Figure 6-20) appears.

2. In the Set Cell field, make sure the cell contains the outcome you want, the total cost in cell D10.
3. In the To Value field, enter the number you want D10 to be, $130,000.
4. In the By Changing Cell field, enter the cell you want to change, the project days in cell $D$3.

5. Press OK.

   The number of project days in cell D3 automatically changes to 53.1446540880503, which is a lot more information than you probably need! Round it down manually, by typing 53 into cell D3, which will change the total costings so that they come just under the $130,000 target you needed.

If you tried to manually enter a number with decimal places into cell D3, the data validation you created earlier in this chapter in Figure 6-6 would not allow it. Because a goal seek is essentially pasting the number into the cell, it circumvents the data validation rule, as though you had copied and pasted the value.

Calculating a break-even point with a goal seek

Using goal seek is also very helpful for break-even analysis. In this section, you perform a simple break-even calculation using a goal seek. (For more detail on break-even analysis, see Chapter 9.)

For a practical example of how to use a goal seek to calculate a break-even point, let’s work with the model you built earlier in this chapter. You’ve linked it through in such a way that if the number of units sold changes, the revenue changes, and so does the variable costs. You’d like to know the minimum number of units you need to sell in order to cover costs (the break-even point). Follow these steps:

1. Go to www.dummies.com/go/financialmodelinginexcelfd and download and open File 0603.xlsx.
2. Go to the Assumptions worksheet, and try changing the number of units sold from 8,940 to 8,000.

3. Go back to the IS worksheet, and you’ll see that the profitability has dropped from 20% to 14%.

   You could continue to do this manually until you reach zero, but a goal seek will be much quicker and more accurate.

4. On the Data tab on the Ribbon, in the Forecast section, select What-If Analysis and then select Goal Seek.

   The Goal Seek dialog box appears (see Figure 6-21).

5. In the Set Cell field, enter the cell that contains the outcome you want (the profit), C24.
6. In the To Value field, enter the number you want C24 to be, 0.
7. In the By Changing Cell field, enter the cell you want to change (the number of units on the Assumptions page), $A$3.

8. Press OK.

   The number of units in cell A3 on the Assumptions page automatically changes to 6,424, which is the break-even point.