Write formulas in the yellow cells below that rearranges this data in a format that recognizes the seasonality. The formulas should use VLOOKUP functions to pull data from the above chronological presentation. VLOOKUP uses the left-most column of data. The formulas should combine the Year in Column B with the Quarter in Row 86 to match with the 'Key' you created above. The formulas should also be written using '$'s in the addressing so that the formula in cell C87 can be copied across and down. Year Q1 Q2 Q3 Q4 Total Growth 2018 10 15 32 45 102 2019 10 23 33 71 137 34% 2020 19 25 42 85 171 25% 2021 Total 20 59 30 65 120 235 37% 93 172 321 645 We have calculated the percentage that each quarter represents of each years total sales. Year Q1 Q2 Q4 2018 10% 15% 31% 44% 2019 7% 17% 24% 52% 2020 11% 15% 25% 49% 2021 Total 9% 9% 13% 28% 14% 27% 51% 50% 1 2 Notice that we have calculated the quarterly percentages by year, the average of these across years, and the quarterly percentage of the total sales by quarter summed across all four years. You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 4 When the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. This is a very simple analysis that demonstrates the value of using concatination as a financial analyst. 3 2 Enter a formula in the yellow box below for the CAGR based on annual data from 2018 to 2021. Assuming that growth rate (CAGR) continues, enter a formula in the yellow box below to forecast Revenues for 2022. 3 Analyze the data and enter formulas in the yellow cells below to forecast quarterly Revenues for fiscal year 2022. 4 Remember to use '$' so that the formula in column C can be copied to the right. Year 2022 Q1 Q2 Q3 Q4