Urgent! Somebody Pleez help!! Inventory Valuation

[aromaveda]

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!!

 

[Bernard Liengme]

We are as confused as you!
Calm down and tell us the layout of the data and what summary features you
need.
best wishes

 

[aromaveda]

Hi Bernard,

The data lay out:- On worksheet 1 data for purchases - 6 column
containing transactional data- A)Date B) Style#, C) Qty, D) Pric
(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 ( I
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 W
1 ), D) Inventory Aging - 0-30 days old , F) 31-60 Days old, G) 61-9
days old.
The aging represents for how long the inventory on hand is sitting i
the warehouse. The report should compute inv on hand by addin
purchased qty for a month less sales qty and should lay down by the ag
of the inventory. If item A Qty = 1000 units was purchased in Aug 05, i
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 b
several purchases in a month & so the sales. The qty reduced by sale
shoud be on FIFO basis ( First In First Out) e.g. two sep purchases o
8/1/05 500 qty, on 9/1/05 qty 500, sales in Oct 05 qty 400, inv o
hand should have two diff agings for balance 100 out of first purch i
Aug & 500 in Sept.
If purchase/Sales data needs to be on same WS I don't mind, fo
simplicity I kept it on two separate sheets.

thanks

 

[Bernard Liengme]

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

 

[aromaveda]

I can get the net inventory on hand by using SumIf function but I cannot
figure out how would I do the datewise aging. If you could give me some
ideas I will try on my own and will seek your adivse when stuck

Thanks
Sanjay

 

[Bernard Liengme]

Have a look at SUMPRODUCT. Good explanation at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html