Hello! Please help with the attached accounting related problems in the excel spreadsheet relating to income statements, break even analysis, contribution margin. Many thanks!

Module 5 Excel Workbook Assignment MBA 6315 Problem A (20 points): Hare Inc. sold 23,000 units of its only product and incurred a loss (ignoring taxes) for the current year as shown here. During a planning session for year 2017's activities, the productions manager notes that variable costs can be reduced 40% by installing a machine that automates several operations, To obtain these savings, the company must increase its annual fixed costs by $135,000. The maximum output capacity of the company is 40,000 units per year. Hare Company Contribution Margin Income Statement For the Year Ending December 31, 2016 Sales Variable Costs Contribution Margin Fixed Costs Pre-Tax Loss $ 1,610,000 1,288,000 322,000 400,000 $ (78,000) Required: 1 Compute the break-even point in dollar sales for year 2016. (2 points) 2 Compute the predicted break-even point in dollar sales for year 2017 assuming the machine is installed and no change occurs in the unit selling price. (Round all numbers to the nearest dollar.) (3 points) 3 Prepare a forecasted contribution margin income statement for 2017 that shows the expected results with the machine installed. Assume that the unit selling price and the number of units sold will not change, and no income taxes will be due. (5 points) 4 Compute the sales level required in both dollars and units to earn $600,000 of target pretax income in 2017 with the machine installed and no change in unit sales price. (Round all numbers to the nearest dollar and to whole units.) (5 points) 5 Prepare a forecasted contribution margin income statement that shows the results at the sales level computed in part 4. Assume no income taxes will be due. (5 points) Solution: Please place the space below and e it with a border so tha 1 2 3 4 Check Figures: Item 3: Pre-Tax Income = $302,200; Item 5: Contribution Margin = $1,135,000 5 Solution: Please place your answer and supporting calculations in the space below and either highlight your final answer or surround it with a border so that it can be easily identified by the grader. Break-Even Point in Dollar Sales for 2016: Predicted Break-Even Point in Dollar Sales for 2017: Forcasted Contribution Margin Income Statement for 2017: Sales level required in Dollars for 2017 to earn target pretax income of $600,000: Sales level required in Units for 2017 to earn target pretax income of $600,000: Forecasted Contribution Margin Income Statement at Sales Level computed in Item 4: Module 5 Excel Workbook Assignment MBA 6315 Problem B (26 points): L'il Mack Ltd makes toy trucks. Their pre-tax income and their margin of safety have both been low compared to the levels the company would like to achieve. Consequently, they asked their employees to do some research and come up with suggestions that would result in increases of both pre-tax income and their margin of safety over the next year. The current pricing structure consists of the following data points: Selling price Unit volume Variable costs per unit: Material Direct labor Packaging Overhead Fixed costs: Manufacturing $ Selling and administrative $ $35.00 41,600 $10.00 $6.00 $1.05 $0.50 256,000 365,000 Three employee teams have come up with different plans to improve pre-tax income and the margin of safety for next year. Respond to Required Items #1, 2, & 3 for the Current Year and for each of the Three Scenarios suggested by the employee teams. Answer Required Item #4 for each of the Three Scenarios and then make the decision requested in Required Item #5. Required: 1. Compute the Break-Even point in both units and sales dollars for the Current Data and each of the Three Scenarios. (4 points) 2. Provide a Contribution Margin Income Statement in proper form for the Current Data and each of the Three Scenarios. (9 points) 3. Provide the Margin of Safety calculation for the Current Data and each of the Three Scenarios. (4 points) 4. Briefly, list two pros and two cons that you consider important when comparing the results of implementing each of the Three Scenarios. (6 points) 5. Choose the Scenario that you would recommend to the company and briefly explain why it is the best choice. (3 points) Current Data--Check Figure: Margin of Safety = 14.45% Scenario 1--Check Figure: CM% per unit = 58.14% Research indicates that the company can purchase a different material, of equal quality, to construct the trucks. This would reduce material costs by 20%. Using the new material would also result in a 15% reduction in direct labor costs. All other data points remain constant. Scenario 2--Check Figure: BE in Units = 26,038 Research indicates that the company can purchase a different material, of equal quality, to construct the trucks. This would reduce material costs by 20%. Using the new material would also result in a 15% reduction in direct labor costs. Market research indicates that the company could increase the selling price by 10%, but that doing so would result in a 10% decrease in units sold. All other data points remain constant. Scenario 3--Check Figure: Pre-tax Income = $295,500 Negotiations with suppliers resulted in an agreement that would decrease the cost of the same new material mentioned in Scenarios 1 and 2 by 20% if the company purchased it in bulk. To use up the material purchased in the amounts required to get the discount, the company would need to increase sales unit volume by 50%. Market research indicates that this is possible as long as the selling price is decreased by 20%. Market research also indicates that improved packaging would be needed to achieve the needed sales volume levels. The packaging costs would increase by 25%. Solution: Show your your analysis in organized schedules as much as possible. A schedule for completing Requirement #1 is provided for you. Requirement 1: Selling price Unit volume Variable costs per unit: Material Direct labor Packaging Overhead Fixed costs: Manufacturing Selling and administrative Break Even Analysis: In Units In Sales Dollars Requirement 2: Requirement 3: Requirement 4: Scenario 1: Pros: Cons: Current Year $35.00 41,600 $10.00 $6.00 $1.05 $0.50 $ $ 256,000 365,000 Scenario 1 Scenario 2 Scenario 2: Pros: Cons: Scenario 3: Pros: Cons: Requirement 5--Recommendation: ssible. A schedule for Scenario 3 Module 5 Excel Workbook Assignment MBA 6315 Solution: Please place yo the space below and eith it with a border so that it Problem C (26 points): EasyBake Company is able to produce two toy products, a Little Oven and a Big Oven, with the same machine in its factory. NOTE that only ONE Oven can be manufactured at a time using this machine. The Company currently makes both ovens, but management is concerned that this strategy is not providing maximum benefit. They are thinking of adding a second shift and have asked you to figure out their best course of action. The following information is available: 1 EasyBake Data: 2 Selling price per unit Variable costs per unit Contribution margin per unit Machine hours to produce ONE unit Maximum customer demand for each Oven per month (max they can sell) Little Oven 110 35 75 1/3 hour 540 units Big Oven 150 68 82 1 hour 190 units 3 The company presently operates the machine for a single eight-hour shift for 22 working days each month. Management is thinking about operating the machine for two shifts, which will increase its productivity by another eight hours per day for 22 days per month. This change would require $12,500 additional fixed costs per month. 4 Required (NOTE--show all calculations for full credit): 1 Determine the contribution margin per machine hour that each product generates. (4 points) 2 How many units of the Little Oven and the Big Oven should the company produce if it continues to operate with only one shift? Assume all ovens produced are sold. (4 points) 3 How much total contribution margin does this sales mix produce each month? (4 points) 4 If the company adds another shift, how many units of the Little Oven and the Big Oven should it produce? (3 points) 5 How much total contribution margin would this mix produce each month? (3 points) 6 Should the company add the new shift? (3 points) 7 Suppose that, based on market research, the company determines that it can increase the Little Oven's maximum sales to 650 units per month by spending $10,000 per month in marketing efforts. Should the company pursue this strategy and the double shift? Provide support for your answer. (5 points) NOTE that this problem is NOT asking you to calculate Break-Even. Check Figures: Item 4: Big Ovens = 172 Units; No check figure provided for the Little... Item 7: Little Ovens Total Contribution Margin = $48,750 5 6 7 Solution: Please place your answer and supporting calculations in the space below and either highlight your final answer or surround it with a border so that it can be easily identified by the grader. Contribution Margin by Machine Hour for each Oven: Units of each Oven that company should produce to maximize Contribution Margin, assuming only one production shift: Total Contribution Margin resulting from solution to Item 2: Units of each Oven that company should produce to maximize Contribution Margin, assuming TWO production shifts: Total Contribution Margin resulting from solution to Item 4: Should the company add the second shift? Why or why not? Should the company add the advertising campaign and the second shift? Why or why not? Show all relevant calculations. Module 5 Excel Workbook Assignment MBA 6315 Problem D (38 points): Weather-Tight sells windows and doors. The following data applies to the company's operating activities: Current sales mix in units percentage Selling Price per Unit Variable Cost per Unit Fixed Costs Current Unit Volume Assume all units produced are sold. Windows 85.00% $200 $125 $550,000 68,000 Doors 15.00% $500 $350 $310,000 12,000 Use the space provided below for organized calculations (including things like Current Unit Volume, Wtd CM, Wtd Average CM, Sales Mix, etc. See the narrated lectures for guidance. The detailed questions you are required to answer start on line 40 of this worksheet and are also listed briefly in the SOLUTION space provided starting in Column J of this worksheet: Required: 1 What is the current break-even point in units for both products combined? (2 points) 2 What is the number of units of each product that need to be sold at the current break-even point? (2 points) 3 What is the current break-even point in sales dollars for both products combined? (2 points) 4 How much, in sales dollars, of each product does the company currently need to sell at the break-even point? (2 points) 5 Show the Current Contribution Margin Income Statement at Break-Even. (6 points) 6 Show the Contribution Margin Income Statement at the Current Sales Level. (7 points) 7 What is the Margin of Safety at the Current Sales Level? (3 points) The Company is working on budgets for next year and plans a 10% increase in Target Pretax Income; a 10% increase in both Selling Price and Variable Cost; and a 20% increase in Fixed Costs, keeping the Current Sales Mix in Dollars constant. 8 Show the Contribution Margin Income Statement at the Target Pretax Income Level. HINT--you need to start at the bottom of the Contribution Margin Income Statement and work your way up to the top to figure this out. (8 points) 9 What is the Sales Unit Volume of each product needed to achieve the Target Level of Pretax Income? (3 points) 10 What is the Margin of Safety at the Target Sales Level? (3 points) Check Figures: Item 4: Doors = $747,826; Check Figure for Windows is not provided. Item 6: Total Contribution Margin = $6,900,000; Item 8: Windows Pre-Tax Target Income = $5,005,000; Check Figure for Doors is not provided. Solution: Please place your answer and supporting calculations in the space below and either highlight your final answer or surround it with a border so that it can be easily identified by the grader. 1 Total Units (products combined) to Break Even: Total 100.000% $860,000 80,000 2 Units of Each Product to Break Even: , Wtd CM, Wtd Average to answer start on line J of this worksheet: nt? (2 points) ak-even point? (2 points) 3 Total Sales Dollars (products combined) to Break Even: 4 Sales Dollars by Product to Break Even: 5 Current Contribution Margin Income Statement at Break Even: 6 Contribution Margin Income Statement at Current Sales: 7 Margin of Safety at Current Sales Level: e; a 10% increase in both Dollars constant. need to start at the e this out. (8 points) ome? (3 points) 8 Contribution Margin Income Statement at the Target Level of Pretax Income: 9 Units of each Product to reach Target Pretax Income: 10 Margin of Safety at Target Sales Level