K M N O . B D E F G H J L 79 2021 Q4 202104 120 80 81 Write formulas in the yellow cells below that rearranges this data in a format that recognizes the seasonality. 82 The formulas should use VLOOKUP functions to pull data from the above chronological presentation. VLOOKUP uses the left- most column of data. 83 The formulas should combine the Year in Column B with the Quarter in Row 86 to match with the 'Key' you created above. 84 The formulas should also be written using '$'s in the addressing so that the formula in cell 087 can be copied across and down. 85 86 Year Q1 Q2 Q3 04 Total Growth 87 2018 10 15 32 45 102 88 2019 10 23 33 71 137 34% 89 2020 19 25 42 85 171 25% 90 2021 20 30 65 120 235 37% 91 Total 59 93 172 321 645 92 93 we have calculated the percentage that each quarter represents of each years total sales. 94 95 Year 01 Q2 Q3 04 96 2018 10% 15% 31% 44% 97 2019 7% 17% 24% 52% 98 2020 11% 15% 25% 49% 99 2021 9% 13% 28% 51% 100 Total 9% 14% 27% 50% 101 102 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. 103 You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 104 when the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. 105 This is a very simple analysis that demonstrates the value of using concatination as a financial analyst. 106 107 Enter a formula in the yellow box below for the CAGR based on annual data from 2018 to 2021. 108 109 110 Assuming that growth rate (CAGR) continues, enter a formula in the yellow box below to forecast Revenues for 2022. 111 112 113 Analyze the data and enter formulas in the yellow cells below to forecast quarterly Revenues for fiscal year 2022. 114 Remember to use '$' so that the formula in column C can be copied to the right. 115 Year Q1 Q2 Q3 04 116 2022 117 -4296 K M N O . B D E F G H J L 79 2021 Q4 202104 120 80 81 Write formulas in the yellow cells below that rearranges this data in a format that recognizes the seasonality. 82 The formulas should use VLOOKUP functions to pull data from the above chronological presentation. VLOOKUP uses the left- most column of data. 83 The formulas should combine the Year in Column B with the Quarter in Row 86 to match with the 'Key' you created above. 84 The formulas should also be written using '$'s in the addressing so that the formula in cell 087 can be copied across and down. 85 86 Year Q1 Q2 Q3 04 Total Growth 87 2018 10 15 32 45 102 88 2019 10 23 33 71 137 34% 89 2020 19 25 42 85 171 25% 90 2021 20 30 65 120 235 37% 91 Total 59 93 172 321 645 92 93 we have calculated the percentage that each quarter represents of each years total sales. 94 95 Year 01 Q2 Q3 04 96 2018 10% 15% 31% 44% 97 2019 7% 17% 24% 52% 98 2020 11% 15% 25% 49% 99 2021 9% 13% 28% 51% 100 Total 9% 14% 27% 50% 101 102 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. 103 You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 104 when the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. 105 This is a very simple analysis that demonstrates the value of using concatination as a financial analyst. 106 107 Enter a formula in the yellow box below for the CAGR based on annual data from 2018 to 2021. 108 109 110 Assuming that growth rate (CAGR) continues, enter a formula in the yellow box below to forecast Revenues for 2022. 111 112 113 Analyze the data and enter formulas in the yellow cells below to forecast quarterly Revenues for fiscal year 2022. 114 Remember to use '$' so that the formula in column C can be copied to the right. 115 Year Q1 Q2 Q3 04 116 2022 117 -4296