Docstoc

0 324 25625 6_0201T case12

Document Sample
0 324 25625 6_0201T case12 Powered By Docstoc
					Case 12      Medium Student Model. Leasing                           PNC                    8/19/04
This case illustrates the lease-versus-purchase decision. The model calculates the NAL for the lessee and the
NPV and IRR for the lessor, and it does sensitivity and scenario analyses to show how the NAL and the NPV
vary with the lease payment.
  Note that most inputs are entered in blue type; generally speaking, these are the only cells you should
change. Also, some cells are blank and must be filled in. The cells to be filled in are shaded in light green.
Until those cells are filled in, the model will not produce valid results. You should fill in the blank cells
starting in the upper left corner. As you fill in the cells, the errors, zeros, and other incorrect cells will change
to the correct numbers.
This model begins with a set of inputs, with the ones used in the model shown in Column D, beginning with
the lease payment on Row 12, in blue type. These inputs are then used in the calculating sections of the
model. Changing the blue inputs will change the forecast.
The forecast can be changed manually by typing data into Column D in the Input Section. Alternatively, you
can use Excel's Scenario Manager. This is a useful and easy-to-use tool when scenarios have been specified
as they have been for this model. Simply click Tools>Scenarios to open a dialog box that lists the 2
scenarios. Select one of them and then click "Show," and immediately the inputs for that scenario will be
inserted into the relevant cells in the model. Click "Close" to exit Scenario Master and look at results under
the selected scenario. You can go back to the other scenario by repeating the process.
It is a bit more difficult--but not terribly difficult--to create scenarios, edit existing ones, and get scenario
summaries. We provide a Scenario Summary as generated by Excel on Tab 2 to show SLC's forecasted value
under the 2 scenarios, and the Tutorial explains how to create and modify scenarios.

                                     Active Case:
                                     this data used
Table 12-1. Data for Case            in calculations     Scenario Data for:
                                       Base Case       Base Case Neutral Case
Lease payment:
Lessee Data:
Equipment cost
Maintenance cost
Loan interest rate
Expected residual value
Lessee's tax rate


Lessor Data:
Equipment cost to lessor
Maintenance cost to lessor
Lessor's opportunity cost
Expected residual value to lessor
Lessor's tax rate

Tax Depreciation Table: (The entries in Section III of the table change if the lessor has a cost advantage)
 I. Pertaining to Both          II. Pertaining to Lessee                III. Pertaining to Lessor
             MACRS          Depr'n       Annual      Ending          Depr'n       Annual      Ending
    Year       Rate          Basis       Depr'n     Book Value        Basis       Depr'n Book Value
     1          20%
     2          32%
     3          19%
     4          12%
     5          11%
                                                            1 of 7
6    6%
    100%




           2 of 7
Table 1: Lessee's Analysis: Changes with the scenario. Start with base case.
After-tax cost of debt:

Borrow and Buy:          Year 0       Year 1      Year 2            Year 3     Year 4
Loan proceeds: Inflow
Equipment cost: Outflow
Interest paid
Interest tax shield
Repay loan principal
Maintenance cost
Maintenance tax shield
Depreciation tax shield
Residual value
Saving or Tax on res val
Net CF if buy

PV cost of owning =

Cost of Leasing:             Year 0   Year 1      Year 2            Year 3     Year 4
Lease payment
Lease tax shield
Net leasing CF

PV cost of leasing =

NAL = Net advantage to leasing = PV cost of leasing       -        PV cost of owning    =   NAL
                                                      -                                =

A positive NAL means leasing costs less than buying, i.e., leasing has the advantage.

ALTERNATIVE ANALYSIS: WORK WITH ANNUAL CASH FLOW DIFFERENTIAL
                         Year 0 Year 1   Year 2    Year 3   Year 4
CF buying - CF leasing =

NAL = PV of Differential =

Table 2-a. Lessor's Analysis: Changes with the scenario. Start with base case.                    Table 2-b. Lessor's A




                                                          3 of 7
Lessor's after-tax opportunity cost:      0.00%
                          Year 0       Year 1      Year 2        Year 3      Year 4               Equip cost
Equip cost                                                                                        Maintenance
Maintenance                                                                                       Maint tax saving
Maint tax saving                                                                                  Depreciation tax shield
Depreciation tax shield                                                                           Residual value
Residual value                                                                                    Tax on residual
Tax on residual                                                                                   Lease payment
Lease payment                                                                                     Tax on lease payment
Tax on lease payment
           Lessor's CF
           PV of CF                                                                               Lessor's NPV =
Lessor's NPV =                                                                                    Lessor's IRR =
Lessor's IRR =
                                                                                                   Use Goal Seek on the leas
Table 3. NAL and NPV To Make Bargaining Graph.                  Use base case data for valid graph.to find a payment that wou
             NAL to      NPV to                                                                    same NPV as the original l
 Lease       Lessee      Lessor                                                                    $23,134. A payment of $31
Pmt (D9)                                                                                           the job using base case in
$280,000
$290,000
$300,000
$310,000
$320,000
$330,000

GRAPHING THE RESULTS OF DATA TABLES. Use base case data except lease payment.
We can use of the data tables to create an interesting and useful graph. To get a usable graph,
simply select (highlight) the range A96:C101 in Table 3, click the Chart icon on the top menu bar, then
click for a scatter diagram, then indicate a chart with lines connecting points, and then click finish.
You could label the axes and do some formating to make the graph look better, as we did with the
graph below, but we will leave that to you.

