As Jeff said, personal preferance.
What type of driving are you intending on doing? If you're planning on taking it on a track then don't bother with an auto, but if you spend a lot of time on the road the S4 will have enough power to make good use of a tiptronic box. Never drive a CVT A4 myself, but had a Auris last month with a single speed box and it was wierd...