Balance sheet using matrix

Hi atifkazmi1980 ,

I am assuming that your organization does not use an ERP system that automatically generates the balance sheet from recorded transactions. Typically, ERP accumulate balances for GL accounts mapped to the balance sheet, while for GL accounts mapped to the profit and loss (P&L) statement, they only accumulate balances for the current year. At the end of the year, the net profit or loss is transferred to the retained earnings (unappropriated profit) line which is part of BS accumulated balances.

To replicate this logic, you can use a Calendar table, a Transactions table containing journal entries (where debits and credits sum to zero), and an additional GL_Mapping table. The GL_Mapping table should classify each GL account into either "BS" (Balance Sheet) or "PL" (Profit and Loss). Relationships should be created as follows:

1. Calendar[Date] to Transactions[Date].
2. GL_Mapping[GL_Account] to Transactions[GL_Account].

We calculate the balances as follows:

1. Balance Sheet (BS) Accounts: A cumulative sum of transactions for accounts classified as "BS."
2. Profit and Loss (PL) Accounts: A year-to-date (YTD) sum for accounts classified as "PL."
3. Retained Earnings: A cumulative sum of prior years’ "PL" transactions, explicitly added to the retained earnings GL account in the balance sheet.

1. Cumulative Sum for Balance Sheet Accounts

BS_Cumulative = 
CALCULATE(
    SUM(Transactions[Amount]),
    FILTER(
        ALL(Calendar),
        Calendar[Date] <= MAX(Calendar[Date])
    ),
    GL_Mapping[GL_Type] = "BS"
)

2. Year-to-Date Sum for Profit and Loss Accounts

PL_YTD = 
CALCULATE(
    SUM(Transactions[Amount]),
    DATESYTD(Calendar[Date]),
    GL_Mapping[GL_Type] = "PL"
)

3. Retained Earnings Filtered by the GL Account

Retained_Earnings = 
CALCULATE(
    SUM(Transactions[Amount]),
    FILTER(
        Transactions,
        GL_Mapping[GL_Type] = "PL" &&
        YEAR(Transactions[Date]) < YEAR(MAX(Calendar[Date]))
    ),
    GL_Mapping[GL_Account] = "Retained Earnings"
)

Explanation of the Logic:

1. Balance Sheet: The BS_Cumulative formula calculates the running total of all "BS" transactions up to the current date.
2. Profit and Loss: The PL_YTD formula sums transactions for "PL" accounts year-to-date. This resets at the start of each fiscal year.
3. Retained Earnings: The Retained_Earnings formula:
• Filters transactions for accounts classified as "PL."
• Includes only prior years’ data (YEAR(Transactions[Date]) < YEAR(MAX(Calendar[Date]))).
• Explicitly adds this value to the "Retained Earnings" GL account (GL_Mapping[GL_Account] = "Retained Earnings").

By explicitly filtering for the "Retained Earnings" GL account in the retained earnings calculation, we ensure the correct accumulation of net profit or loss into the designated retained earnings line. This approach is scalable and flexible, as changes in GL classifications can be managed directly in the GL_Mapping table.

Best regards,