This is a big project. Why not get started yourself and tell us what goes
wrong.
Start by reading a simple Excel book.

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"aromaveda" wrote
in message ...

Hi Bernard,

The data lay out:- On worksheet 1 data for purchases - 6 columns
containing transactional data- A)Date B) Style#, C) Qty, D) Price
(FOB), E) Duty, F)Freight etc... F) Avg Cost.
*On Worksheet # 2 * - Data related to sales - A) Date, B) Invoice #, C)
Customer, D)Qty Sold, E)Style #, F)Rate, G)Total.
Summary features needed - Inventory on hand as of month end ( If
possible on any given date) Report could have columns A) Style # B)
Total Qty On hand C) Cost Price ( for style per unit = Avg cost from WS
1 ), D) Inventory Aging - 0-30 days old , F) 31-60 Days old, G) 61-90
days old.
The aging represents for how long the inventory on hand is sitting in
the warehouse. The report should compute inv on hand by adding
purchased qty for a month less sales qty and should lay down by the age
of the inventory. If item A Qty = 1000 units was purchased in Aug 05, in
Sept 500 were sold = Qty on hand should be 500 but should be shown in
60-90 days old column & valuation should be at avg cost. There might be
several purchases in a month & so the sales. The qty reduced by sales
shoud be on FIFO basis ( First In First Out) e.g. two sep purchases on
8/1/05 500 qty, on 9/1/05 qty 500, sales in Oct 05 qty 400, inv on
hand should have two diff agings for balance 100 out of first purch in
Aug & 500 in Sept.
If purchase/Sales data needs to be on same WS I don't mind, for
simplicity I kept it on two separate sheets.

thanks


Bernard Liengme Wrote:
We are as confused as you!
Calm down and tell us the layout of the data and what summary features
you
need.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"aromaveda"
wrote in
message ...

I need to prepare a report on a periodical basis of inventory on
hand
Qty, value & age ( 0-30,31-60, 61-90 days etc.) In the database I
will
have few cost components of each purchase ( FOB, Duty, Freight
etc.)
by style #. Second table will be Sales(Date, Inv #, Cust, Style,
Qty,
Selling Px). I need to run the report usually on a month end but
sometimes on any given date. Which functions/formulas I should use?
I
am bit confused. Some one please help with some sample
codes/Functions/formulas. Thanks a bunch in advance!!



--
aromaveda

------------------------------------------------------------------------
aromaveda's Profile:
http://www.excelforum.com/member.php...o&userid=29449
View this thread:
http://www.excelforum.com/showthread...hreadid=494868



--
aromaveda
------------------------------------------------------------------------
aromaveda's Profile:
http://www.excelforum.com/member.php...o&userid=29449
View this thread: http://www.excelforum.com/showthread...hreadid=494868