Net working capital Equipment After tax salvage Salvage value Investment cash flow Table 7 by 7c3sP4v

VIEWS: 39 PAGES: 4

									Table 7.2
Operating revenues and
costs

Assumptions:

Growth rate in price              2%
Growth rate in unit cost          5%
                                                                      Sales        Cost Per       Variable     Fixed       Operating
                           Year        Production      Price        Revenues         Unit          Costs       Costs          Costs
                                   1         6,000      $100.00     $600,000.00         $64.00   $384,000.00   $50,000.00  $434,000.00
                                   2         9,000      $102.00     $918,000.00         $67.20   $604,800.00   $50,000.00  $654,800.00
                                   3        12,000      $104.04    $1,248,480.00        $70.56   $846,720.00   $50,000.00  $896,720.00
                                   4        13,000      $106.12    $1,379,570.40        $74.09   $963,144.00   $50,000.00 $1,013,144.00
                                   5        12,000      $108.24    $1,298,918.59        $77.79   $933,508.80   $50,000.00  $983,508.80
                                   6        10,000      $110.41    $1,104,080.80        $81.68   $816,820.20   $50,000.00  $866,820.20
                                   7         8,000      $112.62     $900,929.94         $85.77   $686,128.97   $50,000.00  $736,128.97
                                   8         6,000      $114.87     $689,211.40         $90.05   $540,326.56   $50,000.00  $590,326.56

Table 7.3
CCA

Initial cost               $800,000
Deprecation rate               20%
                                       Beginning                     Ending
                           Year          UCC           CCA            UCC
                                   1   $400,000.00    $80,000.00    $320,000.00
                                   2   $720,000.00   $144,000.00    $576,000.00
                                   3   $576,000.00   $115,200.00    $460,800.00
                                   4   $460,800.00    $92,160.00    $368,640.00
                                   5   $368,640.00    $73,728.00    $294,912.00
                                   6   $294,912.00    $58,982.40    $235,929.60
                                   7   $235,929.60    $47,185.92    $188,743.68
                                   8   $188,743.68    $37,748.74    $150,994.94
Table 7.1
Cash flow worksheet

Assumptions:
Corporate tax rate                    40%
Net working capital:
End of year 0:                     $40,000
End of year 8:                          $0
Other years (as % of sales):          15%
Salvage value                     $150,000

Year                                     0             1             2               3               4               5               6             7             8
I. Income
Sales revenues                                $600,000.00   $918,000.00   $1,248,480.00   $1,379,570.40   $1,298,918.59   $1,104,080.80   $900,929.94   $689,211.40
Operating costs                               $434,000.00   $654,800.00    $896,720.00    $1,013,144.00    $983,508.80     $866,820.20    $736,128.97   $590,326.56
CCA                                            $80,000.00   $144,000.00    $115,200.00       $92,160.00      $73,728.00      $58,982.40    $47,185.92    $37,748.74
EBIT                                           $86,000.00   $119,200.00    $236,560.00     $274,266.40     $241,681.79     $178,278.20    $117,615.05    $61,136.10
Taxes                                          $34,400.00    $47,680.00      $94,624.00    $109,706.56       $96,672.72      $71,311.28    $47,046.02    $24,454.44
Net income                                     $51,600.00    $71,520.00    $141,936.00     $164,559.84     $145,009.08     $106,966.92     $70,569.03    $36,681.66

II. Investments
Net working capital              $40,000.00    $90,000.00   $137,700.00    $187,272.00     $206,935.56     $194,837.79     $165,612.12    $135,139.49         $0.00
Change in n.w.c.                -$40,000.00   -$50,000.00   -$47,700.00    -$49,572.00     -$19,663.56      $12,097.77      $29,225.67     $30,472.63   $135,139.49
Equipment                      -$800,000.00
After tax salvage                                                                                                                                       $150,000.00
Investment cash flow           -$840,000.00   -$50,000.00   -$47,700.00     -$49,572.00     -$19,663.56     $12,097.77      $29,225.67     $30,472.63   $285,139.49
Table 7.4
Incremental Cash Flows

