Determining Fixed vs. Variable Cost Components Using Tableau (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 new sales price.
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). That information is included in the following Excel file: Fixed and Variable Data Set Tableau.xlsx.
Note: Round all amounts to two decimals.
1. Use the Trendline tool in Tableau to determine the fixed and variable components for each of the three manufacturing overhead components. Use the default model type (linear).
a. What is the cost formula for indirect materials? Y = $ 200
(X) +$ 1.25
b. What is the cost formula for indirect labor? Y = $ 100
(X) +
0.5
(X)+$1
c. What is the cost formula for other manufacturing overhead? Y = $ 300
2. What is the cost formula for 6582-D (per month)? Note: Use the answers above to arrive at the correct cost formula for 6582-D.
Y = $
(X)+$
3. Determine the minimum sales price Genessee could charge to achieve a monthly gross profit of $7,500 next year. Management expects unit sales will average 1,500 per month. Hint: You won't be
able to use Tableau for this. Start by determining the CVP formula. $
