Simple Cash Flow Table

[Anonymous]

Hi all,

Has anyone any ideas how I could possibly take the below data and present it as a cashflow table with opening and closing balances?

See data below:

My desired outcome is represented in the green table on the right-hand side.

Any help would be much appreciated!

Cheers,

Aaron

[Anonymous]

Data below in table format for anyone who wishes to play about with it!

Opening Balances
Currency	Account Number	Balance	Amount EUR
EUR	ACC100000001	500,000.00	500,000.00
USD	ACC100000002	7,000.00	6,160.00
GBP	ACC100000003	1,400,000.00	1,596,000.00

Expenses & Income
Week Ending	Category	Currency	Amount	Amount EUR
15/03/2020	Payroll	EUR	20,000.00	20,000.00
15/03/2020	VAT	EUR	4,000.00	4,000.00
15/03/2020	Revenue	EUR	100,000.00	100,000.00
15/03/2020	Expenses	EUR	15,000.00	15,000.00
15/03/2020	Payroll	USD	5,000.00	4,400.00
15/03/2020	VAT	USD	400.00	352.00
15/03/2020	Revenue	USD	1,000.00	880.00
15/03/2020	Expenses	USD	5,000.00	4,400.00
15/03/2020	Payroll	GBP	50,000.00	57,000.00
15/03/2020	VAT	GBP	10,000.00	11,400.00
15/03/2020	Revenue	GBP	275,000.00	313,500.00
15/03/2020	Expenses	GBP	35,000.00	39,900.00
22/03/2020	Payroll	EUR	60,000.00	60,000.00
22/03/2020	VAT	EUR	12,000.00	12,000.00
22/03/2020	Revenue	EUR	300,000.00	300,000.00
22/03/2020	Expenses	EUR	45,000.00	45,000.00
22/03/2020	Payroll	USD	15,000.00	13,200.00
22/03/2020	VAT	USD	1,200.00	1,056.00
22/03/2020	Revenue	USD	3,000.00	2,640.00
22/03/2020	Expenses	USD	15,000.00	13,200.00
22/03/2020	Payroll	GBP	150,000.00	171,000.00
22/03/2020	VAT	GBP	30,000.00	34,200.00
22/03/2020	Revenue	GBP	825,000.00	940,500.00
22/03/2020	Expenses	GBP	105,000.00	119,700.00

[v-deddai1-msft]

Hi aaron92, 

I didn't find a visualization that can meet your requirements, and based on the information you gave us, we can't get the calculation logic of each column in the result table you shown.

Can you describe the calculation logic of opening balance and closing balance more clearly? Whether each category in the result table is the sum of amout calculated according to the Week Ending?

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft 
Sorry for the delay getting back to you!

There is no calculation logic for the opening balances. They would be updated by the user on a weekly basis and remain static.

That said, you're correct in saying that each category in the result table is the sum of the values for that week ending date.

Ideally I would like to be able to take that opening balance, then substract the category totals for each week. After this is completed for the first week, it would create the "closing balance". This should then be used as the "opening balance" for the following week.

Hopefully this makes more sense!

Let me know if you have any other questions.

Thanks,
Aaron