Please complete using the provided excel templates, imaged above.
Case 7-65 Cost-Volume-Profit with Multiple Products, Sales Mix Changes, Changes in Fixed and Variable Costs Artistic Woodcrafting Inc. began several years ago as a one-person, cabinet-making operation. Employees were added as the business expanded. Last year, sales volume totaled $850,000. Volume for the first five months of the current year totaled $600,000, and sales were expected to be $1.6 million for the entire year. Unfortunately, the cabinet business in the region where Artistic is located is highly competitive. More than 200 cabinet shops are all competing for the same business. Artistic currently offers two different quality grades of cabinets: Grade I and Grade II, with Grade I being the higher quality. The average unit selling prices, unit variable costs, and direct fixed costs are as follows: Grade I Grade II Unit Price $3,400 1,600 Unit Variable Cost $2,686 1,328 Direct Fixed Cost $95,000 95,000 Common fixed costs (fixed costs not traceable to either cabinet) are $35,000. Currently, for every three Grade I cabinets sold, seven Grade II cabinets are sold. 3. Artistic can buy computer-controlled machines that will make doors, drawers, and frames. If the machines are purchased, the variable costs for each type of cabinet will decrease by 9%, but common fixed cost will increase by $44,000. Compute the effect on operating income, and also calculate the new break-even point. Assume the machines are purchased at the beginning of the sixth month. Fixed costs for the company are incurred uniformly throughout the year. 4. Refer to the original data. Artistic is considering adding a retail outlet. This will increase common fixed cost by $70,000 per year. As a result of adding the retail outlet, the additional publicity and emphasis on quality will allow the firm to change the sales mix to 1:1. The retail outlet is also expected to increase sales by 30%. Assume that the outlet is opened at the beginning of the sixth month. Calculate the effect on the company's expected profits for the current year, and calculate the new break-even point. Assume that fixed costs are incurred uniformly throughout the year. D E F G H A . B 1. Initial Problem Data 2 3 Sales Value 4 First 5 months $600,000 5 Full Year $1,600,000 6 7 Common Fixed Costs 8 $35,000 9 Grade 1 Grade 2 Sales Mix Price Per Unit Variable Costs Per Unit Fixed Costs 0.3 $3,400 $2,686 $95,000 0.7 $1,600 $1,328 $95,000 IU 1 Unit Sales of Grade 1 and Grade 2 Cabinets 2 Let X = a package of grade 1 and grade 2 cabinets 1 5 S1=sales mix grade 1 and S2 sales mix grade 2 5 - 3 sales=S1*x*(price grade 1)+S2*x*(price grade 2) 2 0 Package Units Units Grade 1 Units Grade 2 1 2 Breakeven Quantity CM=Contribution Margin Sales Mix S1=% sales Grade 1 S2=%sales Grade 2 Weighted Average Contribution Margin=51*CM grade 1+52*CM Grade2 Breakeven Quantity for a package of a Package=(fixed cost)/(weighted average CM) Breakeven Package Quantity Breakeven Grade 1 Breakeven Grade 2 Purchase of new machinery to use for the last 7 months of the accounting year Varaible costs fall by 9% to: Grade 1 Grade 2 2444 1208 Fixed costs increase by $44,000 due to the new machine Sales for the last seven months of the year are $1,000,000 The new contribution margins: CM grade 1 CM grade 2 The new weighted average CM is Weighted Average CM the new fixed costs Fixed Costs Let X= package of cabinets sales=S1*x*(price grade 1)+S2*x*(price grade 2) # of packets last 7 months Grade 1 Units Grade 2 Units Change in CM=new grade 1 units* (new CM grade 1-old CM grade 1)+new grade 2 units*(new CM grade 2-old CM grade 2) Change in CM New Breakeven Quantity for Package=(new fixed costs)/(new weighted average CM) New Breakeven Quantity of a Package New Breakeven Grade 1 New Breakeven Grade 2 Add a retail store and use the initial data without the new machine All data and analysis are for the last 7 months of the year Common fixed costs increase by $70,000 Sales mix changes to 50% grade 1 and 50% grade 2 Sales increase by 30% to $1,300,000 Let X =packet of grade 1 and grade 2 packages sales last 7 months=Sales mix grade 1*x*(price grade 1)+Sales mix grade 2*x*(price grade 2) Unit Sales for Package Unit Sales Grade 1 Unit Sales Grade 2 Change in CM=CM grade 1*(new units grade 1-old units grade 1)+CM grade 2*(new units grade 2-old units grade 2) Change in CM Change in fixed costs over the last 7 months change in operating income-change in CM-change in fixed costs Change in operating income
