Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sales forecasting

    I am trying to build a sales forecaster that will fill in a chart based on user input. My sheet allows the user to enter a receipt number, day of the week that the receipt was obtained and the time of day. Those are the variables.

    Hidden away on the sheet is a chart based on factual averages that shows what percentage of the weekly receipts are acquired during the various dayparts. The chart has 35 entries, or five per day. This information is set in stone and not variable. As an example, we know that this business will do 1.97% of their total receipts before 10:00 AM on Monday, another 2.27% between 10:00 AM and 2:00 PM, etc.

    We know that the first ticket (receipt) every day begins with #1. I'm okay with figuring sales ($). What I can't seem to do, is compose a formula that will feed the user input into the Results chart.

    If the users says that he received receipt number 450 at 2PM on a Wednesday, is that enough information to complete the Results chart? We know that 2.08% of all weekly tickets are received by 10Am on Wednsday, while 2.35% of all weekly tickets are received from 10Am to 2PM. So, 450 receipts (tickets) by 2PM would represent 4.43% of the weekly total (2.08% + 2.35%).

    I would prefer to make this work using formulas and not VBA. I know my explanation is as clear as mud, so I have attached a sample sheet which may help.

    Thanks,
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you copy d13:d17 to o24:028 so that C7 will get times instead of text, you can put the formula in E13:
    Code:
    =IF(AND(E$12=$C$5,$D13<=$C$7),ROUND($C$3/SUMIF($D$13:$D$17,"<="&$C$7,INDEX($E$21:$K$25,0,MATCH($C$5,$E$12:$K$12,0)))*E21,0),"")
    and copy it to e13:k17. That will put numbers in the day column for the times and null strings in the rest.

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    The Hedgehog (2014-05-07)

  4. #3
    New Lounger
    Join Date
    May 2014
    Posts
    2
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Steve, Nothing short of amazing. Works perfectly. Thank you so much.

    Ricky

Posting Permissions

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