Does exhaust makes power?
Yes
Is it true thats exhaust worsen fuel consumption?
Depends how good your ECU adapt to the changes in A/F ratio.
Why does it consume more fuel in some cases??
If you look at the graph, after installing the exhaust the A/F becomes rich thus consumes more fuel
What kind of exhaust do you want?
Any free flow exhaust can make power just find a good inlet size depending on your engine, and choosing an exhaust depends on your preference on size, looks, sound and quality.
If theres a bolt on exhaust for your car than you should go for it because you dont have to cut and weld anything, good quality, already R&D(piping size, bends, flow etc) to get the best performance from your car and most bolt ons from HKS, Tanabe, Blitz etc are JASMA apprved which means the noise level are very low while making performance
And you can install it by yourself, just lift your car and unbolt the old and bolt on the new
For custom exhaust just find the right size for your car and choose the correct piping size and a good quality welder and its recommended to use stainless steel so because smoother air flow, last longer and good looking