WEEK 7 ASSIGNMENT – FINANCIAL FORECASTING USING EXCELWeek 7 Assignment – Financial Forecasting Using ExcelOverviewDeveloping quantitative recommendations and financial forecasts using linear regressio
Week 7 Assignment Word Template PART A: Scatter Plot and Regression Analysis 1. Complete in the Week 7 Assignment Excel Template. Develop a scatter plot that accurately depicts data contained in Table 1 (found in the Week 7 Assignment Excel Template). 2. Make and support two conclusions regarding the relationship between profits and sales levels that can be made from the scatter plot.  [Answer here] 3. Complete in the Week 7 Assignment Excel Template. Compute the regression. Use the Excel Linear Regression feature and Table 1 data (found in the Excel template) in the scenario to develop a best-fit linear equation of the form Y = a + b1 X1 where: Y is the profit. X1 is the sales level.  a is the intercept from the regression analysis. b1 is the coefficient of X1 from the regression analysis. 4. Interpret the results of your regression by doing the following:  Identify and interpret the r Square statistic from the regression analysis.    [Answer here] Interpret the meaning of the numerical values of a and b1 estimated in the context of this problem.  [Answer here] Identify and interpret the t statistics for a and b1. [Answer here] PART B: Scatter Plot and Multiple Regression Analysis Complete in the Week 7 Assignment Excel Template. Develop scatter plots that accurately depict the data contained in Table 2 (found in the Week 7 Assignment Excel Template) – that is, plot the profits of the 20 stores by each of the variables listed.  Make and support two conclusions regarding the relationship between profits and sales levels that can be made from the scatter plot.  [Answer here] Complete in the Week 7 Assignment Excel Template. Use the Excel Multivariable Regression feature to pull data for Table 2 to develop a best-fit multiple regression equation of the form: Y = a + b1 X1 + b2 X2 + b3 X3 + b4 X4 where:  Y is the profit. X1 is the sales level. X2 is the size of each store. X3 is the number of product lines carried by the store. X4 is the distance from the nearest major competitor. Interpret the results of your regression. Do the following: Identify and interpret the r Square statistic from the regression analysis.    [Answer here] Interpret the meaning of the numerical values of a, b1, b2, b3, and b4 estimated in the context of this problem.  [Answer here] Identify and interpret the t statistics for a, b1, b2, b3, and b4. [Answer here] PART C: Store Location Recommendation Complete in the Week 7 Assignment Excel Template. Imagine that Casey Hardware is considering moving forward with one of the following three locations as described in Table 3 of the Week 7 Assignment Excel Template. Perform the necessary calculations in Excel to determine the potential profitability of each of the three locations under consideration based on your multiple regression analysis results from PART B.  Paste your regression equation into the designated area in the Week 7 Assignment Excel Template tab Part C. Use the equation and the data from Table 3 to complete Table 4 – Profitability Calculator. The last column, Overall Projected Profits, should be calculated using the equation. Identify the location where Casey Hardware should open their next store and explain why in one sentence. © 2021 Strayer University. All Rights Reserved. This document contains Strayer University Confidential and Proprietary information and may not be copied, further distributed, or otherwise disclosed in whole or in part, without the expressed written permission of Strayer University. Page 5 of 5