Calculate YTD (Year to Date) value from monthly transaction amounts

Hello,

Please help in solving the Year to Date (YTD) amounts. I checked the available solutions but either their time grain is different (day level) or they are for single year, leading to simpler solution.

For finance department, they need to calculate the YTD amounts for multi year financial line items. The problem is described as below:

The data file ‘Line-aggregation-calculation.xlsx’ is attached.

  • There are two entities , E1 and E2

  • Each entity has 10 line items.

  • The financial year has 12 months, starting from 1 to 12

  • For each entity, for year 2022, month 12, the year-to-date value is provided in `input-data` tab of data file.

  • For each entity, for year 2023, 2024 & 2025, monthly transaction amount is provided in `input-data` tab of data file. It is not necessary that each line item would have monthly transaction hence in the input data, for many line items, if transactions are not there, the line for the month would be missing.

Goal:

  • For each entity, line item, year, month, calculate the year to date amount. The formula is as below:

OPBAL (year opening balance) = previous year, last month (month 12)'s year-to-date amount

year-to-date amount = OPBAL + sum of monthly transaction amount from first month 1 to current month

In the data file, year-to-date amount is computed in 

output-data

tab for comparison. Also, 

calculations

tab contains the illustration of 

year-to-date

calculation formula.

  • Prepare a report of `monthly` and `year-to-date` amounts.

In the data file, 

output-report

is manually computed for reference. The orange shaded part of the report is generated using Year to date amounts.

Line-aggregation-calculation.xlsx

Thanks for the help.