Business Finance Homework Help

Download the attached data and follow the instruction below. Submit your excel workbook saved as “LastnameMT2.xslx” (i.e. “AsciMT2.xslx”) by 05/07/2021, Friday 11:59am (by noon). Submit your excel workbook with your responses to Canvas. Suggested duration: ~60 minutes.

1. Import the text file into excel workbook using “get data From Text” button at Data tab.

– Use “Regression” tool at “Data Analysis” to find the impact of several variables on “Development Cost”.

Y Development Cost

X1 Reparable Items

X2 Primary Functions

X3 Estimated Weight

X4 Service Life

– Regress all the variables separately. Find the best variable that explain the “Development Cost”

– Add another variable to the best variable and compare the estimation results in terms of R-square, Adjusted R-Square, and Standard Error terms.

– Regress using three and lastly all four variables and compare the estimation results in terms of R-square, Adjusted R-Square, and Standard Error terms.

– Find the best model.
2. Prediction and Graphing.

– Open a new worksheet called “Prediction-Confidence Interval.” Copy/paste your data in the worksheet and add the columns and cells shown below:

AGBS 105 – page 2

– Use the best model for the “Prediction-Confidence Interval” worksheet.
– Insert Coefficients for Intercept, variables in variable order, and lastly enter Standard Error value.

– If a variable is not selected for the best model insert its coefficient as 0 (zero). (Coefficient is not statistically significant)

– Enter the formulas as shown in the figure (You can enter the formulas in the first row, and copy/paste for the rest). The worksheet will calculate “Predicted Cost”, “Lower 95%” and “Upper 95%” columns automatically.

– Save your workbook and submit it to Canvas – All tables need to be formatted nicely.

the data for the question is attached below.