First-In, First-Out Inventory Database

[rommelq]

Hi,

I'm trying to create a simple first-in, first-out inventory database using excel but without the vbasic codes. Below is a sample of my database. I would like to seek your help in coming up with formulas to replace the "?" Or you may suggest some other way to achieve the same results.

My objectives are to breakdown the ending inventory under the FIFO method using simple formulas and to determine the total consumed tanks and corresponding costs for the month.

By the way, i'm using only Excel 2002 under Windows ME.

Thank you.


First In, First Out
Date Type Tanks Unit Price Amount
01/01/04 Delivery 10 1,210.00 12,100.00
01/05/04 Delivery 5 1,220.00 6,100.00
01/06/04 Consumed -6 ? ?
01/10/04 Delivery 6 1,230.00 7,380.00
01/12/04 Consumed -8 ?
01/15/04 Delivery 20 1,240.00 24,800.00
01/17/04 Consumed -12 ? ?
01/20/04 Delivery 5 1,250.00 6,250.00

Balance 20

Breakdown:
?
?

 

[Zack Barresse]

forgive my ignorance, but what are you looking for in your breakdown? can you post using Colo's HTML maker (link on bottom of page)

 

[SIXTH SENSE]

hi!
Just a question.
What would be the unit price in this line

01/12/04 Consumed -8 ?

where in 21 units has been delivired 10+5+6
and 6 has been consumed?
It is obvious that this has mixed price per unit. thus
I think requires another row to fully show the how much this
cost for this month.
Thus averaving the price will do for this situation where the
overall price for one month will still be eqaul?

 

[rommelq]

Thank you for your replies. Sorry if I was a little bit unclear about my case. Also there'san error when i try to install the add in from COLO HTML maker. I'll try to elaborate it further.

First In, First Out
Date Type Tanks Unit Price Amount
01/01/04 Delivery 10 1,210.00 12,100.00
01/05/04 Delivery 5 1,220.00 6,100.00
01/06/04 Consumed -6 -
01/10/04 Delivery 6 1,210.00 7,260.00
01/12/04 Consumped -8 -
01/15/04 Delivery 20 1,240.00 24,800.00
01/17/04 Consumped -12 -
01/20/04 Delivery 5 1,250.00 6,250.00

Balance 20


Let's say my boss ask me, "From what delivery batches did you get the consumption (8 tanks) last 01/12/04?
Give me the delivery batches, unit cost, and the total amount."

My manual method would be:
01/01/04 delivery 10 1,210.00 12,100.00
less 01/06/04 consumed -6 1,210.00 (7,260.00)
Bal 01/01/04 delivery 4 1,210.00 4,840.00
add 01/05/04 delivery 4 1,220.00 4,880.00
8

Then, he also asked me, "What is the breakdown for the inventory balance?"

I have to manually trace the deliveries first to come up with the answers:
Balance Breakdown of the 20 tanks:
01/20/04 delivery 5 1,250.00 6,250.00
01/15/04 delivery 15 1,240.00 18,600.00
20

Is there a way to manage the database so I can come up with the answer quickly without doing much analyzing of my data?
ray:

 

[ken2step]

Your totals of your breakdowns don't jive with your data????

01/20/04 delivery 5 1,250.00 6,250.00
01/15/04 delivery 15 1,240.00 18,600.00

Data above that reads:

01/15/04 Delivery 20 1,240.00 24,800.00
01/20/04 Delivery 5 1,250.00 6,250.00

I am unclear how you can compare units when the unit prices are different each delivery also. Can you elaborate some more on this???
Ken

 

[rommelq]

Sorry to got you confused.

The breakdown of 20 tanks came from the latest delivery batches. Using the first in first out method of inventory the 20 tanks came from:


delivery batch dated 01/20/04 5 tanks @1,250.00/tank total=6,250.00

and

delivery batch dated 01/15/04 15 tanks (out of the 20 tanks delivered) @1,240.00/tank total = 24,800.00


Hope i made it clear this time.

 

[SIXTH SENSE]

Hi Rommelq!
Is this the breakdown you want?
The computation of the cost of the consumtion can be automated thru a UDF.
See Fifo row if the above procces is the rigth computation proccess you want.

FIFO.xls
	A	B	C	D	E	F	G
13	Breakdown:
14
15	Delivery
16	Date	Type	Tanks	Unit	Price
17	1/1/2004	Delivery	10	1,210.00	12,100.00	10	12100
18	1/5/2004	Delivery	5	1,220.00	6,100.00	5	6100
19	1/10/2004	Delivery	6	1,230.00	7,380.00	6	7380
20	1/15/2004	Delivery	20	1,240.00	24,800.00	5	6200
21	1/20/2004	Delivery	5	1,250.00	6,250.00
22			46			26	31780
23	Consumed
24	Date	Type	Tanks	Unit	Price
25	1/6/2004	Consumed	6	1210	?	6*1210	7260
26	1/12/2004	Consumed	8	1210-1220		4*1210+4*1220	9720
27	1/17/2004	Consumed	12	1220-1230-1240	?	1*1220+6*1230+5*1240	14800
28			26			Totalconsumption	31780
29
30						FifoSolution	31780
Sheet2

 

[SIXTH SENSE]

Hi!

Is this a correct balance sheet? I tried my fifo UDF here if this
is the corect computation you want!

FIFO.xls
	A	B	C	D	E
1	Date	Type	Tanks	Unit	Price
2	January1,2004	Delivery	10	1,210.00	12,100.00
3	January5,2004	Delivery	5	1,220.00	6,100.00
4	January6,2004	Consumed	-6	Na	-7260
5	January10,2004	Delivery	6	1,230.00	7,380.00
6	January12,2004	Consumed	-8	Na	-9720
7	January15,2004	Delivery	20	1,240.00	24,800.00
8	January17,2004	Consumed	-12	Na	-14800
9	January20,2004	Delivery	5	1,250.00	6,250.00
10
11		Balance	20	TotalDelivery	56,630.00
12				TotalConsumption	-31,780.00
13				bal	24,850.00
Sheet2

 

[rommelq]

Sixth Sense,

You are correct! You got the balance of the inventory using the FIFO method. And only the automated presentation of the breakdown that is left. You mentioned a UDF? What is it?

 

[SIXTH SENSE]

Hi!
UDF means USER DEFINED FUNCTION . this is a code in VB that
that is tailored for your need. In other words this is a costumized function. I just forgot to post it. Ill look for it and post later.