cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## Conditional Formula involving looping

Highlighted
Atom

Hello Alteryx community,

I have a requirement in which I want to perform some computations based on certain conditions.

Please see the attached spreadsheet for data. I need to calculate the value for the column 'Cost Running Total'

As per the business Rules, the value for is calculated based on following :-

1) Please note that 'Buy' and 'Carry Forward' are regarded as 'Purchase' transactions. 'Ignore' does not have any effect on the Cost

2) For BUY or CARRY FORWARD transactions - the value from the 'Purchase Cost' column is cumulatively added to the 'Cost Running Total' from the previous row to form 'Cost Running Total'

3) For SELL transaction, the formula for 'Cost Running Total' is
"Total Running Cost (as of previous row) - ((Total Running Cost as of Purchase / Total Running Qty as of Purchase) x Qty Sold)"

For eg.

- For the Sell transaction on row 9, the regarded Purchase is the Carry Forward on row 1 since transactions are handled in FIFO order and the 'Qty Sold' in Sold transaction is less than 'Qty Purchased' in Carry Forward transaction.

the formula for calculating 'Cost Running Total' would be 15500 - ((2500/50) * 20)

So, after Selling 20 units from the 50 units purchased in Carry Forward form the 1st row, the units remaining in the 1st purchase are 30.

- For the Sell transaction on row 10, the regarded Purchase is the Carry Forward on row 1 since the 'Qty Sold' in Sold transaction(30) is less than or equal to Quantity Remaining in the Carry Forward transaction(30)

the formula would be 14500 - ((2500/50) * 30)

So, after Selling 30 units from the remaining 30 units purchased in Carry Forward form the 1st row, the units remaining in the 1st purchase are now 0.

- For the Sell transaction on row 12, the Purchase on row 1 i.e Carry Forward on row 1 cannot be used since there are no more units remaining in that purchase transaction. So, we now need to consider the next Buy transaction on row 3 after Carry Forward. Since the 'Qty Sold' in Sold transaction at row 12 (10) is less than or equal to Quantity Purchased in the Buy transaction at row 3 (10), we can regard this Buy as purchase transaction for this sell.

the formula would be 15000 - ((3500/60) * 10)

So, after Selling 10 units from the remaining 10 units purchased in Buy form the 3rd row, the units remaining in this purchase are now 0. So, we need to consider next Buy transaction as purchase for the subsequent Sell transaction.

4) IGNORE has no effect on the cost as mentioned above.

Alteryx

Have you attempted to build something out in Alteryx for this already? It will enable you further if you give it a go and post what you do so far.

Asteroid

I think you want to show FIFO method of accounting.

You did some mistake in example: cell I8 and I9 - use cost of first goods bought.

For cell I12 you need to use not total cost from row 2, but marginal.

***

So I suggest to create somehow table accounting all income (with marginal cost) and all outcomes.

You may solve this:

* with macro - good way, hovewer you need extra skills to write macros in Alteryx

* by formulas. Here is good Excel realization on FIFO: https://pakaccountants.com/fifo-costing-inventory-excel-data-tables/

And you could bring this logic to Alteryx.

Labels