COMPANY: VERISK ANALYTICS

When you have completed this assignment, you will be able to: - Calculate historical yearly sales growth - Identify analyst estimates of growth - perform a regression using the regression tool in excel - use the regression output and use the average of your calculations and analysts predictions to create two forecasts of future sales 1. Go to the 5-year income statement you downloaded for your company in Project #1. Look at the revenues (or sales) for the most recent 5 years for your company. 2. Calculate the historical sales growth rate. To find the sales growth rate, use the formula: Sales Growth in year 1 = Sales in year 2 Sales in year 1 Sales in year 1 Use this formula for a 5 year period (you will have 4 numbers). Average these numbers. 3. Look up what analysts believe is the future growth rate in sales, earnings or dividends for your company. Find at least three estimates of earnings, cash flow, or dividend growth for the longest future period that you can find (probably 5 years). Note you can find this in Yahoo! Finance, Hoovers, Nasdaq, CNN Money, etc. However, use at least three sources for your companys rates for full credit. Find an average sales growth rate. (You will have at least four numbers to average the one you calculated in #1 and at least 3 that you looked up.) 4. Forecast future sales using regression analysis. You should use the regression tool in excel to find your companys forecasted sales. However, given that Excel 2011 for Macs does not provide the regression tool, you are also allowed to calculate your slope and intercept for the regression analysis, by using the following excel formulas: =intercept(yvars,xvars) and =slope(yvars,xvars), where yvars are your sales numbers, and xvars are your sales years. 5. Forecast future sales for the next 11 years using the average of the growth rate that you calculated in #2 and the growth rates that you looked up in #1. Your deliverable is your calculations of sales growth rates and the future growth and growth rates that youve looked up (with sources listed), your regression and your forecast of 11 years of future sales. Document where all the numbers come from. This should be an excel document. Please put all of the calculations on a single tab (with formulas**) in your worksheet, as is done in the example. One tab should be your companys income statement. Your regression output (if obtained from the excel regression analysis) and data should be your second and third tabs.

**Your new data should use cell references NOT manual input. For example, instead of writing sales for 2016 directly as $1,500, you must enter in a cell reference. Cell references are formatted as =IncomeStatement!C5 This will output the content of cell C5 from the IncomeStatement