Inventory Aging & 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!!

--------------------------------------------------------------------------------

 

[nbrcrunch]

Hello aromaveda and welcome to the board.

You'll need to provide the math that works on the fields.

In other words, if qty is multiplied by value, we need to know that.

To ask about formulas and functions in general, why, any number of them can be used. If you need an introductory course, this site has dozens of articles.

http://www.mrexcel.com/articles.shtml

____

First get a clear idea of what you need to do in general mathematical terms then attack the problem from there by finding the functions and formulas you need.

 

[aromaveda]

Hi nbr

Thanks for responding. In a nutshell my problem is to run a similar kind of report like AR Aging but with inventory qty numbers. To have a valuation next to that is to top it. Simply put I have all the info related to purchases ( date purhcase, product #, qty purchased, rate per unit, total amount) I could either put sales info on the same table but i prefer to have that on a separate sheet. columns would be ( date sold, invoice #, customer, qty sold, rate @ sold, total inv amount). Not all the columns in both the tables are needed for my report but i prefer to still have them for info purpose. Required- at the end of the month ( or on a date) inventory schedule will show stock on hand but it will break it down by age i,e it will show in specific columns how long it is been sitting in the warehouse. Example- product A - Qty 1000 purchased on 1st Aug 05, qty 500 on 1st Dec 05. 600 units Sold in Oct 05. If I run an inventory aging report as of Dec 05 the balance qty will be 900 but by using First in First out method it will take out 600 from oldest purchased units. The aging report will show inventory on hand in two seperate columns 400 units in 90 - 120 days & 500 units in 0-30 days. This tell me out of total inventory on hand how much is how old. I hope I not confusing you with this explanation.
Thanks
Sanjay

Hello aromaveda and welcome to the board.

You'll need to provide the math that works on the fields.

In other words, if qty is multiplied by value, we need to know that.

To ask about formulas and functions in general, why, any number of them can be used. If you need an introductory course, this site has dozens of articles.

http://www.mrexcel.com/articles.shtml

____

First get a clear idea of what you need to do in general mathematical terms then attack the problem from there by finding the functions and formulas you need.

 

[nbrcrunch]

what you are looking for is a full-fledged inventory system. For Excel to provide that functionality, you'd need more than simple formulas. Business practices always dictate: "Best tool for the need." Excel may not be what you are looking for.

However, for some "quick-and-dirty" reporting, perform a search on this site for SUMPRODUCT.

Also, investigate the filter option in Excel.

Also recommend searching from Yahoo for: Excel Inventory and see what hits you get.

http://search.yahoo.com/search?p=excel+inventory&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8