Rental Rate Simulation Spreadsheet

This utility provides the ability to run price change test simulations to determine the impact on rental revenue when a different special rate structure is applied, based on past invoice billings from contracts over a selected period.

The resulting spreadsheet includes a tab summarizing rental revenue by Product Class and a tab summarizing rental revenue by Group. A third tab is optional that can be generated by the Include Detailed Product Tab option to display the supporting invoice details for each product.

FROM INVOICE DATE

Accept the first day of the previous month as the starting point for the actual invoices to consider in the simulation, or enter a preferred starting point.
Only posted invoices generated from cycle billing or rental return of a rental contract will be reflected in the pricing simulation.

Note: Miscellaneous Invoices are not included as these invoices do not always track rate details.

TO INVOICE DATE

Accept the last day of the previous month as the cut-off date for the actual invoices used in the simulation or enter a preferred ending date.

REPORTING SET, "ALL" OR "SEL"

To utilize the Set filter choose one of the following:

• Leave this field blank or type ALL to include products from all Reporting Sets.
• Enter a single specific Set code to include only products in that set, or select one from the Reporting Set Search window.
• Type SEL to select multiple Sets or to select Sets by Owner as outlined in Select Reporting Sets.

PRODUCT CLASS, "ALL" OR "SEL"

To utilize the Product Class filter choose one of the following:

• Leave this field blank or type ALL to include products from all Rental Product Classes.
• Enter a single specific Product Class code for the spreadsheet, or select one from the Product Class Search window.
• Type SEL to make a selection of classes as outlined in Select Product Classes.

PRODUCT GROUP, "ALL" OR "SEL"

To utilize the Group filter choose one of the following:

• Leave this field blank or type ALL to include all Rental Groups in the simulation spreadsheet.
• Enter a single specific Group, or select it from the Group Search window.
• Type SEL to make a selection of Groups as outlined in Select Groups.

DIVISION, ALL, OR SEL

• Type ALL to include invoices from all divisions in the spreadsheet.
• To generate the spreadsheet for invoices from a single division, leave this field blank for the Head Office, or enter a specific division code, or select a division from the drop-down list.
• Type SEL to select multiple divisions or divisions by accounting region to include in the spreadsheet, as outlined in Division Select By Region.

Invoice Selection by Rates Billed:

The invoice information reflected in the simulation spreadsheet can be filtered by the following check boxes, so that only invoice line items that match the check box selections are included in the totals calculations.

INVOICES BILLED STANDARD RATES

Check this box to include Standard Rates for products or for groups, including any 'Divisional Special Rates' defined as standard by the Rental Rate Level selected for a store in the Division Rate Parameters.

Uncheck this box if standard and Division defined special rates should not be considered in this simulation test.

INVOICES BILLED TIER 1 RATES

Check this box to include products invoiced at First Tier Rates as defined by Groups for each customer in the Multi-Tiered Customer Special Pricing, when the Multi-Tiered Pricing feature has been activated in the Company Rate Parameters.

Uncheck this box if Tier 1 rates should not be considered in this simulation test.

Note: The three "Tier" level filters are enabled even if the Multi-Tiered Pricing feature is not active, because pricing data is always stamped with the source even if multi-tier is turned off.
When tiered pricing is active, the price source of Tier1, Tier2, Tier3, Division, or Standard rates, is captured and when tiered pricing is not active, the price source of Customer, Division, or Standard rates is captured.

INVOICES BILLED TIER 2 RATES

Check this box to include products invoiced at Second Tier Rate Levels as defined for each customer in the Multi-Tiered Customer Special Pricing, when the Multi-Tiered Pricing feature has been activated in the Company Rate Parameters.

Uncheck this box if Tier 2 rates should not be considered in this simulation test.

INVOICES BILLED TIER 3 RATES

Check this box to include products invoiced at Third Tier Rate Levels as defined for each customer in the Multi-Tiered Customer Special Pricing, when the Multi-Tiered Pricing feature has been activated in the Company Rate Parameters.

Uncheck this box if Tier 3 rates should not be considered in this simulation test.

INVOICES BILLED CUSTOMER RATES

Check this box to include products invoiced at the Customer Special Rates, when Multi-Tiered Pricing is NOT active.
Uncheck this box if Customer Special Rates should not be considered in this simulation test.

INVOICES BILLED SPECIAL RATE, "ALL" OR "SEL"

To further filter the invoice special rates, choose one of the following:

• Leave this field blank or type ALL to include all Special Rates on invoices in the simulation.
• Enter a single specific Special Rate, or select it from the Special Rate Level Search window.
• Type SEL to make a selection of Special Rates to include, as outlined in Select Special Rate Levels.

Example Use of this Filter:
To see the impact of charging a 'Level 2' rate instead of a 'Level 1', uncheck the Invoices Billed Standard Rates and enter the 'Level 1' as this Invoice Billed Special Rate to select only products that billed at rate 'Level 1'.

SPECIAL RATE LEVEL TO USE FOR SIMULATION

Enter the specific Special Rate to use for the new pricing in the simulation, or select it from the Special Rate Level Search window.
This Rate Level will then be used to calculate how the billings would have been invoiced at this level.

Note: Only products and groups which are included in this Special Rate Code will be included in the resulting spreadsheet.

INCLUDE CUSTOMER DISCOUNT PERCENTAGES

Check this box if the simulated calculation should reflect any customer defined rental discount percentages that have been assigned on the "document header" and that have been applied to the product detail lines.

Uncheck this box if discount percentages from the detail lines should not be reflected in this simulation results.

Note: Any rental discount percentages applied to the product detail rates per the Product / Group Special Rate Discounts setup in the Special Rate Code are always applied in the simulation, as these rates are considered standard.
The Product / Group Special Rate Discounts can be used when the Enable Discount % By Special Rate Level feature is activated in the Company Rate Parameters.

INCLUDE DETAILED PRODUCT TAB

Check this box to generate a third tab on the spreadsheet showing the formula used to calculate the actual and the simulated rental charges for each rental item on each invoice, sorted by Class, Group and the product.
The invoice number is listed with the sequence number to identify the detail line on the invoice.

Uncheck this box to suppress this third tab, as this could generate a large spreadsheet.

INCLUDE CALCULATION DETAILS

This option is only available when the Detail Product Tab is being generated.

Check this box to include the full rate structure text showing Months/Weeks.Days for each invoice detail to support how the billed amount was calculated on the Details tab.
Uncheck this box to suppress the rate structure text breakdown on the Details tab.

IGNORE INVOICES THAT DON'T CHANGE

Check this box to exclude invoice details that would be billed at the same amount.
Uncheck this box to include items where the billing would not be changed on the spreadsheet.

Finished?

Click the ACCEPT button to generate the spreadsheet output, or CANCEL to abort.