Spreadsheet: 675798d1588498759-need-help-with-index-aggregate-for-repeated-records-cash-flow
Sheet: Cash Flow 2020

Cash Flow Statement
Particulars | 2020-04-01 00:00:00 | 2020-05-01 00:00:00 | 2020-06-01 00:00:00 | 2020-07-01 00:00:00 | 2020-08-01 00:00:00 | 2020-09-01 00:00:00 | 2020-10-01 00:00:00 | 2020-11-01 00:00:00 | 2020-12-01 00:00:00
Cash balance at the beginning of the month | 1256424 |  |  |  |  |  |  |  |  |  |  |  | 2020-11-01 00:00:00
Cash Inflows |  |  |  |  |  |  |  |  |  |  |  |  | Amount
Hotel cash flow net | 2785485.592816664 | -569015.7198453108 | 1496054.17638075 | -551223.775529 | 134844.2647790881 | 270436.7670554714 | 2880095.430919144 | 3626354.739093143 | 5659120.107272914 |  |  |  |  |  | Top | 5
Less: Provision for low occupancy and ADR | -500000 | -500000 | -500000 | -500000 | -500000 | -500000 | -500000 | -500000 | -500000 |  |  |  |  |  | [NAME]
Airport Road Tower Rental Income | 0 | 1151745.5 | 2368509 | 890481 | 249762.5 | 249275 | 370006 | 258187.5 | 363050 |  |  |  |  |  | Count
Al Jewn Tower Rental Income | 0 | 272462.5 | 0 | 8075000 | 276112.5 | 0 | 0 | 252462.5 | 23650
M Villa Rental Income
Hotel Units Income | 0 | 60000 |  |  | 30000 |  |  | 30000 |  |  |  | TOTAL
TSPM - Rental income | 0 |  | 170548.5 |  | 170548.5 |  |  | 170548.5
Dividend - CSJ |  |  |  |  |  |  |  |  | 1000000
Dividends - TRR |  | 1245731 | 0 |  |  |  |  |  | 0
Reimbursements | 0 | 28240 |  | 60000 |  |  |  | 145000 | 0
Sale of shares | 0 | 7400000 |  |  |  |  |  |  |  |  |  | [NAME] in M8
Loan repayment by related entities |  |  |  |  | 1200000 |  |  |  | 1900000 |  |  | =IF(L8="","",INDEX($A$24:$A$66,AGGREGATE(15,6,(ROW($B$24:$J$66)-ROW($B$24)+1)/($B$24:$J$66=N8),COUNTIF($N$8:N8,N8))))
Loan repayment by associates |  |  | 888889 | 888889 | 888889 | 888889 | 888889 | 888889 | 888888 |  |  | [NAME] in M9
Total Cash Inflows |  |  |  |  |  |  |  |  |  |  |  | =IF(L6="","",INDEX($A$21:$A$63,AGGREGATE(15,6,(ROW($B$21:$J$63)-ROW($B$21)+1)/($B$21:$J$63=N6),COUNTIF($N$5:N6,N6))))
Particulars | 2020-04-01 00:00:00 | 2020-05-01 00:00:00 | 2020-06-01 00:00:00 | 2020-07-01 00:00:00 | 2020-08-01 00:00:00 | 2020-09-01 00:00:00 | 2020-10-01 00:00:00 | 2020-11-01 00:00:00 | 2020-12-01 00:00:00
Cash Outflows
Loan Repayments & Restructuring Costs
Bank Loan - Dalma Tower (25th of each Mar, June ) | 0 |  | 6189000 |  |  | 0 |  |  | 0
Bank Loan - Dalma Interest (25th of each Mar, June ) |  | 89100 | 36500 |  |  | 0 |  |  | 0
Bank Loan -  130,000,000 (30th of each Mar, June, Sep, Dec ) | 0 |  | 6500000 |  |  | 3250000 |  |  | 3250000
Bank Loan -  130,000,000 - Interest (30th of each Mar, June, Sep, Dec ) |  |  | 1256529.66 |  |  | 1225886.93 |  |  | 1181436.48
Group restructure costs - Transfer fees |  | 1169343.75
Group restructure costs - Mortgage Costs |  | 2505000
 |  |  |  |  |  |  |  |  |  |  |  | The results in row M8 and M9 are appearing as same, since there are two values of 1,200,000 in the month of November
Sharholders Withdrawals/Expenses |  |  |  |  |  |  |  |  |  |  |  | I want the correct 2nd result from the column A & Row 34, which should be "ADC Cash Requirement" for the month of November
Shareholders Fixed monthly Expenses | 0 | 145950 | 145950 | 145950 | 145950 | 145950 | 145950 | 145950 | 145950 |  |  | Please help
Shareholder Withdrawals | 0 | 1200000 | 1200000 | 1200000 | 1200000 | 1200000 | 1200000 | 1200000 | 1200000
ADC Cash requirement | 0 | 1135000 | 10000000 | 1100000 | 0 | 1205000 | 1600000 | 1200000 | 0
Petty Cash requirement
Building Construction | 0 | 185000 | 185000 | 185000 | 185000 | 185000 | 185000 | 185000 | 185000
Staff Expenses for Shareholder | 0 | 278478 | 294217 | 259000 | 259000 | 259000 | 259000 | 259000 | 259000
Company Expenses
Staff Expenses | 0 | 366055 | 527525 | 507525 | 436615 | 366055 | 739936 | 948466 | 486055
Audit fees: All Companies | 0 | 158250 | 0 | 158250 | 0 | 0 | 0 | 0 | 0
Valuation fees all properties |  |  |  |  |  |  |  |  | 0
Trade License Fees: All Companies | 0 | 86095 | 0 | 0 | 13500 | 15000 | 20700 | 10672 | 10000
General Expenses | 0 | 51899.66 | 51899.66 | 163899.66 | 51899.66 | 54899.66 | 301899.66 | 51899.66 | 51899.66
Community Fee |  | 80000
Legal Expenses | 0 | 768104 | 70283 | 70283 | 70283 | 107783 | 70283 | 70283 | 70287
Airport Road Tower Expenses | 0 | 227496.25 | 100800 | 55550 | 50000 | 84800 | 100050 | 50000 | 80800
AJ Tower Expenses | 0 | 665505.375 | 450175 | 207675 | 475175 | 153175 | 542675 | 125175 | 95175
Villa Expenses | 0 | 10793.875 | 16250 | 1000 | 1000 | 16250 | 1000 | 1000 | 16250
Hotel Rooms Service Charges | 0 | 17500 |  | 8750 |  |  | 8750
Land Rent: Elite name |  |  |  | 185000 |  | .
Design Fees - Hotels | 0 | 93750 | 93750 | 93750
Related /Associate Companies Cash Requirements
Settlement - ORE | 0 |  | 0 | 0 |  | 0 |  | 0 | 0
ORE Refunds | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
TSH Requirement
FF&E
CAPEX and Special Projects
Apartment Furniture Change | 0 | 0 | 0 | 0 | 0 | 0 | 0
Insurance |  |  |  |  |  | 1050000
Community Fees |  |  | 0 | 0 | 583020 |  | 120000 | 0
Total Cash Outflows
Cash Surplus / (Shortage) for the month
Cash Balance at the end of the month
 | 2020-04-01 00:00:00 | 2020-05-01 00:00:00 | 2020-06-01 00:00:00 | 2020-07-01 00:00:00 | 2020-08-01 00:00:00 | 2020-09-01 00:00:00 | 2020-10-01 00:00:00 | 2020-11-01 00:00:00 | 2020-12-01 00:00:00