Docstoc

Project on Market Penetration

Document Sample
Project on Market Penetration Powered By Docstoc
					Wacky Gantt Chart
Prepared 5/18/2005
             Year                            1                          2                          3
           Quarter                  1     2     3       4      1     2     3       4      1     2     3      4
        Week/Activity        0    1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10
             Purchase Rights
        R&D for Mass Production
                                         Setup Manufacturing Plant
                                                                                  Sales
3                    4                          5                          6
     4      1     2     3       4      1     2     3       4      1     2     3       4
    11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 12 11 12 1 2 3 4 5 6 7 8 9 12 11 12 73 74 75 76 77 78 79 80 81
Wacky Gantt Chart

                                          R&D                                           Small Facility


                                                                                        Large Facility
                                                                  Continue??


                                    Discount combined market NPV
                                    To NPV0                                                                      Present Value of Sales and Operations Cash Flow



                                                                                                                                       Weighted Average of Sale




           Year                                     1                                  2                                           3
        Quarter
       Week/Activity      0       1 1 3 4 2 6 7 3 9 10 11 12 1 1 3 4 2 6 7 3 9 10 11 12 1 1 3 4 2 6 7 3 9 10 4
                                    2     5     8       4      2     5     8       4      2     5     8
          Purchase Rights
        R&D for Mass Production
                                               Setup Manufacturing Plant
                                                                                                         Sales


        Technology Purchase       $75,000.00                                   Outflows from Factory Building and Equipment
                                       Outflows from R&D
                                                                                                                                         Outflows from Change
                          100% Market
                          80% Market

                          50% Market

                          100% Market
                          80% Market
                          50% Market


es and Operations Cash Flows for each Facility Decision and market possibility


                                           Termination In-Flow
  Weighted Average of Sales and Operations Inflows




                                       4                                         5    6
              4
             11 12 1 1 3 4 2 6 7 3 9 10 11 12 1 1 3 4 2 6 7 3 9 12 11 12 1 1 3 4 2 6 7 3 9 12 11 12 73 74
                     2     5     8       4      2     5     8       4      2     5     8       4




     Outflows from Change in Working Capital
Tim Hayes                                                                                          1
Shane Schmidt                                                                                      2
Kathy Sims                                                                                         3
Finance Midterm                                                                                    4
5/18/2005                                                                                          5
                                                                                                   6
Category                                                                                           7
 Sub Category                                                                                      8
                                          Variable    Value            Criteria/Notes              9
General Variables                                                                                 10
                     Basis for Whacky Technoloty      $75,000.00       Year 0                     11
                                          Inflation   3.50%                                       12
                               Capital Gains Rate     28%                                         13
                                   Discount Rate      15.50%                                      14
                                General Tax Rate      42.00%                                      15
R&D Variables                                                                                     16
                         Production Process R&D       $150,000.00      End of First Year          17
      Time Required for Production Process R&D        1                Year                       18
         Probability of success (workable proces)     40%                                         19
                                                                                                  20
Product Facilities                                                                                21
                   Sorrento Valley Land Purchase      $250,000.00      Year 0                     22
                              Appreciation of land    With inflation                              23
  Configuration A (Sole Use)                                                                      24
                                      Building Cost   $200,000.00      Year 0                     25
                     Two lines of Production Cost     $85,000.00                                  26
                     Total Production (both lines)    40,000           Doses per Year             27
                            Facility Salvage Value    $75,000.00                                  28
                         Equipment Salvage Value      $20,000.00                                  29
                            Time to create Facility   1                Year                       30
                   MACRS Years for Depreciation       7                                           31
                      Increase in Working Capital     $80,000.00       1 Year before Production   32
     Increase in Working Capital after Production     12%              of Sales                   33
  Configuration B (Multi-Production)                                                              34
                                      Building Cost   $350,000.00      Year 0                     35
                      Multiple Lines of Production    $150,000.00                                 36
                     Total Production (both lines)    50,000           Doses per Year             37
                            Facility Salvage Value    $125,000.00                                 38
                         Equipment Salvage Value      $60,000.00                                  39
                            Time to create Facility   1                Year                       40
                   MACRS Years for Depreciation       7                                           41
                      Increase in Working Capital     $100,000.00      1 Year before Production   42
     Increase in Working Capital after Production     12%              of Sales                   43
Market                                                                                            44
                                  Cases of Cancer     50,000           Per Year                   45
                     Market Price (Partial Market)    $70.00                                      46
                         Market Price (Full Market)   $80.00                                      47
                                      Price Growth    6.50%            3% Plus Inflation          48
                                       Project End    6                Years                      49
  Forecasted Market Penetration                       Market           Probability                50
                                     Whole Market     50,000           20%                        51
      Similar, Less Effective Drug by Competitors     40,000           50%                        52
 Comparable Products Developed by Competitors         25,000           30%                        53
