https://ccnymailcuny-my.sharepoint.com/:x:/g/personal/malam008_citymail_cuny_edu/EWFp3-LrfZxBsKztt5GAELoB55LHrDL_sVO_NaOJ1N-0ew?e=qNt1jA

Enter the Selected Companies n column B ('Ticker') of the green boxes below enter in alphabetic order the five tickers for which Revenues in a quarter represented more than 40% of annual sales. We have given you a head start with 'AAP', Review the detail of end of quarter dates of the selected companies to confirm that the seasonality is correct and not a result of irregular financial quarters. For example, 'AAP' had irregular reporting periods which has resulted in multiple quarters being aggregated into QB in each year which has resulted in a 'Data Integrity Issue'. We have identified AAP as a 'Data Integrity Issue'. =or each other ticker select the Season or 'Data Integrity Issue'. Ticker Season Data Integrity Issue Data Integrity Issue Data Integrity Issue :Iick into the box in the ne Notin alph Change the first ticker to 'I Spring (02) that tickers above are in alphabetic order. Summer (03) Fall (Q4) t is interesting to underst Data Integrity Issue that are not Data Integrity Issues are so seasonal. The company(ies) with a winter season distribute natural gas. This makes sense because of the winter heaing seasons The company(ies) with a spring season income provided income tax perparation. This makes sense with an Aprl 15th filing date. estore it to the correct ticker, We have calculated the percentage that each quarter represents of each years total sales. Year Q1 Q2 Q3 Q4 2016 24% 25% 25% 27% 2017 24% 24% 25% 27% 2018 24% 25% 25% 26% 2019 24% 25% 25% 26% Total 24% 25% 25% 26% Average 24% 25% 25% 26% 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. When the results are relatively insensitive to methodology and assumptions the results are statistically 'robust'. Enter a formula in the green box below for the CAGR from 2016 to 2019. Assuming that growth rate continues, enter a formula in the green box below to forecast Revenues for 2020. Analyze the data and enter formulas in the green cells below to forecast quarterly Revenues for fiscal year 2020. Remember to use'S' so that the formula in column C can be copied to the right. Year Q1 Q2 Q3 Q4 2020 What do you think the peak season for the following businesses will be? Tour Guide Fireworks Retailer Christmas Tree Farm Winter (Q1) Ski Slope Spring (Q2) Children's Camp Wedding Venue Summer (Q3) Fall (Q4) EXHIBIT Strategic Concepts CASE Travel Industry CASE Distribution Channels DATA ANALYSIS Seasonality DATA Seasonality CASE Seasonality74 In the gray table below is data from two independent variables and a dependent variable. 75 12 11 18 15 14 11 13 6 Independent 1 16 Independent 1 6 V 1 5 5 6 20 27 35 33 19 34 00 P 29 42 3 Dependent 36 39 79 80 Use this data to perform a multivariate regression of the two independent variable and the dependent variable. Dependent 81 1 In the 'Input Y Range:' box enter 1 182:1191 Independent 1 Independent 1 36 82 2 In the 'Input X Range:' box enter G182:H191 3 In the 'Output Options' select the 'Output Range:' 39 83 12 4 Click on cell A193 in the 'EXCEL Workspace' Sheet and click on 'OK' 16 20 84 11 27 85 35 86 18 15 33 87 19 88 14 11 34 4 29 13 0OUTUTHU 42 The regression generates a significant amount of statistical information, but, the most important results are: The intercept is 1.724 The coefficient of the first independent variable is .983 96 The coefficient of the second independent variable is 2.963 The 'R-Squared' is a statistical measure that represents the proportion of the variance for a dependent variable that's explained by an independent variable or variables in a regression model. 98 A R-Squared of 0 indicates that there is absolutely no statistical relationship between the independent and dependent variables. 99 A R-Square of 1 indicates that that the independent variables fully explain the dependent variables. 00 In the green cell immediately below, enter the R-Squared calculated above. 01 189 Hint: The adjusted R-Square is a modified version of R-squared that has been adjusted for the number of predictors in the model. The values are similar enough that either value will be graded as correct. 04 The LINEST function supports multivariate (multiple factors) least squared linear regression. 05 The LINEST function is an 'array function' and like all other array functions is more complex than what is presented in this course. 06 Knowing that you have access to the LINEST is valuable, however, most Financial Analysts do not need such a capability. 07 08 Histogram 09 Histogram Charts are only supported in Excel 2016 and later. 10 In earlier versions of Excel. you will need to use an add-in. CASE Seasonality EXCEL Statistical Functions A CASE Travel Industry CASE Distribution Channels DATA ANALYSIS Seasonality DATA SeasonalityBelow we have the Net Income Margins of the companies from the 'DATA Comps' Sheet of Company Forecasting (Workbook 8) in the 'Information Technology' Sector. AAPL 22.4% ACN 10.1% QRVC 4.3% FTN 18.4% ADP 12.2% ADS 12.4% ADSK -3.1% AKAM 11.0% AMD 5.2% MU 46.5% ANSS 32.49 APH 14.8% AVGO 60.5% NVDA 35.3% BR 9.9% DNS 16.2% AMAT 19.2% ADBE 28.7% ANET 15.3% CRM 8.4% CSCO 0.2% CTSH 13.0% CTXS 19.4% LRCX 21.5% DXC 6.1% FFIV 21.0% FIS 10.5% FISV 20.4% FUR 15.9% FLT 33.3% NTAP 19.0% GLW 9.4% HPE 6.2% HPQ 9.1% IBM 11.0% INTC 29.7% IPGP 27.7% IT 3.1% JKHY 24.5% JNPR 12.2% KEYS 4.3% DATA ANALYSIS Seasonality DATA Seasonality CASE Seasonality EXCEL Statistical FunctionsJAMI 24.570 258 JNPR 12.2% 259 KEYS 4.3% 260 MA 39.2% 261 MCHP 6.7% 262 MXIM 18.8% 263 MSFT 15.0% 264 MSI 13.2% 265 KLAC 19.9% 266 INTU 267 ORCL 28.1% 268 PAYX 27.4% 269 PYPL 13.3% 270 QCOM 21.4% 271 ADI 24.1% 272 SNPS 13.9% 273 STX $0.11 274 SWKS 23.7% 275 SYMC 0.7% 276 TEL 18.3% 277 TSS 14.3% 278 TXN 35.4% 279 V 50.0% 280 VRSN 47.9% 281 WDC 3.3% 282 WU 15.29 283 XLNX 29.1% 284 XRX 3.8% 285 286 We will create a histogram of this data. 287 1 Click anywhere in the data set. 288 2 Click on the 'Insert' tab above. Home Insert Page Layout Formulas Data Review View A+ Share ~ Store M . EY Slicer + A . T Equation PivotTable Recommended Table PivotTables Pictures Shapes " My Add-ins Charts PivotChart Sparklines Timeline Link Comment 9 symbol 289 C164 fx 0.10568669527897 290 3 In the selection of charts in the middle of the Ribbon, select the 'Statistical' chart icon. 291 4 In the dropdown menu that appears select 'Histogram' (upper left selection). 292 Based on the chart created, how many companies had Net Income Margins of between 2.6% and 14.6%. 293 294 295 296 297 DATA ANALYSIS Seasonality DATA Seasonality CASE Seasonality EXCEL Statistical Functions EX