Part A

Sensitivity Analysis on DCF APV of HTHT International Expansion HTHT will need funding supports for its international expansion. You have been given a complete DCF APV model that looks at two possible funding options as shown below. Option A Amortized loan for 100% of the purchase price Maturity of 10 years 7% annual interest rate The loan amount will be received on 12/31/2022. The first payment due 1 year after loan 1 amount is received. Option B Interest only loan for 100% of the purchase price The loan amount will be received on 12/31/2022. First loan payment due 12/31/29. However, the loan must be repaid in full by 12/31/2034. Outstanding principal must be repaid at liquidation time. 35% annual rate Corporate tax rate is 35%. HTHT's cost of capital (WACC) is 25%. HTHT's shareholders require a 10% return. Part a.. There is a concern that fluctuations in the financial market may affect cost of equity and cost of funding. In addition, you have given an additional information that HTHT will only accept project with NPV larger than USD$ 50 million. Please build two data tables, one for each financing option above, to investigate how changes in cost of equity and cost of funding will change the accept/reject decision. Please build the data tables on the given separate tab called Sensitivity Analysis. Please comment on the results. Please utilize conditional formatting to support your results. Part b.: HTHT has also asked you to build data validation lists on two independent variables: number of rooms and variable costs (as % of same year revenue). Please build the data validation lists on the relevant independent variables given on the DCF (APV) tab. HTHT expects a minimum of 100 rooms available at any given time. In addition, HTHT expects variable costs to vary from 10% to 25%. Assume that the cost of equity is 10% and that cost of funding is 7% (35%) for Option A (Option B). What is the APV value for Option A with 100 rooms and 25% variable costs? How about for Option B? Given that HTHT will only accept project with NPV larger than USD$ 50 million, will you accept the project under this extreme condition? Please insert your comment on the given separate tab called Sensitivity Analysis. Sensitivity Analysis on DCF APV of HTHT International Expansion HTHT will need funding supports for its international expansion. You have been given a complete DCF APV model that looks at two possible funding options as shown below. Option A Amortized loan for 100% of the purchase price Maturity of 10 years 7% annual interest rate The loan amount will be received on 12/31/2022. The first payment due 1 year after loan 1 amount is received. Option B Interest only loan for 100% of the purchase price The loan amount will be received on 12/31/2022. First loan payment due 12/31/29. However, the loan must be repaid in full by 12/31/2034. Outstanding principal must be repaid at liquidation time. 35% annual rate Corporate tax rate is 35%. HTHT's cost of capital (WACC) is 25%. HTHT's shareholders require a 10% return. Part a.. There is a concern that fluctuations in the financial market may affect cost of equity and cost of funding. In addition, you have given an additional information that HTHT will only accept project with NPV larger than USD$ 50 million. Please build two data tables, one for each financing option above, to investigate how changes in cost of equity and cost of funding will change the accept/reject decision. Please build the data tables on the given separate tab called Sensitivity Analysis. Please comment on the results. Please utilize conditional formatting to support your results. Part b.: HTHT has also asked you to build data validation lists on two independent variables: number of rooms and variable costs (as % of same year revenue). Please build the data validation lists on the relevant independent variables given on the DCF (APV) tab. HTHT expects a minimum of 100 rooms available at any given time. In addition, HTHT expects variable costs to vary from 10% to 25%. Assume that the cost of equity is 10% and that cost of funding is 7% (35%) for Option A (Option B). What is the APV value for Option A with 100 rooms and 25% variable costs? How about for Option B? Given that HTHT will only accept project with NPV larger than USD$ 50 million, will you accept the project under this extreme condition? Please insert your comment on the given separate tab called Sensitivity Analysis