Best Method for Creating a P&L in Google Sheets from Database?

Question

Objective: Create a Profit & Loss ("P&L") report in Google Sheets across multiple properties.

I have a database of income, expense, debt service etc. for different properties. Call this sheet DB1:

If I create a pivot table from this data in the format I want, it looks like this:

I want to insert calculated rows in between rows in the pivot table above. For example, I want to insert a row for Net Operating Income ("NOI") which is equal to Income - OpEx. And I want one for Cash Flow ("CF") which is equal to NOI - Other Expense - Debt Service.

Problem 1: There appears to be no way to insert a calculated ROW in a pivot table in Google Sheets.

Workaround 1: The workaround I'm using (thank you @horanimal and @TheMaster) is to create a second database table, "DB2", with calculated row entries:

This utilizes named functions. In the example formula cell below, it's using a named function CY_PROPERTY() which indirectly grabs the property name from the property column in the given row and a named function NOI which totals the income and expense for Property A from the DB1 tab using SUMIFS functions and takes the difference.

Now we can make a third database, "MasterDB", which combines DB1 and DB2 via an array, and make the pivot table from it that we want, with calculation rows such as NOI added:

This method of using pivot tables is ideal because it allows us to add accounts (e.g. Misc Income), create additional reports such as reports comparing the prior year ("PY") to the current year ("CY"), change views etc.

Question 1: Is this the best method to achieve our objective?

Next, another problem arises when we want to add new properties. If we add financial data for a new Property C to DB1, we also need to add the set of formula rows for Property C to DB2.

Problem 2: Can you call functions in Google Sheets and evaluate them in the target cell?

We couldn't find a way to do this. So instead, we made a formula template generator at the top of the DB2 sheet. You enter the new property name, e.g. Property C in all rows of the template, and then copy and paste it into the DB2 database below it:

Because all named functions use indirect references to inputs instead of cell references, it works.

Question 2: Is there a better way to create DB2 entries for new properties?

In conclusion, the above methodology doesn't seem to be the cleanest solution, and it still seems like there should be a way to insert calculated rows in pivot tables, but this is what we've got.

Answer 1

My advice would be to create a bunch of Named Functions (best tutorial I've seen shout out to Ben Collins, my guru! & official docs shout out to Google!) instead of the separate sheet of formulas you mentioned.

Then you can write an =IF() function in the A column rows that evaluates the fixed inputs in the other columns of the same row you mentioned & calls whichever function you want based on the logic you set.

For example, you could have a custom function called ADDONE() where the formula definition is =INDIRECT("R[0]C[1]",FALSE)+1 and then include that in your if function.

Here's an example I made real quick to illustrate: Custom Named Functions being called in other functions