Need help forecasting

Copper Contributor

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?

5 Replies
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...

@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 

@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

mathetes_0-1617466436026.png

 

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.

mathetes_1-1617466658439.png

 

 

Thank you very much for all of this bu i am not underdanding how you did forecasting

@kaybyers 

 

I'm not understanding what you're not understanding. Your original post asked, and I quote:

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?

 

I didn't specifically do "forecasting" at all; never intended to. What I gave you from the, admittedly, limited set of data shows the pattern of previous activity (to the extent there is one), by day of the week, by month. Isn't that what you wanted? You're the one who then will base your forecasting on that.

 

If you're saying you don't understand how to do a Pivot Table based on data, I suggested some research on YouTube or Google. Here's a specific link that should be helpful. https://exceljet.net/search?query=pivot+table

 

And YouTube has many instructional videos on the topic.

 

If there's something else that you're seeking beyond these, then please spell it out a bit more. Forecasting itself is inherently quite subjective; and I think that is especially true in a case such as yours.  But seeing the pattern in the previous activity is a good starting point.