When it comes to writing financial reports I encourage you to adopt a plain, direct style. If you do not then, at best, your reports will be over-written and, at worst, you will obfuscate the message you are trying to communicate. (I suppose I should, on that basis, permit over-writing if your intention is to obfuscate!)Read More
Good design helps people to use products and this applies to spreadsheets and financial models as much as it does to smartphones and websites. When you create a spreadsheet you should consider how it is formatted as part of its overall design. I have made a short video with three tips for formatting your spreadsheets to ensure they are useable.Read More
One of my goals for 2019 is to get my online school up and running and today I have published my first proper course. It’s a short course, taught mostly using videos, about the standards and principles that underpin good financial models. By adopting these principles you will make fewer errors in your spreadsheets and it will be easier for you, and others, to modify or revise the model after it is built. And because you have skills that are seldom taught to accountants you will stand out from the crowd.
The course fee is £49 but you can get 25% off From today until until 28 February 2019 by using the coupon code LAUNCH25. Get the course here.
This course is the first of five that I have planned under the umbrella of Skills for (Public Sector) Accountants. All five of the courses are about areas of work that are important to accountants but which are not taught in a professional accounting qualification. Following on from the financial modelling course will be courses about report writing, making presentations, managing suppliers and measuring value for money.
The remaining four courses in the bundle will be published over the first few months of 2019. If you want to get each course as it is published and save yourself more than half the price you can buy the whole bundle of five courses now and, until the end of February 2019, using the coupon code LAUNCH25 will get you 25% off the early bird price of £99.
January 1st is an arbitrary day to be the first day of the year. As a former public servant in the UK I would also see 1 April as the first day of the financial year. Several years ago I wrote a post explaining why the UK tax year starts on 6 April (which itself refers to the first day of the year traditionally being 25th March, “Conception Day”). Anyway, 1 January is the conventional start of the year in terms of the change from 2018 to 2019 (CE, not AD) and the extended break I had over the Christmas and new year period has given me a chance to think about this blog.Read More
Last 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.
6 Do not mix up time periods in a work sheet
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.
7 Do not use named ranges
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.
8 Do not hide rows or columns
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.
9 Do not use macros
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.
10 Hide the gridlines
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