Eljay School A large Australian School has recently opened an online education division called Eljay. The school provides online education services to students from around the world. In an effort to improve the services offered to students, a job management system was developed to improve the responses provided to student queries. At the end of every week, a manager from the administration team archives an excel file that contains data about student queries for that week. The manager of the administration team believes that the data within this spreadsheet would be valuable for business decision making. However, employees within the team lack the technical knowledge to analyse and interpret this data. Subsequently, the manager is unsure how to utilise this data to gain insights on how to improve response times and outcomes for students. The administration team manager decided to hire you to assist in analysing and interpreting the data. The table below shows a data file that was archived containing information on student queries for one week. Table 1 Job Student ID Student Name Date Opened Date Closed Hours Job Type Job Priority Job Status Code (d/mm/yyyy h:mm) (d/mm/yyyy h:mm) Charged 0001 0009990t Charles Brody 01/08/2020 10:30:49 01/08/2020 12:30:49 2 T Important Closed 0002 0001090t John Lucas 02/08/2020 15:23:26 2/08/2020 16:23:26 Normal Closed 0003 0009989t Adam Smith 02/08/2020 18:21:46 04/08/2020 16:20:23 Teaching Less Important Closed 0004 0001 100t Maree Sarah 03/08/2020 09:36:21 05/08/2020 12:20:23 Teaching Normal Closed 0005 0009988t Eve Bonny 04/08/2020 12:24:20 Assessment Important Re Process 0006 0001120t Adelle Smith 04/08/2020 09:28:26 Assessment Less Important Oper 0007 0009987t Jill Water 05/08/2020 11:30:33 Assessment mportant In Process DO08 0001130t Jack Hill 06/08/2020 12:10:33 06/08/2020 14:30:33 2 Assessment mportant Closed While only a small number of queries were received from students in the first week of the month, student queries are expected to go up over the remainder of the month. However, before getting actual data from the job management system in the coming weeks, Eljay school has asked you to explore the possible analyses that can be performed if using the current output. Eljay provided you with a document listing specific instructions on what you are expected to do in Excel. The instructions are listed below. Requirements (1) Open an Excel Workbook and name it as 'Student ID_ Student Name_ ACCT6001 Assessment 3' (i.e. 0009989t_Adam Smith_ACCT6001 Assessment 3). Create a worksheet labelled as 'Job Data'. Make a table similar to the one above using hypothetical details for 50 students. The table should include the following columns: Job Code, Student ID, Student Name, Date Opened, Date Closed, Job Type, Job Priority, Job Status. In your hypothetical data, assume: a) all student queries were made by students located in Australia, b ) at least 30 jobs that have a job status of "closed" c) at least one of each Job Status is included in the data set for each Job Type. For example, include at least: "open", "in process", "re process", and "closed" entries for "IT", ACCT6001_Assessment Brief 3_Case Study - Excel Page 2 of 7
