In reply to:
CSS is self explanatory, unless the template you're using isn't very user friendly
Unfortunately, you have just described a great many of the commonly available mambo templates many use to attempt their own "tweaking". There are, however, numerous tutorials (of varying quality, granted, but some are excellent!) about designing mambo templates and guides to css usage with mambo. These are easily found via google, and discussed at length on the principle mambo related community sites.
Three thoughts come to mind:
1) Check out Joomla - forked/branch/descendant (you choose depending on your politics in the whole mambo/joomla split fiasco!) with more active support than mambo and IMHO better code. Vist joomla.org for forums that are *much* more active than the mambo sites.
2) Spend a little time browsing through the many template download sites available specifically for GPL mambo/joomla templates. There are literally *hundreds* of free templates with great variety. You might not have to "tweak" at all to find something that works for you.
3) If you have already had a "bad experience" and want to comission template work, consider going with one of the several "old-timers" in the mambo/joomla template design business. These guys (absolam, rhuk, peek, and *many others* - you can tell by the samples of their work you will encounter browsing around via suggestion 2 above) have earned their reputations and are not that much more expensive (if at all!) than anyone else you are likely to find.
Good Luck!
--rlparker