Using Spreadsheets to Create Estimates

This section provides set up and procedural information for importing and exporting spreadsheets to create estimate requests with multiple products from iQuote, and then export the prices calculated in the estimate.

You can map different types of spreadsheets in the iQuote system so you can reuse customer-provided information from a standard spreadsheet. You can also use spreadsheet templates to map different characteristics per product, and enable iQuote to capture the product information in different formats and spreadsheet designs through those template and other configurations.

Once you create a template and begin the import process, you can use the Request For Quote process to import the spreadsheet and review the information before creating estimates. iQuote also provides tips about incomprehensible characteristics so you can change them. Once the review is complete, you can create an estimate with one click, or create multiple estimates or estimate options from one or multiple spreadsheets.

After you create an estimate, an estimator reviews the information, makes changes as needed, confirms the cost and engineering, and then publishes the price, which updates the quote request. You can then export the calculated price in the estimate into the same spreadsheet format you imported.

The Spreadsheet Export feature is beneficial both to companies with large quantities of data and to customers who want to send a spreadsheet to their printing provider to obtain all completed prices.

This section describes how to create new templates, how to create a new RFQ, how to import a spreadsheet, and how to export the price once the estimate is ready This section also addresses the other tools provided with this enhancement to create and map spreadsheets to system data.

 

Configure iQuote To Import Products And Export Prices

Refer to the following topics for information on how to set up your iQuote system to import products and export prices based on the Request For Quote.

 

Enable Access Controls

Note: Your system administrator must do all the following to enable the necessary permissions on your user profile before you can export and download data from iQuote.

1. Navigate to Administration > User > User Profiles. The User Profile List screen opens.
2. Create a new or select an existing user profile record to which you want to enable access controls. The User Profile screen opens.
3. On the Permissions tab of the User Profile screen, enable the following access controls in the Permissions area:

• Object Status:

‎EPS > Administration > Workflow > Object > Edit Object > Object Status.

• Template:
• EPS > Sales > Records > iQuote > Import Spec > Spreadsheet Template
  • Insert Template – provide access to add new spreadsheet template
  • Edit Template – provide access to edit a spreadsheet template
  • Delete Template – provide access to delete a spreadsheet template, if not being used before
• Request for Quote
• ‎EPS > Sales > iQuote > Request for Quote.
  • Add – provide access to add new RFQ
  • Edit – provide access to edit new RFQ
  • Download Export Attachment – provide access to download the exported spreadsheet with prices
  • Export RFQ Data – provide access to go thru the export the prices process

Note: When you grant permission to edit or add RFQs, you also grant permission to import spreadsheets.

 

1. Click Save to save your changes. The User Profile screen closes, and iQuote saves your changes.

 

Configure The Request For Quote (RFQ) Workflow Status

Do all the following to configure the RFQ workflow status:

1. Navigate to Administration > Workflow > Object. The Object List screen opens.
2. Use the search to locate the Request for quotes Object record and double-click the record or click the Edit button. The Object screen opens.

1. On the Status tab, click the New button. The New Status screen opens.

1. On the New Status screen, complete the following fields, as applicable:

• In the Title field (required), enter a status name of Open.
• In the Action to be activated field, enter a description of the action you want this status to perform when selected, such as Create new.
• Select the Initial check box if you want to make this the automatic default status for new requests.
• Leave the Final check box deselected.
• Leave the Active check box selected to make this status available for use throughout the system.
• In the Tag field, select Open.
• In the Color field, select the color you want to assign to items in this status.
• The Next Status field is unavailable until you add additional statuses for RFQs. Leave this field blank.
• The Triggers area is unavailable until you add Input and Output events to the system. Skip this area.

1. Click the Access Control tab.

1. In the left pane, select a user group to which you want to assign access for this status.
2. In the Access area on the right, select the actions you want to authorize for the selected user group.
3. One at a time, select additional user groups in the left pane and assign access, as needed.
4. Click Save. The New Status screen closes, and the Object screen refreshes, with the status you created in the grid on the Status tab.
5. Repeat steps 3 through 9 to create Status records for Close and Cancel.
6. Click Save. The Object screen closes, and the Object List screen refreshes.
7. On the Object List screen, click Apply Workflow.

Note: Make sure no users are in the system before you apply the workflow, as this may cause issues after iQuote changes the database structure.

 

Create A Spreadsheet Template

