The goal is to recreate the calculation of the three valuation techniques using Excel.

1. Key in the background financial information on an Excel spreadsheet and format it professionally.

2. Replicate the calculation of technique 1, 2, and 4 in Chen & Kim (2010) on the spreadsheet.

3. Use cell reference for calculation. Don't key in the numbers in the calculation. Reference to the cells that contain the numbers needed for the calculation. When I click on the cell, I should see only equations referring to cells created in step 1 or earlier calculations.

4. Use Excel PV function for the calculation. Your answers will be slightly different from the numbers on the article. That is fine. Your answers are actually more precise. For technique 4, you do not need to show the "Present VAlue of RMB" column in Exhibit 6 since the PV function gives us the DCF directly.

Source: China National Tourism Administration (2006). progress is another reason why we chose Selling expenses: 4 percent of (broker and the Huatian Hotel for our study. legal) sales price Taken from the TEJ financial database, yearly income presented here covers the Exhibit 5 presents the abbreviated finan- period from 1995 to 2005. In this article, cial statement of Huatian Hotel from 1995 we assume the money to purchase this to 2005. The net income before debt ser- hotel will come from a first mortgage, rep- vice is defined as operating income after resenting 75 percent of the purchase price; undistributed and preopening expenses but the balance will be raised from equity part- before interest, tax, and depreciation and ners. In addition, the current costs of vari- amortization. In this case, the net income is ous financing sources are as follows: equivalent to the cash flow before debt service. Mortgage Interest:' 8 percent Valuation in Practice Amortization: 20 years Results of Technique 1 (Band of Mortgage constant: 0.10185 Investment-One Stabilized Year) Loan-to-value ratio: 75 percent This valuation technique, using one sta- Equity Capital bilized year, takes the cost of capital used in Cash-on-cash (equity dividend): 16.35 percent hotel investment and calculates a weighted Equity yield: 20 percent average of these costs based on the percent- age relationship of each capital source to the Other Valuation Rates whole. The weighted average cost of capital Terminal capitalization rate:* 12 percent becomes the rate used to capitalize the sta- Total property yield (discount rate): 14 percent bilized net income into a value estimate.Exhibit 5: The Abbreviated Financial Statement of the Huatian Hotel (in Thousand RMB) Net Income Equity Operating Business before Debt Net Income/ Net Income/ Year Asset Revenue Expense Service Asset (%) Equity (%) 1994 136,127 1995 197,944 116,465 63,969 29,555 34,414 17.39 29.55 1996 481,352 281,948 143,923 62,494 81,429 16.92 28.88 1997 500,280 327,555 143,584 50,044 93,540 18.70 28.56 1998 636,331 435,907 113,204 52,401 60,803 9.56 13.95 1999 627,149 468,362 87,909 47,262 40,647 6.48 8.68 2000 702,992 502,940 110,569 71,661 38,908 5.53 7.74 2001 1,031,942 627,314 131,695 78,568 53,127 5.15 8.47 2002 1,028,477 645,241 189,643 119,038 70,605 6.87 10.94 2003 1,158,529 646,126 231,258 152,917 78,341 6.76 12.12 2004 1,179,382 480,169 266,062 187,782 78,280 6.64 16.30 2005 1,162,612 473,029 260,216 190,724 69,492 5.98 14.69 Average 791,545 455,005 158,367 94,768 63,599 9.63 16.35 Source: Taiwan Economic Journal database.Mortgage (75 percent) x (0.1019) = 0.076387 Year 1 (1995): (34,414) x (0.8950) = 30.801 Equity (25 percent) x (0.1635) = 0.040882 V (in thousands) = RMB 734,865. Overall rate 0.117269 V (in thousands) = 63,599/0.117269 = RMB 542,334. Results of Technique 3 (Hotel Valuation Formula-Ten-Year DCF) Results of Technique 2 (Band of Investment-Three-Year Buildup) This technique uses a ten-year DCF hotel-valuation formula. The total of the When we use the stabilized net income mortgage component and the equity com- of technique 1 to estimate the value of ponent equal the value of the property. The Huatian Hotel, we can easily see that sub- following symbols are used: jectivity is involved when estimating the reliable value due to the limitations of NI = net income available before debt service, the one-year time frame. Valuation tech- V = value, nique 2 uses a longer period of stabilized M = loan-to-value ratio, income in estimating the value of hotel, in F = annual debt service constant, n = number of years in projection, this case, a three-year projection of income. D. = annual equity dividend, In this technique, we take the third year's D, = residual equity value, net income and capitalize it at the capital- b = brokerage and legal cost percentage, ization rate and then discount the future P = fraction of loan paid off in projection period, value to the present value. The net incomes F = annual constant that would be required to of years one and two are also discounted amortize the entire loan with the project to the present value by the same rate. tion period, These present values are added together to R = overall "terminal capitalization" rate produce the estimate of the hotel's value. applied to net income to calculated total property reversion, and Year 3 (1997): (93,540/0.1 173) x (0.8011) = 638,831 Year 2 (1996): (81,429) x (0.8011) = 65,233 = present worth of 1RMB at the equity-yield rate.Results of Technique 4 (Ten-Year DCF with Overall Discount Rate) Due to large cash flow surpluses, the large institutional investors often tend to purchase hotels without any debt. In this case, the debt service should not be deducted from the net income. The total tenth-year net income is calculated by add- ing tenth-year net income plus estimated sales price, which is calculated by the eleventh-year net income divided by termi- nal capitalization rate (12 percent), and subtracting the broker and legal costs. Accordingly, we obtain the value of RMB 471,968,000 (see Exhibit 6) by adopting the discount rate of 14 percent used by Rushmore (1992).Exhibit 6: Technique 4-Ten-Year Discounted Cash Flow (DCF) with Overall Discount Rate (14 Percent) Net income before debt service Present Value of RMB DCF Year (Thousand RMB) 1 at 14 Percent (Thousand RMB) 34,414 0.877193 30,188 81,429 0.769468 62,657 93,540 0.674972 63,137 60,803 0.592080 36,000 40,647 0.519369 21, 111 38,908 0.455587 17,726 53,127 0.399637 21,232 70,605 0.350559 24,751 78,341 0.307508 24,090 634,216 0.269744 171,076 V (thousand RMB) = 471,968 *Sales RMB 69,492,000/0.12 = RMB 579,100,000 Less: Broker and legal 23,164,000 Plus: Tenth-year net income 78.280.000 Total 634,216,000