Author Topic: Case Study Spreadsheet club (like a book club...only with a spreadsheet...)  (Read 949 times)

Ladychips

  • Bristles
  • ***
  • Posts: 397
I love, love, love MDM's case study spreadsheet (https://forum.mrmoneymustache.com/forum-information-faqs/case-study-spreadsheet-updates/).  But every time I open it, I am quickly overwhelmed and make no progress on filling it out.

I thought it might be worth seeing if any forum folks would be interested in working through the spreadsheet together.  We can ask questions, suggest answers, provide encouragement, etc.  @MDM even said he would help us! Maybe we can do a section a week and see how it goes.

Any takers?

SquashingDebt

  • Stubble
  • **
  • Posts: 205
I might be interested!  I've certainly had the same experience with it so far, haha.

Ladychips

  • Bristles
  • ***
  • Posts: 397
@SquashingDebt - yea!  Glad to have you.  I was hoping we would have more interested, but I'm happy to proceed just the two of us.  People can join in as we go if they want.

Here's my thinking, but I'm open to suggestions.  I'd like to do my reading/studying/practicing on the weekend (likely Sunday) since I'm still a working stiff.  And then post my questions/thoughts/etc. early in the week.  We'd take on a section a week (or so as we see how it goes).

How about we start with the second tab (calculations) - columns A-D, rows 1-20 (essentially the first box)? Those look easy enough for me to start off feeling like I can be successful! Does that work for you?


MDM

  • Walrus Stache
  • *******
  • Posts: 9306
@SquashingDebt - yea!  Glad to have you.  I was hoping we would have more interested, but I'm happy to proceed just the two of us.  People can join in as we go if they want.

Here's my thinking, but I'm open to suggestions.  I'd like to do my reading/studying/practicing on the weekend (likely Sunday) since I'm still a working stiff.  And then post my questions/thoughts/etc. early in the week.  We'd take on a section a week (or so as we see how it goes).

How about we start with the second tab (calculations) - columns A-D, rows 1-20 (essentially the first box)? Those look easy enough for me to start off feeling like I can be successful! Does that work for you?
As you go through this, your thoughts on how to improve the Instructions tab would be appreciated.  Or, in general, how to "improve usability for new users."  For better or worse, I've lost the perspective of a new user, so if you can help me help others, that would be great.

Ladychips

  • Bristles
  • ***
  • Posts: 397
Absolutely!  I'm not all that bright so if I can understand the instructions, anyone can!

SquashingDebt

  • Stubble
  • **
  • Posts: 205
@Ladychips Sounds great!  Sorry for the delayed reply - just had a brutal 2 days at work.  I'll tackle that first box today or tomorrow and report back!

SquashingDebt

  • Stubble
  • **
  • Posts: 205
Ok, I filled in the first section!  Some other context to share as we go along:  single, 32, with fairly straightforward finances - W2 earnings only, 401k, Roth IRA, HSA, no debt (renter), maybe my first taxable investments later this year if I finish my new car savings.


@MDM, I have my first comment/question for you: 

The only thing in this first section that slowed me down was how to handle my employer-sponsored HSA situation. 

I contribute $75 per biweekly paycheck (deducted from my gross salary) and my employer contributes $125 per month ($1500 per year), for a total of $3450.  I put that whole $3450 in cell B7.  But then I figured I should add the $1500 from my employer contribution to my gross salary, right?

And the personal HSA (B45) is for if I contribute directly to my HSA, not through my employer (and thus not getting the deduction from my FICA-eligible wages)?  So I'll put that at zero.

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
Ok, I filled in the first section!  Some other context to share as we go along:  single, 32, with fairly straightforward finances - W2 earnings only, 401k, Roth IRA, HSA, no debt (renter), maybe my first taxable investments later this year if I finish my new car savings.


@MDM, I have my first comment/question for you: 

The only thing in this first section that slowed me down was how to handle my employer-sponsored HSA situation. 

I contribute $75 per biweekly paycheck (deducted from my gross salary) and my employer contributes $125 per month ($1500 per year), for a total of $3450.  I put that whole $3450 in cell B7.  But then I figured I should add the $1500 from my employer contribution to my gross salary, right?

And the personal HSA (B45) is for if I contribute directly to my HSA, not through my employer (and thus not getting the deduction from my FICA-eligible wages)?  So I'll put that at zero.
Good questions!

Yes, B45=0 for your situation is correct.

As for how employer HSA contributions go, there are a couple of options and either can work.  You could either
- Put your $1950 in B7 and add $1500 in B19 to whatever 401k match you get, or
- Put all $3450 in B7 and add $1500 to gross salary/wages, as you figured.

I think "employer match" is more straightforward, instead of adding the HSA match to "gross salary/wages," but it's a matter of taste.

