• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Inventory report in Excel and Crystal

I'm getting involved in a project where the customers wants an inventory report on the following criteria/conditions. This is the first time I'm getting involved in this sort of reporting and I need to some help with the logic of the report. I will use Excel together with VBA connecting to the database and will not need help with the technical aspect of it just the logic. Any help and links with information is appreciated.

The logic will be:
Purchase recommendations base on sales history
Forecasting inventory needs base on sales history
Lead times for vendors
 
I have the following fields in the ERP system
Available qty
On-Hand qty
Committed qty
Back-order qty
On-order qty
Re-order point
Minimum order qty
And all the necessary fields for sales history

Any help and links with information is appreciated
0
Gerhardpet
Asked:
Gerhardpet
  • 3
1 Solution
 
GerhardpetAuthor Commented:
I also have a flied for lead time from vendors
0
 
mlmccCommented:
Why did you include Crystal in the question title?
You don't seem interested in using it as a reporting tool.

Need a clarification
Available Qty - total count in the warehouse that is not committed to an order
On Hand qty - total count in the warehouse
Committed Qty - total of orders

So On Hand = Available + Committed

On Order - is this the count you have ordered from suppliers for restocking?
Back Order - Amount due to you or the aount you are short on orders to your customers?

How often will this be run?
Daily, weekly, monthly, as necessary?

Who will run the sheet?
Will this be a shared sheet or will there perhaps be several copies?

Basic idea as I see it

For each product
    How much was sold this time last year? - query database for sales last year from current date to current date + 2 * lead time
    How much do we have available?
    If it is not enough how many should be ordered
         How much is on order that will arrive in time?
         May at this point want to look further ahead as 4* lead time
         Change reorder point?
    If sufficient to cover will we be at or below the reorder point?
         If so then how much to reorder?
               Look further ahead so you are covered
               Place several orders rather than 1 large?
              When to reorder? Consider lead time.  May be able to delay order so it arrives when we hit the reoorder point.
     If still above reorder point mark as OK - relook in 1 day? 1 week?

mlmcc

0
 
GerhardpetAuthor Commented:
Crystal was not supposed to be included in the question. It must of been there from a previous question I asked.

I will answer your questions soon. Thank you for your input for now
0
 
GerhardpetAuthor Commented:
Thank you mlmcc

Your answer will help and I also found some good information here http://www.effectiveinventory.com
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now