Fall 2016 Cost Accounting CVP Spreadsheet Project
Instructions: You are to create a spreadsheet that will easily perform the sensitivity analysis required below. All calculations should be properly referenced to data placed elsewhere in the spreadsheet, so that changing the appropriate data item automatically updates the analysis. The excel file should be emailed to me by Friday, October 14th.
Kenyon Enterprises is considering several options for the upcoming year's production and sales of its two items. Current sales mix is 65% Product A and 35% Product B. Total sales last year were 20,000 units. Tax rate was and is expected to be 22%. Unit sales and cost information is as follows:
Product A Product B
Selling price $ 35 $ 55
Variable Mfg. Cost 12 18
Variable Selling Cost 3 7
Fixed Mfg. Cost $40,000 $60,000
Fixed Selling & Administrative Costs (allocated) $91,000 $91,000
Required calculations: For EACH scenario for the coming year, provide the following:
# contribution margin per unit
# weighted average contribution margin
# breakeven point in units (in total and per unit)
# breakeven point in sales dollars
# contribution margin format income statement (for original scenario, used last year's sales)
For each scenario, provide any additional information needed beyond what is required above, in order to fully answer the question asked of each scenario. For all of the scenarios, the starting point should be the prior year's information, so any increases and decreases should be treated independently and should begin from last year's information. Your "data box" should include all unit information and relevant total amounts that allow you to calculate all of the items listed under the required calculations without any numerical amounts in the cells where you provide your answer.
Scenario 1: What level of sales is needed (for each product) if fixed manufacturing costs increase by 10% and selling price is increased by 5% for each product to compensate? Required operating profit is $225,000.
Scenario 2: How many units of each product are needed if sales mix is altered to sell proportionately more of Product B so that the new sales mix is 50-50? Calculate new break even units, and also calculate units needed to earn the target operating profit of $225,000.
Scenario 3: Opportunity to alter the cost structure: If Kenyon invests in a more computerized manufacturing process, variable manufacturing costs for each product per unit would be cut by 1/3 but fixed manufacturing costs would increase by $60,000 for each product. At what level of sales does this benefit Kenyon? At current target levels for sales, which cost structure is more profitable for Kenyon? Which do you recommend?