Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Simple Cash Flow Table

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

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

    Opening Balances   
    CurrencyAccount NumberBalanceAmount EUR
    EURACC100000001            500,000.00            500,000.00
    USDACC100000002                7,000.00                6,160.00
    GBPACC100000003         1,400,000.00         1,596,000.00

     

    Expenses & Income    
    Week EndingCategoryCurrencyAmountAmount EUR
    15/03/2020PayrollEUR     20,000.00     20,000.00
    15/03/2020VATEUR       4,000.00       4,000.00
    15/03/2020RevenueEUR   100,000.00   100,000.00
    15/03/2020ExpensesEUR     15,000.00     15,000.00
    15/03/2020PayrollUSD       5,000.00       4,400.00
    15/03/2020VATUSD          400.00          352.00
    15/03/2020RevenueUSD       1,000.00          880.00
    15/03/2020ExpensesUSD       5,000.00       4,400.00
    15/03/2020PayrollGBP     50,000.00     57,000.00
    15/03/2020VATGBP     10,000.00     11,400.00
    15/03/2020RevenueGBP   275,000.00   313,500.00
    15/03/2020ExpensesGBP     35,000.00     39,900.00
    22/03/2020PayrollEUR     60,000.00     60,000.00
    22/03/2020VATEUR     12,000.00     12,000.00
    22/03/2020RevenueEUR   300,000.00   300,000.00
    22/03/2020ExpensesEUR     45,000.00     45,000.00
    22/03/2020PayrollUSD     15,000.00     13,200.00
    22/03/2020VATUSD       1,200.00       1,056.00
    22/03/2020RevenueUSD       3,000.00       2,640.00
    22/03/2020ExpensesUSD     15,000.00     13,200.00
    22/03/2020PayrollGBP   150,000.00   171,000.00
    22/03/2020VATGBP     30,000.00     34,200.00
    22/03/2020RevenueGBP   825,000.00   940,500.00
    22/03/2020ExpensesGBP   105,000.00

       119,700.00

     

     

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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