How To Make a P&L with Pivot Tables in Google Sheets

Question

Hello!

This is more of a spreadsheet question than a real estate theory question, but I'm asking this group since anyone who has tried to make a P&L this way will have run into this problem!

Problem: How do you insert a calculated ROW into a pivot table in Google sheets?

In my example below, I have a pivot table from a financial database for multiple properties. The pivot table has rows for Income and Expenses by property (simplified for example), with the properties as columns.

I want to insert ROWS (not columns!) into the pivot table to calculate KPIs like Profit (= Income - Expense). (There are more complicated calculations but simplifying here for sake of example).

I know how to insert a Calculated Field, which provides a COLUMN with the specified formula. However, I can't figure out how to insert a calculated field that produces a ROW with a specified formula.

Example below.

Thank you in advance!

Kim

Example Financial Database:

Example Pivot Table:

In the pivot table example above, I would want to add two calculated rows:

• 1. The first would calculate the Net Operating Income (= Income Total - Expense Total). This would be a row inserted between rows 10 and 11 above.
• 2. The second would calculate the Cash Flow (= Net Operating Income - Other Expense Total). This would be inserted after row 13.

Kim Hopkins · Answer

Well, @Jeff S. and @Mohammed Rahman, thanks for your interest in this project. You'll be happy to know, or perhaps not care at all :D, that I finally finished this project!

Through a lot of discussions in other spreadsheet forums as well as long conversations with ChatGPT, I was able to set up a Google Sheets template (without using AppsScript!) that creates a dynamic P&L and KPI analysis dashboard for each property as well as across the entire portfolio.

Here are the four main reports it produces:

Here is also a video tour of the template in case anyone is curious. Some final thoughts:

- I'm sure there are much fancier SAAS programs out there but even with the fancy ones, I have found the portfolio wide KPI analysis like this to be lacking.

- Additionally, the accounting programs often summarize data differently than how an investor wants to see it (for example, including depreciation in expenses and excluding interest payments). Many of them also do not contain the property wide analysis functionality.

- I think this is a really nice way for an Asset Manager (NOT a property manager) to see the annual and YOY performance of their portfolio, without having to use a huge property management software that is both expensive and redundant if they have third party property managers.

I hope this post helps other people who may look for this type of solution in the future.

Cheers,

Kim

Kim Hopkins · Answer

Hi @Jeff S., yes I experienced exactly what you described! ChatGPT would give me a formula I didn't believe, and I would ask if it "was sure about that answer" and it would apologize and say its syntax was wrong and give me another answer. Very interesting, albeit you are correct, it was frustrating in this case since it went on and on with several wrong answers until I fact checked and realized it indeed was completely incorrect and had no idea how to approach this problem.

I'm currently still trying to figure out a workaround. The basic question, in case anyone reading this is interested, is how to create formulas dependent on rows of data in a database, which can live update when new rows of data are added.

I figured out a very complicated way to do it by adding a column for calculated amounts and adding rows at the top for calculated Accounts (e.g. NOI). The new calculated amounts column calls in formulas based on the calculated Account (e.g. NOI vs CFBDS vs ROE etc) but now I'm stuck on how to call in formulas in a clear manner.

ChatGPT is stuck on this as well, so I'm back to Stack Overflow and asking the "human" gurus.

As an aside, I can't believe it's this hard to create a simple  P&L with KPI analysis in Google Sheets. (The complexity is coming from the need for it to auto update when new rows of financial data are added). And if anyone has a better "system" for creating such a spreadsheet, I'm all ears!

Kim Hopkins · Answer

Hey @Jeff S.!

So, FANTASTIC suggestion to check out ChatGPT for this. Love that idea. Thank you so much for taking the time to plug in my question and share the response.

Unfortunately, I've learned after about 4 hours of brain pain that ChatGPT isn't always correct on such matters.

First, its suggestion to insert a Profit column := Income - Expense doesn't make sense as written, because the Incomes and Expenses are represented on different ROWS in my database, not in COLUMNS as it is assuming.

I managed to workaround that using SUMIFS formulas to calculate the Profit by Property in each row. As you can see, it repeats the profit for the given property each time the property appears in a row. (I got that idea from ChatGPT so thanks again there!).

I then tried to create a pivot table using steps 4-8 that you shared from ChatGPT. As far as I can see, its instructions are wildly inaccurate here. I can't figure out any way to pivot the data to have Profit appear as a row with the profit amounts lined up under the property's other income and expense rows.

Moreover, Step 5 says to click on the "drop down arrow next to Rows to create pivot group". There is no such drop down arrow, and the pivot group function doesn't work in the way ChatGPT suggests. Additionally there's no "formula" section, and if by this, it means a "calculated field", then we're back where we started with the issue of calculated fields producing only columns instead of rows.

Like you, I've also done a lot of programming in VBA and Google Scripts and would be happy to nerd out any time. In particular, if you have another idea on how to arrange this database correctly, or how to tweak ChatGPT's instructions, I'm all ears! Snippets below.

