L Key A B B D E F G J 50 Arranging Data 51 Below are sales data for a company that is highly seasonal around the Q4 holidays season. 52 You are going to create a unique key for each year/quarter so that you can use reference functions (VLOOKUP) to pull the data into the required format. In the green cells below create a key that concatenates the 'Year' and the 'Quarter". 54 Concatination is presented in the 'EXCEL Complex Functions' sheet of Cash Flow Statement (Workbook 6). 55 56 Year Quarter Revenues 57 2017 01 10 58 2017 Q2 15 59 2017 Q3 32 60 2017 Q4 45 61 2018 Q1 10 62 2018 Q2 23 63 2018 Q3 33 64 2018 04 71 65 2019 01 19 66 2019 02 25 67 2019 Q3 42 68 2019 Q4 85 69 2020 Q1 20 70 2020 Q2 30 71 2020 Q3 65 72 2020 Q4 120 73 74 Write formulas in the green cells below that rearranges this data in a format that recognizes the seasonality. 75 The formulas should use VLOOKUP functions to pull data from the above chronological presentation. 76 The formulas should also be written using 'S's in the addressing so that the formula in cell 079 can be copied across and down. 77 78 Year Q1 Q2 Q3 Q4 Total Growth 79 2017 80 2018 81 2019 82 2020 83 Total 84 85 We have calculated the percentage that each quarter represents of each years total sales. 86 e 87 Year Q1 Q2 Q3 Q4 88 2017 e 02 89 2018 90 2019 91 --- 2020 92 Total -- 93 94 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. 95 You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 96 When the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. 97 98 Enter a formula in the green box below for the CAGR based on annual data from 2017 to 2020. 99 100 101 Assuming that growth rate continues, enter a formula in the green box below to forecast Revenues for 2021. 102 103 104 Analyze the data and enter formulas in the green cells below to forecast quarterly Revenues for fiscal year 2021. 105 Remember to use 'S' so that the formula in column C can be copied to the right. 106 Year 01 Q2 Q3 04 107 2021 108