Please help using excel. Also, use the check figures provided to compare answers. Show work with formulas used.

Problem B (26 points): Block Ltd makes BLOCKS. 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: Solution: Shox jour your analysis in organized schedules as much as possible. A schedule for completing Requirement #1 is provided for you. Requirement 1: Current Ye:Scenario 15cenario 25cenario 3 $33.50 41,000 Selling price $33.50 Unit volume 41,000 Variable costs per unit: Material $9.00 Direct labor $5.50 Packaging $0.95 Overhead $0.50 Fixed costs: Manufacturing ###### Selling and administr: ###### Selling price Unit volume Variable costs per unit: Material Direct labor Packaging Overhead $9.00 $5.50 $0.95 $0.50 Fixed costs: Manufacturing Selling and administrative $ 275,000 $ 350,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 11.2. & 3 for the Current Year and for each of the Three Scenarios suggested by the employee teams. Ansver Required Break Even Analysis: In Units In Sales Dollars Requirement 2: 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 Soenarios (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) Requirement 3: Current Data-Check Figure: Margin of Safety = 13.14% Scenario 1--Check Figure: CMZ = 60.227 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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. Requirement 4: Scenario 1: Pros: Cons: Scenario 2--Check Figure: BE in Units = 26,567 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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 2: Pros: Cons: Scenario 3--Check Figure: Pre-taz Income = $277.666 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 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 Direct labor costs would remain the same as they were in Scenarios 1 and 2. Scenario 3: Pros: Cons: Requirement 5--Recommendation: Manufacturing Selling and administrative $ 275,00U $ 350,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. Ansver Required Item 14 for each of Break Even Analysis: In Units In Sales Dollars Requirement 2: Required: . 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 Soenarios (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) Requirement 3: Current Data--Check Figure: Margin of Safety = 13.14% Scenario 1--Check Figure: CMZ = 60.22% Research indicates that the company can purchase a different material, of equal quality. to construct the blocks. 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. Requirement 4: Scenario 1: Pros: Cons: Scenario 2--Check Figure: BE in Units = 26.567 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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 2: Pros: Cons: Scenario 3--Check Figure: Pre-tax Income = $277.666 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%. Direct labor costs would remain the same as they were in Scenarios 1 and 2. Scenario 3: Pros: Cons: Requirement 5--Recommendation: Problem B (26 points): Block Ltd makes BLOCKS. 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: Solution: Shox jour your analysis in organized schedules as much as possible. A schedule for completing Requirement #1 is provided for you. Requirement 1: Current Ye:Scenario 15cenario 25cenario 3 $33.50 41,000 Selling price $33.50 Unit volume 41,000 Variable costs per unit: Material $9.00 Direct labor $5.50 Packaging $0.95 Overhead $0.50 Fixed costs: Manufacturing ###### Selling and administr: ###### Selling price Unit volume Variable costs per unit: Material Direct labor Packaging Overhead $9.00 $5.50 $0.95 $0.50 Fixed costs: Manufacturing Selling and administrative $ 275,000 $ 350,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 11.2. & 3 for the Current Year and for each of the Three Scenarios suggested by the employee teams. Ansver Required Break Even Analysis: In Units In Sales Dollars Requirement 2: 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 Soenarios (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) Requirement 3: Current Data-Check Figure: Margin of Safety = 13.14% Scenario 1--Check Figure: CMZ = 60.227 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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. Requirement 4: Scenario 1: Pros: Cons: Scenario 2--Check Figure: BE in Units = 26,567 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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 2: Pros: Cons: Scenario 3--Check Figure: Pre-taz Income = $277.666 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 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 Direct labor costs would remain the same as they were in Scenarios 1 and 2. Scenario 3: Pros: Cons: Requirement 5--Recommendation: Manufacturing Selling and administrative $ 275,00U $ 350,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. Ansver Required Item 14 for each of Break Even Analysis: In Units In Sales Dollars Requirement 2: Required: . 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 Soenarios (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) Requirement 3: Current Data--Check Figure: Margin of Safety = 13.14% Scenario 1--Check Figure: CMZ = 60.22% Research indicates that the company can purchase a different material, of equal quality. to construct the blocks. 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. Requirement 4: Scenario 1: Pros: Cons: Scenario 2--Check Figure: BE in Units = 26.567 Research indicates that the company can purchase a different material, of equal quality, to construct the blocks. 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 2: Pros: Cons: Scenario 3--Check Figure: Pre-tax Income = $277.666 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%. Direct labor costs would remain the same as they were in Scenarios 1 and 2. Scenario 3: Pros: Cons: Requirement 5--Recommendation