Worksheets: Create and Edit

These steps assume you have already set up the following common data:

• Banks

• Bank Accounts (including virtual accounts)

• Bank Account Trees

• User Codes

• User Code Trees

• Bank Connectivity

• Bank Account Document Import

The steps also assume you have a detailed understanding of the following functionality:

• Viewing Transactions in Balance Explorer

Please follow the above links to complete any setup steps that you may have missed before continuing.

Gather Information

To implement worksheets, you need to determine your reporting requirements. Put together examples of all reports along with a detailed explanation of each report’s purpose.

Review Your Reports

Upon review, some reports may be best fit to Dashboards or Business Intelligence reporting tools, while others are better suited for standard reports or FI reports.

A subset of your reports (especially those that are in a spreadsheet format) may be best suited to be converted into worksheets. Your cash position spreadsheet is a prime example of the type of report that is best handled in worksheets.

Plan for Worksheets

Since every business’s needs and data are different, the exact setup of your cash position worksheets is unique. Therefore, this document is a guide on the various building blocks of a worksheet and how each piece of functionality works rather than a prescriptive guide.

Plan Structure of Spreadsheet Reports

Do your spreadsheet reports adhere to the following for each bank account?

• Closing Balance = Opening Balance + Credits – Debits

• Proof of cash included

Even if the current spreadsheet reports do not adhere to this standard, plan to structure the cash position worksheets (CPWs) so that they do. It is important to follow this best practice for consistency across accounts and proof of cash.

Plan Data Presentation in Spreadsheet Reports

Determine the following for each spreadsheet report:

• Where all pieces of information is coming from

• How transaction data is imported into Ripple Treasury

• How the data is organized and grouped for best interpretation

• How the data needs to be viewed to create a foundation for cash decisions and actions

The Ripple Treasury standard format for a prior day CPW is a single date (pivot) with accounts in the columns and activity types (user codes) in the rows.

• Dates:

  • Single date (standard)

  • Date range

  • Does it include totals by a timeframe such as week or month?

• Accounts:

  • Listed singly

  • Listed in groups (account trees)

  • Listed by a combination of single accounts and account trees

    • There is no standard for this (custom setup)

• User codes:

  • Listed singly

  • Listed in groups (user code trees)

  • Listed by a combination of single user codes and user code trees

    • There is no standard for this (custom setup)

Plan Labels

Determine the labels you need for your cash position worksheets (CPWs).

• Accounts:

  • Accounts can be displayed as ID, number, or name.

  • Account trees can be displayed as ID or name.

• User codes:

  • User codes can be displayed as ID or name.

  • User code trees can be displayed as ID or name.

Plan User Codes

User codes define the types of activity that can happen to an account. In balance reporting files coming from banks, they are usually BAI bank codes (BAI) or SWIFT generated codes (MT940).

These bank-generated codes are typically not directly used for reporting purposes. You may want to instead group bank codes into one custom label. A custom user code will need to be created for each of these labels using transaction assignment (TA) rules.

• For example, for all balances that have a user code of 0142 or 0195 and have “FX” in the description, replace the user codes with “FX_CR.”

• For all balances that have a user code of 0191, 0195, 0196, or 0208, replace the user codes with “WIRE_IN.”

• For all balances that have a user code of 0115 or 0116, replace the user codes with “LOCKBOX_CR.”

Plan User Code Trees

Consider whether you want to aggregate your user codes using a user code tree.

Set Up Worksheet User Options

If you want to edit worksheet user options, then test your new options before applying them to understand their impact on the worksheets. Some combinations could distort your information.

Refer to Manage user options for details about what the options are and how to edit them.

Prepare for Worksheets

1. Gather data for a couple of days where the activity was heavy or unusual such as payroll, month-end, or tax day.

2. Load the balance reporting files for those days into Ripple Treasury.

3. Extract the credit and debit records into an Excel spreadsheet using Balance Explorer.

4. Add three new columns to your extracted spreadsheet:

   • User code or label you are currently using

   • New custom user code

   • New custom user code description

5. This creates a list that includes the bank codes with your current corresponding user codes or labels, and the new custom user code with its description.

   

6. Using this list, create the custom user codes that you need. The user code IDs have a 10-character maximum length.

7. Find the bank codes you will be replacing with custom user codes, if applicable.

8. Create the transaction assignment (TA) rules that will convert the bank codes to user codes.

Create Worksheets

When creating a worksheet, the Ripple Treasury Platform times out after 20 minutes regardless of user activity. The timeout causes account lists and user code lists to disappear and leaves you unable to save your new worksheet. To avoid this problem, add an account and user code within the first 20 minutes of creating a new worksheet and select Save. Then you can continue editing and saving updates to the worksheet.

