Usage Rule Example for Movie Rental Business
When creating and uploading a usage rule for a movie rental business to process the movies rented, the length of the rental, and the type of movie, you would create a Usage Lookup Table with the following name and requirements:
Movies — A two-column table that lists the names of each movie in the first column and the corresponding classification of each movie (either New Rel, Premium, Classic, or Regular) in the second column. The names of the movies must be in the same format as would be specified when entering the usage information (for example, "The Fighter" or "Gone With the Wind"). Although you can use movie names of your own choosing and add additional movies, to explore this example, you would need to create a Lookup Table with the following minimal entries:
| Lookup | Value |
|---|---|
|
The Fighter |
New Rel |
|
The Switch |
Premium |
|
Gone With the Wind |
Classic |
|
On the Beach |
Regular |
| Cell | Explanation | Contents |
|---|---|---|
B4 | Service Resource (User Input). Name of movie. Used to determine type of movie and cost to rent. | Gone With the Wind |
B7 | UOM (Unit of Measure). Indicates that rental calculations are based on the specified UOM (minimum of three days). Informational only. Not used elsewhere. | Days |
B8 | Amount (User Input). Number of days actually rented. Used to determine final cost of rental. | 6 |
B9 | Description. Included on the customer's invoice. | Movie Rental |
D2 | Rule Result. Formula to verify that a movie title is entered. The rule cannot work unless there is a movie title entered. | =IF(B4=""; 0; 1) |
E2 | Rule Rate. A formula to determine the charge for this movie for the time specified. | =IF(D2=1; G7; "N/A") |
G2 | Formula to determine type of movie in a Lookup Table. | =VLOOKUP(B4; $Movies.A2:B22; 2; 0) |
G6 | Label to identify the following four cells. No calculations involved. | Calculations |
G7 | Formula that performs the final calculation to determine cost of movie. | =(G10+((G22-3)*G8)) |
G8 | Formula to determine the daily cost of the selected movie. Totaled contents of cells that identify applicable cost per day. | =SUM(G17:G20) |
G10 | Formula to determine the rate for minimum three-day rate. Multiply contents of G8 by number of days. | =G8*3 |
G11 | Label to identify the following four cells. No calculations involved. | Base Rates |
G12 | Base rate per day for rate "New Rel." No calculations involved. | 2.67 |
G13 | Base rate per day for rate "Premium." No calculations involved. | 1.67 |
G14 | Base rate per day for rate "Classic." No calculations involved. | 0.67 |
G15 | Base rate per day for rate "Regular." No calculations involved. | 0.33 |
G16 | Label to identify the following four cells. No calculations involved. | Movie Type |
G17 | Formula to determine if movie is "New Rel" classification. If "Yes," display the price-per-day. Otherwise display "0." | =IF(G2="New Rel"; G12; 0) |
G18 | Formula to determine if movie is "Premium" classification. If "Yes," display the price-per-day. Otherwise display "0." | =IF(G2="Premium"; G13; 0) |
G19 | Formula to determine if movie is "Classic" classification. If "Yes," display the price-per-day. Otherwise display "0." | =IF(G2="Classic"; G14; 0) |
G20 | Formula to determine if movie is "Regular" classification. If "Yes," display the price-per-day. Otherwise display "0." | =IF(G2="Regular"; G15; 0) |
G22 | Label to identify the following cell. No calculations involved. | Days Rented |
G23 | Formula to determine the number of days rented with a minimum of three days. | =IF(B8<3; 3; B8) |
Column B is reserved for input, column C is reserved for output, column D is reserved for the Rule Result, and column E is reserved for the Rate generated by the rule. Cells in columns G through Z are reserved for rule-related information, formulas, and calculations.
When performing calculations, Gotransverse recognizes only certain functions. For a list of these functions, refer to Global Usage Rule SpreadsheetFunctions.