Fifo-system

Hi:

I found this UDF VBA code to calculate FIFO on this Site: http://www.excel-it.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
00-001 / 200 / $80

This is the layout for inventory In:

Code / Quantity / Unitary Cost
00-001 / 400 / $100
00-001 / 600 / $150

This is the layout for inventory Out:

Code / Quantity / Formulated Unitary Cost
00-001 / 400 / Formula Result = $90
00-001 / 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 30-Oct-10 9 * * * * * * * 9.00 * * * * * * * * * 9 * * 4 A 30-Oct-09 300 * * * * * *10.00 * * * * * * *309 * * 5 A 15-Jan-10 * * * * * * * *200 * * * * *11.00 * * * * * * *109 * *1,991.00 * 6 A 19-Jan-10 * * * * * * *400 * * * * * *10.00 * * * * * * *509 * * 7 A 19-Jan-10 * 150 * * * * *12.00 * * * * * * *359 * *1,500.00 * 8 A 27-Jan-10 * * * * * * * *100 * * * * *10.50 * * * * * * *259 * *1,000.00 * 9 B 27-Jan-10 * * * * * * *100 * * * * * *10.50 * * * * * * *100 * * 10 B 30-Mar-10 * * * * * * * *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 "Inputdata-Purchse" 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 In-First 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 1-Jan Apple 200 80 80 1-Jan Banana 1000 25 25 15-Jan Apple 400 100 93.33 1-Feb Apple -400 90 100 15-Feb Apple 200 90 95 1-Mar 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 7-1-09 to 8-18-09 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 in-first 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) 05-04-2010 BS-10 5000 86 7 pur/10-11/002 06-04-2010 BS-10 2500 215000 06-04- 2010 ADD-15 200 115 8 pur/10-11/005 08-04-2010 BS-10 500 43000 07-04-2010 ADD-16 14000 89 9 pur/10-11/008 08-04-2010 ADD-15 8.25 948.75 08-04-2010 BS-10 5000 88 10 pur/10-11/002 08-04-2010 ADD-16 12000 1068000 09-04-2010 ADD-9 50 1700 11 pur/10-11/007 09-04-2010 BS-10 4000 348000 10-04-2010 ADD-15 50 125 12 pur/10-11/009 09-04-2010 ADD-15 87.5 10062.5 01-05-2010 BS-10 18000 87.56 23 pur/10-11/026 10-04-2010 ADD-16 1500 133500 05-05-2010 ADD-9 50 1675 24 pur/10-11/029 06-05-2010 BS-10 5000 439120 07-05-2010 ADD-15 50 132.86 25 pur/10-11/032 08-05-2010 ADD-16 14125 1166655 08-05-2010 ADD-16 14000 82.36 26 pur/10-11/037 11-05-2010 BS-10 2500 92.56 29 pur/10-11/040 12-05-2010 ADD-15 50 125.89 30 pur/10-11/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