1. Go to Cash > Position and Forecasting > Worksheets.

   

2. The Worksheets screen opens with a list of all existing worksheets.

3. Select New Worksheet or select the Clone icon for a worksheet to be cloned.

   • The clone function allows you to create a new worksheet using an existing worksheet as a base to be edited. We recommend you clone existing worksheets, starting with the prior day worksheet, to ensure consistency throughout worksheets and avoid discrepancies.

   

4. The Create Worksheet Setup or Clone Worksheet Setup screen opens.

   

5. Fill in the sections as needed. Asterisks (*) designate required fields.

   • Refer to Accounts setup in worksheets for detailed instructions about setting up accounts.

   • Refer to User codes setup in worksheets for detailed instructions about setting up user codes.

     • Add a Proof of Cash formula field to your prior day worksheet to validate that all information is in the worksheet.

   • Refer to Worksheet setup field descriptions for explanations about each field.

6. Select Save & Execute at the bottom of the screen to save the worksheet and then open the worksheet to its default view.

Edit Worksheets

1. Go to Cash > Position and Forecasting > Worksheets.

   

2. The Worksheets screen opens with a list of all existing worksheets.

3. Use the column filters as needed to search for a worksheet.

4. Select the Edit icon for the worksheet to be edited.

   

5. The Edit Worksheet Setup screen opens.

   

6. Edit the sections as needed.

   • Refer to Accounts setup in worksheets for detailed instructions about setting up accounts.

   • Refer to User codes setup in worksheets for detailed instructions about setting up user codes.

   • Refer to Worksheet setup field descriptions for explanations about each field.

7. If you are making edits to one worksheet, also update worksheets with similar information to avoid discrepancies.

8. Select Save & Execute at the bottom of the screen to save the worksheet and then open the worksheet to its default view.

Accounts Setup in Worksheets

The Accounts tab displays the accounts and account trees that are used in the worksheet calculations. Accounts can be added to the worksheet in this tab or using the overlay.

When creating a worksheet, the Ripple Treasury Platform times out after 20 minutes regardless of user activity. The timeout causes account lists and user code lists to disappear and leaves you unable to save your new worksheet. To avoid this problem, add an account and user code within the first 20 minutes of creating a new worksheet and select Save. Then you can continue editing and saving updates to the worksheet.

Add Accounts and Trees

1. Select Add Accounts from the Accounts tab or the overlay.

   

2. In the Add Accounts pop-up screen, navigate to the Accounts tab or the Account Trees tab as needed.

3. Select the accounts (including accounts of digital assets) or trees that you want included in the calculations.

   1. Filter the list as needed to search for applicable items.

   2. Use the Shift or Control keys to select multiple accounts as needed.

   3. Navigating to a new page will unselect all items.

4. Select Add to add selected items without exiting the Add Accounts screen.

   1. After an account or tree is added, it is grayed out.

   2. Adding a tree includes all subtrees and associated accounts even though they are not visible in this screen. Refer to Bank Account Explorer to verify everything included when you add a tree. If an account is inside multiple account trees, it is possible to duplicate the account in the worksheet, which creates incorrect calculations. Make sure you are aware of all accounts that will be included in each tree that you add.

5. After adding or selecting all required items, select Add and Close.

   

6. All added accounts and trees appear in the Accounts tab.

Edit Appearance

1. Select the Edit icon for the account (including accounts of digital assets) or tree to be edited.

   

2. The fields become editable.

   

3. Edit the fields as needed to change the way the account or tree will appear in the worksheet.

4. Select the Save icon.

Add Target Balance and Detail

The target balance is only required when you use the Create recommended transfer functionality. You may choose to include target balance information for other reporting purposes.

1. Select the Target icon for the account (including accounts of digital assets) or tree that requires a target balance.

   

2. Enter the target balance, soft target balance, hard target balance, and payment templates for the account or tree.

3. Select Save.

Delete Item

1. Select the Delete icon.

   

2. Select OK from the pop-up that opens.

Add Separator Line Item Type

1. Select Show More.

   

2. The field to add separator lines and subtotals opens and the Show More button turns into a Show Less button.

3. Drag the Separator Line into the worksheet as needed. You can add as many as desired.

   • Separator lines are typically used to divide sections of the worksheet.

   

4. Select the Edit icon to add a name or color to the separator line.

   

5. Edit the ID, Description, and Header Color fields as desired.

   • ID field has 16 characters max.

6. Select the Save icon.

Add Subtotal Item Type

For accounts, trees are typically used instead of subtotals. However, subtotal calculations may be needed for your use. For example, where you might want to group and subtotal accounts by currency.

1. Select Show More.

   

