Determining Fixed vs$.$ Variable Cost Components Using Excel $($Diagnostic Analytics$)$

Required

Note: Round all answers to two decimals. analysis, along with the direct cost information to estimate the cost equation.

Hint: Click on $95\backslash \%$ confidence level box and check the box to add a Line fit plot for each indirect cost element to show the relationship in chart form.

Hint: You may need to change the minimum bound on the horizontal axis to $700$ to see the line clearly on each chart.

Cost$-$estimating equation: $\backslash ($ Y$=\backslash$$ $\backslash )$

b$.$ Use the prior year data to create a graph of the various overhead costs by month. Create a Combo chart as follows. Hint: Under the Insert tab, click on Insert Combo chart.

$1.$ The primary vertical axis in dollars, and the secondary vertical axis is units of production. Hint: Click the Secondary axis box for units produced.

$2.$ The horizontal axis is Months.

$3.$ Units produced should be a Clustered column type; the overhead cost elements should be Line type.

$4.$ Describe the trend in the overhead components based on a review of your chart.

Indirect materials and indirect labor costs are relatively stable, whereas other overhead showed more fluctuations over the year. fixed costs, and a formula for gross profit. Use the Goal Seek tool to find the sales price. Goal seek is found under the Data tab, and under What$-$if Analysis.

d$.$ In what areas would Goal Seek help management in decision$-$making?