Docs: Projected Balances sheet

[randy]

Overview

First, a few things to understand about what this sheet is and what it isn’t. The Projected Balance sheet is somewhat manually driven. It’s not dynamically pulling data from your automated balances or transactions and it doesn’t factor in your existing budget amounts. If you copy it into a Tiller spreadsheet it will pull in your custom categories from your Categories sheet, if you have one, but that is the only linked data from other sheets in your Tiller spreadsheet.

It’s meant to give you an estimate of your daily projected balance based on fixed recurring monthly and yearly income and expenses. It’s simply a tool to help guide you, but be aware that other discretionary variable expenses or surprise income throughout the month that aren’t accounted for in this sheet will affect your daily projected balance.

Installing the Projected Balances sheet

1. Launch or Install the Tiller Community Solutions Solutions add-on
2. Open the add-on and choose "Analysis” from the tags dropdown on the Explore tab
3. Click on Projected Balances
4. Choose “Add to spreadsheet”
5. Start customizing

How to use the Projected Balances sheet

The great thing about this sheet is that you can use it run projections anytime, and then run a different scenario later based on the configuration settings at the top of the sheet. Duplicate the sheet as many times as you’d like to run concurrent scenarios.

1. Choose a start date. You can modify this at any time you want to run a different projection scenario.
2. Choose a period interval such as days, weeks, or months.
3. Choose the number of periods for your selected interval by typing in a number. The sheet currently can only run one year of daily projected balances from the start date.
4. Enter a starting balance if you have one. This is the starting balance for all accounts for which the recurring transactions configured starting on row 9 in this sheet have an affect. For example, if your Netflix subscription fee is automatically deducted from your credit card each month, and you include Netflix as a monthly recurring transaction, you’d want to include your current credit card balance in this total starting balance.

Setting up your recurring transactions tables

Your recurring transactions should include both income and expenses. Income transactions should be entered as positive amounts. Expense transactions should be entered as negative amounts.

1. Select a category from the dropdown in column A for fixed monthly recurring categories in the Monthly Recurring Transactions section. If you don’t have a Categories sheet you can still type in a category here, but you’ll see a red triangle in the upper right of the cell.
2. Choose the day of the month on which you expect the transaction to post to the account. If a transaction posts on the last day of the month choose “last day.”
3. Enter the amount of the recurring transaction. If it’s an expense be sure to enter it as a negative number (-400) and if it’s income be sure to enter it as a positive number (1000).
4. Repeat these steps for all recurring monthly transactions you want to track in your Projected Balance sheet.

That’s all you really need to do to start seeing the Projected Balance chart update, but you can continue customizing this sheet for your unique financial picture.

Accounting for Yearly Recurring Transactions

Your daily projected balances will be more accurate if you also include infrequent yearly recurring transactions like car insurance, personal property taxes, an annual bonus, or car tags. You can configure this in the Yearly Recurring Transactions section.

1. Type in a description for the transaction.
2. Choose the month and day the transaction will post to your account. You can type in the month and day using two formats. Use either Mar 5 or 3/5 format.
3. Enter the amount for the yearly recurring transaction. The same rules apply as the monthly recurring ones. Expenses are negative and income is positive.

Checking your daily projected balance and making adjustments

If you want to see the exact balance amount you can click on the chart and hover across the line to see the projected balance for any given day during the configured period.

You can also scroll a bit further to the right to see the daily projected balances for the period as a list.

If you want to see more or less data in the chart or list play around with the configuration settings at the top of the sheet by changing the start date, period interval, number of periods, and starting balance data.

What about variable expenses throughout the month?

Of course your daily projected balance would be even more accurate if it accounts for things that are variable and don’t occur on the same day throughout the month like groceries, gas, and restaurants.

Including this type of data in the chart is possible, but it’s considerably more complex to build, and we wanted to keep it simple in this first version of the tool. Let us know we have your vote for continuing to build out projected balance capability, or how you’ve accomplished this already in your own sheet, by emailing support@tillerhq.com or using the chat window on the Tiller Console.

