Jenson Manufacturing Is Developing Cost Formula for Future Planning and Cost

Jenson Manufacturing is developing cost formula for future planning and cost control.Utilities is one of the mixed costs associated with production.The cost analyst has suggested that units produced be used as the activity base to determine the fixed and variable costs and the cost formula for utility costs.The controller feels the most appropriate cost base is direct labour hours.Below is information collected over the past 8 months:
$\begin{array}{|r|r|r|r|}\hline \text { Month } & \text { Units Produced } & \begin{array}{r}\text { Direct Labour } \\\text { Hours }\end{array} & \text { Utilities Cost } \\\hline 1 & 1,100 & 500 & \$ 7,200 \\\hline 2 & 900 & 300 & 6,500 \\\hline 3 & 1,300 & 400 & 7,100 \\\hline 4 & 1,500 & 600 & 7,500 \\\hline 5 & 1,720 & 1,000 & 10,800 \\\hline 6 & 1,700 & 900 & 10,000 \\\hline 7 & 1,700 & 800 & 9,200 \\\hline 8 & 1,670 & 1,100 & 12,000 \\\hline\end{array}$
Required:
Use the least squares regression method to determine the cost formula for utilites cost assuming units produced is the independent variable.Calculate the R-squared.Use the same method to determine the cost formula and R-squared assuming direct labour hours is the independent variable.Use a spreadsheet or calculator to determine the slope and intercept.Do you recommend the company use units produced or direct labour hours as a cost base? Explain.
$\begin{array}{|l|r|r|}\hline \text { Answer: } & \text { Units Produced } & \text { Direct Labour Hours } \\\hline \text { Intercept } & 1174.559719 & 4074.166667 \\\hline \text { Slope } & 5.254833671 & 6.733333333 \\\hline \text { R-squared } & 0.680142544 & 0.962287567 \\\hline \text { Cost Formula } & Y=1174.56+5.25 \mathrm{x} & \mathrm{Y}=4074.17+6.73x\\\hline\end{array}$