Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inventory report in Excel and Crystal

Posted on 2011-09-24
4
Medium Priority
?
255 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

609 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