Spreadsheet Modeling for Decision Making

Question 1 (10 marks) A company is evaluating the performance of its sales staff members based on the yearly sales results from 2021 to 2023 and the current customers' feedback. Question 1 10 marks J ohrw % compared with last 2 agc Table 1 shows the 3-year Sales Results and the current Customers' Feedback (score of 1 to 5 with 5 being the best) of its employees. The company is considering evaluating its employees and to propose recommendations based on three evaluation systems. Complete the model by entering the appropriate formulas to generate the corresponding recommendations, i.e. "Excellent", "Normal" or "Poor" for each evaluation system marked as (a), (b) and (c) respectively. Ensure that the formulas can be filled down the rows for each column and treat these three recommendations independently. Recommendation (a): "Excellent" if the sales result in Year 2023 is the highest among all the employees, "Poor" if this year's sales result is the lowest among all the employees. Otherwise, display "Normal". Recommendation (b): "Excellent" if the sales have increased for each of the two years (2023's sales is higher than 2022, and 2022's sales is higher than 2021) and the customers' feedback is at least 3. "Poor" if the sales have decreased for each of the two years. Otherwise, display "Normal". Recommendation (c): "Excellent" if 2023's sales result is higher than 2022's by more than 30% and 2023's sale result is higher than 2021 by more than 40%, or the customer feedback is 5. "Poor" if the sales have decreased for each of the two years or if the customers' feedback is the lowest among the employees. Otherwise, display "Normal". After that, the final recommendation would be based on the following condition: The employee will only be promoted if he or she achieves "Excellent" recommendations for three recommendations. Otherwise, he or she remains in the current position. In other words, there are two possibility outcomes: "promotion" or "no promotion".