Author Topic: One sheet to rule them all...  (Read 55269 times)

BabyShark

  • Stubble
  • **
  • Posts: 158
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #100 on: December 22, 2016, 06:17:33 PM »
This is the dumbest question ever but I know on the January tab, I should put in December's expenses.  But I put in January's paychecks?

Imustacheyouaquestion

  • Stubble
  • **
  • Posts: 137
Re: One sheet to rule them all...
« Reply #101 on: December 23, 2016, 07:54:37 AM »
What's the difference between "discretionary" and "monthly excess" categories?

khangaroo

  • 5 O'Clock Shadow
  • *
  • Posts: 82
  • Age: 28
  • Location: Clackamas, OR
    • My Blog
Re: One sheet to rule them all...
« Reply #102 on: December 23, 2016, 08:51:13 AM »
Whooo! Found this at the perfect time for the new year! Definitely going to supplement this to my other spreadsheet and hopefully eventually move to one. Currently it takes me around 15-30 mins to pay all my bills and record everything on my other one but this one looks like it's going to take much longer, I guess something to try with this coming new year!

Thanks OP!!
"Live like no one else so that later you can live and give like no one else" - Dave Ramsey
My Story of How I Paid off $81k of Debt in 1 Year and got completely debt-free! http://khang-nguyen.com/?p=624

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #103 on: December 24, 2016, 06:37:53 AM »
This is the dumbest question ever but I know on the January tab, I should put in December's expenses.  But I put in January's paychecks?

Never a dumb question.  You should put January expenses and paycheck in the January tab.  The sheets don't technically use that data until the 1st of the next month.  So Jan data gets calculated in on Feb 1.  If you look at the FI tab and scroll down you can see it is always a month behind as you can't calculate the data until it is all in at the end of the month.  That is why it updates on the 1st.

Also on the 1st you will update all your accounts on the NW sheet so it takes a "snapshot" at that point in time of each account's value.  Notice on the NW tab your expenses do lag one month behind as you can't calculate those until the end of the month.  So on the 1st of Feb you put in all account values on that date and add the expenses to the Jan row.  The NW tab keeps track of all your account balances and expenses over time even when you start over for a new year.  That data is much of the data that is used on the dashboard.

To start over in Jan just "Save as" "Make a copy..."  a new sheet and call it  FI Tracker 2017.  Then you just need to update the Jan sheet to 1/1/2017 and the rest of the sheets will update and all the data in the FI sheet will go blank until the first of the month.

What's the difference between "discretionary" and "monthly excess" categories?

Nothing really they are just left over stuff that you try not to spend on.  I think they were divided that way in the original sheet and it has just remained.


« Last Edit: December 24, 2016, 07:52:12 AM by wienerdog »

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #104 on: December 24, 2016, 06:52:43 AM »
Whooo! Found this at the perfect time for the new year! Definitely going to supplement this to my other spreadsheet and hopefully eventually move to one. Currently it takes me around 15-30 mins to pay all my bills and record everything on my other one but this one looks like it's going to take much longer, I guess something to try with this coming new year!

Thanks OP!!

Keeping up with two might take a little longer.  This one looks complicated but it isn't actually that bad.  What I do is when I pay my electricity bill online I just pull up the sheet and enter the value for that month.  Anytime I pay a bill I just enter it at that time.  It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.

I don't use Mint to track my bank account only credit cards so when I use the bank account to pay bills I just manually enter it into the sheet.  My day to day spending like groceries, gas etc all get put on a card and I use Mint to sort those and total them for me then I just cut and paste to the current months expense sheet at the end of the month.

For the new year as mentioned above just open the sheet and "Save as" "Make a copy..." and name it FI Tracker 2017.  Update the date on the Jan sheet cell A1 to 2017 and you should be good to go.  If you you have old monthly data you could put those in the NW sheet for the respective dates or just change the first row to 1/1/2017 and the second to 2/1/2017.  Cut and paste those two on down that column and you should have all the new dates for the next months to come.  Mine goes out to 2025 I think right now.  I haven't done that for a while so I would be interested to see how that goes.  I think the color formatting for the year will be off so that might need to be redone.
« Last Edit: December 24, 2016, 07:51:38 AM by wienerdog »

somers515

  • 5 O'Clock Shadow
  • *
  • Posts: 42
Re: One sheet to rule them all...
« Reply #105 on: December 24, 2016, 07:48:29 AM »

It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.


