Docstoc

CVP Analysis - Excel

Document Sample
CVP Analysis - Excel Powered By Docstoc
					Basic Cost-Volume Profit (CVP) Analysis

Stratford Company distributes a lighweight lawn chair that sells for $15 per unit. Variable costs are $6 pe

Required:
Answer the following independent questions:

 1. What is the product’s CM ratio?
 2. Use the CM ratio to determine the break-even point in sales dollars.
 3. The company estimates that sales will increase by $45,000 during the coming year due to increased de
 4. Assume that the operating results for last year were as follows:
          Sales                         $360,000
          Variable Expenses              144,000

           Contribution Margin             $216,000
           Fixed expenses                   180,000

           Net Operating Income            $ 36,000

                   a. Compute the degree of operating leverage at the current level of sales.
                   b. The president expects sales to increase by 15% next year. By how much should net o

      5. Refer to the original data. Assume that the company sold 28,000 units last year. The sales manag



SOLUTION TO PROBLEM NO.:1:
REQUIREMENT NO.:1:

                       PRODUCT'S CM RATIO:
Selling price per unit                       $              15
less:Variable cost per unit                  $               6
CONTRIBUTION PER UNIT                        $               9

Contribution margin ratio = Contribution
margin per unit/Selling price per unit                     60%

REQUIREMENT NO.:2:
Break-even point in total sales dollars=
Fixed expenses/CM ratio

Fixed expenses                               $         180,000
CM ratio                                                   60%
Break-even point in total sales dollars      $         300,000
    REQUIREMENT NO.:3:

    Increase in sales                           $          45,000
    less:Variable cost($45,000/$15)*$6          $          18,000
    Increase in net operating income            $          27,000

    REQUIREMENT NO.:4:

a   Sales                                       $         360,000
    less:Variable expenses                      $         144,000
    Contribution Margin                         $         216,000
    Fixed expenses                              $         180,000
    Net Operating Income                        $          36,000
    Degree of Operating Leverage=
    Contribution Margin/Net Income                              6

b   Increase in sales @15%                      $          54,000
    Increase in variable expenses @15%          $          21,600
    INCREASE IN NET OPERATING INCOME            $          32,400


    REQUIREMENT NO.:5:
                           INCOME STATEMENT
               BASED ON RESULTS OF LAST YEAR OPERATIONS
a   Sales in units                                          28,000
    Selling price per unit                    $                15
    Sales                                     $           420,000
    less:Variable expenses @ $6 per unit      $           168,000
    CONTRIBUTION MARGIN                       $           252,000
    less:Fixed Expenses                       $           180,000
    NET OPERATING INCOME                      $            72,000

b                     INCOME STATEMENT
        BASED ON CHANGES PROPOSED BY THE SALES MANAGER

    Sales in units(increase by 50%)                         42000
    Selling price (reduced by 10%)              $           13.50
    Decrease in Unit contribution margin        $            7.50

    Expected total contribution margin with
    lower selling price:                        $         315,000
    Present total contribution margin (28,000
    units *9)                                   $         252,000
    INCREMENTAL CONTRIBUTION MARGIN             $          63,000
    Change in fixed costs:
    Less:Incremental advertising expense        $          70,000
Reduction in net income                      $              (7,000)

                                                           COMPARATIVE INCOME STATEMENTS
                                                      PRESENT 28,000 UNITS
                                                    TOTAL             PER UNIT
Sales                                        $          420,000 $              15
less:Variable expenses                       $          168,000 $               6
Contribution margin                          $          252,000 $               9
less:Fixed expenses                          $          180,000
Net Income                                   $            72,000

Based on the comparative income statements it is evident that the suggestion of the sales manager will result in
reduction of net income by $7,000. S0, the changes should not be made.
per unit. Variable costs are $6 per unit, and fixed costs total $180,000 annually.




e coming year due to increased demand. By how much should net operating income increase?




rrent level of sales.
t year. By how much should net operating income increase?

0 units last year. The sales manager is convinced that a 10% reduction in the selling price, combined with a $70,000 increase in
NCOME STATEMENTS
                         EXPECTED 42,000 UNITS
                      TOTAL               PER UNIT         DIFFERENCE
             $              567,000 $              13.50 $       147,000
             $              252,000 $                  6 $        84,000
             $              315,000 $               7.50 $        63,000
             $              250,000                      $        70,000
             $               65,000                      $        (7,000)

 of the sales manager will result in
with a $70,000 increase in advertising expenditures, would cause annual sales in units to increase by 50%. Prepare two contribut
0%. Prepare two contributing format income statements, one showing the results of last year’s operations and one showing what
erations and one showing what the results of operations would be if these changes were made. Would you recommend that the c
Would you recommend that the company do as the sales manager suggests?
Basics of CVP Analysis: Cost Structure

Memofax, Inc. produces memory enhancement kits for fax machines. Sales have been very erratic with so
       Sales(13,500 units at $20 per unit)           $270,000
       Variable expenses                              189,000
         Contribution Margin                           81,000
         Fixed expenses                                90,000
         Net operating loss                         $ (9,000)

