In this exercise we want to consolidate a number of buildings we own into a total, but we may want to sell or buy some buildings and we don’t want to have to keep changing the formula.
Question
On the appropriate sheets
- Look at the setup and information in Building 1 to Building 4
- Note that on each sheet the same type of information is exactly in the same place.
- Note the Beg>> and <
- Go to the Consolidation sheets and do the following
- In G7 row use the Summing Through Sheets formula to add up all the units from Beg to End. You do this because as some stage you may want to remove Building 1 or Building 4 and can’t do it if you refer to them as the first and last part of the formula
- In G12 row do the same as above for the revenue
- In G13 row do the same as above for opex
- Go back to G8 row and calculate the average rent (total rentals / number of units)
- Go back to G9 row and calculate the average opex (total opex/ number of units)
- Go to H2 row and calculate the rental escalation factor
- Go to H3 row and calculate the opex escalation factor
- Notice the number in F17
- Remove Building 1 from the calculation by dragging the sheet outside of the area between Beg>> and <
- Go back to the consolidation sheet and look at F17 again
- Play with moving Buildings in and out of the area
- Create your own building (copy the sheet exactly), capture some inputs, and add it to the area to see the impact
To see the full course contents click here.