Go to the Client Invoices worksheet. In cell B3, insert a formula using the DATE function to display the current date as 2024/06/01 (yyy/m/d) so that Brett can use the date in calculations. [Mac Hint: The date will display using the format yy/m/d.] 2. In column F, Brett wants to display the age of the invoice, which is the number of days between the current date and the invoice date. Provide this information as follows: a. In cell F6, insert a formula without using a function that subtracts the invoice date (cell B6) from the current date (cell B3). b. Use an absolute reference to cell B3 in the formula. c. Use the Fill Handle to fill the range F7:F15 with the formula in cell F6, filling the range without formatting. 3. In column G, Brett wants to calculate the number of days the invoice is overdue. If the age of the invoice is greater than 30 days, it is overdue. Calculate the days overdue as follows: a. In cell G6, insert a formula using the IF function that tests whether the age of the invoice (cell F6) is greater than 30. b. If the age of the invoice is greater than 30, subtract the due date (cell E6) from the current date (cell B3). c. If the age of the invoice is less than or equal to 30, display 0 to show that the invoice is not overdue. d. Use an absolute reference to cell B3 in the formula. e. Use the Fill Handle to fill the range G7:G15 with the formula in cell G6, filling the range without formatting. Africa Illustrated Excel 365/2021 | Module 3: SAM Project 1a 4. Brett sends an email reminder to clients with overdue invoices for amounts more than R1,000. Provide a quick way to identify those clients as follows: a. In cell H6, insert a formula using the AND function that returns TRUE if the number of days overdue (cell G6) is greater than 0 and if the invoice amount (cell D6) is greater than 1000. b. Use the Fill Handle to fill the range H7:H15 with the formula in cell H6, filling the range without formatting. 5. Brett assigns each client a rating of A, B, or C depending on the number of days an invoice is overdue. Display this information using a nested IF function as follows: a. In cell I6, insert a formula using an IF function to test whether the days overdue (cell G6) are less than 1 and displays a rating of "A" if the condition is true. b. The next condition uses another IF function to test whether the days overdue (cell G6) are less than 30 and displays a rating of "B" if the condition is true. c. Display a rating of "C" if the conditions are false. d. Use the Fill Handle to fill the range I7:I15 with the formula in cell I6, filling the range without formatting. 6. Clients are eligible for a discount if they have a rating of "A" or do not require an email reminder. Provide this information for Brett as follows: a. In cell J6, insert a formula using the OR function that returns TRUE if the rating (cell I6) equals "A" or the email reminder (cell H6) equals FALSE. b. Use the Fill Handle to fill the range J7:J15 with the formula in cell J6, filling the range without formatting. 7. Brett likes to keep track of the total number of current invoices he has sent to clients. In cell B17, enter a formula using the COUNT function that counts the invoice numbers (the range A6:A15). 8. Brett also likes to keep track of the approximate amount he has billed clients. In cell B18, enter a formula using the ROUND and SUM functions that totals the invoice amounts (the range D6:D15) and rounds the result to 0 decimal places. 9. Go to the Quarter 3 Sales worksheet. Apply the Accounting number format to the sales amounts (range B6:B11) to indicate they are rand amounts. 10. Brett wants to calculate the percentage of each category of insurance contributed to his total sales. Because he also wants to avoid a divide by zero error in the calculation, he has already entered the IFERROR function. Complete the formula as follows: a. In cell C6, before the comma in the IFERROR function, create a formula without using a function that divides the amount of automobile insurance sales (cell B6) by the total sales (cell B11). b. Use an absolute reference to cell B11 in the formula. c. Use the Fill Handle to fill the range C7:C10 with the formula in cell C6. Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.