W/B Spreadsheet - Best CG?

[JohnSBA]

I've been working on a W/B spreadsheet for my 172Q that will calculate CG aft limit for the gross weight it calculates from my loading inputs. I'd looked for something online, and after a few minutes of searching decided to do my own. The sheet I have so far needs to be validated, and I need to add the Arm calculations at the bottom of that column. Once I feel comfortable that the sheet works as intended I'll try to figure out a way to share it here in case it's of use to anyone else doing their own and capable of checking/modifying my formulae.

Since the Aft CG limit line on the 172 chart is straight I just used a constant calculated from the top & bottom values of the line. That way you get the Moment limit number for the calculated weight, that you can compare with the calculated CG Moment of your loading. So for other types of a/c you'd need to change the baseline numbers and presumably also the constant, but then you'd have a sheet you can use on the Droid X or any other such device (Android, iPhone, iPad, Blackberry, etc...).

So far, as described in an Android POA thread, I'm using the free Shead Spreet Lite as my app for this on my cool $50 Droid X, but will be looking into other spreadsheet app options. Meanwhile, here's what the spreadsheet looks like on the computer:

I haven't yet locked the calculated cells, or messed with the cell background colors to draw the eye toward input points. I find it really useful being able to quickly run different loading scenarios, to get the remaining useful load number I'm after.

All this is leading up to figuring out the optimal CG for my plane. My current idea is to fly with my wife in the back seat and have her gradually pull a 20# weight (soft camp-style water jug?) forward as I adjust trim and check stable airspeed. Has anyone figured out a better way to do this? Once I know the optimal CG Arm number, I can aim for that with the spreadsheet.

 

[Graueradler]

Optimum CG for minimum drag is aft limit.

 

[Graueradler]

I'm going to try to upload a pdf of the spreadsheet I made for my Cherokee. It is an excel sheet but won't upload in that format. If you want, PM me an email address and I'll send you the actual sheet. The lines that do the work are normally hidden so data isn't inadvertently entered in them. The boxed row is the input row. The graph automatically includes the statement of input conditions.

 

[JohnSBA]

pdf of the spreadsheet I made for my Cherokee

Cool. Thanks for sharing. I've thought about making a chart too, but decided it wasn't worth the effort to me at this point since there are only two variables of concern (gross & CG), and it seems that CG limits are impossible to exceed in my 172Q without loading a rock collection in the aft baggage compartment.

excel sheet but won't upload in that format

Maybe there is (or could be a way to have) a POA files section where such stuff could be shared so we're not all re-inventing the wheel. There have to be hundreds of sheets better than what I did, that would at least have been helpful in setting mine up. Clearly there are liability issues both with macro trojans and w/b calculation errors potentially putting POA at risk, so maybe that's why sheets don't upload here?

lines that do the work are normally hidden so data isn't inadvertently entered

My plan is to just lock those cells, and probably use grey background on calculated cells. If there's enough interest, we could at least use this POA w/b sheet thread with links to info as a place to brainstorm and share.

 

[Trapper John]

...and it seems that CG limits are impossible to exceed in my 172Q without loading a rock collection in the aft baggage compartment.

I think it's a good idea to check the forward limit, not just the aft.

I guess it depends on how fancy you want to get and if it's intended to be a replacement for the W&B stuff in the POH. There are combined weight limits for the two baggage areas, position of the front seats makes a difference, etc.

I like Graueradler's spreadsheet.

 

[BillTIZ]

Optimum CG for minimum drag is aft limit.

Many sailplane racing pilots thought the same thing.
But flying at aft cg limit made the gliders unstable for slow speed thermaling flight, especially if the thermal is bumpy.

80-85 percent aft worked better.

 

[jesse]

I would try and get the CG envelope in there. You're kind of ignoring a pretty important part of weight and balance.

I haven't released WnB Pro for Android yet but I may.

 

[JohnSBA]

I would try and get the CG envelope in there

From my past study of the 172M chart and experimenting with numbers, it just didn't seem to me much of an issue given my caution with gross weight and avoiding aft CG. Not evident in the extreme example I posted though, eh?

