Just need Req 6 and Req 7

(These digits will be used to for some variables in the project) Cave this file in EXCEL FORMAT as: P2Lostname Highest = Round all dollar amounts to whole dollars, except for per unle amounts. Per unit amounts should be Lowest other than 0 mounded to the nearest cent. Percentages should be rounded as follows: 20.75% or 2075 Do not change rows or colums on this spreadsheet. Input areas are in yellow. You MUST show your work in the areas indicated to get credit for the problem. You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: Requirement 1: Replace the two unknown amounts (?) in A. and B. as directed. To find the unknown for B, you will have to complete Requirement 2. Then, complete the totals on the income statement, $364,000 Baron Company Income Statement For the Year Ended December 31, 2018 Sales Cost of Goods Sold Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed) $70,200 39,000 64,000 173,200 190,800 No Gross Margin Operating Expenses Commissions (variable) Shipping (variable) 29,120 9,000 A. Multiply the HIGHEST digit entered above B C D Advertising (fixed) Billing (mixed) Sales and Administrative Salarie (fixed) Total Operating Expenses Page + G H 90,000 above by 1,000 and enter here enter the result 16,011 B. Complete Requirement 2 below and insert result her 70,000 204 131 Net Operating Income (Loss) (13,331) ling price per unit is $14 and variable manufacturing overhead is $1 per unit. All variable expenses in the company vary in terms of units sold. ere 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). How many units of product did Baron sell in 2018? 26,000 equirement 2: illing costs for the past 5 years, along with total units sold is presented the table to the right: Year 2017 2016 2015 2014 2013 Sales in Units Billing Costs 23,580 $ 5,692 23,900 $ 5,717 21,000 $ 5,361 23,000 S 5,293 20,850 $ 5,290 Use the high-low method to calculate the following: Show your work in this area: ANSWER: a. Variable billing cost per unit Vcpu = (55,717 - 5,290)/(23,900-20,850) $0.14 b. Total fixed billing costs yvx+f 5,717 0.14 x 23,900) + F $ 2,371 c. Total cost equation for billing costs Y = $0.14(x) +$2,371 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. Y = $0.14(26,000) $2,371 $ 6,011 Insert your answer for 2)d. In the income statement above in the blank provided for Billing (Unknown B) The company has been operating at a loss for several years. Management is trying to determine what it can do to make next year profitable. Requirement 3: Redo Baron's 2018 income statement in contribution format, showing both a total column and a per unit column in the space provided below. You MUST use formulas and cell refereces in the income statement to earn full credit. Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. Total Units 26,000 Amount Per Unit 364,000 $ 14.00 Sales Variable Costs Direct materials Direct Labor Variable MOH Sales commissions 13 Shipping 70,200 39,000 26,000 29,120 9,000 2.70 Hint: Divide total cost column by total units to get vc per unit. 1.50 1.00 0.35 035 0.14 $ $ $ $ 9,000 3,640 176,960 187,040 7.19 Shipping Variable billing Total Variable Costs Contribution Margin Fixed Costs Fixed MOH Advertising Sales and admn. salaries Fixed billing Total Fixed Costs Net Operating Income (Loss) $ $ $ $ $ 38,000 90,000 70,000 2,371 100,371 (13,331) $ Hint: Operating Income (Loss) must equal the original income It 6 Requirment 4: 17 Calculate Baron's current breakeven point in both units and dollars. Show work in this area: 99 Units: $ 27,853.11 100 (200,371+0)/7.19 Page 2 ANSWER Units 27,853 104 Dollars $ 392,884.31 (200,371+0)/0.51 Dollars $ 392,884 Requirement 5: Multiply the lowest digit (other than zero) from the last four digits of your student ID number by 10,000 and enter for C. below to get the reduction in advertising. C.10,000 a. The vice president suggests that selling price be lowered by 10% and advertising be reduced by She is confident that this action will increase sales volume by 25%. The new selling price price per unit would be: $ 12.60 The new advertising amount would be: 9987.4 OOONNN Total Units 32,500 Amount P er Unit $ 409,500 $ 12.60 New from Req. 5 above 139 2.70 1.50 Hint: Hint: Ear unit Variable costs are the same as the runt as VC per unit remains constant with chan total variable costs will change 1.00 137 Sales 138 Variable Costs Direct materials 140 Direct Labor 141 Variable MOH 142 Sales commissions 143 Shipping 144 Variable billing 145 Total Variable Costs 146 Contribution Margin 147 Fixed Costs 148 Fixed MOH 149 Advertising 150 Sales and admn. salaries 151 Fixed billing 152 Total Fixed Costs 153 Net Operating Income 87,750 48,750 32,500 36,400 11,375 4,550 221,325 188,175 0.35 0.14 6.81 5.79 New from Req. 5 above 31,500 90,000 70,000 1,461 192,961 (4,786) Requirement 6: Using the budgeted income statement based on the vice president's proposal above in Req. 5.b., calculate the following: ANSWERS Show your work in the areas provided Da. Breakeven in units: 1 (192,961+0)/5.79 de 3 33,327 53 b. Margin of safety in units: 54 409,500-33,327 376,173 66 c. Margin of safety in dollars of sales: .67 168 5.79/12.60 = .46 169 d. Margin of safety percentage: 170 171 172 173 Requirement 7: 174 a. Calculate the operating leverage factor using the vice-president's proposed income statement above. 175 Answer should use two decimal places. 176 Show your work in this area: Answer u. Margmor Salety percentage: -73 Requirement 7: -74 a. Calculate the operating leverage factor using the vice-president's proposed income statement above. 175 Answer should use two decimal places. Show your work in this area: 176 177 178 Answer 179 181 182 b. If sales volume increases 12%, operating income will increase by: Percent Dollars