I think in most cases a good cat-back is all that is needed unless you mod out the engine. There are several conversations going on about headers on the car forums and although they will add HP, a real good set that does a great job of scavanging the exhaust can cost upwards of a $1,000.00. If you have that kind of cash, I'd do it!
Some can be had for $500 as well. The TRI-Ys I am looking at for my 350 with high flow cats cost $1240.00.
The 2.5" cat-back system I am looking at (ATR Quads) are about $800.00. So you are looking at about $2k if I actually did it.
Both would net me a total of about 25-30hp or at least that is what the dyno said on a few people that had the combo.
The real trick is finding a good combo that doesn't sound raspy or pop. I have heard the combo I want and it has the low rumble, but it's not obnoxious.
On a Motorhome, I am not sure on what kits are out there, but if you have a standard engine (350 or 454) it can't be that hard to find something, but listen to it first if you can find someone that has the setup you want. Many people I have seen have run out and got something, installed it and sometimes didn't like the sound.