If anyone can find a scenario where exceeding forward CG is an issue in a 172, I'd like to see your numbers. Maybe I just stopped trying forward numbers when they exceeded the weight of anyone I've flown with. I could reach aft CG with me at the controls, 500# in the back seat and 1/2 fuel. The CG numbers were similar when I looked at my 172Q chart, except of course the useful load is over 1000# so there are more ways to exceed. I just don't seem to fly with such scenarios.

I'll look for info on the seat position and adjust the Arm accordingly since I always fly at the same setting and put front passengers one or two clicks back so they're far enough forward for good seatbelt protection but they're less likely to interfere with the rudder. I don't recall seeing seat position info in the POH though, so will maybe need to guesstimate it. I'm wondering if it's really that big a deal though, given the extreme numbers I've run, the fact the front seats are near the aircraft center of lift, and the variability in body types and seat back position.

Something I've thought about doing is hooking a pull gauge on the tail tiedown ring and measuring the tipping weight with a couple of different loadings. Since it's an easily calculated Arm point my theory is it could help nail down other variables and maybe serve as a ramp check if I have fuzzy numbers (like if my wife wants to buy an antique, or I doubt her girlfriend's reported weight). First I need to get the Q's POH from the plane and go over that chapter again in detail. Maybe some day I'll cough up the cash to re-weigh the plane, but it just didn't seem worth it since I fly well inside the envelope and flight-related variables the w/b affects have probably changed too. Regardless of the numbers, I've found that when flying with aft CG within about 20% of the envelope I dislike how planes fly. I've done it in an Archer II, Tiger, 172M and 172Q and just decided not to do it any more. That's why my spreadsheet shows the difference between limit and calculated aft CG. Guess I could throw in the percentage within the envelope too, since approximate envelope position is something the chart essentially shows at a glance. Or I could just take my sheet's number and glance at the chart, since all the pencil work to get the number is a part of w/b I haven't enjoyed. Especially for trying different loading scenarios, when it's so easy to just plug different numbers into the sheet.

I appreciate the comments, and welcome more because they help me reconsider the assumptions made from my own exploration. You've also increased my appreciation of why my spreadsheet would at best only provide a starting point for anyone creating their own for preflight use.

 

[Graueradler]

Optimum CG for minimum drag is aft limit.

=BTIZ Many sailplane racing pilots thought the same thing.
But flying at aft cg limit made the gliders unstable for slow speed thermaling flight, especially if the thermal is bumpy.

80-85 percent aft worked better.

I agree. I only addressed drag. If you are still inside the fore and aft limits, stability should still be acceptable. Stability decreases as the CG moves aft.

 

[COFlyBoy]

I plotted the W&B envelope on my spreadsheet. The envelope for a PA-32 is a little bit complex, so it helps to see where I am inside it. I also plot the full fuel W&B and the empty W&B to see how it will progress.

Example:

 

[JohnSBA]

I also plot the full fuel W&B and the empty

Exactly! This is one of the things I was curious about with the 172, since with many planes it's possible to take off inside the envelope and have normal fuel consumption put you outside the CG limits. My impression from the few scenarios I've run so far seems to indicate that's not an issue with the 172 (slope angle of the aft CG line on the Cessna chart seems to match fuel burn), but as we've seen there may be different experiences and perspectives here on POA that can provide helpful info.

Yours would sure be more complex if the tip and main tanks had different Arms, eh? I guess having all four tank numbers at the top is handy for fuel management at a glance in flight, eh?

Something I like about my little sheet so far is having space to itemize the baggage contents so I don't have to add them manually, and it's easy to cut/paste items between the compartments to shift the CG number.

Still need to come up with a strategy to determine the ideal CG Arm number for speed/efficiency and stability, and maybe that's going to be a long-term experiment to find a personal preference given the discussion so far.

 

[Trapper John]

Still need to come up with a strategy to determine the ideal CG Arm number for speed/efficiency and stability, and maybe that's going to be a long-term experiment to find a personal preference given the discussion so far.

It's an interesting thought exercise. Let's say you come up with a solution. As a practical matter, what do you do with it? As fuel burns off, the CG is going to move a little. Do you have your passenger scoot a suitcase to make up for it?

I have a feeling that trying to measure potential speed gain might get lost in the error of the ASI, altimeter and thermometer.

 

[Jaybird180]

Something I've thought about doing is hooking a pull gauge on the tail tiedown ring and measuring the tipping weight with a couple of different loadings. Since it's an easily calculated Arm point my theory is it could help nail down other variables and maybe serve as a ramp check if I have fuzzy numbers (like if my wife wants to buy an antique, or I doubt her girlfriend's reported weight).

Can you elaborate on this a bit more? I always thought about how to approach the subject, sometimes people can be offended when asking their weight, regardless of the explanation as to why you need an accurate number...and I don't think keeping a scale handy would go over well either.

 

[JohnSBA]

Can you elaborate on this a bit more? ...people can be offended when asking their weight

My mental image looks something like this: I load the plane for a typical mission (petite wife plus 1/2 fuel and assorted sky junk in the baggage areas). I use the pull gauge (fish scale, or whatever I can find with the range expected) to see how much force it takes to lift the nose wheel just off the ground with various amounts of junk in back. In the future after loading all of the above plus the grande-sized item or passenger(s) I do the same. My theory is that with some advance calculations and experiments it should be possible for my new & improved spreadsheet to give me an approximate Arm and extrapolated gross weight once I'm aboard (and maybe I could test that with the help of a friend on the gauge when I'm strapped in). Since we seldom fly without my wife and me in front and I'm averse to flying near gross weight, it might be more useful for me than other pilots because weight variations in the back seat and baggage areas affect the effective tail weight (CG) much more than in front seats. Clearly none of this is needed, since it can all be calculated more accurately by the sheet I have. Just thought it could be a cross-check, and useful when I don't have a scale on the ramp or prefer not to use it. For small taildraggers maybe you could do this with a bathroom scale for the tailwheel load to get the CG more accurately and also (with Arm numbers) deduce the approximate gross weight.

Possible drawback for me: An affordable and small/light (i.e. always aboard) pull gauge accuracy may be insufficient to give a usefully accurate Arm number. Others here may also spare me the exercise by noting some other problem I'm missing. Of course, there's also a fair chance I'll never get around to trying it.

 

[jesse]

My mental image looks something like this: I load the plane for a typical mission (petite wife plus 1/2 fuel and assorted sky junk in the baggage areas). I use the pull gauge (fish scale, or whatever I can find with the range expected) to see how much force it takes to lift the nose wheel just off the ground with various amounts of junk in back. In the future after loading all of the above plus the grande-sized item or passenger(s) I do the same. My theory is that with some advance calculations and experiments it should be possible for my new & improved spreadsheet to give me an approximate Arm and extrapolated gross weight once I'm aboard (and maybe I could test that with the help of a friend on the gauge when I'm strapped in). Since we seldom fly without my wife and me in front and I'm averse to flying near gross weight, it might be more useful for me than other pilots because weight variations in the back seat and baggage areas affect the effective tail weight (CG) much more than in front seats. Clearly none of this is needed, since it can all be calculated more accurately by the sheet I have. Just thought it could be a cross-check, and useful when I don't have a scale on the ramp or prefer not to use it. For small taildraggers maybe you could do this with a bathroom scale for the tailwheel load to get the CG more accurately and also (with Arm numbers) deduce the approximate gross weight.

