Calculation Sheets

A calculation sheet is a tool used to determine the selling prices of a tour package for a group of passengers travelling together. This tool is used by tour organizers who need to regularly prepare quotations for the tours they create.

The costs include both fixed prices applied to the group as a whole (such as a guide and a bus) and variable prices applied to each individual passenger (such as hotel accommodation and activity entrance fees). The calculation sheet helps you come up with a selling price per passenger according to group size. The selling price is based on these costs, as well as additional pricing information: markup, rounding policy, staff costs and free-of-charge passengers you add to the group according to group size.

The calculation sheet module also allows you to produce a day-by-day itinerary which can accompany the quotation.

Once the calculation sheet is created, you can print the quotation and itinerary and send them  to the person requesting the tour. After the calculation sheet quotation is approved, a group travel file based on the calculation sheet components can be created in the Reservations System where you can manage the actual trip.

You can copy and revise quotations as necessary so that you have a history of all quotations.

Prerequisites

For more information see, Calculation Sheets Settings

Before you can start working with calculation sheets, you need to define in the database definitions the parameters for working with the module. Some parameters are fixed and cannot be changed from within the calculation sheet. Others, are default settings that can be edited for each individual calculation sheet.

These definitions include:

• Hotel accommodation pricing method for tour staff members (bus drivers, tour guide, and company escort)

• Price rounding policy

• Default amounts of paying passengers and free of charge passengers.

• With groups, you may be asked to add a number of free passengers if the group is large enough. For example, for 20 paying passengers, you will allow 1 free passenger and for 40 paying passengers you will allow 2 free passengers.

• Default guide and transport price lists to attach to the calculation sheets. These price lists are automatically attached to the calculation sheet's itinerary, where you can track the total number of full days, half days and transfers for each day.

• Default numbers of staff in single and double rooms

• Default calculation sheet mode. There are two calculation sheet modes:

• • Totals are based on the sum of net prices plus markup: The tour cost is based on the sum of supplier (net) prices + markup. Travel files created using this calculation sheet mode include a single gross transaction which consists of all the net transactions in the calculation sheet, and markup is applied to this gross transaction.

  • Totals are based on the sum of all gross prices (standard group): The tour cost is based on the sum of all selling prices. Travel files created using this calculation sheet mode display the selling price for each service transactions all of which are standard transactions.

• Default markup to be applied to the total net cost in the calculation sheet. The markup can be a percentage and/or a fixed amount. The currency of the fixed amount is the selling currency of the calculation sheet.

• Price formulas which are used to create standard base prices and supplements, which can comprise multiple service types. Price formulas are used to define how the price components are calculated for each service type.

• Before you can begin creating price formulas, you are going to need to select the service type of the transaction that is going to be created in travel files that are opened through a calculation sheet: package or escorted tour.

Click here for more information on price formulas.   In the image above, Escorted Tour has been defined as the selling price transaction. If the selling price of the tour package is based around a single Base Price: With hotels, the base price is built around a person in a double room, and therefore the formula is: 1*Person in DBL/TWIN/1 With activities the base price is built around a price per adult, and therefore the formula is: 1*Per Adult/1. If you want to add a supplement Child Reduction (12) that applies only to children under 12 sharing a room with 2 adults: With hotels, the child reduction supplement is built around the difference between the price of the child and the price of a person in a double room, and therefore the formula is:  1*1st child share/1-1*Person in DBL/Twin/1 With activities the child reduction supplement is built around the difference between the price of the child and the price of the adult, and therefore the formula is: 1*Per Child/1-1*Per Adult/1. In the calculation sheet the Base Price for each paying passenger and the Child reduction (12) supplement are comprised of these two formulas. The calculation sheets now have a single base price and a supplement (in a negative amount) that represents the supplementary charge that is deducted from the base price. Each formula can be broken down as follows: The number by which the price component is multiplied The price component (Person in DBL/Twin, 1st Child Share, Per Child, Per Adult) The number by which the result of steps 1-2 is divided

Creating a calculation sheet

For more information see, Operations - Calculation Sheets

After the database definitions are defined, you can use the Calculations Sheet module to create calculation sheets for your tour.

Each calculation sheet is divided into four tabs:

1. Information

