A B D E 1 Customer 2 Eget Corporation 3 Eget volutpat LLC 4 Arcu Nunc Associates 5 Ipsum Nunc Id Corporation 6 Elit Sed Consequat Corporation 7 Curabitur Ut PC 8 Proin Company Date Account Account Last Closing Account Opened Balance Date 7692-6853-8864-1843 10/17/2018 $20,302.98 5/20/2022 4865-7149-6934-1082 1/29/2021 $12,009.59 5/31/2022 5421-2576-4204-8967 10/24/2019 $4,975.68 5/3/2022 5260-5115-4312-9393 5/24/2020 $19,938.55 5/13/2022 7366-4521-1078-2086 12/10/2020 $2,998.79 5/2/2022 1851-5737-6337-5949 1/19/2021 $9,254.63 5/8/2022 1256-7746-2300-8955 6/10/2019 $11,104.96 4/23/2022 9618-4637-1860-2436 1/3/2018 3356-5506-8100-7835 4/12/2020 3194-4679-7526-2090 12/27/2020 5550-1148-4866-1459 8/18/2019 4552-2559-6862-8458 7/16/2018 7663-7807-1318-3090 1/15/2020 6600-5375-1334-7964 12/22/2020 2623-6670-6765-3739 2/11/2018 17 Nunc Ullamcorper Velit Limited 9475-6320-3613-9247 2/3/2019 18 Non Incorporated 9 Commodo Incorporated 10 Vitae Sodales Ltd 11 Sem Associates 12 Lectus Incorporated 13 Lacus Pede LLC 14 Sed Dui Fusce Industries 15 Magnis dis parturient Consulting 16 Neque Inc. $72.34 4/22/2022 $10,224.43 5/7/2022 $13,772.86 4/28/2022 $20,932.72 4/24/2022 $1,652.73 5/27/2022 $20,655.96 5/11/2022 $21,294.42 5/13/2022 $23,272.49 5/18/2022 $20,955.73 5/19/2022 $548.55 5/11/2022 $13,051.35 5/20/2022 $15,588.22 5/22/2022 $18,429.62 5/6/2022 $18,575.38 5/6/2022 $23,444.21 5/19/2022 $11,846.91 5/6/2022 $22,560.83 5/16/2022 $6,156.14 5/7/2022 $15,433.77 5/25/2022 4550-7833-1127-6018 5/3/2020 19 Montes Nascetur ridiculus LLP 4722-3766-9403-6457 12/24/2020 20 Luctus Curabitur Egestas Limitec 6385-2142-4671-9698 2/24/2020 21 Pede Nunc Sed Limited 4766-3862-5462-2347 9/27/2020 4974-2050-8647-7177 12/30/2018 1909-9689-2416-6208 4/12/2018 8421-3144-9326-8707 8/11/2020 6622-4922-1212-3677 8/24/2018 8162-3631-1263-9436 9/28/2021 2004-3211-1938-2792 1/22/2019 22 Adipiscing Ltd 23 Fringilla Euismod Corp. 24 Arcu Vestibulum Ante Associate 25 Magna Sed Dui Ltd 26 Lorem Inc. 27 Pretium Et Rutrum Limited 28 29 30 31 32 33 F Next Statement Date G $ $ $ $ $ $ H Annual Renewal Renewal Amount Date 89.00 10/31/2022 89.00 1/31/2023 189.00 89.00 249.00 249.00 249.00 89.00 89.00 $ $ $ $ $ $ $ 89.00 249.00 89.00 189.00 $ 89.00 $ 89.00 $ 249.00 $ 89.00 89.00 189.00 89.00 $ $ $ $ 189.00 $ 189.00 $ 89.00 $ 189.00 $ 89.00 $ 249.00 Part 3: 5 points. CreditAccts Worksheet - Instructions Write a formula to populate the "Next Statement Date" column of data. The next statement date occurs 20 working days after the last closing date. Write a formula to determine the next annual renewal date. The annual renewal happens at the end of the month (determined by the month in which the account was opened) in the current or future year. For any accounts that were opened prior to June you can assume that the account was renewed - so the renewal date should roll to 2023. For example - an account opened on 10/17/2018 - would renew on 10/31/2022. An account opened on 1/29/2021 would renew on 1/31/2023. O You may insert additional columns, as needed, to break this problem down... just leave all working columns on the worksheet - and then either copy/paste values to column H- or write the final formula in column H to produce the data required. O Yes, this one is difficult. Here are a few hints: . Remember the EOMonth() function takes two arguments: the date to operate on and the number of months to roll forward: EOMonth(Date Account Opened, #Months) HINT: There is a YEAR function that returns just the year part of a date. YEAR(TODAY()) returns 2022 on March 1, 2022. YEAR(C2) returns 2018. Subtracting those two values gives you the number of years to roll forward. (2022-2018 = 4), multiplying that by 12 gives you the number of months to roll forward. But there is one additional item to address if the Date the Account Opened is a month prior to TODAY()'s month, you must consider that the renewal has already taken place, thus the Annual Renewal Date should roll forward to 2023 (one additional year or 12 months more). As stated above: if the Date Account Opened is October of 2018... and it is June of 2022, the annual renewal happens at the end of the month, 4 years (or 48 months) later. If the Date Account Opened is January, 2021 and it is June of 2022, the Annual Renewal is not January of 2022, but rather should display at the end of the month in January of 2023. Format the data, to present meaningful and an easy to consume report