Anchoring

Overview

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

In this lesson we’re going to start with a rather fundamental concept called anchoring. If you want to build a financial model, it’s impossible to become even an average modeler without using this key concept. Every model you see will use anchoring and we will cover this hundreds of times in the course.

So to illustrate the concept of anchoring, let’s look at a simple multiplication table. Here we want to multiply cells B1 to D1 with A2 to A4. For example, we want to see 2×1 in B2, 2×2 in C2, and 2×4 in C3. How do we do this with only one formula within this whole table? We will create a single formula in cell B2 and copy it across the rest of the table.

To understand this in more detail, let’s now go into Excel and see how best to approach the challenge.

And we’re now in Excel with the same table that we saw on the screen a short while ago. We want to type into here B2=B1xA2. So 2×1 and now press enter. And remember I want one formula across this whole table and so let’s do a quick test. I’m going to copy this cell B2 into C2 with ctrl+C and now ctrl+V to paste, and I want to evaluate this formula;

I want to see if it’s linking to the right places. And so I can press the function key F2. When I press F2, we can see the cells that this cell uses or refers to. It links to B2 and also to C1. And if I want to have one formula across this whole table, there’s clearly an issue here.

I don’t want this column to move to the right or this reference to move to the right as I copy across to the right. I want this red cell to have stayed over in column A, and I can do that by locking or anchoring. So if I go back to this cell. If I want to stop the column moving to the right, I can put a dollar sign in front of the A.So I can either do it by putting a typing a dollar sign in manually, or if I highlight A2 and press function key F4, or in Mac command+T once, twice, three times, it’s got a dollar sign in front of the A. So I’ll press enter and now I’ll copy across to the right again with ctrl+C, ctrl+V. If I go back into this cell and press F2, we can see this reference to A2 has not moved across to the right.

This dollar sign in front of the column reference has locked or anchored it. And remember I also want to copy this down so let’s test that by ctrl+C, ctrl+V. I go in here and press F2, I’m still looking to column A that’s great. But what’s happened is my row reference, which was C1, is now linking to C2. And I don’t want this reference row to go below row 1. So I can put a dollar sign in front of the row reference. But the row reference here is the one, the number one. So I can put a dollar sign in here.

So if I now copy this cell to these two cells with ctrl+C and ctrl+V, I’d test the cell again by F2, it’s now keeping the column reference as A, because of my dollar sign here, and my row reference as 1, because the dollar sign here. So if I copy this formula across the whole table with ctrl+C and ctrl+V, it’s now giving me these results and I’ll check a couple or click in here and press F2, 4×3 that’s correct. Down here, 8×2 that’s correct.

So I’ve now achieved one single formula across this whole table, and that’s how we anchor.