Freight – Creating a freight pricing spreadsheet

Freight – Creating a freight pricing spreadsheet

 

Step 1. On the manage freight screen, Click on the template button.
This will export the template spreadsheet for you to add all of your freight pricing information into. (See screen shot below)

 


 

When you open the exported spreadsheet there are already two options in the spreadsheet. These options are ‘Custom’ and ‘Pickup’, they are there for you to use as a guide.
Keep them in the spreadsheet as you will use these options as well.

 


 

Step 2. Filling out the spreadsheet.

The first column in the spreadsheet is ‘Freight Co’ this is the code for the freight company. When you uploaded your freight suppliers they would have been uploaded with a code, we link the freight to the freight company using the ‘Freight Co’ (Freight Company Code). In this example I am using the freight company of Australia Post. (See screen snap below)

 


 

 


 

 

The second column in the spreadsheet is ‘Zone Name’ this field is to specify a zone. You can have the zone be anything. In this example the zone is ‘QLD’. (See screen snap below)

 

 


 

 

The next fields ‘postcode from’ and ‘postcode to’ isn't from ‘you’ to the ‘delivery address’, it is the range that the freight supplier sends to.

For example if the freight supplier delivers anywhere between postcodes 4000 to 4999. You enter 4000 in "postcode from" and 4999 in ‘postcode to’. (See screen snap below)

 

 


 

The field ‘Courier Name’ is where you enter the name of the courier (Freight Company). In this example it is Australia Post. Remember the ‘Freight Co’ and the ‘Courier Name’ need to match up as it is in the system. (See screen snaps below)

 

 


 

 


 

The field ‘Deliver From’ is your postcode where the job is being delivered from, generally your factory’s postcode.

 




 

The field 'Delivery Method' is the option that shows for the person quoting when they select a freight option from the dropdown. We recommend keeping this general in nature as adding versions for each freight supplier makes sense when an estimator is quoting but not when a customer or sales rep is quoting. (See screen snap below)

We suggest the following ‘Methods of Delivery’:

Custom –       Enter in a $0 and you would manually add a freight price

Pickup –         Enter in a $0

Standard –     Enter in the supplier pricing that you would use 80% of the time for this method

Priority –         Enter in the supplier pricing that you would use 80% of the time for this method

 

You can add other delivery methods or a variation on these names but this is the most straight forward setup. In this example we used ‘Standard’ (See screen snap below)

 

 


 

 


 

The field ‘base price’ is if you want to use a base price for the pricing. It is the base charge for the delivery. You don’t have to use this. This is just one of the options for pricing the freight.

 

The field ‘price per kg’ is the additional charge per kg on top of the base charge. You don’t have to use this it is just one of the options for pricing the freight.

 

The fields ‘Base Kgs’ and ‘Max Kgs’  are Minimum and maximum weights, these are to add in weight ranges if you wish. Setting minimum and maximum kgs for certain couriers and change pricing dependent on postcode ranges as well.

 


****Keep in mind here we are entering freight to calculate a price to add to the quotes that cover your costs plus markup. We are not looking to enter all your various suppliers pricing options. For each delivery method, use the pricing structure that you would use 80% of the time. With the Delivery methods we can't have two different sets of pricing. For example if you have standard delivery on both Startrack and Australia Post you can't have different prices for the same area code, weight range and delivery method. Just use the main supplier that you would normally use. From a customer's or sales rep's point of view, when finishing off the quote they just see standard delivery so they just choose the delivery method not the courier.
You can have different suppliers linked to the one freight 'method' but these need to be separated by a weight range so IQ knows what option to choose based on the job weight. So for example 0kg to 50kg may be quoted with Australia Post rates and then 50.001kg to 100kg may use TNT pricing structure****


 

 


 

The field ‘TAT’ stands for ‘Turn Around Time’. Add the average number of days the delivery method will take.

 


 

The field ‘Markup’ is for adding a percentage mark up to your freight if required.

 

The field ‘Fuel Levy’ is for adding a percentage mark up for a fuel levy if required.

 

 


 

 

Step 3. Now that you have completed the spreadsheet, the final field ‘XML’ has a special formula to create the freight pricing into the system. The first two lines ‘custom’ and ‘pick up’ have the formula already built into them. All you have to do is copy the formula and paste it into the new lines that you have created. (Follow screen snaps below)

 

 


 


 

 


 

Step 4. Once all of your lines have the correct XML formula to them you are now able to upload into IQ. Copy all XML lines from the spreadsheet.

 


 

Step 5. In the manage freight screen, where you downloaded the spreadsheet template. Click on the upload button.

 


 

Paste the copied XML formulas into the upload screen. Please read the warning in red. The warning states the following: ‘You are about to replace all the current freight rules in the system with the ones you have provided, would you like to proceed?’ If yes click save and close. One more warning will pop up at the top of the screen Warning you for the final time that you are about to replace all current freight rules with the ones you have provided. Click OK and the freight pricing will be uploaded into your system.

 


 


 

 



    • Related Articles

    • Deliveries - Upload Shipping/Dispatch Information on Quote and Job Details

      Introduction Often job can require multiple deliveries. These can be added manually but they can also be done in bulk, via a csv upload. The upload can be completed at both quote and job level. Quote On the quotes ‘Order Details’ page, the ‘Freight’ ...
    • Dispatch Board - Freight by Product

      Overview This article covers the Dispatch Board for systems configured to price freight by product (‘Old’ Freight). For systems using freight by cart, refer KB article Dispatch Board – Address (Cart) Based ‘Freight by Product’ is basically a freight ...
    • Freight - How to BULK Update Freight Pricing

      To adjust the freight rates, go into ‘Manage Rates’ under ‘Admin, Freight’.       When you are updating freight rules, follow the steps as shown:   Step 1: "Export" current freight rules to CSV         Step 2: Modify freight in exported CSV       ...
    • Dispatch Board – Freight By Cart - Address (Cart) Based

      Overview This article covers the dispatch board for systems configured to price freight by cart (‘New’ Freight) For systems using freight per product, refer KB article Dispatch Board - Freight by Product Freight by cart allows for more granular ...
    • Freight - 3rd Party Shipping

      Introduction 3rd party shipping allows printIQ to use ‘Customers Supplied Credentials’ for shipping rates and services. Essentially the shipping is being booked on and invoiced directly to the customer from the freight provider. When a 3rd party ...