Docstoc

Albert's Excel Examples - Zone Cours

Document Sample
Albert's Excel Examples - Zone Cours Powered By Docstoc
					Year                Beginning UCC CCA           Ending UCC
1                    $    7,100,000 $ 1,420,000 $ 5,680,000
2                    $   12,780,000 $ 2,556,000 $ 10,224,000
3                    $   10,224,000 $ 2,044,800 $ 8,179,200
4                    $    8,179,200 $ 1,635,840 $ 6,543,360
5                    $    6,543,360 $ 1,308,672 $ 5,234,688



CCA Rate            20%
Installation Cost    $  14,200,000
Resale Value         $   3,550,000
Year   Beg. Net Fixed Assets End. Net Fixed Assets Depreciation Net Capital Spending
0       $                 -   $          14,200,000 $       -   $         14,200,000
1       $           7,100,000 $           5,680,000 $ 1,420,000 $                 -
2       $          12,780,000 $          10,224,000 $ 2,556,000 $                 -
3       $          10,224,000 $           8,179,200 $ 2,044,800 $                 -
4       $           8,179,200 $           6,543,360 $ 1,635,840 $                 -
5       $           6,543,360 $                 -   $ 1,308,672 $          (3,550,000)
Year         Sales    Beginning NWC Additions to NWC        Ending NWC
1              90,000 $       550,000 $           1,260,000 $ 1,810,000
2             100,000 $    1,810,000 $            1,260,000 $ 3,070,000
3             110,000 $    3,070,000 $              882,000 $ 3,952,000
4             117,000 $    3,952,000 $                  -    $ 3,952,000
5              65,000 $    3,952,000 $                  -    $ 3,952,000




Unit Price   $   360
Add to NWC       35%
Unit Sales Year1                           90,000
Unit Sales Year2                          100,000    Unit Sales
Unit Sales Year3                          110,000    Revenue
Unit Sales Year4                          117,000    Variable Cost
Unit Sales Year5                           65,000    Fixed Cost
Unit Price                         $          360    Depreciation
Installation cost                  $   14,200,000    EBIT
Fixed costs                        $      175,000    Taxes
Variable cost per unit             $          227    Net Income
Net working capital beginning      $      550,000
Salvage value                      $    3,550,000    OCF
CCA Rate                                      20%    Net Capital Spending
Net working capital subsequently              35%    Additions to NWC
Tax rate                                      40%    Cash Flow from Assets
Required return                               25%
Project Life (years)                             5


                                                     NPV
                                                     IRR
                                                   YEAR
                0                 1                 2                3                 4                 5
                            90,000           100,000          110,000           117,000            65,000
                    $   32,400,000    $   36,000,000 $     39,600,000    $   42,120,000    $   23,400,000
                    $   20,430,000    $   22,700,000 $     24,970,000    $   26,559,000    $   14,755,000
                    $      175,000    $      175,000 $        175,000    $      175,000    $      175,000
                    $    1,420,000    $    2,556,000 $      2,044,800    $    1,635,840    $    1,308,672
                    $   10,375,000    $   10,569,000 $     12,410,200    $   13,750,160    $    7,161,328
                    $    4,150,000    $    4,227,600 $      4,964,080    $    5,500,064    $    2,190,656
                    $    6,225,000    $    6,341,400 $      7,446,120    $    8,250,096    $    4,970,672

                   $     7,645,000    $    8,897,400   $    9,490,920    $    9,885,936    $    6,279,344
$      14,200,000 $            -      $          -     $          -      $          -      $   (3,550,000)
$         550,000 $      1,260,000    $    1,260,000   $      882,000    $          -      $   (3,952,000)
$     (14,750,000) $     6,385,000    $    7,637,400   $    8,608,920    $    9,885,936    $   13,781,344




    $8,218,853.23
              47%
Unit Sales Year1                           90,000
Unit Sales Year2                          100,000    Unit Sales
Unit Sales Year3                          110,000    Revenue
Unit Sales Year4                          117,000    Variable Cost
Unit Sales Year5                           65,000    Fixed Cost
Unit Price                         $          360    Depreciation
Installation cost                  $   14,200,000    EBIT
Fixed costs                        $      175,000    Taxes
Variable cost per unit             $          227    Net Income
Net working capital beginning      $      550,000
Salvage value                      $    3,550,000    Project Net Income
CCA Rate                                      20%    Depreciation
Net working capital subsequently              35%    OCF
Tax rate                                      40%
Required return                               25%
Project Life (years)                             5
                                   YEAR
