Part 1 Using the template provided in the attached Word document, prepare a contribution margin income statement for the year-ending December 31, 2021. Data needed to complete the income statement is located in the completed operational budgets contained in the tab "Initial Annual budget" within the attached Excel file. Part 2. Use the "Calculation" tab of the Excel file Workbook to complete this part. Based on the contribution margin income statement completed in Part 1, calculate the company's: a. Break even point in units of product b. Calculate the break even point in sales dollars c. Calculate sales units required to achieve net income of $3,000,000 d. Calculate sales dollars required to achieve net income of $3,000,000 The formulas for break-even point and target income in units and in sales can be found in Chapter 22 of the textbook. ***Round calculations to the nearest unit and sales dollars to the nearest dollar. Part 3. Use the "Revised Budget" tab of the Excel File Workbook to complete this part. Create revised operational budgets using the sales amount needed to achieve target income, as calculated in Part 2 Budgets to be completed include: Sales budget Production Budget Direct Materials Budget Direct Labor Budget Selling and Administrative Budget Overhead Budget Best World Product Company Annual Budget Exercise Required: Part 1 Using the annual data totals for Best World Product Company, complete the following information: Per unit Total (@47,500 Units) 7.125,000 Sales 150 Variable costs: Direct Materials Direct Labor Overhead SGA Costs Total Variable costs Contribution Margin Fixed Costs: Manufacturing Overhead SGA Overhead Total Fixed Costs Net Income Part 2 Use Excel File "Calculation" tab for this part 1. Using CVP analysis a. Calculate the number of units that must be sold during the year for the company to break-even. b. Calculate the sales dollars required to break-even Calculate the number of units that must be sold during the year for the company to achieve net income of $3,000,000 d. Calculate the sales dollars required to reach target income of $3,000,000 C 2. Using the project Excel workbook - "Revised Budget' tab, create revised annual budget based on the number of unit sales required to reach net income of $3,000,000 Sales Budget Production Budget Materials Budget Labor Budget Manufacturing Overhead Budget SGA Budget Part 3 - Reflection: 1. Describe how the change in sales impacted budgets within the master budget. Why is a sales forecast the starting point for creation of all budgets within the master budget. 2. Many companies prepare multiple budgets based on different sales projections. Why do you think a company would do this? Provide specific benefits to management when multiple budgets are available. 3. Explain how CVP analysis and budgets work together to provide a "path" for the organizations to meet their financial goals. Sales Budget For the Year Ending December 31, 2021 Expected unit sales Sales price per unit Total sales Annual 47,500 150 7,125,000 For the Year Ending December 31, 2021 0 1 12 13 14 15 16 17 18 19 20 21 22 23 1 Expected unit sales Add: desired ending finished goods units Total required units Less: beginning finished goods units Required production units Annual 47,500 5,200 52.700 1,500 51,200 Direct Materials Budget For the Year Ending December 31, 2021 Units to be produced Pounds of material per unit Total pounds needed for production Add: desired ending inventory of powder Total pounds of material required Less: beginning inventory of material Pounds of material to be purchased Cost per pound Total cost of material required Annual 51,200 4 204,800 22,960 227,760 19,000 208,760 10 $2,087,600 Selling and Administrative Budget For the Year Ending December 31, 2021 Expected unit sales Variable selling and administrative costs @ $5 Fixed selling and administrative costs Total selling and administrative costs Annual 47,500 $237,500 100,000 $337,500 Overhead Costs For the Year Ending December 31, 2021 Expected unit sales Variable overhead costs @ $30/unit Fixed overhead costs Total overhead costs Annual 47,500 $1,425,000 1,100,000 $2,525,000 E F A B C D Best World Products - CVP Calculations For Year ended December 31, 2021 1. Break-Even point in unit sales B 2. Break- Even point in sales dollars 0 11 12 73 3. Target Income in unit sales 14 15 16 17 4. Target Income in sales dollars 18 19 20 21 22 Show complete calculations for full credit 23 24 25 26 st World Products get Data for 2021 Sales Budget For the Year Ending December 31, 2021 Annual Expected unit sales Sales price per unit Total sales 150 0 Production Budget For the Year Ending December 31, 2021 Annual 5,200 Expected unit sales Add: desired ending finished goods units Total required units Less: beginning finished goods units Required production units 1,500 -1,500 Direct Materials Budget For the Year Ending December 31, 2021 Annual 4 22,960 5 7 8 -9 30 31 32 33 34 35 36 37 38 39 Units to be produced Pounds of material per unit Total pounds needed for production Add: desired ending inventory of powder Total pounds of material required Less: beginning inventory of material Pounds of material to be purchased Cost per pound Total cost of material required 19,000 -19,000 10 Direct Labor Budget For the Year Ending December 31, 2021 Annual 2 Units to be produced Number of hours of labor/unit Total labor hours Cost per labor hour Total labor costs $27 $0 Selling and Administrative Budget For the Year Ending December 31, 2021 Annual Expected unit sales Variable selling and administrative costs @ $5/unit Fixed selling and administrative costs Total selling and administrative costs SO $0 Overhead Costs For the Year Ending December 31, 2021 Annual 3 Expected unit sales Variable overhead costs @ $30/unit Fixed overhead costs Total overhead costs $0 0 1 $0 2 3 74 75 76