I'm the same way.  I use a version of the "one sheet to rule them all" and definitely enjoy updating the sheet each month.  I read your post to my wife and she laughed.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #106 on: December 24, 2016, 07:56:15 AM »

It is kinda funny but I actually look forward to the 1st of the month just to see the sheet update.


I'm the same way.  I use a version of the "one sheet to rule them all" and definitely enjoy updating the sheet each month.  I read your post to my wife and she laughed.

A couple of times I have updated the sheet after the end of business say on the 31st but the sheet won't update until midnight.  If I happen to wake up at 3am normally you would just roll over and go back to bed but on the 1st you have to get up and see what the sheet did since you went to bed.  lol  Like a kid at Christmas every month!

BabyShark

  • Stubble
  • **
  • Posts: 158
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #107 on: December 29, 2016, 07:51:09 AM »
Thanks wienerdog!  Is there anyway to force an update to the sheet? I've just put in almost all of my 2016 data (minus December) and was eager to see what it looks like, but I guess I can wait a few days if I have to.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #108 on: December 31, 2016, 07:17:07 AM »
The NW tab drives most of the data. If you want to cheat change the date in column A of that sheet.  If you have all your data in there for December 2016 (column A will be 1/1/2017) then change 1/1/2017 to today's date and it will update it for you or just wait until midnight tonight.  lol 

smacpa

  • 5 O'Clock Shadow
  • *
  • Posts: 30
Re: One sheet to rule them all...
« Reply #109 on: January 02, 2017, 09:36:35 AM »
I feel dumb, can't get this to pull into my googlesheets as editable?


*nevermind just took me 10 minutes to figure out...lol
« Last Edit: January 02, 2017, 09:54:58 AM by smacpa »

lamprey

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #110 on: January 06, 2017, 02:04:25 PM »
Thank you so much for this awesome sheet! As someone who doesn't spreadsheet often, I have a question that's probably super basic...

I have a lot of categories in Mint, so that I can keep really close tabs on where my money goes. All of my categories have many sub-categories. Most of the sub-categories have only one or two items throughout the year. So what steps should I take to either:

a) add more lines to the expense categories on the FI page, or
b) export the CSV from Mint so that it only includes top level categories (e.g. just "Education" rather than "Tuition", "Books and Supplies", "Tutorials")

Thanks!

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #111 on: January 07, 2017, 01:11:45 PM »
There are a couple ways to do it.  Under the mint tab for each category under the dollar value there is this formula.  This example uses the pet category:

Code: [Select]
=sumif(F$45:F$98,"Pet Food & Supplies",D$45:D$98)
The formula will look through lines 45 - 98 and total the value from the D column if the F column matches the text "Pet Food & Supplies".

If you wanted to sum a couple different categories from mint under one category in the FI sheet you would add additional fields to the formula.  For example for Pets:

Code: [Select]
=sumif(F$45:F$98,"Pet Food & Supplies",D$45:D$98) + sumif(F$45:F$98,"Pet Grooming",D$45:D$98) + sumif(F$45:F$98,"Veterinary",D$45:D$98)
This new formula would total all the categories under Pet in mint to the FI sheet category.  Note it only scans 43 lines that you paste in the mint sheet at the bottom.  If you have more than 43 entries for the month you might change the second number to 200 in every formula under the categories.  That will make sure it picks up all the entries.

That route is probably easier but then you lose all the categories for fine tracking.  If you wanted to insert more categories I think it isn't too bad as the formulas should adjust.  I haven't tried this but did do a quick check to see if it seems to work.

If you go to line 28 on the FI sheet that is the Pet category. (you can pick anywhere I just using the pet as an example) If you click on the row 28 and highlight the whole row.  Then up to Insert up in the menu bar and Row below.  That will insert one blank row below the pet category.  If you want more new categories you can hit Ctrl + y nine times and that will repeat what you just did 9 more times.  So you get a total of 10 new rows.  You will then need to add titles to them. 

