Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 wasnt 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 years 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 years monthly production levels and manufacturing overhead costs (indirect materials, indirect labor, and other).

image text in transcribed

Required

Note: Round all answers to two decimals.

a. Determine the cost formula for 6582-D. Use Excels regression analysis tool to develop a cost-estimating equation for total fixed and variable manufacturing costs. Use the regression tool three times (once for indirect materials, 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.

Manufacturing Overhead Costs
Variable Cost per Unit Total Fixed Cost
Direct materials

Answer

Answer

Direct labor

Answer

Answer

Indirect materials

Answer

Answer

Indirect labor

Answer

Answer

Other overhead

Answer

Answer

Total

Cost-estimating equation: Y = $Answer + $Answer 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.

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.

c. Use Excels 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. Hint: In a schedule in Excel, enter number of units, variable manufacturing cost per unit and total 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.

$Answer

\begin{tabular}{|c|c|c|c|c|} \hline & \begin{tabular}{c} Units \\ Produced \end{tabular} & \begin{tabular}{l} Indirect \\ Materials \end{tabular} & \begin{tabular}{r} Indirect \\ Labor \end{tabular} & \begin{tabular}{l} Other \\ Overhead \end{tabular} \\ \hline January & 1,000 & 5,150.00 & $21,750.00 & $31,510.00 \\ \hline February & 1,200 & 5,756.00 & 22,120.00 & 33,391.00 \\ \hline March & 1,150 & 5,558.50 & 22,180.00 & 31,903.00 \\ \hline April & 980 & 5,097.00 & 21,535.00 & 30,995.20 \\ \hline May & 1,050 & 5,354.00 & 21,797.50 & 32,572.50 \\ \hline June & 1,150 & 5,558.50 & 22,065.00 & 32,332.00 \\ \hline July & 1,090 & 5,388.50 & 22,108.00 & 32,126.10 \\ \hline August & 1,170 & 5,675.60 & 21,993.40 & 33,927.40 \\ \hline September & 925 & 4,957.75 & 21,493.75 & 30,066.50 \\ \hline October & 980 & 5,097.00 & 21,662.40 & 30,485.00 \\ \hline November & 1,025 & 5,224.75 & 21,773.75 & 32,048.75 \\ \hline December & 825 & 4,686.25 & 21,418.75 & 29,291.25 \\ \hline \end{tabular}

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Investing In People Financial Impact Of Human Resource Initiatives

Authors: John W. Boudreau, Wayne F. Cascio, Alexis A. Fink

3rd Edition

1586446096, 978-1586446093

More Books

Students also viewed these Accounting questions

Question

Does it highlight your accomplishments rather than your duties?

Answered: 1 week ago

Question

Does it clearly identify what you have done and accomplished?

Answered: 1 week ago

Question

Does it avoid using personal pronouns (such as I and me)?

Answered: 1 week ago