CMS Made Simple uses plain HTML, CSS, and Smarty in all templates. This means that you can use any jQuery effect in your templates as well. jQuery is a JavaScript framework and has a lot of plugins.
To use the plugins, you have to separate HTML, CSS, and JavaScript and put them at the appropriate place in CMS Made Simple. HTML markup is saved in templates, whereas CSS is saved in stylesheets and then assigned to the template. JavaScript can be placed globally (Site Admin | Global Metadata), so that it is available on the whole page. Alternatively, it can be placed in the page template. In this case, it will be available in all pages using this template. If you need JavaScript only in one page, then you can add it to the Page Specific Metadata field in the Options tab.
Let's see how you can integrate a jQuery drop-down menu on your website. Examples and instructions for this effect can be found at http://www.dynamicdrive.com/style/csslibrary/item/jquery_multi_level_css_menu_horizontal_blue...