Once you have the FI sheet complete then you need to go to each months sheet and insert the same number of rows.  You then need to copy the formula from right above where you inserted the row (in my example you would copy the Pet formula and then paste it to the new rows.  Once you do that then the categories should match what you entered on the FI sheet. 

Then you have to do the same thing in the mint sheet and update the formulas similar to what I did in the first example so the new categories match your mint categories.

I hope that helps.


lamprey

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #112 on: January 08, 2017, 07:34:14 PM »
Thank you, worked like a charm! Seriously, the dashboard needle is just awesome to see.

ssbsts

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #113 on: January 08, 2017, 08:15:26 PM »
Thank you so much for this sheet, it is so amazing and exactly what I need.  I had one question, more just looking for opinions:

I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis.  So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck.  Then every quarter I purchase and then immediately sell said stock.  This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that?  Or does it even matter on the monthly tabs?  I originally added it as a third paycheck, but I can't decide if that is correct.  Also, if I dont add it in it seems like it affects my savings rate numbers. 

Just curious what people's thoughts are.

Thanks!

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #114 on: January 09, 2017, 11:07:04 AM »
Thank you so much for this sheet, it is so amazing and exactly what I need.  I had one question, more just looking for opinions:

I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis.  So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck.  Then every quarter I purchase and then immediately sell said stock.  This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that?  Or does it even matter on the monthly tabs?  I originally added it as a third paycheck, but I can't decide if that is correct.  Also, if I dont add it in it seems like it affects my savings rate numbers. 

Just curious what people's thoughts are.

Thanks!

The way you are doing it now it gets calculate in the savings rate as it probably falls into unallocated cash every 3 months.  This basically is the difference between the total income - taxes - investments - mortgage - charity.  But technically you are saving that amount every month so it is better to do as below.

It is a deduction but it sounds like you get the money eventually but it is always yours that you saved.  To me it should be included in the saving rate each month.  That is really all the monthly sheet is calculating for you.  It totals what you bring in,  what goes to taxes and what goes to savings (401k, mortgage, cash left over etc).  What I would do is create a formula under one of the investment categories and sum the totals of the deduction from the your paycheck each month.  Putting that total up under one of the 6 investment categories would make sure it gets calculate in for your savings rate when it was actually saved.

If you need help ask or look at how the 401K is done as a deduction.  It takes the deductions for all the paychecks then adds in the employer match and totals that at the very end of each check.  You could insert a new line for your special deduction and then use one of the investment categories to set that box equal to your total from down bottom in the check area. 

Since it is totaled in for the month it will be calculated in.  Every quarter then I would assume you see a jump on your NW tab of that amount documented under one of the accounts.  You are saving that amount every quarter somewhere right?  :>)

dlawson

  • 5 O'Clock Shadow
  • *
  • Posts: 37
  • Age: 27
  • Location: Southern California
Re: One sheet to rule them all...
« Reply #115 on: January 11, 2017, 05:27:41 PM »
So, quick high-level question:

Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.

Or am I doing something wrong?

ssbsts

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #116 on: January 11, 2017, 11:37:31 PM »
Thank you so much for this sheet, it is so amazing and exactly what I need.  I had one question, more just looking for opinions:

I have money deducted from each paycheck that is used to purchase company stock (ESPP) on a quarterly basis.  So I have the ESPP deduction in my list of deductions in the gross pay section of each paycheck.  Then every quarter I purchase and then immediately sell said stock.  This provides me a lump sum of money every three months and I was just wondering what everyone thinks is the best way to reflect that?  Or does it even matter on the monthly tabs?  I originally added it as a third paycheck, but I can't decide if that is correct.  Also, if I dont add it in it seems like it affects my savings rate numbers. 

Just curious what people's thoughts are.

Thanks!

The way you are doing it now it gets calculate in the savings rate as it probably falls into unallocated cash every 3 months.  This basically is the difference between the total income - taxes - investments - mortgage - charity.  But technically you are saving that amount every month so it is better to do as below.

It is a deduction but it sounds like you get the money eventually but it is always yours that you saved.  To me it should be included in the saving rate each month.  That is really all the monthly sheet is calculating for you.  It totals what you bring in,  what goes to taxes and what goes to savings (401k, mortgage, cash left over etc).  What I would do is create a formula under one of the investment categories and sum the totals of the deduction from the your paycheck each month.  Putting that total up under one of the 6 investment categories would make sure it gets calculate in for your savings rate when it was actually saved.

If you need help ask or look at how the 401K is done as a deduction.  It takes the deductions for all the paychecks then adds in the employer match and totals that at the very end of each check.  You could insert a new line for your special deduction and then use one of the investment categories to set that box equal to your total from down bottom in the check area. 

Since it is totaled in for the month it will be calculated in.  Every quarter then I would assume you see a jump on your NW tab of that amount documented under one of the accounts.  You are saving that amount every quarter somewhere right?  :>)

Perfect, thanks for the detailed explanation.  Much better than what I had.  And again thanks for your contributions to this sheet.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #117 on: January 12, 2017, 11:05:25 AM »
So, quick high-level question:

Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.

