Hi,
1-How to do the demand panning & stock in excel ?
2-The sale data required in this analysis or what type of data require ?
Hi,
1-How to do the demand panning & stock in excel ?
2-The sale data required in this analysis or what type of data require ?
The questions are simple but the answers are not.
1. Do you have legacy data for the previous few years for the time periods like months or quarters giving the stock demand for each period?
2. Is there a trend to increasing or decreasing demand over the periods that you have data for and is there anything that could affect demand that is new?
3. Do you use J.I.T. (Just In Time) system of stock control or do you have warehousing of stock?
4. What is the lag time between ordering and delivery of stock?
5. Do your suppliers give bulk discounts and other discounts at different times of the year?
With the above you can determine the potential demand and the best time(s) to order stock both to minimize stock on hand and the cost of obtaining the required stock.
If suppliers give bulk discounts on orders, you may find that the discount more than offsets the cost of warehousing the stock or it may not pay to take the discount because it costs too much to warehouse the stock until required.
If there isn't a consistent demand trend for stock in any given period, it may be cost effective to make more than one order per period as demand becomes evident.
This is by no means a complete list of factors to consider.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Really answer are not simple by looking at your question you ask
1- No I don't have that data. Only sale report
2- If its related to sale so by seeing the sale report of every month I can check what brand or sku sale fast base of no of unit sold. Otherwise I don't clue.
3- Yes have warehouse
4- Its vary on product to product.
5- Yes they give discount on off seasonal product
Actually I'm new to excel don't know too much and my work is related to inventory. So I was looking what type of analysis i can be easy for beginner. I saw you reply earlier but I was keep reading response to understand the factor which you mention.
Excel does math so what you have to do is to decide what calculations have to be made. For example, you have sales data for various SKUs. By listing the SKU sales by dates, you can see what the trend is. You can then take the average increase or decrease (math) and add or subtract that from the latest sales (math again) to get an estimate of future sales.
Seeing that you warehouse product, you can take advantage of the best bulk prices if the warehousing costs are less than the sales savings (math again). Is your expected demand large enough to qualify and make worth while bulk purchases at off season rates?
Now knowing what you have to calculate, you can layout your Excel worksheet so that you can make the calculations which will mostly be adding (SUM function or just +), subtraction (-), division (/), multiplication (*) and AVERAGE.
The enclosed workbook is just an idea on how to setup in order to make the calculations that you may have to make. It has 1 row per SKU and 1 column per year for various data items. Try and create the worksheet that works for you. If you run into difficulty, post the problem that you are facing and we can try and help you solve it.
can you give one or two example in sheet.
I had made a mistake or two on the file that I uploaded. This should be more clear but remember this is only an idea for what you can do and not a template to be used as is.
The idea behind this is that in using legacy statistics, you can make a more "educated guess" at what will be required for the upcoming time frame. The average takes the legacy increases and decreases and gives an average over time as to what is happening with that item. Seeing that you are warehousing stock, there will likely be stock left over and there will be a cushion of stock for when you are running low and have to order again. You may call this an order point. If the anticipated demand for the item is less than the stock on hand then nothing has to be ordered.
When entering amounts, don't use price amounts, use units as prices rise and fall and will not necessarily give a picture of what actually happened.
Good luck.
Last edited by newdoverman; 12-08-2015 at 10:37 AM.
need a excel to calculate replenishment of packing material on a packaging line which has 10 machines in series. E.g Filler, capper, labaler, case erector, case packer, checkweigher & so on. The speeds of each machine is different( filler- 300 botles/min, capper- 320 bottles/min to cap, labeler -350 bottles/min, case packer -30 cases/min & so on.
Also, there should be a second sheet for forklift movement distance calculation which should represent "from" & "to" distances from packing material stores to each machine & how much time forklift would take 1 pallet to shift from PM store to respective machine based on assumption of speed of 10km/hr.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks