A B D E 1 Customer Account Last Closing Balance Date $20,302.98 5/20/2022 $12,009.59 5/31/2022 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 9 Commodo Incorporated 10 Vitae Sodales Ltd 11 Sem Associates 12 Lectus Incorporated 13 Lacus Pede LLC Date Account Account Opened 7692-6853-8864-1843 10/17/2018 4865-7149-6934-1082 1/29/2021 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 $72.34 4/22/2022 3356-5506-8100-7835 4/12/2020 $10,224.43 5/7/2022 3194-4679-7526-2090 12/27/2020 $13,772.86 4/28/2022 5550-1148-4866-1459 8/18/2019 $20,932.72 4/24/2022 4552-2559-6862-8458 7/16/2018 $1,652.73 5/27/2022 14 Sed Dui Fusce Industries 7663-7807-1318-3090 1/15/2020 $20,655.96 5/11/2022 15 Magnis dis parturient Consultin 6600-5375-1334-7964 12/22/2020 $21,294.42 5/13/2022 16 Neque Inc. 2623-6670-6765-3739 2/11/2018 $23,272.49 5/18/2022 17 Nunc Ullamcorper Velit Limited 9475-6320-3613-9247 2/3/2019 $20,955.73 5/19/2022 18 Non Incorporated 4550-7833-1127-6018 5/3/2020 $548.55 5/11/2022 19 Montes Nascetur ridiculus LLP 4722-3766-9403-6457 12/24/2020 $13,051.35 5/20/2022 20 Luctus Curabitur Egestas Limitec 6385-2142-4671-9698 2/24/2020 $15,588.22 5/22/2023 21 Pede Nunc Sed Limited 4766-3862-5462-2347 9/27/2020 $18,429.62 5/6/2022 4974-2050-8647-7177 12/30/2018 $18,575.38 5/6/2022 1909-9689-2416-6208 4/12/2018 $23,444.21 5/19/2022 8/11/2020 8421-3144-9326-8707 $11,846.91 5/6/2022 6622-4922-1212-3677 8/24/2018 $22,560.83 5/16/2022 8162-3631-1263-9436 9/28/2021 $6,156.14 5/7/2022 2004-3211-1938-2792 1/22/2019 $15,433.77 5/25/2022 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 F Next Statement Date G Renewal Amount $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 89.00 89.00 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 H Annual Renewal Date 10/31/2021 1/31/2023 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. 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. A B D E 1 Customer Account Last Closing Balance Date $20,302.98 5/20/2022 $12,009.59 5/31/2022 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 9 Commodo Incorporated 10 Vitae Sodales Ltd 11 Sem Associates 12 Lectus Incorporated 13 Lacus Pede LLC Date Account Account Opened 7692-6853-8864-1843 10/17/2018 4865-7149-6934-1082 1/29/2021 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 $72.34 4/22/2022 3356-5506-8100-7835 4/12/2020 $10,224.43 5/7/2022 3194-4679-7526-2090 12/27/2020 $13,772.86 4/28/2022 5550-1148-4866-1459 8/18/2019 $20,932.72 4/24/2022 4552-2559-6862-8458 7/16/2018 $1,652.73 5/27/2022 14 Sed Dui Fusce Industries 7663-7807-1318-3090 1/15/2020 $20,655.96 5/11/2022 15 Magnis dis parturient Consultin 6600-5375-1334-7964 12/22/2020 $21,294.42 5/13/2022 16 Neque Inc. 2623-6670-6765-3739 2/11/2018 $23,272.49 5/18/2022 17 Nunc Ullamcorper Velit Limited 9475-6320-3613-9247 2/3/2019 $20,955.73 5/19/2022 18 Non Incorporated 4550-7833-1127-6018 5/3/2020 $548.55 5/11/2022 19 Montes Nascetur ridiculus LLP 4722-3766-9403-6457 12/24/2020 $13,051.35 5/20/2022 20 Luctus Curabitur Egestas Limitec 6385-2142-4671-9698 2/24/2020 $15,588.22 5/22/2023 21 Pede Nunc Sed Limited 4766-3862-5462-2347 9/27/2020 $18,429.62 5/6/2022 4974-2050-8647-7177 12/30/2018 $18,575.38 5/6/2022 1909-9689-2416-6208 4/12/2018 $23,444.21 5/19/2022 8/11/2020 8421-3144-9326-8707 $11,846.91 5/6/2022 6622-4922-1212-3677 8/24/2018 $22,560.83 5/16/2022 8162-3631-1263-9436 9/28/2021 $6,156.14 5/7/2022 2004-3211-1938-2792 1/22/2019 $15,433.77 5/25/2022 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 F Next Statement Date G Renewal Amount $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 89.00 89.00 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 H Annual Renewal Date 10/31/2021 1/31/2023 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. 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