Production                                                                   54
                      Production Costs First Year    65%         of Sales    55
                       Production Costs 2-6 Year     63%         of Sales    56
WACC (Weighted Average Cost of Capital)                                      57
 Bonds                                                                       58
                                     Coupon Rate     12%                     59
                               Payments per Year     2                       60
                                 Years to Maturity   20                      61
                                        Years Left   16                      62
                                     Market Price    $1,150.00               63
                                   Flotation Costs   4.50%                   64
 Preferred Stock                                                             65
                                               Par   $100.00                 66
                                 Annual Dividend     9.50%                   67
                                     Market Price    $108.00                 68
                                   Flotation Costs   $3.25       Per share   69
 Common Stock                                                                70
                             Current Selling Price   $42.50                  71
                                 Annual Dividend     $0.90                   72
                       Earnings paid in Dividends    10%         Annually    73
                          Return on Equity (ROE)     16%                     74
                     Flotation Costs of New Issue    5%          Per Share   75
                                   Risk Free Rate    4.50%                   76
                        BioTech Market Premium       14.75%                  77
                                              beta   1.4                     78
 Firm Capital Structure                                                      79
                                 Long Term Debt      25%                     80
                                  Preferred Stock    5%                      81
                                            Equity   70%                     82
Adjustments to Present Value of certain expenses
                            Inflation Rate  3.50%

                Research and Development
                                      P0        $150,000.00
                                          P1    $155,250.00


                               Small Facility
                                          P0    $200,000.00
                                          P2    $214,245.00

                              Large Facility
                                         P0     $350,000.00
                                          P2    $374,928.75

                   Small Facility Equipment
                                         P0     $85,000.00
                                          P2    $91,054.13

                   Large Facility Equipment
                                         P0     $150,000.00
                                          P2    $160,683.75

     Small Facility Equipment Salvage Value
                                         P0     $20,000.00
                                          P6    $24,585.11

     Large Facility Equipment Salvage Value
                                         P0     $60,000.00
                                          P6    $73,755.32

                Small Facility Salvage Value
                                          P0    $75,000.00
                                          P6    $92,194.15

                Large Facility Salvage Value
                                          P0    $125,000.00
                                          P6    $153,656.92

    Change in Working Capital Small Facility
                                         P0     $80,000.00
                                          P2    $85,698.00

    Change in Working Capital Large Facility
                                         P0     $100,000.00
                                          P2    $107,122.50
Schedule L: Land Purchase and Land Value

                              Land at Year 0    $250,000.00
                          Appreciation Rate     3.50%
                Land Price at purchase Date     $258,750.00

Land Value, Years 2-6                           Terminal Cash Flow
     Year    Core Year*    Value of Land***        Proceeds from Sale of Land   $307,313.83
        1             2       $267,806.25                                 Tax   ($20,396.81)
        2             3       $277,179.47                      Total Proceeds   $286,917.02
        3             4       $286,880.75
        4             5       $296,921.58       Tax
        5             6       $307,313.83                 Proceeds from Sale     $307,313.83
        6             7       $318,069.82                              Basis    ($258,750.00)
        7             8       $329,202.26                   Taxable Revenue       $48,563.83
        8             9       $340,724.34                           Tax Rate             42%
        9            10       $352,649.69                  Taxes Due at Sale      $20,396.81

*Coresponding year to cash flows NPV Decision
**Land is not depreciable
***Assumes 3.5% appreciation per year
Schedule B.s - Building Depreciation for Standard Facility
      Year Core Year*              Cost***         MACRS** Depreciation            Book Value
          1             3    $214,245.00              2.46%      $5,264.57       $208,980.43
          2             4    $214,245.00              2.56%      $5,493.46       $203,486.97
          3             5    $214,245.00              2.56%      $5,493.46       $197,993.51
          4             6    $214,245.00              2.46%      $5,264.57       $192,728.94
Straight Line 39 Year Depreciation
Note: The slight difference in the first and last year is due to only 11.5 months of depreciation
*Year corresponds to years in Sc 1.a
**Non residential real estate (buildings) with ADR greater than 25 years.
***This assumes that the cost to build the building rises with inflation

Terminal Cash Flow
        Proceeds from Sale of Building        $92,194.15
                                  Tax         $42,224.61
                      Total Proceeds         $134,418.76

Tax
                     Proceeds from Sale  $92,194.15
                           Book Value* ($192,728.94)
                       Taxable Revenue ($100,534.79)
                               Tax Rate         42%
                      Taxes Due at Sale ($42,224.61)

