Need to calculalte occupancy nights, gross rental, and average daily rate in Excel spreadsheet

Please see the attached spreadsheet.  I need to create a spreadsheet which calculates nights of occupancy, total gross receipts and average daily rate from a table of check in/check out dates and gross rents.  The problem I face is that many of the dates cross months and I would like to break this data out by month.
Rental-Data.xlsx
tommcafeeAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
In column P, use this formula:

=N3/(EOMONTH(DATE(YEAR(TODAY()),M3,1),0)-DATE(YEAR(TODAY()),M3,1)+1)

Format as percentage.
0
 
Rob HensonFinance AnalystCommented:
Are you OK with some additional helper columns?

Would there be occasions where the duration crosses more than 2 months, eg check in 29 July and Check out 2 September?
0
 
tommcafeeAuthor Commented:
Rob,

Absolutely fine with helper columns.  Shouldn't have a case of crossing more than 2 months.

Thanks!
Tom
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Rob HensonFinance AnalystCommented:
See attached.

Haven't been able to do anything with % Occupancy as there is nothing to base it on; Occupancy as % of what?
Rental-Data.xlsx
0
 
tommcafeeAuthor Commented:
You're on the right track calculating nights and ADR.

% Occupancy = number of occupied nights / total calendar days per month.
0
 
tommcafeeAuthor Commented:
Perfect!  Thank you for working through this.  I couldn't figure out how to split the months.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.