Author Topic: Anybody feel like sharing their 'badass' spreadsheet?  (Read 10152 times)

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Anybody feel like sharing their 'badass' spreadsheet?
« on: January 08, 2015, 08:08:19 AM »
Do you have a spreadsheet that you think is just badass? Do you fee like sharing? I'm kind of interested in what other people are doing in excel to track things. Of course please remove the financial figures, but leave the formulas.

Interested in some fresh ideas on how to look at things....if nobody feels like sharing, then that is understandable :) ...figured it is worth a shot though.

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #1 on: January 08, 2015, 08:24:48 AM »
This is what i use to track expenses...nothing fancy or super badass, but i thought id share since i created the thread

first tab is the vlookup table that can be modified
2nd tab is the detailed breakdown - only the first column is a calculated field (filled in with made-up numbers to show functionality)
remaining tabs are all calculated fields of the main categories i wanted to see

everything can be filtered as you wish.

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #2 on: January 08, 2015, 09:14:42 AM »
It's like you read my mind. I was searching all over and checked my google drive for a spreadsheet I might be able to use. I am trying to find a weekly, Monthly, quarterly, yearly, all tied into one.

I went back to Mint.com but my credit union will not support them. I have some spreadsheets but not quite what I want them to be. I figure it's a new year so why not try to track from the beginning.

I probably won't stick with a hard and set budget but I would like to attempt to track our money a little better. My faults in the past has been having too many categories and I just need to simplify it down.

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #3 on: January 08, 2015, 09:21:38 AM »
I like this sheet at first glance.

Question. Do I put numbers in the Vsheets? SO I would manually enter my paycheck on page income vlookup? If I go out to eat then I would put it in dining vlookup? And so on?

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #4 on: January 08, 2015, 09:43:11 AM »
I like this sheet at first glance.

Question. Do I put numbers in the Vsheets? SO I would manually enter my paycheck on page income vlookup? If I go out to eat then I would put it in dining vlookup? And so on?
The only sheet you input anything really is the '2015 spending breakdown' sheet.
enter the subcategory, payee, amount, date, notes....the main category has the vlookup function based on the subcategory.
the remaining individualized tabs are automated to sum the main categories. (you can filter out the 'nulls' on the individualized tabs)

so if i go to the grocery, i come back home, go to the 2015 spending breakdown tab, enter the subcategory, payee, amount, date, notes...everything else is automated (all negative amounts are formatted to show red, all positive amounts are formatted to show green, anytime income appears in the main category it is formatted for a brighter green, and anytime savings appears in the main category it is formatted for light green...all of that is automated though)

You can obviously modify the 'vlookup table' tab to fit your needs and specific categories
i think i arbitrarily stopped my vlookup formulas at row 79....so if your categories on the vlookup table tab go beyond that, just modify the formula in column A on the 2015 spending breakdown tab to accommodate

that answer everything?

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #5 on: January 08, 2015, 09:45:20 AM »
Yeah I think I messed it up lol. I use spreadsheets but I am not a pro. I will tinker around with this and see how it goes.

I will use one to test it out and get a hang for it how it works.

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #6 on: January 08, 2015, 09:45:41 AM »
It's like you read my mind. I was searching all over and checked my google drive for a spreadsheet I might be able to use. I am trying to find a weekly, Monthly, quarterly, yearly, all tied into one.

I went back to Mint.com but my credit union will not support them. I have some spreadsheets but not quite what I want them to be. I figure it's a new year so why not try to track from the beginning.

I probably won't stick with a hard and set budget but I would like to attempt to track our money a little better. My faults in the past has been having too many categories and I just need to simplify it down.

good call...this has got me thinking now...i may have to do that

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #7 on: January 08, 2015, 09:54:29 AM »
I like it but I already messed my download up playing with it. Think I have the hang of how this one works. I like the simple input on the main page. I'll have to delete this one and try again lol

I feel stupid now. I can remove the nulls which then makes it a little easier for me to grasp. Still playing with it.
« Last Edit: January 08, 2015, 10:01:45 AM by b4u2 »

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #8 on: January 08, 2015, 10:14:37 AM »
I like it but I already messed my download up playing with it. Think I have the hang of how this one works. I like the simple input on the main page. I'll have to delete this one and try again lol

I feel stupid now. I can remove the nulls which then makes it a little easier for me to grasp. Still playing with it.
yea...filter the nulls and its all good

just figured out how to break out the main '2015 spending breakdown tab' into separate monthly tabs that compute automatically. It will be easy to sum it by quarter now. thanks for the idea. i can post it when i'm done.

i have a lot of down time at 'work'....so i play with financial spreadsheets all day

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #9 on: January 08, 2015, 10:18:12 AM »
I like it but I already messed my download up playing with it. Think I have the hang of how this one works. I like the simple input on the main page. I'll have to delete this one and try again lol

I feel stupid now. I can remove the nulls which then makes it a little easier for me to grasp. Still playing with it.
yea...filter the nulls and its all good

