Why workforce planning solutions in Excel don’t work in the long run
Using Microsoft Excel for workforce planning is by far the most common solution when a higher education institution has no professional workforce planning system. Everything from simple Excel files with 2-3 tabs to extremely complex solutions with linked Excel documents with over 50 tabs. Creating Excel solutions for workforce planning is a completely natural thing to do and is often the only way to go when an individual planner wants to facilitate workforce planning and make it more efficient. In a larger context, however, these Excel solutions can become a major problem. Let me explain why.
Dependent on one person
Excel sheets are often developed by an individual who is knowledgeable in both workforce planning and Excel. When that person leaves the organisation or changes job, it is often extremely difficult for other people to familiarise themselves with and maintain the Excel sheets. In a worst-case scenario, the entire workforce planning is lost. When new planners take over, often the easiest and most secure thing is to simply start again from the beginning and develop your own solution in Excel. In other words, back to square one.
Risk of multiple versions of the truth
With an Excel solution, all planners start out working on the same Excel sheet and the same information. But it probably won’t stay that way. Every time a change is made, there is a risk that the wrong Excel sheet will be updated, that the change will not be inserted in the overall Excel sheet or that someone else will make a change and save it in an older version of the Excel sheet. This results in multiple versions where you end up not knowing which information is up to date.
Difficulty in maintaining information security
It is very easy to share the information in an Excel sheet. All it requires is for the Excel sheet to be sent by email. Given that workforce planning often contains sensitive information about people’s absences, employment details and salaries, this is critical from a security point of view. A workforce planning system, on the other hand, has separate logins and permission systems to control who receives access to what information. This solution is therefore far more secure.
Challenge of complying with GDPR
GDPR – The General Data Protection Regulation – is a regulation that applies throughout the EU and aims to create a uniform, equal level of protection for personal data. If there are defects in a company’s processing of personal data, it may be forced to pay what is referred to as an administrative penalty of up to EUR 20 million or four per cent of its turnover.
Complying with all GDPR requirements correctly and securely is particularly difficult with Excel-based solutions and ensuring that the information is not disseminated to unauthorised persons and that it is saved and managed securely becomes a challenge for the management.
Not possible to log changes
Another common requirement for workforce planning solutions is logging of changes to personal data, in other words the system must provide a list of who changed what and when. This requirement is not fulfilled with an Excel solution.
The human factor gives rise to errors in formulas
Errors in calculation formulas in Excel are also extremely common.* When Excel sheets become large and complex, it is easy for the human factor to lead to errors in calculation formulas, for example forgetting to include the contents of certain cells in the formula. There is then a risk that decision-makers may receive incorrect supporting data and draw incorrect conclusions and make incorrect decisions for the organisation on the basis of that data.
There is a risk that the workforce planning will be incorrect
Calculating workforce planning in a correct manner that complies with the regulations of the working hours agreement is extremely complex, particularly when care of a sick child, sick leave, leave of absence, parental leave and other changes in the scope of a person’s post during the year are taken into consideration. This, along with rules for calculating continuous professional development time, supporting data for monthly account-coding for salaries and monthly utilisation rates, means that the Excel sheet must always be based on estimates and simplifications in order for workforce planning to be possible at all.
When the planners are forced to reinvent the wheel in their own Excel workforce planning solutions, it is obvious that errors and faults will easily occur.
Reporting becomes excessively complex
It is difficult enough to keep track of all Excel sheets and make them work together, but trying to export relevant data for different kinds of reports at the same time is an excessively complex – if not impossible – task. The more complex the Excel solution, the more difficult reporting will be.
No overall visualisation
Of course some users can create graphs and charts from Excel sheets, but compiling complex information is frustrating and time-consuming. For example, two views that are easy to display in a professional workforce planning system but that are very difficult to compile in Excel are utilisation rates per person per month and a graphical representation of when courses and activities occur during the term.
Difficult to scale up
As long as only one or a few people are working on workforce planning, an Excel solution can initially work reasonably well. But as more and more planners, and particularly course coordinators, become involved in the planning, it becomes more complicated and the risk of errors in formulas and dependencies between Excel sheets increases.
So there are many convincing reasons to stop using the home-made Excel solution and transfer to a professional workforce planning system. A professional workforce planning system provides many benefits for higher education institutions. If you are curious about what these are, you can click on the link for a description of the main benefits of a workforce planning system designed for higher education institutions. If you would like tips on what requirements you should expect a workforce management tool to meet, click on the link to read the article. If you want to learn more about how the workforce planning process can be organised at a higher education institution, we have also produced an article on that topic.
According to a study by the University of Hawaii, 88 per cent of all Excel sheets contain some kind of error.
About the author
Jens Apelgren has over ten years’ experience in workforce planning at higher education institutions and has personally introduced workforce planning systems at 18 different institutions. Jens is CEO of Retendo AB, which offers systems to streamline administration for both higher education institutions and project-oriented companies. Jens is closely involved in the development of Retendo’s workforce planning system.