Solved

Inventory report in Excel and Crystal

Posted on 2011-09-24
4
248 Views
Last Modified: 2012-05-12
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
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36593366
I also have a flied for lead time from vendors
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 36594033
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
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36594062
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
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 36813296
Thank you mlmcc

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question