0                 1                 2                3                 4                 5
            90,000           100,000          110,000           117,000            65,000
    $   32,400,000    $   36,000,000 $     39,600,000    $   42,120,000    $   23,400,000
    $   20,430,000    $   22,700,000 $     24,970,000    $   26,559,000    $   14,755,000
    $      175,000    $      175,000 $        175,000    $      175,000    $      175,000
    $    1,420,000    $    2,556,000 $      2,044,800    $    1,635,840    $    1,308,672
    $   10,375,000    $   10,569,000 $     12,410,200    $   13,750,160    $    7,161,328
    $    4,150,000    $    4,227,600 $      4,964,080    $    5,500,064    $    2,190,656
    $    6,225,000    $    6,341,400 $      7,446,120    $    8,250,096    $    4,970,672

    $    6,225,000    $    6,341,400   $    7,446,120    $    8,250,096    $    4,296,797
    $    1,420,000    $    2,556,000   $    2,044,800    $    1,635,840    $    1,308,672
    $    7,645,000    $    8,897,400   $    9,490,920    $    9,885,936    $    5,605,469
Unit Sales Year1                           90,000
Unit Sales Year2                          100,000    Unit Sales
Unit Sales Year3                          110,000    Revenue
Unit Sales Year4                          117,000    Variable Cost
Unit Sales Year5                           65,000    Fixed Cost
Unit Price                         $          360    Depreciation
Installation cost                  $   14,200,000    EBIT
Fixed costs                        $      175,000    Taxes
Variable cost per unit             $          227    Net Income
Net working capital beginning      $      550,000
Salvage value                      $    3,550,000    Sales
CCA Rate                                      20%    Costs
Net working capital subsequently              35%    Taxes
Tax rate                                      40%    OCF
Required return                               25%
Project Life (years)                             5
                                   YEAR
0                 1                 2                3                 4                 5
            90,000           100,000          110,000           117,000            65,000
    $   32,400,000    $   36,000,000 $     39,600,000    $   42,120,000    $   23,400,000
    $   20,430,000    $   22,700,000 $     24,970,000    $   26,559,000    $   14,755,000
    $      175,000    $      175,000 $        175,000    $      175,000    $      175,000
    $    1,420,000    $    2,556,000 $      2,044,800    $    1,635,840    $    1,308,672
    $   10,375,000    $   10,569,000 $     12,410,200    $   13,750,160    $    7,161,328
    $    4,150,000    $    4,227,600 $      4,964,080    $    5,500,064    $    2,190,656
    $    6,225,000    $    6,341,400 $      7,446,120    $    8,250,096    $    4,970,672

    $   32,400,000    $   36,000,000   $   39,600,000    $   42,120,000    $   23,400,000
    $   20,605,000    $   22,875,000   $   25,145,000    $   26,734,000    $   14,930,000
    $    4,150,000    $    4,227,600   $    4,964,080    $    5,500,064    $    2,190,656
    $    7,645,000    $    8,897,400   $    9,490,920    $    9,885,936    $    6,279,344
Unit Sales Year1                           90,000
Unit Sales Year2                          100,000    Unit Sales
Unit Sales Year3                          110,000    Revenue
Unit Sales Year4                          117,000    Variable Cost
Unit Sales Year5                           65,000    Fixed Cost
Unit Price                         $          360    Depreciation
Installation cost                  $   14,200,000    EBIT
Fixed costs                        $      175,000    Taxes
Variable cost per unit             $          227    Net Income
Net working capital beginning      $      550,000
Salvage value                      $    3,550,000    CF Without Depreciation
CCA Rate                                      20%    Depreciation Tax Shield
Net working capital subsequently              35%    OCF
Tax rate                                      40%
Required return                               25%
Project Life (years)                             5
                                   YEAR
0                 1                 2                3                 4                 5
            90,000           100,000          110,000           117,000            65,000
    $   32,400,000    $   36,000,000 $     39,600,000    $   42,120,000    $   23,400,000
    $   20,430,000    $   22,700,000 $     24,970,000    $   26,559,000    $   14,755,000
    $      175,000    $      175,000 $        175,000    $      175,000    $      175,000
    $    1,420,000    $    2,556,000 $      2,044,800    $    1,635,840    $    1,308,672
    $   10,375,000    $   10,569,000 $     12,410,200    $   13,750,160    $    7,161,328
    $    4,150,000    $    4,227,600 $      4,964,080    $    5,500,064    $    2,190,656
    $    6,225,000    $    6,341,400 $      7,446,120    $    8,250,096    $    4,970,672

    $    7,077,000    $    7,875,000   $    8,673,000    $    9,231,600    $    5,082,000
    $      568,000    $    1,022,400   $      817,920    $      654,336    $      523,469
    $    7,645,000    $    8,897,400   $    9,490,920    $    9,885,936    $    5,605,469
