The Power-Equipment-Speed Relationships

I spend and awful lot of time and energy measuring my power on the bike. But power doesn't win races, speed does. For the foreseeable future, power will be the best way to track changes in fitness; and it will be the best method of structuring a training program.

But in order to step back and see the big picture or to run through various equipment scenarios, I think it's important to know:

1. how much faster will I be on a flat TT if I increase my FTP by 20 watts, or


2. how much time will I cut off a climb if I lose 5 pounds, or


3. how much time will I cut off a climb if I buy a bike that's 2 pounds lighter, or


4. how much energy (power) will I save sitting in a 25-mph paceline if I swap my Ksyriums for Zipp 404s, or


5. if I do a solo break away with one lap to go on the rolling Augusta road race course would I go faster with my lighter box section rims or heavier but more aero deep section rims, or


6. what will the winning gap be if I sprint with 800 watts over 150 meters against a 180-pound guy putting out 970 watts? And more importantly, who will win? And if it's not me, how many more watts do I need to win?

There are many variables involved in answering those questions, but almost all of them are measurable to a fairly high degree if you have the time, equipment, patience, and desire. I've already answered many of them for myself, but I've used different spreadsheets or methods or web sites for almost every question and situation. And those spreadsheets, methods, or web sites often make assumptions that I don't agree with or simplifications that don't apply to what I'm doing. So it's hard to be consistent across the board with answering the questions and be confident in the answers.

So I decided to create a spreadsheet that ties all the physics together to the best of my abilities. I'm well aware that I'm probably the 10,000th person to attempt this task and that it's been done by folks with a lot more knowledge that I have. My spreadsheet might be better than some and will not be as good as others you might have seen. I hope it is accurate from a physics and math standpoint, but I'm not 100% sure. If you find errors, please let me know so I can make corrections and minimize the embarrassment factor.

But if nothing else, going through the tedious process has helped me understand the physics behind the above questions better than I did before, and that's reason enough to do it.

I've named the spreadsheet Badger because all the other names I thought of were about a mile long and sounded like the title of someone's masters thesis. This is bike racing, not study hall. It's a work in progress, but if you assume a rolling resistance coefficient, you can calculate almost anything else (use Crr=0.005 if you aren't sure. There are field tests to measure Crr, and I'll work on those later).

My spreadsheet is available here: Badger

In a nut shell, this is how it works:

• The first sheet, "CdA calc", uses your input data (weight, weather data, distance, time, slope, and assumed rolling resistance) to calculate the forces against the rider (rolling resistance, air resistance, and slope resistance). Start and finish speeds should be entered so momentum can be accounted for, but results are best when start and finish speeds are equal. All the forces against the rider are set equal to the wattage you put out (to satisfy conservation of energy), and the equation is set up to solve for your drag coefficient CdA. For coast down tests, use wattage equal 0.
• On the second sheet, input your weight, weather data, and the CdA that you calculated from the first sheet (you have to manipulate Cd and A to get the right CdA). Use 0.40 for A if you aren't sure - as long as CdA is correct it really doesn't matter). Assume a rolling resistance coefficient (Crr). Then you can set up each segment of any course to see how changing variables such as wattage, weight, rolling resistance, and CdA will change your finish time for the entire course. If the totals on the left side of the spreadsheet show errors, just delete any cells on the right side of the spreadsheet that aren't being used and that should solve the problem.