Help with Excel functions

[Karanbir Soin]

Hi ,
In the file attached which I am trying to implement Cost of shares sold based on FIFO.
Formula in M6 is displaying the correct value.
But formula in M7 and M8 are displaying the same value as in M6.
If we select M7 and then click Fx in formula bar the result displayed is correct but it shows as shown in cell M6.
Similar results in N7 and N8.
Help will be appreciated.

Best Regards

 

[Peter Bartholomew]

Hi Karanbir

You have two options to correct your formula. The formula shows as a single-cell array formula copied down. The array formula produces the list of valid row numbers correctly. Then when it comes to search for each occurrence, as an array formula, it will look for the first instance of the Num parameter wherever the formula is written. Hence you see {3;3;3}, corresponding to k=1 as the second parameter of SMALL.

Your options are: 1) to modify your formula to
=SMALL(
IF((Share_Id=Share_Key)*(Shares_sold>0)*(Date>=From_Date), Num)
,k)
where k is refers to a RELATIVE single cell range starting with $B6, the first element of 'Num', or

2) To leave the formula as it is but enter it as a multi-cell array formula in which case the second term of the array shown in cell Q7 will pick up its value of Num from the corresponding cell B7 as you require.

 

[Peter Bartholomew]

There is a third option. If you define a named formula 'matches' to return the list or values for Num that meet the criteria [let it refer to]
= IF((Share_Id=Share_Key)*(Shares_sold>0)*(Date>=From_Date),Num)
then the remaining worksheet formula
=SMALL(matches, Num)
does NOT require CSE array entry.

That means that the parameter Num is allowed to use implicit intersection to pick a single value from the Num range. This is probably your best solution as it removes the risk of forgetting CSE.

With a bit of luck all this will be much simpler with dynamic arrays, provided you use Office 365.

 

[Nebu]

Hi:

Do this Non-Array formula work for you?

=SMALL(IF(MMULT((Share_Id=Share_Key)*(Shares_sold>0)*(Date>=From_Date),1),Num),ROW(A1))

Thanks

 

[Karanbir Soin]

Thanks to Peter and Nebu to have taken time out to help me.
I have implemented peters option 1.
With this the FIFO appears to be working.
Value of Cost of shares sold in the outward table needs to be done via what if analysis>Data Table method.
I am selecting range P6:Q49 and in what if analysis data table I select the input row cell as $P$6.
What should be selected as the input Column cell that column Q gets data of cost such that in Q6 the value will be 200.
In Q 7 this will be 150.
The worksheet is attached.
Looking forward for inputs.

 

[Karanbir Soin]

The File has some corrections done.
Please use this file.

 

[bosco_yip]

The File has some corrections done.
Please use this file.

Based on your revised file "Sheet1" information, I setup a simple FIFO table with formulas as in :

1] In "Sell Amount" I5, copied down :

=IF(G5="","",SUMPRODUCT((SUM(G$4:G5)>SUMIF(OFFSET(D$3,,,ROW(D$1:$D1)),">0"))*(SUM(G$4:G5)-SUMIF(OFFSET(D$3,,,ROW(D$1:$D1)),">0"))*(E$4:E4-E$3:E3))-SUM(I$3:I4))

2] In "Sell Price" H5, copied down :

=IF(G5="","",I5/G5)

3] In "Purchase Amount" F4, copied down :

=IF(D4="",0,D4*E4)

Remark : In this formula solution, Row 3 "Do not use" need to keep blank.

Regards
Bosco

 

[Karanbir Soin]

Thanks Bosco.
I have created a workbook with a sheet on your guideline. It is attached.
When I add another share id, the calculation of cost of shares sold includes the purchase price of the first share id. The calculation need to be on the share id specific. To get a balance shares for each row I have added a balance shares column. what could be formula for that.
Regards

 

[bosco_yip]

Thanks Bosco.
I have created a workbook with a sheet on your guideline. It is attached.
When I add another share id, the calculation of cost of shares sold includes the purchase price of the first share id. The calculation need to be on the share id specific. To get a balance shares for each row I have added a balance shares column. what could be formula for that.
Regards

1] The formula solution is designed for single product and not for multi-product

2] You can workaround by using separated worksheets for individual product, and adopt the same formula system

3] You can visit this Financial Modeling website to see another FIFO formula calculation, and you can understand it is not a simply job.

http://www.sumwise.com/blog/fifo/

Regards
Bosco

 

[Karanbir Soin]

Yes FIFO is a complex thing. The first file seen by you is modeled along lines of a protected workbook(FIFOComplete.xls), work of a Thai gentleman. I have sent a Mail to him I am awaiting a response.I am attaching that file, see if you can understand the data table part of the worksheet. Data for Inward/ outward in the first table can be changed.
It has some conditional formatting too.
It is very neat.

Regards