Something none of the answers have really spelled out yet is the true effect of combining speed modules with productivity modules.
The effects
The key observation is that the effects stack additively. This means that, e.g. an Assembler 3 with 4x Productivity 3 modules will produce 40% more items per input, while working at 40% speed (-60% malus). And consuming 320% more power for 420% total, but more on that later.
Meanwhile a single Speed 3 increases speed by 50%. Your assembler has all its slots taken up by Prod mods, and beacons only transmit half the effect, so it's 25% per module. Place four beacons in range of your assembler, and the modules give it a +100% bonus. Summed with the malus from the prods, it totals 140% speed. While also having 40% extra output (for free).
The result is that you need way fewer machines to make way more output with way less input.
Let's visualise it.
- Here's a calculator output for one yellow belt of red circuits. Notice how it needs 2 input belts of iron, 5 input belts of copper, and 114 assemblers total (plus miscellanea for oil products). And uses 107 MW of power.
- And now here's the same assembly line, heavily moduled (with four speed-3 beacons in range of each assembler): we're using 36% as much copper, ~36% as much oil, 45% as much iron, and only 31 assemblers. And the assemblers themselvers are only using about 22% extra power (this doesn't take into account beacon power draw, but again, more on that later).
But why bother?
The effects are absolutely drastic for large-scale builds.
The poster child of the effect of modules is probably rocket science, which I will leave for you to peruse yourself in
What does this mean? Everything is smaller and easier. Sure, you can always just build a larger factory. Place 10x more assemblers and furnaces, 3x more miners (on 3x more patches), and get the same output. But why not place 3x more miners, then only 3x more assemblers and furnaces, and get 3x the output?
Because space is free (and infinite), but logistics aren't. Your items need to get from point A to point B, and your factory has a large amount of different processing lines all feeding into each other. It's just that much more effort to connect 10x bigger assembly lines to each other. It's just wasting your play time with needless complexity. Not that it trivialises the game or anything: if you want the extra logistical complexity, just build even bigger while reaping the advantages of modules!
Some of the other answers have mentioned UPS/lag, which becomes relevant for pushing the game to its limits (or if you're playing on a weaker machine). Beacons do not take any processing power - they just apply an attribute to nearby machines - so being able to build the same factory with 5x-10x fewer machines means you get that much less lag. Or conversely, you can build a 5x-10x bigger factory before the game slows down.
A note on mining speed, late-game
Mining productivity research essentially gives you free ores per ore. It's also the only infinitely repeatable research whose costs grow linearly rather than exponentially, so it's realistic to reach very high levels when going for endgame builds. At minining productivity 10 you have a 100% bonus, meaning two ores per ore; at mining productivity 100 you have a 1000% bonus, meaning you get ten free ores for every ore mined (or 11 ores per ore).
At those levels, ore patches start lasting for a very long time indeed. And so it starts making sense to add speed modules to miners, to get more output from the mine. It's not gonna run out for dozens of hours anyway, and it's just faster to stick some modules than it is to run out, find another patch, lay more miners, lay your train tracks to it, hook it up to your train schedules...
But what about power?
Beacons take a lot of power to run. Especially if you're going to be building a factory containing thousands of the things.
Here's the thing, though: power generation in Factorio is really cheap and easy. Usually, a power plant will take up about 5% of a typical factory*. So think of it this way: it's much easier to quadruple your power production while building a 5x-10x smaller factory, than it would be to quintuple your entire factory. If you were willing to put down 1000 extra assembly machines and 2000 extra furnaces - it's much much easier to, instead, simply plop down 8 extra nuclear reactors and the few dozen heat exchangers and turbines around them.
Oh, and pollution is worth mentioning here. Beacons don't generate pollution either, so funnily enough despite both speeds and prods having large pollution penalties, because you'll be using just that much fewer machines, I don't remember the maths but I think moduled factories are either actually greener than unmoduled ones, or at the very least barely any more polluting.
*The elephant in the room here is solar power, which is notorious for requiring vast tracts of land to get covered in solar panels to give reasonable outputs. But here's the thing, you need so many solar panels that you're probably going to automate its construction (with expandable roboported blueprints) already anyway. And solar fields simply... exist; they require absolutely zero logistics to set up, once you've got the blueprint designed and kicked off - so in their case, space really is infinite and building large is completely free.
But what if I'm not building giant endgame bases
Well then modules aren't essentials. You can easily launch the rocket without ever touching a module.
The tl;dr is that modules drastically cut down the time and effort to build huge megabases; and make it possible to build far bigger ones without lagging than could be done otherwise. But they're definitely not necessary or essential when building at small scales, like just enough to launch one rocket in vanilla.
What about efficiency?
I've focused this answer on speed and prod, because ultra-end-game, these are the ones that matter, and they are the ones that are truly essential. Efficiency is mainly useful in other scenarios - namely, when you want to reduce pollution. But when you do want to reduce pollution, they're absolutely cracked - the maximum you can get on any machine is -80% power consumption (and pollution), and you can easily reach that on most machines with just tier 1 greens.
As a result they can be a good candidate for sticking into miners, which normally generate a huge amount of pollution, provided you're not ultra end game yet and don't have huge mining productivity bonuses (at which point you'll be wanting to switch to speed 3s as mentioned earlier). But in general, tier 1 greens are a very cheap way to cut down your pollution and power consumption if you ever need to for any reason.