Question

Use the Financial database from “Excel Databases.xls” (Google drive link below). Use Total Revenues, Total Assets, Return on Equity, Earnings Per Share, Average Yield, and Dividends Per Share to predict the average P/E ratio for a company. Use Excel to perform a forward selection regression analysis. Assume a 5% level of significance. Which independent variables are in the final model?   Select all that apply.

		Total Revenues
		Total Assets
		Return on Equity
		Earnings Per Share
		Average Yield
		Dividends Per Share

https://drive.google.com/file/d/1yT22MXE0QAFJ0IOFMJosHCnqVTORiLM-/view?usp=sharing

Answer 1

Solution:

in excel install Analysis tool pak and then go to

Data>Data analysis >Regresion select P/E ratio as Y

and  Total Revenues, Total Assets, Return on Equity, Earnings Per Share, Average Yield, and Dividends Per Share as X

Click OK

So Financial - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do - Consolidate Relationships et External Data * Show Queries From Table New Query-Lo Recent Sources Get & Transform Clear Reapply Advanced Connections Properties Refresh All - Edit Links Connections 2 Sort Filter Flash Fill HI Remove Duplicates Text to Columns Data Validation Data Tools What-If For Analysis SE Forecast Sort & Filter 3 x fc 16.7 Regression ? X Type Input Input Y Range: | OK 1 SIS1:S15101 SCS1:SH$1011 Share Avera 0.22 0.63 0.36 Cancel Input X Range: Help 0.6 Labels Confidence Level: Constant is Zero 95 % 1.4 0.34 А Company AFLAC Albertson's Allstate Amerada Hess American General American Stores Amoco Arco Chemical Ashland Atlantic Richfield Bausch & Lomb Baxter International Bristol-Myers Squibb Burlington Coat Central Maine Power 1.4 Total Revenues Total Assets Retu 7251 29454 14690 5219 20106 80918 8340 7935 3362 80620 19139 8536 36287 32489 3995 4116 14319 19272 25322 1916 2773 6138 8707 16701 14977 1777 775 954 2299 7777 Output options O Output Range: O New Worksheet Ply: O New Workbook Residuals Residuals standardized Residuals 2.8 1.1 2.83 1.04 1.13 1.52 0.02 09 Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

You will get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.432778402
R Square	0.187297145
Adjusted R Square	0.134864703
Standard Error	69.04214864
Observations	100
ANOVA
	df	SS	MS	F	Significance F
Regression	6	102167.065	17027.84	3.572161	0.003141
Residual	93	443314.1009	4766.818
Total	99	545481.1659
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	92.52650832	18.86379472	4.904979	3.96E-06	55.06675	129.9863
Total Revenues	-0.000256372	0.000467506	-0.54838	0.584743	-0.00118	0.000672
Total Assets	9.93576E-05	0.000171438	0.579553	0.563618	-0.00024	0.00044
Return on Equity	-0.16180255	0.305318763	-0.52995	0.597412	-0.76811	0.4445
Earnings per Share	-29.80440213	6.979598256	-4.27022	4.71E-05	-43.6645	-15.9443
Average Yield	-18.58923715	6.310208613	-2.9459	0.004069	-31.1201	-6.05841
Dividends per Share	58.02948964	18.93443694	3.064759	0.002851	20.42945	95.62953
				p<0.05 ,significant variables

From output

for the variables earning per share,average yield and dividends per share are significant variables as p<0.05

ANSWER:

Earnings Per Share
Average Yield
Dividends Per Shar