Using Conditional Formatting in Real Estate Financial Modeling
Your real estate financial models are only as good as their ability to be used by others. Or in other words, if others can’t figure out how to use your model, it isn’t worth much! So when building real estate financial model templates, besides using Modeling Best Practices, I personally employ a variety of techniques to guide the user as they navigate the model and while inputting assumptions.
One such technique, is to use Conditional Formatting.
Note: This topic originally came up in a lengthy forum thread in our real estate financial modeling Accelerator training program. I created a video response on how I use Conditional Formatting in construction cash flow forecasting, and this mini-tutorial was born.
I use a simple construction cash flow module to demonstrate this technique.
What is Conditional Formatting in Excel?
Every cell has some base formatting. At any time, you can manually change that formatting depending on the needs of your model. However, sometimes you may want the cell to have different formatting depending on what’s in that cell, or in another cell. That’s where Conditional Formatting comes in.
Conditional formatting is a tool available in Excel (and Google Sheets) where you can set the formatting for a given cell to change dynamically based on some logic statement you write.
So for instance, imagine you want to highlight all cells greater than a certain value. Conditional Formatting can do that for you. Or perhaps, you want to hide all columns beyond a certain date. Conditional Formatting can do that as well.
When to Use Conditional Formatting in Real Estate Financial Modeling?
When to use Conditional Formatting in your models really depends on what you’re doing. But there are certain instances when this comes up more often than not.
For instance, I use Conditional Formatting to change black font (i.e. calculation) cells to blue font (i.e. input) cells based on an action by the user. So in the case of floating rate vs. fixed rate debt, the user would toggle from Fixed to Floating, and a row of interest rate inputs would change to blue to signal to the user to change those monthly values.
Other circumstances where Conditional Formatting comes in handy including hiding (or graying out) unnecessary sections or cells, changing the number formatting dynamically, highlighting certain values of interest, or in the case of the tutorial below, guiding the user to enter values where they’re meant to be entered.
Video Tutorial – Using Conditional Formatting in Real Estate Financial Modeling
I recently received a question in our Accelerator Forums related to a Conditional Formatting rule I use in my Actual + Forecast Construction Draw Schedule with S-Curve. In that model, the user enters actual construction cash flows that have occurred to date, and then the model forecasts the remaining cash flows dynamically.
I added Conditional Formatting logic to that model, so as to guide the user to which months to enter actual cash flows. Or in other words, the months that are eligible for actual cash flows are visible while future months are grayed out. The logic is based on a date assumption entered by the user.
In answering the question, I created the following video and then thought this would be a nice companion to a blog post on the subject. So download the completed file from the tutorial below and then watch the video.
- Click here to download the file created in this video tutorial
