This is the third in my series of tips for how to do financial modeling more easily in Excel. If you have read the two earlier posts (here and here) you will notice that I frequently refer to logic as a key part of good modeling. I am not exactly referring to Aristotle's three laws of logic, but I may be using logic in a way consistent with Boolean algebra. Basically I believe that Excel can capture any way of thinking or "logic" that we can conceive in a model. Perhaps an example will clarify.
Suppose we are building a ship for $25 million dollars. We are using $4 million in equity and $21 million in bank financing and the equity is drawn down first. All interest is capitalized during construction. We may change the mix of equity and bank financing and the logic we have to build in Excel is how to have the bank financing start after all the equity is used (with no hard coding of any number). Click on the following link to download the model. Download Example.xls
First notice that the financing assumptions for equity and debt are blue bold in Column B to indicate that they are assumptions and any changes to these values should flow through the model without any changes required to any other part of the model. If I had a full model with a Sources and Uses Statement, these assumptions would have linked back there and would have been unbold black.
The logic that forces the bank debt to start when the equity reaches $4 million is in Row 16 starting at cell C16. The formula I used is =IF(C12=$B$9, SUM($C$6:C$6)-C12-C15,0). To limit the equity to $4 million I used Row 11starting with cell D11 and the formula is =IF(C12=$B$9,0,MIN(D6,$B$9-SUM($C$11:C11))). The IF command is the key to building the logic in these examples but the logic also required nesting some other commands inside the IF statement, such as the use of the MIN command to minimize two values. By taking the time to build these IF statements I avoid hard coding any values and do not have to rebuild the formulas if the equity or bank debt assumptions change.
Another point about building models. Never use the B Column for period values (Month 1, Month 2, etc.) Column B is for assumptions, to represent prior period values of zero and for command criteria indicators, such as in =SUMIF(range, criteria,[sum range]). Suppose you build a model that includes expense budgets for 100 departments each with six categories of expense. After you build the department budgets your boss says he changed his mind and he wants to know the total expense by each expense category. You have two choices--build six formulas each with 100 links (and hope you don't miss a department) or you can build one formula using SUMIF and copy it five times for the other five categories. A simple example of SUMIF can be downloaded in the following link.Download sumif.xls
In both of the models included here I have made frequent use of the $. $A$4 means that this cell remains unchanged when it is copied; for example when an assumption remains unchanged over all periods in a model. $A4 means that the column is fixed and the row changes when the cell is copied (A4, A5, A6). A$4 means that the column changes but the row is fixed when the cell is copied (A4, B4, C4); for example where you reference the same row across multiple periods in different parts of a model. If you subscribe to my view that you build one period models and then copy>paste for all the other periods, the $ is very useful.
Note: I have no idea where the Blue Bold convention comes from to indicate assumptions, but it is widely used in financial modeling.