Presented below is Trident 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. Trident Company Income Statement For the Year Ended December 31, 2016 Sales $350,000 Cost of Goods Sold $60,000 Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed) 52,500 64,000 176,500 173,500 Gross Margin Operating Expenses Commissions (variable) 21,000 A. Multiply the HIGHEST digit entered above Shipping (variable) above by 1,000 and enter here enter the result 90,000 Advertising (fixed) Billing (mixed) Sales and Administrative Salaries (fixed) ? B. Complete Requirement 2 below and insert result 60,000 Total Operating Expenses Net Operating Income (Loss) , A 5: A Project 2 Su17 (2) - Excel ? E - a FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in E AutoSum E Wrap Text AY H Calibri -11 A A General Fill- +8 8 Conditional Format as Cell Formatting Table Styles Paste BIU- EE E Merge & Center - $- % > Insert Delete Format Sort & Find &e 2 Clear Filter- Select Clipboard a Font Alignment Number Styles Cells Editing M9 A B D E G H K M P Q 40 41 Selling price per unit is $14 and variable manufacturing overhead is $1 per unit. All variable expenses in the company 42 vary in terms of units sold. There was no change in beginning or ending inventories. Trident's plant capacity is 40,000 43 units per year. 44 Sales in Units Billing Costs 45 Requirement 2: 46 Billing costs for the past 5 years, along with total units sold follows: Year 2014 23,850 3,375 47 2013 22,000 3,156 48 2012 21,000 3,033 49 2011 22,750 3,290 50 2010 23,000 3,309 51 Use the high-low method to calculate the following: Show your work in this area: 52 ANSWER: 53 a. Variable billing cost per unit 54 55 56 b. Total fixed billing costs 57 58 c. Total cost equation for billing costs 59 60 61 d. Determine the total number of units currently being sold. Then determine total billing costs for that level- sales. 62 63 Insert your answer for 2)d. in the income statement above in the blank provided for Billing (Unknown B) 64 65 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. 68 69 Requirement 3: 70 Redo Trident's 2016 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 statements in Requirements 3 and 5 to earn full credit. 72 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. 73 74 Total Units Amount 75 Per Unit Proj 2 READY 100% 10:33 AM O Type here to search x 7/19/2017 86 Contribution Margin , A 5: A Project 2 Su17 (2) - Excel ? E - a FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in E AutoSum AY H Calibri -11 A A E Wrap Text General Fill- +8 8 Conditional Format as Cell Formatting Table Styles Paste BIU- EE E Merge & Center - Insert Delete Format Sort & Find &e 2 Clear Filter- Select Clipboard a Font Alignment Number Styles Cells Editing M9 A B D E H K M P Q 67 make next year profitable. 68 69 Requirement 3: 70 Redo Trident's 2016 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 statements in Requirements 3 and 5 to earn full credit. 72 12 Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing. 73 74 Total Units Amount 75 Per Unit 76 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 Hint: Note that of total MOH, variable is $1 per unit ; the remaining MOH must be fixed 82 Sales commissions Shipping Variable billing 83 84 Variable Cost per unit is from From Requirement 2 a) above 85 Total Variable Costs 87 Fixed Costs 88 Fixed MOH 89 Advertising 90 Sales and admn. salaries 91 Fixed billing Fixed billing is from From Requirement 2 b) above Total Fixed Costs 92 93 Net Operating Income Hint: Operating Income must equal the original income from above. 94 95 96 Requirment 4: 97 Calculate Trident's current breakeven point in both units and dollars. Show work in this area: 98 99 Units: ANSWER 100 101 102 Units Proj 2 READY 100% 10:33 AM O Type here to search x 7/19/2017 , Project 2 Su17 (2) - Excel ? E - a FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in E AutoSum AY H Calibri -11 A A E Wrap Text General Fill- +8 8 Conditional Format as Cell Formatting Table Styles Paste BIU- EE E Merge & Center $- % > Insert Delete Format Sort & Find &e 2 Clear Filter- Select Clipboard a Font Alignment Number Styles Cells Editing M9 A B D E F G K M P Q 94 95 96 Requirment 4: 97 Calculate Trident's current breakeven point in both units and dollars. 98 Show work in this area: 99 Units: ANSWER 100 101 102 Units 103 104 Dollars: 105 106 Dollars 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 114 a. The vice president suggests that selling price be lowered by 5% and advertising be reduced by 115 She is confident that this action will increase sales by 10,000 units. C. 116 117 The new selling price price per unit would be: 118 119 The new advertising amount would be: 120 121 122 123 124 125 126 127 128 129 Proj 2 READY 100% 10:33 AM O Type here to search x 7/19/2017 , , A 5: A Project 2 Su17 (2) - Excel ? E - a FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in E AutoSum E Wrap Text AY H Calibri -11 A A General Fill- +8 8 Conditional Format as Cell Formatting Table Styles Paste BIU- A EE E Merge & Center - $- % > Insert Delete Format Sort & Find &e 2 Clear Filter- Select Clipboard a Font Alignment Number Styles Cells Editing M9 A B D E F G H K M P Q 130 b. Prepare a new contribution margin income statement, using the vice president's recommendation. Remember, when volume changes, 131 total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new 132 number of units. (Remember, VC per unit remains constant.) 133 134 Total Units Amount 135 Per Unit 136 137 Sales 138 Variable Costs New from above 139 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 New from above 154 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice president's proposal above, calculate the 158 Show your work in the areas provided. (Hint: You must calcualte a new breakeven point to complete this section.) 159 a. Margin of safety in units: 160 161 162 b. Margin of safety in dollars of sales: 163 164 165 c. Margin of safety percentage: Proj 2 READY 100% 10:33 AM O Type here to search x 7/19/2017 , A: E- Project 2 Su17 (2) - Excel ? E - a FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign in E AutoSum AY H Calibri -11 A A E Wrap Text General Fill- +8 8 Conditional Format as Cell Formatting Table Styles- Paste BIU- EE E Merge & Center Insert Delete Format Sort & Find &e 2 Clear Filter- Select Clipboard a Font Alignment Number Styles Cells Editing M9 X A B D E H K M P Q 154 155 156 Requirement 6: 157 Using the budgeted income statement based on the vice president's proposal above, calculate the 158 Show your work in the areas provided. (Hint: You must calcualte a new breakeven point to complete this section.) 159 a. Margin of safety in units: 160 161 162 b. Margin of safety in dollars of sales: 163 164 165 c. Margin of safety percentage: 166 167 168 169 Requirement 7: 170 a. Calculate the degree of operating leverage using the vice-president's proposed income statement above. 171 Answer should use two decimal places. 172 173 Show your work in this area: 174 Answer 175 176 177 178 b. If sales volume increase 10%, operating income will increase by: 179 180 Percent Dollars 181 182 183 184 185 186 187 188 189 Proj 2 READY 100% 10:33 AM O Type here to search x 7/19/2017 ,