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$).$ See data to the right.

Required

$$a$.$ Determine the cost formula for $6582-$D$.$ Use Excel$$s 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.

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 create custom 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 Excel$$s Goal Seek tool to determine the sales price required to meet the $$7,500$ gross profit goal. Management believes monthly sale in units 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.

d$.$ Discuss how Goal Seek $($or any other Excel tool$)$ might help management with CVP Analysis.

Units Indirect Indirect Other

Produced Materials Labor Overhead

January $1,000$ $$5,150\mathrm{.}00$ $$21,750\mathrm{.}00$ $$31,510\mathrm{.}00$

February $1,2005,756\mathrm{.}0022,120\mathrm{.}0033,391\mathrm{.}00$

March $1,1505,558\mathrm{.}5022,180\mathrm{.}0031,903\mathrm{.}00$

April $9805,097\mathrm{.}0021,535\mathrm{.}0030,995\mathrm{.}20$

May $1,0505,354\mathrm{.}0021,797\mathrm{.}5032,572\mathrm{.}50$

June $1,1505,558\mathrm{.}5022,065\mathrm{.}0032,332\mathrm{.}00$

July $1,0905,388\mathrm{.}5022,108\mathrm{.}0032,126\mathrm{.}10$

August $1,1705,675\mathrm{.}6021,993\mathrm{.}4033,927\mathrm{.}40$

September $9254,957\mathrm{.}7521,493\mathrm{.}7530,066\mathrm{.}50$

October $9805,097\mathrm{.}0021,662\mathrm{.}4030,485\mathrm{.}00$

November $1,0255,224\mathrm{.}7521,773\mathrm{.}7532,048\mathrm{.}75$

December $8254,686\mathrm{.}2521,418\mathrm{.}7529,291\mathrm{.}25$

$1,045$ $$5,291\mathrm{.}99$ $$21,824\mathrm{.}80$

a$.$

Manufacturing Overhead Costs

Variable Cost per Unit Total Fixed Cost

Direct materials

Direct labor $-$

Indirect materials

Indirect labor

Other overhead

Total $$-$ $$-$

Cost$-$estimating equation:

c$.$

Goal Seek

Number of units

Sales price

Variable costs

Fixed costs

Gross profit

$***($SOLVE FOR MANUFACTURING OVERHEAD COSTS$($VARIABLE PLUS FIXED$),$ COST ESTIMATING EQUATION, AND SALES PRICE REQUIRED FOR $7500$ GROSS PROFIT GOAL$)***$