Abstract
This case involves actual data obtained from an interview with a partner, a manager and two associates at a firm located in Singapore, Alvarino (pseudonym). The firm is part of a global network of accountancy and business advisory firms. The network comprises of more than 100 independently owned and managed firms that straddle more than 100 geographical locations across the world. The case illustrates issues that Alvarino experienced in scheduling their staff for audit advisory engagement. As a service oriented and cost-conscious business, workforce scheduling is essential to help Alvarino’s management optimise its workforce allocation. The objective of this case is to create a data model that maps user and data requirements to optimise Alvarino’s workforce-scheduling processes.
Keywords
Background
Information systems (IS), in particular data model and database design are essential topics that accountancy students must master (Richardson et al., 2016; Romney and Steinbart, 2018). This is because well-implemented databases help companies control the performance of business processes and make better decisions (Chen and Hsu, 2007). Data modelling with the use of entity relationship (ER)/unified modelling language (UML) class diagrams provide developers the data details for data analytics and IS development. ER/UML class diagrams form the foundation in the development of databases. While it is essential for accountancy students to be equipped with database concept and design, teaching cases related to this area remain limited as they require formulation of data models, SQL queries and database designs (Harris and Vaught, 2008). Students also face challenges in developing data models from a given database as they need deep understanding of user data requirements and relationships between data (Kroenke et al., 2017).
This case is based on a real-world accountancy and business advisory firm in Singapore, Alvarino. Alvarino (Singapore) is part of a global network of accountancy and business advisory firms. The firm faced issues in managing their workforce scheduling manually on spreadsheets. Our teaching case context is beneficial to accountancy students, because the context (i.e. workforce scheduling) helps them to understand one of the core business processes in advisory firms for pursuing effectiveness and efficiency (Dodin, 1999). Furthermore, workforce scheduling is critical in many services organisations in controlling costs and satisfying customers and clients (see Burgy et al., 2019; Ernst et al., 2004; Ruiz-Torres et al., 2019).
We collected the data on audit jobs via personal interviews with one partner and two employees at Alvarino. We looked at the audit and assurance service of the firm in our teaching case. The interview was conducted to understand the workforce-scheduling issues, user and data requirements for the scheduling and how this scheduling is related to other business processes at Alvarino. The partner’s and employees’ names for this case have been pseudonymised to protect interviewee confidentiality.
This case provides students with an overview of Alvarino and its workforce-scheduling issues. They then take on the role of system analyst at Alvarino to document the data requirements and develop a data model using the requirements given at the end of the case.
The case
Overview of Alvarino
Alvarino is one of the world’s leading network of accountancy and business advisory firms. It is located in Singapore, with about 200–300 employees. This Singapore firm offers a similar range of services as that of the global Alvarino network. The network comprises of more than 100 independently owned and managed firms that straddle more than 100 geographical locations in Asia Pacific, North America, Latin America, Europe, Middle East and Africa. They employ more than 30,000 employees globally with experts across industry sectors and possess in-depth local knowledge. The firms in the network come together to share knowledge and resources, with a common goal of delivering exceptional service quality to meet their clients’ business and accounting needs.
The key services provided by Alvarino are audit and assurance, tax and specialist advisory services. Audit and assurance as the firm’s largest business line where the firm provides attestation to the integrity of financial reports prepared by the clients, in accordance to statutory and accounting standard requirements. The audit service focuses on risk assessment of the clients’ businesses and internal control processes. The assurance covers IS and controls of clients and forensic investigation. Alvarino provides additional value-added services such as training and updates of technical financial reporting standards and legislative changes. A quality audit service requires high independence, objectivity, ethical standards and strong technical knowledge in the auditors carrying out the audit.
Alvarino also provides tax advice to corporate and private clients. Corporate and international tax advice are provided to corporate clients for compliance and tax efficient structuring and financing, particularly when clients operate in various geographical locations and transfer pricing arises in international trade. The dimensions of tax advice include both direct and indirect tax. Alvarino’s third business line is specialist advisory services, which are categorised into corporate finance, corporate governance and risk management, IFRS (International Financial Reporting Standards) reporting, corporate recovery and insolvency, and legal services. Corporate finance activities include advisory and transaction support on sales and acquisitions, from pre-deal evaluation to post-deal integration. Corporate governance and risk-management services span a range of advice to strengthen client corporate governance, enterprise risk-management services, Sarbanes-Oxley Act compliance, internal audit services and information technology audit services. IFRS reporting services consist of training and assistance to non-audit clients to manage financial accounting standard conversion from preliminary assessment to implementation. For corporate recovery and insolvency services, Alvarino manages financial distress situations, which may involve restructuring, fraud investigation and personal insolvency cases. Alvarino also provides legal advice in cross-border acquisitions, restructuring of multinational groups or mergers of individual corporate entities, as part of an integrated solution together with tax and corporate finance advice.
The Alvarino network is led by the Chief Executive Officer (CEO) and the Chief Operating Officer (COO) and governed by an international board based in London, United Kingdom. The directors on the international board are senior partners drawn from member firms and elected to represent firms of varying sizes and regions. The board is responsible for setting the network’s overall strategies and priorities, and maintaining the values, standards and processes among members of the network.
The firm has recently implemented CaseWare, a vendor software system that assists in managing audit engagements. CaseWare contains features that enable the firm to manage risks and controls in the audit processes. However, the firm is not using CaseWare to schedule staff engagements. Thus, we worked with the firm on a system solution to schedule audit staff engagements.
Workforce-scheduling issues at Alvarino
Alvarino encountered challenges in scheduling audit staff engagements. As a service-oriented and cost-conscious business, workforce scheduling is essential for the firm to survive and thrive in the market. In the current process, the scheduling is done manually on spreadsheets. The scheduler would update the spreadsheet files manually when new information is available on the staff’s schedule, (e.g. staff leave, staff movements). These files needed to match the engagement budget, manpower and engagement timeline to available resources. Alvarino’s management felt that the current process was inefficient as the information could not be updated in a timely manner.
While Alvarino was aware that workforce scheduling is a critical process in accountancy and business advisory firms, its current scheduling process is unable to help Alvarino’s management optimise its workforce allocation. Workforce allocation is an integral part of advisory planning and control processes as it enhances the firm’s operational effectiveness and efficiency. Effective workforce scheduling would enable Alvarino to plan assurance projects’ timelines and satisfy client’s deadlines. Efficient scheduling allows Alvarino’s management to control costs through optimal workload scheduling of their advisors, employee over-time requirements and travel schedules.
Alvarino attempted to develop a dynamic and robust IS for workforce scheduling. The scheduling system aims to (1) allocate manpower usage efficiently and to eliminate or minimise the idle time of an unassigned staff, (2) allow multiple elements to be considered, for example, the engagement budget, team size and availability of resources to make the system more dynamic than the current spreadsheet and (3) generate relevant reports for decision-making related to resource allocation and budget.
Alvarino conducted a review of its internal business processes with a view of making improvements to the processes. Assurance engagement scheduling is an important area of the business and Alvarino has been seeking alternatives to their current scheduling system as part of its business process improvement. Assurance engagement scheduling is the job allocation platform for the assurance staff. It is an important part of audit planning as management will have to allocate manpower according to the audit job budget and ensure that sufficient manpower is scheduled to complete the job within the given deadline. Efficient scheduling will allow Alvarino to maximise resource usage and minimise cost by fully utilising all the available resources. The aim of the current scheduling system is to minimise the gap between job demand and manpower supply in the most efficient and effective manner. In addition, the system’s objective is to minimise idle time of an unassigned staff. The current issues faced by the manual scheduling system are as follows:
Alvarino is unable to optimise the manpower usage as it is difficult to track idling manpower on a daily basis via the spreadsheet. To identify idling manpower, the scheduler has to review every personnel’s schedule to check their availability. In this case, the scheduler must continuously update and retrieve the data from the spreadsheets. The process is, therefore, slow and inefficient. In addition, as the entire process is manual, the schedule updating is prone to error.
Staff scheduling is a dynamic process. To plan for a schedule, it requires the inputs of many elements such as the engagement budget, team size, resource availability and reporting deadline. In the current spreadsheet, the scheduler is unable to perform a real-time update to the master personnel schedule. Furthermore, the current process is highly dependent on the availability of the scheduler as other managers may not be familiar with the functions and formulae used in the spreadsheets to use it well.
The current staff scheduling process does not respond to last-minute requests well. The spreadsheet is unable to efficiently identify an idling resource. Hence, the scheduler faces difficulty in identifying available resources and matching them to any last-minute engagement request by the engagement team.
As the process is not automatically updated and requires extensive manual entry and processes, Alvarino’s management is unable to track changes to the spreadsheet immediately.
The current spreadsheet is not integrated to other systems as it is a standalone file. The schedule does not integrate with the existing staff’s leave, budgeting and timesheet systems. Therefore, information is sometimes outdated, and human errors occur when integrating information from different sources. As amendments made to the job schedules are not updated to job budgets automatically, job managers may not be informed of amendments in a timely manner.
The current scheduling system using a spreadsheet is unable to generate any user reports that are useful for managerial decision-making. For example, Alvarino’s management is not able to generate reports to compare budgeted versus actual time spent on engagement, and staff utilisation to assist in management decision-making as the spreadsheet is a static document.
Accessibility to the spreadsheet for scheduling purposes is limited, as currently users are unable to access the system when they are out of office.
The partner at Alvarino, Morgan (pseudonym) initiated the move to replace the current spreadsheet with a more robust, dynamic and flexible system. The system should allow for multiple inputs and generation of reports. The ultimate goal of the new system is to maximise resource allocation for audit staff engagement. Morgan invited systems analysts to discuss the system requirements. During the briefing sessions, the systems analysts documented the user’s requirements for the improved workforce-scheduling system. Morgan provided us with critical information such as the scheduler’s role, the data required in the scheduling system and the workflow that should be reflected in the system inputs and outputs. The information is essential for the analysts to document the use cases of the system. These use cases provide information on user requirements and can be used to validate the data model, the database design and the actual database implementation. Consequently, these use cases formed the foundation that enabled the analysts to design a data model and develop a database for the workforce-scheduling system to permit users to enter workforce-related data and to generate meaningful reports.
During the meeting with Morgan, a manager in charge of job scheduling, Michael and his two associates, Michelle and Maureen discussed the following entities, attributes and business rules for the workforce-scheduling data model as shown in Table 1.
Entity, attribute, business rules.
Morgan, Michelle and Maureen revealed the following relationships within the above entity groups:
A group has one or many partners, managers and associates. Each partner, manager and associate belongs only to one group. A group may work on several jobs. However, each job can only be assigned to one group.
A team has one or many associates, but associates can only belong to a team. A team has one manager. Similarly, a manager can only be involved in one team. Each associate belongs only to one team, but a team can have multiple associates. A team may work based on one or multiple job(s) and budget(s). Each job and budget can be managed only by a team.
A manager can work on one or multiple jobs. Each work can only be assigned to a manager. A manager also authorises the budget for each job. Each manager may authorise one or multiple budget(s). Each budget, however, can only be referred to one manager.
An associate may be assigned to one or many time slot(s) in scheduling. Each time slot in the scheduling may consist of one or multiple associates.
The budget may be allocated for one or multiple job(s), yet a particular job belongs to only one budget.
A job may be scheduled in one or multiple time slot(s). Each time slot can be referred to one or multiple jobs.
A client may request one or multiple jobs yet each job can only be referred to one client.
Data model development
Morgan requested system analysts at Alvarino to construct a data model (conceptual schema) for the workforce-scheduling system. Their role is to develop a structural physical model of a database. Identifying the structural model of the data, and the physical model of how data should be stored is important in designing a database management system as the model has to be able to record the data systematically from multiple business processes and events to allow users to manage and integrate a collection of data, further processed for multiple purposes (Gelinas et al., 2014).
A data model is the visualisation of how data should be stored and related to each other, for example, access rights to the systems, and what kind of data they require to complete particular events. Data models can be visualised via ER/UML class diagrams. An ER diagram depicts the logical relationships between entities such as departments, division, events, activities; provides visualisation of how the entities should be connected to each other; and what attributes are applicable to each entity. A UML class diagram has a similar purpose to an ER diagram. A UML class diagram is, however, primarily used for object-oriented software. While UML class diagram is largely used for software development, it can be used to develop logical models.
Suggested student tasks
Students assume the role of system analysts to help Alvarino construct a data model that can be depicted via ER/UML class diagrams. In order to create the data model, the analyst has to identify the relationships, primary keys and foreign keys according to the information given. A primary key must be unique and cannot contain null values, whereas a foreign key is the mirror of a primary key in another table to allow connections between two or more tables. The primary and foreign keys are provided in the above description, and the analyst must introduce the appropriate primary and foreign keys in the appropriate entities to facilitate relationships between those entities. The analyst must also show the cardinality (one or many) and modality (zero or one) of the relationships for the ER diagram. At the same time, for UML class diagrams, the analyst must show the multiplicity (0. . .*, 0. . .1, 1. . .1 and 1. . .*) of the relationships between entities. The development of the workforce-scheduling data model can be done using perpetual licenced or open-source modelling software.
Footnotes
Declaration of conflicting interests
The author(s) declared no potential conflicts of interest with respect to the research, authorship, and/or publication of this article.
Funding
The author(s) received no financial support for the research, authorship, and/or publication of this article.
