Spreadsheet sums solved

Variables are the resources that can be changed to meet the objective, so for a DIY enthusiast the variables could include decorating materials of varying costs. For a manufacturer planning which products to produce, the resources include raw materials, labour, warehousing space and machine tools. Whatever the variable resource, it must be expressible in numerical terms such as total man-hours, kilos of tomatoes, number of griddles and so on.

Constraints are the real-world practical considerations that affect the decision-making process. For example our DIY fan must cover all four walls. Constraints are also used to specify which resources can be split, so while it’s fine to use half a kilo of ingredients or half an hour of time, it’s not OK to produce half a car windscreen.

A practical example
So that’s the theory, now let’s try it out. The scenario is of a garden furniture manufacturer making three items: tables, benches and chairs. All three are made from different combinations of the same five components: wood slats, legs, braces, screws and cushions. The aim is to maximise profits by making the most suitable mix of items from the materials in stock, yet satisfying all the sales and marketing criteria the manufacturer knows from experience.

Download the worksheet SolverEx1.xls from our website. As yet, this worksheet contains no formulas, only labels and numbers. The figures in cells B2 to D6 define how many components of each type are required when making the three items of furniture. Those in column E show how many components are in stock, and those on row 9 show the profit per item for the finished goods.

The yellow areas are for formulas, which are yet to be inserted, and the green band in row 8 represents the three variables that Solver is allowed to change. The cell with the red border is the objective to be maximised, ie total profits.

Download the formulas in the form of SolverEx2.xls. They have all been made as straightforward as possible: those in column F compute the components required to build however many items have been determined by Solver (in the green band). Those in column G represent how many components of each type are left over.

The formulas in B10 to D10 compute the profits generated by making Solver’s suggested mix of products, and these are added together in cell G10 to arrive at the total profit, which is the figure Solver is trying to maximise by changing the values of the green cells. The dollar signs in the formula in cell F2 are there for a reason; so that the single formula can be copied into the cells beneath it without modification, thus saving a lot of typing.