Calculation of Cash Flows-Finance Assignment Solution Sample

QUESTION

 

This case is a straightforward valuation exercise using the weighted average cost of capital to calculate the net present value of a project. As such, it could be incorporated in a number of different courses, ranging from an introductory course in financial management, to a finance course in valuation or a real estate course in valuation. The case can be viewed as a “lease” versus “buy” decision. There are three options available to FNB:

 (1) Continue to rent.

(2) Buy existing building and continue to lease existing and additional office space.

(3) Build a new building

 To calculate incremental cash flows, we need a “base case.” Assume that the base case is to simply continue to rent. This implies that options 2 and 3 will have positive incremental cash flows equal to the rent that would have been paid under option 1.

One page memo identifying the ten rubrics. Four deal with the proper calculation of cash flows for the three options available to FNB. Four deal with proper calculation of the WACC for discounting the cash flows. Two deal with the qualitative aspects of the case that cannot easily be addressed by NPV/WACC.

The spread sheet(s) with valuations will need to be included as an excel spreadsheet

 

ANSWER

Lease or Buy Decision Making FNB Bancorp

Computation of cash outflow in 3 options

  • Option-1 – Renting and leasing the space required

As per the data provided the PV of option to rent and lease entails a cash outflow of $ 2,76,72,271.75 in present value terms up to 2014 starting from 2000.

  • Option-2 Buying Class A and renting the rest space

As per the given information about buying the space and renovating it, the PV of construction and renting netted off with the PV of terminal value and rent inflow is found to be $ 1,05,90,502.38

Option-3 Buying the high raiser building

As per the given data the PV of construction of building netted off with the depreciation tax saving, terminal value and rent received forms to be -$ 13,03,20,193.28

Estimation of terminal Value

The terminal value was considered as the capitalised value i.e. for option-2 $ -96800039.72 and for option-3 $ 23,75,86,639.69

Computation of WACC

  • Rate of Risk Free return

Since the case in hand has the life of nearly 10 years the Rf is taken as 10-year Treasury Bond Rate i.e. 6.50%

  • Beta

De-levering the beta with the debt equity ratio of FNB, which was found by considering debt value of 8.1 billion and market value of equity, came to 0.45. Therefore Levered Beta of FNB as a REIT using the given tax rate was found to be 1.034

  • Cost of equity

As per capital asset pricing model, the cost equity using the data above and a historical market return was found to 15.18%

  • WACC

Using the average rate of debt on market value of debt I.e. 6.56% and considering weighted average of Equity and Debt the cost of Capital is computed as 10.78%

Incremental Cash flows

Taking option 1 as Base, none of the option gave in positive cash flows in present value terms. Considering option 2 over 1 will have a saving $ 1,01,24,985.76 impact and whereas considering option 3 over 2 will have a saving of $ 16,18,01,715.25 impact over the cash position of FNB in present value terms.

Decision and other factors

FNB shall go with 3rd option of constructing a high raiser as it will lead to highest saving.

 

Option 1    
Class A office at 420 main street 109000 sq ft
Rent per sq feet till 2004 10  
Life of lease-initial 10  
Rent per sq feet from 2004 26  
class c office space 27000 sq ft
Rent per sq feet till 2004 9  
Rent per sq feet from 2004 13  
new rented space per year 10000 sq ft
current rent 13  
increment 2%  
   

 

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
  2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014
Option -1                              
Class A office 1090000 1090000 1090000 2948493.6 3007463.472 3067612.74144 3128964.9962688 3191544.29619418 3255375.18211806 3320482.68576042 3386892.33947563 3454630.18626514 3523722.78999045 3594197.24579025 3666081.19070606
Class C office 243000 243000 243000 365180.4 372484.008 379933.68816 387532.3619232 395283.009161664 403188.669344897 411252.442731795 419477.491586431 427867.04141816 436424.382246523 445152.869891454 454055.927289283
Additional Space 135252 275914.08 422148.5424 574122.017664 732005.5725216 895974.820766438 1066210.03671206 1242896.2713672 1426223.47139387 1616386.60091305 1813585.76622444 2018026.34350792 2229919.10957625 2449480.37574992 1950000
Total 1468252 1608914.08 1755148.5424 3887796.017664 4111953.0525216 4343521.25036644 4582707.39490406 4829723.57672304 5084787.32285682 5348121.72940527 5619955.5972865 5900523.57119122 6190066.28181322 6488830.49143162 6070137.11799534
                               
Option-2                              
Buy Class A 19600560 8223571.2 1197042.624 1220983.47648 1245403.1460096 1270311.20892979 1295717.43310839 1321631.78177056 1348064.41740597 1375025.70575409 1402526.21986917 1430576.74426655 1459188.27915188 1488372.04473492 1518139.48562962
Rent from retail -281280 -281280 -281280 -609440 -609440 -609440 -609440 -609440 -609440 -609440 -609440 -609440 -609440 -609440 -609440
Class C office 243000 243000 243000 358020 365180.4 372484.008 379933.68816 387532.3619232 395283.009161664 403188.669344897 411252.442731795 419477.491586431 427867.04141816 436424.382246523 445152.87
Additional Space 132600 270504 413871.12 562864.7232 717652.52208 878406.68702592 1045303.95756084 1218525.75624236 1398258.3052881 1584692.74599318 1778025.26100435 1978457.19951757 2186195.20546692 2401451.34877443 2401451.34877443
Depreciation tax saving 236600 334600 432600 432600 432600 432600 432600 432600 432600 432600 432600 432600 432600 432600 334600
Terminal Value                             100220743.420653
Total 19458280 8121195.2 1140033.744 1099828.19968 1286196.0680896 1479161.90395571 1678915.07882923 1885649.89993611 2099565.73185573 2320867.12109217 2549763.92360532 2786471.43537055 3031210.52603696 -9045792.22424413 -96800039.7163578
                               
