Please solve requirement 5, requirement 6, and requirement 7.

6 Enter the last 4 digits of your student ID number: (These digits will be used to for some variables in the project) this file in EXCEL FORMAT as: P2Lastname Highest 9 Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0- rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or .2075 10 11 Do not change rows or colums on this spreadsheet. Input areas are in yellow. 12 You MUST show your work in the areas indicated to get credit for the problem. 13 You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. 14 15 Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: 16 17 Requirement 1: Replace the two unknown amounts (?) in A. and B. as directed. To find the unknown for B, you will have to 18 complete Requirement 2. Then, complete the totals on the income statement. 19 20 Baron Company Income Statement For the Year Ended December 31, 2018 Sales $364,000 23 Cost of Goods Sold 24 Direct Materials (variable) $70,200 25 26 Direct Labor (variable) 39,000 Manufacturing Overhead (mixed) 64,000 28 173,200 190,800 29 Gross Margin 30 Operating Expenses Commissions (variable) 31 29,120 Shipping (variable) A. Multiply the HIGHEST digit entered above Advertising (fixed) Billing (mixed) above by 1,000 and enter here enter the result B. Complete Requirement 2 below and insert result here Sales and Administrative Salaries (fixed) 70,000 35 Total Operating Expenses 37 38 Net Operating Income (Loss) 39 40 Selling price per unit is $14 and variable manufacturing overhead is $1 per unit. All variable expenses in the company vary in terms of units solod. 41 There was no change in inventory levels between the beginning and end of the year. Baron's plant capacity is 40,000 units per year (relevant range) 43 C. How many units of product did Baron sell in 2018? Sales in Units Billing Costs 45 Year 23,580$ 5,692 46 Requirement 2: 2017 47 Billing costs for the past 5 years, along with total units sold is presented 48 in the table to the right: 23,900 S 5,717 2016 21,000 $5,361 2015 23,000 $5,293 20,850 5,290 49 2014 2013 51 Use the high-low method to calculate the following Show rwork in this area ANSWER: 53 a. Variable billing cost per unit 56 b. Total fixed billing costs 57 59c. Total cost equation for billing costs Proj 2 56 b. Total fixed billing costs 57 59 c. Total cost equation for billing costs 61 d. Determine total billing costs for 2018 based on the cost equation in 2)c. above and the total number of units sold in 2018 Insert your answer for 2d. in the income statement above in the blank provided for Billing (Unknown B) 64 66 The company has been operating at a loss for several years. Management is trying to determine what it can do to 67 make next year profitable 69 Requirement 3: 70 Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. 71 You MUST use formulas and cell refereces in the income statement to earn full credit. 72 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing 73 Total Units Amount 74 75 Per Unit 77 Sales 78 Variable Costs 79 Direct materials Hint: Divide total cost column by total units to get VC per unit. 80 Direct Labor 81 Variable MOH 82 Sales commission:s 83 Shipping 84 Variable billing 85 Total Variable Costs 86 Contribution Margin 87 Fixed Costs 88 Fixed MOH 89 Advertising 90 Sales and admn. salaries 91 Fixed billing 92 Total Fixed Costs 93 Net Operating Income (Loss) Hint: Operating Income (Loss) must equal the original income (loss) from above. 94 Page 2 95 96 Requirment 4 97 Calculate Baron's current breakeven point in both units and dolar 98 Show work in this area 99 Units: ANSWER 100 101 102 Units 103 104 Dollars 105 106 Dollars 107 108 109 Requirement 5: Proj 2 100% Scroll Lock J11 09 Requirement 5 110 111 Multiply the lowest digit (other than zero) from the last four digits of your student ID number by 10,000 and 112 for C. below to get the reduction in advertising. 113 1141a. The vice president suggests that selling price be lowered by 10% and advertising be reduced by 115| She is confident that this action will increase sales volume by 25%. 116 The new selling price price per unit would be: 118 The new advertising amount would be 119 120 121 122 123 124 125 126 127 128 129 b. Prepare a budgeted margin income statement, using the vice president's recommendation. Remember, when volume changes, 130 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new 131 number of units. Remember, VC per unit remains constant, but total VC changes proportionately with changes in volume. 132 You MUST use formulas and cell refereces in the income statement to earn full credit. 133 Total Units Amount 134 135 Per Unit 136 137 Sales New fromReq.5 above 138 Variable Costs 139 Direct materials Hint: Per unit Variable Costs are the same as the original income statement 140 Direct Labor as VC per unit remains constant with changes in volume. However 141 variable MOH total variable costs will change 142 Sales commissions 43 Shipping 44 Variable billing 145 Total Variable Costs 146 Contribution Margin 147 Fixed Costs 148 Fixed MOH 49 Advertising 150 Sales and admn. salaries 151 Fixed billing New fromReq.5 above 152 Total Fixed Costs 153 Net Operating Income 154 155 156 Requirement 6 157 Using the budgeted income statement based on the vice president's proposal above in Req. 5.b., calculate the following: 158 ANSWE 159 Show your n the areas provide 160 a. Breakeven in units: 162 163 b. Margin of safety in units: 164 Proj 2 100% Scroll Lock 133 134 Total Units Amount 135 Per Unit 136 37 Sales New from Req. 5 above 138 Variable Costs Hint: Per unit Variae Costs are the same as the original income statement as VC per unit remains constant with changes in volume. However total variable costs will change 139 Direct materials 40 Direct Labor 141 Variable MOH 142 Sales commissions 143 Shipping 44Variable billing 145 Total Variable Costs 46 Contribution Margin 47 Fixed Costs 48 Fixed MOH 49 Advertising New from Req. 5 above 150 Sales and admn. salaries 151 Fixed billing 152 Total Fixed Costs 53 Net Operating Income 154 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice president's Page3 Req. 5.b., calculate the following: 158 159 ANSWE Show n the areas provid 60a. Breakeven in units: 161 162 163 b. Margin of safety in units: 164 165 66 C. Margin of safety in dollars of sales: 167 168 169 d. Margin of safet 170 172 173 Requirement 7: 74 a. Calculate the operating leverage factor using the vice-president's proposed income statement above 75 Answer should use two decimal places. Show your work in this area: 176 178 Answer 179 180 181 182b. If sales volume increases 12%, operating income will increase by: 183 Percent Dollars 184 185 186 187 188 Proj 2 Scroll Lock 6 Enter the last 4 digits of your student ID number: (These digits will be used to for some variables in the project) this file in EXCEL FORMAT as: P2Lastname Highest 9 Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0- rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or .2075 10 11 Do not change rows or colums on this spreadsheet. Input areas are in yellow. 12 You MUST show your work in the areas indicated to get credit for the problem. 13 You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. 14 15 Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: 16 17 Requirement 1: Replace the two unknown amounts (?) in A. and B. as directed. To find the unknown for B, you will have to 18 complete Requirement 2. Then, complete the totals on the income statement. 19 20 Baron Company Income Statement For the Year Ended December 31, 2018 Sales $364,000 23 Cost of Goods Sold 24 Direct Materials (variable) $70,200 25 26 Direct Labor (variable) 39,000 Manufacturing Overhead (mixed) 64,000 28 173,200 190,800 29 Gross Margin 30 Operating Expenses Commissions (variable) 31 29,120 Shipping (variable) A. Multiply the HIGHEST digit entered above Advertising (fixed) Billing (mixed) above by 1,000 and enter here enter the result B. Complete Requirement 2 below and insert result here Sales and Administrative Salaries (fixed) 70,000 35 Total Operating Expenses 37 38 Net Operating Income (Loss) 39 40 Selling price per unit is $14 and variable manufacturing overhead is $1 per unit. All variable expenses in the company vary in terms of units solod. 41 There was no change in inventory levels between the beginning and end of the year. Baron's plant capacity is 40,000 units per year (relevant range) 43 C. How many units of product did Baron sell in 2018? Sales in Units Billing Costs 45 Year 23,580$ 5,692 46 Requirement 2: 2017 47 Billing costs for the past 5 years, along with total units sold is presented 48 in the table to the right: 23,900 S 5,717 2016 21,000 $5,361 2015 23,000 $5,293 20,850 5,290 49 2014 2013 51 Use the high-low method to calculate the following Show rwork in this area ANSWER: 53 a. Variable billing cost per unit 56 b. Total fixed billing costs 57 59c. Total cost equation for billing costs Proj 2 56 b. Total fixed billing costs 57 59 c. Total cost equation for billing costs 61 d. Determine total billing costs for 2018 based on the cost equation in 2)c. above and the total number of units sold in 2018 Insert your answer for 2d. in the income statement above in the blank provided for Billing (Unknown B) 64 66 The company has been operating at a loss for several years. Management is trying to determine what it can do to 67 make next year profitable 69 Requirement 3: 70 Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. 71 You MUST use formulas and cell refereces in the income statement to earn full credit. 72 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing 73 Total Units Amount 74 75 Per Unit 77 Sales 78 Variable Costs 79 Direct materials Hint: Divide total cost column by total units to get VC per unit. 80 Direct Labor 81 Variable MOH 82 Sales commission:s 83 Shipping 84 Variable billing 85 Total Variable Costs 86 Contribution Margin 87 Fixed Costs 88 Fixed MOH 89 Advertising 90 Sales and admn. salaries 91 Fixed billing 92 Total Fixed Costs 93 Net Operating Income (Loss) Hint: Operating Income (Loss) must equal the original income (loss) from above. 94 Page 2 95 96 Requirment 4 97 Calculate Baron's current breakeven point in both units and dolar 98 Show work in this area 99 Units: ANSWER 100 101 102 Units 103 104 Dollars 105 106 Dollars 107 108 109 Requirement 5: Proj 2 100% Scroll Lock J11 09 Requirement 5 110 111 Multiply the lowest digit (other than zero) from the last four digits of your student ID number by 10,000 and 112 for C. below to get the reduction in advertising. 113 1141a. The vice president suggests that selling price be lowered by 10% and advertising be reduced by 115| She is confident that this action will increase sales volume by 25%. 116 The new selling price price per unit would be: 118 The new advertising amount would be 119 120 121 122 123 124 125 126 127 128 129 b. Prepare a budgeted margin income statement, using the vice president's recommendation. Remember, when volume changes, 130 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new 131 number of units. Remember, VC per unit remains constant, but total VC changes proportionately with changes in volume. 132 You MUST use formulas and cell refereces in the income statement to earn full credit. 133 Total Units Amount 134 135 Per Unit 136 137 Sales New fromReq.5 above 138 Variable Costs 139 Direct materials Hint: Per unit Variable Costs are the same as the original income statement 140 Direct Labor as VC per unit remains constant with changes in volume. However 141 variable MOH total variable costs will change 142 Sales commissions 43 Shipping 44 Variable billing 145 Total Variable Costs 146 Contribution Margin 147 Fixed Costs 148 Fixed MOH 49 Advertising 150 Sales and admn. salaries 151 Fixed billing New fromReq.5 above 152 Total Fixed Costs 153 Net Operating Income 154 155 156 Requirement 6 157 Using the budgeted income statement based on the vice president's proposal above in Req. 5.b., calculate the following: 158 ANSWE 159 Show your n the areas provide 160 a. Breakeven in units: 162 163 b. Margin of safety in units: 164 Proj 2 100% Scroll Lock 133 134 Total Units Amount 135 Per Unit 136 37 Sales New from Req. 5 above 138 Variable Costs Hint: Per unit Variae Costs are the same as the original income statement as VC per unit remains constant with changes in volume. However total variable costs will change 139 Direct materials 40 Direct Labor 141 Variable MOH 142 Sales commissions 143 Shipping 44Variable billing 145 Total Variable Costs 46 Contribution Margin 47 Fixed Costs 48 Fixed MOH 49 Advertising New from Req. 5 above 150 Sales and admn. salaries 151 Fixed billing 152 Total Fixed Costs 53 Net Operating Income 154 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice president's Page3 Req. 5.b., calculate the following: 158 159 ANSWE Show n the areas provid 60a. Breakeven in units: 161 162 163 b. Margin of safety in units: 164 165 66 C. Margin of safety in dollars of sales: 167 168 169 d. Margin of safet 170 172 173 Requirement 7: 74 a. Calculate the operating leverage factor using the vice-president's proposed income statement above 75 Answer should use two decimal places. Show your work in this area: 176 178 Answer 179 180 181 182b. If sales volume increases 12%, operating income will increase by: 183 Percent Dollars 184 185 186 187 188 Proj 2 Scroll Lock