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 CAPEX 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: No.of No. Occup Rate Full-time Emplo monthly Salary yees of ancy bed/ Cost of Sale/Variable Beds Rate Day Human Resources Revenue:Room Cost Food 1. CEO T 4,000.00 Project 1 Beverages 2.Manager 2,500.00 Villa 4.50% 200 Food & Bev. Exp 3. Bar Manager 2 1,000.00 Executive Master Suite 10 50% 180 Other Kitchen Purch. 4. Accountant 1 2,000.00 Standard Master Suite 18 50% 180 Sales & Marketing 4. Storekeeper/Cooks 2 600.00 Standard Room 8 60% 180 Marketing Rate Per Unit 55% of bar and restaurant income 25% of food expenditure 7% of food expenditure 700 Per month 2500 per month 5. Drivers 2 500.00 Project 2 Sales Commission 0.005 of Room income 6. Ber Attendant 4 500.00 Ville 2 80% 200 General Admin Cost 7. Watchman Day 2 400.00 Executive Master Suite 3 70% 200 Utilities/Bills 6,000 per month 8. Watchman Nite 2 450.00 Standard Master Suite 6.55% 180 Insurance 10,000 per year 9. Cleaner ] 400.00 Executive Suite 5 70% 180 Repairs & Maint. 2,000 per month Total 20 Standard Room 2 60% 180 Fuel 3,200 per month Other Incomes Travel and Transport 1,000 per month Loan Details Proceeds from Loans Existing loans Interest Principal Repayment mothly 1,100,000.00 Other Income 2,673,963.00 62,893.00 Capex Per Unit Bar & Restaurant 1,300 Daily 8,000 MNy Comm. & Internet 1,500 per month Printing & Stationery Staff Training First Aid 800 per month 5,000 per quarter 1,500 per month Interest payment monthly 36,467.00 Yr of investment Jun/1st Y 916,000 Donation 800 per month 184,000 Professional Fees 10,000 per year 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-3, iv. Return on Investments (ROI) V. Internal Rate of Returns (IRR) vi. Pay Back Period Assuming WACC of 30%. 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 CAPEX 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: No.of Emplo No. Occup Rate/ Full-time monthly Salary yees of ancy bed/ Beds Rate Day Rate Per Unit Human Resources Revenue:Room Cost of Sale/Variable Cost Food 1. CEO 4,000.00 Project 1 Beverages 2. Manager 1 2,500.00 Villa 4 50% 200 Food & Bev. Exp 3. Bar Manager 1,000.00 Executive Master Suite 10 50% 180 Other Kitchen Purch. 4. Accountant 1 2,000.00 Standard Master Suite 18 50% 180 Sales & Marketing 4. Storekeeper/Cooks 2 600.00 Standard Room 8 60% 180 Marketing 55% of bar and restaurant income 25% of food expenditure 7% of food expenditure 700 Per month 2500 per month 5. Drivers 2 500.00 Project 2 Sales Commission 0.005 of Room income 6. Bar Attendant 4 500.00 Villa 2.80% 200 General Admin Cost 7. Watchman Day 2 400.00 Executive Master Suite 3 70% 200 Utilities/Bills 6,000 per month 8. Watchman Nite 2 450.00 Standard Master Suite 6 55% 180 Insurance 10,000 per year 9. Cleaner 3 400.00 Executive Suite 5 70% 180 Repairs & Maint. 2,000 per month Total 20 Standard Room 2 60% 180 Fuel 3,200 per month Other Incomes Travel and Transport 1,000 per month Loan Details Proceeds from Loans Per Unit Bar & Restaurant 1,300 Daily Comm. & Internet 1,500 per month Existing loans Interest Principal Repayment mothly 1,100,000.00 Other Income 2,673,963.00 62,893.00 Capex 8,000 Mthly Printing & Stationery 800 per month Staff Training First Aid 5,000 per quarter 1,500 per month Interest payment monthly 36,467.00 Yr of investment Jun/1st Yr 916,000 Donation 800 per month 184,000 Professional Fees 10,000 Per year 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-3, iv. Return on Investments (ROI) V. Internal Rate of Returns (IRR) vi. Pay Back Period Assuming WACC of 30%.