Notes:
● You must create a spreadsheet template before you can import the spreadsheets in the RFQ.
● Import and export information are defined in the same template.
● When importing and exporting, iQuote reads only the first worksheet (tab) in a spreadsheet. In the export process, all other tabs with content are retained in the spreadsheet as originally imported.

Tip: Refer to the Spreadsheet layout overview topic for information on how to build a spreadsheet in MS Excel for field mapping in iQuote.

 

1. Navigate to Sales > Records > iQuote > Import Spec > Spreadsheet Template, or enter "Spreadsheet Template" in the Quick Search field. The Spreadsheet Template List screen opens.
2. Do one of the following on the Spreadsheet Template List screen:

• To create a new spreadsheet template, click the New button. The Insert Template screen opens.

• To modify an existing spreadsheet template, double-click the template name, or select the template and click the Edit button. The [Template Name] Template screen opens.

• To delete an existing spreadsheet template, select the template and click the Delete button, then click Yes when prompted to confirm the deletion. The system deletes the template, and the Spreadsheet Template List screen refreshes. In case the spreadsheet template it’s already in use in a RFQ, the template cannot be deleted

1. On the Insert Template screen or the [Template Name] Template screen, complete the following fields, as applicable:

• In the Name field (required), enter a descriptive name to identify this template.
• In the Customer field, select the Customer record to which you want to link to this template.

Note: Templates you link to a Customer record can be used in requests for that customer only. If you want to make a template available for any quote request, leave this field blank.

• In the Product Specification field (required), enter, or use the Advanced Search feature to select, the Product Specification record you want to assign to this template.

Note: The system uses the Product Specification you select to determine which components and characteristics to map on this template.

• In the Measurement System field select whether you want the spreadsheet to use Metric centimeter (cm), metric millimeter, or Imperial measurements.

Important: If the spreadsheet measurement system is different from the one set in iQuote, the system converts the measurements when importing. However, you must be sure the option you select in the template is compatible with the measurement system you plan to use in the spreadsheet you upload to the system.

• In the Version field (required), select the version number of this product specification you want to assign to this spreadsheet.
• In the Spreadsheet Layout field, select the layout that corresponds to the way you created your spreadsheet. (Refer to the Spreadsheet layout overview topic in this guide for more information.)
• In the Last Header Line Number field, enter the number of last header row at the top of the spreadsheet that you want to exclude from the import.
• Select the Active check box to make this template available throughout the system.

1. In the Export Related Information area, complete the following fields, as applicable:

• In the Last Column With Content field (required if you want to enter numbers or leave the Column – Spreadsheet field blank for any exported fields), enter the ID of last column in the spreadsheet that you want to include in the import.
• In the Order When Exporting Last Columns (Per Qty) field, select one of the following values, depending on how you want map multiple quantities in the spreadsheet:
  • Select Order by Mapping Sequence + Quantity if you want to map the data first by quantity, then by each characteristic within the quantity. This enables the system to export the characteristics in the sequence per quantity.
  • Select Order by Same Characteristic + Quantity if you want to map the data first by characteristic, then by each quantity that displays that characteristic. This enables the system to export the same characteristics multiple times based on the number of quantities in the estimate.

1. Map the spreadsheet fields in the Fields Mapping area, using the Map Fields in iQuote topic as a guide.
2. Click Save to save your changes.

 

Spreadsheet Layout Overview

You can use spreadsheet software, such as Microsoft Excel, to build a list of estimates that you can then import into iQuote. To do this, you must organize the product information into one of the following two formats that iQuote can recognize:

• Single component with one line per product
• Multi-components with flat components in each line

Single component format

Spreadsheets that follow the single component format consist of one dedicated line for each final product. Each product consists of a single component, such as a tabloid, a label, or a folder with the corresponding specifications in each of the columns of the spreadsheet. For example:

 

In this example, the Products area of the Estimate Product Specification screen counts each of these lines as one component:

Multi-components format

Spreadsheets that follow the multi-components format include a final product across more than one line, with each line containing the specifications for one of the product’s components. For example:

When you import a spreadsheet with a multi-components format, the order of the components determines the product specification hierarchy. In other words, the system uses the first line as the final product (and is therefore not flat), and evaluates the following lines as children components (flat). For example, a magazine (final product) with a cover and text (children components):

