Problem
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 others). (That information is included in the Fixed and Variable Data Set.xlsx file available on the textbook's website. A video demonstrating Excel tools used to answer the questions in this problem is also available on the website.)
Task
I. Determine the cost formula for 6582-D. Use Excel's regression analysis tool to calculate the fixed and variable manufacturing overhead costs. Round elements to two decimal places.
i. Check the 95% confidence level. Consider checking the box to add a line fit plot for each indirect cost element to show the relationship in chart form. (You may need to change the minimum bound on the horizontal axis to 700 to see the line clearly.)
II. Use the prior year's data to create a graph of the various overhead costs by month.
i. Create a combo chart as follows:
• The primary vertical axis is dollars, and the secondary vertical axis is units of production.
• The horizontal axis is Months.
• Units produced should be clustered column type; the overhead cost elements should be line type. (Units produced would be linked to the secondary vertical axis.
III. Use Excel's Goal Seek tool to determine the sales price required to meet the $7,500 gross profit goal. Management believes monthly sales will average 1,500 next year. Assume the company will not maintain any inventory of finished goods.
IV. Discuss how Goal Seek (or any other Excel tool) might help management with CVP Analysis.