The biggest passion in my bid and proposal world right now is the Excel modelling of profitability within a bid-price. This is not just about calculating the selling price to generate revenue, but also the estimation (and subtraction) of whole-life costs of delivery to understand likely ‘end result’ gross and net margins. Once constructed, such models can ‘play tunes’ to calibrate the prices offered to a customer and the likely end results of profits for the supplier.
A particular challenge is trying to ‘beat the clock’ to get an offer out in response to a customer’s request for proposal whilst ruminating on the likely profit margins when selling products or services at a certain price. Yes, the price would generate income, but would the extent of the income cover the consequential costs of delivery across people, processes, risk, inflation, Forex, third party suppliers, logistics, duties/taxes and overheads?
Making a profit from a selling price that will keep finance directorates happy at the point of sign off and offering a competitive price to a customer are not always one and the same. They often contradict each other and without detail, accuracy, agility and scrutiny this price vs profit debate can make sign-offs and the release of an approved price to a customer rather difficult.
All companies want to win large volumes of work and make large profits. In heavily competed tenders (usually the ones everyone wants to win), there can be difficult decisions to make between winning the work and making large profits. Of course, the idea is not to win any work that loses money, unless it is part of a longer term strategy or business case.
The aim of modelling profitability is to predict and adjust a sell-price (for delivering a product or service to a customer within a contractual arrangement) and then report how much profit can be expected. As this is all calculated before the work commences, it is a ‘best guess’ and therefore inaccuracy is a considerable risk.
In terms of using Excel to calculate profitability, it is all well and good to estimate that the price is 100 and that the cost is 75 and, therefore, there is a profit margin of 25%. However, that 25% can never be realised if the price is unrealistic (in terms of what is contractually agreed with a customer and consequently invoiced) or if costs do not reflect the nature of the work to be delivered or if some cost items are missing (such as inflation, duties/taxes, Forex, indirect costs, corporate overheads, etc).
Furthermore, cashflow can mean that whilst the final profit is 25%, it will not be a month by month profit. There may be significant costs incurred at the beginning of the work (particularly in design and build projects), with revenues not being received until milestones have been reached.
Modelling profitability therefore has to be evidence-based and reconcilable to other completed projects or work that have actual management accounts and P&Ls. Scrutiny of these will, at least, ensure that all cost items have at been covered in the model. Actual costs from other work within the organisation can also provide a means of validation by adopting parametric and/or analogous estimating.
Parametrics is where parameters are drawn-up to gauge the cost of a product or service (e.g. £/ hr, £/device, £/month). Once baseline values have been established from existing evidence, these can be applied to the profitability model or even used as inputs. Analogous estimating is when the total cost of a similar product or service is used on a pro-rata basis or used as a means to gauge the order of magnitude of a profitability model.
The best case is to model profitability as a repeatable standard procedure in bid pricing and then use these models to manage the accounts throughout the lifetime of the project. In essence, the profitability models are transferred into budgets that actual income and expenditure is tracked against. This way, variance analysis can be conducted in real-time and the lessons learned can be fed back to new models – setting optimism bias or improving the accuracy of model inputs, such as unit costs, people utilisation or volumes/types of activity employed. Over time the accuracy of profitability modelling increases and the models become a reliable tool.
This article was written by Peter Bryans.
Specialising in strategic cost modelling, estimating and pricing, Peter’s 15 year career has seen him at Network Rail, Arup and Detica (now BAE Systems Applied Intelligence) and most recently in senior cost estimating and pricing roles at Aegis and Thales.
