Expected end of year Cash

Overview

One of the reasons I set up a budget is so I can determine what my available cash will be at the end of the year. I run my available cash very lean and any extra cash I have at the end of the year goes into a separate account for emergencies, retirement, or splurges.

However, as the year goes by, because my income and spending do not always follow my budget, my expected end of year cash changes. I could not find a solution that allowed me to determine how EOY cash changes based on year-to-date actuals and my current net cash (cash assets minus credit card balances- more on this below), so I leveraged an existing sheet and created this one.

Installation

Simply copy the sheet to your main Tiller spreadsheet. It uses the standard Tiller foundation sheets- Transactions, Accounts, Categories, and Account Balances. You can download the sheet here.

Setup

You need to choose the Year and the Completed Month of that year. If you don’t want to include your net cash; this is all you need to do. This assumes you have set up a budget in the Categories sheet.

image

If you want to include your current net cash (which I do), you’ll need to choose which accounts to include. To do this, ungroup the columns to the right and all the way to the right in columns BD-BH, you can choose which accounts you want to include for net cash. I include my checking and since I pay all my credit cards each month, all my credit cards.

I do not include loans as those payments are already included in the monthly budget.

Usage

After you choose the year and your completed month, this looks at your year-to-date actual cash flow, your current net cash (if you set this up), and calculates your budgeted end of year cash.

Permissions

I encourage others to copy, use, and modify it.

Notes

Because Tiller does monthly budgets, it doesn’t know when in each month budgeted items are going to happen. This makes cash flow incorrect when you’re in the middle of the month (although it gets closer to being correct the close you get to the end of the month). Because of this, it only allows you to choose completed months of each year.

I created this sheet a while ago, but am just now sharing it. I leveraged someone else’s sheet as the base and made modifications. My apologies for not remembering who created the original so I could give them credit.

Very nice solution to a real need problem. I’d love to have this on the Excel side.