How to assign unit cost to each sales base on FIFO

[solau]

Hi Dudes,

I am having this VBA task and i am facing extreme difficulties. It will be great if I can receive any suggestions/ inspiring solutions to this problem.

Basically, there are three worksheets in my excel workbook.

First,it is the purchase worksheet where the columns are as below. (I have like 10 different products, but to simplify the situation, lets assume there is only product A)

[TABLE="width: 270"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD="align: right"]01-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]02-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]04-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]105[/TD]
[/TR]
</tbody>[/TABLE]

Second, it is the sales worksheet where the columns are as below.
[TABLE="width: 592"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Total amount[/TD]
[/TR]
[TR]
[TD="align: right"]03-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]960[/TD]
[/TR]
[TR]
[TD="align: right"]04-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]1280[/TD]
[/TR]
[TR]
[TD="align: right"]05-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]960[/TD]
[/TR]
</tbody>[/TABLE]


Third, it is the PNL sheet which extracts information from the first and second sheet and calculate the daily PNL on product A. This is where I will need the VBA codes, I am fine with the part to extract the information from first and second sheet. However, I am not able to assign costs to.

The below is the ideal look. It will extract all the sales from sheet 2 and try to assign the unit cost (column 5) base on purchase from sheet 1. The question is how i can get its unit cost (column 5) from sheet 1 base on FIFO.

For example, the sales on 3 Jan should have a unit cost of 100 because it is from the first purchase and so is the sales on 4 Jan.

For the sales on 5 Jan, since the first purchase are all sold, its unit cost will be from the second purchase on 2 Jan and it will be 130.

[TABLE="width: 455"]
<tbody>[TR]
[TD]Date[/TD]
[TD="width: 65"]Product[/TD]
[TD="width: 65"]Quantity[/TD]
[TD="width: 65"]Unit Price[/TD]
[TD="width: 65"]Unit Cost[/TD]
[TD="width: 65"]Unit Profit[/TD]
[TD="width: 65"]Total Profit[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]03-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]660[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]04-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]440[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]05-Jan-16[/TD]
[TD]Product A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]760[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much for the kind help!

 

[solau]

ha Mick, this is not more robust, the calculation results in Sheet 3 (E, F , G ) are all wrong haha.

But I have amended it and it is working fine.

Thanks so much showing all the arrays and it helps improve my understanding of what you are doing there !

Coding is really fun and sometimes, I just need to think out of the box.

There is no certain answer and diff ppl can have diff solutions to a specific problems .

Thanks so much !!!!

 

[MickG]

Glad you're getting to grips with it !!
Regrds Mick

 

[solau]

Hi Mick,

From the codes, I am having some questions on array.

The below is for copying cells value into the array for later manipulation in the purchase sheet (the one which records costs of the purchase).

<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]-->[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Date[/TD]
[TD="width: 65"]Product[/TD]
[TD="width: 65"]Quantity[/TD]
[TD="width: 65"]Cost[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]01-Jan [/TD]
[TD]Product A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]02-Jan [/TD]
[TD]Product B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]130[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

The below codes are from you.


-------From Mick,
Dim i, ii, c As Integer
Dim stup()


c = 0
For Each prod In Range(Cells(2, 2), Cells(3, 2))


For i = 1 To prod.Offset(, 1)
c = c + 1
ReDim Preserve stup(1 To 4, 1 To c)
stup(1, c) = prod.Offset(, -1)
stup(2, c) = prod
stup(3, c) = prod.Offset(, 1)
stup(4, c) = prod.Offset(, 2)
Next i


Next prod

------ from Mick

When we look at the cells(x,y), x is the row index and y is the column index

Hence, I think it will be more direct if I code like the below. Also, it will not require the use of transpose when I later want to put the array value into the sheets.

Sadly, I will receive an error saying that "Subscript out of range".

I have been searching around and still cannot get an answer to fix my codes.

Can you please provide some insights?

----from Harris
Dim i, ii, c As Integer
Dim stup()


c = 0
For Each prod In Range(Cells(2, 2), Cells(3, 2))


For i = 1 To prod.Offset(, 1)
c = c + 1
ReDim Preserve stup(1 To c, 1 To 4)
stup(c, 1) = prod.Offset(, -1)
stup(c, 2) = prod
stup(c, 3) = prod.Offset(, 1)
stup(c, 4) = prod.Offset(, 2)
Next i


Next prod
-----from Harris