+ Reply to Thread
Results 1 to 4 of 4

Forecasting Delivery Variances

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Forecasting Delivery Variances

    I need to show actual variances in delivery volumes as a forecast for the next available delivery date. So if a store has a delivery on Monday and Thursday and on Monday they are -100 cases I need to show +100 cases on Thursday.

    This is made more complicated as stores have different delivery dates and no. of deliveries per week. I have attached a spreadsheet with more information to help - if anyone can help that would be appreciated.

    Thanks,
    Sahil
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,342

    Re: Forecasting Delivery Variances

    Can you show a before and after picture of the same data, so we can see the progression. What does the raw data information look like?

  3. #3
    Registered User
    Join Date
    11-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Re: Forecasting Delivery Variances

    I have added a new spreadsheet showing how the data needs to change from day to day. Each day we get real data which needs to replace the forecast previously assigned to that day (if there was a forecast).

    The raw data will have two columns showing the list of stores and the delivery variance for that day only, no historical data will be added.

    Thanks,
    Sahil
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,490

    Re: Forecasting Delivery Variances

    As modeled on the Thursday sheet the delivery variances are displayed using the following:
    1. Dates for the upcoming days are displayed in row 8 using: =AGGREGATE(15,6,TODAY()+{0,1,2,3,4,5,6}/(TEXT(TODAY()+{0,1,2,3,4,5,6},"ddd")=C2),1)
    Note that this row may be moved and/or hidden for aesthetic purposes.
    2. The next scheduled delivery date for each store is displayed in column A using: =AGGREGATE(15,6,C$8:I$8/(C3:I3=1)/(C$8:I$8>=TODAY()),1)
    3. The forecast variance amounts are displayed in the next delivery date using: =IF(O$2=$A3,-INDEX($K3:N3,AGGREGATE(14,6,(COLUMN($K3:N3)-COLUMN($J3))/($K3:N3<>"")/($K2:N2< TODAY()),1)),"")
    As you manually input the values the formula will be erased in those cells.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Return Delivery Date or state 'No Delivery'
    By jimbokeep in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2019, 11:51 AM
  2. Replies: 1
    Last Post: 12-09-2017, 08:45 PM
  3. Replies: 2
    Last Post: 06-05-2013, 12:14 PM
  4. variances in two worksheets with ado and vba
    By pike in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-09-2011, 07:44 AM
  5. Find variances
    By naomi61 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-21-2010, 10:46 PM
  6. Calculate variances
    By Climaxgp in forum Excel General
    Replies: 11
    Last Post: 05-19-2009, 02:15 AM
  7. Delivery note or delivery order sheet
    By Roy Istanbouli in forum Excel General
    Replies: 1
    Last Post: 01-21-2005, 02:06 PM

Tags for this Thread

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