The Excel model attached to this post is to show modelers how to (1) create and use data tables and (2) insert words into numerical cells.
The Data Table function is a great tool that allows you to show numerous results for various return metrics while a critical measure of the model changes. In other words, it is a great function for doing and showing sensitivity analyses. For example, you can have data tables that show how the IRR and Equity Multiple will be effected by changes in the holding period (as in this model), or how different exit cap rates will effect the exit price. There are numerous tables that one can create. Data Tables are dynamic and will change according to your new inputs as you update the model.
Additionally, the model shows you how to add text to numerical cells. Adding text to a number cell can provide better clarity and allow for easier understanding for both the creator of the model and third party users. For example, let’s say you have the number five in a cell that is meant to represent year 5 of the investment. If you want the cell to read ‘year 5’ but still need the number five for use in a formula in another cell, you cannot simply type ‘year 5’ in the cell because excel won’t understand it to be a number any longer. However, there is a way to put text in numerical cells and still have Excel know that the cell is a number.
The attached Excel files are step-by-step instructional guides for how to use both of these functions in your models.
Any questions about these functions, please feel free to reach out.