Notes:
● It is recommended that you import no more than 100 products per estimate, to maximize system performance.
● The components and structure in the spreadsheet should follow the same structure you select in the Product Specification of iQuote.
● If the spreadsheet includes fewer components than those in the Product Specification of iQuote, the system adds the “missing” components to the estimate. However, if the spreadsheet includes more components than the Product Specification, the system ignores the extra components on import.

 

Map Fields In iQuote

When you map fields in iQuote, you define how the system should interpret the fields in the spreadsheet so that iQuote can correctly import all the data into the estimate structure. After you create a spreadsheet template, you must do the following to map the fields on the Spreadsheet Template screen in iQuote.

1. Navigate to the Spreadsheet Template screen for the desired template.

The Fields Mapping area displays a list of components on the left and a corresponding grid to map the relevant information on the right.

Note: When you import the spreadsheet, the system ignores any components that do not include mapped fields.

1. Use the up and down arrows in the Fields Mapping area to reorder the components on the left, as needed.
2. In the Fields Mapping area, if your template contains multiple components, the system automatically selects the Final Product check box (not editable for multi-component templates) for the first (main) component only.
3. In the Fields Mapping area, select a component on the left and click the New button on the grid on the right for each spreadsheet column you want to map.
4. For each column you want to map, complete the following fields, as applicable:

• In the Column – Spreadsheet field (required if you want to import the mapped characteristics), enter the ID of the spreadsheet column you want to map. For exporting data, you can leave this field blank, and iQuote exports the next sequential column(s) using the Last column with content field.

Note: You can enter letters or numbers in this column, but not both.

Tip: You can enter a hyphen to designate a range of columns to map, such as A-C or 1-3.

• In the Field Map Type field, select one of the following field types that best describes the column information you want to map:
  • Select Import - Characteristic if you want to map the spreadsheet column to a component characteristic in the estimate.
  • Select Import - Estimate Incremental (applies only to final products) if you want to create multiple estimates from one spreadsheet and map the spreadsheet column to a new estimate ID number.
  • Select Import - Option incremental (applies only to final products) if you want to map the spreadsheet column to an estimate option.
  • Select Export – Estimate Info if you want the system to export specific iQuote estimate information into this column on the spreadsheet.
• In the Characteristics field (not available if you select Import - Estimate Incremental or Import - Option Incremental in the Field Type field), select the component characteristics in this spreadsheet column, such as Substrate, Color, Format, etc.
• In the Characteristic Detail field, the system automatically populates some characteristics based on the value(s) you select in the Characteristic field, such as the component description or quantity. Click the Characteristic Detail button to manually define the characteristic details, such as Quantity, Estimate Number, Estimate Product Name, etc., as needed. (Refer to the Define characteristic details topic for more information.)
• In the RFQ Description Group field, enter the name you want displayed in the in the quote request (RFQ) for imported characteristics, and for exported characteristics this name will the column name exported. In case nothing is typed the system will export the name of the characteristic
• In the RFQ Subdescription field, enter the secondary name you want displayed in the quote request (RFQ).

Notes:
● Estimate Incremental, Option Incremental, and Quantity are values set only for components that are final products. Likewise, the specification characteristics are set only for flat components, and the Component Description characteristic is used for both component types.
● You cannot map the same specification to more than a single column. For example, you cannot map the component’s height to both column A and column B.

Tip: Refer to the following screenshots for an example of a product with three populated components:

 

Define characteristic details

1. In the Fields Mapping area of the Spreadsheet Template screen, click Characteristic Detail to define the specification for a particular characteristic.

The Characteristic Detail screen opens with the different fields that correspond to the selected characteristic.

1. On the Characteristic Detail screen, select the Selected check box for the desired option(s).
2. Click Confirm to save your selections. The Characteristic Detail screen closes, and the option(s) you selected appear in the Characteristic Detail field on the Spreadsheet Template screen.

 

Copy field maps

You can use the Copy Field map To button on the Spreadsheet Template screen to map flat multiple components that are the same or very similar.

1. In the Fields Mapping area of the Spreadsheet Template screen, click Copy Field Map To.
2. Map all necessary columns for one of the flat components, using the Map Fields In iQuote topic as a guide.
3. Click Copy Field Map To.

The Copy Field Map To screen opens.

Important: The component in the Description field is the original mapped component, including all the corresponding populated fields.

