18 20 Q1 6 SHEET ROADMAP 7 This is Challenge Homework 8 9 Below are sales data for a company that is highly seasonal around the Q4 holidays season. 10 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. 11 In the green cells below create a key that concatenates the 'Year' and the 'Quarter". 12 Concatination is presented in the 'EXCEL Complex Functions' Sheet of Cash Flow Statement (Workbook 6). 13 14 Year Quarter Key Revenues 15 2016 Q1 10 16 2016 Q2 15 17 2016 Q3 32 2016 Q4 45 19 2017 Q1 10 2017 Q2 23 21 2017 Q3 33 22 2017 04 71 23 2018 19 24 2018 Q2 25 2018 Q3 42 26 2018 Q4 85 27 2019 Q1 20 28 2019 Q2 30 29 2019 Q3 65 2019 Q4 120 31 32 Write formulas in the green cells below that rearranges this data in a format that recognizes the seasonality. 33 The formulas should use VLOOKUP functions to pull data from the above chronological presentation. 34 The formulas should also be written using '$'s in the addressing so that the formula in cell D37 can be copied across and down. 35 36 Year Q1 Q2 Q3 Q4 Total Growth 37 2016 38 2017 39 2018 40 2019 41 Total 42 43 We have calculated the percentage that each quarter represents of each years total sales. 44 25 30 40 2019 41 Total 42 43 We have calculated the percentage that each quarter represents of each years total sales. 44 45 Year Q1 Q2 Q3 04 46 2016 47 2017 48 2018 49 2019 50 Total 51 Average 52 53 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. 54 You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 55 When the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. 56 57 Enter a formula in the green box below for the CAGR from 2016 to 2019. 58 59 60 Assuming that growth rate continues, enter a formula in the green box below to forecast Revenues for 2020. 61 62 63 Analyze the data and enter formulas in the green cells below to forecast quarterly Revenues for fiscal year 2020. 64 Remember to use 'S' so that the formula in column C can be copied to the right. 65 Year Q1 Q2 Q3 Q4 66 2020 67 68 What do you think the peak season for the following businesses will be? 69 70 Tour Guide 71 Fireworks Retailer 72 Christmas Tree Farm 73 Ski Slope 74 Children's Camp 75 Wedding Venue 18 20 Q1 6 SHEET ROADMAP 7 This is Challenge Homework 8 9 Below are sales data for a company that is highly seasonal around the Q4 holidays season. 10 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. 11 In the green cells below create a key that concatenates the 'Year' and the 'Quarter". 12 Concatination is presented in the 'EXCEL Complex Functions' Sheet of Cash Flow Statement (Workbook 6). 13 14 Year Quarter Key Revenues 15 2016 Q1 10 16 2016 Q2 15 17 2016 Q3 32 2016 Q4 45 19 2017 Q1 10 2017 Q2 23 21 2017 Q3 33 22 2017 04 71 23 2018 19 24 2018 Q2 25 2018 Q3 42 26 2018 Q4 85 27 2019 Q1 20 28 2019 Q2 30 29 2019 Q3 65 2019 Q4 120 31 32 Write formulas in the green cells below that rearranges this data in a format that recognizes the seasonality. 33 The formulas should use VLOOKUP functions to pull data from the above chronological presentation. 34 The formulas should also be written using '$'s in the addressing so that the formula in cell D37 can be copied across and down. 35 36 Year Q1 Q2 Q3 Q4 Total Growth 37 2016 38 2017 39 2018 40 2019 41 Total 42 43 We have calculated the percentage that each quarter represents of each years total sales. 44 25 30 40 2019 41 Total 42 43 We have calculated the percentage that each quarter represents of each years total sales. 44 45 Year Q1 Q2 Q3 04 46 2016 47 2017 48 2018 49 2019 50 Total 51 Average 52 53 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. 54 You can see that the average of each year's seasonal percentages is extremely close to the percentages of the totals. 55 When the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. 56 57 Enter a formula in the green box below for the CAGR from 2016 to 2019. 58 59 60 Assuming that growth rate continues, enter a formula in the green box below to forecast Revenues for 2020. 61 62 63 Analyze the data and enter formulas in the green cells below to forecast quarterly Revenues for fiscal year 2020. 64 Remember to use 'S' so that the formula in column C can be copied to the right. 65 Year Q1 Q2 Q3 Q4 66 2020 67 68 What do you think the peak season for the following businesses will be? 69 70 Tour Guide 71 Fireworks Retailer 72 Christmas Tree Farm 73 Ski Slope 74 Children's Camp 75 Wedding Venue