Policy analytics need more than a spreadsheet: a case study in funding formulae

This article presents two case studies, concerning the allocation of £Billions by a mechanism communicated via spreadsheet models. It argues that technical analytic skills as well as policy development skills are a vital component of governance. In the UK, Central Government uses funding formulae to distribute money to local service providers. One commonly stated goal of such formulae is equity of service provision. However, given the complexity of public services, together with variations in need, delivery style and the exercise of stakeholder judgement as to which needs should be met and how, such formulae frequently obscure the process by which equity has been taken into account. One policy ‘solution’ to managing such tensions is to seek ‘transparency’. With respect to funding formulae, this commonly involves publishing the underlying data and formulae in spreadsheets. This paper extends the argument that such ‘transparency’ requires an audience that understands the policy assumptions (and related conceptualisations), data sources, methodological approaches and interpretation of results. It demonstrates how the search for policy ‘transparency’ is also met by the technical quality assurance goals that the operational research community would recognise as best practice in the development both of software generally and spreadsheet models specifically. Illustrative examples of complex formulae acting to subvert equity are drawn from the English Fire and Rescue Service and Police Service allocation formulae. In the former, an increase in the amount of deprivation, as measured by one of six indicators, has the perverse effect of decreasing the financial allocation. In the latter, metropolitan areas such as London are found to gain most from the inclusion of variables measuring sparsity. The conclusion from these scenarios is that the steps needed to for technical quality assurance and policy transparency are mutually reinforcing goals, with policy analysts urged to make greater use of technical analytic skills in software development.