Windows Movie Maker allows you to add titles, special effects, background sounds, narration etc and alows you to burn it onto the CD...
Windows Movie Maker rocks...
Ulead Video StudioThe Complete Movie Making Software, Ulead® VideoStudio™ 8 is home video editing software that makes editing your movies as fun as shooting them. And, if you've never edited a movie before, make one in three easy steps using the new Movie Wizard mode. Share finished projects on DVD, tape, the Web, and mobile devices. Unmatched stability, high-speed rendering and real-time performance mean less waiting and more time for creating
*www.ulead.com/vs/runme.htm
If you want to spend some $100, you can go for Ulead Video Studio..
*www.ulead.com/vs/trial.htm
If you want to share your pictures with your friends then you can create a screen saver or a executable application with
Irfan View.