I was working with a client the other day and while reviewing a modified version of one of our Excel Model Templates, I noticed that they had done something dangerous: customized a row’s formula in a single cell to apply to an exception that was unique to the time period associated with that one column. I used to do this and have been burned by it enough times to now avoid it altogether. Instead, I take the extra time to write a formula for the row that will apply to any and all exceptions over the time series such that if (more likely when) that exception shifts in time during the course of modeling as assumptions change, my formula can handle it without me having to remember to update the cell in which the exception occurs.
Example: I have a single period in which a land loan is paid off by the senior construction loan. The senior loan Draw formula would employ a “+ IF” component that will trigger the additional draw of the land loan repayment amount in the period in which the land loan repayment amount is less than 0 (or greater than 0 if repayment is treated as a positive value). In generic terms, this would be the formula add-on:
+ IF(land loan repayment in current period < 0,land loan repayment amount,0)
Just as the saw goes “The easiest story to remember is the truth”, the easiest formula to maintain is the one that is constant across the entire span of the model timeline i.e., the one you don’t have to maintain!
What are your thoughts?