Short Term Rental daily price calculator

[ffoott]

Hello,

I've been trying to create an Excel calculator that would allow me to easily give a quote to a potencial guest. My Excel knowledge is pretty limited, but I did manage to get it to work in calculating a value given a specific set of conditions, like:

• Is it a weekend?
• Is it low, intermediate or high season?
• How many guests are there?
• How many days will they stay?
• Are there any coupons or promotions to consider?

This is the formula so far (not sure it this helps):

=(AG5IF(AK5="Baixa (Novembro-Abril)"; AQ11; 1)IF(AK5="Alta (Junho-Setembro)"; AQ12; 1)IF(AK5="Intermédia (Maio e Outubro)"; AQ13; 1)+IF(AI5="Yes"; AQ19AJ5AN5; "0")+IF(AH5="Yes"; AQ18; "0")+IF(AJ5>2; 10AJ5-20; 0))IF(AM5="Emigrante (7,5%)"; AQ16; 1)IF(AM5="Portugal (10%)"; AQ15; 1)IF(AM5="Açores (12,5%)"; AQ17; 1)IF(AM5="Cliente (15%)"; AQ14; 1)*AN5

With this I am able to successfully calculate my quote, the exception being:

• If the dates our guest needs a quote for include both weekdays and weekends.
• If the dates our guest needs a quote for include dates that are associated with two different price seasons.

Still, what I really am striving to achieve is a formula or setup that, given the input of a check in and check out date is able to figure out:

• How many weekend days that date interval has and attribute these days an extra value.

1. What price season these dates fall into and attribute a specific multiplier to them. If they fall on multiple price seasons, be able to sort that and attribute them distinct multipliers.

I imagine that a table listing date intervals, their assigned price season and their specific multipliers is part of the answer and have created one - I just don't know how to to plug it in.


Thank you very much!!

 

[Toadstool]

Hi Ffoott,

There's many ways of approaching this, and each raises many questions, but let me ask one question up front: Many hotels have a limit of 14 days duration per booking, do you have the same?

The reason for the question is that the simplest approach would be to present each day of the booking as a row on the sheet with columns showing which discounts/uplifts have been applied to get that daily rate. This would allow you to easily see the weekend/season/Holiday?/coupon discounts and uplifts.

 

[ffoott]

That you for your reply.

In my particular case, the max number of days is 29.

As for your suggestion, although that is a viable option, I would prefer to have it do these calculations automatically, based on the dates given to it. In my head I feel such a calculation is possible, but don't really know to to go about it in Excel.

This would be optimal because, the rest of the variables, that dotn depends on the dates, don't really change from day to day - they are either yes or no throughout the booking duration, like breakfast, number of guests, discount, etc.

If I were to implement the option you mentioned, in a 29 day booking I'd have to manually adjust all of that, line per line...

 

[ffoott]

With further digging and an answer in another forum I was able to finish my project - thank you just the same!

 

[Toadstool]

Oh well! I just finished putting an example together so I'm going to post that anyway...

Personally I would calculate all discounts/uplifts for every day in a calendar and then on the Pricing tab allow specification of dates and factors impacting price then list each day and the factors which made up the price.

e.g.

ffoott.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Price It
2
3		Number of Guests	Checkin Date	Checkout Date	Coupon				Nights	Total
4		6	30-May-19	06-Jun-19	SIXOFF				7	$ 935.00
5
6		Dates	Day	Saturday	Sunday	Holiday	Number of Guests	Season	Coupon	Daily Rate
7		30-May-19	Thu				-12.50%	15.00%	-6.00%	$ 96.50
8		31-May-19	Fri				-12.50%	15.00%	-6.00%	$ 96.50
9		01-Jun-19	Sat	12.00%		20.00%	-12.50%	25.00%		$ 144.50
10		02-Jun-19	Sun		15.00%	20.00%	-12.50%	25.00%		$ 147.50
11		03-Jun-19	Mon				-12.50%	25.00%		$ 112.50
12		04-Jun-19	Tue				-12.50%	25.00%		$ 112.50
13		05-Jun-19	Wed				-12.50%	25.00%		$ 112.50
14		06-Jun-19	Thu				-12.50%	25.00%		$ 112.50
15
PriceIt

