Financial Modelling: Case Study Twelve Stones Properties Limited is a social enterprise private Company incorporated to re- configure the dynamics between the private sector, development sector and rural communities through innovation of real estate activities. In order to realize its vision, the Company intends to construct two residential 60-bedroom hotel apartments in 2 communities of Ghana. The company therefore seeks to raise GHs1,100,000 comprising CAPE Financing and Working Capital financing to fully complete and launch its hotel business in Ghana. After technical and financial due diligence, the following revenue items have been extracted: Human Resources no. of employees full-time monthly salary 1.CEO 1 4,000.00 2.Manager 1 2,500.00 3. Bar Manager 2 1,000.00 4. Accountant 1 2,000.00 5. Storekeeper/Cooks 2 600.00 6. Drivers 2 500.00 7. Bar Attendant 4 500.00 8. Watchman Day 2 400.00 9. Watchman Night 2 450.00 10. Cleaner 3 400.00 TOTAL 30 Loan Details Proceeds from Loans Per Unit 1,100,000.00 Existing loans + Interest 2,673,963.00 Principal Repayment monthly Interest Payment monthly 62,893.00 36,467.00 PAGE 1 Revenue: Room No. of Beds Project 1 Villa 4 50% Occupancy Rate Rate/bed/day 200 Executive Master 10 50% 180 Suite Standard Master 18 50% 180 Suite Standard Room 8 60% 180 Project 2 Villa 2 80% 200 Executive Master 3 70% 200 Suite Standard Master 3 55% 180 Suite Executive Suite 5 70% 180 Standard Room 2 60% 180 Other Incomes Bar & Restaurant = 1,300 Daily Other Income 8,000 Monthly Capex Year of Investment = 916,000 Jun 1st Year 184,000 Cost of Sale/ Variable Cost Food Beverages Food & Beverages Cost of Sale/ Variable Cost Other Kitchen Purchase Sales & Marketing Marketing Sales Commission General Admin Cost Utilities/Bills Insurance Rate Per Unit 55% of bar and restaurant income 25% of food expenditure 7% of food expenditure Rate Per Unit 700 per month 2500 per month 0.005 of room income 6,000 per month 10,000 per year Repairs & Maintenance 2,000 per month Fuel 3,200 per month Travel and Transport 1,000 per month 1,500 per month Comm and Internet Printing & Stationery Staff Training First Aid Donation Professional fees 800 per month 5,000 per month 1,500 per month 800 per month 10,000 per year PAGE 2 calculate the following: i. Profit from operations for year 1 to 3. ii. Total Cash Flows from year 1 to 3. iii. Discounted Cash Flows and the NPV for year 1 to 3. iv. Return on Investments (ROI) v. Internal Rate of Returns (IRR) vi. Pay Back Period. Assuming WACC of 30% PAGE 3