PROJECT 2 – CVP ANALYSIS

    CVP Analysis
    Accounting 202 30 points
    PROJECT 2 – CVP ANALYSIS

    Name: Class time 9.3

    Enter the last 4 digits of your student ID number:
    (These digits will be used to for some variables in the project)
    Save this file in EXCEL FORMAT as: P2Lastname Highest =
    Round all dollar amounts to whole dollars, except for per unit amounts. Per unit amounts should be Lowest other than 0=
    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.

    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, 2015
    Sales $350,000
    Cost of Goods Sold
    Direct Materials (variable) $60,000
    Direct Labor (variable) 52,500
    Manufacturing Overhead (mixed) 64,000
    176,500
    Gross Margin 173,500
    Operating Expenses

    Commissions (variable) 21,000
    Shipping (variable) ? A. Multiply the HIGHEST digit entered above
    above by 1,000 and enter here enter the result
    Advertising (fixed) 90,000
    Billing (mixed) ? B. Complete Requirement 2 below and insert result
    Sales and Administrative Salaries (fixed) 60,000
    Total Operating Expenses

    Net Operating Income (Loss)
    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.

    Requirement 2: Year Sales in Units Billing Costs
    Billing costs for the past 5 years, along with total units sold follows: 2014 23,850 $ 3,375
    2013 22,000 $ 3,156
    2012 21,000 $ 3,033
    2011 22,750 $ 3,290
    2010 23,000 $ 3,309
    Use the high-low method to calculate the following:
    Show your work in this area: ANSWER:
    a. Variable billing cost per unit $ 8.33
    $8.33

    b. Total fixed billing costs -4263.75
    $ (4,264)

    c. Total cost equation for billing costs =(4263.75)+8.33x

    d. Determine the total number of units currently being sold. Then determine total billing costs for that level of sales.
    23,850 194406.75
    $ 194,407
    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 provided below.
    You MUST use formulas and cell refereces in the income statements in Requirements 3 and 5 to earn full credit.
    Hint: You will need to calculate the variable and fixed components of Manufacturing Overhead (MOH) and billing.

    Total Units Amount
    Per Unit

    Sales
    Variable Costs
    Direct materials Hint: Divide total cost column by total units to get VC per unit.
    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 Hint: Operating Income must equal the original income from above.

    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 C.
    She is confident that this action will increase sales by 10,000 units.

    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 changes,
    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.)

    Total Units Amount
    Per Unit

    Sales New from above
    Variable Costs
    Direct materials
    Direct Labor
    Variable MOH
    Sales commissions
    Shipping
    Variable billing
    Total Variable Costs
    Contribution Margin
    Fixed Costs 2
    Fixed MOH
    Advertising New from above
    Sales and admn. salaries
    Fixed billing
    Total Fixed Costs
    Net Operating Income

    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 calcualte 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 10%, operating income will increase by:

    Percent Dollars

                                                                                                                                                                   Order Now