Possible drawback for me: An affordable and small/light (i.e. always aboard) pull gauge accuracy may be insufficient to give a usefully accurate Arm number. Others here may also spare me the exercise by noting some other problem I'm missing. Of course, there's also a fair chance I'll never get around to trying it.

I don't think putting that much force on the tie down ring with the airplane loaded would be a good idea.

Ask them their weight. If it doesn't seem right put them on the scale. I've seen Dr Bruce bust out a scale before to weight passengers before boarding his Seneca.

 

[JohnSBA]

I don't think putting that much force on the tie down ring with the airplane loaded would be a good idea.

Thanks for sharing your concern. Maybe it's just my 172 fixation coming out again, because that's the only a/c type I've actually thought about doing this with. The first time I saw an IA put sandbags on the tail of an empty 172 to work on the nose wheel, I was aghast. Then I noticed that cinching the tail tiedown tight (perhaps 10# pull) resulted in significantly unloading the nose wheel. I've since found that a 172 gets light on the nosewheel when rear passengers and/or heavy baggage are loaded. My estimate is that in such a scenario, the pull required to lift the nosewheel would be less than 20#. Since the only time I'd personally bother to use a pull gauge would be with relatively aft CG and high gross weight (typically concurrent on the 172), my guess is that the quite substantial tiedown ring and associated mounting would not be significantly stressed. There are probably other a/c types with less substantial tiedown hardware and/or different CG (e.g. tipping force required in my loading scenario) so I'm glad you mentioned your reservations for the benefit of those pilots, Jesse.

 