*Book Value adjusted .5 months for mid-month sale
**Everything is taxed at 42% except other investments
Schedule B.l - Building Depreciation for Large Facility
      Year Core Year*              Cost***         MACRS** Depreciation             Book Value
          1             3   $374,928.75                2.46%      $9,212.99       $365,715.76
          2             4   $374,928.75                2.56%      $9,613.56       $356,102.20
          3             5   $374,928.75                2.56%      $9,613.56       $346,488.64
          4             6   $374,928.75                2.46%      $9,212.99       $337,275.65
Straight Line 39 Year Depreciation
Note: The slight difference in the first year is due to only 11.5 months of depreciation
*Core Year corresponds to years in Sc 1.a
**Non residential real estate (buildings) with ADR greater than 25 years.
***This assumes that the cost to build the building does not change over year 1

Terminal Cash Flow
        Proceeds from Sale of Building       $153,656.92
                                  Tax         $77,119.87
                      Total Proceeds          $76,537.05

Tax
                   Proceeds from Sale     $153,656.92
                          Book Value* ($337,275.65)
                       Sale-Book         ($183,618.73)
                       Tax Rate                   42%
                    Taxes Due at Sale     ($77,119.87)
*Book Value adjusted .5 months for mid-month sale
Schedule E.s Depreciation for Equipment, Standard Building
      Year Core Year*           Cost***     MACRS** Depreciation            Book Value
         1            3 $91,054.13            14.29% $13,011.63             $78,042.49
         2            4 $91,054.13            24.49% $22,299.16             $55,743.34
         3            5 $91,054.13            17.49% $15,925.37             $39,817.97
         4            6 $91,054.13             6.25%      $5,686.33         $34,131.64
MACRS 7 Year Depreciation
*Core Year corresponds to years in Sc 1.a
**This assumes that the cost of the equipment does not increase with time
Note: Assumes only 1/2 year depreciation in year 6 due to sale

Terminal Cash Flow
    Proceeds from Saleof Equipment       $24,585.11
                               Tax       ($4,009.54)
                    Total Proceeds       $28,594.65

Tax
                  Proceeds from Sale      $24,585.11
                         Book Value      ($34,131.64)
                       Sale-Book          ($9,546.53)
                       Tax Rate                  42%
                   Taxes Due at Sale      ($4,009.54)


                                                                     ,
Schedule E.l Depreciation for Equipment, Large Building
      Year Core Year*            Cost***     MACRS** Depreciation            Book Value
         1            3 $160,683.75            14.29% $22,961.71            $137,722.04
         2            4 $160,683.75            24.49% $39,351.45             $98,370.59
         3            5 $160,683.75            17.49% $28,103.59             $70,267.00
         4            6 $160,683.75             6.25% $10,034.70             $60,232.30
MACRS 7 Year Depreciation
*Core Year corresponds to years in Sc 1.a
**This assumes that the cost of the equipment does not increase with time
Note: Assumes only 1/2 year depreciation in year 6 due to sale

Terminal Cash Flow
     Proceeds from Saleof Equipment       $73,755.32
                                Tax       ($5,679.67)
                     Total Proceeds       $68,075.65

Tax
                   Proceeds from Sale      $73,755.32
                          Book Value      ($60,232.30)
                      Taxable Income       $13,523.02
                             Tax Rate             42%
                    Taxes Due at Sale       $5,679.67
Scenario 1.a, Present Value of Large Market, Small Facility                                             Discount Rate       15.50%
                                  Year       0                  1                           2                   3                    4              5                 6
           Operating Cash Flows
                           Cases Sold                 0                 0                         0                40,000             40,000            40,000            40,000
                     Price Per Case*            $80.00            $85.20                    $90.74                $96.64            $102.92           $109.61           $116.73
                Revenue at Year End              $0.00             $0.00                     $0.00         $3,865,438.80      $4,116,692.32     $4,384,277.32     $4,669,255.35
                    Operating Cost%              $0.00             $0.00                     $0.00                   65%                63%               61%               59%
                   -Operations Costs             $0.00             $0.00                     $0.00        ($2,512,535.22)    ($2,593,516.16)   ($2,674,409.17)   ($2,754,860.66)
 Profits Before Depreciation & Taxes             $0.00             $0.00                     $0.00         $1,352,904.23      $1,523,176.79     $1,709,868.77     $1,914,395.28
                         -Depreciation           $0.00             $0.00                     $0.00           ($18,276.20)       ($27,792.62)      ($21,418.83)      ($10,950.90)
             Net Profits before Taxes            $0.00             $0.00                     $0.00         $1,334,628.03      $1,495,384.17     $1,688,449.94     $1,903,444.39
                                -Taxes           $0.00             $0.00                     $0.00          ($560,543.77)      ($628,061.35)     ($709,148.97)     ($799,446.64)
               Net Profits After Taxes           $0.00             $0.00                     $0.00           $774,084.26        $867,322.82       $979,300.96     $1,103,997.74
                        +Depreciation            $0.00             $0.00                     $0.00            $18,276.20         $27,792.62        $21,418.83        $10,950.90
              Operating Cash Inflows             $0.00             $0.00                     $0.00           $792,360.46        $895,115.44     $1,000,719.79     $1,114,948.64
                      Adjustments
                          IP Purchase      ($75,000.00)
                                  R&D                       ($155,250.00)
                       Land Purchase                        ($258,750.00)
                   Building Purchase                                                   ($214,245.00)
                Equipment Purchase                                                      ($91,054.13)
          Change in Working Capital                                                     ($85,698.00)       ($463,852.66)        ($30,150.42)     ($32,110.20)      ($34,197.36)
                Terminal Cash Flows                                                                                                                              $1,095,939.08
               Total Net Cash Flows        ($75,000.00)     ($414,000.00)              ($390,997.13)        $328,507.80        $864,965.01       $968,609.59     $2,176,690.35

        Relevant Cash Flows NPV1 N/A                            ($258,750.00)          ($390,997.13)        $328,507.80        $864,965.01       $968,609.59     $2,176,690.35
                            NPV1           $1,813,607.40

