Forum Discussion

kaybyers's avatar
kaybyers
Copper Contributor
Apr 02, 2021

Need help forecasting

I am trying to determine based on when customers have all scheduled dates previously, what days or dates they are going to most likely be scheduling in the future is there a way to do this?
excel
office 365
mathetes's avatar
mathetes
Gold Contributor
Apr 02, 2021
I don't see anything in that spreadsheet that identifies individual customers. Not asking for names, but if there are in the original some identifications, then maybe you could replace the real names consistently with names of Disney characters --or Star Wars, your pick -- and tell us a bit more of what the relevant data are...is it "Sched Date"? And presumably you want to be able to identify the day of the week that is most commonly used by a given customer, IF, that is, there IS a pattern. As a customer (currently) purchasing some kitchen appliances, I have a hard time thinking there'd be any meaningful pattern from my once-every-ten-years purchase pattern; or is this service?

In other words, I'm wondering if you're not tilting at windmills here...
  • kaybyers's avatar
    kaybyers
    Copper Contributor
    Apr 02, 2021

    mathetes ok i am adding the order #'s for the customers to show as a way of identifying customers without giving out any personal info... does this work? also, the rlevant data would probably be from 2 different sources: the order placed date: i need to be able to tell about what dyays of the week or month or times of the year and the volume that could possibly happen in the future. then by the sched. date to show the same as order place date. does that make sense? this is for a service we provide. we pickup old unwanted appliances no longer needed ... all across the country. so this sheet i am sharing with you is not even 1 % of the amount of volume we actually handle in this time frame this is only an example so i may learn how to do this going forward. If you can help me out please let me know it is so appreciated and i would be forever grateful to you for it 

    • mathetes's avatar
      mathetes
      Gold Contributor
      Apr 03, 2021

      kaybyers 

       

      Let's try this. I first added some columns that get the month of order/sched as well as the day of the week. Those are done by formulas, which you can read in the spreadsheet attached. It turns out that you don't need customers to be identified in any manner. I was assuming from your original post that you wanted to know the pattern of individual customers; your second made clear that you had interest only in the collective behavior of ALL clients/customers

       

      Then, based on that at the rest of the database, I created a Pivot Table that can be used to either get number of orders (as it's set up now) or scheds, by appliance or collectively, by month and day of week.

      Here's an example, without differentiating which appliance. You can change what is listed next to "Appliance Type," there at the top, to see more specific listings. So, for example, you'd expect more A/C units in the summer months (but your data doesn't include those months).

       

      I think that the Pivot Table is probably the most useful tool in the Excel tool box for your application. If you're not aware of it--considered one of the most useful and widely used of all analytical tools--then do some research on Google and YouTube, both for what it does and how it works. I think you'll find it very useful.

       

       

      • kaybyers's avatar
        kaybyers
        Copper Contributor
        Apr 05, 2021
        Thank you very much for all of this bu i am not underdanding how you did forecasting

Resources