Using flags in Excel is a powerful tool for improving financial modeling timelines. Best practice financial models for project finance, such as renewable energy financial models, require a financial modeler to master flags.
So what is a flag?
Using Excel, a flag is a cell with a binary output - the cell either contains the value 1 or 0. Consequently, flags come in handy to turn a formula on or off by multiplying the respective formula with the flag.
Modeling clean and robust timelines with flags.
To properly display timelines in a financial model, using flags is a must. Below is an example of a best practice timeline in a project finance model for renewable energy investments. The green flags display the construction period, while the blue ones represent the operational period of the solar park.
The user can easily grasp that the construction of the solar park takes place in the full year of 2023, and thereafter, the park's operations start.
How to model a flag?
There are different types of flags, but we will take you through modeling the most common ones using best practices.
1. Flags for modeling a timeline.
In the above example, all we need to model the construction and operations flags is the start and end dates. To ensure that the dates are always properly reflected, combining quarterly or annual inputs with the EOMONTH function is recommended.
Once the dates are set up in an input sheet, they can be pulled into the timing sheet. To model the flag, we want the output to be 1 if the date falls between the start and end date. Otherwise, we want a 0 to be displayed.
Using an IF function in combination with an AND function will do the trick. If the start date of the model timeline is equal to or larger than the input start date AND if the end date of the timeline is equal to or smaller than the input end date, a 1 should be returned; otherwise, 0.
Modeling timelines in combination with flags is the most common use for a flag. It is a powerful and clean tool to turn on calculations such as revenue streams, operational expenditures, power generation, debt repayment, and much more.
Below is another example of how useful flags can be when modeling sculpted debt repayment structures.
The end user can easily see the underlying timelines for the commitment period, the loan tenor, the grace period, and the debt repayment period.
2. Flags for one-time events.
Flags can be very helpful for one-time events such as the initial funding of the Debt Service Reserve Account (DSRA). In the below screenshot, you can see that the initial funding of the DSRA occurs as a one-time event in the last construction quarter.
3. Flags for checks of the financial model integrity.
Flags are also helpful in discovering errors within a financial model. In a best practice model a balance sheet check is a must. If there is a discrepancy between net assets and equity, a flag can help to easily visualize which period causes the error within the model.
How to use conditional formatting to display a flag properly?
To open Conditional Formatting, on the Home tab, in the Styles group, click Conditional Formatting.
Using conditional formatting, a rule can be implemented stating that if a cell contains the value 1, then certain formatting shall apply.
After the rule has been implemented as above, you can adjust the formatting by pressing Format.
First, formatting the borders will ensure that the flags visually stand out from the other cells.
Second, formatting the Fill with a distinct pattern style and color will ensure that everyone using the model can spot active flags instantly.
How to build an advanced financial model for renewable energy investments?
Do you want to learn how to adequately implement flags within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