[Jaybird180]

I don't think putting that much force on the tie down ring with the airplane loaded would be a good idea.

Isnt it designed to keep the airplane in place during high winds?

 

[JohnSBA]

Re: W/B Spreadsheet - 172Q POH excerpts

I finally brought the POH home today (after a short but glorious local flight, with pix soon to be on our blog and Photo Page). Since it might be of use to some "SuperHawk" pilots, here's some stuff from the W/B section.

So the Datum point is the front-lower firewall face, and (as I noted on this edited down version of my photo of the POH) the Unusable Fuel Arm is 46. Next is a chart with the Arms for various loading points.

Again, these are photos I shot of the POH so they're a bit tweaked due to my camera offset. I've also edited them down to the most relevant parts to save file space here on POA. Next is another overhead diagram with Arm points. I might pencil in an Arm for the front of the forward Baggage Area 1 (up against rear seat base), since that's often where I stow heavy baggage.

Last is a crop of the CG Arm Limits Chart. It's going to be interesting to compare all this with the old copy of a 172M chart I have. The max. ramp weight for the 172Q is 2558# if anyone's interested.

Now I just need to plug some numbers into my draft spreadsheet and then test a few scenarios to see if it works as planned. If there are any SuperHawk pilots reading this, please post a comment on how these charts compare with your measurements and experience.

 

[denverpilot]

John, give a phone call up the road only a few miles from you in Santa Maria to the folks at the Cessna Pilot's Assn and see what they think about lifting the nose with the tail tie-down ring for maintenance. Your mechanic needs to invest in some jack pads, stands, and a jack, before he cracks a bulkhead inside your tailcone. Sure some Cessnas can have glider tow gear hung under there and survive it, but for how many cycles? The bulkheads aren't getting any younger. Metal fatigue of the tail of an expensive aircraft is silly. Abuse a $15 bathroom scale instead.

 

[JohnSBA]

Metal fatigue of the tail

Thanks for adding your concerns, because I'm still pondering this as you guessed. Carrying a bulky scale that adds a few pounds of aft CG just doesn't appeal to me if there's an alternative. I'm not clear on the difference between steady downward force at the tiedown ring with the aircraft at rest and loaded with an aft-ward CG, and presumably stronger forces in flight. With the aircraft loaded aft, the CG is only a few inches forward of the mains. Wouldn't pulling +/- 0.5G keeping level in turbulence, a steep turn or other Normal Category maneuver put more load (and much sharper variations) on the tail? At best maybe it looks undignified to static load the tail (sure bugged me the first time I saw it!), but are you sure it can really harm the airframe? I'm not wanting to start a flame war here, and it does seem some people may have strong feelings, but I'm really wanting to understand the factors.

 

[denverpilot]

Metal has a "memory" and can only be stressed so many times. While our aircraft can be stressed many thousands of times in flight, why introduce additional stress cycles that aren't necessary?

