A B G H K L Accounting 202 PROJECT 2-CVP ANALYSIS 40 points WN Name: Last four non-zero digt of student ID 2 3 4 B 1 (These diglts will be used to for some variables in the project) 9 Save this file as P2Lastname Highest 10 You must use Excel formalas and cell references to receive full credit. Lowest other than 11 Round per unit amounts to the nearest cent. Round all others to the nearest doller. Percentages should look like this: 20.79 12. Do not change rows or colums on this spreadsheet. Input areas are in yellow. 13. For partial credit, you must show your work in the areas indicated. 14 15 Presented below is Trident Company's income Statement prepared on the traditional functional) basis 16 17 Requirement 1: Replace the two unknown amounts (7in A and B as directed to find the unknown for 8, you will have to 18 complete Requirement 2. Then, complete the totals on the income statement. 20 $350,000 22 23 24 25 20 27 28 Trident Company Income Statement For the Year Ended December 31, 2015 Sales Cost of Goods Sold Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed $60,000 52,500 64.000 176,500 173.500 30 32 33 Gross Margin Operating Expenses Commissions (variable) Shipping variable) Advertising (fixed) Proj2 + 21,000 8,000 A. Multiply the HIGHEST digt above by 1,000 90,000 Ready B G D G K 34 Billing Imixed) B. Complete Requirement 2 below and enter here. 35 Sales and Administrative Salaries (fixed) 60,000 316 Total Operating Expenses 32 38 Net Operating Income (Loss) 39 10. The company provides the following information: 41 Selling price $ 14.00 per unit 13 Variable Manufacturing Overhead $ 1.00 per unit 43 Plant Capacity 40,000 units per year 44 All variable expenses vary in terms of units sold. There was no change in beginning or ending inventories. 45 46. How many units did Trident sell? 112,600 47 48 49 Requirement 2: Year Sales in Units Billing Costs 50 Billing costs for the past 5 years, along with total units sold follows: 2014 23,850 $ 3,375 51 2013 22,000 $ 3,156 2012 21,000 5 3,033 552 2011 22,750 3.290 50 2010 23,000 $ 3,309 55. Use the high-low method to calculate the following: 56 Show your work in this area: ANSWER: 57 a. Variable billing cost per unit 58 59 30 b. Total fixed billing costs 60 92 GS co Total cost equation for billing costs 52 unus d. Determine the determine total billing costs using the cost equation and the total number of units old. 65 66 TH P2 Office Update To keep up-to-3 WII C4 x x G H D 88 Insert your answer for 21d. In the Income statement abowe in the blank provided for Billing (Unknown B] 69 70 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 72 Requirements: 7 Rado Trident's 2015 Income statement in contribution format showing both a total column anda per unit column in the space provided below. 7 You MUST U formulas and cell refereces in the income statements in Requirements and to earn full credit. Mint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. 7 Total Units Amount Per Unit 79 RD Sales R1 Variable Corts 82 Direct materials Hint: Divide total cost column by total units to get VC per unit. Direct Labor 1 Variable MOH Hint: Total MOH = Variable OH+Fixed OH. Variable OH =$i per unit 55 Sales commissions 8 Shipping BT Variable billing Variable billing per unit was calculated in Requirement 2 Total Variable Costs 19 Contribution Margin 90 Feed Costs 31 MOH -91 Sales and admn salaries 54 Faxed billing 95 Total Flised Costs 36 Net Operating income Fixed billing was calculated in Requirement 2 Hint: This is the same as the original Operating income above. 58 e Requement o calculateTrident's current breakeven point in both units and dollars Proj2 Ready lo Reep up-to-date with security updates, fixes, and improvements, choose Check for Updates. f C4 x A B C D H K M ANSWER Units Dollars 98 99 Requirment 4 100 Calculate Trident's current breakeven point in both units and dollars 101 Show work in this area 102 103 104 105 106 107 108 109 110 111 112 Requirements: 113 114 a. The vice president suggests a few changes to fix this problem: 115 Reduce selling price by 116 Reduce advertising by 117 This should increase the number of units sold by 118 119 The new selling priceprice per unit would be 120 1231 The new advertising amount would be: 122 123 124 125 126 127 128 229 130 Proj2 + 5.00% C. Multiply the LOWEST non-zero ID number above by 10,000 10,000 units + Ready X Office Update To keep up-to-date with security updates, xusunu C4 X V G 1 K D B E 33 b. Prepare a new contribution margin income statement, using the vice president's recommendation. Remember, when volume changes, 34 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new es number of units. (Remember, VC per unit remains constant.) 86 37 Total Units Amount 38 Per Unit 39 40 Sales New from above 1 Variable Costs 2 Direct materials Direct Labor 4 Variable MOH 45 Sales commissions 45 Shipping 47 Variable billing 48 Total Variable Costs 49 Contribution Margin 50 Fixed Costs SE Fixed MOH 52 Advertising New from above 53 Sales and admn salaries 54 Fixed billing Total Fixed Costs 56 Net Operating Income 57 58 59 Requirement 6 6D Using the budgeted income statement based on the vice president's proposal above, calculate the 61 Show your work in the areas provided. (Hint: You must calcuate a new breakeven point to complete this section) 620 New breakeven point in units 63 54 Answer Proj2 + Real K B G 181 159 Requirement 6: 160 Using the budgeted income statement based on the vice president's proposal above, calculate the 161 Show your work in the areas provided. (Hint: You must calcualte a new breakeven point to complete this section.) 162 a. New breakeven point in units 163 Answer Answer 164 165 166 167 b. Margin of safety in units: 168 169 170 171 122 c. Margin of safety in dollars of sales 173 174 175 Answer 176 Answer 177 d. Margin of safety percentage 178 179 180 181 182 Requirement: 183 a Calculate the degree of operating leverage using the vice president's proposed income statement above. 184 Answer should use two decimal places 185 Show your work in this area: 185 187 188 189 1905. How much will operating income increase if sales volume increases by 10.00% TED Proj 2 + Answer Realty C4 > X H K D c B Answer Answer Answer 169 170 171 172 c. Margin of safety in dollars of sales: 173 174 175 176 177 d. Margin of safety percentage 178 179 180 181 182 Requirement 183 a Calculate the degree of operating leverage using the vice president's proposed Income statement above. 184 Answer should use two decimal places 185 Show your work in this area: 186 187 188 189 1905. How much will operating income increase if sales volume increases by 10.0096 191 192 Percent Answer 193 Dollars 194 195 195 197 198 199 200 200 Proj 2 + Ready A B G H K L Accounting 202 PROJECT 2-CVP ANALYSIS 40 points WN Name: Last four non-zero digt of student ID 2 3 4 B 1 (These diglts will be used to for some variables in the project) 9 Save this file as P2Lastname Highest 10 You must use Excel formalas and cell references to receive full credit. Lowest other than 11 Round per unit amounts to the nearest cent. Round all others to the nearest doller. Percentages should look like this: 20.79 12. Do not change rows or colums on this spreadsheet. Input areas are in yellow. 13. For partial credit, you must show your work in the areas indicated. 14 15 Presented below is Trident Company's income Statement prepared on the traditional functional) basis 16 17 Requirement 1: Replace the two unknown amounts (7in A and B as directed to find the unknown for 8, you will have to 18 complete Requirement 2. Then, complete the totals on the income statement. 20 $350,000 22 23 24 25 20 27 28 Trident Company Income Statement For the Year Ended December 31, 2015 Sales Cost of Goods Sold Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed $60,000 52,500 64.000 176,500 173.500 30 32 33 Gross Margin Operating Expenses Commissions (variable) Shipping variable) Advertising (fixed) Proj2 + 21,000 8,000 A. Multiply the HIGHEST digt above by 1,000 90,000 Ready B G D G K 34 Billing Imixed) B. Complete Requirement 2 below and enter here. 35 Sales and Administrative Salaries (fixed) 60,000 316 Total Operating Expenses 32 38 Net Operating Income (Loss) 39 10. The company provides the following information: 41 Selling price $ 14.00 per unit 13 Variable Manufacturing Overhead $ 1.00 per unit 43 Plant Capacity 40,000 units per year 44 All variable expenses vary in terms of units sold. There was no change in beginning or ending inventories. 45 46. How many units did Trident sell? 112,600 47 48 49 Requirement 2: Year Sales in Units Billing Costs 50 Billing costs for the past 5 years, along with total units sold follows: 2014 23,850 $ 3,375 51 2013 22,000 $ 3,156 2012 21,000 5 3,033 552 2011 22,750 3.290 50 2010 23,000 $ 3,309 55. Use the high-low method to calculate the following: 56 Show your work in this area: ANSWER: 57 a. Variable billing cost per unit 58 59 30 b. Total fixed billing costs 60 92 GS co Total cost equation for billing costs 52 unus d. Determine the determine total billing costs using the cost equation and the total number of units old. 65 66 TH P2 Office Update To keep up-to-3 WII C4 x x G H D 88 Insert your answer for 21d. In the Income statement abowe in the blank provided for Billing (Unknown B] 69 70 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 72 Requirements: 7 Rado Trident's 2015 Income statement in contribution format showing both a total column anda per unit column in the space provided below. 7 You MUST U formulas and cell refereces in the income statements in Requirements and to earn full credit. Mint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. 7 Total Units Amount Per Unit 79 RD Sales R1 Variable Corts 82 Direct materials Hint: Divide total cost column by total units to get VC per unit. Direct Labor 1 Variable MOH Hint: Total MOH = Variable OH+Fixed OH. Variable OH =$i per unit 55 Sales commissions 8 Shipping BT Variable billing Variable billing per unit was calculated in Requirement 2 Total Variable Costs 19 Contribution Margin 90 Feed Costs 31 MOH -91 Sales and admn salaries 54 Faxed billing 95 Total Flised Costs 36 Net Operating income Fixed billing was calculated in Requirement 2 Hint: This is the same as the original Operating income above. 58 e Requement o calculateTrident's current breakeven point in both units and dollars Proj2 Ready lo Reep up-to-date with security updates, fixes, and improvements, choose Check for Updates. f C4 x A B C D H K M ANSWER Units Dollars 98 99 Requirment 4 100 Calculate Trident's current breakeven point in both units and dollars 101 Show work in this area 102 103 104 105 106 107 108 109 110 111 112 Requirements: 113 114 a. The vice president suggests a few changes to fix this problem: 115 Reduce selling price by 116 Reduce advertising by 117 This should increase the number of units sold by 118 119 The new selling priceprice per unit would be 120 1231 The new advertising amount would be: 122 123 124 125 126 127 128 229 130 Proj2 + 5.00% C. Multiply the LOWEST non-zero ID number above by 10,000 10,000 units + Ready X Office Update To keep up-to-date with security updates, xusunu C4 X V G 1 K D B E 33 b. Prepare a new contribution margin income statement, using the vice president's recommendation. Remember, when volume changes, 34 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new es number of units. (Remember, VC per unit remains constant.) 86 37 Total Units Amount 38 Per Unit 39 40 Sales New from above 1 Variable Costs 2 Direct materials Direct Labor 4 Variable MOH 45 Sales commissions 45 Shipping 47 Variable billing 48 Total Variable Costs 49 Contribution Margin 50 Fixed Costs SE Fixed MOH 52 Advertising New from above 53 Sales and admn salaries 54 Fixed billing Total Fixed Costs 56 Net Operating Income 57 58 59 Requirement 6 6D Using the budgeted income statement based on the vice president's proposal above, calculate the 61 Show your work in the areas provided. (Hint: You must calcuate a new breakeven point to complete this section) 620 New breakeven point in units 63 54 Answer Proj2 + Real K B G 181 159 Requirement 6: 160 Using the budgeted income statement based on the vice president's proposal above, calculate the 161 Show your work in the areas provided. (Hint: You must calcualte a new breakeven point to complete this section.) 162 a. New breakeven point in units 163 Answer Answer 164 165 166 167 b. Margin of safety in units: 168 169 170 171 122 c. Margin of safety in dollars of sales 173 174 175 Answer 176 Answer 177 d. Margin of safety percentage 178 179 180 181 182 Requirement: 183 a Calculate the degree of operating leverage using the vice president's proposed income statement above. 184 Answer should use two decimal places 185 Show your work in this area: 185 187 188 189 1905. How much will operating income increase if sales volume increases by 10.00% TED Proj 2 + Answer Realty C4 > X H K D c B Answer Answer Answer 169 170 171 172 c. Margin of safety in dollars of sales: 173 174 175 176 177 d. Margin of safety percentage 178 179 180 181 182 Requirement 183 a Calculate the degree of operating leverage using the vice president's proposed Income statement above. 184 Answer should use two decimal places 185 Show your work in this area: 186 187 188 189 1905. How much will operating income increase if sales volume increases by 10.0096 191 192 Percent Answer 193 Dollars 194 195 195 197 198 199 200 200 Proj 2 + Ready