Or am I doing something wrong?

I think you're looking at it wrong.  The NW tab and FI calculation use the last months data.  How would you track your spending for the month if it is not complete yet?  The Jan tab is a place to store how much money you took in that month and how much you spent that month.  It also trys to show you where that money went.  At the very end of the month (IE  Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month.  It also calculates your savings rate from the previous months data and your FI date.  So in all of Jan you know your net worth and your savings rate from the month before.  You can't calculate your new rate until you have more data at end of the month.

You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.

dlawson

  • 5 O'Clock Shadow
  • *
  • Posts: 37
  • Age: 27
  • Location: Southern California
Re: One sheet to rule them all...
« Reply #118 on: January 15, 2017, 12:01:15 PM »
So, quick high-level question:

Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.

Or am I doing something wrong?

I think you're looking at it wrong.  The NW tab and FI calculation use the last months data.  How would you track your spending for the month if it is not complete yet?  The Jan tab is a place to store how much money you took in that month and how much you spent that month.  It also trys to show you where that money went.  At the very end of the month (IE  Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month.  It also calculates your savings rate from the previous months data and your FI date.  So in all of Jan you know your net worth and your savings rate from the month before.  You can't calculate your new rate until you have more data at end of the month.

You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.

Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)

GetSmart

  • 5 O'Clock Shadow
  • *
  • Posts: 72
Re: One sheet to rule them all...
« Reply #119 on: January 15, 2017, 12:08:16 PM »
OH! I know I'm going to spend some major time playing with this !  Just found it, so I wonder if it's possible to input starting now in Jan and going forward and also input data backwards from December ?  Will the calculations work since you recommend a new sheet each year - will they eventually sync ?  Or should I just forget about the past and move forward ? 

Thanks for making this - it's so cool to have everything in one place !

BabyShark

  • Stubble
  • **
  • Posts: 158
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #120 on: January 15, 2017, 12:47:10 PM »
So, quick high-level question:

Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.

Or am I doing something wrong?

I think you're looking at it wrong.  The NW tab and FI calculation use the last months data.  How would you track your spending for the month if it is not complete yet?  The Jan tab is a place to store how much money you took in that month and how much you spent that month.  It also trys to show you where that money went.  At the very end of the month (IE  Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month.  It also calculates your savings rate from the previous months data and your FI date.  So in all of Jan you know your net worth and your savings rate from the month before.  You can't calculate your new rate until you have more data at end of the month.

You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.

Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)

I thought the same thing!!

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #121 on: January 15, 2017, 12:55:09 PM »
OH! I know I'm going to spend some major time playing with this !  Just found it, so I wonder if it's possible to input starting now in Jan and going forward and also input data backwards from December ?  Will the calculations work since you recommend a new sheet each year - will they eventually sync ?  Or should I just forget about the past and move forward ? 

Thanks for making this - it's so cool to have everything in one place !

You can fill back data on the NW tab.  That is really the only tab that keeps data from year to year besides the Mortgage tab.  You can put in as much as you have.  My data starts back in July of 2015.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #122 on: January 15, 2017, 01:27:54 PM »
So, quick high-level question:

Why don't the monthly tabs key off the last day of the month instead of the first day of the month? Currently the January tab covers the state of finances on January 1st, which effectively means it's tracking December rather than January, which effectively means the spreadsheet as a whole is tracking December 2016 - November 2017. And December 2017 will be covered in next year's sheet.

Or am I doing something wrong?

I think you're looking at it wrong.  The NW tab and FI calculation use the last months data.  How would you track your spending for the month if it is not complete yet?  The Jan tab is a place to store how much money you took in that month and how much you spent that month.  It also trys to show you where that money went.  At the very end of the month (IE  Feb 1 at midnight or 1 second from Jan 31) it updates your net worth according to your account balances at the ending of the month.  It also calculates your savings rate from the previous months data and your FI date.  So in all of Jan you know your net worth and your savings rate from the month before.  You can't calculate your new rate until you have more data at end of the month.

You can make it update any day you want but the end of the month seems logical to me as that is when I know my total spending and the amount I made for Jan.

Ah, okay, I'm just overthinking it. The January tab has "January 1st, 2017" in big bold letters at the top, which sort of points toward that particular tab covering income/spending in December (as in "all the info below is current as of January 1st"), but obviously that's not how the sheet is set up. Ignore me! :)

I thought the same thing!!

