Thread Tools Display Modes
Old 07-10-2017, 07:15 AM
Richard Pearse is offline
Join Date: Aug 2001
Location: New Zealand
Posts: 10,602

Spreadsheet help / ideas needed

In this thread I mention having a basic budget spreadsheet, that I use YNAB for the bulk of my budgeting, and that I had no inclination to create a proper budget spreadsheet that does the work of YNAB. This situation has changed and I've built up a more fully featured budget spreadsheet that imitates the functionality and look of YNAB.

I've done this for a few reasons.

1. I was enjoying fiddling around with my budget, but once the budget is done there's not a lot more you can do on a regular basis. The spreadsheet allows me to fill my "fiddling with the budget" needs by just finessing the spreadsheet itself.

2. YNAB costs around $70 Aus per year. Not a lot of money, but I wouldn't be a true YNAB convert if I didn't look for a way to avoid paying for it. Being an honest person, "avoid paying for it" means not using it, i.e., use something else.

3. Finally, and this is the main reason, YNAB does not have cash forecasting. I understand their philosophy, you should only budget the money you actually have and should avoid playing with "monopoly money", funds you expect to receive in the future but haven't yet. If you budget properly, setting aside adequate cash for everything you will need in the future, then there is no need for forecasting because you know the funds will be there.

That's all well and good but I like to have a graph of my cash extending out for 6 or 12 months and be able to see what times of the year cash might be a bit tight. The YNAB method allows you to shuffle money around your budget to cover areas of over spending but without forecasting you don't know in advance if you will need to shuffle money around. Sometimes you might need to spend less on going out for dinner in one month so you have the cash for a power bill the next month.

So all that out of the way, this is where I'm at. I have a budget spreadsheet that includes my bank account transactions and the budget itself. It largely imitates YNAB in that I enter a transaction in the account table and the budget picks up the amount and category and reduces that category's "envelope" by the appropriate amount. In addition, I would like to be able to enter scheduled transactions with minimum workload and generate a line graph of my future cash reserves based on expected income and expenses.

It needs to be automated to some extent, that is, I want to be able to enter my power bill as (paraphrased) "$750 every three months starting on the 31st of August" and have the forecast pick up every transaction for the selected period. I don't want to enter 12 months worth of transactions individually in order for the graph to pick them up, just one entry for each transaction along with the frequency and amount.

I would also like it to be flexible enough that I don't have to rewrite it for the next year. The forecast only needs to go out to 12 months, 6 months, and 3 months from the current date. It doesn't need to be infinitely variable or anything.

Finally, I am using Apple Numbers for my spreadsheet. Why? Because I like the way it handles tables individually. This does mean that I have limitations though.

At this stage I'm just after any ideas and/or inspiration as to how to go about it. I'd welcome ideas relevant to Excel or Numbers. Sometimes seeing how something works in Excel is enough to get me to a good solution in Numbers even if the same method doesn't work.

So. Thoughts? Questions?
Old 07-11-2017, 11:38 PM
DCnDC's Avatar
DCnDC is online now
Join Date: Feb 2010
Location: The Dueling Grounds
Posts: 13,504
PM me and I can send you a blank of mine. Taking a good look at it now, barebones, at its heart it's just a simple income in, expenses out spreadsheet, with a whole lot of simple calculations that keep account of a whole lot of stuff at once.

Basically it works because I know exactly how much I am paid every 2-week pay period, then I subtract base fixed expenses based on what is due when, then I budget $300 per 2 weeks for variable and general expenses (gasoline, food, beer, work lunches, clothes, gifts, projects, etc.), keeping track of each purchase, and the rest I can divide freely between savings/investments and servicing finite debts (credit cards, car payment). This way I can "see" as far into the future as I want to extend the spreadsheet, and make adjustments as I go. I can be frugal one week and roll over the leftover to the next period's budget, or "borrow" from future weeks to cover myself going over budget this week. And this way I can budget to, say, pay off my car 3 years early, then focus on the credit cards and pay them off quickly as well.


Last edited by DCnDC; 07-11-2017 at 11:40 PM.


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

All times are GMT -5. The time now is 10:26 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2020, vBulletin Solutions, Inc.

Send questions for Cecil Adams to:

Send comments about this website to:

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Copyright 2019 STM Reader, LLC.

Copyright © 2017