2. The field to add separator lines and subtotals opens and the Show More button turns into a Show Less button.

3. Drag the Subtotal into the worksheet as needed. You can add as many as desired.

   • When using a worksheet to look forward, the subtotal is typically at the top of a section followed by account trees being summed. The account trees then let you drill down into the accounts.

   • When using a worksheet to look backward, the subtotal is typically at the bottom of the trees and accounts being summed.

   

4. Select the Edit icon to adjust the ID, Description, and Header Color fields.

   

5. Edit the ID, Description, and Color fields as desired.

   • ID field has 16 characters max.

6. Select the Save icon.

   

7. Expand the Subtotal to view and edit the formula.

   

8. Select Edit.

   

9. Select the account (including accounts of digital assets) or tree from the Available column.

   • As desired use Shift or Ctrl to select multiple components at once.

10. Select the operator button to add or subtract the account or tree.

    • Use caution. It is possible to add or subtract an account multiple times, which would cause incorrect sums.

11. Select Save.

Reorder and Delete

You can drag accounts (including accounts of digital assets) and trees in the worksheet to reorder them as needed. However, Reorder Accounts is often more convenient, especially if you have a lot of accounts and trees in your worksheet.

Reorder Accounts also lets you delete multiple accounts and trees at once instead of deleting them individually using the Delete icon in the Actions column.

1. Select Reorder Accounts.

   

2. Select the account, tree, subtotal, or separator to be moved.

3. To reorder, drag the item to a new location in the worksheet.

4. To delete, select Delete Selected.

5. Select Save.

Save as Account Template

1. Select Save as Template.

   

2. Enter the new Account Template name.

3. Enter the Description.

4. Select Save.

Import Account Template

1. Select Import Template.

   

2. Select the template from the Account Template drop-down list.

3. Select Append or Replace.

   • Append adds the template to the existing list of accounts and trees in the worksheet.

   • Replace removes the existing list of accounts and trees to use the template instead.

4. Select Save.

Add Account Auto-Roll

Auto-roll advances the value date of unreconciled balances for the selected accounts (including accounts of digital assets) to keep estimated balances in the worksheet until they are reconciled.

1. Set up reconciliation rules if they don’t already exist.

   1. Worksheets must have at least one reconciliation rule before setting up auto-roll.

2. Mark the accounts and trees that you want included in auto-roll.

   1. Marking or unmarking auto-roll for an account or tree, changes the auto-roll status for that item across all reconciliation worksheets. Auto-roll can’t be turned on for an item in a worksheet and off for the same item in another worksheet.

User Codes Setup in Worksheets

The User Codes tab displays the user codes and user code trees that have been added to the worksheet. User codes can be added to the worksheet in this tab or using the overlay.

When creating a worksheet, the Ripple Treasury Platform times out after 20 minutes regardless of user activity. The timeout causes account lists and user code lists to disappear and leaves you unable to save your new worksheet. To avoid this problem, add an account and user code within the first 20 minutes of creating a new worksheet and select Save. Then you can continue editing and saving updates to the worksheet.

Add User Code or Tree

Float user codes allow you to include float information in the worksheet. However, in order to include float the Dates tab must be set up to show value date instead of transaction date.

1. Select Add User Code from the User Code tab or the overlay.

   

2. In the Add User Codes pop-up screen, navigate to the User Codes tab or the User Code Trees tab as needed.

3. Select the user codes or trees that you want included in the worksheet. Include only the main user code without float; don’t include the related float user codes.

   1. Filter the list as needed to search for applicable items.

   2. Use the Shift or Control keys to select multiple user codes or trees as needed.

   3. Navigating to a new page will unselect all items so do not move to the next page before adding the needed user codes or trees.

4. Select Add to add selected items without exiting the Add User Codes screen.

   1. After a user code or tree is added, it is grayed out.

   2. Adding a tree includes all subtrees and associated user codes even though they are not visible in this screen. Either add the trees and then drill down into them to verify the user codes and subtrees included (can’t drill down into subtrees from the User Codes tab) or refer to User Code Explorer to verify what will be included when you add a tree. If a user code is inside multiple trees, it is possible to duplicate the sums in the worksheet, which will create incorrect calculations. Make sure you are aware of all user codes that will be included in each tree that you add.

5. After adding or selecting all required items, select Add and Close.

   

6. All added user codes and trees appear in the User Codes tab.

Add Item Types

In addition to user codes and trees, you can add item types to your worksheet: Opening Balance formula, Separator Line, Subtotal, Carry Forward Discrepancy, and Last Reported Balance.

1. Select Show More.

   

2. The field to add item types opens and the Show More button turns into a Show Less button.

3. Drag the desired item type into the worksheet as needed. You can add as many as desired.