Option-3                              
Build a high raiser 100000 4500000 22000000 22000000                      
Class A office   1090000 1090000 1090000                      
Class C office   243000 243000 243000                      
Additional Space   132600 270504 413871.12                      
OPEX 8895000 9254358 9439445.16 9628234.0632 9820798.744464 10017214.7193533 10217559.0137403 10421910.1940152 10630348.3978955 10842955.3658534 11059814.4731704 11281010.7626338 11506630.9778865 11736763.5974442 11971498.8693931
Rent from retail         -1163250                    
Depreciation tax saving         192500 192500 192500 192500 192500 192500 192500 192500 192500 192500 192500
Terminal Value                             1115477074.86594
Net Cash outflow 8995000 15219958 33042949.16 33375105.1832 8465048.744464 9824714.71935328 10025059.0137403 10229410.1940152 10437848.3978955 10650455.3658534 10867314.4731704 11088510.7626338 11314130.9778865 11544263.5974442 -1103698075.99655

 

  Year Option 1 PV Option 2   option 3  
1 2000 1468252 1,325,354.48 19458280 $17,564,504.27 8995000 $8,119,562.26
2 2001 1608914.08 1,310,979.04 8121195.2 $6,617,330.82 15219958 $12,401,560.93
3 2002 1755148.5424 1,290,946.44 1238033.744 $910,598.28 33042949.16 $24,303,742.10
4 2003 $3,887,796.02 2,581,245.22 $1,197,828.20 $795,280.49 $33,375,105.18 $22,158,912.25
5 2004 4111953.0525216 2,464,367.04 1384196.0680896 $829,573.47 8465048.744464 $5,073,255.17
6 2005 $4,343,521.25 2,349,798.61 $1,577,161.90 $853,227.75 $9,824,714.72 $5,315,065.74
7 2006 4582707.39490406 2,237,908.21 1776915.07882923 $867,734.40 10025059.0137403 $4,895,612.99
8 2007 4829723.57672304 2,128,991.19 1983649.89993611 $874,413.02 10229410.1940152 $4,509,227.88
9 2008 5084787.32285682 2,023,279.26 2197565.73185573 $874,429.72 10437848.3978955 $4,153,306.89
10 2009 5348121.72940526 1,920,948.71 2418867.12109217 $868,813.37 10650455.3658534 $3,825,451.17
11 2010 5619955.5972865 1,822,127.74 2647763.92360532 $858,470.14 10867314.4731704 $3,523,450.46
12 2011 5900523.57119122 1,726,902.96 2884471.43537055 $844,196.65 11088510.7626338 $3,245,268.29
13 2012 6190066.28181322 1,635,325.21 3129210.52603696 $826,691.77 11314130.9778865 $2,989,028.35
14 2013 6488830.49143162 1,547,414.67 -9045792.22424413 $-2,157,182.50 11544263.5974442 $2,753,001.93
15 2014 6070137.11799534 1,306,682.97 -96800039.7163578 $-20,837,579.28 -1103698075.99655 $-237,586,639.69
      27,672,271.75   10,590,502.38   -130,320,193.28

 

Pv Factor Increment 1to 2 PV Increment 1 to 3 PV
0.90 -17,990,028.00 -16,239,149.80 -7,526,748.00 -6,794,207.78
0.81 -6,512,281.12 -5,306,351.77 -13,611,043.92 -11,090,581.88
0.74 517,114.80 380,348.15 -31,287,800.62 -23,012,795.67
0.66 2,689,967.82 1,785,964.73 -29,487,309.17 -19,577,667.03
0.60 2,727,756.98 1,634,793.57 -4,353,095.69 -2,608,888.13
0.54 2,766,359.35 1,496,570.86 -5,481,193.47 -2,965,267.13
0.49 2,805,792.32 1,370,173.81 -5,442,351.62 -2,657,704.78
0.44 2,846,073.68 1,254,578.17 -5,399,686.62 -2,380,236.69
0.40 2,887,221.59 1,148,849.54 -5,353,061.08 -2,130,027.63
0.36 2,929,254.61 1,052,135.34 -5,302,333.64 -1,904,502.46
0.32 2,972,191.67 963,657.59 -5,247,358.88 -1,701,322.72
0.29 3,016,052.14 882,706.30 -5,187,987.19 -1,518,365.33
0.26 3,060,855.76 808,633.44 -5,124,064.70 -1,353,703.14
0.24 15,534,622.72 3,704,597.17 -5,055,433.11 -1,205,587.26
0.22 102,870,176.83 -5062492.87768419 1,109,768,213.11 -80900857.6274779
    -10,124,985.76   -161,801,715.25

 

WACC  
Beta for REIT 0.8
Debt to equity ratio for Reit 1
Debt of First national 8.1 billion
Equity of First National 18
Debt Equity ratio of FN 0.45
Beta For FN 1.034
Rf 6.50%
Rm 8.40%
Cost of equity 15.185600%
Cost of debt 6.56%
WACC 10.78%

 

Looking for best Finance Assignment Help. Whatsapp us at +16469488918 or chat with our chat representative showing on lower right corner or order from here. You can also take help from our Live Assignment helper for any exam or live assignment related assistance