This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 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.