I need someone to correct Requirement 3. I'm not sure where I am going wrong on the income statement

Accounting 202 PROJECT 2 - CVP ANALYSIS Name: Colin Flynn Enter the last 4 digits of your student ID number: 1 (These digits will be used to for some variables in Save this file in EXCEL FORMAT as: P2Lastname Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be rounded 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 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 Cost of Goods Sold Direct Materials (variable) Direct Labor (variable) Manufacturing Overhead (mixed) $350,000 $60,000 52,500 64,000 176,500 173,500 Gross Margin Operating Expenses Commissions (variable) Shipping (variable) 21,000 8,000 A. Multiply the HIGHEST digit entere above by 1,000 and enter here enter 90,000 3,467 B. Complete Requirement 2 below a Advertising (fixed) Billing (mixed) Sales and Administrative Salaries (fixed) Total Operating Expenses Net Operating Income (Loss) 60,000 182,467 $ 8,967 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. There was no change in beginning or ending inventories. Trident's plant capacity is 40,000 units per year. Page 1 Requirement 2: Billing costs for the past 5 years, along with total units sold follows: Year 2014 2013 2012 2011 2010 Sales in Units 23,850 22,000 21,000 22,750 23,000 Use the high-low method to calculate the following: Show your work in this area: a. Variable billing cost per unit (3,375-3,147)/(23,850-21,000) b. Total fixed billing costs .08(23,850)=1,908 3,375-1,908=1,467 c. Total cost equation for billing costs .08x + 1,467 d. Determine the total number of units currently being sold. Then determine total billing costs for that level of sales. 350,000/14=25,000. (25,000 x .08) +1,567= 3,467 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 Trident's 2015 income statement in contribution format, showing both a total column and a per unit column in the space provid 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 25,000 Sales Variable Costs Direct materials Direct Labor Variable MOH Sales commissions Shipping Variable billing Total Variable Costs Contribution Margin Fixed Costs Fixed MOH Advertising Amount Per Unit $ 350,000 $ $ $ $ $ $ $ $ $ 60,000 52,500 25,000 21,000 8,000 2,000 168,500 181,500 $ $ 39,000 90,000 14.00 2.40 Hint: Divide total cost column by total units to get VC per unit. 2.10 1.00 0.84 0.32 0.08 6.74 7.26 Page 2 Sales and admn. salaries Fixed billing Total Fixed Costs Net Operating Income $ $ $ $ 60,000 1,467 190,467 8,967 Hint: Operating Income must equal the original income from ab Requirment 4: Calculate Trident's current breakeven point in both units and dollars. Show work in this area: Units: ANSWER Units Dollars: Dollars 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. a. The vice president suggests that selling price be lowered by 5% and advertising be reduced by She is confident that this action will increase sales by 10,000 units. C. The new selling price price per unit would be: The new advertising amount would be: b. Prepare a new contribution margin income statement, using the vice president's recommendation. Remember, when volume chan total variable costs change proportionately. To get total variable costs, multiply the per unit amounts from Requirement 3 by the new number of units. (Remember, VC per unit remains constant, but total VC changes proportionately with changes in volume. You MUST use formulas and cell refereces in the income statement to earn full credit. Page 3 Total Units 35,000 Sales Variable Costs Direct materials Direct Labor Variable MOH Sales commissions 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 $ Amount Per Unit 465,000 $ 13.30 New from Req. 5 above 2 New from Req. 5 above Requirement 6: Using the budgeted income statement based on the vice president's proposal above, calculate the Show your work in the areas provided. (Hint: You must calculate a new breakeven point to complete this section.) a. Margin of safety in units: b. Margin of safety in dollars of sales: c. Margin of safety percentage: Requirement 7: a. Calculate the degree of operating leverage using the vice-president's proposed income statement above. Answer should use two decimal places. Show your work in this area: Answer b. If sales volume increase 20%, operating income will increase by: Page 4 Percent Dollars Page 5 30 points Class time 8 4 to for some variables in the project) Highest = Lowest other than 0= 9:30 2 8 1 ou will have to the HIGHEST digit entered above 00 and enter here enter the result Requirement 2 below and insert result here ompany vary in 00 units per year. Page 6 Billing Costs $ 3,375 $ 3,156 $ 3,147 $ 3,290 $ 3,309 ANSWER: $0.08 $ 1,467 l of sales. $ 3,467 o to make next umn in the space provided below. H) and billing. its to get VC per unit. Page 7 original income from above. $ 10,000 $ 13.30 $ 80,000 mber, when volume changes, quirement 3 by the new es in volume. Page 8 ection.) Page 9 Page 10