Edit Item Type Fields

1. Select the Edit icon in the Actions column.

2. Edit the fields.

   1. The Opening User Code and Carry Forward fields are mandatory.

3. Select the Save icon.

Edit Item Type Formulas

If you have a formula in your worksheet but it doesn't display when you execute the worksheet, go to the Display tab to review the worksheet layout. If you are pivoting on user codes, the formula won’t be visible.

1. Expand the item type to view and edit the formula.

   

2. Select Edit.

   

3. Select the user code or tree from the Available column.

   1. As desired use Shift or Ctrl to select multiple components at once.

4. Select the operator button to appropriately calculate with the user code or tree.

   1. Use caution. It is possible to calculate with a user code multiple times, which would cause incorrect sums.

   2. If desired, you can use multiplication to calculate FX rates without using the FX tab.

   3. If desired, you can multiply user codes by a constant value. This value is typically the forecasted FX rate, or the actual FX rate that you aren’t uploading into the system, or a manual adjustment for another reason.

5. Select Save.

Item Type Options

Recommended Transfer Item Type

A recommended transfer generates a payment or funds transfer, in or out of your account using the target balance to calculate the transfer. The account must already be set up in Ripple Treasury.

1. Select the Actual or Estimated checkbox (not both) from the Create Recommended Transfer heading.

2. A new item type called Recommended Transfer appears at the bottom of your worksheet.

3. Set up the formula. The formula is required for the Recommended Transfer to be functional.

   1. The following example subtracts the Estimated Closing Balance from the Target Balance.

Opening Balance Formula Item Type

The opening balance formula can be established here in the User Codes tab with the Opening Balance formula item type or set at the account level in the Account tab using the Opening User Code and Carry Forward fields.

Separator Lines Item Type

Typically, the separator line is used anywhere you want to separate one section from another (for example, separating your summary user codes from your detail user codes).

If you have a separator in your worksheet but it doesn't display when you execute the worksheet, go to the Display tab to review the worksheet layout. If you are pivoting on user codes, the separator won’t be visible.

Subtotal Item Type

You can have as many subtotals as you need on a worksheet.

• When using subtotals for forecasting worksheets, the subtotal is typically at the top of a section with user code trees allowing you to drill down into the user codes.

  • If you have a subtotal at the top of a forecast worksheet that pivots on accounts, you can’t have a value date prior to today’s date. Otherwise, you get the error message ”Critical (1) : [System.Core] Value cannot be null. Parameter name: source.”

• When using subtotals for worksheets with current and historical information, the subtotal is typically at the bottom of the section.

Carry Forward Discrepancy Item Type

This item type compares the difference between a closing balance user code and the calculated (subtotal) closing balance, which includes the balance derived from the opening balance formula item type. It carries yesterday’s discrepancy between these figures to today. No formula settings are needed within the carry forward discrepancy item type.

Last Reported Balance Item Type

This item type allows you to assign a user code to see activity for each period, even when there are no transactions.

Reorder User Codes

You can drag user codes and trees in the worksheet to reorder them as needed. However, Reorder User Codes is often more convenient, especially if you have a lot of user dodes or trees in your worksheet.

Reorder User Codes also lets you delete multiple user codes instead of deleting them individually using the Delete icon in the Actions column.

1. Select Reorder User Codes.

   

2. Select the user code, tree, or item type to be moved.

3. Drag the item to a new location in the worksheet.

4. Select Save.

Save as user code template

1. Select Save as Template.

   

2. Enter the new user code template name.

3. Enter the description.

4. Select Save.

Import User Code Template

1. Select Import Template.

   

2. Select the template from the User Code Template drop-down list.

3. Select Append or Replace.

   • Append adds the template to the existing list of user codes and trees in the worksheet.

   • Replace removes the existing list of user codes and trees, using the template instead.

4. Select Save.

Add User Code Auto-Roll

Auto-roll advances the value date of unreconciled balances with specified user codes to keep estimated balances in the worksheet until they are reconciled.

1. Set up reconciliation rules if they don’t already exist.

   1. Worksheets must have at least one reconciliation rule before setting up auto-roll.

2. Mark the user codes and trees that you want included in auto-roll.

   1. Marking or unmarking auto-roll for a user code or tree changes the auto-roll status for that code across all reconciliation worksheets. Auto-roll can’t be turned on for an item in a worksheet and off for the same item in another worksheet.

Worksheet Setup Field Descriptions

The way you set up a worksheet in these tabs dictates how your worksheet opens (or executes). Inside the worksheet, you have options to make one-off display updates.

General

Field descriptions with an asterisk (*) are required.

Fields:

