A private investor wants to perform major expansion on a private dormitory in which the information for existing and expansion case provided below. Your task is to analyze the profitability of possible expansion for this case based on the Net present value and discounted payback period and writing a brief report to compare if aforementioned expansion generates enough profit in compare to the existing case. The original dormitory is established in 2010 included a gym, indoor swimming pool and 65 rooms. Evaluation of assets and liabilities of this dormitory in Dec 2019 shows the existing dormitory building was valued at $250,000 (including the gym and swimming pool). The land on which the dormitory stands in the same year worth $1,000,000 and it is estimated that the price of land will not change for next 10 years. It should be mention that the initial cost of investment for entire project is comprised of both aforementioned investment (building, equipment and the land). The investors had taken a long term loan of $600,000 for the purchase of land and construction of the existing dormitory back in 2010. An amount of $240,000 is still outstanding from the loan and should be repaid according to equal installment of loan principle in 4 years (every year 60,000) starting year 2020 and 8% fixed interest rate on loan balance of each year. This repayment will be made irrespective of whether the dormitory was kept and operated as such, refurbished or sold. It is estimated that if this facility is continued in operation until 2025 (duration of financial assessment). the dormitory building and other fixed assets (excluding land) could then be sold for $150,000. Information plan 1 demonstrated the required data if the investor does not implement any expansion and the dormitory performs as it does now from year 2020 to year 2025. Information plan 1 (2020-2025) Average monthly Tariff: $900 for each room Average Occupancy for all the years: 85% All the values are S Thousands (unless otherwise indicated) Co 20 Year Gym membership sale Swimming pool membership sale Manager cost Administrative cost Maintenance and cleaning cost Utility bills Advertising Miscellaneous cost 2020 2021 2022 2023 2024 2025 120 123 126 129 132 (last two digit of your student number) *1000 with annual increasing 10% from year 2020-24 22 28 30 35 13 15 17 19 21 80 82 82 84 85 45 48 51 54 54 55 56 57 58 6 6 6 8 8 57 1 CT.pdf Investment Plan for Expansion investment plan for expansion included two parts as of refurbishment of existing building and constructing new building beside the existing one in which both the plans are implemented in year 2020. The cost of additional land for new building construction expected to be around 500,000$. Another 300,000$ will be cost of constructing a new building with around 50,000 $ of equipment for constructing 50 more rooms. Meanwhile to renovate the existing building expenses are evaluated around 45,000 $. It is estimated that at the end of 2025 entire building and equipment (existing and new construction) will have a value of 500,000$. It is notable that the construction period for new building and renovation period for existing building will be 1 year and within this period the existing building remains operational (it means all the cost and revenues for the base year will remain the same as existing case and it is starting to change once construction and renovation are completely done). Another considerable point here is that the total value of the land for existing and additional will not change for next 10 years Information plan 2 represented the required data in case of major expansion Information plan 2 for the Expanded Dormitory Average monthly tariff $950 for every two person for each room All the values are in S Thousands (unless otherwise indicated) year average occupancy gym membership sale swimming pool membership sale 2020 2021 2022 2023 2024 2025 92% 95% 95% 95% 246 252 258 264 (last two digit of your student number)"1000 and annual increasing 15% from year 2021- 24 44 56 60 70 30 34 38 42 123 123 126 128 96 102 108 114 manager cost administrative cost maintenance and cleaning cost utility bills Activate W Go to PC Setti year average occupancy gym membership sale swimming pool membership sale year 2021- manager cost administrative cost maintenance and cleaning cost utility bills advertising miscellaneous cost 2020 2021 2022 2023 2024 2025 92% 95% 95% 95% 246 252 258 264 (last two digit of your student number)*1000 and annual increasing 15% from 24 44 56 60 70 30 34 38 42 123 123 126 128 96 102 108 114 55 56 58 12 12 16 16 57 Hint 1: Number of month for calculation of annual dormitory occupancy revenue should be considered 10. Hint 2: The value of the land and building and equipment in Dec 2019 should be considered as initial cost for both the cases. Hint 3: all the information are based on the end of year cash flow. Assignment: Using all aforementioned data in the case: a. Prepare the Cash Flow for the existing hotel for the period 2020-2025 and calculate total present worth and discounted payback period based on 10% discount rate of the project in Excel spreadsheet. Prepare the Cash Flow for the expanded hotel as proposed for the period 2020-2025 and calculate total present worth and discounted payback period based on 10% discount rate in Excel spread sheet. b. c. compare both the alternative based on their total present worth and discounted payback period and prepare a report to suggest best alternative. A private investor wants to perform major expansion on a private dormitory in which the information for existing and expansion case provided below. Your task is to analyze the profitability of possible expansion for this case based on the Net present value and discounted payback period and writing a brief report to compare if aforementioned expansion generates enough profit in compare to the existing case. The original dormitory is established in 2010 included a gym, indoor swimming pool and 65 rooms. Evaluation of assets and liabilities of this dormitory in Dec 2019 shows the existing dormitory building was valued at $250,000 (including the gym and swimming pool). The land on which the dormitory stands in the same year worth $1,000,000 and it is estimated that the price of land will not change for next 10 years. It should be mention that the initial cost of investment for entire project is comprised of both aforementioned investment (building, equipment and the land). The investors had taken a long term loan of $600,000 for the purchase of land and construction of the existing dormitory back in 2010. An amount of $240,000 is still outstanding from the loan and should be repaid according to equal installment of loan principle in 4 years (every year 60,000) starting year 2020 and 8% fixed interest rate on loan balance of each year. This repayment will be made irrespective of whether the dormitory was kept and operated as such, refurbished or sold. It is estimated that if this facility is continued in operation until 2025 (duration of financial assessment). the dormitory building and other fixed assets (excluding land) could then be sold for $150,000. Information plan 1 demonstrated the required data if the investor does not implement any expansion and the dormitory performs as it does now from year 2020 to year 2025. Information plan 1 (2020-2025) Average monthly Tariff: $900 for each room Average Occupancy for all the years: 85% All the values are S Thousands (unless otherwise indicated) Co 20 Year Gym membership sale Swimming pool membership sale Manager cost Administrative cost Maintenance and cleaning cost Utility bills Advertising Miscellaneous cost 2020 2021 2022 2023 2024 2025 120 123 126 129 132 (last two digit of your student number) *1000 with annual increasing 10% from year 2020-24 22 28 30 35 13 15 17 19 21 80 82 82 84 85 45 48 51 54 54 55 56 57 58 6 6 6 8 8 57 1 CT.pdf Investment Plan for Expansion investment plan for expansion included two parts as of refurbishment of existing building and constructing new building beside the existing one in which both the plans are implemented in year 2020. The cost of additional land for new building construction expected to be around 500,000$. Another 300,000$ will be cost of constructing a new building with around 50,000 $ of equipment for constructing 50 more rooms. Meanwhile to renovate the existing building expenses are evaluated around 45,000 $. It is estimated that at the end of 2025 entire building and equipment (existing and new construction) will have a value of 500,000$. It is notable that the construction period for new building and renovation period for existing building will be 1 year and within this period the existing building remains operational (it means all the cost and revenues for the base year will remain the same as existing case and it is starting to change once construction and renovation are completely done). Another considerable point here is that the total value of the land for existing and additional will not change for next 10 years Information plan 2 represented the required data in case of major expansion Information plan 2 for the Expanded Dormitory Average monthly tariff $950 for every two person for each room All the values are in S Thousands (unless otherwise indicated) year average occupancy gym membership sale swimming pool membership sale 2020 2021 2022 2023 2024 2025 92% 95% 95% 95% 246 252 258 264 (last two digit of your student number)"1000 and annual increasing 15% from year 2021- 24 44 56 60 70 30 34 38 42 123 123 126 128 96 102 108 114 manager cost administrative cost maintenance and cleaning cost utility bills Activate W Go to PC Setti year average occupancy gym membership sale swimming pool membership sale year 2021- manager cost administrative cost maintenance and cleaning cost utility bills advertising miscellaneous cost 2020 2021 2022 2023 2024 2025 92% 95% 95% 95% 246 252 258 264 (last two digit of your student number)*1000 and annual increasing 15% from 24 44 56 60 70 30 34 38 42 123 123 126 128 96 102 108 114 55 56 58 12 12 16 16 57 Hint 1: Number of month for calculation of annual dormitory occupancy revenue should be considered 10. Hint 2: The value of the land and building and equipment in Dec 2019 should be considered as initial cost for both the cases. Hint 3: all the information are based on the end of year cash flow. Assignment: Using all aforementioned data in the case: a. Prepare the Cash Flow for the existing hotel for the period 2020-2025 and calculate total present worth and discounted payback period based on 10% discount rate of the project in Excel spreadsheet. Prepare the Cash Flow for the expanded hotel as proposed for the period 2020-2025 and calculate total present worth and discounted payback period based on 10% discount rate in Excel spread sheet. b. c. compare both the alternative based on their total present worth and discounted payback period and prepare a report to suggest best alternative