Total Depreciation Schedule
                               Year            0                      1                     2                  3                  4                 5                 6
                            Facility                $0.00                 $0.00                 $0.00         $5,264.57          $5,493.46         $5,493.46         $5,264.57     (Schedule B.s - Bld Dpr)
                         Equipment                  $0.00                 $0.00                 $0.00        $13,011.63         $22,299.16        $15,925.37         $5,686.33     (Schedule E.s Eqp Dpr)
                 Total Depreciation                 $0.00                 $0.00                 $0.00        $18,276.20         $27,792.62        $21,418.83        $10,950.90

Terminal Cash Flow
         Change in Working Capital           $646,008.64
                       Sale of Land          $286,917.02    (Schedule L - Land Purchase)
                   Sale of Building          $134,418.76    (Schedule B.s - Bld Dpr)
                 Sale of Equipment            $28,594.65    (Schedule E.s Eqp Dpr)
         Total Terminal Cash Flow          $1,095,939.08

*Even though they can produce only 40,000 it assumes full market price of $80 because of inelasticity combined with high demand should increase price
Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Scenario 1.b, Present Value of Medium Market, Small Facility                                          Discount Rate       15.50%
                                   Year      0                  1                         2                   3                    4               5                 6
             Operating Cash Flows
                            Cases Sold                0                0                        0                40,000              40,000            40,000            40,000
                      Price Per Case*           $70.00           $74.55                   $79.40                $84.56              $90.05            $95.91          $102.14
                 Revenue at Year End             $0.00            $0.00                    $0.00         $3,382,258.95       $3,602,105.78     $3,836,242.66     $4,085,598.43
                     Operating Cost%             $0.00            $0.00                    $0.00                   65%                 63%               61%               59%
                    -Operations Costs            $0.00            $0.00                    $0.00        ($2,198,468.32)     ($2,269,326.64)   ($2,340,108.02)   ($2,410,503.07)
  Profits Before Depreciation & Taxes            $0.00            $0.00                    $0.00         $1,183,791.28       $1,332,779.77     $1,496,135.25     $1,675,095.95
                          -Depreciation          $0.00            $0.00                    $0.00           ($18,276.20)        ($27,792.62)      ($21,418.83)      ($10,950.90)
              Net Profits before Taxes           $0.00            $0.00                    $0.00         $1,165,515.08       $1,304,987.15     $1,474,716.42     $1,664,145.05
                                 -Taxes          $0.00            $0.00                    $0.00          ($489,516.33)       ($548,094.60)     ($619,380.90)     ($698,940.92)
                Net Profits After Taxes          $0.00            $0.00                    $0.00           $675,998.75         $756,892.55       $855,335.52       $965,204.13
                         +Depreciation           $0.00            $0.00                    $0.00            $18,276.20          $27,792.62        $21,418.83        $10,950.90
               Operating Cash Inflows            $0.00            $0.00                    $0.00           $694,274.95         $784,685.17       $876,754.35       $976,155.03
                       Adjustments
                           IP Purchase     ($75,000.00)
                                   R&D                     ($155,250.00)
                        Land Purchase                      ($258,750.00)
                    Building Purchase                                             ($214,245.00)
                 Equipment Purchase                                                ($91,054.13)
           Change in Working Capital                                               ($85,698.00)           ($405,871.07)        ($26,381.62)     ($28,096.43)      ($29,922.69)
               Terminal Cash Flows                                                                                                                              $1,025,900.25
                Total Net Cash Flows       ($75,000.00) ($414,000.00)             ($390,997.13)            $288,403.87        $758,303.55       $848,657.93     $1,972,132.58

          Relevant Cash Flows NPV1 N/A                           ($258,750.00)    ($390,997.13)            $288,403.87        $758,303.55       $848,657.93     $1,972,132.58
                              NPV1     $1,547,398.38