Fixed it!

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #123 on: January 15, 2017, 01:34:30 PM »
I would love to use this, I did try. But I am so crap at worksheets, I need either a youtube tutorial step by step or a reallllly easy simple worksheet.
I have still not calculated my FI date as a result.
If anyone would like to do a short youtube screencapture video tour of how to use this sheet I would be happy happy happy!

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #124 on: January 15, 2017, 04:51:18 PM »
Dandy what did you have trouble with? I haven't made a screenshot video so that is beyond what I can do.  If you want to try again now is a good time to start.  I would start over with a fresh sheet.  Open the V3 then up top select the File menu option and Make a copy so you have your own copy. 

The first thing you need to do is update the expense categories on the FI sheet.  Use categories that make sense to you and that you want to track.  If you don't have a pet change it to something else.  If you don't use all the categories just leave them blank.

Now go to the NW sheet and delete all the rows so you only have 1/1/2017 and on.  Over on the left select row 3 1/1/2015 by left clicking and holding the mouse button down.  Now drag straight down until you reach 26.  That should highlight rows 3 - 26.  Now up in the Edit menu select the Delete rows 3 - 26.  That will get you NW sheet started this month.  It will blank out some of the calculation but at the end of the month when you update your expenses it will calculate again.

Now in NW sheet update the different titles for accounts that make sense for you.  These are columns G - L and N - S.  Now update the value of those accounts with the value of them today. This will be the 1/1/2017 row.  If you have debts do the same thing for columns T - W.

That should be enough to get you going.  At the end of January update you categories with the amount you spent on each in the Jan sheet.  Update the information about your check / income at the bottom.

See if you can get that far then we will work on your mortgage if you have one so it will auto update.  That isn't needed if you want to ignore it.  Should be able to enter zeros.

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #125 on: January 15, 2017, 07:00:55 PM »
Thanks Weinerdog. It is a good time you are right. I am just hopeless with spreadsheets, dont even know the basics, hubs is even worse than me lol. I will give it another go tomorrow as hubs is good with written directions.
We dont have a mortgage anymore - just paid it off yay!
I really want to see what that needle says :) :)
« Last Edit: January 15, 2017, 07:06:17 PM by dandypandys »

erutio

  • Stubble
  • **
  • Posts: 121
Re: One sheet to rule them all...
« Reply #126 on: January 16, 2017, 10:00:13 AM »
Cool tool, thanks for sharing!

I have some similar spreadsheets of numbers, but you make it so pretty!

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #127 on: January 16, 2017, 07:48:45 PM »
oh crud. Had my husband helping me and we thought we were doing pretty well following the directions, but once done I went back to dashboard and it is all blank, and the needle graphic is gone.
Not sure where we went wrong. Also, how do we set all the mortgage stuff- to basically delete it since we own our house outright now.
Thanks for any help, will try again in the morning with a new saved spreadsheet.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #128 on: January 17, 2017, 09:50:53 AM »
The needle graphic is gone or reads zero?  If it is gone you accidentally deleted it.  If it reads zero that is probably right.   You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.

I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #129 on: January 17, 2017, 10:19:14 AM »
The needle graphic is gone or reads zero?  If it is gone you accidentally deleted it.  If it reads zero that is probably right.   You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.

I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.
Thanks Weinerdog, it is completely gone.
Actually glad I am doing it again, as I didn't realize I should make my categories match exactly to my budget in Mint. Is that right? I should get them the same if I want to copy paste them in using the Mint tab?
« Last Edit: January 17, 2017, 11:36:40 AM by dandypandys »

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
One sheet to rule them all...
« Reply #130 on: January 17, 2017, 06:49:55 PM »
Just popping in to tip my hat to Weinerdog for keeping up with all these questions and replies.

Nice work, the forum is better off with you!


Sent from my iPhone using Tapatalk

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #131 on: January 18, 2017, 07:09:13 AM »
The needle graphic is gone or reads zero?  If it is gone you accidentally deleted it.  If it reads zero that is probably right.   You won't get an update until Feb 1 if you entered the correct data for Jan on the NW tab.

I find the best way to delete the Mort sheet and remove it from the tabs tonight if I get time.
Thanks Weinerdog, it is completely gone.
Actually glad I am doing it again, as I didn't realize I should make my categories match exactly to my budget in Mint. Is that right? I should get them the same if I want to copy paste them in using the Mint tab?

