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.

This is a preview of subscription content, access via your institution.

Fig. 1
Fig. 2
Fig. 3


  1. Andrews, R. (2010). The impact of modernisation on fire authority performance: an empirical evaluation. Policy & Politics, 38(4), 599–617.

    Google Scholar 

  2. Antoniu, T., Steckler, P., Krishnamurthi, S., Neuwirth, E., & Felleisen, M. (2004). Validating the unit correctness of spreadsheet programs. In Proceedings of the 26th international conference on software engineering, ICSE 2004 (pp. 439–448). New York: IEEE Press.

    Google Scholar 

  3. Asthana, S., Gibson, A., & Halliday, J. (2012). The medicalisation of health inequalities and the English NHS: the role of resource allocation. In Health economics, policy, and law (p. 1).

    Google Scholar 

  4. Department for Communities and Local Government (2003). Balance of funding review: 7, equalisation and gearing. Department for Communities and Local Government. www.local.odpm.gov.uk/finance/balance/bof7.pdf. Accessed 14/9/2011.

  5. Department for Communities and Local Government (2007). Fire and rescue operational statistics. http://www.communities.gov.uk/publications/corporate/statistics/frsoperationalstats2007-08. Accessed 14/10/2010.

  6. Department for Communities and Local Government (2009). Headline allocations for the 2009/2010 settlement. http://www.local.communities.gov.uk/finance/0910/head0910.xls. Accessed 29/10/2012, Not available for Shire Counties.

  7. Department for Communities and Local Government (2012). Fire and rescue national framework for England. Communities and Local Government.

  8. Department for Communities and Local Government (2013). Calculation of 2013/2014 formula funding. Department for Communities and Local Government.

  9. Fire Formula Working Group (2005). Minutes ffwg(01)2005. http://www.local.communities.gov.uk/finance/0607/ffwg0501/minutes.pdf. Accessed 28/10/2012.

  10. Gibson, A., & Asthana, S. (2011). Resource allocation for English local government: a critique of the four-block model. Journal of the Royal Statistical Society. Series A. Statistics in Society, 174(3), 529–546.

    Article  Google Scholar 

  11. Gilbert, S. (2003). An evaluation of regional cost allocation models and applications for the Alameda County Fire Department. National Fire Academy.

  12. Halliday, J., Asthana, S., Hewson, P., & Gibson, A. (2012). Playing with fire: limitations of the big society for an emergency service. Public Policy and Administration.

  13. Hewson, P., & Halliday, J. (2011). Discussion on resource allocation models. Journal of the Royal Statistical Society. Series A. Statistics in Society, 174, 558–561.

    Google Scholar 

  14. Janvrin, D., & Morrison, J. (2000). Using a structured design approach to reduce risks in end user spreadsheet development. Information & Management, 37(1), 1–12.

    Article  Google Scholar 

  15. Krishna, V., Cook, C., Keller, D., Wallace, J., Burnett, M., & Rothermel, G. (2001). Incorporating incremental validation and impact analysis into spreadsheet maintenance: an empirical study. In Proceedings of the IEEE international conference on software maintenance (pp. 72–81). New York: IEEE Press.

    Google Scholar 

  16. Kruger, L. (2004). Assistance to firefighters program: distribution of fire grant funding. Congressional Research Service, Library of Congress.

  17. Local Government Finance (England) (2013) The local government finance report (England) 2013/2014. Department for Communities and Local Government.

  18. Mittermeir, R., & Clermont, M. (2002). Finding high-level structures in spreadsheet programs. In Proceedings of the ninth working conference on reverse engineering (pp. 221–232). New York: IEEE Press.

    Google Scholar 

  19. National Audit Office (2011). Landscape review: formula funding of local public services. National Audit Office.

  20. Ólafsson, S. (1998). Teaching mathematical modelling to business students. Annals of Operations Research, 82, 49–58.

    Article  Google Scholar 

  21. Panko, R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing, 10(2), 15–21.

    Article  Google Scholar 

  22. Powell, S., Baker, K., & Lawson, B. (2008). A critical review of the literature on spreadsheet errors. Decision Support Systems, 46(1), 128–138.

    Article  Google Scholar 

  23. Public Accounts Committee (2011). Formula funding of local public services. Parliament UK, 55th Report.

  24. Reinhart, C. M., & Rogoff, K. S. (2010). Growth in a time of debt. The American Economic Review, 100, 537–578.

    Article  Google Scholar 

  25. Rothermel, K., Cook, C., Burnett, M., Schonfeld, J., Green, T., & Rothermel, G. (2000). Wysiwyt testing in the spreadsheet paradigm: an empirical evaluation. In Proceedings of the 2000 international conference on software engineering (pp. 230–239). New York: IEEE Press.

    Google Scholar 

  26. Royce, W. (1970). Managing the development of large software systems. In Proceedings of IEEE WESCON, Los Angeles (Vol. 26(8)).

    Google Scholar 

  27. Sandford, M. (2013). The local government resource review. House of Commons Library.

  28. Settlement Working Group (2009). Local Government Finance Settlement 2010/11 settlement working group meeting 25 march 2009. Department for Communities and Local Government. http://www.local.communities.gov.uk/finance/1011/swg/090325.htm. Accessed 24th June 2013.

  29. Smith, P. (2006). Formula funding of public services (Vol. 41). London: Routledge.

    Google Scholar 

  30. Stone, M. (2012). Getting to grips with England’s formula for local authority support. Public Money & Management, 32(2), 145–152.

    Article  Google Scholar 

  31. Weistroffer, H. R., & Narula, S. C. (1997). The state of multiple criteria decision support software. Annals of Operations Research, 72, 299–313.

    Article  Google Scholar 

Download references


This work was funded by the Rural Services Partnership.

Author information



Corresponding author

Correspondence to Paul Hewson.

Additional information

This work was funded by the Rural Services Partnership.



Fire and road fatalities by fire and rescue service 2007.

Table 2 Audit Commission data on Fire Deaths and population 2007, Department for Transport “STATs19” data on road death

Rights and permissions

Reprints and Permissions

About this article

Cite this article

Hewson, P., Halliday, J., Gibson, A. et al. Policy analytics need more than a spreadsheet: a case study in funding formulae. Ann Oper Res 236, 215–232 (2016). https://doi.org/10.1007/s10479-013-1475-4

Download citation


  • Funding formula
  • Spreadsheet errors
  • Transparency
  • Software development methodology