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.
Frequently Asked Questions about Adding Line Items to the OpSt Sheet in the All-in-One Model
Is it possible to add more line items to the MF-OpSt or ORI-OpSt worksheets?
Out of the box, the model does not support adding new line items automatically. However, with a few manual steps, it is possible to add other income or operating expense line items effectively.
Where should I insert a new line item in the OpSt tab?
Insert the new row in the middle of the section (e.g. annual Other Income or monthly Operating Expenses). Avoid inserting at the top or bottom, as the SUM formulas may not capture the new item.
How do I make sure the formulas work for the new row?
Copy the row immediately above or below the newly inserted one, and paste it into the new row. Do this for both the monthly and annual sections. This ensures formulas and formatting remain consistent.
Can I copy formulas from any row to create the new line item?
No. Only copy from rows that are standard income or expense items. Do not copy rows like ‘Management Fee’ or ‘Make Ready’ as they use custom formulas and may disrupt the logic.
Will the new line items appear in the Settings tab?
No. The MF Settings and ORI Settings tabs do not reflect manually added line items. While this won’t affect the cash flows, you won’t be able to edit the new line items from the Settings tabs.
What should I do after adding the new line item?
Perform an error check. Confirm that the total (SUM) rows update correctly and that the new values flow properly through to the Property CF tab.
Is there a video tutorial to help with this process?
Yes. A short video is included in the original post to visually guide you through the steps of adding new line items to the OpSt tab.
