Question B2 ABC Equipment is a trading firm specializing in office equipment. The following figure shows the monthly sales report, indicating the commission payment to the salesmen of the firm in April 2020. D E F G . B 1 ABC Equipment - Monthly Sales Report 2 3 Year: 2020 4 Month: APR 5 Branch: HHB 6 7 8 9 10 Staff ID SO24 S087 S116 S146 S177 S216 S225 Sales Amount Performance $100,000 Satisfactory $250,000 Satisfactory $180,000 Satisfactory $520,000 Outstanding $110,000 Satisfactory $1,000,000 Outstanding $80,000 Unsatisfactory Payment Ref. Commission 2020APRSO24 $10,000.00 2020APRS087 $25,000.00 2020APRS116 $18,000.00 2020APRS146 $78,000.00 2020APRS177 $11,000.00 2020APRS216 $150,000.00 2020APRS225 $8,000.00 11 12 13 14 15 16 Total 17 18 Highest Lowest Average $1,000,000 $80,000 $320,000 19 20 (a) Explain how you would obtain the values in cell C17, C18, and C19 by using appropriate MS Excel functions. (3 marks) (b) Range D8:D14 exhibits the performance ranking according to the following criteria: Sales amount Below $100,000 Between $100,000 and $300,000 Above $300,000 Performance Unsatisfactory Satisfactory Outstanding By using the IF function in MS Excel with appropriate cell references, explain how you would derive the results in the range D8:D14. (5 marks) (c) The Payment Ref. shown in column E was derived by combining the value shown in cell B3, cell B4, and the respective Staff ID. By using appropriate function in MS Excel with appropriate cell references, explain how you would generate the Payment Ref. as shown in the range E8:E14. (4 marks)