6 Enter the last 4 digits of your student ID number: 9196 (These digits will be used to for some variables in the project) Highest- Lowest other than 0= 8 Save this file in EXCEL FORMAT as: P2Lastname 9 Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be 10 rounded to the nearest cent. Percentages should be rounded as follows: 20.75% or .2075 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. 9 15 Presented below is Baron Company's Income Statement prepared on the traditional (functional) basis: 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 19 complete Requirement 2. Then, complete the totals on the income statement. Baron Company Income Statement For the Year Ended December 31, 2018 21 $364,000 Sales Cost of Goods Sold 23 Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed) $70,200 39,000 64,000 25 27 173,200 190,800 Gross Margin Operating Expenses Commissions (variable) Shipping (variable) Advertising (fixed) Billing (mixed) Sales and Administrative Salaries (fixed) 31 32 29,120 9,000 A. Multiply the HIGHEST digit entered above 90,000 above by 1,000 and enter here enter the result B. Complete Requirement 2 below and insert result here 34 35 70,000 Total Operating Expenses 37 Net Operating Income (Loss) 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 sold 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) 42 43 c. How many units of product did Baron sell in 2018? 26000 45 46 Requirement 2: 47 Billing costs for the past 5 years, along with total units sold is presented 48 |in the table to the right: 49 Year 2017 2016 2015 2014 2013 Sales in Units Billing Costs 23,580 $ 5,692 23,900 $5,717 5,361 23,000 $5,293 20,850 $5,290 51 Use the high-low method to calculate the following: 52 53 a. Variable billing cost per unit 54 Show your work in this area: ANSWER: 5717-5290/23900-20850- $0.14 56 b. Total fixed billing costs 57 5717-3346- $2,371 $2371+$0.14x 59 c. Total cost equation for billing costs 60 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 62 63 64 65 66 The company has been operating at a loss for several years. Management is trying to determine what it can do to make 67 next year profitable 68 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. $2371+$0.14*26000-$3640 2371+3640 $6011 $6,011 Insert youranswerfor2)d. in the income statement above in the blank provided for Billing (Unknown B) -- 67 next year profitable 68 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 73 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing Total Units Amount Per Unit 75 77 Sales 78 Variable Costs 79 Direct materials 80 Direct Labor 81 Variable MOH 82 Sales commissions 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) 94 95 96 Requirment 4: 97 Calculate Baron's current breakeven point in both units and do $364,000 $14 $70,200 $ 39,000 $ 26,000 $29,120 $9,000 $3,640 $ 176,960 $ 187,040 2.70Hint: Divide total cost column by total units to get VC per unit. 1.50 1.00 1.12 0.35 0.14 6.81 7.19 $38,000 $90,000 $70,000 $2,371 $ 200,371 (13,331) Hint: Operating Income (Loss) must equal the original income (loss) from above Page 2 Show work in this area: 99 Units: ANSWER 96 Requirment 4: 97 Calculate Baron's current breakeven point in both units and do 98 99 Units: 100 101 102 103 104 Dollars: 105 1067.19/14 107 108 109 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 enter 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 Show work in this area: ANSWER FC 200,371 CM-7.19 Units 27,868 FC 200,371 CMR:51.38% Dollars $389,944 The new selling price price per unit would be: 118 119 120 121 122 123 124 125 126 127 128 The new advertising amount would be: 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 134 135 136 137 Sales 138 Variable Costs 139 Direct materials 140 Direct Labor 1411 Variable MOH 142 Sales commissions 143 Shipping 144 Vaiable 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 Total Units Amount Per Unit New from Req. 5 above Hint: Per unit Variable 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 New from Req. 5 above 153 Net Operating Income 154 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice preside 158 159 160 a. Breakeven in units: Page 3 above in Req. 5.b., calculate the following: ro ANSWE Show your work in the areas provided 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice president's prop 158 159 160 a. Breakeven in units: 161 162 163 b. Margin of safety in units: 164 165 166 C. Margin of safety in dollars of sales: 167 168 169 d. Margin of safety percentage: 170 Page 3 q. 5.b., calculate the following: ANSWE Show your work in the areas provided 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 178 179 180 181 182b. If sales volume increases 12%, operating income will increase by: 183 184 185 186 187 Dollars Percent