• *Worksheet ID: (16 character max.) Unique identifier of the worksheet (not modifiable once created).

  • The worksheet ID can’t be edited, but the worksheet can be cloned with a modified worksheet ID and then the original can be deleted. Deletion is restricted if the worksheet is tied to a job filter.

• Active: Mark the checkbox to activate or deactivate the worksheet.

• *Description: (35 character max.) Description of the type of worksheet created.

• Worksheet Category: Select the worksheet category if you have created categories to organize and manage worksheets in your Ripple Treasury Platform.

  • Worksheet categories are managed in Cash > Position and Forecasting > Worksheet Category.

• * Worksheet Owner: Read-only field automatically generated based on the user who created the worksheet.

  • The worksheet owner can’t be edited, but the worksheet can be cloned by a new user and then the original can be deleted. Deletion is restricted if the worksheet is tied to a job filter.

Accounts

If the worksheet has reconciliation rules, a Show Auto-Roll Summary icon is visible in the upper right corner of every tab. The overlay displays a summary of the accounts and user codes that will be set up for auto-roll.

The Accounts tab displays the accounts (including accounts of digital assets) and account trees that are used in the worksheet calculations. Accounts can be added to the worksheet in this tab or by expanding the Show Overlay icon that is visible in the upper right corner of every tab. The overlay shows a preview of your setup and lets you add accounts and user codes.

When creating a worksheet, the Ripple Treasury Platform times out after 20 minutes regardless of user activity. The timeout causes account lists and user code lists to disappear and leaves you unable to save your new worksheet. To avoid this problem, add an account and user code within the first 20 minutes of creating a new worksheet and select Save. Then you can continue editing and saving updates to the worksheet.

Fields:

• ID: ID of the line item (account, tree, or item type from Show More). The ID can’t be edited for accounts and trees. The ID may or may not appear in the worksheet, depending on your settings in the Display tab.

• Description: You can edit the description to anything you’d like but it can’t be blank. If you edit the description here, the edit will only appear in this worksheet. It won’t be updated in the other places the account or tree is used.

• Type: Read-only field that indicates the line item type (account, tree, or item type from Show More).

• Hidden: Mark this checkbox to hide the account or tree on the worksheet. This results in accounts being used in the calculation but not being visible on the worksheet.

• Auto-Roll: (Only applicable for worksheets with reconciliation rules) Mark the checkbox to include the account or tree in the Reconciliation auto-roll.

• Header Color: Add a header color as needed for the Description or ID Column on the worksheet.

• Opening User Code: Turns yesterday’s closing balance into today’s opening balance. This is used because your bank may not provide an opening balance; they typically don’t for current day files (estimated transactions).

  • Some banks do provide an opening user code value (BAI bank code 0010), but you may choose to still carry forward yesterday’s closing balance for convenience.

  • You can specify the opening user code for an individual account or set of accounts. This is common when you are using a different opening balance user code for a bank or account than for the rest of your accounts.

  • You can specify a specific opening balance, user code, or closing balance from the day before for the entire worksheet. This is what is carried forward to the opening balance for a given date.

• Carry Forward: This column works in conjunction with the Opening User Code column. You use this because:

  • Your bank may not report the opening balance so you need to carry forward yesterday’s closing balance.

  • When creating a forecast worksheet, you need to carry forward a balance into the future from the last known closing balance to derive the future balance of an account.

  • When creating a current day worksheet (estimated transactions) bank current day files, the files may be provided by your banks multiple times per day (up to every 15 minutes) so we recommend carrying forward yesterday’s closing balance.

    • For the USA, ensure you factor in the float because yesterday’s closing ledger balance is not necessarily all available today.

  • Carry forward options:

    • Prior Day: Use “Prior Day” as the carry forward. This will use yesterday’s closing balance as today’s opening balance.

    • Current Day: This is a non-standard selection. Don’t use “Current Day” unless you have a compelling reason. This will use today’s closing balance as today’s opening balance.

• Actions: Available actions for the line item.

  	Edit: Edit the line item.
  	Delete: Delete the line item.
  	Target: Add target balance and details for the line item.
  	Save: (Only visible in Edit mode.) Save the changes made in Edit mode.
  	Cancel: (Only visible in Edit mode.) Cancel the changes made in Edit mode.

User Codes

If the worksheet has reconciliation rules, a Show Auto-Roll Summary icon is visible in the upper right corner of every tab. The overlay displays a summary of the accounts and user codes that will be set up for auto-roll.

The User Codes tab displays the user codes and trees that have been added to the worksheet. User codes can be added to the worksheet in this tab or by expanding the Show Overlay icon that is visible in the upper right corner of every tab. The overlay shows a preview of your setup and lets you add accounts and user codes.

