Is there any way I can get a projected P&L that shows - by line item - the total amount spent to the end of the prior month, plus the total amount budgeted for remaining months?
Tell me if I’m not thinking about this correctly, but this is the only way I’ve ever been able to figure out how much money I’m likely to have left over at the end of the year, so I can adjust my spending accordingly.
Tabs such as the Yearly Budget do not account for a bonus I may have received in the summer, or extra spending on gifts I anticipate in December…
@bbruck I can’t think of an exact Community built template that has a full projected P&L as you described. However, you can check out the Projected Balances template to see if that would work.
Also, one of our members shared a workflow that might be helpful.
@bbruck If I understand you correctly, you are looking for a way to project your budget (estimated income and expenses) for the next 12 months. The Incredibly Cool Budget page in Income Companion does just that. I’ve just joined Tiller to look at possibilities for linking the program to Google sheets for importing deposits and credit card transactions the recording of which is currently manual. Nothing else is needed by IC because the program automatically builds your Incredibly Cool Budget from your descriptions of your income, allowance, bills, sinking funds and savings. Although there may be additional opportunities for importing the information to maintain the Debt-to-income Ratio and Net Worth pages in the program. I’m excited to explore these options.
Thanks for the response unfortunately that’s not what I’m looking for. To be more clear, what i’m looking for is a monthly P&L that has the months across the top Jan - Dec and the categories down the side.
For each category, I want the actual expenses from Jan - Sep, and the budget for Oct - Dec. This will give me the most accurate estimate of what my actual P&L will be at the end of the year.
OK. Thanks for the clarification. All of the information, past and future, that would make up a P&L is available in Income Companion. I’m going to take a look at adding a P&L page. If it ends up being created, would you care to see it?
Late to the party here, but wondering if you have seen the Yearly Budget sheet? This captures YTD activity in both income and expenses and the coming months for all. You can see what has happened and where you might end up if your future behavior matches your expectations.
I’ve come to the conclusion that a P&L in Income Companion, while possible, would be of little or no use to you. The program does not collect transactions from online sources. To have a P&L in IC you would have to use the software as your primary financial planning and cash flow management tool. My guess is that is not what you are looking to do.
Brad - thanks for posting. You can tease what’s needed from the yearly budget sheet but only by manually totaling the budget amounts for the remaining months and adding them to the actuals.
I could create the sheet myself if I could figure out how to get the categories to appear in the first columns, using some of the more primitive vlookup and offset functions I know how to use. Is there any sheet that simply lists the categories (as they may be changed) that otherwise appear on the yearly budget sheet? (I don’t know enough to mess with the formulas on that sheet without screwing the whole thing up…)
Interesting. When I look at column D of the Yearly Budget Sheet, I thought I was seeing the projected budget availability, given past expenses and upcoming budget amounts, combined.
I think the hardest problem here, @bbruck, is how to handle the current month. Do you use last year’s actuals? This year’s budget? Some prorated version of current actuals to today and last year’s actuals for the balance of the month? I’m curious how you’d implement this part of the report.
If you can figure out this riddle, I pulled the relevant data together for you in this starter project. The spreadsheet is just a stripped down version of the the Budget Builder template.
To get the data you want, just add formulas into G:R that blends the current year actuals in T:AE with the prior year actuals AG:AR.
Randy - THANK YOU SO MUCH!!! I have absolutely no idea how you get those categories to populate - as Arthur C. Clarke used to say “any sufficiently advanced technology is indistinguishable from magic.” So I assume it was magic.
I’ll take the next steps of putting the the formulas. I am rather a beginner/intermediate person - so I don’t know how to do arrays - but I can fill it out in some way that will get the job done, I suspect.
Will respond here when I’ve got that much done. I have no concept how to get it from there into my own spreadsheet, but I’m sure I will find out i time.
BTW - Our family sees my income on the fifth of each month, and my wife’s paycheck arrives around the 28th - so for us it’s a simple decision - we use actuals through last month, and budgets for this month through the end of the year.
Oh… I thought you were trying to blend with actuals from the prior year, in which case, I’m not sure the starter project is the right one. The focus of what I built was pulling actuals from this year and the prior year. It sounds like you’d prefer actuals and budgets from this year.
In that case, I think you can just build off the Yearly Budget as @Brad.warren suggests.
To copy a template into your personal spreadsheet, just right click on the tab and use the Copy To function.
As for ARRAYFORMULA, that is really just a shortcut for maintenance and improved experience on row insertion. If you are more comfortable building formulas in individual cells and dragging them, that woks great too.
Wow - you guys totally rock!!! With everyone’s help I was able to do exactly what I wanted in a copy of the Yearly Budget spreadsheet. I changed cell B5 to say “Projected Cashflow” and put the following formula in cell D5. It ain’t pretty but it worked.
It adds up the actuals for the previous months and the budgets for the current and coming months to give me a Projected Cashflow. For me this is 100x more useful!
Well done @bbruck. I’d love to see more users using the community templates (including the “Tiller Labs” templates) as building blocks and not just as finished products. As you’ve demonstrated, for most concepts, there is a pretty short path from an existing template to a customized solution that meets your needs.
I’m impressed to see you’re using the OFFSET() formula!
This is great! But, for anyone looking to use this, in the Yearly Budget sheet I have (version 1.00), the Actual Cashflow values to add up are on row 6, not 5 as he references in this formula. And the Budgeted Cashflow values are in row 5, not row 4. So, the formula that worked for me here is: