Weight Balance Estimator Spreadsheet

FishDoctor

Hello Fellow T@bbers!

I created a spreadsheet calculator to estimate trailer weight and tongue weight based on the distribution of weight from added or removed items. This is my first draft, but it seems to be working pretty well for me. It is currently in Microsoft Excel, but I might try to port that over to a web-based application in the future. Right now, this is calibrated for my own 2017 T@B S Max. While it will does allow some changes to overall starting trailer weight and tongue weight if the user desires to do that, it is not set up to calculate for other trailers right now (additional input parameters would need to be adjusted to do that). I just thought I would make it available in case anyone wants to play around with it and give me some feedback! 

If you would like to access download the file and give it a spin, you can use this link to get it from Google Drive: https://drive.google.com/open?id=1uZjRkUcO_48NlXr7YQW4LM1puyt8FgvI . Most of the spreadsheet is locked and protected right now, mainly because it would be too easy for someone to make their version unworkable if they inadvertently changed things around!

Be sure to read the notes and instructions. Let me know if you have questions!

John (FishDoctor)

jkjenn

Nice work!

klenger

I'd be happy to host that on my website with full credit to the author.  Link in my signature.

BrianZ

Very interesting idea!
Unfortunately, it doesn't appear to be fully functional with the Excel on my Android phone.  Some cells display this message..
"This version of Excel doesn't support running or interacting with Form controls."

Also, can this be used from any starting point with regard to how the trailer may already be loaded, as long as the tongue & trailer weights were obtained at that point?  Or, must one account for the +/- loading of each segment by using an empty trailer weight & adjusting for any items previously added/removed? 
I would assume it's the former, and that the idea is to simply estimate the effects of actions such as "what if I removed an object weighing X from location A and/or added it to location B, how would that effect my tongue weight & percentage?".

FishDoctor

BrianZ said:

Very interesting idea!
Unfortunately, it doesn't appear to be fully functional with the Excel on my Android phone.  Some cells display this message..
"This version of Excel doesn't support running or interacting with Form controls."

Also, can this be used from any starting point with regard to how the trailer may already be loaded, as long as the tongue & trailer weights were obtained at that point?  Or, must one account for the +/- loading of each segment by using an empty trailer weight & adjusting for any items previously added/removed? 
I would assume it's the former, and that the idea is to simply estimate the effects of actions such as "what if I removed an object weighing X from location A and/or added it to location B, how would that effect my tongue weight & percentage?".

Yeah, Excel on Android will not run the two simple macros that I put into the spreadsheet -- one that recalibrates the calculation for a new starting tongue weight by adjusting the assumed percentage the tongue contributes to the overall weight and one that resets the spreadsheet with the default values. The calculator will still work without those, but if your starting tongue weight is different than the default, the results will be off. Changing the starting tongue weight without adjusting the percentage the tongue contributes to the overall weight affects the calculations because it affects where the center of gravity is located on the trailer (it is NOT necessarily at the position of the wheels). I couldn't figure out how a user not experienced with Excel could make that adjustment without the macro.

You are correct that the idea behind this is to enter the starting conditions (total weight and tongue weight) for your trailer however it may be loaded and then use the yellow entry cells to see how adding, removing or moving weight around in the trailer will affect total trailer weight and, especially, tongue weight/tongue weight percentage. That was actually my main interest in doing this for myself.

FishDoctor

@BrianZ -- If you tell me what your base weight is and waht your tongue weight is, I can do the needed calculation and tell you what value to change without needing to use the macro button. It's actually not that hard to make the change on the version I provided for download.

Dalehelman

Once again you guys are making me afraid to leave my driveway. 

Cbusguy

@Dalehelman problem is solved by simply using a sensible tow vehicle.   

Sharon_is_SAM

We do our own simple version of this.  Add up the weights of the cargo added to the TaB and TV, make sure we are well under the weight ratings of both vehicles individually and combined (GVWR and GCWR), measure the tongue weight with the current load (add that to the TV weight).  Go camp😁. Because the CSS has a lighter tongue weight (kitchen in rear), we actually are always on the low end of tongue weight as a percent of total TaB weight.  We carry as much up front as possible to add weight.   Funny because we were worried about the impact of our 96# battery on tongue weight.  Turns out that is not an issue at all!

Luckyj

Dalehelman said:

Once again you guys are making me afraid to leave my driveway. 

I totaly agree, so let's have our TD delivered to en enclose home garage, and make that a nice cozy camping spot forever.  We could even remove the wheels and sell then, since they will never be used anymore.

my tought are that if loading is done with weight balance in mind, and using a scale every once in a while to put us back into it (you need to weight your loggage anyway to get a precise speadsheet) you will get a good feel for it.

the spead sheet is nice, probably inspired by load master for air plane and such,  but to me, it pull part of the fun from the vacation.

again, it is very nicely done, but is it necessary, for each of you to decide.  But this is just me.

Cbusguy

@Luckyj  T@B ownership has all aspects of enjoyment,      For some it is the tiny aspect,  others minimalist, others it is glamping,  yet others it is engineering and modification.    Yes some do travel and enjoy that experience too.