Note that the graph is scaled so as to be meaningful for the Base Case scenario. The lines may not
cross where we can see them if the inputs are changed materially.




                                                       4 of 7
                                 Graph for Bargaining
                                                                                               Lease Pmt (D12)=
                                                                                                          NAL =
  $75,000
                                                                                                          NPV =
                                  NAL to Lessee
                                                                                    Force NAL = NPV by changing D9:
                                  NPV to Lessor
  $50,000
                                                                                        Synergy = NAL + NPV =

                                                                                         Use Goal Seek to find lease payments that
  $25,000                                                                                 cause NAL=0, NPV=0, and NAL=NPV.
                                                                                         NAL = 0 when Lease =
                                                                                         NPV = 0 when Lease =
       $0                                                                                NPV=NAL when Lease =
       $ 290,000     $ 295,000      $ 300,000      $ 305,000            $ 310,000
                                                                                         The setups for Goal Seek to cause
  -$25,000                                                                               NAL=0, NPV=0, and NAL=NPV
                                                                                         and NAL=NPV by changing D12
                                                                                         are shown below.
  -$50,000
                                 Lease Payment

             To get NAL = 0                         To get NPV = 0                                    To get NAL = NPV




Note that the NAL is zero or negative if the lease payment is $305,547 or more, so the lessee would not lease. Also,
the NPV is zero if the payment is $290,457 or less, so the lessor would not offer the lease. Thus, to be acceptable to
both the lessee and the lessor, the lease payment must be within the range of $290,457 to $305,547. At the lower
limit the lessee gets all the benefits of leasing, and at the upper limit the benefits all go to the lessor. At a lease
payment of $297,720 the lessee and lessor share the benefits (the synergy), which total $35,212, equally.




                                                               5 of 7
Table 4. Data Tables to Show Sources of Leasing Synergy: Go to Neutral Scenario before using.
  From the Neutral Scenario, we change the differentials between the lessee's and the lessor's variables as shown.
  Wider differentials create synergy, thus making leasing more economically justified. These tables are not
  meaningful at other than the neutral scenario. Click Tools > Scenarios > Neutral > Show > Close. Look at
  data tables and at the graph above. Go back to base case by clicking Tools > Scenarios > Base Case > Show
  > Close. Lessee's
  Lessee's                 Synergy
interest rate Interest   from factor
  vs. base Rate (D16)
   -0.5%                              The higher the lessee's interest rate, the
    0.0%                              more expensive is buying and owning, hence
    0.5%                              the better is leasing and the higher the synergies.
    1.0%
    1.5%

  Lessor's                  Synergy
Purchse cost   Lessor's   from factor
Advantage Cost (D21)                    The greater the lessor's purchase price
  -1.0%                                 advantage, the greater the synergies.
   0.0%
   1.0%
   1.5%
   2.0%

 Lessor's    Lessor's   Synergy
Maint. costMaintenancefrom factor
Advantage Cost (D22)
  -5.0%                                 The greater the lessor's maintenance cost
   0.0%                                 advantage, the greater the synergies.
   5.0%
  10.0%
  15.0%

 Lessor's  Lessor's  Synergy
 Residual Residual from factor
Advantage Value(D24)
  -5.0%                                 The greater the lessor's residual
   0.0%                                 advantage, the greater the synergies.
   5.0%
  10.0%
  15.0%

 Lessee's
    tax                                           Leasing Synergies Created by Differential Tax Rates
rate (D18)                                                     Lessor's tax rate (D25)
     $0         0.0%        20.0%        25.0%       30.0%       35.0%      40.0%        45.0%        50.0%
   0.0%




                                                          6 of 7
Effects of Leasing on Financial Statements                                     8/19/04



Original Situation:
Current Assets             $100                Current Liabilities       $50
Fixed Assets                100                Long-term Debt             50
                                               Common Equity             100
Total Assets               $200                Liabilities and Equity   $200
Debt Ratio:               50.0%

After adding $100 of fixed assets paid for with long-term debt:
Current Assets             $100            Current Liabilities           $50
Fixed Assets                200            Long-term Debt                150
                                           Common Equity                 100
Total Assets               $300            Liabilities and Equity       $300
Debt Ratio:              66.7%

After leasing $100 of fixed assets, Capital lease:
Current Assets             $100             Current Liabilities          $50
Old fixed assets           $100             Long-term Debt               $50
Value of leased assets       100            Capitalized lease pmts       100
                                            Common Equity                100
Total Assets               $300             Liabilities and Equity      $300
Debt Ratio:               66.7%

After leasing $100 of fixed assets, Operating lease:
Current Assets             $100            Current Liabilities           $50
Fixed Assets                 100           Long-term Debt                 50
                                           Common Equity                 100
Total Assets               $200            Liabilities and Equity       $200
Debt Ratio:               50.0%

                                    Original     BorrowCap Lease Op Lease
Sales Revenues                         $200        $200     $200     $200
Operating costs                         100         100      100      100
 Operating income (EBIT)               $100        $100     $100     $100
Debt service (interest + amort)           20         40       20       20
Lease payments                                                20
Taxable income                          $80         $60      $60      $80
Taxes at 40%                             32          24       24       32
Net income                              $48         $36      $36      $48

EBITDA/(debt service + lease pmts        5.0          2.5        2.5     5.0

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:11/8/2011
language:English
pages:7
g6qAmXc4 g6qAmXc4
About