Feasibility analysis project

Financial Feasibility Study (30 points) Conduct the financial feasibility analysis, one without gap, one with gap. Grading: based on accuracy of the calculation. All calculation should be made using functions in the Excel file. This is critical for the gap analysis. Hard-copied numbers and wrong calculation lose points. House rules: • Please work on the hotel that your group chose at the beginning of the class. Please make sure to check all the numbers on your multi-year pro forma as the numbers will be used for the financial feasibility analysis and the pro forma will be evaluated as well. To check, please see “Pro Forma-How to” file made available in folder “Week 5.” • Although this is an individual project, I encourage you to discuss with your group members and compare the numbers. • However, please make sure that you should be the person who actually type things in the file and complete. Step-by-step Guide: 1. Open the Excel file entitled “Project Worksheet_Your Name.” 2. Save the file and change the file name to incorporate your name. 3. Open the Excel file of the 6-year Pro Forma (Assignment 5), which your group has reconciled. 4. Copy and move the Pro Forma file to the “Project Worksheet” file. 5. Name the Pro Forma sheet “Pro Forma.” 6. Delete the sheet “Space for Pro Forma.” 7. Click the sheet “Financial Feasibility Study.” 8. Put the name and address of the hotel that you chose in rows #2 and #3. 9. Put the number of rooms (row #12) and total cost per room (row #13 – professor will provide). 10. Calculate hotel development costs using 9. 11. Calculate land cost = 5% of the hotel development costs. 12. Calculate total use of funds by adding 10. and 11. 13. Calculate First Mortgage and Equity (rows #6 and #7) using the total use of funds and capital structure percentages (65% and 35%). 14. Calculate Total Source of Funds by adding the two numbers in 13. 15. Put mortgage amount using a function. 16. Calculate annual payment (row #18) using the PMT function. 17. Calculate monthly payment (row #17) by dividing the annual payment by 12. 18. Complete First Mortgage section using the knowledge you earned through Assignment 4. Note that this is annual schedule while Assignment 4 was a monthly schedule. Don’t forget to put actual year of opening and four consecutive years in row 20 and row 29. Assume that the property opens in January. 19. Put Net Operating Income (row #30) based on the Pro Forma. Please note to use the Net Operating Income after reserve. Use the function =’Pro Forma’!CellNumber. 20. Put First Mortgage Annual Payment from row #18. Assume that the property opens in January. 21. Calculate Cash Flow after Debt Service (row #32) using 19. And 20. 22. Put sixth year NOI from the Pro Forma sheet (row #36). Use the NOI after reserve. Use the function =’Pro Forma’!CellNumber 23. Calculate Gross Sales Price (row #38) using information in rows #36 and #37. (Assignment 7) 24. Calculate Net Sales Price (row #40) using information in rows #38 and #39. (Assignment 7) 25. Put remaining first mortgage balance (row #41) from the section of First Mortgage (row #25). 26. Put Return of Equity (row #42 = row #7). 27. Calculate Capital Gain (row #43). (Assignment 7) 28. Calculate Sales Price per Room. (Assignment 7) 29. Complete rows #48 through 51 using the information appearing above. (Assignment 6) 30. Calculate cash outflow and inflow (row #52) using rows #49 through #51. (Assignment 6) 31. Calculate Cash-on-Cash Return. For this divide annual cash outflow and inflows by Equity. All percentages are positive numbers. 32. Calculate 5-year Leveraged IRR using 30. (row #52) and IRR function. 33. Copy the sheet “Financial Feasibility Analysis.” 34. Rename the copied sheet “Gap Analysis.” The sheet “Financial Feasibility with Gap” is provided as it might be of help to explain the gap analysis. Please delete the “Financial Feasibility with Gap” sheet. 35. Find the Total Gap (row #8) that makes 5-year Leveraged IRR 30%. First Mortgage stays at 65%. As the total Gap increases, Equity decreases accordingly. Total Source of Funds stays the same. Sum of First Mortgage, Equity and Total Gap is always 100%. 36. After finishing the work, please submit it to the professor through BlackBoard. Don’t forget to attach the file!