The Money Mustache Community
Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: Future Lazy on December 11, 2014, 04:09:55 PM
-
Hi all!
So recently I have been trying to come up with a good system for calculating my True Cost Of Commuting according to the car we own, the repairs it needs and the distance I commute to work. After messing up my formula and math a billion times and having to recalculate it on paper an equal billion times, I realized, DUH I CAN MAKE A SPREADSHEET
But I don't know how to do spreadsheets very good, so here is my first 'final draft':
https://docs.google.com/spreadsheets/d/1jzGt-QxiO5iYiFw5PkoX-UVOnr6pj64zDqDhAJRRNNI/edit?usp=sharing
Everything seems to look really good and the numbers add up, but I'm not completely sure it's solid.
Can all you spreadsheet magicians and mathematical wizards out there take a look and suggest any changes or improvements? Especially if there are corrections to make!
-
In column K both overtime pays are not multiplied by their loading. Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect. Your yearly calculations are also incorrect, as there are not 52 weeks in a year - that would make a year 364 days, and you do not work 52x5 days per year as there are public holidays and annual leave. Also, you work overtime, so does any involve extra commutes (e.g. working weekends).
-
In column K both overtime pays are not multiplied by their loading.
At first I was like, whaaat, but then I was like OH DUH
But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?
Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.
I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?
Your yearly calculations are also incorrect, as there are not 52 weeks in a year - that would make a year 364 days, and you do not work 52x5 days per year as there are public holidays and annual leave.
So, 365/7 = 52.142857, more accurate?
Not everyone has paid holidays off, or annual leave. Also, not everyone works full time... How could I incorporate these as a variable?
Also, you work overtime, so does any involve extra commutes (e.g. working weekends).
I don't work weekends, so for me it is only 5 days. But it might be a good idea to include a little box that says something like.... "# Days Per Wk Commuting"?
If anybody can point me to a good online class or tutorial that would teach me some of these more complicated bits, that would be just awesome. In the meantime, I suppose I'll just keep messing around with it!
-
Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.
I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?
Feel free to steal the tax calculation in the spreadsheet downloadable from http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228
-
But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?
Instead of "=multiply(H8, H10, 2)" use "=H8*H10*2"
And then forget you ever heard of multiply().
-
The formula you want is product - although with 3 numbers, I'd just do X * Y * Z as solon mentions. Product is cleaner if you've got a range to multiply.
-
Tax rates are progressive - the first $x you pay no tax, the next $y you pay z%... so your tax calculations are incorrect.
I knew this seemed off, but I'm completely baffled as to how to include that kind of tiered calculation in my spreadsheet. Suggestions or tutorial materials?
Feel free to steal the tax calculation in the spreadsheet downloadable from http://forum.mrmoneymustache.com/ask-a-mustachian/how-to-write-a-'case-study'-topic/msg274228/#msg274228
Thank you thank you!! I think I got it transferred over cleanly, sans the non-payroll bits... But I wouldn't mind having my work checked, if anybody is willing.
But I'm not 100% sure on how to get them to multiply correctly, since feeding in a formula of "=multiply(H8, H10, 2)" provides the error "Expected two arguments, got three arguments". It must be possible to multiply all three variables within the program in an efficient way, but how?
Instead of "=multiply(H8, H10, 2)" use "=H8*H10*2"
And then forget you ever heard of multiply().
Oh my god of course. For some reason I had it in my head that I needed to do something along the lines of "=multiply(A1, B1)divide(C1)" but I just couldn't get it to work. I almost thought I couldn't use multiple operations, which seemed nuts in my head.
Because it was. :)
Thanks a bunch for helping guys, I think I have it going now...