There’s a number of reasons I like manual exposure mode, and a number of places where it’s either necessary to shoot manual exposure or apply exposure compensation to achieve the effect you’re looking for (or to get the “correct” exposure.)
The simplest example of where manual exposure is useful is when I’m, say, doing a portrait session outdoors where the light is constant. Depending on my framing and what other elements are in the picture, the camera may think there’s more or less light in the image. Remember, the camera doesn’t know what it’s looking at, and it’s going to try to achieve a distribution of tones from dark to light that it thinks the scene should have. The exposure algorithms are a bit more complicated these days, but, to simplify, the camera is shooting for an average of tones to be 18% gray.
So, point your camera at a completely white sheet. Take a picture in an automatic exposure mode. Now point your camera at a completely black field. Take a picture. You should have the exact or almost exact same picture: a medium gray field, even though the objects in reality are white and black. The camera is just trying to average both to gray.
Back to my portrait example. So I’m out shooting in constant lighting, but my camera may vary the exposure by 1/3 or 2/3 of a stop in either direction (in average circumstances–it can be more exaggerated in certain instances) because of varying elements in the frame. This isn’t fatal–it shouldn’t ruin your picture generally, you can tweak it later–but why give yourself the extra work? Also, when you start taking advantage of batch correcting in a program like Lightroom or Aperture or whatnot, you just correct that first photo and, as long as the lighting stays the same throughout your batch, you can just paste that correction across all the pictures in your set. If you were in automatic metering mode, you might have to tweak some pictures up a 1/3 of a stop, others down 1/3, and this also effects your contrast curves slightly, so you have that to deal with, etc.
Another case is in dramatic lighting conditions. Some of the coolest pictures are in lighting situations that the camera has a difficult time figuring out. When you encounter high contrast lighting situations, like say rim lighting at one extreme, the autoexposure is going to try to expose that picture to get shadow and midtone detail, which is going to completely kill the mood. Now that is an example of studio rim lighting, but you see this all the time in real life, like during sunrise and sunset, at bars, in theatres, etc. To properly preserve the mood of the shot and your artistic vision, you have to shoot this manual or dial in a good amount of exposure compensation (how much depends on the strength of the light, but usually at least 1.5-2 stops.)
When shooting indoors with flash, this is perhaps the most useful time to shoot manual. Effective flash use requires the photographer to balance flash with ambient light, and the way you control ambient light is through your camera exposure. Let’s say you’re in a bar. Your friend is sitting on a stool next to you, in a relatively dark area, while in the background you have some cool neon lights or other atmospheric lighting. If you take a picture of your friend on full auto or P, the camera will take a well-exposed picture of your friend, but absolutely kill the background, because it will usually default to a 1/60 second shutter speed. So, if you’re in a room where the background ambient is 1/15 f/2.8 and your camera is giving you 1/60 f/2.8, you’re losing a lot of mood and detail. Now, if you’re in A mode, it will go slower than that. The problem now is it’s generally going to set the exposure for the same exposure as a flash-less picture. Remember how I said your friend is sitting in a dark part of the bar? While it 1/15 f/2.8 may be fine to get the mood of the room in the background, your camera also is metering for this big black blob in front of you, so you might be getting a reading of 1/4 or even 1/2 and you’ll get far too much foreground ambient and a lot of “ghosting”. This may or may not be desirable, depending on what you’re going for, but by shooting manual, you can just dial in the precise amount of ambient light you need.
I know that got technical, and there’s a lot in there to try to understand–I wish I had some pictures to show all the situations in which a manual setting or overriding the auto setting with exposure compensation is useful, as it would be easier to understand when you can see the differences immediately.