Open Access A real-time Excel-based scheduling solution for nursing staff reallocation
Outi Anneli Tuominen Assistant head nurse, Turku University Hospital, Turku, Finland
Heljä Lundgren-Laine Nurse director, Turku University Hospital, Turku, Finland
Wiveka Kauppila Nurse director, Turku University Hospital, Turku, Finland
Maija Hupli Senior lecturer, University of Turku, Turku, Finland
Sanna Salanterä Professor of clinical nursing science, University of Turku, Turku, Finland
This article describes the development and testing of an Excel-based scheduling solution for the flexible allocation and reallocation of nurses to cover sudden, unplanned absences among permanent nursing staff.
A quasi-experimental, one group, pre- and post-test study design was used (
During the use of the paper-based scheduling system, 121 absences were identified; during the use of the Excel-based system, 106 were identified. The main reasons for the use of flexible ‘floating‘ nurses were sick leave (
The Excel-based scheduling solution offered better support in obtaining substitute labour inside the organisation, with smaller employment costs. It also reduced the number of tasks ward managers had to carry out during the process of reallocating staff.
Nursing Management. 23, 6, 22-29. doi: 10.7748/nm.2016.e1516Correspondence
This article has been subject to double-blind review and has been checked for plagiarism using automated softwareConflict of interest
Received: 24 April 2016
Accepted: 06 July 2016
Published in print: 30 September 2016
NURSING RESOURCE allocation and reallocation is a daily challenge and a time-consuming task for nurse managers, who have to ensure safe staffing is maintained around the clock. Focusing on short-term scheduling is vital because understaffing interferes with patient care and has a negative effect on care outcomes (Aiken et al 2014, Ball et al 2014, Clark et al 2015).
The reallocation of nursing staff as a management process can be encapsulated in three aspects: sudden absences; allocation and reallocation of available staff; and the financial resources required to cover sudden absences. Sudden absences, such as sick leave, are the main reason for nurse understaffing (Schreuder et al 2011, Clark et al 2015).
The financial consequences of sick leave absences are obvious for employers, although it is difficult to make cross-country comparisons. For example, in the UK, 131 million days were lost due to sickness absence across the working population in 2013, with the highest sickness rate recorded in the health sector (Office for National Statistics 2014). In Finland, an employee has about 9.9 absences from work in 1 year and, on average, one absence day costs employers €300 (European Foundation for the Improvement of Living and Working Conditions 2010), or approximately £255.75. Reallocation may be necessary in health care for reasons other than sickness absence, for example due to unexpected high patient numbers or increased workloads (Fagerström et al 2014). Reallocation may also be necessary due to a lack of professional experience or expertise, rather than a lack of nursing staff.
The staffing resources available to cover sudden absences vary by organisation. The most common way to manage unstaffed shifts is to use supplemental nurses, such as agency or temporary staff (Adams et al 2015, Xue et al 2015), but this will depend on a hospital’s financial resources and is not a cost-effective solution (Hurst and Smith 2011, Adams et al 2015). Interestingly, Xue et al (2015) found that there were no significant differences between the costs of agency nurses and permanent nurses, if the permanent nurses carried out overtime to cover unstaffed shifts. For these reasons, this study concentrated on developing and testing an Excel-based scheduling solution for the use of an organisation’s available permanent nursing resources, float pool nurse allocation and floating nurse reallocation to cover sudden staff absences. Float pool nurses in Finland are hospital permanent nurses who are hired directly into float pools to cover sudden absences of nursing staff. Floating nurses are also hospital permanent nurses working in one particular unit, but who may be reassigned to another unit during their regular shift.
In this study, nurses were hired directly into float pools, which are cost effective and commonly used to cover unstaffed or short-staffed shifts (Dziuba-Ellis 2006, Overman et al 2014). Float pool nurses sometimes move to a different unit daily. Floating nurses are hired by individual units, but may occasionally be reassigned to other units (Strayer and Daignault-Cerullo 2008, Good and Bishop 2011).
Paper-based scheduling remains a common solution for allocating and reallocating float pool or floating nurses. The paper-based system is supplemented by phone and email communication, noticeboard messages and manual notification procedures, but it can be inefficient (Kulma and Springer 2006, Valentine et al 2008). Most other alternatives are web based. However, the authors’ literature overview did not identify any original research on web-based scheduling and only a few case reports described the use of such solutions in practice (Kulma and Springer 2006, Ellerbe 2007, Valentine et al 2008).
Ward managers devote considerable working hours and tasks to nursing staff reallocation, but the number of tasks seems to decrease when a web-based scheduling system is used, rather than paper (Kulma and Springer 2006, Valentine et al 2008). However, earlier case reports did not describe how the reduction of ward managers’ working time was measured. In addition, while a ward manager’s total working time has been studied, the time spent on specific activities in terms of human resource reallocation has not been analysed (Arman et al 2009, Surakka 2008).
This study aimed to develop and test an Excel-based scheduling solution for float pool nurse allocation and floating nurse reallocation to cover sudden absences among permanent nurses. In addition, the object was to evaluate the number of ward managers’ tasks dedicated to human resource reallocation. It aimed to answer the following research questions:
» What tasks do ward managers need to undertake in the process of reallocating nursing staff as substitute labour when using a paper-based system and when using Excel-based scheduling?
» How does the new Excel-based scheduling solution differ from the paper-based system in obtaining substitute labour?
» What are the financial implications of using the Excel-based scheduling solution compared with a paper-based system?
Research ethics were considered at each stage of the study. All ward managers in the study department had an equal opportunity to participate. Participation was voluntary and participants were able to drop out at any time, without giving a reason. They were informed of their rights in a cover letter attached to the survey. The hospital gave ethical approval for the study, based on the official study application protocol.
Sample and study design
We used a quasi-experimental, one group, pre-and post-test study design (Box 1) with total sampling. Participants (n=17) were selected purposefully by including all ward managers (n=8) and assistant ward managers (n=9) from one university hospital department.
Study design for the reallocation of nursing staff
The study aimed to develop and test an Excel-based scheduling solution for allocation of float pool nurses and reallocation of floating nursed to cover the sudden absences of permanent nursing staff. In addition, it aimed to evaluate the number of tasks dedicated to human resource reallocation by ward managers.
The research questions
» What tasks do ward managers need to undertake to obtain substitute labour using a paper-based and an Excel-based scheduling system for nursing staff reallocation?
» How does the new Excel-based solution differ from the paper-based system in obtaining substitute labour?
» What are the financial implications of using the Excel-based solution compared with using the paper-based system?
The study department included 212 permanent registered nurses (RNs) and 23 practical nurses (equivalent to enrolled nurses in the UK) working across 9 units. Seven units operated in three shifts, 24/7. In addition, a total of 18 permanent float pool nurses worked in this department.
All the units in the department used a paper-based scheduling system for the daily allocation of float pool nurses and reallocation of floating nurses.
The pre-test and post-test data were collected between November 2012 and July 2013.
Developing an Excel-based solution
There was an identified need to streamline the rescheduling process and reduce the time required to allocate float pool nurses and reallocate floating nurses.
There were no known IT-based solutions for this in Finland. Following the literature review, specific criteria for streamlining the rescheduling process were devised, as follows:
» The process should be easily integrated in the organisation’s HR systems.
» It should enable the reservation of floating nurses and float pool nurses in the same solution.
» It should be low cost and, if possible, offer cost savings.
» It should be usable by nurses outside office hours.
» It should provide data about the scheduling of float pool nurses and rescheduling of floating nurses.
The paper-based system as a baseline
At the outset of the study we defined a baseline for resource reallocation: all the units in the department used a paper-based system. All available shifts of float pool nurses were reserved from a printed scheduling list.
The ward managers had to walk to another unit to see the list, which was compiled by the ward manager of the float pool nurses. If there were no available float pool nurses to cover a sudden absence, ward managers emailed or telephoned other unit ward managers to ask if they could provide a floating nurse or release one. It should be noted that supplemental nurses were rarely used in the department studied because of the associated cost.
This system had its challenges. Ward managers did not necessarily read their emails immediately, resulting in delayed responses. Other ward managers would then cover absences by either granting overtime or organising a sudden shift exchange.
After completing this rescheduling task, the manager might then receive a reply about the availability of a float pool nurse from another ward. This was time consuming and often caused confusion.
In an attempt to improve the process, ward managers experimented with daily meetings, but this was also seen as difficult and time consuming. Even if the process of rescheduling happened during office hours, they had difficulties covering sudden absences outside office hours, such as at weekends.
The Excel-based solution
The development of the reallocation tool for managing float pool and floating nurses included a Microsoft SQL server, a database management system with the primary function of storing and retrieving data as requested by other software applications. Excel was the user interface. This development was undertaken through a collaboration between the University of Turku’s department of nursing science, Turku University Hospital and the company Fujitsu Finland Ltd. The development process took 6 months, with a total cost of €27,000 (approximately £23,000).
The tool does not incur a license fee and the organisation has allocated it a budget to cover possible update requirements.
The Excel-based scheduling solution was easily integrated in the organisation’s HR systems, enabling the reservation of floating and float pool nurses based on real-time workforce demands. Following the principles of open access, all information relating to resource management was available to the entire organisation, including managers and staff. Before the intervention, every participating staff member received a 90-minute training session with one of the researchers (OT), with further support available as needed. Users of the Excel-based scheduling solution were also offered technical support from the researcher if necessary.
Access to the new scheduling system was provided through the hospital intranet to ward managers, assistant ward managers and nursing staff. Administrators, in this case the ward managers, had permission to change shifts, working hours and, if necessary, modify the data reserves – for example if the reason for the reservation was entered incorrectly. To enable the reservation of a float pool nurse, the ward manager administrator entered their planned shifts in the tool in 3-week cycles.
End users (ward managers, assistant ward managers and nursing staff) could make or cancel reservations of float pool nurses and increase or reduce the number of available floating nurses. If no float pool nurses or floating nurses were available, end users could make additional requests for floating nurses.
The new system has an additional feature that enables nursing staff to write short patient reports to support this work and data collection. Reports may cover the number of patients, number of nursing staff and the required care intensity, using a scale of 1:3. This information was used by ward managers to support decisions about staff allocation or reallocation. Float pool nurses get information on the unit where they will be working when they log on to the hospital intranet.
The Excel-based solution enabled data collection on a range of information, including the reasons for sudden staff absences, reservation of float pool or floating nurses, number of floating shifts, unplanned exchanges of shifts relating to unstaffed shifts, and the number of staff employed as substitute labour. When necessary, the solution also allowed unfilled shifts to be split between units, for example in 4-hour blocks.
The two scheduling systems as a process are presented in Figure 1.
After every reported sudden absence, ward managers completed the ward managers’ reallocation tasks survey to report the number of tasks required to cover the absence – this included phone calls, emails, meetings and various notification procedures for obtaining substitute labour. Ward managers noted on the survey who the target person was to fill the absence – a nurse on the unit, a float pool nurse, another unit’s nurse, the ward manager of float pool nurses, the ward manager of another unit, or the director of nursing. The reasons for the absence and whether or not the attempted staff replacement was successful were also noted.
In addition, we counted the actualised floating shifts before and after the intervention over 4 months. During the use of the paper-based system, ward managers counted the actualised floating shifts manually, while during the use of the Excel-based scheduling system, the actualised floating shifts were counted automatically by the system.
Data were analysed using SPSS Statistics 20, an IBM software package used for statistical analysis. The number of ward manager tasks were analysed before and after the intervention. The results of the survey were carried out with non-parametric tests because of the skewed distribution of data. The data contains plenty of zero values due to the nature of the survey. The Mann-Whitney U-test was used to analyse ward manager tasks. Cross-tabulation (chi-square) was used to compare the results of the two systems; unscheduled shifts, unplanned shift changes, unplanned long working days, understaffed shifts and the number of substitute nurses were evaluated. Descriptive statistics were presented as a percentage, mean and standard deviation.
The financial outcomes of each successfully reallocated unstaffed shift were calculated by taking the average hourly salary for an external nurse (€20.12 (£17.15), including social security payments), and calculating the cost of a single nursing shift (8 hours × €20.12 (£17.15) = 160.96 (£137.17)). Each shift that was staffed by a floating nurse saved €161 (£137.20) because this avoided the need to hire an external nurse, which would incur additional costs.
The number of sudden absences by nursing staff were identified during two 4-week data collection periods (pre- and post-test). During the use of the paper-based system, 121 absences were identified and during the use of the Excel-based system there were 106 (Table 2).
The main reasons for the substitution of floating nurses were sick leave (n=66) and workload (n=31). Other reasons (n=29) included patient transfers to other hospitals, scheduling errors and the start or end of employment. The competence level of a nurse was listed once as a reason for substitution.
Ward manager tasks during rescheduling
At baseline, the ward manager tasks for covering absences included phone calls, emails, meetings and sending out various notifications. The number of tasks decreased significantly (P=0.022) when Excel-based scheduling was introduced (Table 2).
Phone calls were used as part of both scheduling systems (Table 2), but the Excel-based system reduced the number of calls made and the difference was statistically significant (P=0.013). During the use of the paper-based scheduling system, up to five phone calls were needed to obtain one substitute nurse, while the Excel-based solution never required more than three. Most of the phone calls were between unit ward managers.
Emails were less commonly used to arrange substitute labour. During the period of data collection, 64 emails were sent during the use of the paper-based system, while just 13 emails were sent when using the Excel-based system (P=0.023). Under the paper-based system, a maximum of 7 emails were sent to obtain one substitute member of staff, as opposed to 3 or fewer under the Excel-based system. Most of the emails were sent between unit ward managers and the float pool nurse ward manager (Table 2).
Conversations were used to acquire substitute staff during the use of both scheduling systems, but with the adoption of the Excel-based solution their number was also reduced (from 132 to 66; P=0.090). This difference is not statistically significant.
A maximum of 6 conversations took place to obtain one substitute member of staff during the use of the paper-based scheduling system, but never more than 3 under the Excel system. Unlike the phone calls and emails between the unit ward managers, conversations were mainly with unit nurses. Table 2 provides more information.
During implementation of the Excel-based system, the decrease in the number of unstaffed shifts was statistically significant (P=0.004). In addition to ward managers and assistant ward managers, nurses started to use the system. During the post-test follow-up time for the Excel-based scheduling solution, nurses made 19 reservations, 10 of which were made outside office hours.
There were no significant changes to the unplanned exchanges of nurses’ planned shifts (P=0.236) or the number of long working days (P=0.766) used to address the sudden labour needs. Table 3 provides further information.
The cost of actualised floating shifts was evaluated before the implementation of Excel-based scheduling (between November 2012 and February 2013) and after (between April 2013 and July 2013).
The potential financial implications were estimated using average hourly salary and the typical 8-hour shift of a nurse in the study department. When an external nurse is hired to cover sudden absences, hourly salary increases by 9% because external staff costs are added to the annual compensation paid to existing nurses (Table 4).
The difference in cost savings between the two 4-month studies was €9,338 (£7,957) Table 4). If external labour or agency nurses had been hired for every actualised floating shift, the estimated costs for the hospital would have been much higher (Adams et al 2015, Hurst and Smith 2011). This calculation does not take into account any bonuses paid to individual nurses, which could be awarded on the basis of the number of hours worked or level of professional experience.
The estimated cost savings under the Excel-based system (€9,338 (£7,957) over a 4-month period) are equivalent to 3 months’ salary for one nurse. In addition, the organisation was able to make effective use of existing labour as the need arose.
The estimated savings are approximate and illustrative of potential rather than actual savings. However, this illustrates that it is possible to create a system that allows an organisation to collectively and effectively manage the reallocation of its nursing staff.
The results confirm that the use of an Excel-based scheduling solution can improve the process of nursing staff reallocation, by reducing the number of tasks performed by ward managers to cover staff shortages and by increasing the reallocation of floating nurses. It can also create staffing cost savings for the hospital.
Based on the study results, implementation of an Excel scheduling system reduced the number of tasks carried out by ward managers in the process of nursing staff reallocation (P=0.022). This outcome provides more information about the content of the ward managers’ work. Arman et al (2009) and Surakka (2008) stipulate that staffing allocation is part of a ward manager’s role, and the number of tasks and working hours required to process nursing staff reallocation are considerable (Valentine et al 2008, Kulma and Springer 2006). This study shows how the number of tasks is reduced when switching from paper-based scheduling to an Excel-based solution.
To the authors’ knowledge, this study is the only research that has described the specific tasks of nursing staff allocation and reallocation. In future, it will be possible to measure the time that ward managers spend on the nursing staff reallocation process and the economic effects of the associated tasks.
The use of the Excel-based scheduling solution decreased (P=0.004) the number of unscheduled shifts (n=33) for nursing staff reallocation, compared with baseline (n=66). In this study, there were no differences in overtime worked by nurses (P=0.766) before and after the introduction of the system.
If permanent nurses undertake overtime to cover unstaffed shifts, there are no savings for the organisation, compared with the use of agency nurses (Xue et al 2015). The efficient use of available permanent staff could, in some cases, be a way to reduce nursing overtime if the reallocation of unstaffed shifts is properly structured.
The authors believe this study was one of the first to evaluate the costs and/or savings of permanent nursing staff reallocation in addition to case reports (Kulma and Springer 2006, Ellerbe 2007, Valentine et al 2008).
The estimated average daily salary of an external nurse is €161 (£137.20), hence, during the 4-month follow-up period, the estimated cost savings totalled €9,338 (£7,957). Every reallocated unscheduled shift covered by permanent staff will bring salary cost savings for an organisation.
Following the study, the department has adopted the Excel-based scheduling system. Based on the log information, a total of 395 floating shifts were actualised in 2014. Over one year, projected average cost savings would be €63,595 (£54,204). A more thorough cost-effectiveness analysis, a larger data set and a longer study collection time are needed to verify the cost-effectiveness of the Excel scheduling system.
The use of float pool nurses or floating nurses will not solve all the challenges in the reallocation of nursing staff. However, reallocation is a part of a ward manager’s daily work and organisations should investigate how the process can be improved. The use of the Excel-based scheduling solution should support the daily work of the ward manager by minimising the time necessary to carry out the work and delivering the best outcomes.
As with all study methods, the quasi-experimental, one group, pre-test post-test study design has its limitations (Harris et al 2006). Randomisation was not used in our study because of the small target group (n=17). However, the number of absences (n=227) was fairly large. The study design did not have a control group because the pre-test exemplified normal practice and was used as a comparison for the intervention results.
The survey used in this study was developed specifically for this research. It was easy to use and enabled us to obtain the answers to the research questions. It did not include any socio-demographic variables concerning respondents, which could be included in future studies.
Because of the small target group our results should only be considered as indicative. However, the results are encouraging. There is also a need to study the nurses’ experience and patient outcomes that were not included in this study.
Based on the study findings, Excel-based scheduling is a promising solution for the reallocation of nursing resources in hospital settings. Our results indicate that the Excel-based solution can reduce the number of ward manager tasks and offer better support when obtaining substitute labour, with smaller employment costs and time savings. To ensure success and adoption, it is necessary to provide sufficient training during the implementation phase of new electronic systems.
Although these results are positive, larger studies are needed to evaluate and validate our findings by including different hospital units, larger sample sizes and a longer follow-up period.
Finally, as is well known, floating of nurses can cause staff dissatisfaction and stress. When implementing human resource reallocation in a healthcare setting, an organisation should also collect feedback from nurses about their experiences of working under any new system to support implementation of the practice as a permanent model.
Adams J, Kaplow R, Dominy J et al (2015) Beyond a Band-Aid® approach: an internal agency solution to nurse staffing. Nursing Economics. 33, 1, 51-58.
Aiken LH, Sloane DM, Bruyneel L et al (2014) Nurse staffing and education and hospital mortality in nine European countries: a retrospective observational study. Lancet. 383, 9931, 1824-1830.
Arman R, Dellve L, Wikström E et al (2009) What health care managers do: applying Mintzberg’s structured observation method. Journal of Nursing Management. 17, 6, 718-729.
Ball J, Murrells T, Rafferty A et al (2014) ‘Care left undone’ during nursing shifts: associations with workload and perceived quality of care. BMJ Quality and Safety. 23, 2, 116-125.
Clark A, Moule P, Topping A et al (2015) Rescheduling nursing shifts: scoping the challenge and examining the potential of mathematical model based tools. Journal of Nursing Management. 23, 4, 411-420.
Dziuba-Ellis J (2006) Float pools and resource teams: a review of the literature. Journal of Nursing Care Quality. 21, 4, 352-359.
European Foundation for the Improvement of Living and Working Conditions (2010) Absence From Work. www.eurofound.europa.eu/observatories/eurwork/comparative-information/absence-from-work (Last accessed: 12 September 2016.)
Fagerström L, Lønning K, Andersen MH (2014) The RAFAELA system: a workforce planning tool for nurse staffing and human resource management. Nursing Management. 21, 2, 30-36.
Good E, Bishop P (2011) Willing to walk: a creative strategy to minimize stress related to floating. Journal of Nursing Administration. 41, 5, 231-234.
Harris AD, McGregor JC, Perencevich EN et al (2006) The use and interpretation of quasiexperimental studies in medical informatics. Journal of the American Medical Informatics Association. 13, 1, 16-23.
Hurst K, Smith A (2011) Temporary nursing staff – cost and quality issues. Journal of Advanced Nursing. 67, 2, 287-296.
Kulma M, Springer B (2006) Easing the bottom-line impact of staffing shortages: a case study in shift bidding. Healthcare Financial Management. 60, 4, 92-94.
Office for National Statistics (2014) Sickness Absence in the Labour Market: February 2014. http://www.ons.gov.uk/employmentandlabourmarket/peopleinwork/labourproductivity/articles/sicknessabsenceinthelabourmarket/2014-02-25 (Last accessed: 12 September 2016.)
Overman K, Hauver J, McKay J et al (2014) Maintaining competency for float nursing staff. Journal for Nurses in Professional Development. 30, 4, 204-208.
Schreuder JA, Roelen CA, Van Zweeden NF et al (2011) Leadership styles of nurse managers and registered sickness absence among their nursing staff. Health Care Management Review. 36, 1, 58-66.
Strayer N, Daignault-Cerullo EM (2008) Closed staffing: a staff nurse strategy. Critical Care Nurse. 28, 6, 51-57.
Surakka T (2008) The nurse manager’s work in the hospital environment during the 1990s and 2000s: responsibility, accountability and expertise in nursing leadership. Journal of Nursing Management. 16, 5, 525-534.
Valentine N, Hughes D, Douglas K (2008) Achieving effective staffing through a shared decision-making approach to open-shift management. Journal of Nursing Administration. 38, 7-8, 331-335.
Xue Y, Chappel AR, Freund DA et al (2015) Cost outcomes of supplemental nurse staffing in a large medical center. Journal of Nursing Care Quality. 30, 2, 130-137.
Improving the patient’s experience of cancer-related fatigue
Fatigue is a complex, multifactorial disorder with physical,...
What you need to know about running a shift
Many junior emergency department nurses are given no formal...
Improving health with acute liaison nursing
People with learning disabilities have increased health...
Mental health nurses on prescribing in Ireland
The study described here gauged perceptions of nurse...
Using change in nursing practice: a case study approach
THE CHANGE described in this paper took place in a large...