Year                           0            1                2              3               4               5               6              7             8
Sales revenues                            $600,000.00    $918,000.00   $1,248,480.00   $1,379,570.40   $1,298,918.59   $1,104,080.80   $900,929.94   $689,211.40
Operating costs                           $434,000.00    $654,800.00     $896,720.00   $1,013,144.00     $983,508.80     $866,820.20   $736,128.97   $590,326.56
Taxes                                      $34,400.00     $47,680.00      $94,624.00     $109,706.56      $96,672.72      $71,311.28    $47,046.02    $24,454.44
Operating cash flow                       $131,600.00    $215,520.00     $257,136.00     $256,719.84     $218,737.08     $165,949.32   $117,754.95    $74,430.40
Investment cash flow       -$840,000.00   -$50,000.00    -$47,700.00     -$49,572.00     -$19,663.56      $12,097.77      $29,225.67    $30,472.63   $285,139.49
Total project cash flow    -$840,000.00    $81,600.00    $167,820.00     $207,564.00     $237,056.28     $230,834.85     $195,174.99   $148,227.58   $359,569.89

Net present value:        Discount rate     NPV
                                     0%    $787,847.58
                                     1%    $708,377.76
                                     2%    $634,213.40
                                     3%    $564,928.17
                                     4%    $500,134.92
                                     5%    $439,481.70
                                     6%    $382,648.14
                                     7%    $329,342.35
                                     8%    $279,298.05
                                     9%    $232,272.10
                                    10%    $188,042.23
                                    11%    $146,405.04
                                    12%    $107,174.22
                                    13%     $70,178.93
                                    14%     $35,262.36
                                    15%      $2,280.47
                                    16%    -$28,899.23
                                    17%    -$58,398.68
                                    18%    -$86,330.23
                                    19%   -$112,797.47
                                    20%   -$137,896.04
Note: We have excluded remaining CCA after the project life. The present value of this amount is calculated below.

UCC end of year 8:             $150,994.94
Salvage value                  $150,000.00
Remaining UCC                      $994.94

                            PV end of year 8
                             of future CCA                      Total project
      Discount rate           tax shields      PV at year 0         NPV
                       0%           $397.98           $397.98    $788,245.56
                       1%           $379.03           $350.02    $708,727.79                                         NPV Profile
                       2%           $361.80           $308.79    $634,522.19
                       3%           $346.07           $273.19    $565,201.36
                                                                                                      $1,000,000
                       4%           $331.65           $242.33    $500,377.26
                       5%           $318.38           $215.49    $439,697.19
                       6%           $306.14           $192.07    $382,840.22                           $800,000
                       7%           $294.80           $171.58    $329,513.93
                       8%           $284.27           $153.58    $279,451.64                           $600,000
                       9%           $274.47           $137.75    $232,409.85
                      10%           $265.32           $123.77    $188,166.00




                                                                                                NPV
                                                                                                       $400,000
                      11%           $256.76           $111.41    $146,516.45
                      12%           $248.74           $100.46    $107,274.68
                      13%           $241.20            $90.73     $70,269.65                           $200,000
                      14%           $234.10            $82.07     $35,344.43
                      15%           $227.42            $74.34      $2,354.81                                 $0
                      16%           $221.10            $67.44    -$28,831.79
                      17%           $215.12            $61.26    -$58,337.42
                                                                                                      -$200,000
                      18%           $209.46            $55.72    -$86,274.50
                      19%           $204.09            $50.75   -$112,746.72                                               Discount Rate
                      20%           $198.99            $46.28   -$137,849.76

Internal Rate of Return

(found by Excel's Solver)

                                 IRR           NPV at IRR
                               15.073586%              $0.00

								
To top