That doesn't matter so much as the actual Mint categories are in the formula the Mint sheet uses.  I explained to another user how that works a few posts up as they found they had too many categories under Mint.  I showed them a way where they could put several Mint categories under on FI Sheet category.  Did you make any ground when you reworked it?

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #132 on: January 18, 2017, 07:19:56 AM »
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.

BabyShark

  • Stubble
  • **
  • Posts: 158
  • Location: Virginia
Re: One sheet to rule them all...
« Reply #133 on: January 18, 2017, 07:24:15 AM »
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.

I do the same thing.  Every month it seems like I'm telling my husband about a new tracking system I'm testing out or something.  He just shakes his head at me and changes the subject because he knows I'm doing it to make sure we're set for our futures.  I use Mint and PC too.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #134 on: January 18, 2017, 10:01:30 AM »
Gotcha! No I gave up again, because I had all these subcategories in Mint too. My husband said not to do this because, he says, I don't need yet another online thing to check every day.
Idk- I do have a little online checking problem, but this seems worth it.
I have Mint and Personal Capital.
Thanks for all your hard work, I probably will just do it once he leaves the house today hahah.

I do the same thing.  Every month it seems like I'm telling my husband about a new tracking system I'm testing out or something.  He just shakes his head at me and changes the subject because he knows I'm doing it to make sure we're set for our futures.  I use Mint and PC too.

I dumped PC long ago.  On the second phone call I deleted my account.  Mint I only use it to track the credit card purchases which is my daily spending.  I guess I entered home value and car value in the beginning but ignore all that.  Never have put in investment accounts and bank accounts so I always show debt if it wasn't for the car and home.  LOL  The new bill thing Mint has going is getting annoying as I know what my bills are.

I have been using Future Advisor for a year or so just to see how I do against the new fangled AI Betterment type portfolios.  So far no bothersome phone calls from them.

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #135 on: January 19, 2017, 10:16:10 AM »
Hello! I am getting along fairly well we my redo.
A few things so far that have tripped me up:
On the Mint sheet I couldn't get the 2 numbers to match, I went through it multiple times making sure that all my rebates, credits, transfers were deleted and that all the categories were going in to the right place.
Figured it out- on lines 10,14, 17 the formula has a shortened amount than the others at 98 instead of 145. Matched perfectly once I noticed it. Thanks for this mint page, works fab.

A)    Now I am having another trip up. In v2 instructions it says in step 2 in FI tab,  to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.

Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.


B)     Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.

C)     On the Monthly Budget Sheet instructions it says  1) The Monthly/Actual. This is the left section where you see the Budgeted and Actual. You should only manually enter numbers in the white cells. Colored cells are automatically calculated, so do not change them unless you want to fiddle with calculations. Note that there is a register-like feature for capturing miscellaneous spending to the right (off the screen). You can add line items here and they will be totaled in the Miscellaneous row of the Monthly/Actual

I couldn't figure out where this was?

D)    LAstly, on the v3 instructions for Net worth it says "Do keep in mind that the first 4 green columns are used in the formulas to calculate your passive income (this can be changed, if you like, in the YMOYL tab formulas)."
I am not sure what 4 green columns you mean?

Thanks for any help. Sorry for being so asky!
« Last Edit: January 19, 2017, 11:10:09 AM by dandypandys »

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #136 on: January 21, 2017, 08:49:22 AM »
Hello! I am getting along fairly well we my redo.
A few things so far that have tripped me up:
On the Mint sheet I couldn't get the 2 numbers to match, I went through it multiple times making sure that all my rebates, credits, transfers were deleted and that all the categories were going in to the right place.
Figured it out- on lines 10,14, 17 the formula has a shortened amount than the others at 98 instead of 145. Matched perfectly once I noticed it. Thanks for this mint page, works fab.

I will check the master mint sheet.  One thing I have found you have to be careful when you are removing extra credits, rebates and transfers.   I use to delete the whole row.  I didn't notice until later it was changing the formula up top to look at less and less lines.  Now I just highlight the row and hit the back space to "blank" that row.  That way it won't change the formulas.

Thanks for the heads up.  I will fix it in the master sheet if it accidentally got into there.

A)    Now I am having another trip up. In v2 instructions it says in step 2 in FI tab,  to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.

Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.

In version 3 one of the forum members mentioned we could only edit the categories on the FI and use formulas to update the rest of them in all the other sheets so you don't have to manually do it.  I thought I got all them in Version 3.  Which ones didn't update in version 3?

B)     Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.