You don't see the real Pro's attaching weights to the tails to do maintenance usually, and never have I seen a pilot head out to the ramp with a tail rope and a strain gauge to do W&B. In a 172 it's really unlikely you're ever loading anything in that you couldn't weigh, and it doesn't take too much effort to get a feel for how much things weigh by picking them up. Maybe people excluded, but if you get a balky passenger who won't let you know their weight, you can usually "eyeball" that and add a fudge factor. (Hint: They're usually really obese and self-conscious about it.)

Also note: There is a forward seat limit for weight in most Cessna POH's and it's based not on CG, but on what the seat is physically rated to hold and protect in a crash. Putting a morbidly obese person in a seat rated for 400 lbs is signing their death sentence in an off-field hard landing or crash. You're PIC, you have to say no. It may also be rated that way so the seat track stays screwed to the floor in moderate turbulence for both your sakes. (400 isn't the real number, check your specific POH.)

For really close to gross weight flights, I require pax be honest and I carry a "luggage scale" where it looks like a handle with a strap under it. You clip the strap onto the bag by it's handle and lift. The scale reads the weight digitally. I've checked it against a "Doctor's" style scale so I trust it. It weighs 1 lb and can measure up to 75 lb. The only thing it won't easily weigh is something without handles, but I can wrap a bulky item with the cargo net (you ARE going to secure that box, right?) and hang it that way.

What are you loading that you'd want to do this other procedure? Did I miss that? Even animal carriers can be weighed by their handles with one of these luggage scales.

With that said, you might be over thinking this a bit for a Skyhawk.

If you really want a shock, have your mechanic actually weigh your aircraft. 99% of work done is by "book" numbers and the FAA official calculation formula when optional gear is added/removed by shops. Many aircraft when actually put on a scale are significantly off from their "official" W&B paperwork.

Unless you're going right up to max gross every flight, you probably don't need to be as "fancy" as you're thinking.

It's pretty hard to get a Skyhawk way out of CG limits loading things that'll fit right in their "normal" places. A Bonanza, that's a different story.

And remember, if you get a Cessna so tail heavy that the nose gear strut stays extended at touch-down, there's a nose-gear steering centering cam and locks that allow the rudder trim to have a force to work against.

I've seen at least one person mini-panic when they landed nice and nose-high, held the yoke all the way back (as they should have) and had to tap a brake to stay centered up because the nose-wheel was locked straight ahead.

 

[JohnSBA]

Metal has a "memory" and can only be stressed so many times.

My sense is that you're not expert in either the metallurgy of fatigue limits in aircraft alloys or the aeronautical engineering details and associated stress levels of the 172 tail structures, so maybe this aspect of our conversation can go no further.

You don't see the real Pro's attaching weights to the tails

This is a great point, and I tend to agree. Just bad form. Seems like me hanging a pull gauge on the tail ring would look goofy to everyone nearby, especially my perplexed passengers.

There is a forward seat limit for weight in most Cessna POH's

If anyone could point me to that section, I'd appreciate it. I confess to not having read an entire POH since upgrading from 172M to 172Q, and to having forgotten much of what I did read. I'm appreciating this dialog as a nudge to read it again. That said, the max seat weight is something I'd have skimmed before because the situation has never arisen. In the coming months though, I may consider taking someone who's quite hefty and unlikely to submit to the scale. If this person whose word I trust is also unwilling to confirm being below my max calculated weight, no go. So my new spreadsheet will be a handy timesaver as will the seat limit number. As you infer, and has been easier to confirm with the spreadsheet, the 172Q is quite tolerant of sensible loading both for gross weight and CG.

there's a nose-gear steering centering cam and locks

Good for pilots new to the 172 to be aware of.

Well, I'm going to make another post now on a discovery about the "spreadsheet" aspect of this thread. Thanks for sharing your concerns.

 

[JohnSBA]

Re: W/B Spreadsheet - Free Smartphone Apps!

All I can say is, "Duh!" Last night I was bumping up against the limits of the Android app "Shead Spreet" in trying to get the xml spreadsheet I'm building in Open Office to format properly on my Droid X. I was about to rummage the Market again, when it hit me like a bolt: Google Docs!

