Scheduling Uses
Overview
When creating a detailed budget for an affordable housing development you cannot accurately calculate the construction loan interest without a draw schedule and you cannot create a draw schedule unless you have a schedule of sources and uses. This training shows you how to add timing information to a detailed line item budget and create a clean report showing that timing.
Structure
Whenever practical build your financial model with inputs separate from calculations, separate from reports. This gives the model user a convenient environment to input data; gives the model author unlimited space to do calculations; and gives the model reader concise and well formatted information. Trying to do everything on a single tab is hard to use, design, and read.
Dynamic Labels
It is convenient for the model user and reader to know what month each development milestone is planned to occur. You can either include static labels in various locations or you can create dynamic labels that update when milestone timing assumptions change. The latter is a faster more robust method.
To include the milestones labels in the example above, we first make a list of Milestones including abbreviations and month numbers, like the image below.
Next we use the INDEX and MATCH function to lookup the abbreviations (if there is one) for any given month number.
Coding: The INDEX function retrieves (aka looks up) a value from an array of cells
The MATCH function tells how far down an array of cells a given reference was found
Used together they retrieve a value that corresponds with a given reference
Cell C2:AM2 contains month numbers 0-36
Cell C1= INDEX( Milestones[Abbreviation], MATCH( C2, Milestones[Month],0))
Coding: To avoid seeing errors, wrap the function above with IFERROR( … ,"")
Inputs
To add the functionality for model users to schedule uses we are going to add 39 columns to our detailed line item budget, [Start], [End], and 37 columns for up to 36 months named [0m] – [36m].
The [Start] column will signal that the cost will be incurred first in that month. The [End] column will signal that the cost will no longer be incurred after that month. We will later design the model to evenly spread the cost of that line item from the start month to the end month.
The other 37 columns will be use when a cost is not evenly spread and the model user wants to provide a custom breakdown of when the cost will be incurred. Take architecture for example, the majority is paid at closing (month 0) but the supervision cost may not get paid until completion (month 14). Since we don’t want the model to evenly spread the cost we leave [Start] and [End] empty and put 800,000 in [0m] and 100,000 in [14m].
Coding: The convention I use assumes [End] is the same as [Start] if you don’t specify an [End], meaning the entire cost is incurred in a single month.
Warning: To avoid duplicates, the model ignores custom scheduling when [Start] has a value in it.
Calculations
On the calculations tab we need to convert the inputs from the user into costs in their relevant months. To do so we (a) use the INDEX function with the ROW function to retrieve data (b) create helper cells for granular computations, and then (c) create an formula that interprets the user’s inputs.
Coding: The ROW function tells you what row a cell is in. It is used with the Autocomplete feature in Excel to put sequential numbers into a set of cells, like 1, 2, 3, …
When used inside the INDEX function it allows you to retrieve data from an array, one row after another. See cell Calc!D2 for an example.
Warning: Whenever you use the ROW function, avoid adding or removing rows in that tab. Doing so will create skips in your sequence. If you must add or remove rows, fix the sequence by using the Autocomplete feature again to get the sequence back in order.
Instead of telling you what formulas you will need, I’m going to brainstorm out loud so you can see how the process of designing a tool works (items to be coded will be shown in purple).
What do I want to happen? I want a cell that will tell me how much money needs to be spent in month 0 for a single line item. I need to be able to use Autocomplete to drag the cell down so it addresses every line item and I need to be able to drag it right so it addresses every month.
How will Excel know if a given cost is incurred in a given month? The user put [Start], [End], and custom scheduling information. How will Excel know which method to use [Start] or custom? I will always give the [Start] method priority.
How will Excel honor the [Exclude] column in the Uses table? It will need to test for this and ignore any cost that should be excluded.
What if the user doesn’t provide an [End] month? I will make it the same as the [Start] month.
How will the model know how much of a cost goes in the relevant months? The amount divided by the count of months.
Logical Tests
Excel’s most powerful feature is the ability to alter calculations based on a logical test. Logic such as, “If the cell is negative do this, otherwise do that” would be impossible without logical tests.
Our brainstorming determined that we need three tests (1) is the line item excluded, (2) is [Start] empty, and (3) is [End] empty. While we could code these tests directly into our functions I have decided to give them cells of their own. This reduces strain on the processor if you are going to use them multiple times (we are going to use them about 40 times each).
Let’s look at the test in cell Calcs!A2.
=INDEX(Uses[Exclude],ROW(A1))>""
This cell test whether row 1 of the Uses table has any text in the [Exclude] column. If there is it will return TRUE if that cell has no text it will return FALSE.
Coding: The values TRUE and FALSE are not letters but rather Boolean variables. Excel capitalizes them to communicate that point.
One useful tip is that they can be treated as 1 and 0, respectively, if you put them in parenthesis and perform any mathematics on them. For example: (TRUE) * 2 = 2
Helper Cells
Before we get into the big calculation we should first determine what partial cost will be used when the [Start] method is employed and the month in question falls within the [Start] – [End] range.
The portion should be the total expected cost divided by the quantity of months it will be incurred.
Portion is Amount / ( [End] – [Start] + 1 )
Cell I4 = E4 / (G4 – F4 + 1)
To honor our [Exclude] column we multiply the portion by a test that if false will “exclude” it.
Cell I4 = ( E4 / (G4 – F4 + 1) ) * NOT( A4 )
Coding: Cell I4 would be interpreted (150,000 / ( 0 – 0 + 1) ) * The Opposite Of ( FALSE ) or
150,000 / 1 * TRUE or
150,000 is the portion that should be counted in every applicable month
Warning: The actual cell in the companion spreadsheet use a helper cell for “quantity of months” and employs an additional nullifying test (whether the [Start] cell is empty).
Planning Formulas
Instead of diving straight into the big formula in cell Calcs!J2 I’m going to again brainstorm about it.
=IFERROR(IF(NOT($A2),IF($F2="",INDEX(Uses[0m],ROW($A1)),IF(AND(J$1>=$F2,J$1<=$G2),$I2,0)),0),0)
Sometimes with complex formulas it is useful to create an outline in plain English using MS Word and then converting those words to code before transferring it into Excel for testing. Accompanying this whitepaper is an example of such a brainstorm.
Since we have two different methods of determining what portion of the cost occurs in a given month we will need an IF function to decide between [Start] or custom method. The test for this will be if [Start] = “” then use the custom method otherwise the [Start] method.
The custom method only requires looking up a value but the [Start] method must further test whether the month in question is within the relevant range. Because we have a range, that test will need to combine two tests (1) greater than or equal to [Start] and (2) less than or equal to [End].
We will need to wrap this function in an IF statement that honors the [Exclude] feature on our detailed line item budget.
We will need to wrap this function in an IFERROR statement to turn any errors into 0 so we don’t break the model by doing mathematics on errors.
Take a moment to review the accompanying FormulaBrainstorm.docx and the formula used in cells Calcs!J2 through AP27.
Coding: The formula includes a column reference to Uses[0m], a column in the detailed line item budget. When using the Autocomplete feature and dragging to the right, Excel will change this column reference to Uses[1m], Uses[2m], etc.
While we want this to happen in this example, you won’t want the column reference to change in other applications. To avoid the change use the copy paste feature instead of Autocomplete or wrap the column reference with the INDIRECT function.
Reports
Reports are much simpler to code than the calculations themselves. Simply design the look you want and reference the cells on the Calcs tab that have the data you want to display.
However one tip I’d like to mention is avoid calculating totals using numbers from the Report itself. Instead calculate totals using the cells on the Calcs tab. This will protect you from communicated inaccurate totals when your Report area isn’t big enough to display all the line items that the user created on the Input tab.
The Calcs tab should have a large enough area to catch new lines, but since the Report is often built to fit on a printed page it may not include enough rows if the user went crazy adding items.
Error Handling
Always test your model and find ways to break it. Then build safeguards or warnings to address those. While testing this tool I realized that (a) I could schedule custom expenditures that don’t add up to the line item’s total and (b) I could put an [End] month that is less than the [Start] month.
Without getting into macros, the simplest method of warning users is conditional formatting. Therefore I selected the cells Input!A3:E15 and told it to change the background color to pink if one of those two problems occurred. The conditional formatting logical test is:
=AND($A3="",OR(AND($D3="",SUM($F3:$AP3)>$C3),AND($E3<$D3,$E3>"")))
Here is what this test says in English:
Show a pink background if:
a)[Exclude] is empty, and
b)Either:
- Both of these happen:
- [Start] is empty, and
- The sum total of [0m]-[36m] is different than [Amount]; or
- Both of these happen:
- [End] is greater than [Start], and
- [End] is not empty.
Conclusion
I realize this training got deep into the weeds, but I know these are time saving, error saving, and cost saving techniques. The end result of your efforts will be a user friendly method of scheduling when uses will be incurred, a concise report needed by your lender and investor, and the bones needed for calculating construction loan interest and preparing a draw schedule.
Practice
- What should you avoid doing when using the ROW function?
- What does the INDEX function do?
- Why should you separate Inputs, Calcs, and Reports?
- Why doesn’t the Calcs tab look pretty?
- How do you schedule a cost to be incurred in a single month?
- Why is it critical for the total of [0m] – [36m] to match [Amount]?
- What does 10 * AND( TRUE, FALSE) equal?
- If cell A4 = 3, What does (5 + 5) * A4 > 2 equal?
- What does AND( OR( TRUE, FALSE), NOT( AND(TRUE, FALSE))) equal?
- Does conditional formatting require an IF statement or a logical statement?
Questions?
If you have questions, call your helpful housing analysts at Kingdom Development, Inc., a California nonprofit public benefit corporation.
951.538.6244
714.357.1637