Advanced linking

What's covered in this chapter:

• Combining link navigation with link creating to get super-efficient
• Linking to multiple line items at once.

When there are a lot of links in our model, navigating around the model becomes very efficient. As a quick refresher, to navigate using links, we can either use the built-in Excel keystrokes:

Or we can use the Productivity Pack keystrokes:

We've also seen that the sequence to create a link is:

Combining navigating using links with link creation

We can combine these two skills to get very efficient at creating cross worksheet links.

In this example, we have modelled revenue and linked it to the financial statements. We now want to do the same with Operations and Maintenance cost (O&M cost). O&M cost has already been sign switched as is ready to go.

Imagine we are starting the process while looking at the financial statements. We have a link to revenue already, and a placeholder for O&M cost:

Step 1: Use the revenue link to hop back to the Ops sheet

You'll land on revenue. Move down to Operating Cost.

Step 2: Copy the label of Operating costs (the first step in our link creation process

Step 3: Hop back to the financial statements

You'll land back on revenue on the financial statements. Move down to the O&M cost placeholder.

Step 4: Create the link

Step 5: Copy the link across

In this example, because we are replacing a placeholder with a link, we will also want to clear out the placeholder yellow shading. We can clear shading with Ctrl+Shift+c

Linking to multiple line items

We have created the Ctrl+Shift+q "Quick link" macro so that it can link to multiple line items simultaneously.

In this example - we have four inputs for seasonability adjustment. We want to create links to all four in our revenue calculations.

Step 1: Select all four items

Step 2: Copy the labels

Step 3: Navigate to where we want to put the links & Create the link

The macro will create 4 row anchored links, ready to be copied across. Note that the macro will apply the correct import formatting if required.

Step 4: Copy across

The "copy across" macro will copy all four line items over simultaneously. If you are linking to a constant, the macro will only copy across as far as column H. If you are linking to a series line item, the macros will copy over to the end of the timeline.

Comments

Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.