Speed and Power Spreadsheet
******Changes as of Feb, 2010
Added a calculation for Normalized Power. This way you can vary your power over the course (Power Factor), and check to make sure that the NP adds up to what you would be capable of.
You can map out a course in great detail if you want. I only included 20 segments by default, but you can add more if you wish just by copying more columns... and be sure to change the summations in column A if you do that. But I never had an interest in using more than 20 segments... even if the course had a lot hills and turns, I'd just estimate how much of the the total course was at a particular grade and wind angle and call it good. As long as you don't do funny things like have more uphill than downhill, or more tailwind than headwind on a closed loop, you'll probably be close.
If you aren't sure what values to put in, I'll make a few suggestions:
Crr is the tire rolling resistance coefficient. It includes frictional dissipations in the tire as well as a fudge factor depending on the road roughness. A value of .0055 is a decent ballpark for good tires on an average road, but a smooth track will be better and chip seal will be worse.
CdA is the drag coefficient times the frontal area. This is a lumped value for the entire bike and rider. Obviously this is going to depend on your size, as well as your position, and the equipment you are using. If you are riding a standard bike and equipment but are low in the drops, a ballpark value would be CdA/kg^.67= .018. Or in other words if you weigh 75 kg (165 lb), CdA= .018*75^.67= .325. If you are sitting upright (like on a climb), CdA/kg^.67~ .024. If you have all the greatest TT equipment and good positioning, a CdA/kg^.67 value of .013 is possible.
The power vs altitude compensation factor is based on a study of elite runners. It assumes that the athlete has *not* had time to acclimate... but actually they found little difference between acclimated and non-acclimated performance. The equation used here is only good up to ~10,000 ft, so keep that in mind if you are trying figure out how long it will take you to ride to the top of Mt Everest.
Be conservative with the wind values... they aren't as great at bike level as they are at 10m up where the airport measures it. The ground effect slows it down, plus things like buildings, trees, and bushes will decrease it as well. A wind value of about half of the weather service value is a good estimate, but it can be more like 70% if it is very open ground.
Wind angle factor is based on "bluff body" aerodynamics and should be fairly accurate... except for parts that have an airfoil effect like aero frame tubes and deep carbon wheels and disks. In this case the real cross wind drag penalty will be lower than what I calculate here.
I hope you have fun with this. It took me a lot of time to put together, but I enjoyed doing it. If you have any questions or comments, send me an email.