Total Depreciation Schedule
                                 Year             0                   1                   2                   3                 4                  5                 6
                              Facility                 $0.00              $0.00               $0.00          $5,264.57          $5,493.46         $5,493.46         $5,264.57 (Schedule B.s - Bld Dpr)
                           Equipment                   $0.00              $0.00               $0.00         $13,011.63         $22,299.16        $15,925.37         $5,686.33 (Schedule E.s Eqp Dpr)
                   Total Depreciation                  $0.00              $0.00               $0.00         $18,276.20         $27,792.62        $21,418.83        $10,950.90

Terminal Cash Flow
          Change in Working Capital            $575,969.81
                       Sale of Land            $286,917.02 (Schedule L - Land Purchase)
                    Sale of Building           $134,418.76 (Schedule B.s - Bld Dpr)
                  Sale of Equipment              $28,594.65 (Schedule E.s Eqp Dpr)
          Total Terminal Cash Flow            $1,025,900.25

Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Scenario 1.c, Present Value of Small Market, Small Facility                                              Discount Rate       15.50%
                                  Year       0                   1                           2                   3                    4              5                 6
           Operating Cash Flows
                           Cases Sold                 0                 0                           0               25,000             25,000            25,000            25,000
                     Price Per Case*            $70.00            $74.55                      $79.40               $84.56             $90.05            $95.91           $102.14
                Revenue at Year End              $0.00             $0.00                       $0.00        $2,113,911.84      $2,251,316.11     $2,397,651.66     $2,553,499.02
                    Operating Cost%              $0.00             $0.00                       $0.00                  65%                63%               61%               59%
                   -Operations Costs             $0.00             $0.00                       $0.00       ($1,374,042.70)    ($1,418,329.15)   ($1,462,567.51)   ($1,506,564.42)
 Profits Before Depreciation & Taxes             $0.00             $0.00                       $0.00          $739,869.80        $832,987.59       $935,084.76     $1,046,935.19
                         -Depreciation           $0.00             $0.00                       $0.00          ($18,276.20)       ($27,792.62)      ($21,418.83)      ($10,950.90)
             Net Profits before Taxes            $0.00             $0.00                       $0.00          $721,593.59        $805,194.98       $913,665.93     $1,035,984.29
                                -Taxes           $0.00             $0.00                       $0.00         ($303,069.31)      ($338,181.89)     ($383,739.69)     ($435,113.40)
               Net Profits After Taxes           $0.00             $0.00                       $0.00          $418,524.28        $467,013.09       $529,926.24       $600,870.89
                        +Depreciation            $0.00             $0.00                       $0.00           $18,276.20         $27,792.62        $21,418.83        $10,950.90
              Operating Cash Inflows             $0.00             $0.00                       $0.00          $436,800.49        $494,805.70       $551,345.07       $611,821.79
                      Adjustments
                          IP Purchase      ($75,000.00)
                                  R&D                       ($155,250.00)
                       Land Purchase                        ($258,750.00)
                   Building Purchase                                                     ($214,245.00)
                Equipment Purchase                                                        ($91,054.13)
          Change in Working Capital                                                       ($85,698.00)      ($253,669.42)        ($16,488.51)     ($17,560.27)      ($18,701.68)
                Terminal Cash Flows                                                                                                                                 $842,048.32
               Total Net Cash Flows        ($75,000.00) ($414,000.00)                    ($390,997.13)       $183,131.06        $478,317.19       $533,784.80     $1,435,168.42

        Relevant Cash Flows NPV1 N/A                             ($258,750.00)           ($390,997.13)       $183,131.06        $478,317.19       $533,784.80     $1,435,168.42
                            NPV1               $848,599.72

Total Depreciation Schedule
                                Year             0                     1                     2                  3                  4                 5                 6
                             Facility                 $0.00                $0.00                 $0.00         $5,264.57          $5,493.46         $5,493.46         $5,264.57     (Schedule B.s - Bld Dpr)
                          Equipment                   $0.00                $0.00                 $0.00        $13,011.63         $22,299.16        $15,925.37         $5,686.33     (Schedule E.s Eqp Dpr)
                  Total Depreciation                  $0.00                $0.00                 $0.00        $18,276.20         $27,792.62        $21,418.83        $10,950.90

