Question

	Patient Load	Administrative Cost
January	1540	13900
February	550	7000
March	440	6000
April	1100	9800
May	1430	11900
June	990	9600
July	1210	10200
August	220	4100
September	770	9300
October	1320	11100
November	660	8300
December	1980	16100

Estimated range of monthly activity in the future will be 750-1,500 patients.

A. Prepare a scatter diagram in Excel, then MANUALLY draw a ‘best fit line’ that you feel fits the data. Clearly present a cost equation based on your scatter diagram line. (Note—you may use the ‘insert, then ‘shapes’ function in Excel to manually draw a line, or you can print your diagram and draw the line in pen. HOWEVER—if you have Excel insert a line, the program will run regression on the data to put in the line and thus you will NOT have fulfilled the requirements.)

B. Use the high-low method to estimate the cost behavior for the clinic’s administrative costs. Clearly label the final cost formula.

C. Perform a least squares regression analysis on the data to estimate the cost behavior. Clearly present the final cost formula. Include a brief analysis of the R2 factor for the regression on your Excel sheet.

Answer 1

A.

Month	Patient Load	Administrative Cost
January	1540	13900
February	550	7000
March	440	6000
April	1100	9800
May	1430	11900
June	990	9600
July	1210	10200
August	220	4100
September	770	9300
October	1320	11100
November	660	8300
December	1980	16100

Scatter diagram uploaded. line made using excel shapes

B. High Low Method

VCx	=	16100 - 4100
		1980 - 220
	=	12000
		1760
	=	6.81
TCx	=	VCx + TFC
16100	=	(6.81 X 1980) + TFC
16100	=	13500 + TFC
TFC	=	2600
TCx	=	VCx + TFC
TCx	=	6.81x + 2600

C. regression Done in Excel

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.981347
R Square	0.963042
Adjusted R Square	0.959346
Standard Error	666.1316
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.16E+08	1.16E+08	260.5747	1.72E-08
Residual	10	4437314	443731.4
Total	11	1.2E+08
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	3331.06	443.0964	7.517686	2.02E-05	2343.779	4318.34	2343.779	4318.34
X Variable 1	6.333111	0.392329	16.14233	1.72E-08	5.458946	7.207275	5.458946	7.207275

TCx = 6.33x + 3331