When creating a worksheet, the Ripple Treasury Platform times out after 20 minutes regardless of user activity. The timeout causes account lists and user code lists to disappear and leaves you unable to save your new worksheet. To avoid this problem, add an account and user code within the first 20 minutes of creating a new worksheet and select Save. Then you can continue editing and saving updates to the worksheet.

Fields:

• Create Recommended Transfer: Mark the Actual or Estimated checkbox to generate a payment or transfer funds into or out of a bank account.

  • These bank accounts must first be set up in Ripple Treasury.

  • Marking one of these checkboxes (do not mark both) adds the recommended transfer item type to the worksheet.

• ID: (16-character max.) ID of the line item (user code, tree, or item type from Show More). This cannot be edited for user codes and trees.

• Description: You can edit the description to anything you’d like but it can’t be blank. If you edit the description here, the edit will only appear in this worksheet. It won’t be updated in the other places the user code or tree is used.

• Type: Read-only field that indicates the line item type (user code, tree, or item type from Show More).

• Hidden: Mark this checkbox to hide the user code or tree on the worksheet. This results in user codes being used in the calculation but not being visible on the worksheet.

• Auto-Roll: (Only applicable for worksheets with reconciliation rules) Mark the checkbox to include the user code or tree in the reconciliation auto-roll.

• Header Color: Add a header color as needed for the Description or ID columns on the worksheet.

• Opening User Code: (Only applicable for the Opening Balance item type) Turns yesterday’s closing balance into today’s opening balance. This is used because your bank may not provide an opening balance; they typically don’t for current day files (estimated transactions).

  • Some banks do provide an opening user code value (BAI bank code 0010), but you may choose to still carry forward yesterday’s closing balance for convenience.

  • You can specify the opening user code for a specific set of accounts on the Account tab or add the user code here if you want it applied to all accounts.

• Carry Forward: (Only applicable for the Opening Balance item type) This column works in conjunction with the Opening User Code column. You use this because:

  • Your bank may not report the opening balance so you need to carry forward yesterday’s closing balance.

  • When creating a forecast worksheet, you need to carry forward a balance into the future from the last known closing balance to derive the future balance of an account.

  • Carry forward options:

    • Prior Day: Use “Prior Day” as the carry forward. This will use yesterday’s closing balance as today’s opening balance.

    • Current Day: This is a non-standard selection. Don’t use “Current Day” unless you have a compelling reason. This will use today’s closing balance as today’s opening balance.

    • See Account: Uses the carry forward setup from the Account tab.

• Create Balance: (Only applicable for the Subtotal item type) Allows the balance from a Subtotal item type to be created and applied to a user code. If set up, this is created using the WKBALSUB plugin.

• Create Other Balance Type: (Only applicable for the Subtotal item type) Allows users to create an additional balance transaction based on a subtotal value. For example, if the Actual column for the subtotal is creating a balance, when the Create Other Balance Type checkbox is marked you can create an estimated balance for the same amount and user code. This setting also applies to the estimated column (marking the column creates an actual balance).

• Last Reported Balance: (Only applicable for the Last Reported Balance item type) This assigns a user code so you can see activity for each period, even when there are no transactions. This field is only visible if it is enabled. Contact Ripple Treasury Support to have it enabled.

  • Example: User code 5607 had a $100 balance on Monday and a $150 balance on Friday. When including the Last Reported Balance column in the worksheet, you would see $100 on Monday, Tuesday, Wednesday and Thursday. Then you would see $150 on Friday.

• Actions: Available actions for the line item.

  	Edit: Edit the line item.
  	Delete: Delete the line item.
  	Save: (Only visible in Edit mode.) Save the changes made in Edit mode.
  	Cancel: (Only visible in Edit mode.) Cancel the changes made in Edit mode.

Dates

The Dates tab allows you to define how the dates and offsets default in your worksheet, and the type of dates to display. Other than the banking calendar and accounting calendar, all of these settings can be changed in the executed worksheet. But the next time you execute the worksheet it goes back to the defaults that you set up in this section.

Date Offset Details

Fields:

• Date Type:  Date type to be viewed in the worksheet.

  • Transaction Date: Date of the transaction (initiation date, not necessarily value date).

  • Value Date: Settlement date, the date the transaction was debited or credited to the bank account. If you’re creating a forecast worksheet or including float user codes, use value date. Forecast and float are looking into the future; if you used transaction date, you wouldn’t accurately reflect non-current transactions.

  • Process Date: Date the transaction was entered or imported into Ripple Treasury, which could be months or years after the value date or transaction date. If you’re evaluating transactions imported on a certain date for variances, use process date.