Terminal Cash Flow
         Change in Working Capital             $392,117.88
                       Sale of Land            $286,917.02    (Schedule L - Land Purchase)
                   Sale of Building            $134,418.76    (Schedule B.s - Bld Dpr)
                 Sale of Equipment              $28,594.65    (Schedule E.s Eqp Dpr)
         Total Terminal Cash Flow              $842,048.32

Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Scenario 2.a, Present Value of Large Market, Large Facility                                               Discount Rate       15.50%
                                  Year       0                 1                              2                   3                    4              5                 6
           Operating Cash Flows
                           Cases Sold                 0               0                             0                50,000             50,000            50,000            50,000
                     Price Per Case*            $80.00          $85.20                        $90.74                $96.64            $102.92           $109.61           $116.73
                Revenue at Year End              $0.00           $0.00                         $0.00         $4,831,798.50      $5,145,865.40     $5,480,346.65     $5,836,569.19
                    Operating Cost%              $0.00           $0.00                         $0.00                   65%                63%               61%               59%
                   -Operations Costs             $0.00           $0.00                         $0.00        ($3,140,669.03)    ($3,241,895.20)   ($3,343,011.46)   ($3,443,575.82)
 Profits Before Depreciation & Taxes             $0.00           $0.00                         $0.00         $1,691,130.13      $1,903,970.83     $2,137,335.80     $2,392,993.96
                         -Depreciation           $0.00           $0.00                         $0.00           ($32,174.70)       ($48,965.01)      ($37,717.15)      ($19,247.69)
             Net Profits before Taxes            $0.00           $0.00                         $0.00         $1,658,955.42      $1,855,005.82     $2,099,618.66     $2,373,746.26
                                -Taxes           $0.00           $0.00                         $0.00          ($696,761.28)      ($779,102.44)     ($881,839.84)     ($996,973.43)
               Net Profits After Taxes           $0.00           $0.00                         $0.00           $962,194.15      $1,075,903.38     $1,217,778.82     $1,376,772.83
                        +Depreciation            $0.00           $0.00                         $0.00            $32,174.70         $48,965.01        $37,717.15        $19,247.69
              Operating Cash Inflows             $0.00           $0.00                         $0.00           $994,368.85      $1,124,868.38     $1,255,495.97     $1,396,020.53
                      Adjustments
                          IP Purchase     ($75,000.00)
                                  R&D                     ($155,250.00)
                       Land Purchase                      ($258,750.00)
                   Building Purchase                                                     ($374,928.75)
                Equipment Purchase                                                       ($160,683.75)
          Change in Working Capital                                                      ($107,122.50)        ($579,815.82)       ($37,688.03)     ($40,137.75)      ($42,746.70)
                Terminal Cash Flows                                                                                                                                $1,239,040.53
               Total Net Cash Flows       ($75,000.00) ($414,000.00)                     ($642,735.00)         $414,553.03      $1,087,180.36    $1,215,358.22     $2,592,314.35

        Relevant Cash Flows NPV1 N/A                            ($258,750.00)            ($642,735.00)         $414,553.03      $1,087,180.36    $1,215,358.22     $2,592,314.35
                            NPV1     $2,145,230.50

Total Depreciation Schedule
                                Year             0                     1                      2                   3                 4                 5                 6
                             Facility                 $0.00                $0.00                  $0.00          $9,212.99         $9,613.56         $9,613.56         $9,212.99     (Schedule B.s - Bld Dpr)
                          Equipment                   $0.00                $0.00                  $0.00         $22,961.71        $39,351.45        $28,103.59        $10,034.70     (Schedule E.s Eqp Dpr)
                  Total Depreciation                  $0.00                $0.00                  $0.00         $32,174.70        $48,965.01        $37,717.15        $19,247.69

Terminal Cash Flow
         Change in Working Capital             $807,510.80
                       Sale of Land            $286,917.02    (Schedule L - Land Purchase)
                   Sale of Building             $76,537.05    (Schedule B.s - Bld Dpr)
                 Sale of Equipment              $68,075.65    (Schedule E.s Eqp Dpr)
         Total Terminal Cash Flow            $1,239,040.53

Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Scenario 2.b, Present Value of Medium Market, Large Facility                                             Discount Rate       15.50%
                                 Year      0                  1                              2                   3                    4              5                 6
          Operating Cash Flows
                          Cases Sold                 0               0                              0               40,000             40,000            40,000            40,000
                    Price Per Case*            $70.00          $74.55                         $79.40               $84.56             $90.05            $95.91           $102.14
               Revenue at Year End              $0.00           $0.00                          $0.00        $3,382,258.95      $3,602,105.78     $3,836,242.66     $4,085,598.43
                   Operating Cost%              $0.00           $0.00                          $0.00                  65%                63%               61%               59%
                  -Operations Costs             $0.00           $0.00                          $0.00       ($2,198,468.32)    ($2,269,326.64)   ($2,340,108.02)   ($2,410,503.07)
