@randy This template is fantastic, great job. I had made my own P&L which was similar but I really like how the 1st column feature works Income/Expense Category Headings and Category rows and how they auto-flow based on the number of categories in each heading. I could not accomplish this on my own, so this workflow makes understanding cashflow a breeze without having to deal with formatting issues. I did add a dynamic dropdown for selecting subsequent years which is very useful. This is great!
So glad to hear that, @chris.larson1967. I think one of the coolest things about sharing templates is that you can so easily learn how to do something new or better by picking through someone elseās shared solutions. Iāve learned most of what I know in this way.
Thanks for the feedback!
I would love to see an AVG column to the right of TOTAL that would average the monthly amounts (but only if the month has already occurred).
Sounds like a great idea, @morganmb. Just insert a column and add the formula. The average will ignore months with no data, but it will take a little creativity to not average partial months.
Since there are zeros in the default spreadsheet, the average function includes them, which is not what I want. Then if I use the <>0 attribute, it works except for the months when zero is a real historical value (not a future null), so canāt quit figuring it out.
If it were me, Iād try to use the OFFSET() function with some IF() statements to form a dynamic range within the AVERAGE() function.
Thanks for the tip, but prob a bridge too far for me.
If I added a column B between CATEGORY and Total, I would put this formula in B6 and then expand it down to the bottom:
=iferror(if(isblank($A6),1/0,average(offset($D6,0,0,1,countif($D$4:$O$4,"<"&EOMONTH(today(),-1))))))
This might not work in all scenarios but it should be really close.
VoilĆ ā it works! Thanks!
I canāt add this to my spreadsheet. How do I resolve this error?
The problem is not intuitive but there are two versions of the Categories sheet (both names āCategoriesā) and you have the wrong one. Details are here.
Essentially you need to copy in the version with the budgets and trick the spreadsheet into linking to the new data (e.g. without breaking all of your data validation and references)ā a pretty clumsy process.
I might have a hack if youāre willing to share your spreadsheet briefly. Can you DM me?
Hello everyone, is this template available for excel? This is exactly what Iām looking for. Unfortunaley, I am using the excel subscription which does not have any add-onās (to my knowledge). Any help would be greatly appreciated.
Good question, @cdiepholz. Unfortunately, we have not built a version of this Live P&L for Excel.
On a related note, we offer the Tiller Community Solutions Add-on in Sheets but do not have similar tooling on the Excel sideā¦ but we are quietly working on bringing many workflows to parity.
@randy Hi Randy any way to have the columns for future months fill with the budgeted amount from the category tab?
That would be interesting.
I donāt really have time right now to implement that. Peeking at the formulasā¦ Rather than rewriting the complicated formulas that render the fancy UX in the visible area (C:N), probably the easiest way to do it would be to find a way to extend the source data section AF:AQ to switch over to budget values after the previous month. One complication is that the query that grabs the data in AE4 doesnāt include unused categories that are unusedā¦ so somehow youād have to extend the category list to those not used in actuals for previous months but with budgets in current/future months.
Iād say it is non trivialā¦ but doable with some persistence and creativity, @Patrick.
A post was split to a new topic: Way to filter out certain Groups/Categories in the Live P&L report?