Although I am very favorable of a hole with strategy and risk-reward, I almost always support the 18th hole being a tough test. As a tournament golfer, I dislike par 5 finishers. Yes, it gives great drama for a player trying to make up that last shot, but it really makes the hole less dramatic for a guy with a 1 or 2 shot lead. Sure, not every course is set up to play tournaments, but even in a friendly match I would hate to see my opponent have a cake-walk par to win.
Thus, I prefer a strong test on the 18th. I don't really like water or OB, because that ruins the fun. One shot in the water or over the fence and the hole is over. As Flynn once said, the recovery shot is the greatest fun in golf - or something like that. Therefore, an ideal finishing hole would be one that is a stern test - a hard par - requiring the golfer to execute if he/she desires to post a score. There should be some risk involved off the tee, so the golfer can decide to either make the tee shot easier or the approach shot, and pricing in the risk to do so.
In normal conditions, I like the 18th at Muirfield. It is not a perfect representation of my ideal finisher but does the job well. The risk is really in the rough, which is darn cruel. I remember having a good round there once and contemplating hitting a 3 wood or bunt driver to ensure being in the fairway, accepting a longer, more difficult approach. Since it wasn't a tournament, and the round was not record-setting by any means, I ended up hitting a full driver into the fairway. However, I could only imagine the toughness in tournament conditions. The nerves standing on the tee, knowing I have to hit the fairway. Yes, the rough is a little too thick for my standards, but recovery is still an option (might be able to progress the ball). I just hate easy finishers; make the golfer earn his score!