How to Use Excel to do Problem 1 and 2?

11:01 AM Tue Oct 8 100% +c @ +591; Problems d. Use the regression tool to verify your results from part c. Is the trend statistically signicant? Use at least three methods from the regression output to show why or why not. e. Turn off iteration and use the Scenario Manager to set up three scenarios: 1) Best CaseSales are 5% higher than expected, 2) Base CaseSales are exactly as expected. 3) Worst CaseSales are 5% less than expected. What is the DFN under each scenario? 2. Use the same data as in Problem 1. a. Recalculate the percentage of sales income statement, but this time use the TREND function to forecast other income and interest expense. b. Recalculate the percentage of sales balance sheet, but this time use the TREND function to forecast cash, other long-term assets, and other liabilities. 0. Do these new values appear to be more realistic than the original values? Does this make sense for each of these items? Might other % income statement or balance sheet items be forecasted in this way? 3. The spreadsheet le "Chapter 5 Problem 3.xlsx" (to nd the student spreadsheets for Financial Analysis with Microsoft Excel, eighth edition, go to www.cengagebrain.com) contains monthly total returns for the S&P 500 index, Apple, Inc, (AAPL), and Fidelity Contrafund (FCNTX) from September 2011 to August 2016 (60 months). a. Create a scatter plot to show the relationship between the returns on AAPL and the S&P 500. Describe, in words, the relationship between the returns of AAPL and the S&P 500. Estimate the slope of a regression equation of this data. Repeat for FCNTX. '"'"""b".'"'""'lf:i'd a"unreal"nenasnnetestne"chart,"ana place the equation and R2 on the chart, Does this equation conrm your guess from part a? How much of the variability in AAPL returns can be explained by variability in the broad market? Repeat for FCNTX. 165 I86 562: nuAn-rcn z. I::..._..__:..I Qt'____t I:....-...._n:..__