IL_EX365_2021_3a_ZacharySutton_Report_1

Zachary Sutton GE ver. 17.0.1-rc0000 1. 0/2 Create a formula using a function. In the Client Invoices worksheet, the formula in cell B3 should contain the DATE function. 2. 2/2 Create a formula without using a function. Copy a formula into a range. 3. 0/2 Create a formula using a function. In the Client Invoices worksheet, the formula in cell G6 should use an absolute reference to cell B3. Copy a formula into a range. In the Client Invoices worksheet, cell G6 contains an incorrect formula. 4. 2/2 Create a formula using a function. Copy a formula into a range. 5. 2/2 Create a formula using a function. Copy a formula into a range. 6. 3/3 Create a formula using a function. Copy a formula into a range. 7. 3/3 Create a formula using a function. 8. 3/3 Create a formula using a function. 9. 3/3 Change the number format. 10. 3/3 Create a formula using a function. Copy a formula into a range. SUBMISSION #1 | SCORE IS: 21 OUT OF 25 Brett Thao is an insurance agent for the Great Midwestern Insurance in Milwaukee, Wisconsin. He is analyzing the invoices he has sent clients and the sales he generated in the third quarter of the year. He asks you to help him complete the analysis. Go to the Client Invoices worksheet. In cell B3, insert a formula using the DATE function to display the current date as 6/1/2024 (m/d/yyyy) so that Brett can use the date in calculations. [Mac Hint: The date will display using the format m/d/yy.] 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. 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. Brett sends an email reminder to clients with overdue invoices for amounts more than $1,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. 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. 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. 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 ). 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. Go to the Quarter 3 Sales worksheet. Apply the Accounting number format to the sales amounts (range B6:B11) to indicate they are dollar amounts. 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. Illustrated Excel 365/2021 | Module 3: SAM Project 1a

Author: Note: Illustrat

Great Midwestern Insurance Client Accounts Current date: 6/1/2024 Client Invoices Invoice Number Invoice Date Account Name Amount Due Date Age of Invoice Days Overdue Email 1894 3/15/2024 Miguel Grazia $ 1,425.15 4/14/2024 78 48 1 1907 3/15/2024 Melissa Sullivan $ 907.88 4/14/2024 78 48 0 1988 4/2/2024 Ruth Hahn $ 915.65 5/2/2024 60 30 0 1997 4/2/2024 Troy Danz $ 1,023.33 5/2/2024 60 30 1 2003 5/1/2024 Jeong Pen $ 824.80 5/31/2024 31 1 0 2004 4/15/2024 Tom Marlowe $ 1,095.67 5/15/2024 47 17 1 2014 5/1/2024 Brent Pederson $ 1,045.50 5/31/2024 31 1 1 2021 4/15/2024 Charles Korato $ 1,235.33 5/15/2024 47 17 1 2045 5/15/2024 Alex Munoz $ 1,127.75 6/14/2024 17 0 0 2053 5/15/2024 Anna Napier $ 804.67 6/14/2024 17 0 0 Total invoices: 10 Total amount: $ 10,406.00

Great Midwestern Insurance Sales Summary Quarter 3 Category Amount % of Total Automobile $ 10,453.25 23% Casualty $ 8,755.67 20% Homeowners $ 9,877.35 22% Personal property $ 8,034.80 18% Renters $ 7,523.50 17% Total $ 44,644.57