|
Listen to this post if you prefer
|
Column graphs can help us quickly analyze important data series in our models, and also identify potential errors that stick out to us visually more so than they might in a row of numbers. While having graphs set up in your templates is a recommended best practice, a data series that is much shorter or much longer than the default x-axis duration won’t show up in a meaningful and useful way, as seen below.
The good news is that with a little bit of investment the graph x-axis can be made dynamic through the use of the Name Manager and the OFFSET function.
You can follow along in the Excel file here.
Introduction to Name Manager
With Name Manager you can create a reference name for a selection of cells or even a formula so the given data or calculation can be accessed or referenced again quickly. For example, if there was a particular variable that would be referenced in formulas throughout your workbook, you could use the Name Manager to create a reference name for it. This new reference name could then be used in a formula, so you would not have to go back and click on the desired variable’s cell when writing the formula. The Name Manager is accessed from the Formulas section of the toolbar as shown below.
With Respect to graphs, the column or row data can be named. In the example below you could name the highlighted row selection “sponsor_draw” for example. (Note that the data selected reaches all the way to column NK which is not captured in this image.)
Now if you had a graph that referenced this data range you could edit the Series input to reflect the name you created. In this example it would have currently read =’Direct Project Costs Example’!$D$5:$NK$5”. To implement the new name you would change it to read “=’Direct Project Costs Example’!sponsor_draw”. Note that the sheet name ‘Direct Project Costs Example’ is still left at the start, and the name is added in place of the cell range.
Note that this has not yet created a dynamic selection though as the “sponsor_draw” name is still a static reference of columns D through NK for the Sponsor Draw row. In this example the graph is very mis-sized because the date range extends out many months, by design, to accommodate different possible inputs. You could have just selected the data manually so that the graph stopped where the current data stopped, but if inputs were changed and the new resulting data extended your previously selected range, you would then have to go in and manually change the data range again for the graph series. This is obviously not best practice, so the next step will explain how to use the Name Manager in conjunction with OFFSET to be dynamic.
Example 1: Making a Name reference dynamic with the OFFSET and MATCH functions to let a date value govern the x-axis range
Now that you know how to create names for the various series of a graph, you must add a formula to the given names to make the references dynamic so that the graph will size properly – according to the Construction End & Final C of O Received date in this example.
As shown above, there is a toggle with two date choices. The difference in the dates impacts the number of months over which the senior construction loan is drawn, expanding the x-axis from 19 to 22 months, and contracting it back from 22 months to 19. Note that to support this example we have provided two illustrative sets of data, each of which resides on its own tab, and we will be defining three names: sponsor_draw, third_draw, and senior_draw. Below we give the steps for defining sponsor_draw.
You would go to Name Manager and create a new name for sponsor_draw. In the “Refers to:” section at the bottom of the new Name box, you will write the dynamic function by starting with “=OFFSET(“. The function works as follows…
OFFSET(reference anchor point, rows before start of data, columns before start of data, height of data, width of data)
For the first argument in the formula, which is the reference, you will select the first cell in the respective data series. In the example below that is D5 which is the first month of the Sponsor Draw line. You can type 0 for columns and 0 for rows, or simply put commas consecutively to skip over these arguments. You will type 1 for height since you are just referencing row 5. The last piece of the OFFSET function, width, is where you can make the range dynamic for this example. Here you will use a MATCH function that looks up an end date (D13 in this case) within the relevant date range. MATCH will find the referenced end date.
The formula for this example would read:
=OFFSET(‘Direct Project Costs Example’!$D$5,0,0,1,MATCH(‘Direct Project Costs Example’!$D$13,’Direct Project Costs Example’!$D$3:$NK$3,0))
The image below shows what this will look like. Note that the selected range only goes through the Construction End & Final C of O Received date. When the date input in cell D13 is changed the range automatically adjusts.
The same procedure is used for the two additional dynamic names, you just have to change the reference point. In this example the reference cell at the start of the OFFSET function would be one cell lower for Third-Party Draws and another cell lower for Senior Loan Draws, with the rest of the formula reading the same.
Once there is a dynamic name created for each of the Draw types, you would need to update the series values in the graph so they reference the dynamic names (using the procedure described above in the Introduction to Name Manager section in this post). Make sure that each series contains its respective dynamic range. If one of the series is still a static selection the graph will not be dynamic and update. The resulting graph would display as shown below. Note that the graph ends at the inputted date for Construction End & Final C of O Received which is the 19th month.
When that input is changed to “11/1/2023” in this example, the graph adjusts accordingly.
Example 2: Making a simple duration input govern the x-axis range
If you do not need to reference calendar dates to determine the x-axis range, you could simply refer to a cell that was a number input for hold period years for example. In the example below for a graph of annual NOI where we have a data series that spans 15 years, the OFFSET function would use cell F9 for its width instead of a MATCH function. This can be an easier use of the OFFSET function with Name Manager if your data allows for it.
