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

Genessee Industries introduced a new product last year $(6582-$D$).$ Although it was very popular, it wasn$$t very profitable. Management has asked you to provide them with information to help them set a sales price that will provide them with a monthly gross profit of $$7,500.($Last year$$s sales price was $$75$ per unit.$)$

You know that the direct costs per unit are $$25$ for direct materials and $$5$ for direct labor. You are given information about last year$$s monthly production levels and manufacturing overhead costs $($indirect materials, indirect labor, and other$).$

once for indirect labor, and once for other overhead$).$ Use the information from each analysis, along with the direct cost information to estimate the cost equation.

Hint: Click on $95\%$ 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: $Y=!$

$x+!$

$x$

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.

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

The horizontal axis is Months.

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

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

All components of overhead costs showed an increasing trend in costs over the year.

$7500$

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