Question 2.

Look at the data set on Moodle (20 firms of data). Use this data to run an Excel regression model to predict whether a firm will go bankrupt or not.

If the Y value of the firm exceeds 0.5, the firm is predicted to be not bankrupt NB. Using that critical value for Y, make a prediction B or NB for each firm in the set.

Compare the predictions with the actuals to find how many Type I errors are made and how many Type II errors. A Type I error is when you predict NB but the firm goes bankrupt.

Extra Question 3.

1. Instead of 0.5, you can try different hurdle points to see how this affects the frequency of Type I and Type II errors. If we assume that the cost of a type I error is 3 and the cost of a Type II error is 1, find the hurdle point that =gives the lowest average cost across the sample.
2. Is the hurdle that gives the lowest expected cost best? Instead, consider also the variance of the cost (remember that higher variance is bad). Trying increasing hurdles of 0,1, 0.2, 0.3, ....etc. plot a graph of the expected cost against the variance of cost and come to a conclusion about which is the best hurdle point.

SUMMARY OUTPUT			You can change the green cells
Regression Statistics
Multiple R	0.652677
R Square	0.425987
Adjusted R Square	0.318359
Standard Error	0.423532
Observations	20
ANOVA											3	Type 1 error cost
	df	SS	MS	F	Significance F						1	Type 2 error cost
Regression	3	2.129934	0.709978	3.957975	0.027493
Residual	16	2.870066	0.179379
Total	19	5
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.23907	0.197539	1.210241	0.243763	-0.17969	0.65783409	-0.17969	0.657834
X Variable 1	0.348304	0.129368	2.692349	0.016021	0.074056	0.62255268	0.074056	0.622553
X Variable 2	-0.12267	0.103381	-1.18662	0.2527	-0.34183	0.09648484	-0.34183	0.096485
X Variable 3	0.375785	0.184133	2.04084	0.058122	-0.01456	0.76612875	-0.01456	0.766129
								0.3	<----hurdle
				firm	outcome	Y estimate		Prediction				Error Cost		Sqd dev
RESIDUAL OUTPUT			1	1	1.34215277		1		OK		0		0.64
				2	1	0.86243128		1		OK		0		0.64
Observation	Predicted Y	Residuals		3	1	0.41521135		1		OK		0		0.64
1	1.342153	-0.34215		4	1	0.94195109		1		OK		0		0.64
2	0.862431	0.137569		5	1	0.78281439		1		OK		0		0.64
3	0.415211	0.584789		6	1	0.16684822		0		error		1		0.04
4	0.941951	0.058049		7	1	0.55658448		1		OK		0		0.64
5	0.782814	0.217186		8	1	0.59844618		1		OK		0		0.64
6	0.166848	0.833152		9	1	0.36817562		1		OK		0		0.64
7	0.556584	0.443416		10	1	1.0953188		1		OK		0		0.64
8	0.598446	0.401554		11	0	0.39150058		1		error		3		4.84
9	0.368176	0.631824		12	0	0.31029927		1		error		3		4.84
10	1.095319	-0.09532		13	0	0.22765837		0		OK		0		0.64
11	0.391501	-0.3915		14	0	0.36139001		1		error		3		4.84
12	0.310299	-0.3103		15	0	0.22709964		0		OK		0		0.64
13	0.227658	-0.22766		16	0	0.12436256		0		OK		0		0.64
14	0.36139	-0.36139		17	0	0.2879538		0		OK		0		0.64
15	0.2271	-0.2271		18	0	0.31117108		1		error		3		4.84
16	0.124363	-0.12436		19	0	0.36381284		1		error		3		4.84
17	0.287954	-0.28795		20	0	0.26481768		0		OK		0		0.64
18	0.311171	-0.31117										0.8	<-----expected error cost
19	0.363813	-0.36381
20	0.264818	-0.26482										1.7474	<------variance of cost
												1.7474	<---variance another way