CPU can but it most cases won't affect your site speed. To clarify, if your CPU is not currently maxed out, you're most likely not going to see the bottleneck that a maxed out CPU would cause, thus your site won't be affected by said bottleneck.
If, on the other hand, you're CPU is maxed out, it can affect how quickly a service is able to respond to a request (i.e. Apache/NGINX), thus it can slow things down -- in some cases by a little, in some cases by quite a bit. It really depends on how maxed the CPU is.
That said, given the exact same configuration across two Droplets, one with a single 1.8GHz CPU and the other with a single 3.0GHz CPU (as examples), where the CPU is not maxed, there should be no difference in how quickly the website loads.
A faster CPU won't magically reduce a normal 3s load time down to 1s for example :-).
If your website is loading slow on a Droplet (or VPS in general) where there's no issue with the CPU being maxed, then:
1). You need to look in to optimization on the back-end (Apache, MySQL, etc) as those are most likely the issue. There's not much you can do when it comes to mod_php. While cPanel may ship with decent defaults, they aren't all-inclusive -- a control panel isn't a substitution for a sysadmin :-).
2). You need to look in to optimization of the front-end (caching, compressing images, reducing the number of and size of CSS/JS).
3). You need to reduce bloat -- more specifically, if you're not using a WordPress theme or plugin, don't just deactivate it, delete it. Not only will that help lighten things up, it's a security risk leaving them there if they aren't actively updated.
So what are High CPU Droplets good for?
Well, they work exactly like normal Droplets to start, so they're still good for general day-to-day workloads, but in the event you are having issues with CPU and you do need more power, they are perfect options. They do offer less storage though, so Block Storage becomes a bit of a necessity when using them.
They are also good for building -- for example, I test build NGINX from source a lot. When it comes to CPU, the
make command allows multi-CPU compiling so I can build NGINX much faster on a system with more and better CPU's than I can on one that doesn't have it available.
For cPanel, EasyApache should also run faster as well as, the last time I used it, quite a bit was built from source during command execution. So when you're updating Apache/PHP, this can help to speed things up drastically.
Just a few examples -- there's always plenty of use cases, though before jumping in and paying for something you may not need right now, take a close look at your current CPU.
1). How's it working so far?
2). What are your load averages?
3). Are you seeing high I/O wait?