Quick Tutorial: Add Line Items to OpSt Sheet in All-in-One
I was recently asked the question whether it’s possible to add more other income or operating expense line items to the MF-OpSt or ORI-OpSt worksheets in my All-in-One Model for Underwriting Acquisitions and Development. Short answer is: out of the box this is not supported. Or in other words, there’s not a button you can press that will do this for you automatically.
However, with a little manual work on your part it is possible to add new line items. Now it’s important to follow a few steps in a certain order so that the SUM (i.e Total) rows continue to work correctly and the new line items flow through to the DCF. But if done right, it’s a 20 second process that allows you to further customize the model to meet your specific needs.
Steps to Follow to Add Line Items to the OpSt Tab
So there are a few steps to follow and nuances to keep in mind when you’re doing this. I’ll follow up these steps with a short video showing you how to do this. First the steps:
- Go to either the ORI-OpSt or MF-OpSt tab.
- Find the annual operating statement section of the worksheet.
- Choose whether you’d like to add an Other Income or Operating Expense line item.
- Within the annual section (e.g. annual Other Income) where you want the new line item, insert a new row. Make sure the row is in the middle of the section, not at the top or bottom.
- Find the exact same place within the monthly section (e.g. monthly Other Income) and insert another row.
- Copy the row either immediately above or below the newly inserted row and paste the copied contents into the newly inserted row.
- Do the same within the annual section, copying and pasting the row either immediately above or below the newly inserted row.
- Rename the newly inserted line item.
- Confirm the formulas are working properly.
- With your new line item added, you should be ready to go.
Now here are a few pitfalls and things to keep in mind when doing this:
- Make sure to insert rows within the middle of the section where you’re adding a line item. If you insert a line item at the top or bottom of the section, the formula within the SUM (i.e. Total) row may not capture the newly added line item.
- Make sure when you copy a row to paste to the newly inserted line item row, that the row you copy a) is immediately above or below the newly inserted row, and b) contains the same formula types and formatting as a standard other income or operating expense line item. In other words, don’t copy the ‘Management Fee’ or ‘Make Ready’ rows as they use formulas different from the standard.
- Error check before you’re done. This is important. Confirm that the total row is working properly, and that the total amount is flowing through correctly to the Property CF tab.
- Remember that the MF Settings and ORI Settings tabs will not contain the newly added line items. This won’t impact the cash flows, but it does mean if you decide to alter the OpSt tab using the Settings tab at some point, you’ll won’t be able to update the manually added line items from the Settings tab.
Quick Video Tutorial – Adding Line Items
Now that I’ve laid out the steps and potential pitfalls, allow me to quickly show you how to do this. If you have more questions, please don’t hesitate to ask in the All-in-One Support Forum.
