
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hi, I want to make an Exceksystem in a certain format. However, I have some problems making it. I hope the experts on this site can help me solve the problem. (possibly using VBA). The right material has to be found automatically and the right price must be calculated according the use of the materials. I have posted an attachment with description at http://www.worksheet.nl/forumexcel/v...8774fifo.html
Who can help me?
Regards, Exelhenk
Similar Excel Video Tutorials
Expense tracker training video
 This training video from ExcelExperts.com teaches you how to use the Expense Tracker system. Keeping track of your expenses is essential when times ar ...
Time Over or Under (Negative Time)
 See how to show Negative time with the "Use 1904 date system". See how to create a formula that will show the time over or under a hurdle.
Similar Topics
I am trying to interpolate between data. Imagine the following situation:
Stock trade: Trade Price: Consideration: FIFO Cash:
+100,000 2.50 250,000 0
+50,000 2.75 137,500 0
+225,000 3.00 675,000 0
Then I sell: 184,250 @ 3.40
The resultant cash that is due from these trades is calculated on a FIFO
basis (First In First Out). So the calculation would be (100,000
*(3.402.50) + 50,000 *(3.402.75) + 34,350 * (3.403.00)) = 136,240. But I
want to add a column that does this automatically. I.e.:
Stock trade: Trade Price: Consideration: FIFO Cash:
+100,000 2.50 250,000 0
+50,000 2.75 137,500 0
+225,000 3.00 675,000 0
184,250 3.40 626,450 +136,240
The cash line would also need to be able to go the other way... i.e. if had
sold first and then hit a buy, what is the cash flow on a FIFO basis.
Any ideas how I can do this??
Dear all,
It gives immense pleasure to stay as member of this forum as it helps to solve all my difficulties in excel in a matter of one day.
Thank you all .
I still pose one the problem of FIFO consumption valuation.In the enclosed file purchases are recorded in "inputPurchase" sheet with their landed costs.
The material is consumed on day to day basis and i want to calculate the consumption value based on FIFO basis at column "D2" onwards on sheet "OutputFIFO consumption".
I understand the difficulty faced my me to arrive at consumption value for each day.
Thank you all in adavnce
Gentle
Hello Everybody,
I found this UDF called as FIFO on internet . I tried to use it. This UDF calculates inventory value on FIFO basis. It worked perfectly excet in one instance( marked in yellow colour in attached file.) can anybody help me what is wrong. I understand VB very little. Can record macros but no difficult programming. This UDF will help me a lot if this problem is solved.
Can anybody help me?
Hello,
I have a spreadsheet that has historical buy and sell transactions of securities ('Trade Blotter' tab) and this information is summarised in the 'Filter' tab by security (that is, if there were 3 transactions for CBB historically, cell B3 in 'Filter' shows how many shares of CBB are currently owned and what the total cost has been on an aggregate level.) The final piece of information I'm looking for on an aggregate level is the cost per unit (which is not simply cost divided by shares, but is using the FIFO method). I have calculated it using the FIFO method on the 'FIFO' tab, cell C8. FIFO tab shows the historical transactions of CBB in this example, it is assumed that the 400 shares sold are the first 400 shares purchased (thus 221 from the first transaction and 189 from the 2nd transaction) at those prices. Thus, in this case, I have 262 shares remaining, all from the 2nd transaction, and they were all purchased at $113.28.
What I am looking to do is to put the total cost value from cell D8 of the 'FIFO' tab in cell F3 on the 'Filter' tab. Thus, I'm looking to consolidate the calculations in the FIFO tab. Would anyone be able to help with this, either through VBA or formulas?
Thanks in advance,
http://www.4shared.com/file/rJJSBIjG/Blotter.html
Hello
I am hoping this is an esay thing to fix but I have found a FIFO function to use in Excel and it doesnt seem to work. My VBA is basic and I cant step through the code as it is a function. All I basically want to calculate is the FIFO value of the sale quantity. As you will see in the attached its calculating some weird values.
For the Sale on the 1st Nov I would expect the following:
323 x 640 = 206,720
For the Sale on the 2nd which goes across two inventory values I would expect the following:
3,409 x 640 = 2,181,760
392 x 668 = 261,856
= 2,443,616 as sale USD value FIFO and then this would come off the closing value.
Thanks for your help.
Hi:
I found this UDF VBA code to calculate FIFO on this Site: http://www.excelit.com/workbook_downloads.htm and I need to know how ti install it for use on a new Excel Workbook.
I've been able to use and play around with it within the .xlt workbook provided; but now, I'd like to transport it to my own workbook and it it there.
Can someone help me with this. I'd appreciate i very much.
Cheers!
Hi,
I am trying to calculate a current position and a Weighted Average Price using teh FIFO methodology.
the attachment shows the data has a long (or positive) position of 56 if you total hte buys and sells.
Could you help me do this calculation using excel functions, ideally without excel VB but if that is the only soloution then great.
Thanks Craig
I'm trying to value inventory using FIFO method. I have multiply "layers" of values. Example is I have an inventory of 1,000 lbs. to value. Below are the last three transactions with prices paid.
Dec 15  200 lbs @ $1.25
Dec 10  500 lbs @ $2.00
Dec 8  500 lbs @ $1.50
Doing the math using FIFO method the 1,000 lbs should be valued at $1.70/lb or $1,700 total (200x1.25 + 500x2.00 + 300x$1.50).
I'm looking to create a formula that can be used every month that will calculate from a list similar to above where column A will be the date, column B the lbs, and column C the price/lb. I could add another column (D) that would be total price if that helps. I would like to just be able to enter the lbs of inventory I'm needing to value and have a fomula calculate the FIFO value based of the data above.
Hope this makes sense.
Hi all. First of all, I would like to mention that I've only worked with excel for a couple of days, and I need some advice.
My problem: there is a building with 1 elevator, 11 floors and 140 passengers of which we know their arrival time, their destination and their arrival floor. I now have to calculate the sum of the service times (= waiting time + traveltime) of all passengers for 2 heuristics: FIFO and circular.
FIFO(first in first out): the first passenger will be serviced first, the second second, etc... So,we deal with every passenger individually, so at no time, there is more than one passenger in the elevator.
Circular: the elevator goes up to the top floor and picks up everybody going up on his way. When the elevator reaches the top, he goes back down and now picks up everybody going down. In this case, MORE than 1 passenger can be in the elevator at the same time
For the FIFO case, I already managed to calculate the total service time. But for the circular heuristic, I'm really stuck..
Does anyone have suggestions?
I uploaded the data, and my solution for FIFO as an example. The original data is in red. The black data is my solution for the fifo problem.
Thx in advance!
http://www.2shared.com/file/9013033/...erekening.html
Hello,
I have a spreadsheet that has historical buy and sell transactions of securities ('Trade Blotter' tab) and this information is summarised in the 'Filter' tab by security (that is, if there were 3 transactions for CBB historically, cell B3 in 'Filter' shows how many shares of CBB are currently owned and what the total cost has been on an aggregate level.) The final piece of information I'm looking for on an aggregate level is the cost per unit (which is not simply cost divided by shares, but is using the FIFO method). I have calculated it using the FIFO method on the 'FIFO' tab, cell C8. FIFO tab shows the historical transactions of CBB in this example, it is assumed that the 400 shares sold are the first 400 shares purchased (thus 221 from the first transaction and 189 from the 2nd transaction) at those prices. Thus, in this case, I have 262 shares remaining, all from the 2nd transaction, and they were all purchased at $113.28.
What I am looking to do is to put the total cost value from cell D8 of the 'FIFO' tab in cell F3 on the 'Filter' tab. Thus, I'm looking to consolidate the calculations in the FIFO tab. Would anyone be able to help with this  I believe it would be through the use of multiple arrays in one cell as I've done in column D of the 'FIFO' tab.
Thanks in advance,
Ayesha
Hello, I hope someone can help me with this, I have a inventory management worksheet for several products and I want to use the First In, First Out system for this.
This is the layout for the Initial Inventory:
Code / Quantity / Unitary Cost
00001 / 200 / $80
This is the layout for inventory In:
Code / Quantity / Unitary Cost
00001 / 400 / $100
00001 / 600 / $150
This is the layout for inventory Out:
Code / Quantity / Formulated Unitary Cost
00001 / 400 / Formula Result = $90
00001 / 400 / Formula Result = $125
Is there a way using either excel formulas or VBA code to calculate the FIFO system?
I appreciate any help you could offer, thanks!
how to write a formulas to calculate FIFO cost in columm G when stock is out base on the attached, please help me!
Sheet1
*
A
B
C
D
E
F
G
H
2
prodcode
date
in
out
price
Bal_Q
cost$
*
3
A
30Oct10
9
*
* * * * * * 9.00
* * * * * * * * * 9
*
*
4
A
30Oct09
300
*
* * * * *10.00
* * * * * * *309
*
*
5
A
15Jan10
*
* * * * * * *200
* * * * *11.00
* * * * * * *109
* *1,991.00
*
6
A
19Jan10
* * * * * * *400
*
* * * * *10.00
* * * * * * *509
*
*
7
A
19Jan10
*
150
* * * * *12.00
* * * * * * *359
* *1,500.00
*
8
A
27Jan10
*
* * * * * * *100
* * * * *10.50
* * * * * * *259
* *1,000.00
*
9
B
27Jan10
* * * * * * *100
*
* * * * *10.50
* * * * * * *100
*
*
10
B
30Mar10
*
* * * * * * *100
* * * * *11.00
* * * * * * * *  *
* *1,050.00
*
11
*
*
*
*
*
*
*
*
12
*
*
*
*
*
*
*
*
Excel tables to the web >> Excel Jeanie HTML 4
Thanks in advance!
Friends,
Good day to you all.
Please help to to solve this perinial problem of FIFO valution.
I have a sheet named "InputdataPurchse" where the landed cost are recorded for all products for entire year.
I have to calculate the FIFO value for the closing stock as on particular date or on the last date of month.
Sample for the month of april and may is shown as output in their respective sheet by doing it manually which took quite a lot of time and with 100% scope of error.
Somebody help me to get the things easy way to do it.
Thanks in advance.
Gentle
Hi,
I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First InFirst Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.
I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.
Any help is greatly appreciated.
Thanks!!!
Hi. This is another FIFO question.
I would like to get a FIFO cost of my stock portfolio which can have both positive and negative inventory (I mean Long position and short position).
Date
Item
Buy/Sell
Price
Cost
1Jan
Apple
200
80
80
1Jan
Banana
1000
25
25
15Jan
Apple
400
100
93.33
1Feb
Apple
400
90
100
15Feb
Apple
200
90
95
1Mar
Apple
600
100
100
Date is a date on which I bought or sold.
Item is an item. Company
Buy/Sell positive number is purchase and negative number is a sales.
Price is a price at which I bought or sold the share.
Cost is the number I would like to get using UDF.
Functions should look like this =FIFO(items,buy/sell,price)
Table should be sorted in chronological order beforehand.
Any help appreciated. Thank you very much.
Hi All,
I am trying to value inventory on FIFO basis (atleast to nearest possible accurate level). I have exported & doctored few reports from Quick Books (accounting s/w). On attached sheet on FIFO details tab I caputred on hand inventory, received between 7109 to 81809 PO by PO details on PO cost tab with other cost overhead items broken down. what i would like to do.. on FIFO details tab for on hand inventory find rate or average rate starting from the last received PO. see example shown on line 11 on FIFO Details sheet. can any one provide any code or formula to achieve this? # PO received column does not have much significance, since i was trying to check how many po's are making the total received qty's. Please help
SD
Hi,
Good day to you all.
I have a problem of FIFO calculation basedon the "Purchase book Master" and "closing Balance FIFO".
Ineed to arrive (extract from "purchase book master") the qty ,lot ,and landed cost /unit as on thedate of closing balance which is in sheet"closing balance FIFO" incell A1 onwards.Assume the consumption occured on FIFO basis.SO the closingbalance lying is from latest one and the previous one if qty is not sufficientto cover closing balance.
Theoutput desired clearly indicates the solution I am expecting.What I havepresented is only a small portion of data.So I need someones help to sove this issue.
TOBE system I am looking for.
1) User will place the file (to be uploaded) in a specific folder
Constraint  File may have different name each time, but there will be only one file in the folder at a given time.
2) Macro should read data from this file and upload into the system
Problem facing  Using Transfersheet functionality but since my file name is different every time, the macro is not able to find the file. How can I overcome this problem and make macro to pick file with file name consideration.
Ideally I want the system to work in a way that any time someone drops file into this folder, system should read the file automatically (FIFO) and then move this file to some other folder and picks the next one in line. Is it possible?
any pointers will help
SKV
Dear Sir,
please help me for calculation of capital gain through stocks on FIFO Method . i had attch my stock trade book file for your reference. it will be very grateful as well as very helpful for me if you help me by adding any formula or any macro function which will calculate capital gain for different stocks base on FIFO method. in my attach file i had attch CP WORK SHEET which i had downloaded from boker website.
so the program should not only calculate the profit by subtracting the cost of shares on FIFO basis (that is identify the earliest existing stock). but since the data does not readily give me the correlation between the purchases and sales on FIFO basis
i want to know how CP calculation base on FIFO Mehod by first considering sale qt, sale date, sale price then purchase qt base on sale qt, purchase date, will be done in excel file base on Trade book data.
I had uploaded file in discussion forum.
Thanks a lot in advance
Thanks
Sunil
Hello All...
I need help to calculate inventory value using FIFO method...
never used VB.. rather only simple formulas...Can someone help??
Thanks
Hoi, ik wil een excelsysteem maken in het format zoals ik in de bijlage heb meegestuurd. Ik loop echter tegen wat problemen aan waarvan ik hoop dat jullie die voor mij op kunnen lossen (eventueel met gebruik van VBA). Zo moet automatisch het juiste materiaal gevonden worden en moet de juiste prijs berekend worden bij het gebruik. (zie excel). Een bijlage met omschrijving is te vinden op: http://www.worksheet.nl/forumexcel/v...tml#post573182
Wie kan mij helpen?
Groeten, Excelhenk.
Hi All,
I need help to come up with a solution for FIFO (First In First Out) method for tracking the "buys" and "sells" in a list of stocks. I would prefer a solution without UDF or VB programming (Im not good with those). Just a clever Excel setup/formulas would help tons.
Example:
Bond Trans Date Amount
A Buy 1/1/2010 1,000.00
A Buy 1/15/2010 1,000.00
A Sell 1/20/2010 (1,000.00)
A Buy 1/25/2010 1,000.00
A Sell 1/30/2010 (2,000.00)
B Buy 2/1/2010 500.00
B Buy 2/5/2010 300.00
B Sell 2/15/2010 (300.00)
B Sell 2/27/2010 (500.00)
C Buy 3/6/2010 250.00
C Sell 3/10/2010 (250.00)
C Buy 5/2/2010 400.00
C Sell 5/24/2010 (400.00)
So, basically I need to apply the first "Sell" date to the first "Buy" date. Stock A was first sold on 1/20/2010 for $1,000, therefore, I would reduce the first "Buy" on 1/1/2010 by the 1,000. This is FIFO method. The raw data comes in this format and we can change/adapt the layout to get FIFO working.
Thanks for your help!!!
I am building an inventry program which involves both the amount of item in stock and thier value.
Does any one know how to do a FIFO ( first infirst out) formula.
I appreceate any help, thanks
Ray
Dear all,
Some saviour to solve this never ending iteration problem of FIFO valuation of Products consumed in the following attched file.This FIFO value of consumption derived from purchase master;date,product,qty,landed cost.
All fields are entered as INPUT.
I want the results from cell K2 onwards.
Please some one save me
Purchase Master
Consumption master
Date(A)
Product(B)
Qty(C)
Landed cost/
unit (D)
Good Receipt
Note No.(E)
Purchase order No.
(F)
Consumption
date(H)
Product Name(I)
Consumption
Qty(J)
FIFO value of consumption(K)
05042010
BS10
5000
86
7
pur/1011/002
06042010
BS10
2500
215000
0604 2010
ADD15
200
115
8
pur/1011/005
08042010
BS10
500
43000
07042010
ADD16
14000
89
9
pur/1011/008
08042010
ADD15
8.25
948.75
08042010
BS10
5000
88
10
pur/1011/002
08042010
ADD16
12000
1068000
09042010
ADD9
50
1700
11
pur/1011/007
09042010
BS10
4000
348000
10042010
ADD15
50
125
12
pur/1011/009
09042010
ADD15
87.5
10062.5
01052010
BS10
18000
87.56
23
pur/1011/026
10042010
ADD16
1500
133500
05052010
ADD9
50
1675
24
pur/1011/029
06052010
BS10
5000
439120
07052010
ADD15
50
132.86
25
pur/1011/032
08052010
ADD16
14125
1166655
08052010
ADD16
14000
82.36
26
pur/1011/037
11052010
BS10
2500
92.56
29
pur/1011/040
12052010
ADD15
50
125.89
30
pur/1011/042
Hi guys,
I need to use SUMIF with more than one criteria, and I'm having problems. I'm not an expert with Excel formulas, and although I found a site that (supposedly) explained how to get round this problem (using DSUM), I couldn't make sense of it. I'll try to explain my situation in plain English...
My spreadsheet contains 2 ranges of data (one from System X & one from System Y) to be reconciled.
Column A contains the currency of the deal from System X.
Column B contains the type of deal from System X.
Column C contains the amount of the deal from System X
Column E contains the currency of the deal from System Y.
Column F contains the type of deal from System Y.
Column G contains the amount of the deal from System Y.
Now, what I need is for Column D to Sum all amounts from Column G where the deal type in Column F is equal to the deal type in Column B AND the currency in Column E is equal to the currency in Column A.
I hope this makes sense, as I can barely get my head round it myself.
If anyone knows the solution, your advice would be greatly appreciated.
Many thanks,
Jon

