Skip navigation

I need help calculating the "occupancy rate" of a lodge.

741 Views 13 Replies Latest reply: May 9, 2013 10:37 AM by Caleb Kingston RSS
Caleb Kingston Level 1 Level 1 (130 points)
Currently Being Moderated
May 7, 2013 12:48 PM

Hello there. I have a spreadsheet that shows check in, check out times for guests in 5 units: unit 1, 2, 3, 4, & 7 (5 and 6 aren't being rented).

 

I would like to have a chart that automatically displays the occupancy rate for each room as a guest is entered into the table. I'm not great at all with charts in numbers and another difficult part is creating formulars that detects the changes of a new month. So I've drawn a rough draft of what I'd like the chart to look like. Can anyone help me come up with a formula to make the chart display this?

 

Screenshot_5_7_13_1_30_PM.png

  • jaxjason Level 4 Level 4 (3,320 points)

    I would really like to help with this but might need some more info. first if you could clarify for those of us not in the rental or hotel business, how is "occupancy rate" calcuated by hand?

     

    Second, if you could actaully hide the columns in your screen shot instead of blurring them out. Blurring them just seems to makes it harder to see when it shows in the limited zoom window.

     

    Offhand, if % occupancy is as simple as (number of days occupied)/30 then you can have a summary table that has date range versus Cabin number. Use sumifs to add up your column of days per cabin. divide by the date difference between start and end dates. plot that.

     

    Jason

  • Jerrold Green1 Level 7 Level 7 (28,205 points)

    Caleb,

     

    Thanks for making your data available. The first thing I noticed is that you have some data entry errors in the dates. It isn't apparent when you use the short year form, but some of your dates are in the years 0012 and 0013, 2000 years ago. Sorting that out will improve the calculations.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,205 points)

    Caleb,

     

    Here's a simple calculation, not exactly what you wanted, but much easier to achieve.

     

    Screen Shot 2013-05-07 at 5.50.22 pm.png

     

    In the summary table I used a SUMIF function to find the total days booked for each unit, and for the total days available, I subtracted the earliest check-in date from the latest check-out date.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,205 points)

    Caleb,

     

    In my summary table, the expression for Days Occupied is:

     

    =SUMIF(Accepted Bookings :: C, A, Accepted Bookings :: G)

     

    The expression for Rate of Occ is:

     

    =B/$B$8

     

    I'd like to help you with the breakdown by month, but it's not an easy putt. There was an almost identical question here in the past 12-18 months - you can probably find the discussion if you have some luck searching. I recall working on the solution and finding it quite complex. I don't think it's worth the effort in your case considering how sparse the data is, but that's your decision. You will be using SUMIFS (note the extra "S") to further constrain the summations to a particular month. But, the main issue is the problamatic reservation that spans the end of month; difficult, as you have speculated.

     

    Good luck,

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)

    Hi Caleb,

     

    If I'm reading your table correctly, the unit numbers are i column C, In date in column E, Out date in column F and duration of stay in column G.

     

    For stays that do not cross from one month to another, a simple SUMIFS statement woud work to calculate the number of occupied days in a given month. As you've found, theough, the stays that begn in one month and end in another are a bit trickier.

     

    One way to resolve the issue is to use an auxiliary table which calculates the occuped days (for all units) using a separate column for each month, then a summary table to sum the monthly data for each unit.

    Here's an example, using random data over a two and a half month span.

    Screen Shot 2013-05-07 at 6.49.55 PM.png

    Note that the dates in the header row of Aux and Summary are actual Date and Time Values set to the first of each month, and formatted to show only the month and year. These are used in the calculations.

    The only formula in Bookings is =F-E in each cell of column G (Stay).

     

    Aux has two formulas:

     

    A2, and filled down: =Bookings :: C2

    This simpy copies the values from column C of Bookings to column A of Aux.

     

    B2, and filled down and right:

    =IF(AND(Bookings :: $E2>=B$1,Bookings :: $F2<EOMONTH(B$1,0)+1),Bookings :: $F2-Bookings :: $E2,IF(AND(Bookings :: $E2<B$1,Bookings :: $F2>B$1),Bookings :: $F2-B$1,IF(AND(Bookings :: $E2<=EOMONTH(B$1,0),Bookings :: $F2>EOMONTH(B$1,0)),EOMONTH(B$1,0)+1-Bookings :: $E2,"no")))

     

    Same formula, separated into sections:

    =

    IF(AND(Bookings :: $E2>=B$1,Bookings :: $F2<EOMONTH(B$1,0)+1),Bookings :: $F2-Bookings :: $E2,

     

    IF checks if the IN date AND the OUT Date are both in the month for this column. If so, the calculation is done and the formula is done; if not, control is passed to the next if:

     

    IF(AND(Bookings :: $E2<B$1,Bookings :: $F2>B$1),Bookings :: $F2-B$1,

     

    IF checks it the IN date is before the month for this column AND the OUT date is after the beginning of this month. If so,th calculaton is done and the formula is done; if not, contorl is passed to the thrid IF:

     

    IF(AND(Bookings :: $E2<=EOMONTH(B$1,0),Bookings :: $F2>EOMONTH(B$1,0)),EOMONTH(B$1,0)+1-Bookings :: $E2,

     

    IF checks if the IN Date is before the end of the month for this coumn AND the OUT date is after the end of the month. If so, the calculation is done; if not, the text blow is returned to the cell.

     

    "no")))

     

    This text was chosen to give me a visual indication of the result. In practice, I would replace it with the empty string ( "" ).

     

    Summary has a single formula:

     

    B2, and filled down and right:

    =IFERROR(DUR2DAYS(SUMIF(Aux :: $A,$A2,Aux :: B))/DAY(EOMONTH(B$1,0)),"")

     

    SUMIF sums the duatons of Stay for each unit in a specific month for each column.

     

    DUR2DAYS converts the duration to a number of days.

     

    DAY(EOMONTH(B$1,0) returns the number of days in the month whose date is in row 1.

     

    The first is divided by the second, and the result is formatted as percentage, with 0 decimal places.

     

    IFERROR traps the error that occurs when the source column on Aux contain only text values, and replaces the error message with the empty string.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    Hi Caleb,

     

    Cell references without a table name attached refer to cells on the same table as the formula. B1, on the Summary table, contains the Date and Time value January 1, 2013 00:00:00, formatted to show only the (short form) month and year values, joined by a dash.

     

    The $ operator holds that reference to row 1 as the formula is filled down the column. The lack of the $ operator before the B permits that column reference to change as the formula is filled to the right.

     

    Regards,

    Barry

     

    PS: If you haven't yet done so, i'd strongly recommend downloding and reading at least the first four chapters of the Numbers '09 User Guide to learn some of the basics of working with Numbers. You'll find a link to the download in the Help menu in Numbers '09.

     

    Also available in the same place is the iWork Formulas and Functions User Guide, in which you'll find descriptions and usage examples for all the functions available in Numbers.

     

    B

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.