Thank you again,

Kim

Database with ChatGPT's Suggested Profit Column Added:

Nonsensical Pivot Table Using ChatGPT's Database:

Jeff S. · Answer

You’re asking the wrong website, @Kim Hopkins.  We have an Excel spreadsheet we use to evaluate loans, calculate returns, break-evens, create invoices, generate payoff demand letters, and lots more.  It’s been a work in progress for many years and I constantly update it with some hard-learned Visual Basic (VBA).

I started using ChatGPT recently and it’s been amazing.  As long as I can clearly explain what I want, ChatGPT instantly creates the VBA. It works 98% of the time.  Modifications that might take me days now take an hour.  It’s also fun.

I cut the problem you defined and pasted it into ChatGPT.  Here’s the answer:

In Google Sheets, it is not possible to directly add a calculated row to a pivot table. However, you can use a workaround by adding a helper column to your source data and then use that column in your pivot table to create a calculated row.

Here are the steps:

• Add a new column to your source data and label it "Profit".
• In the first row of the "Profit" column, enter the formula to calculate profit for that row (e.g., "= Income - Expense").
• Copy the formula down to all the other rows in the "Profit" column.
• Refresh your pivot table to include the new "Profit" column.
• In the pivot table, click on the drop-down arrow next to "Rows" and select "Create pivot group".
• In the "Create pivot group" dialog box, enter a label for your new row (e.g., "Total Profit").
• In the "Formula" section, enter the formula to calculate the total profit for all properties (e.g., "=SUM(Profit)").
• Click "Create" to add the calculated row to your pivot table.

You should now have a calculated row in your pivot table that shows the total profit for all properties. You can repeat these steps to add additional calculated rows as needed. Note that any changes you make to the source data will be automatically reflected in the pivot table, including any updates to the helper column.

This makes me wonder how ChatGPT will change the lending business and real estate investing in general.

Jeff S. · Answer

I’m glad you found the resources to solve your problem, @Kim Hopkins.  Since my first post to you, above, I also started using Bard.  It’s similar to ChatGPT but I don’t find the VBA code recommendations as sophisticated.  Nonetheless, Bard might have a few advantages for you.

Where ChatGPT is run by OpenAI, its database stops in 2021.  This might not matter for coding  issues, but it will affect topical discussions.  Bard is run by Google and is as current as its web browser is.   One benefit to you is that Google also runs Google Sheets.  Therefore, I was wondering if you would have gotten more useful answers for your Google Sheets problem with Bard.

It doesn’t matter at this point, but you might try Bard out in the future as well as ChatGPT.  FYI, I’ve tried it for Excel VBA and I like ChatGPT better.

Cody J Leivas · Answer

You don't need to use the pivot table. You can use the sumif formula to organize information better.

Try looking up how to use sumif on youtube.

If you send it to me, I can do it in less than 5 Mins.

Mohammed Rahman · Answer

Very nice @Kim Hopkins - you should consider a career in commercial real estate private equity if you're not already working in that field.

Ben VanTuil · Answer

HI Kim,

I am not a fan of pivot tables, as they can break when you do manipulations like this.  It is often easier to add the rows/columns you want outside of the original pivot table.  There are two options for this then.

1) use the pivot table, add your new rows with the formulas.  Then create a new sheet, referencing the cells on the pivot table to create a new table with the data in the order you like.

2) use a series of formulas (sumifs mostly) to create the essence of the pivot table, but more stable and you are able to split it up and divide how you want with new rows in between.

If you need more in depth info or spelling out the details better I can do that.

Jeff S. · Answer

It takes a bit of work to use ChatGPT, but not much, @Kim Hopkins.  You should assume it’s like an expert sitting alongside you as you work and with whom you can ask questions, point out errors, and ask for clarifications in real-time.  It’s the weirdest experience because I must continuously remind myself it’s not a human being.

If you don’t like the response or it doesn’t do what you want, you can either edit and resubmit your original question by clicking the edit button on the right, or ask a follow-up, or simply point out a mistake.  ChatGPT will come back, apologize for its error (creepy!!!), and suggest a workaround.  If you don’t understand its response, say so and ask for clarification.  Perhaps take a step back and instead of constraining yourself in the direction you’re going, ask ChatGPT how it would solve your problem.  Don’t take no for an answer and don’t get frustrated.  I can see you are.

I don’t use Google Sheets and I don’t understand your problem enough to help you technically, Kim.  Sorry.  I was simply offering a place you could go for help quickly because I really do think you’re on the wrong website for this. Up until ChatGPT, I would use some of the online VBA message boards for help.  It could take days to go back and forth and still not resolve my issue.  Now, I’d hate to be an owner of one of those message boards.

Aaron Breckenridge · Answer

@Kim Hopkins it's been a really long time since I did this in Excel, but I believe there is a side-menu that shows you the pivot table's rows, columns, data and filters. You should be able to drag the calculated field from the columns list to the rows list.

How To Make a P&L with Pivot Tables in Google Sheets

Most Popular Reply