Question

Please give step by step excel tutorial

The calculation of the coefficient of determination r2 depends on the number of independent variables. An adjusted value of r2 based on the number of degrees of freedom is calculated using the formula shown below, where n is the number of data pairs and k is the number of independent variables 1-2) (n-1) n-k-1 adj The accompanying table shows the net sales (in billions of dollars), total assets (in billions of dollars), and shareholder's equities (in billions of dollars) for a company for five years. Calculate r3dj and determine the percentage of the variation in y that can be explained by the relationships between variables according to 3 Compare this result with the one obtained using r2 Click the icon to view the table. Data Table Shareholder's equit Net sales, x1 Total assets, x2 63.3 371.9402.9403.7 419.444.4 164.4163.9 173.2 180.1 190.1 65.9 71 68.3 71.9 Print Done

Questions:

A) r^2=

B) r^2adj=

C) About BLANK % (number value) of the variation in y can be explained by the relationship between the variables

Answer 1

Sol:

Instal analysis tool pak in excel go to

data>data analysis>regression

Book1 Excel Tell me what you want to do . Share File Home Insert Page Layout Formulas Review View Adabala Naveenkumarvenkat show Queries EGroupData Analysis Flash Fil Consolidate Connections -Remove Dupcates Relationships Ungroup,-- 2,solver るReapply 目Properties -L Recent Sources All- Edit Links What-If Forecast Subtotal Analysis Sheet Get External New Data Query Text to Refresh Sort Filter Data Validation . Advanced Columns All-Edit Links l Sort Filer Get&Transforn Sort &Filter Data Tools Analyze F11 Intenal Confidential Restricted Sensitivity Not set 63.3 371.9164.4 5.9 402.9 163 71.2403.7173.2 68.3419.4180.1 719444.4190.1 163.9 Data Analysis Analysis Tools Cuvariance Descriptive Statistics OK t-lest Iwo-Sample for Variances Fourier Analysis Histugrarn Maving Average Random NumberGencration Rank and Percentile 10 12 14 15 16 18 19 Sheet2 Sheet6 | Sheet5 Sheet3 Sheet4 Sheet 1 +100% 928 AM 4/20/2019 5 Type here to search

select the data

Book1 Excel Tell me what you want to do . Share Home Insert Page Layout Fomulas Review View Adabala Naveenkumarvenkat Consolidate Relationships Manage Data Model Group Ungroup Data Analysis Flash Fill External New From Ober es Remove Duplicates : Data Validation " What-If Forocast Subtotal Analysis Sheet Refresh Advanced Columns Recent Sources All- Edit Links All, Edit Links 치 Sort Filterる DataQuery Get&Transforn Sort &Filter Data Tools Analyze A1 Inteal Confident Reyression Sensitivity Not set OK SA$1 SAS6 Input y Range Input & Range Labels 63.3 371.9 164.4 65.9 402.9163 71.2 4037173.2 68.3 419.4180.1 71.9 444.4190.1 163.9 Constant is Lero Confidence Level:95 Output options OQutput Range: New Worksheet Ply: 10 New Workbook 12 Residuals Residual Plots Line Fit Plots 14 15 16 Normal Probability Normal Prubability Plols 18 19 Sheet2 Sheet6 Sheet5 Sheet3 Sheet4 Sheet1 9.29 AM Type here to search 4/20/2019 5

you i will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.831303
R Square	0.691065
Adjusted R Square	0.38213
Standard Error	2.83326
Observations	5
ANOVA
	df	SS	MS	F	Significance F
Regression	2	35.91328	17.95664	2.236929	0.308935
Residual	2	16.05472	8.027361
Total	4	51.968
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	20.32679	22.70638	0.895202	0.465147	-77.3709	118.0244
x1	0.067788	0.123362	0.549499	0.637824	-0.463	0.598573
x2	0.115319	0.294779	0.391205	0.733389	-1.15301	1.383651

From output:

r^2=0.691065

Adjusted R Square

0.38213

C) About BLANK % (number value) of the variation in y can be explained by the relationship between the variables

0.691065=0.691065*100=69.11% variation in y is explained by model.