So I opened gDox on my laptop (comes with any gSignup, such as gMail), uploaded the sheet, and signed my Droid X copy of Firefox into my gDox account. There with every aspect of my original's formatting intact, was the sheet. Any time I changed the laptop version, it instantly showed up on the phone with a refresh.

On the downside, I find the Mobile gDox interface more annoying than the full version on my laptop and it won't recalc offline near as I can tell. But if I can get used to the quirks it will probably be fine on the ramp, since I've yet to land at an airport without either Verizon phone signal or some sort of net connection. At least it can suffice until I find a good free Android spreadsheet.

Does anyone here use gDox on their phone or tablet? Any tips or pointers you'd be willing to share?

 

[denverpilot]

My sense is that you're not expert in either the metallurgy of fatigue limits in aircraft alloys or the aeronautical engineering details and associated stress levels of the 172 tail structures, so maybe this aspect of our conversation can go no further.

No, I'm not an expert. I went TO the experts on what's been seen in the real world as really REALLY expensive repairs on Cessnas as they age. Those nice folk 95 miles up the road from you. They spent a significant part of the weekend showing us photos of the cracks in the station 209" aft of the firewall and the next one aft in the tail of the 182. The 172 is virtually identical. Frankly with their experience fixing the problems, I'd say they know more than Wikipedia on the subject, any time it applies to Cessnas.

Smacking the tail tie-down ring on the runway is also a common cause of those cracks in the bulkheads, which is commonly done by students flying approaches too fast and flaring too fast. It's a relatively fragile area, and my concerns are not necessarily for you, but for the owner. If I saw you winching the nose of *my* aircraft up by the tail tie-down ring, you might find that strain gauge temporarily inserted in an orifice it wouldn't be too comfortable in.

Treat rentals as if you had to pay the repair bills, is all I'm saying. They're not out there on the ramp for "experiments" and if you think they are, see if an insurance lawyer agrees after you pay the deductible and file a claim. No one does what you're suggesting because it's absolutely unnecessary. The book W&B calculations take 5 minutes and they work for everyone else on the airport. Seriously. M

You can argue all the metallurgical science you like. Those nice folk right there within an easy drive of your home can show you photos that prove what's really happening in the field. I've hinted, if you won't take the hint I'll be more bold and just say "Call them".

If anyone could point me to that section, I'd appreciate it. I confess to not having read an entire POH since upgrading from 172M to 172Q, and to having forgotten much of what I did read. I'm appreciating this dialog as a nudge to read it again. That said, the max seat weight is something I'd have skimmed before because the situation has never arisen.

You're scaring me. If you have time to tinker with spreadsheets you have time to read your POH for your aircraft model and know it cold.

The forward high number in older 172 POHs is a max limit per seat also, indirectly. In other words, if you go off the chart for that forward station with your weight and the pax weight combined, you're outside of the certified limits.

But since you're convinced your time is better spent tinkering with a spreadsheet instead of practicing with the charts published by the manufacturer... I'm not sure this conversation will be very productive. I'm offering advice in an open forum that as you go fly more types that have less forgiving W&B behaviors, knowing that the POH is your bible and everything else must be tested to the POH with a bit of extra work up-front, will mean you're here, alive, chatting with us, and not dead.

Augmenting the POH after using it is reasonable. Trying to go around it because it's more convenient to avoid the charts and write spreadsheets, is a bit anti-authoritarian which is a commonly discussed post-accident mental attitude in fatal accidents.

The CFIs at the club I flew out of were required to have every student file a TOLD sheet prior to all flights. Takeoff and Landing Data. It included W&B. If you whipped out your handy dandy iPhone or Android or other gadget, they'd say "Fine, prove to me that it's accurate. Do one long-hand with the book and I want to see the results against your gadget. We'll do a couple and then you can use the gadget." The point was that batteries fail, and you might be sitting somewhere without a gadget and only the POH. Do you fly home without doing your W&B? Heck no. You do it the normally accepted way.

The point was to build up habits that you always do, pre-flight. Proven shortcuts were allowed after you proved you could do it the normal way.