2. Price Definitions

3. Itinerary

4. Calculation Sheet

Information

Click here for an image.

In this tab you enter the general information on the calculation sheet, which includes:

• Calculation sheet name, usually the tour name

• Name of the person requesting the calculation sheet quotation

• If relevant, the travel agent associated with the tour

• The number of staff members in single and double rooms

• Notes which can be printed on the itinerary produced from the calculation sheet (but not on the itinerary produced from the group travel file).

Also, in this tab you can view the following information:

• The calculation sheet number

• Travel file number of the group travel file created from the calculation sheet

• The date on which the calculation sheet was created

• The date on which the calculation sheet was last updated

Price Definitions

Click here for an image.

The next step in creating a calculation sheet is to define the price definitions, which include:

• The calculation sheet's selling currency

• Calculation sheet mode.

• Markup

• Guide and transport price lists

• Group sizes comprised of both paying and free passengers. For each group of X paying passengers you can add Y free passengers.

The default values of the last four parameters are actually defined in the database definitions as mentioned previously, but this can be changed in the Price Definitions tab.

Itinerary

Click here for an image.

The next step in creating a calculation sheet is to create an itinerary.

The first step is to define the beginning and end dates of the trip. After this information is defined, you can create the day-by-day itinerary with its daily components. Services can be added to the itinerary from a price list or manually. After the services have been added you can edit the individual days of the itinerary to add a description of the activities that will take place that day,  change the hotel board basis, and edit the duration of the bus and guide. It is also possible to attach pre-defined remarks.

Calculation Sheet

Click here for an image.

After defining the tour information in the previous three tabs, you can view the actual costing in the Calculation Sheet tab. This tab is also used to print the calculation sheet: proposal, itinerary and other internal printing options.

The calculation sheet is divided into four sections:

1. Group costs: Group costs are fixed costs that are divided among all the passengers of the group, such as transportation services, guide services and staff expenses.

Click here for a detailed example. In the above image, there are three services that affect the entire group: Transport/tour, Guide/Assistance and Staff expense. In this case, the cost of the transport tour goes up according to the size of the group. The cost of the guide/assistance and staff expenses remain consistent across all group size options. The total of these costs is then divided among all paying members of the group to give you a cost per person for all the group costs. In the middle column for a group size of 20 paying passengers and 1 free of charge passenger, the costs are as follows: Transport/tour: 765 EUR Guide/Assistance: 690 EUR Staff Expense: 1320.54 EUR The total of all group costs is: 2775.54 EUR. Divide this amount by the group size (20), and the cost per person is 138.77

2. Individual costs: Individual costs are the variable costs that are applied individually to each passenger, such as hotel accommodation, entrance fees and meals.

Click here for a detailed example.   In the image above, there are two services whose cost is determined by the number of individuals in the group: Hotel and Activities. To this you need to add the cost of including 1 free passenger in the relevant paying passengers' columns. The cost of the free passenger is divided among all the other members of the group. In the middle column the individual costs for a group of 20 paying passengers and 1 free passenger are as follows: Hotel: 358.41 EUR Activities: 94.91 EUR The total cost of these two services is 453.32 EUR. To this you need to add Add for free cost, which is the cost of the free passenger divided among the group of 20 paying passengers: 453.32/20 = 22.66 EUR. Therefore the total cost per passenger is 475.98 EUR (453.32 + 22.66). However, this is not the only option for including free passengers: The suppliers include free passengers in their contract, and you choose to offer them to the group. In this case no extra charge is applied to the rest of the paying passengers. The suppliers include free passengers in their contract, and you choose to not offer these free passengers to the group. In this case, the system divides a negative amount among the paying passengers to indicate that a free passenger is being used to subsidize the total net cost. Some suppliers include free passengers in their contract, others do not, and you choose to offer the free passengers to the group for the free services only. In this case, the cost of the free passenger is divided among the rest of the paying passengers only for the services whose suppliers do not grant free of charge passengers. You only offer free passengers for some of the services you received for free from suppliers. In this case, the negative amount only comes from the services whose suppliers grant free passengers.

3. Totals: This section lists the total net cost for each passenger the total markup and the total selling price per person to which the rounding policy is applied.

