I wanted a simple sheet to calculate my monthly expenses and check that against my savings, so I can understand how well-prepared I am in case of an emergency. The Net Worth sheet doesn’t help me understand how much liquid cash I have, and just looking at the number on the balances sheet doesn’t make it clear how many months of runway I have.
I moved recently, and having the sheet autofill based on the Category sheet was really useful. I didn’t see one like this in the template gallery so I’m sharing here.
Copy the template from here to your Foundation Template powered sheet.
Are there dependent sheets?
Accounts, Balances, Categories
Setup
Set the category that has all your monthly expenses in step 1.
Select accounts that have liquid cash in them you want to count towards your emergency fund
at the bottom of your accounts, tick the box if you want to subtract your current credit card balances from the total
Usage
This Sheet should give you an idea of how many months you could scrape by on emergency savings on. I added the option to subtract credit card balances to give myself an idea of exactly how much truly liquid, unreserved cash I have on hand. I plan to use this to encourage me to save and tell me when it’s time to shift focus to other investments.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Feel free to copy/modify/redistribute. I’d appreciate a link back here or to my website.
Future changes
I figure this is a common need, so I’m thinking of ways to make this easier for more people to use:
make the design …nicer? I don’t have a good eye for this
Allow for multiple categories to define monthly expenses
Add fixed additional amount to each month
Allow for any number of accounts
Make the account math more flexible, allow any number of subtractions of different categories of accounts
I tried this out. Pretty cool Idea and looks great! I did have errors that I was able to correct by changing the column that is being referenced. I’m not sure if it was referencing the wrong columns because I have changed/added some or not. It’s a very likely possibility it was because I changed some of my sheets and not with your actual formula. It was pretty easy for me to fix.
What I changed for my use and what might make this easier for some people is maybe instead of the user choosing what category to use for those emergency expenses (because everyone might not want to put all their emergency fund expenses in the same category), make this dependent on tags column and then the user just adds a “Emergency” tag or something to that effect to the expenses they will still have in “emergency mode”.
Then instead of it looking under category column for “Bills”, have it look in the tags column for all the expenses that include the tag “Emergency”. That way they aren’t having to change the way they categorize things, they just add an “Emergency” tag.
Overall, a great idea and pretty solid execution!
I’ve been working on the same problem, but in a solution that more works within my ecosphere of sheet add-ons. Using the Tags column as suggested above is the route I took to solve “what is an emergency expense?”
As for the references, there’s an easy and standard way to reference columns on another sheet that is dynamic, and does not break the reference if the user changes the position of the column in the sheet. I’m going to work on this sheet to incorporate that part and post my solution for you to copy and study. It’s a really neat pattern!
I worked out the logic to use tags instead of category groups, and also dynamically repointed all references for you!
Now, you just define what you want to use as the tag over in the Tags column on the Categories sheet. Then, you enter that same value in the Tags column for an expense that will need paid during an emergency, and it should show up on the new sheet. I entered Emergency as the default, but you can change this to whatever you want.
I also made it to where your tag will be picked up even if there are multiple tags in the same column and the value doesn’t exactly match (so you can have multiple tags for your mortgage like “Investment, Emergency” etc…)
Finally, I moved the balances to the top so you can have unlimited accounts or expenses without any issues, and repointed the formula to work out the credit card total using the values on the Accounts tab instead of the Balances tab. If you want an account to be a part of this total, you just set the group for it to Credit Cards on the Accounts sheet (which you’ve already done since your Balances sheet splits them into a “Credit Card” category.)
Check it out and please let me know if you have any questions on what a new formula is doing/how something works, and I will do my best to answer Also, you can (and should) hide all the columns to the right of H in the sheet you use. I left them unhidden so you can see everything going on.
@ianhyzy I messed around with the sheet that I linked a little more. I added the ability to pull the Monthly Expense values from various different columns on the Categories sheet. I noticed your solution was only looking at the first month’s budget. I initially changed this to look at the current month’s budgets to get a closer estimation, but felt it could be better.
Since budgets can change over time, I modified this so that you can pull the values from the first month, the current month, the last month, or an average of the latest 12 months present in the Categories sheet. All you need to do is change the drop-down option and everything adjusts for you.
Note: all of these totals will be the same if your budgets never change.
I toyed with the idea of working out a way to pull these totals on an expense level instead of all grouped to the same. So you could choose to use the average for one expense and the last for another. I might do that if I get some more free time to tinker.
Example:
I have a few quarterly expenses like trash that only have budgets entered in for 4 of the months, so using the average in this instance would be best. I also have car insurance that seems to always rise every 6 months or so. So using the budget entered in the last column makes the most sense tin that situation.
Also, you can (and should) hide all the columns to the right of 