INDEX Function in Excel: Returns a value or the reference to a value from within a table or range. Learn how to apply the INDEX function for financial modeling purposes.
What is the INDEX function?
INDEX is a powerful tool for making references within a financial model. Since the function returns values from an array depending on a selected row and, optionally, column number, INDEX is especially useful for considering different scenarios in a financial model.
How to apply the INDEX function in Excel?
The INDEX function uses three inputs, of which only two are required.
- The first argument, array, requires selecting a range of cells or an array constant. If the selected array only refers to one row or one column, the subsequent row_num or column_num argument is optional.
- The second argument, row_num, is required unless the third argument, column_num has an input. Row_num selects the row within the array from which to return a value. If the argument is left blank, the third argument column_num is required.
- The third argument, column_num, is optional unless the second argument, row_num, is omitted. Column_num selects the column within the array from which to return a value.
How to use INDEX in a scenario manager of a financial model?
Using the INDEX function for setting up a scenario manager in a financial model for renewable energy investments is a must.
In the above example, an input sheet for time-independent variables uses the INDEX function for setting up a robust and flexible scenario manager.
When considering renewable energy investments, banks often require a different case for financing and debt sizing purposes than equity investors. Debt providers typically consider the P75 or P90 energy production value for their analysis, while equity investors rely on the P50 energy production value, reflecting a less conservative investment view.
In the above financial model, five cases are set up while a live case in column M flows through the dependent calculations and integrated financial statements. The end user can select the live case in cell M3 via a dropdown menu. The selected live case is the second argument for the INDEX function. The first argument is simply the row of the respective input line.
In the above example, this is the P-value for the energy generation selection in row 104, resulting in an INDEX(O104:S104,$M$3) function, which will return P50 given that the second case is selected.
How to properly apply the INDEX function in a financial model for renewable energy investments?
Do you want to learn how to model the INDEX function within a financial model created explicitly for renewable energy investments? Then check out the Advanced Renewable Energy Financial Modeling course.
Article sources
