3 4 Name: Last four non-zero digit of student ID 6 5 4 3 2 6 6 7 8 (These digits will be used to for some variables in the project) 9 Save this file as: P2Lastname Highest = 10 You must use Excel formulas and cell references to receive full credit. Lowest other than 0= 11 Round per unit amounts to the nearest cent. Round all others to the nearest dollar. Percentages should look like this: 20.75% 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(?) 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 Trident Company 21 Income Statement 22 For the Year Ended December 31, 2015 23 Sales $350,000 24 Cost of Goods Sold 25 Direct Materials (variable) $60,000 26 Direct Labor (variable) 52,500 27 Manufacturing Overhead (mixed) 64,000 28 176,500 29 Gross Margin 173,500 30 Operating Expenses 31 Commissions (variable) 21,000 32 Shipping (variable) A. Multiply the HIGHEST digit above by 1,000 33 Advertising (fixed) 90,000 34 Billing (mixed) B. Complete Requirement 2 below and enter here. 35 Sales and Administrative Salaries (fixed) 60,000 36 Total Operating Expenses 37 38 Net Operating Income (Loss) 39 34 Billing mixed) B. Complete Requirement 2 below and enter here. 35 Sales and Administrative Salaries (fixed) 60,000 36 Total Operating Expenses 37 38 Net Operating Income (Loss) 39 40 The company provides the following information: 41 Selling price $ 14.00 per unit 42 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? 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 52 2012 21,000 $ 3,033 53 2011 22.750 $ 3,290 54 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 60 b. Total fixed billing costs 61 62 63 c. Total cost equation for billing costs 64 65 d. Determine the determine total billing costs using the cost equation and the total number of units sold. 66 67 68 Insert your answer for 2)d. in the income statement above 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. 71 72 Requirement 3: 73 Redo Trident's 2015 income statement in contribution format, showing both a total column and a per unit column in the space provided below. 74 You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit. 75 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. 76 77 Total Units Amount 78 Per Unit 79 80 Sales 81 Variable Costs 82 Direct materials Hint: Divide total cost column by total units to get VC per unit. 83 Direct Labor 84 Variable MOH Hint: Total MOH = Variable OH + Fixed OH. Variable OH = $1 per unit 85 Sales commissions 86 Shipping 87 Variable billing Variable billing per unit was calculated in Requirement 2 88 Total Variable Costs 89 Contribution Margin 90 Fixed Costs 91 Fixed MOH 92 Advertising 93 Sales and admn. salaries 94 Fixed billing Fixed billing was calculated in Requirement 2 95 Total Fixed Costs 96 Net Operating Income Hint: This is the same as the original Operating Income above. 97 98 99 Requirment 4: 100 Calculate Trident's current breakeven point in both units and dollars. 101 Show work in this area: 102 ANSWER 103 104 105 Units 106 107 108 109 110 Dollars 111 5.00% C. Multiply the LOWEST non-zero ID number above by 10,000 10,000 units 111 112 Requirement 5: 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 price price per unit would be: 120 121 The new advertising amount would be: 122 123 124 125 126 127 128 129 130 NIEU 131 132 133 b. Prepare a new contribution margin income statement, using the vice president's recommendation. Remember, when volume changes, 134 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new 135 number of units. (Remember, VC per unit remains constant.) 136 137 Total Units Amount 138 Per Unit 139 140 Sales New from above 141 Variable Costs 142 Direct materials 143 Direct Labor 144 Variable MOH 145 Sales commissions 146 Shipping 147 Variable billing 148 Total Variable Costs 149 Contribution Margin 150 Fixed Costs 151 Fixed MOH 152 Advertising New from above 153 Sales and admn. salaries 154 Fixed billing 155 Total Fixed Costs 156 Net Operating Income 157 158 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 164 Answer 165 166 167 b. Margin of safety in units: 168 169 Answer 170 171 172 c. Margin of safety in dollars of sales: 173 174 Answer 175 176 177 d. Margin of safety percentage: 178 179 Answer 180 181 182 Requirement 7: 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 Answer 188 189 190 b. How much will operating income increase if sales volume increases by 10.00% 191 192 Percent 193 194 Dollars 195 196