Solved

Inventory report in Excel and Crystal

Posted on 2011-09-24
4
251 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 101

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

689 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