+ Reply to Thread
Results 1 to 8 of 8

Predictive Stock

  1. #1
    Registered User
    Join Date
    05-07-2011
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Predictive Stock

    New to the forum so hi everyone.

    Im trying to put together a predictive stock usage spreadsheet as per attached, but cant help thinking this isnt the best way of doing it.

    Just to give you an idea, I have the days of the month across the top and then the following

    Row 3 is simply the days of the month
    Row 4 are averaged sales based on the actual sales.
    Row 5 are the actual sales on that day of the month.
    Row 7 again are days of the month
    Row 8 are actual physical stocks
    Row 10 are days of the month
    Row 11 are predicted stock levels based on predicted sales from row 4.

    Am I missing a trick somewhere along the line and there is a far easier way of doing this?

    I realise I havent accounted for stocks in yet, but thats simple enough. Im just wondering if there is a better way excel can handle forecasts or predictive sales etc?

    Many thanks in advance for anyone who offers any help and advice on this.

    Pickle.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Predictive Stock

    Try the attachment.
    Attached Files Attached Files
    Martin

  3. #3
    Registered User
    Join Date
    05-05-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Predictive Stock

    Hi pickle,

    See suggestion attached. In my point of view, the most important part is to structure the stock transactions you will have, then once that is done you can work on the reports the way you need. In the example attached on sheet 'Transactions' you have a table where you can enter all transactions:
    (+) for adding items to the stock
    (-) for selling stocked items
    As you have Excel 2007 please notice I have define the data on this sheet as a table. That is a very good way to keep data well structured (like for example, whenever you enter a formula on the first line, that formula will be repeated across the column even for a new record added).

    Then, on sheet 'Report' you will have the report the way you asked. In case you want to see it for another product you can enter its name on cell Report!A2 (provided you have the transactions for that product captured on sheet 'Transactions'). On cell Report!A3 you will see the initial stock (in this case the initial stock will be the sum of all transactions before the month on cell Report!C1).

    In case you want to see the sales predictons for more months just select the last column of sheet 'Report' (rows 1 to 5) and drag outside the selection to extende the formulas. The only columns you can't delete are from A to D.

    I hope that will address your question.

    Good luck
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-07-2011
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Predictive Stock

    Thanks for the suggestions and the time spent helping me. I will have a play around with them and will certainly post any setup I derive from the sheets / advice offered.

    If anyone else would like to comment in the meantime, please do so.

    Thanks again.

    Pickle.

  5. #5
    Registered User
    Join Date
    05-05-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Predictive Stock

    Mate, please find attached an improved version where it will allow you to chose the product (unique names) on cell Report!A2. Please notice that I had to create a pivot table (sheet 'Summary') and define a name based on the list of products on it.

    I hope that will help.

    Cheers,
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-07-2011
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Predictive Stock

    Hi Inesi,

    Thanks for all the work you have done on this. Im really pretty poor with excel so I have to admit to struggling to understand a lot of what you have done ( your a genius imo )

    If you look at the attached sheet, I have kind of replicated things in a veeeeery simplistic way which I understand. Now while our Sales, Average Sales, Stock all matches, our estimated figures vary a little and I cant figure out why.

    I have changed to days, because some of the lines move quickly through the month and possibly need to be ordered on two or three occasions through the month, so I would probably do a separate tab for each month and then just copy paste for all the products and put my starting stock. That way I could start afresh each month.

    Why does it vary slightly though? If I can match your system the way I have done it, at least I will be able to explain how it works to people.

    Pickle.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-05-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Predictive Stock

    Hi Pickle, no worries mate, I am more than glad to help and you are lucky because my wife is travelling, otherwise no chance of working on that on a Saturday night

    So, let's get cracking: Open the file attached and let's go through it.

    The difference between the stock forecast is because we both used diferent formulas. In your formula you are doing:
    Current_Stock_Forecast = Stock_Forecast_Previous_Month - Current_Average_Stock

    In my formula:
    Current_Stock_Forecast = Current_Stock - Sum_All_Average_Stock

    If you formula is the right one, then select D12:AH12, copy and paste special on cells D6:AH6 selecting the option Formulas.

    Regarding the formulas used, don't worry, it looks scary but let me try to explain:

    - SUMIFS: That is a very cool Excel 2007 function. What is does is sum values on a given vector/list of values based on as much conditions as you want.
    >> So, for the 'Actual Sales' what I am doing is the sum of all values on Column C (of sheet 'Transactions') only if the Column A (of sheet 'Transactions') = Report!A3 (in this case 'Milkshake') AND Column C (of sheet 'Transactions') is lower than zero AND Column E (of sheet 'Transactions') = Report!B1 (in this case 2011.01).
    >> The same I do for 'Stock', the only diference is that I only sum the positive values of Column C (of sheet 'Transactions')

    - OFFSET: That is another cool function. What is does is to create a vector/list of values. For example, on the 'Average Sales' you are doing the average of D9 on column D, then the average of D9:E9 on column E, then the average of D9:F9 on column F (and so on...). The formula OFFSET is there to do exactly that but without the need to manually edit each cell. The parameters of this formula are (in sequence):
    >> Reference: Cell where you want to start
    >> Rows: How many rows to offset after the reference
    >> Cols: How many columnss to offset after the reference
    >> Height: How many rows to consider in your list
    >> Width: How many columns to consider in your list
    So, for example:
    - OFFSET($D$3,0,0,1,5) = D3:H3
    - OFFSET($D$3,0,0,5,1) = D3:D7
    - OFFSET($D$3,0,0,5,5) = D3:H7

    What you will also notice is that I have used the formula COLUMN. That one is just to get the number of the Excel column so I can make the trick on the OFFSET. For example:
    - OFFSET($D$3,0,0,1,COLUMN(H$2)-3) = OFFSET($D$3,0,0,1,8-3) = D3:H3

    I hope that is clear, otherwise let me know.

    Cheers
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-07-2011
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Predictive Stock

    Thanks for the explanation, im going to continue to have a fiddle around with it all over the weekend. I feel confident I can put something together of use now.

    Really appreciate the help you have offered. Make sure you have a great weekend.

    Pickle.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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