Excel Solver Primer for Construction Stakeholders

Solver in Excel does what its name is. Like any other excel tool it is best learnt through an example. Let us look at stakeholder’s motives. 

Builder’s motives are plain and simple profit maximization. Professional motives are a bit complex and difficult to formulate. 

The Architect want space planning to meet his design vision. The Green Building Professional wants external façade to optimize daylight as well as thermal insulation. The structural and project Engineers want  the structure and construction process to be safe.

The Project Management team is required to satisfy the needs of all the stakeholders.



The tool that can resolve it is “Excel Solver”. 

In case you are using the Solver add-in for the first time you need to get familiar with the solver parameters dialogue box. But before you can do it you need to add in the solver as though it comes along with Excel, solver is not automatically loaded.

To load the solver

File,Excel Options, add-ins, Manage Excel add-ins, select the Excel add-in checkbox.

Problem Definition:  For sake of simplicity let us assume that the business requirement is to construct a 100 Sqm clear space with a given clear height. Qualitative analysis during initial meeting with the Architect MEP and structural engineer decides for a flat slab load bearing masonry structure.  The cost surveyor collects the input rates and quantity scenarios of Concrete, rebar steel, masonry work, for windows and for façade insulation etc. (as required by the green building norms)

From the cost parametric equations, it is clear that major cost inputs are dependent on the space aspect ratio. The steel rebar and RCC quantities and hence the costs exponentially increase as the aspect ratio reaches 1. The periphery masonry and façade lengths and costs decrease as we approach an aspect ratio 1. This suggests that the minimum cost for constructing the required space exists at some given aspect ratio which fits in the space planning requirements.

We are to minimize cost while meeting the performance criteria set by professionals. Please download Excel workbook   CostOptimizationSolverPrimer from the link. 

For clarity, various cell inputs and outputs have been named as Width, TotalCost etc. 

For opening the Solver Parameter window

Data, Solver

In the Solver Parameter Window

1. & 2      Set Objective: Minimize the TotalCost

3   .   By Changing Variable cells: Short Span i.e. ‘Width’

4S.ubject to the constraints:Width>=7 Meters & Width <=10 Meter
5.For this primer follow the default solving Method and click solve.

Excel quickly solves and let you know the short span which will minimize the cost while meeting the professionals' nod as  defined during the design process.

Experiment with the workbook by changing inputs. Now think of what else can be done with this wounder tool....