Create Balance sheet

Question

Hi,

I am trying to create Balance Sheet using  Alteryx but my calculation is not working as they related to each other.

Actually I trying to add the highlighted columns, Opening Balance, Balance available and Closing Balance.

Formula:

Opening Balance= Previous day closing balance, Balance available=SUM(C3,D3) and Closing Balance=K3-SUM(E3:I3)

Any help would be appreciated.

Thanks,

Irfan

AlteryxQuestion.xlsx

Irfan · Accepted Answer

Hey,

I got the solution using the below community link-

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Creating-a-Beginning-and-Ending-Balance-Schedule/m-p/112460#M34618

Thank you.

Thableaus · Accepted Answer

Hi @Irfan

This is a great example to show how Alteryx can fully automate your reports and make your life much easier.

Here is a sample of a workflow that would solve your problem:

- In your Input Tool, be sure to First Import Data on Line 2 - otherwise, this won't work - Alteryx needs to know where to start to find columns.

- I used Select tool to take out the columns of your final result just to be more clear.

- Next step, I sorted Date by Ascending so you can be sure to compare Dates in the right order.

- Data Cleansing Tool converts the Null Values into 0, so they can be used in mathematical operations

- Formula Tool: You calculate first the Balance Available (sum of two columns)

Then you create Closing Balance, which is Balance Available - Expense Columns (be sure to use parenthesis summing all expense columns)

- Multi-Row formula Tool comes in the last step, so you can bring the previous row of Closing Balance to create your Opening Balance column.

- Select Tool at last is used to reorder columns to your expected outcome.

See how easy it is? Alteryx saves time (and lives).

If you have any doubts, let me know, ok?

Cheers,