Customizing the Projected Balances sheet

If you’re comfortable with queries, arrayformulas, and other Google Sheets tricks you might have learned from Ben Collins’ website, or you just want to check out what’s under the hood, you can unhide columns Q through AE.

Each column header has an explanation note that you can view by hovering over the cells in row 1 in columns Q through AE to learn more about how these columns power the chart and daily balance list.

If you mess around with what’s in the hidden columns, and something breaks, use the Tiller Community Solutions add-on to restore the sheet and start new.

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Google Sheets category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

[CSH]

I have transactions in the same category that occur more than once a month - for example, payments for category student loan occur on the 1st and 20th - but it would be helpful to be able to identify the account and/or add a note to this sheet in order to track where the recurring payment is going. Is it possible to add an accounts selection column and/or a notes column in this sheet?

[heather]

Hi @CSH,

I think you could add multiple of the same category and specify a different date for the second instance, per your example with student loans. Just create two entries for Student Loans.

As for adding notes, I wouldn’t insert any extra columns there as I think it would break the sheet’s formulas, though @jono would know best.

Heather

[jono]

Hi @CSH,
@Heather is correct that adding extra columns sometimes breaks the sheet formulas.

But I noticed in the Projected Balance sheet, Column D is blank and just used as a spacer column. If you wanted, you could put Notes or Account info into Column D, right next to your monthly recurring transactions. You should also make that column a big wider so you can better see what you enter there.

Any information in that column would not impact the Projected Balances results.

Would that solution work for you?

[CSH]

Hi there -

Thanks for getting back to me! I ended up creating a new sheet using a simple google template for the running balance and then added on tiller labs to import the sheets I need from tiller. I will post a copy without data so you can see what I did.

[jono]

@CSH,
I’m glad you were able to figure out a better solution.
Please share it so others might benefit as well.

[jfederline]

I’ve gone a completely different direction - perhaps conceptually where this might go next?

I plan and support corporate enterprise software. For me, budgeting alone is a coarse estimated plan, at best, too light on numerical rigor. The killer feature of Quicken was “Scheduled Transactions” and the resulting forecast chart. This, combined with the principle of “plan every future dollar now, so you can adjust for all actuals” is what I needed from Tiller.

I took what I think was the direct predecessor to this sheet last year, and hacked it to intake all transactions for a single account for the current year, and rather than use those fuzzy budget numbers to look forward, I created every single future income and expense manual transaction into the Transactions tab for the entire year ahead, using the Simple Business Add-On’s " Add Transaction, and sucked those in too.

I further hacked the projected balance sheet to produce a daily actual balance based on the Balance History to the current date, and then used all the future transactions I created with the Simple Business add-in to project out a 100% accurate balance for the account, for the entire year, using actual future transactions, not estimates through a rough budgetary proxy.

It is compute intensive for the entire year, using all past-actual and planned-forecasted transactions, all balance histories, for all accounts (one sheet per account is required in my hack). But, as we say in business, if you can project everything using actuals and planned down to the red cent, you become much more agile for small and large adjustments to the future plan. So, I’ve further customized the projected balance sheets to look for a flag on a settings sheet and refrain from calculating while I’m updating sheets, it makes for too much lag.

The information is worth the compute time wait. Ex: I have all my accounts planned this way. If I want to adjust my cash savings plan for the next 5 months to allow for a large purchase on a credit card that I want to take 5 months to pay off, I input future transactions for the actual CC purchase, the estimated monthly CC interest expense, the projected CC payments to pay both principal and interest off, I update the already-existing future cash savings transfers in and out of savings and checking, respectively.

The Simple Business Add Manual Transaction functions sets up the future row into Transactions in a way that is ready for using the built-in Reconciliation feature, to recon the manual future transactions with the incoming bank feed, once it comes in. This is what Quicken used to do! I was almost there!! Alas… the UI for the Recon feature is bass-ackward, showing the latest transactions for recon, which, if you have many other planned future transactions, is those, instead of the recent incoming bank feed transactions.

