Spreadsheet sums solved

For most of us, most of the time, Excel is nothing more than a very sophisticated list maker that has big advantages over a word processor because it can perform mathematical tricks, while its grid-like layout makes it easy to get words and numbers neatly aligned. Although we might plan a course of action based on the figures in an Excel worksheet, it’s we who are making the decisions, not Excel.

All this can change if Excel’s Solver Add-in is used. It has the ability to test various scenarios in order to achieve a specific goal (usually maximising profits or minimising the use of resources) by following a set of rules dictated by the user. In other words, it does all the tedious time-consuming work of trying out alternative solutions until it finds the best one.

Problems, problems
Solver can tackle all sorts of complex problems but what it is good at is optimisation. For example it can tell a manufacturer the most profitable mix of goods to make from a fixed set of resources, or an investment advisor the best way to allocate a client’s limited funds to minimise risk.

In the office it can be used to allocate meeting rooms to avoid clashes and prevent rooms standing empty, and for a distribution company it can work out how best to pack delivery trucks to maximise the available space and minimise the number of vehicles that are required.

Actually, it can’t do any of these things until it has been installed, so before we get down to the nuts and bolts of setting up an example problem for Excel to solve, take a look at the walkthrough which you can click to at the end of this feature, which demonstrates how easy it is to install Solver on your computer. All instructions in this Masterclass have been designed for Excel 2003.

Achieving your target
Using Solver for really complex problems can be difficult: it’s a job for specialists and consultants, but there are lots of simpler tasks that anybody can tackle. The only theory you need to know involves breaking tasks down into variables, constraints and objectives. Don’t worry, these are less frightening than they sound.

The objective is what you want to achieve and it must be a number that will fit into a single cell on an Excel spreadsheet. Usually the number represents a maximum or minimum to be achieved, ie the most money or the least amount of time, but sometimes it’s a specific figure.