The Hard Rock Mining Company is developing cost formulas for management planning and decision making purposes. The Company$$s cost analyst has concluded that utilities cost is a Mixed Cost and he is attempting to find a base with which the cost might be closely related correlated. The controller has suggested that tones mined might be a good base to use in developing a cost formula. The production superintendent disagrees; she thinks that direct labor hours would be a better base. The cost analyst has decided to try both bases and has assembled the following information:

Quarter Tones Mined DL Hours Utilities Cost

$1$st $15,0005,00050,000$

$2$nd $11,0003,00045,000$

$3$rd $21,0004,00060,000$

$4$th $12,0006,00075,000$

$1$st $18,00010,000100,000$

$2$nd $25,0009,000105,000$

$3$rd $30,0008,00085,000$

$4$th $28,00011,000120,000$

Required:

$1.$ Using tones mined as independent variable:

a$.$ Prepare scattergraph that plots the tones mined on the horizontal axis and utilities cost on the vertical axis.

b$.$ Determine a cost formula for utilities cost using least$-$squares regression. Express the formula in terms of Y$=$a $+$ bX

$2.$ Using direct labor hours as independent variable:

a$.$ Prepare scattergraph that plots the direct labor hours on the horizontal axis and utilities cost on the vertical axis.

b$.$ Determine a cost formula for utilities cost using least$-$squares regression. Express the formula in terms of Y$=$a $+$ bX

$3.$ Would you recommend that the Company use tones mined or direct labor hours as a base for planning utilities cost? Why?

I WANT YOU TO ANSWER THESE QUESTIONS IN EXCEL FORMAT