It provides a path for a formula's contents that does not change across version upgrades.
Consider this scenario: Say you build libfoo.dylib with Homebrew. It is version 2.0.0, and so it lives at /usr/local/Cellar/libfoo/2.0.0/lib/libfoo.dylib. You want to link to it from another program you are building, so you pass -L/usr/local/Cellar/libfoo/2.0.0/lib -lfoo to gcc. Your program compiles. Later on, you upgrade to libfoo 2.0.1 and remove v2.0.0. Now /usr/local/Cellar/libfoo/2.0.0/lib/libfoo.dylib no longer exists, and your program no longer runs, because it can't dynamically load libfoo.
That's okay. libfoo.dylib is also available at /usr/local/lib/libfoo.dylib. It's a symlink to the latest version of libfoo, so it should always be present. So you pass -L/usr/local/lib -lfoo to your program and compile it. Later you upgrade to libfoo 2.0.1. No problem, because /usr/local/lib/libfoo.dylib is still present and points to the v2.0.1 copy.
That's great, and Homebrew existed with just that system for a while. The problem is, some formula are "keg-only", so they are not symlinked from /usr/local. (Generally they are keg-only because they shadow a version of a library that ships with OS X, and superseding OS X libraries can cause problems.) Say you want to link to a keg-only version of the library. It's not symlinked from /usr/local/lib, so you have to give the full path to the version installed in /usr/local/Cellar, which is brings you back to the first problem listed above.
/usr/local/opt solves this problem. It provides a place for the current version of all formulae to be symlinked, regardless of whether they are keg-only or not. Now, when you want to compile your program, you can use -L/usr/local/opt/libfoo/lib -lfoo, and your program will link to the latest version of libfoo, even if you upgrade it and even if it is keg-only.