• Banking Calendar: Selects the bank calendar to be used when generating the worksheet. The default is US. The bank calendar is used to establish the business days and holidays, which determines the business days used for the start and end date offsets when “Business Days” is selected. If you choose the calendar “No Holiday,” this overrides the Holidays day type.

• Accounting Calendar: Selects the accounting calendar to be used when generating the worksheet. The accounting calendar determines the accounting period used for the start and end date offsets when “Accounting Periods” is selected.

  • You have preconfigured calendars such as month end, month begins, quarter end, quarter begins, and semi-annual.

  • This calendar is important for situations when your accounting month end does not correlate to a calendar month end (for example, 445).

• Start Date: Determines the start date for transactions in the worksheet. Enter the offset value (positive or negative number), then select the operating unit (Accounting Periods, Business Days, or Calendar Days). For example, entering “-1” and selecting Accounting Period generates the worksheet one accounting period prior to the start date).

• End Date: Determines the end date for transactions in the worksheet. Enter the offset value (positive or negative number), then select the operating unit (Accounting Periods, Business Days, or Calendar Days). For example, entering “-1” and selecting Accounting Period generates the worksheet one accounting period prior to the start date).

• Forecast Start Date: Determines the start date for when forecast data can be entered into the worksheet.

  • This is only for manually-entered forecast data and is tied to fast entry; it doesn’t affect imported forecast data that is committed to the worksheet.

• Forecast End Date: Determines the end date for when forecast data can be entered into the worksheet.

  • This is only for manually-entered forecast data and is tied to fast entry; it doesn’t affect imported forecast data that is committed to the worksheet.

• Use System Forecast Options: If this checkbox is marked, the worksheet uses the default forecast offsets that are set in System Options. Contact your Ripple Treasury Support for assistance changing the default if needed.

Day Types

Mark the checkboxes for the default day types that you want included in the worksheet. At least one is required.

Checkboxes:

• Weeks: Aggregates the view by weeks. Show start of week opening balance, end of week closing balance, and total net movement (debts and credits) for that week.

• Months: Aggregates the view by months. Show start of month opening balance, end of month closing balance, and total net movement (debts and credits) for that month.

• Quarters: Aggregates the view by quarters. Show start of quarter opening balance, end of quarter closing balance, and total net movement (debts and credits) for that quarter.

• Years: Aggregates the view by years. Show start of year opening balance, end of year closing balance, and total net movement (debts and credits) for that year.

• Business Days: Includes business days in the worksheet.

• Non Business Days: Includes non-business days (e.g. Saturday, Sunday) in the worksheet.

• Holidays: Includes holidays, as defined in the holiday calendar, in the worksheet.

• Periods: Includes accounting periods, as defined in the accounting calendar, in the worksheet. If you mark this checkbox, you must have selected an accounting calendar; otherwise you will get an error when you try to save.

• Range: Uses the start and end date range.

Display

The Display tab allows you to derive the format and display settings.

Worksheet Layout

The worksheet layout defines how and where the date, user code, and accounts appear in your worksheet. You can also define what balance types are viewed (actual, estimated, and forecast).

Data Placement

Move the Account, Date, and User Code cards as needed to set up your worksheet.

• Top Left: The card in the top left corner sets the pivot. This means that you will only see the first data element you choose for this type of data. In the above image, the worksheet will pivot on Account.

• Top: The card in the top position (top right) indicates the columns in the worksheet. In the above image, the worksheet columns will be Dates.

• Left: The card in the left position (bottom left) indicates the rows in the worksheet. In the above image, the worksheet rows will be User Codes.

The above settings (the worksheet is pivoting on Date with Account in columns and User Codes in rows) will create a table with the following setup.

If you move the cards so the worksheet is pivoting on User Code with Date in columns and Account in rows, it creates a table with the following setup.

If you move the cards so the worksheet is pivoting on Account with User Code in columns and Date in rows, it creates a table with the following setup.

Balance Type

Mark the balance type (actual, estimated, and forecast) checkboxes and Eye icons as needed to set up your worksheet. Any combination of multiple balance types may be used.

• If a balance type is unmarked, it will not be available in the worksheet.

• If a balance type is marked but the eye is slashed through, it will not be in the worksheet by default but it can be added to the worksheet after the worksheet is executed.

• If a balance type is marked and the eye is not slashed through, it will be in the worksheet by default.

Show

The Show section defines what information will be visible in the worksheet.

Fields:

• Show Account As: Account identifier on the worksheet. Default is ID. For trees, the default is Account ID even if you select “Number.”

  • ID

  • Number

  • Name

• Show User Code As: User code identifier on the worksheet. Default is Description.

  • Code

  • Description

• Show Account Owner: Shows the owner of the account on the worksheet.