just figured out how to break out the main '2015 spending breakdown tab' into separate monthly tabs that compute automatically. It will be easy to sum it by quarter now. thanks for the idea. i can post it when i'm done.

i have a lot of down time at 'work'....so i play with financial spreadsheets all day

I like that idea even better! I am still working with it. I have down time at work also but not as Excel savy as I would like to be. On the vlookup sheets could a house payment option be added? So I could see the monthly/yearly total for house payment?

A home Vlookup sheet

Some of the categories don't seem to work. If I go to the 2015 spending breakdown and put in fitness in sub cat it puts #NA in main. Same for home. I haven't tried others yet.

Scratch that. I need to watch the categories better.

I kinda figured out how to add a new page for Home.

The more I use it the more I like it. I started filling in since it's only the 8th. I prefer to not look at it as a budget but more to get a better idea of where the money goes. If you make any changes to the original let me know and I'll hopefully be able to use them as well.
« Last Edit: January 08, 2015, 12:09:25 PM by b4u2 »

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #10 on: January 08, 2015, 12:13:36 PM »
I like it but I already messed my download up playing with it. Think I have the hang of how this one works. I like the simple input on the main page. I'll have to delete this one and try again lol

I feel stupid now. I can remove the nulls which then makes it a little easier for me to grasp. Still playing with it.
yea...filter the nulls and its all good

just figured out how to break out the main '2015 spending breakdown tab' into separate monthly tabs that compute automatically. It will be easy to sum it by quarter now. thanks for the idea. i can post it when i'm done.

i have a lot of down time at 'work'....so i play with financial spreadsheets all day

I like that idea even better! I am still working with it. I have down time at work also but not as Excel savy as I would like to be. On the vlookup sheets could a house payment option be added? So I could see the monthly/yearly total for house payment?

A home Vlookup sheet

Some of the categories don't seem to work. If I go to the 2015 spending breakdown and put in fitness in sub cat it puts #NA in main. Same for home. I haven't tried others yet.

Scratch that. I need to watch the categories better.

I kinda figured out how to add a new page for Home.

The more I use it the more I like it. I started filling in since it's only the 8th. I prefer to not look at it as a budget but more to get a better idea of where the money goes. If you make any changes to the original let me know and I'll hopefully be able to use them as well.
Yea...same here...i'm not really using it as a budget, but like you said, i use it to see more where i am spending my money, which i guess in turn, does tie back into your budget. this is mainly for tracking purposes for me...because i'm over analytical like that and one of my goals this year is to track every dime i spend :)

I'll let you know as i update things

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #11 on: January 08, 2015, 01:51:23 PM »
how is this looking b4u2?

ive attached a blank copy and a mock finished version.

the only thing you should be inputting are subcategory, payee, amount, date, and notes (columns B-F) on the 2015 Spending Breakdown tab....(whatever you type in for subcategory must be on the 'vlookup table' tab in order for it to fill in the main category)
Everything else in the entire workbook is automated. Again, you can modify the Vlookup Table tab to fit your own expense categories.

once the month is over, go to that month's individual tab and filter out the 'blanks' for the date column. You can then sort by category or however you wish and could probably create a nice little graph from this after you totaled the individual categories.

same with each quarter....once each quarter is over, go to that quarter's individual tab and filter out the 'blanks' from the date column.

for the individual vlookup tabs at the end in light red (income, savings, dining, etc)...you will need to re-filter all of the 'nulls' out of the date column as you add things in the main '2015 spending breakdown' tab in order to 'refresh' these lists.

im sure there is a much easier, prettier, and more efficient way to do this....but this works for me.

[attachment deleted by admin]

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #12 on: January 08, 2015, 02:17:02 PM »
how is this looking b4u2?

ive attached a blank copy and a mock finished version.

the only thing you should be inputting are subcategory, payee, amount, date, and notes (columns B-F) on the 2015 Spending Breakdown tab....(whatever you type in for subcategory must be on the 'vlookup table' tab in order for it to fill in the main category)
Everything else in the entire workbook is automated. Again, you can modify the Vlookup Table tab to fit your own expense categories.

once the month is over, go to that month's individual tab and filter out the 'blanks' for the date column. You can then sort by category or however you wish and could probably create a nice little graph from this after you totaled the individual categories.

same with each quarter....once each quarter is over, go to that quarter's individual tab and filter out the 'blanks' from the date column.

for the individual vlookup tabs at the end in light red (income, savings, dining, etc)...you will need to re-filter all of the 'nulls' out of the date column as you add things in the main '2015 spending breakdown' tab in order to 'refresh' these lists.

im sure there is a much easier, prettier, and more efficient way to do this....but this works for me.

I like it! I think I should be able to copy and paste my info from the other sheet I was using into the new. So far so good. It also works with Google spreadsheets since I don't have a copy of excel at home.

