How to annualize a trial balance & still sum to zero

Hi All,

I'm still fairly new with Alteryx, so I'm wondering if this is possible.

I'm trying to to create a workflow that allows me to annualize my company's world-wide trial balance. The roadblock I'm running into is how to plug the annualized Profit and Loss into the balance sheet and still make the TB sum to zero on a company-by-company basis. I.e., plugging the difference between a trial balance as of 1/31/22 vs a trial balance with an annualized P&L at 12/31/22 and balance sheet balances at 1/31/22.

This file is broken up by three key fields: company code, General Ledger Account #, and Account Balance. I used a formula tool to create a new column for the annualized P&L amounts, but now I'm at a total loss for how I can go about doing this.

Any suggestions or ideas is greatly appreciated. Thank you in advance!

Best Practices

Help

Accepted answers

RolandSchubert

Hi @shungui ,

as I understand your workflow, there is a split between account 306000 and all other accounts. The annualized value for 306000 is calculated for all companies in an additional column (ANNUALIZED GL 306000). I would remove the column VALUE (old, monthly value) and rename the new column using a Select tool:

In the Select tool, you can also remove all other fields created for calculation, the Union tool brings together all accounts, 306000 with the new value.

What do you think?

Best,

Roland

All comments

RolandSchubert

Hi @shungui ,

if I got you right, you want to annualize net income and transfer it to the balance sheet (i.e. add it to the retained earnings in shareholders' equity). It addition, you have to add the same amount to a corresponding item on the assets side (cash or receivables).

Of course, this is still very general. To give some more concrete hints, sample data or a more detailed description would be helpful.

Best,

Roland

shungui

Hi Roland,

Yes! That's exactly what I want to do!

I'm playing around with it now. This is my first post/issue, so I'm not sure how to upload sample data. Do I upload the workflow by itself?

Thanks so much

shungui

This is the workflow I'm working in currently. I'm stuck at how to plug the retained earnings account (GL 306000) with the new "annualized GL 306000." How can I replace the existing balances in 306000 with the new "annualized ones"?

TB Automation Workflow.yxmd

RolandSchubert

Hi @shungui ,

In the Select tool, you can also remove all other fields created for calculation, the Union tool brings together all accounts, 306000 with the new value.

What do you think?

Best,

Roland

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703