The Home Page · The Integral Worm · My Resume · My Show Car · My White Papers · Organizations I Belong To
Human Computer Interaction (IFSM303) · Web Information Architecture (IFSM387) · Database Project Overview (IFSM420) · Artificial Intelligence (IFSM427)
Information Systems and Security (IFSM430) · Information Systems Analysis & Design (IFSM436) · Project Management (IFSM438)
Legal Aspects of Information Systems (IFSM474) · Enterprise Network System Design (IFSM498)
Project Proposal
There are many organizations that are so small, that they do not need specific crews for varying day-to-day functions. However, these organizations still need a relatively large number of employees to perform those tasks and rotate between tasks. Hence, it becomes necessary for a manager to select who is to perform what task(s) or how many employees are to work at a position or station from an eight-hour shift or less.
Problem
Large bookstores that have a large number of full time personnel that perform expected day-to-day duties. These bookstores have requirements that shift on regular basis, obligating management to constantly shuffle where the personnel must be.
As an example, some days are busy and require a large number of people working at the cash registers and walking the floor, serving customers. While another day would require the same people to be in the backroom sorting and receiving or in the café. Some days of the week have higher customer traffic, while other days have less. In the later case there is other important work that must be delegated. A manager often has a limited number of employees working under him or her, and cannot always staff the optimal amount of employees that would be required at each station. Rather they must choose what takes precedence at the moment. As a example, on a busy day the majority employees could work at the registers while the next day employees are tasked with catching up on the work that had piled up in the backroom from the day before. A large bookstore is open seven days a week with groups of employees taking off two days at time, on staggered days in order to maintain coverage of the sales floor during hours of operation. The manager may not always know who is available, and of them, who would serve best at a particular station. A manager does not always know which stations should be focussed on for the greatest efficiency, and who is the best employee at which particular station. Therefore, the manning of workstations can become unbalanced. Because the number of employees available so few, employees are forced to rush and can only provide so much labor, or there may be too many employees who are idle being unable assist with a function that is already being done. Also it is possible to assign people that are not as experienced or as efficient at a set station. While there are many situations where placing less experienced employees on particular tasks would be inevitable, productivity would increase by having at least one person at a station that is experienced with performing the tasks at the particular station functions and could assist others should problems arise.
Solution
We propose creating a database that on any given day of the week would provide a manager with a recommendation as to how many people should be scheduled at each work station. In addition to the number of employees, the system would return the names of the employees who would be the most productive at each workstation. While it is possible that a workday situation could change, at least the decision support system would provide the manager with a recommendation to work with and assist in creating a work schedule. This system is especially useful if the manager is not familiar with all their employees, such as when a new manager is hired and is obligated to make scheduling decisions.
Information Needed
The database will hold the employees’ names and their corresponding Employee Number. The Employee Number is the unique identifier for the database system. This data set of employees represents the anticipated rotational changes on the various workstations on regular basis— managers and other employees with fixed tasks would not need to be entered. The decision support system and database would be a stand-alone system residing on the manager’s PC workstation and would not be directly linked to any main files of the business. The decision support system holds the days and times employees are available to work during the week or pay period. In addition to this information, the decision support system will provide recommendations on which employees would provide the best service at any particular workstation providing optimal coverage and productivity during operational hours. The system is designed to return a value of ‘None’ if there are no employees that will provide optimal coverage, and in the case of four or more, the system will return 'all' if the employee is highly experienced may staff any of the required work stations.
Work and Requirements Plan
The manager is responsible for entering the required information about the employees. The manager selects a day of the week that he requires a recommendation and the system returns a numbered list for each workstation and the best employees to provide optimal service. The manager would have to tailor the day, marking off any employees that were absent or required at a workstation. The manager has the ability to check off an employee that is not available for a particular rotation. A manager also has the flexibility to vary the parameters of each station. One day the manager may have a new cashier or may loose a cashier, changing the maximum number that can set. Another change may be a requirement mandated by upper management decreasing the minimum number of employees scheduled at the registers due to regular surges of customers to cashier stations. In most cases, there will be a fixed maximum number of stations that can be filled on a particular day. The manager may want to shut down that station, setting both the maximum and minimum employees to zero. It would take an unusual situation to create more than the maximum number of stations; henceforth, there is normally a set number of help desks or backroom jobs that need to be filled.
We propose designing the Employee Scheduling Decision Support System program using the VBA (Visual Basic for Applications) compiler provided with Microsoft Excel as most businesses are using Microsoft Office providing for maximum portability and flexibility.
Changes in the DSS Model
The original proposed design of the Decision Support System (DSS) was too specific. In order for the Decision Support System (DSS) to work on a large scale, it had to be made more general. Not only was the source code changed, but also the layout of the spreadsheet that the macros were working with in the original model. The code was adjusted so that the user would be able to select the day of the week and reset so that the old data would not interfere with a new query. The answer grid now resets for each use so that the previous information will not confuse the solver when it is trying to perform calculations based on new or different data.
The spreadsheet for the employees was simplified by converting the listed skills to ones and zeros. A one is used if the employee has the required skill under the column name or a zero if they do not have the skill. This made it much easier for the program to query the data, rather than having to translate the words into ones and zeros. It was necessary to translate the skill names so that the solver program would work with them after they have been sent to the grid. By changing the model from a list of skills to a grid proved to be helpful to the user by simplifying the input operation. If the user had to add or manipulate several employee entries, then the user would just have to place a set of ones in the employee grid, rather than have to write out the different sets of skills repeatedly. The user would still need to be able access the sheet that lists the employees, so the user could add new staff members to the rotation or edit the existing employees. The user would also require the ability to edit the expected demand at each station, although it is unlikely that the user would have to alter the maximum number of hours that each employee could work.
The DSS also provides the user with the option to choose which day of the week they want to perform the query, which was a fixed factor in the original model, (this was to ensure that the model could work). There were no command buttons in the original model, only the macros that had to be used manually and the solver had to be selected from the pull down menu to be operated manually. Both of these functions have been automated in the DSS when the user selects the correct options for them. The macros that are currently in the model are simpler. The modification pf the spreadsheet layout made it possible for that part of the code to be less complicated. The different steps of the code (searching for the employees, then sending them to the first grid so that they can be used in the solver) are now contained in one module, rather than making the spreadsheet look at two modules to get the processes it needed to use. Source code that did not appear in the first model, was written to work with and help the interface so the user could navigate the DSS and would not have to work with hardwired sets of code. These sets of code were placed in separate sections as needed. Overall, the amount of coding increased, although the individual sections in the module are smaller and are more contained.
The DSS now separates the information for the final result, placing the list of available employees onto one sheet above the number of hours that they should work. The model was working with a fixed number of employees so it could afford to lump the results together, and place them on the same sheet that the calculations were put on (the first grid in the original model). It was necessary to add a grid that the solver would use to calculate the answer, but including it on the results page may lead to user confusion on what answers the system was returning. This way the employee looks at the detailed information about the people who are available to work, and views the chart that would determine how they could best work with the people and hours available to them. The final results were massaged to make them easier to read, the grid is now labeled and colored so the user can understand what part of the information they are looking at, thereby reducing user frustration.
Justification and Description
The purpose of the DSS is to receive a recommendation for how many hours each available employee should spend at each station. This is to maximize deployment of the employee skills and to meet the demand at each station on a daily basis without making any of the employees work more hours than they are legally permitted. The DSS does this by taking a list of the employees that will be rotated among the workstations, along with a list of their skills and the days that they are not able to work. When the user begins the query, the DSS searches this list and selects the employees that are available on that particular day. From the list of available employees it takes the set of ones and zeros that summarize their skills and runs the solver program on the list. The solver is set to assign each of the employees the maximum number of hours that can be placed under each skill while trying to meet the demand set at each workstation without going over the eight hour work day constraint (placing that restraint first over the hours that are needed at each station). The DSS then provides a list of the employees and produces an hours report in the form of a grid.
At the beginning, the DSS provides a brief explanation of what it is and what it does for the user. Next, it presents the user with the options that must be selected for the system to function. There are options that are assumed for the user, such as the user will be inquiring about a day of the week, or that the user will want to edit or update the employee information spreadsheet. Last, the system provides finer details about what are the skills of the employees or the hours that are demanded at each station. The user can start the main query the DSS was designed for and adjust the numbers that influence it, mainly the hours that are demanded for each station.
The DSS provides the answer as a model, by presenting a list of the employees that are available that day, and a grid that shows how many hours each employee should work at each station. There is no need to change the grid layout in the answer from the model as it lets the user see all the information about the number of hours needed to be given by each employee. In the grid the user can see the different hours in relation to each other, providing the user with a very rough version of a schedule that they can use to create the posted schedule.
As previously stated, when the user selects the option to update the employee information they are presented with a set of ones and zeroes representing the employees’ skills instead of checkboxes or a form next to the names and numbers of the employees. The reason for this is creating a set of checkboxes next to the current entries could limit the number of employee spaces that would be used. Making boxes would have over-complicated the source code required. The purpose of the digital decision grid (zeros and ones) was to reduce excess coding that posed a problem in the original model. The user would have to know to put in a one or zero under each skill. However, this would allow the employee list to be more flexible in its size and maximize the speed that code would execute and run though the data. Knowing that the user will have to initially enter the zero and one coding for each employees skill set should not pose a problem in the way of the user understanding how to use the DSS. Appearance and functionality are practically the same as clicking checkboxes. It should not be difficult for a first-time user to understand what are the requirements for the skill set grid and how to use them.
Using the employee information list is explained in the user manual. In addition, the user manual explains the basic user options of the DSS and what they are expected to enter into the section requiring the number of hours demanded at each station. The main function of the DSS has been designed so that the user may start a query and produce a grid with the required result simplistically.
The DSS was not intended for utilization for a large number of employees, as would be the situation in a medium sized store. In order to save user time, the system does not require a large amount of effort to input and update. After the first use where the bulk of employees are added and the hours demanded at each station are decided, some time will have to be spent setting up the DSS the first time it is used. This is especially true if there is a relatively large number of staff to be rotated among the workstations. A large number would be a staff of 20 employees attending to the workstations in a bookstore, which is highly unlikely, but still plausible, depending on the demand imposed.
This DSS also allows the user to test future situations. The options are intentionally set up to be general, so the user can enter hypothetical situations, see the distribution of employees, and compare how much of the demand placed by each workstation is being met. Future employees could be added to see how they would affect the way the hours are assigned or removed to determine what would be taken away from the hours that could be given. The demand could be adjusted to see how changing the store's hours or employee work times would change. The store's hours would directly relate to how many hours the registers and the café would be open, although most stores do not keep their café open for the entire period the store is open. The employee work times are direct function of backroom staffing hours because there is always work that needs to be done during operation hours, whether the store is open for customers or not.
The DSS allows the user compare and contrast the results of different staffing decisions. The number of hours that the store provides generally would not change much during the week, although it might on weekends, holidays and during promotional events. The user would also have to consider the influence of 'busy' days where the hours demanded would be more than normal. The user can better determine the days off that should be assigned to the staff members by testing different possible schedules. The DSS does this by showing the results produced from different combinations of employees in order to meet the demand required by each station. This is also an informal indicator of employee skills since the DSS uses the skills of available employees to decide the assignments, although this is not the main purpose of the system. It is not difficult to determine if a workstation lacks proper coverage because one or more employees are not trained to handle it. Those employees can be rotated so that their skill sets are deployed. These employees could also be cross-trained in order to fill in the 'skill gap' shown by the schedule. Employees can also be rotated or trained if the DSS is keeping any one employee at one position for extended periods of time.
The Home Page · The Integral Worm · My Resume · My Show Car · My White Papers · Organizations I Belong To
Contact Me · FAQ · Useful Links