Need help with BS & Pl statement

[Inactive User]

Hi there,

I am very new to Alteryx and I am trying to prepare BS & Income statement based on two table -

Tb has below cols- 

period Name

ledger code

Ledger name

currency code

company code

period year

account code

account description

start balance

Debit

Credit

end balance

General account has below cols- 

GL code

Description

Account Type

Level type

How to prepare a BS & pl statement out of it.

Please suggest 

[BS_THE_ANALYST]

@Inactive User 

Whilst you're new to Alteryx, lots of the community members will be new to preparing BS & Income statements.

I'd suggest that you upload both of the tables above with some fictitious values. 

It's really unhelpful that you've provided no instructions on how you leverage the two tables to get to the result. As a bare minimum, you should provide instructions on how you'd do it manually. This way the community can translate what you're saying into the tools and provide assistance.

[Inactive User]

Hi @BS_THE_ANALYST

Thanks so much for the response. I have attached the sample file

[Inactive User]

Hi there,

Can anyone help me with this?

[Luke_C]

Hi @Inactive User 

You'll want to make sure that your GL listing is complete, it looks like not every account in your data is on the GL sheet. Then it's really a mapping exercise to map each account to where you'd want to show on your financial statements. Once you have the mapping to the different groupings (assets, liabilities, etc) then it's just a group by tool exercise to summarize up. I'd suggest adding checks like making sure A + L = SE, and so on. 

[apathetichell]

you can start by joining your account code on test to your gl code. you can then summarize based upon account type and level - taking the sum of beginning balance and ending balance. what you do after that is really up to you/your end logic.

ths is for BS - for PL - I don't really see you automating it off of this.

[Inactive User]

Can anyone guide me how to calculate retained earning part?

Apart from Trial Balance and GL table, I have one more table which is journal listing.
I should join trial balance and journal listing then need to join with GL account table to map the account code.
Also I just want to understand what is the need of beginning and ending balance?

Please someone help on this!! I am stuck

[Luke_C · Accepted solution]

Hi @Inactive User 

At it's most basic level, retained earnings is typically calculated as beginning retained earnings + net income - dividends. I'd suggest discussing with an SME at your organization as you are getting more into questions around business requirements versus alteryx questions.