Required:
 1. Compute the company’s CM ratio and its break-even point in both units and dollars
 2. The sales manager feels that an $8,000 increase in the monthly advertising budget, combined with an i
 3. Refer to the original data. The president is convinced that a 10% reduction in the selling price, combin
 4. Refer to the original data. The company’s advertising agency thinks that a new package would help sa
 5. Refer to the original data. By automating certain operations, the company could slash its variable expe
   a. Compute the new CM ratio and the new break-even point in both units and dollars.
   b. Assume that the company expects to sell 20,000 units next month. Prepare two contribution format
   c. Would you recommend that the company automate its operations? Explain.

SOLUTION TO PROBLEM NO.:2:

Sales (13,500 units @ $20 per unit)                      $       270,000
less:Variable expenses                                   $       189,000
CONTRIBUTION MARGIN                                      $        81,000
less:Fixed expenses                                      $        90,000
NET OPERATING LOSS                                       $        (9,000)

REQUIRMENT NO.:1:


Contribution margin ratio = Contribution margin /Sales               30%

Break-even point in units sold = Fixed expenses/Unit
contribution margin                                                15000

Break-even point in total sales dollars= Fixed
expenses/CM ratio                                        $       300,000



REQUIRMENT NO.:2:

Incremental increase in sales                            $        70,000
Incremental increase in contribution margin              $        21,000
less: Incremental increase in advertising expense        $         8,000
    Incremental increase in net operating income             $           13,000

    REQUIRMENT NO.:3:

                             INCOME STATEMENT
                 BASED ON CHANGES PROPOSED BY THE PRESIDENT

    Sales in units(increase by 50%)                                       20250
    Selling price (reduced by 10%)                           $            18.00
    Unit contribution margin                                 $             5.40

    Sales                                                    $          364,500
    less:Variable expenses                                   $          255,150
    CONTRIBUTION MARGIN                                      $          109,350
    less:Fixed expenses                                      $          125,000
    NET OPERATING LOSS                                       $          (15,650)



    REQUIRMENT NO.:4:

    Fixed expenses                                           $           90,000

    Selling price per unit                                   $               20
    Variable expense per unit(present)                       $               14
    Increase in packaging cost per unit                      $             0.60
    Total variable expense                                   $            14.60
    Contribution margin per unit                             $             5.40
    Target profit                                            $            4,500


    Units sold to attain the target profit = Fixed
    expenses+Target Profit/Contribution margin per unit                   17500 units



    REQUIRMENT NO.:5:
                                                             ORIGINAL           AUTOMATION
    Selling price per unit                                   $               20 $        20
    Variable expenses per unit                               $               14 $         7
    CONTRIBUTION MARGIN                                      $                6 $        13
    Fixed expenses($90,000+$118,000)                                             $  208,000



a   Contribution margin ratio = Contribution margin /Sales                  65%

    Break-even point in units sold = Fixed expenses/Unit
    contribution margin                                                   16000
    Break-even point in total sales dollars= Fixed
    expenses/CM ratio                                         $          320,000

b                                INCOME STATEMENT
                                   NO AUTOMATION
    Sales in units                                                         20,000
    Selling price per unit                                    $               20
    Sales                                                     $          400,000
    less:Variable expenses @ $14 per unit                     $          280,000
    CONTRIBUTION MARGIN                                       $          120,000
    less:Fixed Expenses                                       $           90,000
    NET OPERATING INCOME                                      $           30,000

                                 INCOME STATEMENT
                                    AUTOMATION

    Sales in units                                                         20,000
    Selling price per unit                                    $               20
    Sales                                                     $          400,000
    less:Variable expenses @ $7 per unit                      $          140,000
    CONTRIBUTION MARGIN                                       $          260,000
    less:Fixed Expenses                                       $          208,000
    NET OPERATING INCOME                                      $           52,000




C   The company should automate its operations since the net operating income
    has increased by $22,000. And with increase in sales the income will directly
    increase with the contribution margin per unit.
have been very erratic with some months showing a profit and some months showing a loss. The company’s contribution format




 g budget, combined with an intensified effort by the sales staff, will result in a $70,000 increase in monthly sales. If the sales ma
on in the selling price, combined with an increase of $35,000 in the monthly advertising budget will cause unit sales to double. W
 a new package would help sales. The new package being proposed would increase packaging costs by $0.60 per unit. Assuming
y could slash its variable expenses in ahlf. However, fixed costs would increase by $118,000 per month.
and dollars.
pare two contribution format income statements, one assuming that operations are not automated and one assuming that they are.
mpany’s contribution format income statement for the most recent month is given below:




monthly sales. If the sales manager is right, what will be the effect on the company’s monthly net operating income or loss? (use
 cause unit sales to double. What will the new contribution format income statement look like if these changes are adopted?
 by $0.60 per unit. Assuming no other changes, how many units would have to be sold each month to earn a profit of $4,500?


d one assuming that they are.
operating income or loss? (use the incremental approach in preparing your answer).
these changes are adopted?
th to earn a profit of $4,500?

				
DOCUMENT INFO