Core modelling skill 7: Copying a calculation block
Download the reference file to practice:
To obtain the worked example file to accompany this chapter buy the financial modelling handbook.
When I introduced Core skill 5 (Creating a calculation), I recommended not to apply row anchoring in a calculation.
Row anchoring in a calculation makes it harder to copy the calculation block.
Compare these two examples.
Example 1 - Copying a calculation block where the elements are row anchored.
In this exercise, we want to copy the electricity generation block. Perhaps we want to add a calculation showing a P90 yield. (Don't worry if you don't know anything about "P50" or "P90" yields in renewables - they represent different probabilities of their being a certain amount of renewable energy resource).
In this first example, the modeller has row anchored each calculation element:
The steps to copy the calculation block:
Step 1: Select the rows we want to include
Step 2: Select the whole rows, creating the copy areae
Step 3: Copy the block
Step 4: Position your cursor where the top line of the copied version of the calculation block will be.
Step 5: Select that row
Step 6: Paste the block
When we trace precedents on the new calculation block, each element is still pointing back at the old block.
To use the new block we’d have to go through and edit each element of the formula. This is not what we want.
Example 2: No row anchoring in the calculation
In this example, we start with this calculation block that has none of the element row anchored.
If we repeat the above steps, the result looks the same. However, when we trace precedents, we see that each block element refers to the new block's ingredients. They don't point back at the old block. We don’t have to do any editing to the formula. We just have to replace the ingredients.
This is exactly what we want.
We never know when we may want to copy one of our calculation blocks in the future. Therefore we set them all up without row anchoring to all be copyable.
Creating space for our copied calculation block
Sometimes we want to copy a calculation block and insert it between two existing blocks.
To do this, select the block following the steps outlined above.
When selecting the rows to copy, be sure to include a blank row.
Copy the block, then position your cursor on the row where the first row of the new block will be. In this case, this is "on top" of the existing block.
If you press Enter at this stage, you'll paste over the top of the existing block.
This will insert the correct number of rows and paste down the clipboard contents.
Note: In reality the keystroke is Ctrl and "plus" / "+".
On most keyboards, the plus symbol is on the shift position of the equals / = key; therefore, the full keystroke is Ctrl+Shift+=
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.