Many over think problems or see problems where there is none.    You are right it adds an unnecessary element of stress.  

FishDoctor

Have your fun and I'll have mine.

I know just fine how to safely load my T@B and tow vehicle for my travels and I have learned how moving things around affects the towing performance. I have always done that just fine without getting my calculator out. I don't really worry about it or obsess about it and I certainly have enjoyed my many miles of travels and many nights of camping. I didn't tell anyone what they need to do or that they were doing things wrong.

I developed the spreadsheet because I got curious about the physics and the math, I like getting a little geeky with Excel and computer stuff, and I enjoyed the science aspect of testing and calibrating the calculations. I had fun putting the calculator together and thought I would share in case anyone else might enjoy playing around with it. If you're not interested or find it silly, climb back in your tow vehicle and camp on instead of getting on a high horse.

FishDoctor

Sharon_is_SAM said:

... "Because the CSS has a lighter tongue weight (kitchen in rear), we actually are always on the low end of tongue weight as a percent of total TaB weight.  We carry as much up front as possible to add weight.   Funny because we were worried about the impact of our 96# battery on tongue weight.  Turns out that is not an issue at all!"

For my T@B, the calculations suggest that adding a 96-lb battery in place of an existing (guessing) 60-pound battery in the front tub would result in about a 24-lb increase in tongue weight.

DeltaPop

This type of calculation is very common in the aviation world and is completed on every airliner prior to flight.

FishDoctor

DeltaPop said:

This type of calculation is very common in the aviation world and is completed on every airliner prior to flight.

And thankfully so! Would I be correct to assume that there are model-specific calculation routines that are applied since the calculations need inputs regarding wheel base, number of wheels, total weight, etc?

Sharon_is_SAM

@FishDoctor, do you know if the CSS frame and wheel position are the same as the S model?  Just took our TaB to storage so I can’t measure.

FishDoctor

Sharon_is_SAM said:

@FishDoctor, do you know if the CSS frame and wheel position are the same as the S model?  Just took our TaB to storage so I can’t measure.

Not certain, but I suspect they are pretty close to the same. On the NuCamp website, the 2020 320S is listed as being an inch longer and 43 pounds lighter (dry weight) than the 320 CS-S. However, I couldn't find anything about the wheel locations for either. I think the difference in length would have minimal effect on the calculations. I'm a little surprised to see that the wet tongue weight of the 2020 CS-S is listed as 36 pounds greater than the tongue weight of the 2020 S model.

If I assume that the length and wheel location is the same as for my 2017 320S and plug wet total weight and wet tongue weight from the 2020 320CS-S into the calculator, it estimates an initial tongue weight of around 175 pounds. Adding an extra 36 pounds of battery weight at the tub location would increase tongue weight by about 24 pounds (new tongue weight of 199 pounds).

Sharon_is_SAM

Glad you caught that.  I thought the CSS tongue weight was less than the S.  

BrianZ

FishDoctor said:

@BrianZ -- If you tell me what your base weight is and waht your tongue weight is, I can do the needed calculation and tell you what value to change without needing to use the macro button. It's actually not that hard to make the change on the version I provided for download.

Thanks, @FishDoctor!  Here's our info..
Trailer weight (includes tongue):  2263 lbs
Tongue weight:  223 lbs

PS:  Agree this could be a fun academic exercise, and now that you've done the hard part by coming up with an automated model, it has the practical value of being able to easily check the safety of a new loading plan without physically needing to get a new tongue weight or paying to get a new trailer weight (time & money saved).  Although, I do get a kick out of seeing our glass bathroom scale holding up the front end of the trailer.

FishDoctor

BrianZ said:

FishDoctor said:

@BrianZ -- If you tell me what your base weight is and waht your tongue weight is, I can do the needed calculation and tell you what value to change without needing to use the macro button. It's actually not that hard to make the change on the version I provided for download.

Thanks, @FishDoctor!  Here's our info..
Trailer weight (includes tongue):  2263 lbs
Tongue weight:  223 lbs

To make the necessary adjustment for your base setup, enter your trailer weight in cell B4 (the red trailer weight value) and change the value in cell B18 (blue colored font) to 6.15. It should read 6.2% because of rounding when you get done. The value for the "Weight on hitch" in the green results box should now display 223 lbs.

If you save the file after you make the changes, you will be able to modify the loadings fields (yellow cells) without needing to re-enter the setup values. Let me know if you think it is calculating properly as you experiment!

BrianZ

Thanks, @FishDoctor, that works.
I tried my first "what if" scenario to see what happens..
If I filled up both the gray(19G) & fresh water(11G) tanks, which I believe are located over the axle, that would add 30 gallons @ about 8 lbs each or about 240 lbs over the axle..

