Question

Fancy Millwork has a factory that produces custom kitchen cabinets. It has multiple product lines ment, we Materials and labor for the cabinets are determined by each job. To simplify the assign ill assume the following average costs e following information shows Fancy Millwork's cost structure of 2018. The materials include S2,000 for a custom kitchen. The hourly rate is $10. T manufacturing costs Th for the wood and other materials on a per job basis. It requires 40 hours of labor on average he sales price will be set at a markup of 80% on The company estimates that it will have 32,000 direct labor hours in total for the kitchen cabinets It assumes 800 units are sold on average per year. A breakdown of estimated yearly costs related to the kitchen cabinets follows: Hint: vou need to distinguish between product costs (MOH) and period costs Salaries- office & administrative Salaries for factory supervisors and janitor Office Rent Factory Rent Office Utilities and Misc office expenses (based on units sold) Sales Travel (based on units sold Insurance office Depreciation - office equipment Depreciation for factory equipment S 400,000 S 200,000 90,000 S 80,000 S 20,000 S 24,000 14,000 45,000 S 70,000 S 15,000 S 50,000 10,000 S 80,000 dvertising Sales commissions (based on units sold Factory Property taxes Maintenance for factorv equipment General requirement: You need to create a schedule for each of the following questions. Use a new worksheet (not workbook) to answer each question. You can combine Q1 and Q2 in one sheet for comparison purposes. All schedules should be included in excel and use formulas with professional formatting, Your spreadsheets should contain formulas and referring numbers. Not using formulas will result in a minimum one letter-grade reduction. I recommend linking the schedules though this is not required. That is, you cannot (as some of you did in the last exam) type in a formula and then type in an answer Your Excel spreadsheet should look like the Figure a. and not like figure b? It cont effective tool to communicate with the managers) Figure b (see how the student prepares ains no numbers linked to other numbers- and thus missing the point of using Excel as an Kitchen Cabinets- Sale Price Calculation Materials (Sxxxxxxxx Units) Labor (xxx xx Sxxxxx) Overhead (OKxxxxx) Total Cost st Per Unit (Total Cost/ 800 U 00x Sale Price Per Unit @e Ma

Help with Question 2, do a CVP Analysis. Include an explanation on the side so that I can understand what you did.

Answer 1

Computation of Total Manufacturing Overhead:

Salary of Factory Supervisors and Janitors	$ 200,000
Factory Rent	80,000
Depreciation on Factory Equipment	70,000
Factory Property Taxes	10,000
Maintenance for Factory Equipment	80,000
Estimated Total Manufacturing Overhead	$ 440,000

Predetermined manufacturing overhead rate = Estimated Total Manufacturing Overhead / Estimated Direct Labor Hours = $ 440,000 / 32,000 DLH = $ 13.75 per DLH

Computation of selling price per unit:

Direct Materials per unit	$ 2,000
Direct Labor per unit	400
Manufacturing Overhead per unit ( 40 DLH x $ 13.75 )	550
Total Manufacturing Cost per unit	$ 2,950
Markup of 80 %	2,360
Sales Price per unit	$ 5,310

Variable cost per unit = $ 2,000 + $ 400 + $ 117.50 = $ 2,517.50

Contribution margin per unit = $ 5,310 - $ 2,517.50 = $ 2,792.50

Total fixed cost = $ 1,004,000

a. Break-even point in units = Total Fixed Cost / Contribution Margin per Unit = $ 1,004,000/ $ 2,792.50  = 359.53 units or 360 units

Break-even sales dollars = $ 360 x $ 5,310 = $ 1,911,600.

b. For a profit of $ 2,000,000,

Required unit sales = ( Fixed Cost + Target Profit ) / Contribution Margin per Unit = $ 3,004,000 / $ 2,792.50 = 1,075.74 units

Required sales revenue = 1,075.74 x $ 5,310 = $ 5,712,179

c. Margin of safety for 800 units = Actual Sales - Break-even Sales = 800 - 360 = 440 units

Margin of safety in dollars = 440 units x $ 5,310 = $ 2,336,400.

Margin of safety in percentage = 440 / 800 * 100 = 55 %

2.

CVP Income Statement
800 units		Total	Per Unit
Sales Revenue ( 800 units x $ 5,310 x 105 % )		$ 4,460,400	$ 5,575.50
Variable Costs
Cost of Goods Sold	1,760,000		2,200
Variable Selling Expenses	74,000		92.50
Variable Administrative Expenses	20,000		25.00
Total Variable Costs		1,854,000	2,317.50
Contribution Margin		2,606,400	3258.00
Fixed Costs ( $ 1,004,000 x 130%)		1,305,200
Net Operating Income		$ 1,301,200