probability of analisys based on historical data

[carwiz]

I rent vacation homes to tourists. My Excel spreadsheet lists arrival and departure dates for each booking going back several years. I’m interested a calculating a probability of the occupancy on each date. The information will be used to optimize daily rental rates.

[MrShorty]

It seems like the simplest measure of "probability of occupancy" would be the fraction occupied in the current data set. Pseudocode =count(years occupied for a given date)/total number of years the data covers.

[carwiz]

It seems like the simplest measure of "probability of occupancy" would be the fraction occupied in the current data set. Pseudocode =count(years occupied for a given date)/total number of years the data covers.

I may not been clear in my post. I need to determine occupancy per each day based on several years of data. It may take several steps to accomplish. I was thinking of doing this:
1. since I only have arrival and departure dates a macro will be needed to fill the rest of the dates in-between.
2. create a pivot table to display a number depending on occupancy. For example, I should see that a given day was occupied 3 times in 7 years. I can make a decision to adjust pricing based on that data.
3. Macro to loop to next row

Unless someone knows a simpler way, I could use help in creating that code.

[gjrr4x1]

Why don't you copy paste all the years into one Sheet, such that the days are the rows (E.g. 1 Jan, 2 Jan, 3 Jan.....) and the years the columns (2005, 2006, 2007....), then give a 1 to occupied days and a 0 to non-occupied days and then sum the rows (E.g. B2 to B8) and divide by 7 (the number of years). To the cell summing up (Say B9) you can format the cells such that if occupancy is for example below 0,40 then it's coloured red, if it's between 0,4 and 0,7 let's say yellow and >0,7 green.

Finally, you could create a table in which the days in which the days in red appear or order the days of the year from lowest occupancy to highest occupancy. This way in an eye glimpse you can tell in which days it may be convenient to adjust the price (i.e. the first of the list or the red ones).



It's a simple way of doing it. I'm sure there's a cleverer way of doing it though.

[carwiz]

Thank you for your help. Your explanation is simple and clear.

[MrShorty]

1. since I only have arrival and departure dates a macro will be needed to fill the rest of the dates in-between.

I don't know that a macro is necessary. It depends on how you are most comfortable programming. Some people seem more comfortable with symbolic languages (like VB) and only use the spreadsheet as an input/output device (like some people use a text file). Others are more comfortable with spreadsheet formulas and prefer to use the spreadsheet as a spreadsheet. It will be up to you to decide if you prefer VB or if you prefer spreadsheet formulas.

As gjrr4x1 points out, a lot of the details in this will depend on how the raw data is laid out, and how much control you have over that. My first thought was similar to gjrr4x1's solution. In this scenario, you end up doing a lot of what you are asking the in part 1 in your head as you enter the data into the spreadsheet. Depending on programming skill and how often this needs to be done, it might be easier and more efficient to do some of the work in your head like this.

Since you haven't given an example of how your raw data is laid out, it is hard to be specific. Here's one approach I envisioned using only spreadsheet formulas.

[carwiz]

Great explanation and the sample worksheet. This is what I needed. You had practically done all the work for me. Thank you for your advice and generosity.