So, the results box shows no change in the tongue(hitch) weight, but since the total trailer weight has increased, the percentage of tongue weight has dropped from 9.9% previously (in the middle of Toyota's recommended range of 9-11%) to 8.9%, or below the safe range. 

I guess we'd be ok with just the fresh water tank full - Yep, a few taps on the spreadsheet on my phone shows 88 lbs of fresh water puts us back up to 9.5%, inside the manufacturer's safe range to avoid swaying.  So, this really is a handy tool, & no weighing or hefting in this case to know what some of our limits might be.  Thanks again!

PS:  So I'm guessing maybe it's better to carry a [mostly] full fresh tank than, say, a half tank, which might be located either behind or in front of the axle, depending on whether you're going uphill or down, and accelerating or braking.  A sudden shift in that load might contribute to trailer sway or other issues if you're already near the percentage tongue weight limit.

Also, I'm wondering why the spreadsheet didn't show a change in tongue weight, if the added load to compartment #4 is centered about 10 inches behind the center of gravity.  Maybe just not enough distance to make a difference?

ScottG

FishDoctor said:

Have your fun and I'll have mine.
...

Roger that, FishDoctor.

Nothing wrong with being a thinker. If thinking is not your thing--or if it stresses you out--there's also nothing wrong with skipping on to the next discussion.  ;-)

FishDoctor

BrianZ said:

I tried my first "what if" scenario to see what happens..
If I filled up both the gray(19G) & fresh water(11G) tanks, which I believe are located over the axle, that would add 30 gallons @ about 8 lbs each or about 240 lbs over the axle...

So, the results box shows no change in the tongue(hitch) weight, but since the total trailer weight has increased, the percentage of tongue weight has dropped from 9.9% previously (in the middle of Toyota's recommended range of 9-11%) to 8.9%, or below the safe range.  ...

PS:  So I'm guessing maybe it's better to carry a [mostly] full fresh tank than, say, a half tank, which might be located either behind or in front of the axle, depending on whether you're going uphill or down, and accelerating or braking.  A sudden shift in that load might contribute to trailer sway or other issues if you're already near the percentage tongue weight limit.

Also, I'm wondering why the spreadsheet didn't show a change in tongue weight, if the added load to compartment #4 is centered about 10 inches behind the center of gravity.  Maybe just not enough distance to make a difference?

Not sure about the sloshing of the water or what effect that might have. As far as not altering the tongue weight, I think that's because the load placement is essentially directly over the wheel, which is the fulcrum in the calculations. If you think of this as being like a see-saw with one half of the board over the fulcrum as being slightly longer than the other, the center of gravity is adjusted toward the longer side. That tells you that placing a particular weight on the longer side has a greater effect on downward force than placing the same amount of weight on the shorter end. However, if you place a weight directly over the fulcrum holding up the see-saw, you have no real effect on the downward force on either end of the board. Weight in zone 4 is considered to be directly over the center of the wheel.

I am not sure if the water/waste tanks are centered over the wheel or if they are offset front to back. I have noticed that adding water to the fresh tank seems to make the tongue weight inrease, so I expect that the center of mass for that tank is actually a little forward of the wheel center. I should test that assumption in the future! Note that if the modified weight falls near a dividing line between 2 zones, it might make sense to divide that weight between the 2 zones and see how that affects the calculation.

BrianZ

Regarding my "PS" comments in my last message above, our cousin who is currently on a solo cross-country trip recently just happened to send me this related message..

"Learned from a plumber camping nearby that we should only carry up to three gallons of water in any of the tanks at a time or else have them full. Partly full can lead to heavy sloshing while driving and increase the risk for causing the trailer to fish tail. Thoughts?"

I don't know yet, but guessing she didn't like my answer, which was something like "It might depend on your situation." and "Do you know your tongue weight & trailer weight?".  She was probably wanting a more simple agree/disagree type response, so I did say it sounded like a good idea, but wanted to do some spreadsheet simulation first..

I already knew from my last simulation above that travelling will both gray & fresh tanks full would not work for us, because our tongue weight percentage would drop below the safe level.  I suspected that a half-full scenario with "sloshing" effects might be a problem, so here is that result..

With both gray & fresh tanks half full, there would be 120 lbs roughly balanced over the axle (19G+11G=30G÷2=15Gx8lbs/G=120lbs)
If we assume we were accelerating up a hill and all contents sloshed backwards behind the axle, then the above simulation shows the front 60lbs moving behind the axle.  This also reduced the tongue weight percentage below the 9% safe level (9-11% according to Toyota).
Repeating this scenario with only the fresh water tank half full, however, resulted in a relatively more safe weight distribution percentage of 9.5%. (Not withstanding any possible adverse effects of the dynamics of the actual shifting of the load while in motion, & more importantly the forces created by the 60 lbs of shifting water being stopped by the back wall of the tank).

Further, repeating with only a full fresh water tank resulted in safe 9.7% tongue weight percentage.  Personally, I believe one should not rely totally on this static approach if you are dealing with a dynamic situation of shifting loads, and water tanks can be either static or shifting, so it's wise to be aware of the difference. I would also tend to agree with our cousin's plumber's advice to have the fresh tank either full (on a T@B 320) or else closer to empty while traveling (but not both tanks full or partially full).