How many rows do you have in the NW sheet?  If you only have one row (IE 1/1/2017) then you should only have account balances and you haven't updated yet with the income and expenses column because January isn't done yet.  Once you have two rows the month before will have your expenses and income.  Then the sheet will be able to calculate your average at E1 and F1.  From there it should update your FI sheet and the gauge.  Why are you waiting until April?  You should update the months row in the NW sheet at beginning of each month or whenever the markets close at the end of the month.


C)     On the Monthly Budget Sheet instructions it says  1) The Monthly/Actual. This is the left section where you see the Budgeted and Actual. You should only manually enter numbers in the white cells. Colored cells are automatically calculated, so do not change them unless you want to fiddle with calculations. Note that there is a register-like feature for capturing miscellaneous spending to the right (off the screen). You can add line items here and they will be totaled in the Miscellaneous row of the Monthly/Actual

I couldn't figure out where this was?

Sorry that was left over from Ver 1 I think.  In that version it was hard to enter new rows as there was stuff farther over to the right.  If you inserted a row it would move everything down.  That was eliminated in Ver 2.  I deleted it in the instructions.  Thanks for the catch!

D)    LAstly, on the v3 instructions for Net worth it says "Do keep in mind that the first 4 green columns are used in the formulas to calculate your passive income (this can be changed, if you like, in the YMOYL tab formulas)."
I am not sure what 4 green columns you mean?

Thanks for any help. Sorry for being so asky!

That was a left over also!  I have edited the master.  Thanks for the input.

VladTheImpaler

  • Stubble
  • **
  • Posts: 212
Re: One sheet to rule them all...
« Reply #137 on: January 21, 2017, 04:47:12 PM »
Hey Weinerdog,

Thanks again for this amazing spreadsheet!
I'm still a spreadsheet novice, so forgive me...

Quick question about cell C5 on the Month sheets (Jan-Dec):

Shouldn't the formula be =sum(B79:B82)
instead =sum(B79:B83)
because medical is a deduction from our pay.
Right? (I might be missing something)
« Last Edit: January 21, 2017, 05:13:13 PM by VladTheImpaler »
"Everyone has different tastes and desires. These differences make the world orderly. If everyone wanted the same things, we would all be struggling against each other to acquire what little was available. Diversity is the source of harmony in human relationships."

-Harry Browne, How I Found Freedom in an Unfree World

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #138 on: January 21, 2017, 06:23:41 PM »
Hey Weinerdog,

Thanks again for this amazing spreadsheet!
I'm still a spreadsheet novice, so forgive me...

Quick question about cell C5 on the Month sheets (Jan-Dec):

Shouldn't the formula be =sum(B79:B82)
instead =sum(B79:B83)
because medical is a deduction from our pay.
Right? (I might be missing something)

Thank IndyPendent not me.  I just modified his work but all credit should go to him.

You are correct but there is a note on C20 that the value there should match your deduction.  It is recorded as an expense so you can track what you spend on it over the years.  It gets added in down below but then subtracted in the expenses.  Good question though.  I did it that way as it made sense at the time to track it.

VladTheImpaler

  • Stubble
  • **
  • Posts: 212
Re: One sheet to rule them all...
« Reply #139 on: January 21, 2017, 08:02:01 PM »
Thanks, that solves it!

(I see what I did...customized all my monthly expenses and removed "Health Insurance" from C20.
So it was not being included in my expenses.

The FI sheet uses C11 (which uses C5) from the monthly sheets to calculate row 57 ("Monthy Income after Tax"), so my income went down a little, which lowered my savings rate for the year a tiny bit.
"Everyone has different tastes and desires. These differences make the world orderly. If everyone wanted the same things, we would all be struggling against each other to acquire what little was available. Diversity is the source of harmony in human relationships."

-Harry Browne, How I Found Freedom in an Unfree World

frugalcanuck

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: One sheet to rule them all...
« Reply #140 on: January 21, 2017, 10:30:13 PM »
Is this the section of the forum where people ask excel spreadsheet questions?  I thought I saw a spreadsheet section when I first joined, but I just looked around and don't see one.

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #141 on: January 22, 2017, 05:54:46 AM »

Thank IndyPendent not me.  I just modified his work but all credit should go to him.


That's being exceedingly modest, WD. I may have started the fire, but you've been adding logs ever since. :D



Sent from my iPhone using Tapatalk

dandypandys

  • Bristles
  • ***
  • Posts: 304
  • Age: 45
  • Location: USA
Re: One sheet to rule them all...
« Reply #142 on: January 22, 2017, 06:44:23 PM »
A)    Now I am having another trip up. In v2 instructions it says in step 2 in FI tab,  to copy and paste the labels you create to A64.
v3 does not say this, instead says it will update automatically, however it hasn't.. it has in the month tabs though which is great.