1. In the Destination Components area, select the components to which you want to copy the selected configurations. In the example below, the field mapping made to the Cover component was copied to the Text component.

Important: Notice in the following screenshot that the specification made in the cover component for column C were not copied to the text component. This occurs when the system recognizes different structures and characteristic details for the components.
In this case, the cover component is a folded sheet and needs only a specified size. Also, the text component is a signature, and needs a defined number of pages.

1. Verify the system copied all the desired columns in the new component and if necessary, map the missing columns and configure their characteristic details.

 

Create A Request For Quote (RFQ)

Use the Request For Quote List screen to create new, or edit existing, requests, and change a request’s status.

Tip: For more information, refer to the “Standard Toolbar” topic in the Introduction to iQuote.

1. Navigate to Sales > iQuote > Request For Quote, or enter Request for quote in the Quick Search field. The Request For Quote List screen opens.
2. On the Request For Quote List page, click the New button. The Insert Estimate Request screen opens.

1. On the Insert Estimate Request screen, complete the following fields:

• In the Customer field (required), enter, or use the Search feature to select, the Customer record for which you want to request a quote.
• In the Title field (required), enter a descriptive name for this quote request.

1. On the Insert Estimate Request screen, complete the following fields on the Identification tab, as applicable:

• In the Contact field (required), the system automatically enters the primary contact associated with the Customer record you selected in the Customer field. You can select a different contact name, as necessary.
• In the Agency field, enter, or use the Search feature to select, the customer agency you want to associate with this estimate request.
• In the Salesperson field (required), the system automatically enters the primary salesperson associated with the Customer record you selected in the Customer field. You can select a different salesperson name, as necessary.
• In the CSR field, the system automatically enters the primary customer sales representative associated with the Customer record you selected in the Customer field. You can select a different CSR name, as necessary.
• In the Estimator field, enter, or use the Search feature to select, the estimator you want to assign to this estimate request.
• In the Product Line field (required), select the product line, such as sheets or mailing, for this estimate request.
• In the Sub-line Product field, select the secondary product line category you want to include in this estimate request.
• In the Limit Date To Send To Customer field, enter or select the date and time by which this estimate request should be sent to the customer.
• In the Project field, or use the Search feature to select, the project you want to associate with this estimate request.

Note: You can select projects associated with the customer record you select in the Customer field only.

1. Once a customer is selected some fields in the Identification tab will be automatically populated: Contact, salesperson and CSR. Check to see if the data is correct and fix it if necessary.
2. Click Confirm to save your changes. The Estimate Request screen refreshes, with the Specification and Attachments tabs.

Tip: Navigate Sales > iQuote > Manage open requests for quote to view or close any open requests for quotes.

‎

Import Specifications From A Spreadsheet

Tip: Whenever you import a spreadsheet, iQuote saves a copy in the Quote Request (RFQ) record. When you create an estimate from an RFQ, the system also records all the corresponding data in the respective RFQ line and assigns each product its own unique ID. This enables iQuote to export a spreadsheet coherent with the imported data and generate new estimates. Refer to the Exported Data From iQuote topic for additional information.

On the Estimate Request screen, click the Specification tab.

1. In the Template field, select one of the pre-defined templates that corresponds to the product type you want to use in this request.

Note: You can select only general templates not linked to a Customer record, or those templates linked to the Customer record you selected in the Customer field.

1. Click Import Spreadsheet.

Note: Skip to step 6 if you prefer to enter all the product data based on the template, rather than upload a spreadsheet.

The Import Spreadsheet screen opens, with the template you selected in the Template field.

 

1. On the Import Spreadsheet screen, in the Spreadsheet field (required), drag the spreadsheet file you want to use for the import from a file browser window, or click the paperclip icon in the field and select one of the following:

• Select Upload File if you want to select a spreadsheet file to import from your computer.
• Select Clear File if you want to clear the file already populated in the field.
• Select Download File if you want to download the file already populated in the field to your computer.
• Select Cancel file if you no longer want to use the file listed in the Spreadsheet field.

1. Click Confirm to save your changes and upload the selected spreadsheet. The Import Spreadsheet screen closes, and the system displays the spreadsheet components in the Components area of the Specification tab on the Estimate Request screen.

Tip: The Estimate column contains the estimate number, the O column includes the number of options, and the each quantity should have its own column. Additionally, the system indicates final products in bold type.

1. Edit the data in the columns, as needed.