Profits Before Depreciation & Taxes             $0.00           $0.00                          $0.00        $1,183,791.28      $1,332,779.77     $1,496,135.25     $1,675,095.95
                        -Depreciation           $0.00           $0.00                          $0.00          ($32,174.70)       ($48,965.01)      ($37,717.15)      ($19,247.69)
            Net Profits before Taxes            $0.00           $0.00                          $0.00        $1,151,616.58      $1,283,814.76     $1,458,418.10     $1,655,848.25
                               -Taxes           $0.00           $0.00                          $0.00         ($483,678.96)      ($539,202.20)     ($612,535.60)     ($695,456.27)
              Net Profits After Taxes           $0.00           $0.00                          $0.00          $667,937.62        $744,612.56       $845,882.50       $960,391.99
                       +Depreciation            $0.00           $0.00                          $0.00           $32,174.70         $48,965.01        $37,717.15        $19,247.69
             Operating Cash Inflows             $0.00           $0.00                          $0.00          $700,112.32        $793,577.57       $883,599.64       $979,639.68
                     Adjustments
                         IP Purchase      ($75,000.00)
                                 R&D                     ($155,250.00)
                      Land Purchase                      ($258,750.00)
                  Building Purchase                                                      ($374,928.75)
               Equipment Purchase                                                        ($160,683.75)
         Change in Working Capital                                                       ($107,122.50)      ($405,871.07)        ($26,381.62)     ($28,096.43)      ($29,922.69)
                Terminal Cash Flows                                                                                                                               $1,028,924.03
              Total Net Cash Flows        ($75,000.00) ($414,000.00)                     ($642,735.00)       $294,241.24        $767,195.95       $855,503.22     $1,978,641.02

       Relevant Cash Flows NPV1 N/A                             ($258,750.00)            ($642,735.00)       $294,241.24        $767,195.95       $855,503.22     $1,978,641.02
                           NPV1              $1,346,603.46

Total Depreciation Schedule
                               Year              0                     1                     2                  3                  4                 5                 6
                            Facility                  $0.00                $0.00                 $0.00         $9,212.99          $9,613.56         $9,613.56         $9,212.99     (Schedule B.s - Bld Dpr)
                         Equipment                    $0.00                $0.00                 $0.00        $22,961.71         $39,351.45        $28,103.59        $10,034.70     (Schedule E.s Eqp Dpr)
                 Total Depreciation                   $0.00                $0.00                 $0.00        $32,174.70         $48,965.01        $37,717.15        $19,247.69

Terminal Cash Flow
        Change in Working Capital              $597,394.31
                      Sale of Land             $286,917.02    (Schedule L - Land Purchase)
                  Sale of Building              $76,537.05    (Schedule B.s - Bld Dpr)
                Sale of Equipment               $68,075.65    (Schedule E.s Eqp Dpr)
        Total Terminal Cash Flow             $1,028,924.03

Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Scenario 2.c, Present Value of Small Market, Large Facility                                            Discount Rate       15.50%
                                  Year      0                 1                            2                   3                    4              5                 6
           Operating Cash Flows
                           Cases Sold                0               0                           0                25,000             25,000            25,000            25,000
                     Price Per Case*          $70.00           $74.55                      $79.40                $84.56             $90.05            $95.91           $102.14
                Revenue at Year End             $0.00           $0.00                       $0.00         $2,113,911.84      $2,251,316.11     $2,397,651.66     $2,553,499.02
                    Operating Cost%             $0.00           $0.00                       $0.00                   65%                63%               61%               59%
                   -Operations Costs            $0.00           $0.00                       $0.00        ($1,374,042.70)    ($1,418,329.15)   ($1,462,567.51)   ($1,506,564.42)
 Profits Before Depreciation & Taxes            $0.00           $0.00                       $0.00           $739,869.80        $832,987.59       $935,084.76     $1,046,935.19
                         -Depreciation          $0.00           $0.00                       $0.00           ($32,174.70)       ($48,965.01)      ($37,717.15)      ($19,247.69)
             Net Profits before Taxes           $0.00           $0.00                       $0.00           $707,695.09        $784,022.58       $897,367.61     $1,027,687.49
                                -Taxes          $0.00           $0.00                       $0.00          ($297,231.94)      ($329,289.49)     ($376,894.40)     ($431,628.75)
               Net Profits After Taxes          $0.00           $0.00                       $0.00           $410,463.15        $454,733.10       $520,473.22       $596,058.75
                        +Depreciation           $0.00           $0.00                       $0.00            $32,174.70         $48,965.01        $37,717.15        $19,247.69
              Operating Cash Inflows            $0.00           $0.00                       $0.00           $442,637.86        $503,698.11       $558,190.36       $615,306.44
                      Adjustments
                          IP Purchase    ($75,000.00)
                                  R&D                    ($155,250.00)
                       Land Purchase                     ($258,750.00)
                   Building Purchase                                                 ($374,928.75)
                Equipment Purchase                                                   ($160,683.75)
          Change in Working Capital                                                  ($107,122.50)        ($253,669.42)        ($16,488.51)     ($17,560.27)      ($18,701.68)
                Terminal Cash Flows                                                                                                                               $845,072.11
               Total Net Cash Flows      ($75,000.00) ($414,000.00)                  ($642,735.00)         $188,968.43        $487,209.59       $540,630.09     $1,441,676.86

        Relevant Cash Flows NPV1 N/A                          ($258,750.00)          ($642,735.00)         $188,968.43        $487,209.59       $540,630.09     $1,441,676.86
                            NPV1             $647,804.80