Doing it the latter way might be slightly more accurate in terms of "time to FI" calculations (because the HSA balance isn't taxed on withdrawal while the 401k balance is), but the uncertainty in those calculations is so large anyway that I'd stick with the simpler approach.  But, again, there is nothing "wrong" either way.

Ladychips

  • Bristles
  • ***
  • Posts: 397
@SquashingDebt - yea!  Progress.  Thanks for the motivation - it helped me work through the box this morning!

Our situations are really different.  I think that means we'll learn a lot from our different perspectives. I'm 50+, married, W-2 jobs, 403b/457s, and rental property.  Looking to retire in 2 years (and 2 weeks).

@MDM - I'm thinking the first box is the easiest box...and it took me some time before I could get the numbers to come out right!  I notice in other boxes you have a comments in the cells for instructions.  Is it too much to put comments in this section that points people to the right W-2 boxes for this section.  My husband has info in 14 (other).  Took me a while to figure out where that was supposed to go!

Ladychips

  • Bristles
  • ***
  • Posts: 397
How about we do columns G-I, rows 2-40 for next time?  @MDM, what's the deal with Mort (Int) - H34.  I understand the directions (put in 1000000) but I don't know why. 

Also, I don't yet understand WHERE and in WHAT ORDER information goes.  For example, in the non paycheck income box (week after next I'm guessing), am I figuring those numbers on another tab, pulling from my taxes, figuring outside the spreadsheet and adding?  I appreciate your patience!!

SquashingDebt

  • Stubble
  • **
  • Posts: 205
Ok, I filled in the first section!  Some other context to share as we go along:  single, 32, with fairly straightforward finances - W2 earnings only, 401k, Roth IRA, HSA, no debt (renter), maybe my first taxable investments later this year if I finish my new car savings.


@MDM, I have my first comment/question for you: 

The only thing in this first section that slowed me down was how to handle my employer-sponsored HSA situation. 

I contribute $75 per biweekly paycheck (deducted from my gross salary) and my employer contributes $125 per month ($1500 per year), for a total of $3450.  I put that whole $3450 in cell B7.  But then I figured I should add the $1500 from my employer contribution to my gross salary, right?

And the personal HSA (B45) is for if I contribute directly to my HSA, not through my employer (and thus not getting the deduction from my FICA-eligible wages)?  So I'll put that at zero.
Good questions!

Yes, B45=0 for your situation is correct.

As for how employer HSA contributions go, there are a couple of options and either can work.  You could either
- Put your $1950 in B7 and add $1500 in B19 to whatever 401k match you get, or
- Put all $3450 in B7 and add $1500 to gross salary/wages, as you figured.

I think "employer match" is more straightforward, instead of adding the HSA match to "gross salary/wages," but it's a matter of taste.

Doing it the latter way might be slightly more accurate in terms of "time to FI" calculations (because the HSA balance isn't taxed on withdrawal while the 401k balance is), but the uncertainty in those calculations is so large anyway that I'd stick with the simpler approach.  But, again, there is nothing "wrong" either way.

Interesting... that's a good reminder that I need to get organized with my HSA.  I just entered the workforce 4 years ago after finishing my PhD and spent the first 3.5 years in student loan repayment mode, so I've been using my HSA as intended - to pay medical bills.  Now that it's around $7k and my student loans are gone, I need to figure out how to invest it and start paying my medical bills out of my cash flow and letting my HSA grow.

Looks like this "spreadsheet club" is already paying off!  (Also I love spreadsheets and am having fun in general, haha.)


@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document."  I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
@MDM - I'm thinking the first box is the easiest box...and it took me some time before I could get the numbers to come out right!  I notice in other boxes you have a comments in the cells for instructions.  Is it too much to put comments in this section that points people to the right W-2 boxes for this section.  My husband has info in 14 (other).  Took me a while to figure out where that was supposed to go!
Perhaps something such as "W-2 box 5 plus all amounts in rows 4-8 on this sheet" for cells B3 and C3?

Not sure how to give specific advice on W-2 box 14, other than point to https://www.irs.gov/pub/irs-pdf/fw2.pdf:
Quote
Box 14. Employers may use this box to report information such as state disability insurance taxes withheld, union dues, uniform payments, health insurance premiums deducted, nontaxable income, educational assistance payments, or a member of the clergy’s parsonage allowance and utilities. Railroad employers use this box to report railroad retirement (RRTA) compensation, Tier 1 tax, Tier 2 tax, Medicare tax, and Additional Medicare Tax. Include tips reported by the employee to the employer in railroad retirement (RRTA) compensation.
Thoughts?

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
How about we do columns G-I, rows 2-40 for next time?  @MDM, what's the deal with Mort (Int) - H34.  I understand the directions (put in 1000000) but I don't know why.
It's due to a recent tax law change.  The mortgage interest deduction is limited to the interest on a balance of $750,000 if the mortgage started in 2018 or later, but can be taken on a balance of $1,000,000 otherwise.  For most people it won't matter. ;)

Quote
Also, I don't yet understand WHERE and in WHAT ORDER information goes.  For example, in the non paycheck income box (week after next I'm guessing), am I figuring those numbers on another tab, pulling from my taxes, figuring outside the spreadsheet and adding?  I appreciate your patience!!
However works best for you.  The Non-paycheck income and Subtractions for AGI sections are structured similarly to the way form 1040 used to look: https://www.irs.gov/pub/irs-prior/f1040--2017.pdf.

