How to annualize a trial balance & still sum to zero

[shungui]

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!

[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

[RolandSchubert]

The easiest way is to upload the workflow and a file with sample data (Excel). Then we can certainly give you a few hints.

[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"?

[RolandSchubert · Accepted solution]

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

[shungui]

That worked!!! Thank you, Roland!