A recent consultancy project spurred to me to write a bit here about some tips and tricks for spreadsheets. Every new thing you learn about working with spreadsheets adds to your skills set and gives options for new and better ways of working with your data – whether it be financial or non-financial data.
The project I’ve been undertaking is for a community based political activist group in the North Rodney area, in the northern part of the Auckland ‘super city’ council area of New Zealand. This group has been advocating for a number of years for the creation of a separate small council with unitary powers (i.e. both district and regional council activities) which is better engaged with the infrastructure needs of the local community and able to address these more efficiently and effectively.
A recent consultancy report was commissioned by the Local Government Commission as part of a review of local governance arrangements. The report estimated that a separate North Rodney council would not be viable, as its annual operating expenditure would greatly exceed its annual operating income. The analysis was based wholly on Auckland Council sub-regional data and various assumptions around apportioning these.
My task was to disprove this conclusion by developing a financial benchmarking tool, choosing appropriate comparator councils in consultation with the client, and hence showing that a similar council in North Rodney could potentially be financially viable.
The tool I developed to solve this problem comprised a spreadsheet containing 2015/16 operating income and expenditure data for all councils in New Zealand. Two alternative datasets were used to ‘triangulate’ the results and provide a reasonable range – budgeted 2015/16 results compiled by the Department of Internal Affairs from all councils’ Long Term Plans; and Statistics New Zealand’s standardised reporting of 2015/16 operating income and expenditure for all councils.
Now for the tips and tricks. The four key spreadsheeting approaches that I used for this analysis are as follows:
- Worksheets – Before beginning, and with some tweaks along the way, I thought about what ‘tabs’ or worksheets I wanted to create to give the spreadsheet its structure. I gave each tab a name, such as ‘Year One summary results’, and ‘Roading activity expenditure’, and sorted them in a logical order.
- Navigation hyperlinks – When you are working with a large number of tabs, it can get annoying and time-consuming scrolling along to find the right one. This can be addressed by creating a separate ‘Index’ tab; inserting the word ‘Index’ at the top of each tab in cell A1 and hyperlinking this to the Index page; creating a contents list on the Index page; and hyperlinking each row of the Index page to the relevant tab name. Using the Index page and the hyperlinks at the top of each page, navigation becomes quick and easy – just click and jump to where you want to go.
- Drop-down lists – For a benchmarking tool, there is a fixed and finite number of items to be selected – in this case, all the councils in New Zealand. Moreover, for what I had in mind (see below), there was benefit in ensuring that each council was precisely named by restricting the choices the user could make. This was resolved by setting up a drop-down list for each council in the results tabs, and similarly in the sensitivity and scenario testing tabs. To change from one comparator council to another, it’s just a case of clicking on the cell, scrolling to the name of the new council and selecting it. The value in the selected cell can then be quickly copied-and-pasted to other cells. This greatly sped up, automated and ‘fool-proofed’ the task of selecting comparator councils.
- VLookup – The final tip, in combination with the drop-down lists for council selection as above, provided the real power of the financial benchmarking tool. It does this by automating the task of updating relevant income and expenditure data for each council selected. This was done using the VLookup function. If you haven’t used this before, keep reading – this is something that may be useful to you in future. The VLookup formula comprises four parts: the location of the value of interest (in this case, the cell containing the council name); a table of data (in this case, a table of data contained in a specific tab, for example ‘Wastewater activity expenditure’); which column in the table contains the data you are interested in (e.g. give me the value in column 4 for this council); and a true/false parameter on whether you want to allow approximate matches (I always set this to ‘false’). The combination of drop-down lists and VLookups provides an automated approach to compiling substantial amounts of detailed data into precise results, with a high degree of flexibility in this case for mixing-and-matching council income and expenditure items.
Overall, even though it sounds a bit complicated to set up, the approach above created a user-friendly and intuitive tool that anyone can quickly learn to use – and use rapidly.
Of course, there are plenty of other tricks you can do with spreadsheets, and I’m no guru on these matters. Hopefully some of the ideas above may inspire you to try out new ways to improve the efficiency and effectiveness of your spreadsheeting.
P.S. To find out more about each of these tips, click on the hyperlinked text throughout the blog post.