+ Reply to Thread
Results 1 to 7 of 7

How to do Demand Planning & stock forecast ?

  1. #1
    Registered User
    Join Date
    03-18-2015
    Location
    uae
    MS-Off Ver
    2013
    Posts
    21

    Post How to do Demand Planning & stock forecast ?

    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 ?

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to do Demand Planning & stock forecast ?

    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

  3. #3
    Registered User
    Join Date
    03-18-2015
    Location
    uae
    MS-Off Ver
    2013
    Posts
    21

    Re: How to do Demand Planning & stock forecast ?

    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.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to do Demand Planning & stock forecast ?

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-18-2015
    Location
    uae
    MS-Off Ver
    2013
    Posts
    21

    Re: How to do Demand Planning & stock forecast ?

    can you give one or two example in sheet.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to do Demand Planning & stock forecast ?

    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.
    Attached Files Attached Files
    Last edited by newdoverman; 12-08-2015 at 10:37 AM.

  7. #7
    Registered User
    Join Date
    12-15-2013
    Location
    Vadodara,Gujarat,INDIA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to do Demand Planning & stock forecast ?

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. rolling order forecast - order planning excel combination of formulas
    By confused44 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 02:01 AM
  2. Replies: 1
    Last Post: 03-22-2014, 08:10 AM
  3. Help with cell formula - Demand planning
    By Squash1966 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2014, 04:25 PM
  4. Help with cell formula - Demand planning
    By Squash1966 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2014, 04:20 PM
  5. Automating and sorting a stock demand spreadsheet
    By jasw529 in forum Excel General
    Replies: 4
    Last Post: 03-28-2013, 08:50 AM
  6. Help with Forecast demand model
    By jame24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2011, 01:33 PM
  7. Fomula to calculate supply vs Demand - Stock
    By Achard in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-17-2008, 02:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1