Required information

Excel Analytics $5-43$: Interpretation of Cost Estimation Results: Visualization

Skip to question

$[$The following information applies to the questions displayed below.$]$

Fiske Corporation manufactures a popular regional brand of kitchen utensils. The design and variety has been fairly constant over the last three years. The managers at Fiske are planning for some changes in the product line next year, but first they want to understand better the relation between activity and factory costs as experienced with the current products. Discussions with the plant supervisor suggest that overhead seems to vary with labor$-$hours, machine$-$hours, or both. The following data were collected from from the last three years of operations:

Quarter Machine$-$Hours Labor$-$Hours Factory Costs

$118,85014,905$ $ $3,388,671$

$218,59015,4773,425,136$

$317,48016,7203,617,144$

$419,24015,9833,573,240$

$521,28017,5013,812,284$

$619,63017,3693,777,312$

$719,24015,2903,531,726$

$818,85014,3663,369,102$

$918,46015,9943,512,487$

$1020,67016,9953,730,734$

$1117,55014,2783,324,915$

$1218,46017,6443,723,786$

Results from a multiple regression of factory costs on labor$-$hours and machine$-$hours using the data of Fiske Corporation are as follows:

Equation:

Overhead $=$ $$1,401,352+($$$134\mathrm{.}90\backslash$times Labor$-$hours$)$

Statistical data

Correlation coefficient $0\mathrm{.}963$

R$20\mathrm{.}928$

Results from a regression of factory costs on machine$-$hours are as follows:

Equation:

Overhead $=$ $$1,770,363+($$$94\mathrm{.}36\backslash$times Machine$-$hours$)$

Statistical data

Correlation coefficient $0\mathrm{.}625$

R$20\mathrm{.}391$

Results from a multiple regression of factory costs on labor$-$hours and machine$-$hours are as follows:

Equation:

Overhead $=$ $$993,339+($$$33\mathrm{.}79\backslash$times Machine$-$hours$)+($$$120\mathrm{.}26\backslash$times Labor$-$hours$)$

Statistical data

Correlation coefficient $0\mathrm{.}984$

R$20\mathrm{.}967$

Adjusted R$20\mathrm{.}960$

Excel Analytics PR $5-43$: Interpretation of Cost Estimation Results: Visualization

Required

Use the high$-$low method to estimate the fixed and variable portions of factory costs based on labor$-$hours.

Managers expect the plant to operate at $16,000$ labor$-$hours next quarter. Assuming the relationship remains the same with the new product line, what are the estimated quarterly factory costs?

Use the high$-$low method to estimate the fixed and variable portions of factory costs based on machine$-$hours.

Managers expect the plant to operate at $21,000$ machine$-$hours next quarter. Assuming the relationship remains the same with the new product line, what are the estimated quarterly factory costs?

Prepare a scattergraph based on the factory cost and labor$-$hour data.

Prepare a scattergraph based on the factory cost and machine$-$hour data.

Managers expect the plant to operate at $16,000$ labor$-$hours next quarter. Assuming the relationship remains the same with the new product line and using the results from the first simple regression above, what are the estimated quarterly factory costs?

Managers expect the plant to operate at $21,000$ machine$-$hours next quarter. Assuming the relationship remains the same with the new product line and using the results from the second simple regression above, what are the estimated quarterly factory costs?

Managers expect the plant to operate at $16,000$ labor$-$hours and $21,000$ machine$-$hours next quarter. Assuming the relationship remains the same with the new product line and using the results from the multiple regression above, what are the estimated quarterly factory costs?

The relevant data has been included within the following file:

Click here to access the Assignment $5-43$ Student File.

Assignment Steps:

Download the Assignment $5-43$ Student File and open it within Microsoft Excel.

Within cell E$2$ enter the formula $=$ IF$($C$2=$ MAX $($$C$$2$:$C$$13),$ "Yes", $"$No$").$ This Excel formula identifies the quarter in which the company experienced the highest number of labor hours.

Copy cell E$2$ and paste within the range E$3$:E$13.$

Within cell F$2$ enter the formula $=$ IF $($C$2=$ MIN $($$C$$2$:$C$$13),$ "Yes", $"$No$").$ This Excel formula identifies the quarter in which the company experienced the lowest number of labor hours.

Copy cell F$2$ and paste within the range F$3$:F$13.$

Within cell H$15$ enter the formula $=($D$13$ D$12)/($C$13$ C$12).$

Within cell H$16$ enter the formula $=$ D$13($H$15*$C$13).$ Note that the result of the formula would have been the same if the fixed costs and labor hours from the lowest quarter $($c

Questions Answers

$1.$ Which of the following statements regarding these analyses is accurate? Unlike a regression analysis, the high$-$low approach uses all observations and is therefore a preferable approach.

$2.$ Which of the following conclusions can be drawn from these simple regression analyses?

$3.$ What are the estimated quarterly factory costs based on th

$4.$ What are the estimated quarterly factory costs, assuming $21,000$ machine$-$hours?

$5.$ What are the estimated quarterly factory costs, assuming $16,000$ labor$-$hours?