Yes, it is 100% worth the money IF you get custom tunes instead of using the "canned" tunes provided.
Yes, it will create more horsepower and torque.
Yes, it will create black smoke IF you desire. However, the goal of most programmers these days is to produce the LEAST amount of black smoke as possible. Reason being, black smoke is unburned fuel. Unburned fuel = wasted power. They want to burn all of the fuel in the cylinder to generate as much power as they can while maintaining fuel efficiency.
EDIT: Damn, Josh beat me to it.