One needs to know these numbers to file taxes at the end of the year.  Estimating these numbers during the year can help avoid surprises at tax filing time.  Perhaps think of these as your "income budget" (similar to how your "expense budget" might be used when you get to the Monthly Average Expenses in rows 76-129.

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document."  I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.

I could change the text in A51 to "SL int. (calc'd from entries in rows 133-137)" - would that suffice? 

I believe all blank white boxes actually stay blank - unless one removes protection and types something in them - but did you find some that work otherwise?

ETA: A few "blank" boxes will display an error message in some circumstances.  E.g., see H19 and H20.
« Last Edit: June 16, 2019, 11:22:18 AM by MDM »

SquashingDebt

  • Stubble
  • **
  • Posts: 205
@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document."  I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.

I could change the text in A51 to "SL int. (calc'd from entries in rows 133-137)" - would that suffice? 

I believe all blank white boxes actually stay blank - unless one removes protection and types something in them - but did you find some that work otherwise?

ETA: A few "blank" boxes will display an error message in some circumstances.  E.g., see H19 and H20.

I think in that particular instance I was a little thrown off because the rest of the section has values for person 1 and person 2, but that line only had the total.  I don't know that A51 needs any extra info - that was just one example.  I was just thinking in the instructions tab there could be a reminder that everything not green will auto-populate so people don't wonder/worry about it.  Something like "User entries go in the cells with green shaded backgrounds. All other cells will auto-populate based on user entries."  I know that's implied already, but it might help ease people into things if they don't have a lot of experience with spreadsheets.  If you work straight down for example, there are values that won't be updated until you get to later sections of the spreadsheet, which can be a little bit confusing if you don't realize how it works.  I guess it all depends on which level of spreadsheet user you're targeting :)

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
@MDM One other thought about how to make things easier for beginners - it might be helpful to add a line in the instructions along with those in rows 27-28 that says something like "Blank white boxes will be auto-populated using data you insert in green cells elsewhere in the document."  I spent a solid minute or so wondering why the B51 and C51 for example (student loan interest) weren't green, and actually thought it was a mistake, until I figured out that it's sourced from other data.

I could change the text in A51 to "SL int. (calc'd from entries in rows 133-137)" - would that suffice? 

I believe all blank white boxes actually stay blank - unless one removes protection and types something in them - but did you find some that work otherwise?

ETA: A few "blank" boxes will display an error message in some circumstances.  E.g., see H19 and H20.

I think in that particular instance I was a little thrown off because the rest of the section has values for person 1 and person 2, but that line only had the total.  I don't know that A51 needs any extra info - that was just one example.  I was just thinking in the instructions tab there could be a reminder that everything not green will auto-populate so people don't wonder/worry about it.  Something like "User entries go in the cells with green shaded backgrounds. All other cells will auto-populate based on user entries."  I know that's implied already, but it might help ease people into things if they don't have a lot of experience with spreadsheets.  If you work straight down for example, there are values that won't be updated until you get to later sections of the spreadsheet, which can be a little bit confusing if you don't realize how it works.  I guess it all depends on which level of spreadsheet user you're targeting :)
Thanks for the suggestion - easy enough to add for whenever a new version is published.

Probably not going to try to cater to those who have never used a spreadsheet at all, but would like to think that it can be accessible for anyone with "some" spreadsheet knowledge.

Ladychips

  • Bristles
  • ***
  • Posts: 397
@MDM , thanks for your feedback.  Yes, I like the proposed instructions re. the W-2 boxes...every little bit helps.  And 'duh' on the mortgage interest change.

After looking more closely, I think it would have been smarter to just keep going down the page instead of moving over a section.  So, I'm proposing we do Columns A-D, rows 22-41 for next time.

This week's boxes were fairly simple and straightforward.  Yea!

@SquashingDebt, you still with us?? 


MDM

  • Walrus Stache
  • *******
  • Posts: 9306
^Looks good.  Keep the observations coming!

Cell comment changes have been made in the next version.  Not sure when I'll decide to upload that: it's usually one or more of
- correct a blatant error
- provide a significant enhancement
- accumulated a bunch of little things.

SquashingDebt

  • Stubble
  • **
  • Posts: 205
@Ladychips thanks for the reminder!  Just did this week's section.  I agree, pretty straightforward.

@MDM - One comment this time - is this the AOC referenced in H30?  https://www.irs.gov/credits-deductions/individuals/aotc

It might be worth switching to AOTC, since it looks like that's the acronym the IRS uses?

When I googled AOC taxes I got a lot of articles about the person AOC (Alexandria Ocasio-Cortez) and her taxes, haha.

MDM

  • Walrus Stache
  • *******
  • Posts: 9306
^Hah!  Yes, no political opinions intended or implied there.  Changed cell text to AOTC and spelled out the whole name in the comment.

Ladychips

  • Bristles
  • ***
  • Posts: 397
@MDM , I'm in no hurry for a new version.  I so appreciate your helping us work through it!

@SquashingDebt, glad you are still with us!!  I'm thrilled I'm not having to do it alone!