No it will sound a little better, and it may add a few horsepower, but you won't feel much difference, if any. Not to say you shouldn't do it, but don't expect to feel pinned back in your seat all of a sudden.
Yes if you put a full 3" exhaust from the CAT back then you will add a few hp, mind you it wont really be anything amazing but you can add a few hp by adding an exhaust.
Note: If your just adding an exhaust tip to make it sound loud, no you wont see any power gains.
If the exhaust allows less air flow restriction then yes, there will be a slight horsepower increase. But to really gain performance you would have to put on a free flow exhaust and a K&N air filter so the car can BREATH easily. The down side is that these modifications could be illegal in the UK and the car might not meet local restrictions regarding emissions controls and noise pollution.