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
Solved

Inventory report in Excel and Crystal

Posted on 2011-09-24
4
244 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
question about excel functionality 3 33
How to bypass VBA Project password 5 53
Excel Calculation 4 52
Copy and Paste Text into Text Box 3 27
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

860 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