Total Depreciation Schedule
                                Year            0                    1                     2                  3                  4                 5                 6
                             Facility               $0.00                $0.00                 $0.00         $9,212.99          $9,613.56         $9,613.56         $9,212.99     (Schedule B.s - Bld Dpr)
                          Equipment                 $0.00                $0.00                 $0.00        $22,961.71         $39,351.45        $28,103.59        $10,034.70     (Schedule E.s Eqp Dpr)
                  Total Depreciation                $0.00                $0.00                 $0.00        $32,174.70         $48,965.01        $37,717.15        $19,247.69

Terminal Cash Flow
         Change in Working Capital           $413,542.38
                       Sale of Land          $286,917.02    (Schedule L - Land Purchase)
                   Sale of Building           $76,537.05    (Schedule B.s - Bld Dpr)
                 Sale of Equipment            $68,075.65    (Schedule E.s Eqp Dpr)
         Total Terminal Cash Flow            $845,072.11

Note: all calculations will be valued at end of year for this spreadsheet. Schedule calculations will ensure end-of-year values
Schedule P: NPV Calculation for alternative buildings and markets
         Year 0                        1                    2                    3            4              5              6
                                                                                                      NPV1            Probably Value
                                                                                                  20%   $2,145,230.50   $429,046.10


                                     Probable NPV1          $1,296,689.27                         50%     $1,346,603.46     $673,301.73


                                                       Large Facility                             30%       $647,804.80     $194,341.44

Present Value of Project
Schedule A                  Schedule R
  $272,316.22                         $556,400.24

                                                                                                        NPV1            Probably Value
                                                       Standard Facility                          20%     $1,813,607.40   $362,721.48


                                     Probable NPV1          $1,391,000.59                         50%     $1,547,398.38     $773,699.19


                                                                                                  30%       $848,599.72     $254,579.92


Schedule R: NPV1 Risk Adjustment for R&D Failure
                           Highest Value Alternative        $1,391,000.59                               Schedule Z: Likely Payoff for
                                         Probability                 40%                                Differring Markets and Facilities
                                              NPV1           $556,400.24                                Large Facility
                                                                                                          Small Market       $14,932.40
Schedule A: Adjustments for 100% Probability Cash Flow                                                    Medium Market     $256,940.59
                                                       NPV1               NPV0                            Large Market      $533,521.39
                         Market Sensitive Cash Flow          $556,400.24      $481,731.81               Standard Facility
                         Research and Development           ($155,250.00)    ($134,415.58)                Small Market       $84,471.77
                             Initial IP Purchase Price               N/A       ($75,000.00)               Medium Market     $326,479.96
                                                 Total       $401,150.24      $272,316.22                 Large Market      $418,673.56
Probability Alternatives and NPV1
Large/Medium Market           Large Market Probability Large Facility NPV Small Facility NPV Difference
20/50                                               20      $1,302,215.68   $1,391,000.59        $88,784.91
21/49                                               21      $1,310,201.95   $1,393,662.68        $83,460.73
22/48                                               22      $1,318,188.22   $1,396,324.77        $78,136.55
23/47                                               23      $1,326,174.49   $1,398,986.86        $72,812.37
40/30                                               40      $1,461,941.09   $1,444,242.39       ($17,698.70)
50/20                                               50      $1,541,803.79   $1,470,863.29       ($70,940.50)
60/10                                               60      $1,621,666.49   $1,497,484.19 ($124,182.30)
Delta Large-Small = 0         37% Probability Large Market
                              33% Probability of Small Market



                                                 Probability of Large Market Required for Large Facility

                                            $100,000.00
        Delta of Large and Small Facility




                                             $50,000.00
                                                                                                   Difference in NPV
                                                   $0.00
                                                            0             50             100
                                             ($50,000.00)                                          Linear (Difference in
                                                                                                   NPV)

                                            ($100,000.00)

                                            ($150,000.00)
                                                                Large Market Probality
                     7
obably Value
               Large Market


               Medium Market


               Small Market




obably Value
               Large Market


               Medium Market


               Small Market

				
DOCUMENT INFO
Description: Project on Market Penetration document sample