Helping public sector managers get value for money from their budgets
A blog about public financial management: the fine art of managing public money to deliver vital services to the public. It involves budgeting, accounting, controlling, auditing, reporting, policy-making and decision-taking.
Over the years I’ve attended several courses on negotiation skills but none of them have been as helpful as reading Getting to Yes by Roger Fisher and William Ury. Read on to find out why.
Over the last couple of weeks I’ve been working on a small e-book about procurement in the public sector as a learning exercise. This is not learning how to write a book, but how to get a manuscript formatted as an e-book, with a cover, etc and uploaded to a platform for sale. It turns out that it is fairly easy to do but, like many things, the first time you do it takes a lot longer.
Read MoreLast week I wrote a post giving you five tips for improving your spreadsheets, and in that post I promised to share five more tips in the future. the first five tips were:
Inputs, calculations & outputs go on different worksheets
Use an input in only one place (so it is easy to change)
Do not hard code numbers into calculations
Break up long formulas into component calculations in separate rows/columns
Use the exact same formula across all the cells in a row
Read the post to get an explanation of these tips.
Below are five more tips, some of which build on the first five.
In tip 5 last week I recommended that you use a common formula across the whole of a row. One reason that people don’t use the same formula across all rows is because they insert sub-totals. In a spreadsheet with monthly columns they might have annual totals inserted every thirteenth column. There is, I think, a better way by using a consistent time period in a spreadsheet and then creating other spreadsheets that summarise the data in alternative time periods.
So, if you need monthly and yearly figures, first create your spreadsheet as monthly columns. These columns can have several rows of headings as needed, to show the count of the months, the financial year, calendar year, etc. What you then need to do is create a new worksheet for the annual totals. In this worksheet you can use a ‘SUMIF’ formula to sum all the amounts that are in year 1, year 2, etc.
This is even more flexible because, for example, if your financial year is not a calendar year you can easily create separate worksheets for each way of analysing the data, something you cannot easily do any other way.
If you put all your input data into an inputs worksheet it can be tempting to use named ranges to define them, to make it easier to write formulas in the calculation worksheet(s). This would be easier for you as the creator of the spreadsheet but it makes things harder for reviewers, auditors and people trying to update it. This is because it is quite difficult in Excel to work out what range the name defines. It can be done, but the dialogue box will list all the named ranges and the reviewer will have to scroll to find the one they want and probably will have to keep notes of them to avoid repeatedly having to make the check.
If you don’t use named ranges the reviewer can see exactly which cells and ranges are being used in calculations. This is much better.
When creating a spreadsheet you might want to simplify what is seen by hiding some of rows and/or columns using the Hide function. If you do this it can be difficult for a reviewer to understand what is going on. Essentially they have to look at all the row and column numbers and letters and spot where they are not consecutive. That can be surprisingly difficult to do.
A better way of tidying up rows and columns is to use the outlining function instead. This function can be done automatically (it finds the subtotals and totals for you and assumes that is where you want the outline to be) or you can do it manually. The advantage of this method is that small boxes with + or – signs appears in the top and left margins making it obvious to the review that there are rows/columns to open out or collapse as the case may be.
Macros are computer programs (scripts) embedded within the Excel file
Macros are an advanced technique and perhaps a relatively small proportion of Excel users could create them. Even if you know how to create them, my recommendation is that you do not use them. This is for several reasons:
like most of my tips, the use of macros make reviewing and updating your spreadsheet difficult (this would be less the case if you created full documentation for the macros but, let’s face it, would you really do that)
macros are not supported in all versions of Excel so you cannot be sure other users could even use the macros
if you are emailing the spreadsheet it may be seen as spam because it is an executable file (a programme) and not a data file.
My reason for recommending this is because the faint grey cell borders add a lot of visual clutter to spreadsheets which is not needed. I think the final spreadsheets are much cleaner and easier to understand without the cell borders. Apart from anything else, it makes any other formatting that you use (colours, shading, underlining, etc) much clearer.
Gridlines are the default option for all new spreadsheets. To turn them off, on a worksheet by worksheet basis, the easiest way is to select the View tab in the menu bar and uncheck the Gridlines box. This works for Windows and Mac versions.
This last tip causes the most discussion when I teach this subject face to face. I don’t quite know why that is. I know very few people who would change the default printer settings to add back the cell borders when printing their spreadsheets.
So there we go: ten tips for better spreadsheets. For more
I’m old enough—just—to remember accounting work before the rise of spreadsheets. We had a accounting ledger system on a mainframe compute to record all the transactions and it could produce printed reports but if you wanted to do any analysis you had to do it by hand. We had stationery called analysis paper which was A3 size, pale blue with a grid pattern in darker blue ink. There were wider cells on the left side and heading and footer rows, too. This stationery made it easier to lay out data in rows and columns but all the sums had to be done with a calculator, at least twice to check they were right. There were no graphs and no sorting on the fly.
Excel is ubiquitous now but in the mid-80s we moved from paper to SuperCalc (I knew some organisations used Lotus-123) and these were spreadsheets that pre-dated the graphical user interface and the mouse. They did not have colours: they were white or green on black depending on your monitor. And we used them like analysis paper, basically summing the numbers in our columns and rows.
Modern spreadsheet packages are massively more powerful with all types of arithmetic, financial, scientific, statistical and database functions built in. Indeed, there are more functions than most users are aware of. My point with this blog post, though, is not to explain some esoteric function but to address the fact that accountants (and others) are not trained in financial modelling. Instead of adhering to some basic standards or principles for all their work people lay out each spreadsheet to solve the problem they have right now. This is OK as long as no-one else is collaborating on the spreadsheet or, at some later date, asked to update it. At times like that the lack of standardisation means it is very difficult to understand how the spreadsheet works and it might be easier for the person to build a new spreadsheet from scratch—using their idiosyncratic style, of course—than update the old one.
One way to envisage what I mean is to think about a paper notebook. Your notebook is yours and it does not matter how you choose to keep notes at meetings. Your notes could jump around from topic to topic, mix words, numbers and pictures, have asides, scribbles and crossings-out but that does not stop you from being able to remember what you need to remember. In my experience most people create spreadsheets in much the same way. They start at the top left of the sheet and add things as they think of them. They are not thinking of future users as they build the spreadsheet.
But there is a better way. Some organisations do have standards for financial modelling so that there is consistency in the work produced by their staff. This makes good business sense since it saves time, enables quality control to be implemented consistently and, I reckon, reduces errors. (Errors in spreadsheets are legion and can be costly, either in money or reputation.)
In the absence of financial modelling standards in your organisation, here are five tips, or principles, that you can adopt in your spreadsheets. Although I mention Excel throughout they would equally work on Google Sheets, Apple's Numbers, or Open Office spreadsheets.
Excel opens to a blank spreadsheet and many people will start creating their masterpiece on ‘Sheet 1’ and build it up and up until it’s done. For years it’s been possible to have multiple worksheets within a spreadsheet file and you move between them by selecting the tabs at the bottom of the screen. I suggest as a minimum you use three worksheets, one for your inputs and assumptions, one for your calculations and one for your outputs. If you want a variety of output formats you might want multiple output worksheets, and if your calculations are complicated you might need several worksheets for them so they are easier to follow (and audit).
It’s separating the inputs that I think is most important. By inputs I mean the key assumptions, such as start and end dates, interest rates, inflation, etc and also data specific to your model. It’s here that you would have all the relevant data for the model such as population information, unit costs, prices. If your organisation has specific financial policies such as the discount rate to use or the profit margin to apply you would also include them on this page. This simple tip, combined with tip 2, makes the job of someone asked to update your spreadsheet much easier. Basically they ought to be able to look at the input sheet and make amendments there which ripple through the calculation sheet(s) into the output.
This is an important tip for avoiding errors and making updates easier. If the interest rate is in cell C10 on the ‘Inputs’ worksheet then wherever it is needed in the calculations it should be referenced as Inputs!C10.
The formulas in the cells in the calculations worksheet(s) should not include actual numbers. The reason for this is that the calculations should be drawing on data on the inputs sheet(s). If you are including some data directly into your calculations you are making it very difficult for someone to understand your model and be able to update it. Even if they find the number within your calculations they may not know why it is there or where it is from. The possible exception to this rule is that sometimes it might be useful to include the numbers 0 and 1 in formulas. For example, you might use them as results from a conditional formula because 0 and 1 are the logical values for false and true.
It’s possible to have very long formulas in a spreadsheet calculation especially if you are nesting If statements or the like. Developing these formulas can be difficult because you need to get all the arguments right and include the correct number of commas and parentheses; and once created, they can be difficult for someone to understand. It can help, therefore, to break the whole formula into component parts and set them out on the rows (or columns) of your worksheet. This makes it easier for you to create the formulas and makes it easier for someone to follow your logic.
When using Excel have you ever noticed a small green triangle appear in the top corner of some of the cells. This is an audit warning marker. It appears when the formula in a cell does not match the neighbouring cells. This is a useful feature but only if you create your worksheets so that the same formula is used across a whole row. If that is how you build your spreadsheets then the green triangle actually works to draw our attention to a possible (likely) error. If you create spreadsheets where the formula used in each column is different then the triangle is no help. Creating formulas that are the same across the rows does require a little bit of extra thinking. This could be, for example, because the calculation you want to create in the first and/or last period of the date range are different from the calculations for all the other periods. You might need a calculation that includes an ‘If’ statement to identify whether it is the first or last period. Another example might be to base the calculations on the number of days in the month. For that you might need a row with a formula that calculates the number of days in the period (i.e. the end date of this period minus end date of prior period) and then use the answers in subsequent formulas.
I’m confident these five tips will improve your spreadsheets immensely but there’s more you can do so I’ll post five more tips soon. Meantime, you can always share these tips with colleagues to improve their work and, if you want to be kept informed about my work, please join my mailing list here.
More than 25 years ago the Committee on Standards in Public Life held an inquiry into Parliamentary scandals and created seven principles that everyone in public life should adhere to.
In my forthcoming course, The Principles of Managing Public Money, there will be a section on making decisions about public money. Making such decisions requires the decision-maker to have integrity and I will be referring to the SELF test for decisions.
Read MoreI’m working on an online course about the principles of managing public money. The course is aimed at everyone in the public sector, whether employees, politicians, volunteers, etc. I want it to be a great course that is valuable to the people who register for it. I’ve got over thirty years’ experience of managing public money and there is a temptation to cram all my knowledge into a single course, but that would not be a good idea.
There’s not a technology reason why I could not have a course comprised of hundreds of lessons that would take the learner many weeks to complete. There is, I think, a practical limit on what a person is willing to take on, especially if it is additional to their work commitments. This means that one of my challenges is to find the balance between including enough material for the course to be valuable without going into too much detail and becoming onerous — or worse, boring. I’ve spent quite a bit of time, therefore, deciding what to include and what to exclude and I’ve had to delete some sections and ideas that I would like to teach in order to keep things focused.
The platform I am using for my courses allows lessons to be grouped into modules. I think five modules, each taking one to two hours to complete is about right. This is something someone could complete in a day if they really wanted to, but more likely they could complete it over 1 to 4 weeks to fit with their schedule.
The five modules I have now decided on are set out below.
These are my thoughts on grouping the course into modules. I would be interested in hearing your thoughts and observations in the comments section.
If you’re interested in this course you can sign up for my mailing list and be the first to know when the course opens for registration by clicking here.
© Gary Bandy 2024