Click here for a detailed example. In the image above, in the middle column for a group of 20 paying passengers and one free passenger, the totals are as follows: Cost per person: 614.75 EUR. This can be broken down as the total group cost per person (138.77) and the total individual costs per person (475.98). Markup:  163.78. This includes a 10.00 EUR fixed amount, and a 25% markup (153.68) Total selling per person: 779 EUR. This is the cost per person + markup (614.75 + 163.78 = 778.53) to which you apply your rounding policy. The rounding policy in this instance is Full Rounding rounded Up, which is how 778.53 is rounded to 779.

The columns in each of these sections represents the cost according to the number of paying and  free passengers as defined in the Price Definitions tab (the left number represents paying customers, the right number represents free customers).

4. Supplements: This section lists the supplements that can be added to or deducted from the base cost for each passenger, such as person in single, child reduction, half-board supplement, or 3rd passenger reduction. The list of supplements is taken from the formulas you defined in the database definitions.

Click here for a detailed example. In the image above there are four supplements: Half Board, Child Reduction (12), 3rd Pax Reduction, and  Single Supplement. In this case, no markup has been applied for the supplements so the net price is the same as the total.

The columns in this section represent the net cost, markup and total selling price for each supplement.

Printing the calculation sheet

There are four options for printing the calculation sheet:

• Itinerary: Print the itinerary of the calculation sheet.

Click here for an image.

• Proposal: Print the price proposal of the calculation sheet.

Click here for an image.

• Send proposal by email: Upload an XSL proposal template and enter predefined remarks to automatically appear in the e-mail. It is possible to upload a file and define these remarks per each required language. If no file has been uploaded, the default system file will be used. The file can be uploaded at Setup - Database Definitions – Proposal Travel File.

• Totals - for internal use: Print the calculation sheet that is displayed in the calculation sheet tab

Click here for an image.

• Full calculation - for internal use: Print a detailed breakdown of each service type and its costs

Click here for an image.

Creating a Group Travel File

Click here for an image.

After the client has approved the proposal, you can create a group travel file for the tour based on the prices in the calculation sheet.

Depending on the calculation sheet mode, a gross transaction is going to be created representing the selling price along with net transactions, one for each service representing the supplier price. In the travel file itself, for each hotel accommodation two transactions will be opened: one for customers and one for staff.

Initially, the group travel file is only going to contain the service transactions. Later you can create reservations, manually add the passenger's names and issue documents when it becomes relevant.

Integrating Calculation Sheet and Self-Operation Tours

It is possible to integrate two mechanisms: Calculation Sheet and Self-Operation Tours.

This combination enables you to use the advantages of each mechanism:

• For Calculation Sheet, you can create a proposal, especially for group tours, including planning of a daily route while calculating the group expenses, the individual expenses and changing numbers of participants.

• For Self-Operation Tours, you can register participants and reduce available spots from the tour.

The combined work on these two mechanisms involves the following actions:

1. Creating a Calculation Sheet including all components of the tour (accommodation, attractions, transportation, guide, staff etc.) and sending the proposal.

2. Once the proposal is approved and executed, two actions take place:

a.     A travel file that does not mention number of participants is opened from Calculation Sheet. All transactions are performed in this travel file.

b.     A tour is created in Self-operation Tours, where the price of the tour per participant is set. The tour is connected to the number of travel files opened in Calculation Sheet.

Click here for an image.

3. Then, every time the tour is sold a travel file is opened for the individual participant and available spots are reduced from the tour accordingly.

4. At the same time, every new participant is also added to the group travel file.

5. The transaction in the group travel file are updated according to the registration:

a.     For transactions of individual services (like hotels or attractions), the participants are also added to the transactions. This makes it possible to issue Rooming List for each hotel or Passenger List.

b.     For transactions of group services (like tour guide or bus services), only the number of participants are updated, which makes pricing and reservations more accurate, for example in case of an increased number of participants making it necessary to change the bus service from a mini-bus to a normal-size bus.

c.      The staff hotel will be updated with the right number only after the names of the staff members are taken out of the travel file. Identification is done through the Title function of Staff, Guide or Driver.

6. Group management and revenue is handled from the group travel file (voucher issuing, sending reservations, and transferring balances from passenger travel files)