So, while I would like to use the Recon feature from the Simple Business add-on, it was not built with long-range forecasting in mind. It could be changed easily to do so, I bet. Instead, I just manually recon the planned future transaction, with the once that the feed brought in, and I delete the planned manual transaction. Done!

In summary, I do down-to-the-red-cent projected balances of all my cash and credit accounts. I chart them in a single chart. I chart actuals-to-date, then projections forward from the current balance and date. If I don’t like the plan what I see, I go change the manually-entered future transactions. I add all future transactions as far as is practical (I go by the year) with the Simple Business Manual Transaction add-in feature, and then I adjust those future transactions as actual transactions come in and are reconciled with the forecast transactions.

Wish list:

• bring Manual Transactions and Reconciliation to the non-business Tiller add-in
• change Reconciliation to work better with this use case
• develop better-performing daily balance forecast calculation sheets (my hack is a real pig)
• develop a better design for the daily balance projection sheet (its not very flexible right now)
• develop better UI for the daily balance forecast calculation sheets

When a chart is added to display the resulting data, you replicate the most killer Quicken feature ever, Scheduled Transactions and the charts.

Why did I do this? I hate the voluminous amount of Categories that is required to use simple budgeting as a planning tool, and I also dislike the rigid months-handcuff of these budgeting approaches.

If you made a Category for every single vendor you ever will pay, you might be able to use this current projected balance sheet. But that is ugly. (For example, I use one Category called “Utilities” for a whole boatload of utilities, and then I dedicate the “Tags” feature to add a Vendor dimension to the data. I use exact future projected balances per account, rather than monthly estimates per Category.)

So, to use categories and this sheet, I would have to sum all my planned Vendor expenditures for all vendors into a forecast for every single category for every month, by itself, since that is how budgeting works in Tiller and all these personal finance tools. If I have 30 categories and 12 months, that is potentially 360 different little budgets I have to plan out. That’s silly!

This method here, doesn’t give a rip what month it is in, and while Category is still mandatory, it only matters whether the Category is Income or Expense (think: corporate income statement). Its just to-date actuals, future transactions, and daily projection calculations.

I can also then use the Tiller Budget sheets to look at my actuals per Category, and establish and update Budgetary amounts, too, to monitor Category spend… exactly how corp finance works. So, I do budget… but only as a monitoring tool first, and secondarily a coarse planning tool. I manage day-to-day and plan the future with daily projected balances based on future manual transactions.

Think this area between corp finance, with GL accounts and cost centers and double entry and full transactional forecasting; and personal finance with categories and accounts and budgets, is the most useful. I’ve taught it to others doing personal finance as a method, I call it something like “cash flow with projected balance” method. I’ve witnessed those who struggle with budgeting use this to gain control.

PS - Its way too much of a hack to put into the contest going on right now. It needs some real resources behind it to become user friendly enough to make generally available for perusal. Here’s a redacted chart shot of the end result. This is for the current year. Prior to Oct 19 are actual account balances from Balance History. After Oct 19 are projected account balances based on future transactions (scheduled or what I thought up at lunch to do 3 weeks from now). Its telling me right now to pay down two credit cards before the end of the year.

[lev.rodovskiy]

Many transaction don’t occur on a specific date, but rather on fixed schedule, e.g, once every two weeks on a Friday. Would be nice to be able to specify transactions in this way in addition to the by date method available now.

[bryce.lohr]

This is beautiful… I was going to build an app that did exactly this when I discovered Tiller Money. Now I’m going to implement it pretty much as you did! I wonder if there’s a way to collaborate on this as a new add-on?

[wreed4]

This is amazing! maybe slightly more complex than I was thinking, but definitely in the right vein. Is there a plan to adopt this into a more generic sheet for others to use?

[wbrodyjr]

I have a similar situation. I get paid every two weeks, not on a certain date each month. Here’s something that worked for me:

Try going into the “Yearly Recurring Transactions” section, enter the month/day of a transaction and the amount. Copy/paste this to the next line down. In the “Month/Day” cell, create a formula that plus 14 days to the cell above it. Copy/paste down 20 rows or so.