They see me rolling

Every year end we set about rolling over to a new financial year, creating a new set of files and folders and more often than not find that we’ve got an awful lot to do to switch from year to year.

Relax don’t do it.

When you come to creating all these new files this year, don’t just do the same thing as you did last year. Try and make the files more dynamic and make them less of a time-suck for the future. Every file/report/spreadsheet is different but there are ways in which they can all be improved with a few changes in the way you develop them.

1) Variables

In a spreadsheet you can give a certain cell or range or formula a name. Go to the formulas tab and press define name then you can select the cell, range or type a formula which you want to save for use anywhere else in the spreadsheet. You could even just type 2018 and call it finyear and then whenever you want to refer to the current financial year you can just type finyear in your formula anywhere in the spreadsheet, even in a macro, and it’ll mean 2018.

A good example where this comes useful is when you have used the financial year dozens of times in your spreadsheet and you want to change the financial year. Rather than go through every formula and change the figure for the financial year, you can just go to the name manager under the formulas tab and select finyear and change the value to be something else like 2019 and should you have used your finyear variable in your formulas, everywhere where it appears will now mean 2019 instead of 2018.

You can achieve the same affect by simply having a lookup sheet or cell or range in which you refer to throughout your spreadsheet so you only have to change one part in order to change the whole spreadsheet.

In a BI report for any reporting system such as Discoverer, ESR BI, Business Objects, Cognos etc. you can set up variables to help you achieve exactly the same outcomes as described for the spreadsheet. No longer should you ever have to have a report called Transaction Report 2017, Transaction Report 2018, Transaction Report 2019 and have to open each of them to be able to look at transactions for that particular year. Create reports with a good set of parameters that can be changed by the end user so they can see any financial year, period, cost centre, supplier etc. that they want. Matching some of the parameters (filters back in spreadsheet lingo) back to custom variables within your reports can make a massive difference in the amount of reports which you use and maintain.

You should aim to not be using more than 1 report for a certain set of data (e.g. 1 for transactional data, 1 for budget tracking, not 5 reports for each division or one that focuses on cost centres and another that focuses on subjective/account codes). This improves consistency and efficiency as you only have to go to one place for everything you need on a certain topic and if something new needs adding then the developer only needs to add it once.

Next time: Frames of Reference – how to let existing reports do the work for you:

If you have any questions or want to discuss what’s featured in this blog, please feel free to contact me on @NHS_FFF