Usage Rule Example for Dog Walker

This example uses multiple data values in the Lookup Table.

When creating and uploading a usage rule for a dog walking business, you would create a Usage Lookup Table with the following name and requirements:

Customers — For regular customers that have a formal agreement or contract, a three column table that lists the name of each customer in the first column, the corresponding agreement or contract in the second column, and the cost assigned to that agreement or contract in the third column. This example uses agreements or contracts that are set up both for differing lengths of walks (for example, 15 minutes, 30 minutes, etc.) and for differing frequency of walks in a week (for example, three times a week, five times a week, etc). For customers without an agreement or contract, an additional entry (not included in this example) would be needed to account for the unscheduled dog walk. Although you can use customer names of your own choosing and add additional customers, to fully explore this example, you would need to create a Lookup Table with the following entries:

Lookup Table Example

Lookup	Agreement	Cost
Jones, John	3-day 15	15
Jones, Jack	3-day 30	17
Jones, Jimmy	3-day 45	20
Jones, Jenny	3-day 60	25
Smith, Jack	5-day 15	12
Smith, John	5-day 30	15
Smith, Dean	5-day 45	18
Smith, Janice	5-day 60	20
Black, John	7-day 15	10
Black, Jack	7-day 30	12
Black, Bill	7-day 45	15
Black, Jim	7-day 60	19

The spreadsheet for this example would include the following information:

Spreadsheet Cell Contents

Cell	Explanation	Contents
B2	Name of dog care plan. Formula to perform a Lookup in the Customers Lookup Table.	=VLOOKUP(B4;$Customers.A1:B13;2;0)
B4	Service Resource (User Input). Name of customer. Used to determine the dog-care plan.	Smith, Jack
B7	Unit of Measure. Calculations are based on minutes of walking time (minimum of 15 minutes, maximum of 60 minutes). This cell in informational and is not used elsewhere.	Event
B8	Amount. Specify the number of dogs being walked. If the number in this cell is more than "1," the rate increases by 20%.	1 or 2
D2	Rule Result. Formula to verify that the name of a customer has been entered. The rule will not work unless a customer is entered.	=IF(B4=""; 0; 1)
E2	Rule Rate. Formula to determine the charge per dog walk, related to the plan identified in B4 by performing a "lookup" in the Customers Lookup Table. If the walk involves more than one dog, the rate is automatically adjusted by an additional 10%.	=IF ((B8=1);(VLOOKUP(B2; $Customers.A2:B14; 3; 0)); (VLOOKUP(B2; $Customers.A2:B14; 3; 0))*1.1)