Should I follow instructions v2 then? So A64 area on FI matches the above part? Don't want to break a formula by mistake.

In version 3 one of the forum members mentioned we could only edit the categories on the FI and use formulas to update the rest of them in all the other sheets so you don't have to manually do it.  I thought I got all them in Version 3.  Which ones didn't update in version 3?

The totals of the FI sheet- starting at A36, in previous versions it told us to copy paste down there


B)     Also not sure why my needle says i have 1.8 yrs til FI. is it just because i will compute after all the january data is entered? I am going to wait until i get to the end of April to do the entering.

How many rows do you have in the NW sheet?  If you only have one row (IE 1/1/2017) then you should only have account balances and you haven't updated yet with the income and expenses column because January isn't done yet.  Once you have two rows the month before will have your expenses and income.  Then the sheet will be able to calculate your average at E1 and F1.  From there it should update your FI sheet and the gauge.  Why are you waiting until April?  You should update the months row in the NW sheet at beginning of each month or whenever the markets close at the end of the month.

OOPS my bad I meant end of Jan :) i left the 2015 line as instructions said to do otherwise the sheet would go wonky .. maybe that got fixed? Thanks for all your help :)


« Last Edit: January 22, 2017, 06:51:39 PM by dandypandys »

frugalcanuck

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: One sheet to rule them all...
« Reply #143 on: January 24, 2017, 04:14:05 PM »
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N.  I only know how to filter groceries and then manually sum up the individual cells that did not collapse.  Is there an easier way to filter?

I have my figures in the attached excel file.

Thanks for any advice.

jamesbond007

  • Stubble
  • **
  • Posts: 205
  • Location: USA
  • One penny at a time.
Re: One sheet to rule them all...
« Reply #144 on: January 24, 2017, 10:55:09 PM »
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?
FI by 2025.

wienerdog

  • Bristles
  • ***
  • Posts: 380
Re: One sheet to rule them all...
« Reply #145 on: January 25, 2017, 04:19:17 PM »
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?

Yes that is fine.  Note that it won't update until you put your income and expenses in at the end of the month.

jamesbond007

  • Stubble
  • **
  • Posts: 205
  • Location: USA
  • One penny at a time.
Re: One sheet to rule them all...
« Reply #146 on: January 25, 2017, 05:40:22 PM »
Ok. I am getting started in the spreadsheet now. Since this is 1/2017, shall I delete everything above 1/1/2017 in the NW sheet?

Yes that is fine.  Note that it won't update until you put your income and expenses in at the end of the month.


Yep absolutely. Thanks. This is great so far. Kudos to all you guys for the hard work.
FI by 2025.

deborah

  • Walrus Stache
  • *******
  • Posts: 5103
Re: One sheet to rule them all...
« Reply #147 on: January 26, 2017, 11:31:02 PM »
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N.  I only know how to filter groceries and then manually sum up the individual cells that did not collapse.  Is there an easier way to filter?

I have my figures in the attached excel file.

Thanks for any advice.
=SUMIF(N$9:N$21,D9,M$9:M$21)



frugalcanuck

  • 5 O'Clock Shadow
  • *
  • Posts: 12
Re: One sheet to rule them all...
« Reply #148 on: February 03, 2017, 02:21:07 PM »
Hi,
How do you get excel to sum up the values of each individual category.. For example, I would like it to give me a groceries total using all the values column M that correspond with the groceries label in column N.  I only know how to filter groceries and then manually sum up the individual cells that did not collapse.  Is there an easier way to filter?

I have my figures in the attached excel file.

Thanks for any advice.
=SUMIF(N$9:N$21,D9,M$9:M$21)

Thank you
This helped alot

mtully

  • 5 O'Clock Shadow
  • *
  • Posts: 1
Feedback on my own sheet to rule them all?
« Reply #149 on: February 04, 2017, 03:02:05 PM »
Hey all,

Really appreciated browsing this thread and thought I'd throw my own "sheet to rule them all" into the mix, primarily to get feedback on it. What works well? What needs to be fixed/adjusted? What am I missing?

https://docs.google.com/spreadsheets/d/18EoyZ4g0dLcY21atwxK25e-XLTKaSVhKT_HWaCf5bak/edit?usp=sharing

Tell me what you think!