Design a spreadsheet and use an algorithm to solve a business problem. You will also produce a report to present the results to a non-technical audience. It′s a simple task for those who have experience with VBA (Excel) All the additional details can be provided. There is a lot more information that is needed to finish this project. Background You are working for an auto repair chain called “Easy Does It”. Your branch accepts appointments through the website of your branch. Each appointment takes a fixed amount of time, e.g., changing all the tyres of a car takes 1 hour, oil and filter change takes 2 hours, an interim car service takes 4 hours, and a full car service takes 8 hours. You employ 12 mechanics, all of which can provide all the services listed on your website. The customers drop off their cars at 8:30 in the morning. The mechanics start working at 9:00 and keep on until 18:00 with a one-hour lunch break between 12:00 and 13:00, for a total of 8 hours per working day. Each customer is called in to pick up their car as soon as their car has been fixed. One of your duties is to determine the daily work schedule for the mechanics, i.e., which mechanic starts working on a given car at which time. For employee satisfaction, it is important that the day’s work is completed as soon as possible, and definitely by 18:00. This provides valuable time for resting and recreation. If the workloads of the employees differ by too much, the employees become disgruntled due to “unjust workload distribution”. Customers can cancel appointments overnight and some employees may fail to show up for work due to health reasons, so you frequently need to readjust the schedule in the mornings. You start thinking that maybe you could use an optimisation algorithm to help you. 1. A typical day’s problem could be represented as per the data in the table below. Job IDProcessing Time 18 21 33 46 55 62 73 83 94 107 113 122 131 146 156 162 175 183 195 206 2. After a brief search on the internet, you decide to use the following algorithm: Algorithm ScheduleMechanics Sort the jobs in decreasing order of processing time Initialise all mechanics to have an empty schedule with a makespan of 0 For i ＝ 1 to numJobs minMakespan ＝ 9 ‘cannot be more than 8 hours mechanicSelected ＝ 0 For j ＝ 1 to numMechanics If Mechanic(j).makespan ˂ minMakespan and _ Mechanic(j).makespan + jobList(i).processingTime ˂＝ 8 Then mechanicSelected ＝ j minMakespan ＝ Mechanic(j).makespan End if Next j If mechanicSelected ˃ 0 then Add job i to the schedule of mechanicSelected Update the makespan of mechanicSelected Else Report job i as “infeasible to be scheduled on this day” End if Next i End Algorithm 3. Note that sorting the jobs by decreasing processing time aims to minimise the total completion time. Selecting the mechanic with the shortest makespan each time aims to equalise the workload. Based on your experience, you think that you should: a. Use buttons or visual interfaces (User Forms) to communicate with the user. The user should be able to: Reset the workbook (delete all existing data), generate job data (possibly random, to test the algorithm for different settings), and solve the problem. b. Set up a worksheet named “Data” to store the number of jobs, time required for each job, as well as the number of employees available and anything else you think is relevant. c. Display the output of your algorithm in a worksheet named “Schedule”.
