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!

 

[MickG]

Try this:-
Your Data on sheet1 and sheet2 Starting "A1", Results on sheet 3 Starting "A1".

[COLOR="Navy"]Sub[/COLOR] MG07Feb29
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Temp        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] g           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   
 [COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
   [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
                oSum = oSum + Dn.Offset(, 2)
                Dic(Dn.Value)(Dn.Offset(, 1).Value) = Dn.Offset(, 2)
    
    [COLOR="Navy"]Next[/COLOR] Dn
   
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
 [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("b2"), .Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
    ReDim ray(1 To Rng.Count * 2, 1 To 7)
    ray(1, 1) = "Date": ray(1, 2) = "Product": ray(1, 3) = "Quantity": ray(1, 4) = "Unit Price"
    ray(1, 5) = "Unit Cost": ray(1, 6) = "Unit Profit": ray(1, 7) = "Total Profit"


p = 0: c = 1
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Temp [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Temp = Dn [COLOR="Navy"]Then[/COLOR] p = 0: g = 0
    [COLOR="Navy"]End[/COLOR] If
    c = c + 1
    [COLOR="Navy"]For[/COLOR] n = 1 To Dn.Offset(, 1)
        [COLOR="Navy"]If[/COLOR] Dic(Dn.Value).exists(g) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] p < Dic(Dn.Value).Count - 1 [COLOR="Navy"]Then[/COLOR]
                p = p + 1
                [COLOR="Navy"]If[/COLOR] Not IsEmpty(ray(c, 1)) [COLOR="Navy"]Then[/COLOR] c = c + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
          g = g + 1
            ray(c, 1) = Dn.Offset(, -1).Value
            ray(c, 2) = Dn.Value
            ray(c, 3) = ray(c, 3) + 1
            ray(c, 4) = Dn.Offset(, 2).Value
            ray(c, 5) = Dic(Dn.Value).items()(p)
            ray(c, 6) = ray(c, 4) - ray(c, 5)
            ray(c, 7) = ray(c, 6) * ray(c, 3)
   [COLOR="Navy"]Next[/COLOR] n
 [COLOR="Navy"]Set[/COLOR] Temp = Dn
 [COLOR="Navy"]Next[/COLOR] Dn


[COLOR="Navy"]With[/COLOR] Sheets("Sheet3").Range("A1").Resize(c, 7)
    .Value = ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[solau]

Hey Mick, you are a genius ! I have been thinking about this problem for a week !
thanks so much for your kind help! Let me try to adjust the codes to see if it works on my data. I really appreciate that !

 

[MickG]

You're welcome

 

[solau]

Hey Mick, I have been spending time on comprehending the code and learning about the new thing createobject.
Create object is totally new to me and I will need some time to digest and to understand the usage of it.

My question will be
1. I have the data shown on the above on sheet 1 and sheet 2. (it is expected that the data on sheet 3 will be auto fed and extracted from sheet 1 and 2)
May I know if the above code is already for that?

2. It seems that me that createobject is like creating a huge array to store all data and speed up calculation / manipulation process. However, I tried on my excel worksheet, which looks exactly as what it looks as the above. I created a button on sheet 3 and pasted your codes. It failed to create object.
Is there anything that I need to enable when i want to create object?

3. oSum = oSum.offset(,2)
dic(dn.value)(dn.offset)

 

[solau]

Hey Mick, I have been spending time on comprehending the code and learning about the new thing createobject.
Create object is totally new to me and I will need some time to digest and to understand the usage of it.

My question will be
1. I have the data shown on the above on sheet 1 and sheet 2. (it is expected that the data on sheet 3 will be auto fed and extracted from sheet 1 and 2)
May I know if the above code is already for that?

2. It seems that me that createobject is like creating a huge array to store all data and speed up calculation / manipulation process. However, I tried on my excel worksheet, which looks exactly as what it looks as the above. I created a button on sheet 3 and pasted your codes. It failed to create object.
Is there anything that I need to enable when i want to create object?

3. oSum = oSum + oSum.offset(,2)
Dic(Dn.Value)(Dn.Offset(, 1).Value) = Dn.Offset(, 2)
‘ may I know what you are trying to achieve here?
It looks like you are trying to sum up all costs on column 4 on sheet 1 and put the sum as oSum.

4. p = 0: c = 1
what is it for? Just to assign value to p and c?

5. after you have dim temp, you have never assigned value to it.
If this is the case, I would expect nothing is in temp.

What is the rationale of the below? Of course temp is nothing and of course temp <> dn given that you did not assign any value to that before.

For Each Dn In Rng
If Not Temp Is Nothing Then
if Not Temp = Dn Then p = 0: g = 0


6. actually, to generalise , will it be possible that you add some lines of comment to the codes to help me understand them?


7. I believe that some of the above questions are quite dumb as I do no really have a clear understanding of createobject.

I have been searching how I can better understand that.

The below is sth that I find the most explanatory. Do you have any recommendation?
VBA for smarties: Dictionaries


thank you so much again! I know I have quite a lot of questions…. Will really appreciate if you can spend your spare time on answering the questions. !

 

[MickG]

Attached is an example of your data (plus a bit).
https://app.box.com/s/z419uyhamrq0qint6n81mfizuw9vy2ev
Sheets 1 & 2 Hold the data with the results on Sheet3 Via button on sheet1.
I should try to run this code on my data first then try your data. See what the results are !!!!
This code uses the Dictionary object and creates an new Dictionary object for each Object specified in the code.
Your link "VBA for Smarties: Dictionaries" is very good and covers most information for Dictionaries, but only covers the basic dictionary.
You need a link to "Dictionary of Dictionaries" which gets more involved.
If you would Still likes some Notes on this code let me Know !!!

 

[solau]

hey Mick!
Thanks so much again!

I have downloaded the excel and tried to run it.

I have received a warning "licence information for this component not found. You do not have an appropriate licence to use this functionality in the design environment."

Does it mean that I need to enable to sth for the create object ? (not sure if the warning is specific to the create object as it does not even run the code and highlight the line with bugs as it usually does)

I am using excel 2011 macbook version.

I also tried copy and paste your codes on my own data. Basically the layout is exactly the same as yours, just the numbers are different. And it goes into the debug mode, highlighting that i cannot create object.

Thanks again in advance of your advice!

 

[MickG]

I'm very sorry but I, perhaps should have warned you, you cannot run a Dictionary object on a Mac Computer.
I'm also not sure I can solve your problem without it !!!

 

[miless2111s]

This could be done without VBA using some additional columns which given an cumulative total of units bought / sold and their costs and then use Vlookup to find the relevant Unit cost. You might have to do some fiddling around when a sales pack contains two or more prices but that could be handled with some additional columns.