Managing advanced payments for your property

• Many hoteliers charge deposits. So, they may need to generate a list of advanced payments charged at any point in time to verify their reporting. Remember that advanced payments would cause the difference between Revenue and Payments in the Daily Revenue report (which can result in big Grand Total and Ledger numbers).
• This article explains how to use the Payment Ledger Report to generate a list of advanced payments posted on a particular day or during a particular period of time.

The Payment Ledger Report is an ideal tool for tracking advanced payments, whether for future or past reservations:

1. Checking Deposits for a Specific Day:

• Generate a list of payments posted on that day.
• Include only the payments from guests arriving the following day or later.

2. Checking Deposits Over a Period:

• Generate the Payment Ledger Report for the desired timeframe.
• Export the data to a spreadsheet.
• Filter out any non-advanced payments to get a clear view of deposits.

Following these steps, you can easily manage and verify advanced payments, ensuring that your financial reporting remains accurate.

1. Generating a list of advanced payments posted on a particular day

Access the Payment Ledger report and apply the following filters:

1. Payment Type: select all
2. Select the desired Payment Date (e.g. the 1st of November)
3. No Associated Transactions should be selected under the drop-down menu
4. Select Check-in Date range: it should be from the second day from the first payment date till any day far in the future (e.g. it should be from the 2nd of November until some date 2 years in the future)
5. Type: select reservations only
6. Apply the filter
7. Export the report as an Excel file (if needed).

You will end up with a list of payment transactions posted on the selected day (the 1st of November) for the reservations that are checking in the future. The report is ready and you can look up the final number.

2. Generating a list of advanced payments posted over a period of time

If you are interested in a payment date range (e.g. the whole month of November), you need to generate a Payment Ledger report in a similar way, export the file and continue working on the spreadsheet. Access the Payment Ledger report and apply the following filters:

1. Payment Type: select all
2. Select the desired Payment Date range (e.g. the whole month of November)
3. No Associated Transactions should be selected under the drop-down menu
4. Select Check-in Date range: it should be from the second day from the first payment date till any day far in the future (e.g. it should be from the 2nd of November till somewhere in 2 years)
5. Type: select reservations only
6. Apply the filter
7. Export the report as an Excel file.

Refining the Data in Google Sheets

Once you’ve exported the report, follow these steps to identify and remove non-advanced payments:

1. Open the Excel file in Google Sheets.
2. Freeze the first row for easy navigation.
   • Go to View > Freeze > 1 row.
3. Format the date columns for easier comparison.
   • Select columns B (Date/Time), I (Check-in Date), and J (Check-out Date).
   • Go to Format > Number > Date.
4. Add a new column (Column P) at the end of the sheet.
5. Apply the "IF formula" to mark non-advanced transactions.
   • In P1, enter the following formula:
     =ARRAYFORMULA(IF(I:I<=B:B,"N/A","ADVANCED")).
   • This formula marks transactions where the check-in date is the same as or earlier than the payment date as "N/A" (non-advanced) and everything else as "Advanced."

Explanation of the Formula:

• ARRAYFORMULA: Ensures the formula is applied to the entire column.
• I:I<=B:B: Compares the check-in date to the payment date.
• "N/A","ADVANCED": Labels transactions as "N/A" (non-advanced) or "Advanced." If desired, you can customize these labels, but they must be in quotation marks.

Filtering and Removing Non-Advanced Transactions:

6. Apply a filter to the new column (Column P). Select Column P and click the filter icon.

7. Filter out Advanced payments. Click the filter icon, uncheck "Advanced" from the drop-down, and apply the filter. This will leave only "N/A" transactions.

8. Select all the "N/A" rows and delete them

9. Remove the filter once you're done. Click the filter icon and disable the filter.

Now, you'll have a list of only the advanced payment transactions.

Recalculating the Total:

• Select the first and last cell of the column with payment amounts and apply the sum formula.
• Example: =SUM(O2:O279).
• Adjust the cell range depending on the number of rows in your sheet.

Now, you have an accurate total of advanced payments for the desired period!