One catch regarding CPU upgrade is, that your CPU must not be too old, otherwise its socket has been replaced by newer version. An example, my almost 3 year old $2000 Dell computer, features a 2.4GHz Pentium 4 - a good cpu at that time. It uses socket 768 (i think). All modern Intel CPU's uses LGA 775 or something. That means, if I want to upgrade my CPU, I also need to upgrade my motherboard. This can easily require replacement of memory (DDR=old) and videocard (AGP 4x=also old) since new motherboard have DDR2 memory sockets and pci-express slots for video... Heck, maybe the powersupply also needs replacement. So whats left? - A DVD drive and a disabled floppy drive... Yes, I COULD buy a slightly faster Pentium 4 with the same old socket, but its MORE expensive than a brand new and MUCH faster Core 2 Duo - so thats really not tempting.
My conclusion, after spending a lot of time on how I can improve the civ4 experince on my computer, is that I cannot - not for a resonable price. A complete replacement of the PC, and letting the kids having the "old" Dell seems like a better idea - but I just can't justify that, cause Civ4 is the only thing requiring it...