Project 2 Name: TYPE in your name here Please use this template provided to solve project 2 that contains 3 parts. Save your file using your first initial, last name, and project 2. Upload your solution to the project 2 link. Make sure you type your name in cell A2. Each part is on a separate worksheet where you should complete the applicable part. You should always verify that your project file has been uploaded correctly. Remember it is your responsibility to take the time to verify that your file has uploaded correctly. If there are problems, please contact me immediately. You will be graded on the accuracy of your answer and the usage of excel. Remember to review the eLectures and guided examples in MBC. Also, there is support material on cost-profit-volume analysis in the Module 16 folder and relevant cost analysis in Module 17 folder. Keep in mind that even though this project focuses on material from Modules 16 and 17, it is building on the concepts learned in Module 15 on cost behavior. Project 2 Objectives: 1. Develop Contribution Income statement. 2. Develop unit contribution margin. 3. Perform Breakeven Analysis. 4. Calculate and analyze operating leverage. 5. Evaluate and recommend how to plan for increase in patient demand 6. Evaluating relevant costs for decision making purposes Grading Rubric: Part 1: #1 #2 #3 #4 #5 #6 #7 #8 #9 Part 2: #1 #2 #3 Part 3: #1 #2 Total points Points 12 8 8 8 8 8 8 8 12 8 8 8 8 8 120 Part 1-pertains to Module 16 DATA ABC, Inc. is a newly organized manufacturing business this year. The following company's costs and expenses are: Sales price per unit $41 Manufacturing costs: Units sold Fixed Costs Direct materials Direct labor Variable Manufacturing overhead Fixed Manufacturing overhead Period expenses: Variable Selling and administrative expenses Fixed Selling and Administrative expenses Totals 6,000 units Variable Costs $8 6 3 $80,000 4 20,000 $100,000 $21 Required: Use the information in the DATA field above using cell referencing to answer the following requirements. 1. Prepare a contribution income statement for the year ignoring income tax implication. 2. Calculate the breakeven point in units. 3. Calculate the breakeven point in sales dollars. 4. Calculate the margin of safety in sales dollars. 5. Calculate the company's operating leverage. 6. Calculate the percentage change in profits if sales are projected to increase by 5%. 7. What if the direct labor cost per unit increases from $6 a unit to $9, what will be the new breakeven in units? Explain why it changed. You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question? 8. What if the manufacturing overhead cost decreases from $80,000 to $70,000, what will be the new breakeven in units? Explain why it changed. You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question? 9. The sales manager believes the company could increase sales by 1,000 units if advertising expenditures were increased by $22,000. Determine the change in the dollar amount of income before taxes, if the company increases advertising expenditures by $22,000. Would you recommend this advertising campaign based on financial results? Why? Solution: #1. Contribution Margin Income Statement #2. Break even in units Units #3. Break even in sales $ #4 Margin of Safety in sales dollars #5 Operating leverage #6 Percentage change in profits if sales increase by: 5% #7 You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question? #8 You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question? #9 The sales manager believes the company could increase sales by 1,000 units if advertising expenditures were increased by $22,000. Determine the change in the dollar amount of income before taxes, if the company increases advertising expenditures by $22,000. Would you recommend this advertising campaign based on financial results? Why? Part 2 contains 3 Decision making recommendations that are independent of each other. Covers material in Module 17 Glass company manufactures glasses that it sells to mail-order distributors. Sales price per pair of glasses: Manufacturing and other costs follow: Variable Cost per unit Direct Materials Direct labor Factory overhead Variable distribution costs are for transportation to mail-or Total Variable costs Fixed costs per month Factory overhead Selling and Administrative Total Fixed costs $58 $11 10 2 3 $26 $20,000 10,000 $30,000 Current monthly production and sales volume 5,000 units Monthly capacity 6,000 units Required: Determine the effect of each of the following independent situations on monthly profits. Make sure you show you work and give a recommendation to management if they should accept the change or not accept the change. #1 A $3 increase in the unit selling price should result in a 1,200-unit decrease in monthly sales #2 A 10% decrease in the unit selling price should result in a 2,000-unit increase in monthly sales. However, because of capacity constraints, the last 1,000 units would be produced during overtime with the direct labor costs increasing by 60%. #3 a British distributor has proposed to place a special one-time order for 1,000 units at a reduced price of $54 per unit. The distributor would pay all transportation costs, so there are no variable distribution costs. There would be additional fixed selling and administrative costs of $2,000. Part 3 contains 2 Decision making recommendations that are independent of each other. Covers material in Module 17. Make sure you show your work. Decision Making #1-Sell or Process Further Joint Product Decision XYZ manufactures A and B from a joint process cost = $70,000. Six thousand pounds of A can be sold at split-off for $20 per pound or processed further at an additional cost of $15000 and then sold for $24. Five thousand pounds of B can be sold at split-off for $10 per pound or processed further at an additional cost of $13,000 and later sold for $17. Required: Which products should be processed further or not? Why? Give the total dollar impact to income for each product based on your recommendation. Recommendation: Decision making #2-Use of Limited Resources-Singe Constraint Bush Manufacturing has 25,000 labor hours available for producing M and N. Consider the following information: M N Required Labor per unit in hou 4 2 Maximum demand in units 8,000 7,000 Contribution Margin per unit $8 $6.00 Required: What is the optimal product mix(how many M and N should be produced)? Explain your answer and show computations. You must tell me how many of Product M and N should be produced. Recommendation