Unit Sales Year1                           90,000
Unit Sales Year2                          100,000    Unit Sales
Unit Sales Year3                          110,000    Revenue
Unit Sales Year4                          117,000    Variable Cost
Unit Sales Year5                           65,000    Fixed Cost
Unit Price                         $          360    Depreciation
Installation cost                  $   14,200,000    EBIT
Fixed costs                        $      175,000    Taxes
Variable cost per unit             $          227    Net Income
Net working capital beginning      $      550,000
Salvage value                      $    3,550,000    OCF
CCA Rate                                      20%    Net Capital Spending
Net working capital subsequently              35%    Additions to NWC
Tax rate                                      40%    Cash Flow from Assets
Required return                               25%
Project Life (years)                             5


                                                     NPV
                                                     IRR




                                                     (S - C)(1 - Tc)
                                                     Additions to NWC
                                                     Capital Spending
                                                     Tax Shield due to CCA
                                                     NPV
                                                   YEAR
                0                 1                 2                3                 4                 5
                            90,000           100,000          110,000           117,000            65,000
                    $   32,400,000    $   36,000,000 $     39,600,000    $   42,120,000    $   23,400,000
                    $   20,430,000    $   22,700,000 $     24,970,000    $   26,559,000    $   14,755,000
                    $      175,000    $      175,000 $        175,000    $      175,000    $      175,000
                    $    1,420,000    $    2,556,000 $      2,044,800    $    1,635,840    $    1,308,672
                    $   10,375,000    $   10,569,000 $     12,410,200    $   13,750,160    $    7,161,328
                    $    4,150,000    $    4,227,600 $      4,964,080    $    5,500,064    $    2,190,656
                    $    6,225,000    $    6,341,400 $      7,446,120    $    8,250,096    $    4,970,672

                   $     7,645,000    $    8,897,400   $    9,490,920    $    9,885,936    $    6,279,344
$      14,200,000 $            -      $          -     $          -      $          -      $   (3,550,000)
$         550,000 $      1,260,000    $    1,260,000   $      882,000    $          -      $   (3,952,000)
$     (14,750,000) $     6,385,000    $    7,637,400   $    8,608,920    $    9,885,936    $   13,781,344




    $8,218,853.23
              47%


                                                   YEAR
                0                 1                 2                3                 4                 5
                    $    7,077,000    $    7,875,000 $      8,673,000    $    9,231,600    $    5,082,000
$        550,000    $    1,260,000    $    1,260,000 $        882,000    $          -      $   (3,952,000)
$     14,200,000    $          -      $          -    $           -      $          -      $   (3,550,000)
                    $      568,000    $    1,022,400 $        817,920    $      654,336    $    1,197,344
PV
 $20,588,709.12
   1,520,992.64
  13,036,736.00
  $2,187,872.75
  $8,218,853.23
Year       Beginning UCC Depreciation Ending UCC    Tax Shield
1           $ 14,200,000 $ 2,130,000 $ 12,070,000    $ 852,000
2           $ 12,070,000 $ 2,130,000 $ 9,940,000     $ 852,000
3           $   9,940,000 $ 2,130,000 $ 7,810,000    $ 852,000
4           $   7,810,000 $ 2,130,000 $ 5,680,000    $ 852,000
5           $   5,680,000 $ 2,130,000 $ 3,550,000    $ 852,000



              $
Installation Cost14,200,000
Resale Value  $   3,550,000
CCA
Year        ICF                 Tax Shield
        0    $    (5,000,000)
        1    $     1,500,000    $    187,500
        2    $     1,500,000    $    328,125
        3    $     1,500,000    $    246,094
        4    $     1,500,000    $    184,570
        5    $     3,500,000    $    (46,289)
PV           $     1,928,023    $    723,848
NPV          $     2,651,871



Year        Beginning UCC CCA          Ending UCC
        1    $   2,500,000 $   625,000 $    1,875,000
        2    $   4,375,000 $ 1,093,750 $    3,281,250
        3    $   3,281,250 $   820,313 $    2,460,938
        4    $   2,460,938 $   615,234 $    1,845,703
        5    $   1,845,703 $   461,426 $    1,384,277



Tax Rate                30%
r                       10%
CCA Rate                25%
           $
Salvage Value     2,000,000
Year           ICF          PV
           0    $  (30,000) $    (30,000)
           1    $    7,000 $       6,604
           2    $    7,000 $       6,230
           3    $    7,000 $       5,877
           4    $    7,000 $       5,545
           5    $    7,000 $       5,231




Tax rate               30%
r                       6%
IRR                  5.37%
NPV            $      (513)

After tax savings does not replace the initial cost.
Setting a Bid Price, Dingo's Didgeridoos Inc.



Beginnig UCC       Depreciation      Ending UCC
           150000             30,000        120,000
          120,000             30,000         90,000
            90,000            30,000         60,000
            60,000            30,000         30,000
            30,000            30,000              0



OCF                   NCS               Change NWC CFFA
                  0              150000         25000   -175000
                  0                                           0
                  0                                           0
                  0              -50000        -25000     75000   study guide

Find the OCF
Set NPV = 0
                        -129352.6845

Using goal seek we found that the OCF has to be               0
for the NPV to be 0.


then Find S                 24545.45455
Then find P                        24.55
                                 PV        Annuity Factor EAC
A   14000   1100   1100   1100    16735.54           2.49 6729.61
B   12000   1200   1200           14082.64           1.74 8114.29

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:12/3/2011
language:English
pages:19