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
Solved! Go to Solution.
@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.
Hi there,
Can anyone help me with this?
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.
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.
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
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.