It's basically an ingame mod starter kit/Mod storage.
AFAIK, It's not in-game, and it's not a starter kit. It's a separate, full program that doesn't store your mods, it's what you use to create them. So, to answer the question in the title: With enough patience and practice, it will let you do almost anything a Bethesda developer would be able to do.
Do not think of it as some pick-up-and-use tool akin to a map editor or some kind of simple program like that. It's basically the exact same tool Bethesda used to create the core game, and, while I'm sure Beth will try to make the mod-making process as least frustrating as possible for the modders, it is still a complicated tool that takes patience and dedication to learn.
If you want to learn more about it, I suggest downloading the http://cs.elderscrolls.com/constwiki/index.php/The_Elder_Scrolls_Construction_Set (If you don't have Oblivion, just read around on the wiki I linked to) and messing around with it a bit. It should give you a good idea of what to expect in the Creation Kit.