MDM

  • Senior Mustachian
  • ********
  • Posts: 11477
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #13 on: January 08, 2015, 03:00:25 PM »
A different objective than the spending tracker discussed above, and not sure it rises to the level of the subject description, but the one downloadable from http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228 has been useful to various people.

There is also http://forum.mrmoneymustache.com/welcome-to-the-forum/spreadsheet-collection/ to peruse.

Runge

  • Stubble
  • **
  • Posts: 220
  • Location: TX
Spreadsheet
« Reply #14 on: January 08, 2015, 05:42:59 PM »
I've got quite a massive spreadsheet going in Google Spreadsheets. Here's the cleaned out version...hope I didn't miss anything... https://docs.google.com/spreadsheets/d/1EbJzudxILhjOsTsiIHM2eBHrWtJ-6PCLU8IyBW8gSCg/edit?usp=sharing

It has a very simple savings goals tracker tab (Goals).
An investment portfolio and asset allocation tracker with rebalancing (Cumulative and Itemized).
A portfolio performance section (Combined, Taxed, Roth, Trad)
And a FIRE tab that has an estimated time to FIRE and SWRs.

There's also a few more tabs on Roth Conversion ladder Fire income and a very simple taxes tab. The last two are mainly a result of me having an idea, but I couldn't fully develop it that well at that time. It might be just total junk, haha.

I'm still in the accumulation phase, so it might be tailored to that more than someone who's already FIRE'd.

I could spend about an hour explaining it verbally to someone as it's decently involved, but just tinkering around with it might help you out some. I've tried to be consistent in keeping all the fields that aren't inputs colored a different color (i.e. grey, orange, etc.) so all of the input fields are a white background. There's a start year cell on the top row of the FIRE tab, that will get you in the right year that you've stared tracking all the stuff.

There's no budget or expense tracking it in as I do all of that on my end through YNAB, so I don't really need a spreadsheet for that...haha.

Let me know what y'all think. P.s. I apologize if it takes me a while to get back. Busy night tonight.

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Spreadsheet
« Reply #15 on: January 09, 2015, 06:38:59 AM »
I've got quite a massive spreadsheet going in Google Spreadsheets. Here's the cleaned out version...hope I didn't miss anything... https://docs.google.com/spreadsheets/d/1EbJzudxILhjOsTsiIHM2eBHrWtJ-6PCLU8IyBW8gSCg/edit?usp=sharing

It has a very simple savings goals tracker tab (Goals).
An investment portfolio and asset allocation tracker with rebalancing (Cumulative and Itemized).
A portfolio performance section (Combined, Taxed, Roth, Trad)
And a FIRE tab that has an estimated time to FIRE and SWRs.

There's also a few more tabs on Roth Conversion ladder Fire income and a very simple taxes tab. The last two are mainly a result of me having an idea, but I couldn't fully develop it that well at that time. It might be just total junk, haha.

I'm still in the accumulation phase, so it might be tailored to that more than someone who's already FIRE'd.

I could spend about an hour explaining it verbally to someone as it's decently involved, but just tinkering around with it might help you out some. I've tried to be consistent in keeping all the fields that aren't inputs colored a different color (i.e. grey, orange, etc.) so all of the input fields are a white background. There's a start year cell on the top row of the FIRE tab, that will get you in the right year that you've stared tracking all the stuff.

There's no budget or expense tracking it in as I do all of that on my end through YNAB, so I don't really need a spreadsheet for that...haha.

Let me know what y'all think. P.s. I apologize if it takes me a while to get back. Busy night tonight.
I like it...i also have tabs for IRA/401(k) tracking, a net worth tracker, year end salary totals (gross, net, taxes paid), and a tab breaking down taxes paid each year.

I do not have anything tracking how close i am to FI, which is what i want...so i will definitely go over those tabs on your spreadsheet and see if i want to steal it or tweak it for myself...thank you!

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #16 on: January 09, 2015, 06:57:08 AM »
thanks for the links MDM...i will definitely check them out!

b4u2

  • Pencil Stache
  • ****
  • Posts: 639
  • Age: 46
  • Location: Cedar Rapids, IA
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #17 on: January 09, 2015, 12:23:56 PM »
Using the new sheet and so far still liking it. Sucks manually inputting everything in from my credit union website but shame on them for not making it easier.

andy85

  • Handlebar Stache
  • *****
  • Posts: 1060
  • Age: 38
  • Location: Louisville, KY
Re: Anybody feel like sharing their 'badass' spreadsheet?
« Reply #18 on: January 09, 2015, 12:41:03 PM »
Using the new sheet and so far still liking it. Sucks manually inputting everything in from my credit union website but shame on them for not making it easier.
i track all of my stuff through Mint as well and although i love mint, i'd like to see savings, income, credit card expenses, debit card expenses, and especially stuff i pay for with cash, all consolidated like i have it. personal preference i guess. like i said, my goal this year is to track every dime i spend. this will help keep me honest.

glad you are finding some use for it!

 

Wow, a phone plan for fifteen bucks!