Tip: Edited items appear in blue.

1. Click any of the flat components to display its corresponding characteristics, as defined in the spreadsheet, in the Component Characteristics area on the right.

1. Review the Status column in both the Components and Component Characteristics areas. The colors indicate one of the following statuses:

• Red: Invalid. Click the component and review the statuses of the corresponding characteristics to correct the issue(s).
• Yellow: Partially valid/warnings apply. Click the component and review the statuses of the corresponding characteristics to correct the issue(s).
• White: Not validated. Click the component and review the statuses of the corresponding characteristics to correct the issue.
• Green: Valid

Notes: Click Check Content in the Components area to process and convert again all characteristics of all components with a status other than Valid (green).

 

1. For any component with an invalid, partially valid, or not validated status, select the component in the Components area, then select the invalid characteristic in the Component Characteristics area and click Edit Characteristic.

The Edit Characteristic screen opens, with a list of fields specific to the characteristic you select.

1. On the Edit Characteristic screen, enter or modify the field values, as needed, then click Confirm to save your changes.

Tip: Select the Substitute spreadsheet content on all components with the same information check box to apply your changes to all components that are identical to the one you modify.

1. Repeat this procedure for all components and characteristics, as necessary, then click Create Estimate at the top of the Edit Estimate Request screen.

1. On the screen that will open, click Confirm to create the estimate.

The system creates the estimate and assigns a title of [RFQ Name] + [Product Name].

1. Navigate to Sales > iQuote > Estimate to verify that the estimate includes all the data from the spreadsheet.

1. Add or modify any options, quantities, or specification, as necessary, then calculate the estimate per your usual workflow.
2. Click View RFQ in the Estimate toolbar to review the original quote request from the Estimate screen.

Note: Do all the following on the Edit Estimate Request screen to upload a new file:
1. Click Import Spreadsheet. The Import Spreadsheet screen opens.
2. Complete the Spreadsheet and Template fields, as needed.
3. Select the Replace Current Data check box if you want the system to erase all current information and replace it with the data from the new spreadsheet.
4. Select the Import To New Estimate check box if you want the system to create new items in the Components column.

 

 

Export Data From iQuote And Download Import/Export Attachments

In addition to importing data from a spreadsheet, you can also export data from iQuote into a new or existing spreadsheet as well as download previously exported spreadsheets.

Note: You must have specific security permissions linked to your user profile to export and download data from iQuote. Contact your system administrator to enable permissions for Sales > iQuote > Request for Quote > Export RFQ Data and Sales > iQuote > Request for Quote > Download Export Attachment.

Navigate to Sales > iQuote > Request For Quote, or enter Request for quote in the Quick Search field. The Request For Quote List screen opens.

On the Request For Quote List screen, double-click the desired RFQ, or select the RFQ record and click the Edit button. The Edit RFQ screen opens.

On the Edit RFQ screen, click Export Data. The Export Data screen opens, with a list estimates previously created based on this RFQ, and the status of each estimate.

On the Export Data screen, select the Replace Current File check box if you want to replace the data in a previously exported spreadsheet with the current data. Leave the Replace Current File check box deselected if you want the system to create a new spreadsheet upon export, rather than replace the existing one.

Tip: Select the Replace Current File check box to minimize the database size.

In the Estimates To Export area, the system will show all estimates created based on the spreadsheet imported. The ones marked as Yes in the Ready To Be Exported, have already the price calculation ready to be exported field to export the data as a spreadsheet. In that case the system will export the price just in the products where the price is done. In the ones that the price is not ready, the products will not have the price exported. You can export price as many times once all estimates are ready.

The system determines the estimate’s export readiness based on the estimate’s status tag, as follows:

Estimate Status Tag  Ready To Be Exported Field Value

0 – Preparing   No

1 – Calculating   No

2 – Defined Price   Yes

3 – Approved by customer  Yes

4 – Approved    Yes

5 – Canceled    No

6 – Negotiating Price   No

7 – Approved: Partial Job  Yes

8 – Approved: Released Job  Yes

9 – Prospective   No

Click Confirm.

The system exports the data in spreadsheet format, according to the spreadsheet template of the last imported spreadsheet, and stores the spreadsheet in the Exported Attachments area of the Edit RFQ screen.

To download imported or exported spreadsheet data, click the corresponding link in the Imported Attachments or Exported Attachments area.