• Show All Available Accounts: Shows all available accounts even if your user does not have access to them. This directly impacts security.

• Show Totals for Trees: Shows the same subtotal regardless of tree level (not commonly used).

• Show Transaction Count: Shows the number of transactions that make up a single balance in brackets.

  • This can be useful when doing reconciliations, TA rule validations, and determining how many transactions haven’t been reassigned.

• Show Variance by Amount: Dictates that variances will display as an amount.

• Show Variance by Percent: Dictates that variances will display as a percentage.

• Show Empty Rows: Displays rows even if they don't have a balance.

• Show Empty Columns: Displays columns even if they don't have a balance.

• Show Zero Amount Rows: Displays rows even if the amount is “0.”

• Show Zero Amount Columns: Displays columns even if the amount is “0.”

• Enable WISA: Enables What If Scenario Analysis functionality.

Carry forward balances

Carry forward balances are used in conjunction with the opening balance formula to carry forward a previous day’s balance to future-dated records (estimated and forecast). For example, you can carry forward yesterday’s opening balance to today.

You can only carry forward actual balances, so you must include the actual balance type in the Worksheet Layout section. If you don’t include the actual balance type, you will get an error when you try to save carry forward balances.

Fields:

• Initiate Across: The balance type that the actual balance should be carried forward to.

  • Estimate

  • Forecast

• Omit Future: Uses the carry-forward calculation instead of the stored balance.

• Offset Calendar Days: Defines at what point to start the carry forward. Select the number of offset days and the type of days (Accounting Periods, Business Days, and Calendar Days).

Custom

Custom defines how amounts, fields and cells are displayed on the worksheet.

Field:

• Amount Display: Choose how amounts are displayed in the worksheet.

  • With Decimals

  • Without Decimals

  • Thousands

  • Millions

• Currency Differences: Mark this checkbox to sum balances and display them as a single total regardless of the currency. For example, even if Japanese Yen and USD are used, they will be shown as a single total.

• Fill Empty Cells: Mark this checkbox to fill empty cells with zeros. This can be helpful when using worksheets as a reporting tool, and less helpful when using a worksheet as a research or validation tool.

• Repeated Accounts: Mark this checkbox to show each occurrence of an account in the worksheet, even if multiple trees use the same account.

  • We recommend unmarking this checkbox when using trees to avoid overinflating the balance. This is a good place to look for troubleshooting if your worksheet figures don’t match balances you see in Balance Explorer.

• Rounding Precision: Mark this checkbox to keep amounts to the precision denoted by the applicable currency (USD has two decimal places). If you have currencies set up to round to nearest whole unit, calculations will be based on these rounded figures.

FX rates

The FX Rates tab allows you to set currency rates on worksheets. When converting into a single currency, certain functionality of a worksheet is restricted. That includes:

• You can’t use the Create Balance column in the User Codes tab, which means you can't store a balance of a foreign currency account into a nominated equivalent currency.

• The worksheet can’t be used for interest allocation because this requires using the Create Balance column to calculate the daily interest.

• You can’t use the Create Recommended Transfer function in the User Codes tab.

• If you select a currency here in the Currency Equivalent field, you must also select Rate Source and Rate Scenario for the currency of the account and the currency equivalent.

Fields:

• Currency Equivalent: Allows you to select a default currency for situations where you have account trees and subtotals with mixed currencies. Leaving this field blank defaults to the currency of the bank account and is referred to as the local currency.

• Actual, Estimated, and Forecast Rate Scenario: Your rate scenarios may differ across transaction types if you use different FX rates for your transactions, such as a month-end rate for actual, a daily rate for estimated, and a third rate for forecast. When converting everything into a single currency (for example, USD), you need to select a rate scenario for all transaction types (actual, estimated, and forecast).

  • If you have subscribed to the REFINITIV market rates service, then use "Reuters" as the rate scenario.

• Actual, Estimated, and Forecast Rate Source: If you use market rates from multiple services, you could have a different rate sources for your actual, estimated, and forecast transactions. When converting everything into a single currency (e.g., USD), you need to select a rate source for all transaction types (actual, estimated, and forecast).

  • If you have subscribed to the REFINITIV market rates service, then use "Reuters" as the rate source.

Groups

You can set up worksheet groups to determine access rights for different groups within your organization. Users with administrator access can secure worksheets for use by specific groups of users. For example, only members of a certain group may be allowed to see a payroll funding worksheet.

When creating a new worksheet, you are required to pick a worksheet group. The Ripple Treasury Platform comes with a default worksheet group called “WORKSHEETS” that you can assign your worksheets to. If you want to organize your worksheets with other groups, you need to set up the worksheet groups before you create your worksheets.

Copyright 2025 Ripple Labs Inc.