Payment Ledger Report

Follow

The Payment Ledger is an accounting report that allows you to view all of the payments collected today, and see the list of transactions associated with those payments to see whether the reservation was paid in full, what was paid for, or make sure your payments match your revenue.

The usefulness of the accounts receivable subsidiary ledger lies in the fact that it can show, at a glance, whether the property has received all the payments that it was supposed to, for a specific day or month.

Getting Started

Access the Payment Ledger on Cloudbeds PMS:

  1. Click Reports.
  2. Select Financial Reports.
  3. Click Payment Ledger.

This report is customizable to your properties needs. Use the filters to make the appropriate selections:

  1. Payment Type: Select the various payment types that will be used for determining which reservations will appear on the report.
  2. Payment Date: Select a single date, or date range, to filter the date on which the payment was made.
  3. Associated Transactions: Select which other reservation transaction you would like to display on the report. Only the selected types of transactions will appear for all of the reservations matching the filters.
  4. Posted / Pending Transactions: You can select whether you want to show only the transactions that have already been posted, or those transactions that are still pending or both.
  5. Search by Reservation Number, Room or Guest Name.
  6. Check in / Check Out Dates: Select the check-in and check-out date of reservations.
  7. User: Select specific users or all users.
  8. Group By: Depending on the type of report you wish to run, you can group by the following (User, Date, Room, Guest Name, Res #, or Description).
  9. Sub Group by: Same options available as listed above.
  10. Status: Select the reservation status (by default, all status will be selected).
  11. Type: Select to sort out different transaction types. Those transaction types are as follows:
    • Reservations - Transactions in a reservation
    • House Accounts - Transactions in a house account
    • Group Profile - Transactions in a group profile (only if Groups feature is enabled).

12.   Send this report by email or export to PDF / Excel.
13.  Click Apply to generate the report.

How to Generate the Report

Show all cash transactions done for a specific time frame

Apply the following filters:

  1. Payment Type: Unselect the box Select all Credits (Payments), then scroll down to CASH and check that option.
  2. Payment Date: Enter the date range or specific date you require.
  3. Associated Transactions: Unselect the box Select all Debits and the box Select all Credits (Payments). Once both are unchecked, scroll down the credits column and check CASH.
  4. Posted / Pending Transactions: Click the drop-down arrow and unselect all and check POSTED.
  5. Group By: If you are using a date range, this section you will want to group by DATE. If you are using a specific date, then group by Room or reservation #.
  6. Sub-Group By: If you are using a date range, this section you will want to sub-group by ROOM. If you are using a specific date then this is optional.
  7. Click Apply.
Show all credit payments

Apply the following filters:

  1. Payment Type: Ensure that the box Select all Credits (Payments) is checked.
  2. Payment Date: Enter the date range or specific date you require.
  3. Associated Transactions: Unselect the box Select all Debits (Charges).
  4. Posted / Pending Transactions: Click the drop-down arrow, unselect all, and check POSTED.
  5. Group By: DESCRIPTION
  6. Sub-Group By: Either GUEST NAME, RES #, or Room.
  7. Click Apply.
Show a list of advanced payments posted at any point in time

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. Keep in mind 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 section explains how to generate a list of advanced payments posted on a particular day or during a particular period of time (for both future and past reservations):

  1. If you need to check the amount of deposits held on a particular day, you need to generate a list of payments posted on that day for the guests arriving the following day or later.
  2. If you need to check it for a period of time, you'll also need to do the same but then extract the report and remove the payment transactions which were not advanced in the spreadsheet.
1. Generating a list of advanced payments posted on a particular day

Apply the following filters:

  1. Payment Type: Select all.
  2. Payment Date: Select the desired date (e.g. the 1st of November).
  3. Associated Transactions: No Associated Transactions should be selected under the drop-down menu.
  4. Select Check-in Date rage: 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. Click Apply.
  7. Export the report as an excel file (if needed).

You will have a list of payment transactions posted on the selected day (the 1st of November) for the reservations that are checking-in 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.

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 until any day far in the future (e.g. it should be from the 2nd of November until somewhere in 2 years).
  5. Type: Select reservations only.
  6. Click Apply.
  7. Export the report as an excel file.

You will have the list of both "advanced" and "not advanced" payment transactions. Some of them were posted during the guest's stay. Unfortunately, you cannot remove them in the system, so in this case, you will need to export the generated report and delete the payments which were not advanced. 

The instructions below describe the best way to do it in a Google Spreadsheet.

In the extracted report, you need to delete payment transactions posted on the day of the check-in or later. You can use the "IF formula" to mark those transactions. Then, you need to delete them and to recalculate the total.

The following instructions explain how to do it in detail.

Open the Excel file in the Google Spreadsheet:

  1. For your convenience, freeze the 1st row.
    How: Click View > Freeze > 1 row.
  2. Change the format for the following columns: B (Date/Time), I (check-in date) and J (check-out date). Switch from 'Plain Text' to 'Date'.
    How: Select the 3 columns > Click Format > Number >  Date.
  3. Add an additional column at the end of the sheet (column P).
  4. On the new column, go ahead and apply an 'IF formula' to mark the non-advanced transactions.
    How: Add the following formula: =ARRAYFORMULA(IF(I:I<=B:B,"N/A","ADVANCED")) to P1.

The formula says the following:

If the check-in date is less or equal to the payment date, it will be called "n/a", if not, it will be called "Advanced".

ARRAYFORMULA = Use this array formula in order to have the 'IF formula' applied to the whole column.

I:I<=B:B means: check-in date is less or equal to payment date/time.

"N/A","ADVANCED" =Excel will generate the results based on the 'IF formula' applied and they will be displayed as "N/A" (not applicable) and "Advanced". These are just the terms and you can use any other words.

Note that Both "N/A and "Advanced" should be added into parentheses, otherwise excel will not accept it.

5.    Apply a filter to the column that you've just created.
       How: Select the column that you've just created > click Create a Filter icon.

Jing

6.      By using the filter that you've just created, go ahead and remove the "Advanced" transactions from the filter to have a list of only "non-advanced" transactions (N/A).
        How: Click the filter icon > remove "Advanced" from the drop-down > Click OK to apply.

Jing

7.     Select all the N/A rows and delete them.

Untitled spreadsheet - Google Sheets - Google Chrome

8.     Remove the filter.
       How: Click the filter icon > turn off filter.

Jing

Now, you have only "Advanced" payment transactions on the list.

9.     Recalculate the total.
      How: Apply the sum formula on the last row.

  • Click on the current total > Click the Functions icon  > select SUM > select the 1st and the last column:row
  • Example: =SUM(O2:O279)

See the example below, and note that the row numbers depend on the number of transactions/rows in your sheet. 

Example:

Untitled spreadsheet - Google Sheets - Google Chrome

Now you will see the total of "Advanced" payments.

Powered by Zendesk