Cell Formulas
Range	Formula
I4	I4	=D4-C4
J4	J4	=SUM(J7:J37)
B7	B7	=IF(ROWS($B7:$B$7)>$I$4+1,"",INDEX(Calendar!A$2:A$5000,MATCH($C$4+ROWS($B7:$B$7)-1,Calendar!$A$2:$A$5000,0)))
C7:I15	C7	=IF($B7="","",INDEX(Calendar!B$2:B$5000,MATCH($B7,Calendar!$A$2:$A$5000,0)))
J7:J15	J7	=IF($B7="","",(SUM(D7:I7)*Calendar!$J$2)+Calendar!$J$2)
B8:B15	B8	=IF(ROWS($B$7:$B8)>$I$4+1,"",INDEX(Calendar!A$2:A$5000,MATCH($C$4+ROWS($B$7:$B8)-1,Calendar!$A$2:$A$5000,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4	Expression	=OR($D$4<$C$4,$D$4>$C$4+29)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D4	List	=INDIRECT(Calendar!$AB$1)

...and the Calendar tab has every date and the data needed to calculate the per day price:

ffoott.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Dates	Day	Saturday	Sunday	Holiday	Number of Guests	Season	Coupon		Base		Holidays	Holidate		No. of Guests	Discount		Season	Start Date	End Date	Seasonal Uplift		Coupon	Discount	Coupon Ends		Data List	Calendar!$A$152:$A$181
2	01-Jan-19	Tue			20.00%	-12.50%		-6.00%		$100		New Year	01-Jan-19		1	0.00%		Low	01-Jan-19	30-Apr-19			SIXOFF	-6%	31-May-19
3	02-Jan-19	Wed				-12.50%		-6.00%		Saturday uplift		New Year	01-Jan-20		5	-12.50%		Low	01-Nov-19	31-Mar-20			TENLESS	-10%	31-May-20
4	03-Jan-19	Thu				-12.50%		-6.00%		12%		New Year	01-Jan-21		10	-15.00%		Intermediate	01-May-19	31-May-19	15.00%
5	04-Jan-19	Fri				-12.50%		-6.00%		Sunday uplift		New Year	01-Jan-22		20	-16.50%		Intermediate	01-Oct-19	31-Oct-19	15.00%
6	05-Jan-19	Sat	12.00%			-12.50%		-6.00%		15%		New Year	01-Jan-23		999	-20.00%		High	01-Jun-19	30-Sep-19	25.00%
7	06-Jan-19	Sun		15.00%		-12.50%		-6.00%		Holiday uplift		New Year	01-Jan-24					Low	01-Jan-20	30-Apr-20
8	07-Jan-19	Mon				-12.50%		-6.00%		20%		Feast of Toadstool	01-Jun-19					Low	01-Nov-20	31-Mar-21
9	08-Jan-19	Tue				-12.50%		-6.00%				Feast of Toadstool	02-Jun-19					Intermediate	01-May-20	31-May-20	15.00%
10	09-Jan-19	Wed				-12.50%		-6.00%				Feast of Toadstool	01-Jun-20					Intermediate	01-Oct-20	31-Oct-20	15.00%
11	10-Jan-19	Thu				-12.50%		-6.00%				Feast of Toadstool	02-Jun-20					High	01-Jun-20	30-Sep-20	25.00%
12	11-Jan-19	Fri				-12.50%		-6.00%				Feast of Toadstool	01-Jun-21					Low	01-Jan-21	30-Apr-21
13	12-Jan-19	Sat	12.00%			-12.50%		-6.00%				Feast of Toadstool	02-Jun-21					Low	01-Nov-21	31-Mar-22
14	13-Jan-19	Sun		15.00%		-12.50%		-6.00%										Intermediate	01-May-21	31-May-21	15.00%
15	14-Jan-19	Mon				-12.50%		-6.00%										Intermediate	01-Oct-21	31-Oct-21	15.00%
16	15-Jan-19	Tue				-12.50%		-6.00%										High	01-Jun-21	30-Sep-21	25.00%
17	16-Jan-19	Wed				-12.50%		-6.00%										Low	01-Jan-22	30-Apr-22
18	17-Jan-19	Thu				-12.50%		-6.00%										Low	01-Nov-22	31-Mar-23
19	18-Jan-19	Fri				-12.50%		-6.00%										Intermediate	01-May-22	31-May-22	15.00%
20	19-Jan-19	Sat	12.00%			-12.50%		-6.00%										Intermediate	01-Oct-22	31-Oct-22	15.00%
21	20-Jan-19	Sun		15.00%		-12.50%		-6.00%										High	01-Jun-22	30-Sep-22	25.00%
Calendar

Cell Formulas
Range	Formula
AB1	AB1	="Calendar!"&ADDRESS(MATCH(PriceIt!$C$4,Calendar!$A$2:$A$5000,0)+2,COLUMN(Calendar!$A:$A))&":"&ADDRESS(MATCH(PriceIt!$C$4,Calendar!$A$2:$A$5000,0)+31,COLUMN(Calendar!$A:$A))
B2:B21	B2	=TEXT(A2,"ddd")
C2:C21	C2	=IF($B2="Sat",$J$4,"")
D2:D21	D2	=IF($B2="Sun",$J$6,"")
E2:E21	E2	=IF(ISNA(MATCH($A2,$M$2:$M$997,0)),"",$J$8)
F2:F21	F2	=IF(INDEX($P$2:$P$11,MATCH(PriceIt!$B$4,$O$2:$O$11,1))=0,"",INDEX($P$2:$P$11,MATCH(PriceIt!$B$4,$O$2:$O$11,1)))
G2:G21	G2	=IF(INDEX($U$2:$U$200,AGGREGATE(15,6,ROW($U$2:$U$200)-1/((A2>=$S$2:$S$200)*(A2<=$T$2:$T$200)),1))=0,"",INDEX($U$2:$U$200,AGGREGATE(15,6,ROW($U$2:$U$200)-1/((A2>=$S$2:$S$200)*(A2<=$T$2:$T$200)),1)))
H2:H21	H2	=IFERROR(IF(INDEX($Y$2:$Y$200,MATCH(PriceIt!$E$4,$W$2:$W$200,0))<A2,"",INDEX($X$2:$X$200,MATCH(PriceIt!$E$4,$W$2:$W$200,0))),"")
A3:A21	A3	=A2+1
T2,T21,T16:T17,T11:T12,T6:T7	T2	=EOMONTH(S2,3)
T3,T18,T13,T8	T3	=EOMONTH(S3,4)
T19:T20,T14:T15,T9:T10,T4:T5	T4	=EOMONTH(S4,0)

 

[ffoott]

Wow...I feel really bad that you went through all that work :/

I promise I'll give it a look to compare with my method and probably learn something from it.

How would I go about sharing my excel file with you so you could could give an opinion on the method I used?

 

[Toadstool]

If you want to share it on Dropbox or Google Drive I'd be happy to take a look.

 

[ffoott]

Dropbox - File Deleted - Simplify your life

www.dropbox.com