WEBVTT 00:00:00.066 --> 00:00:09.066 In the previous video we discussed some Excel pricing basics and downloaded the Excel Pricing Templates from the uStore Admin back office. 00:00:09.066 --> 00:00:16.499 In this video we take a closer look inside the Excel file used for pricing static and dynamic products 00:00:16.500 --> 00:00:22.666 to understand what all the fields are and how you create formulas to change the price of your products 00:00:22.666 --> 00:00:26.132 and control which product properties are displayed. 00:00:26.133 --> 00:00:33.766 There are separate videos to look at using Excel pricing with Upload and Easy Upload product types. 00:00:33.766 --> 00:00:38.599 There is also a video about best practices for Excel pricing. 00:00:38.600 --> 00:00:43.833 This will help you to understand how one spreadsheet can be used to price many products 00:00:43.833 --> 00:00:50.399 and give you some tips so you don’t have to edit the spreadsheet each time you add a new product to the store. 00:00:50.400 --> 00:00:53.500 Here is the Excel that I downloaded earlier. 00:00:53.500 --> 00:01:03.500 In general, uStore will input values about the product and the customer into these cells, and information about product properties here. 00:01:03.500 --> 00:01:10.966 You can use these values to calculate the item price which uStore will read back from these cells. 00:01:10.966 --> 00:01:21.832 If you want to run profit reports, you can also return to uStore your cost price, and a custom value which is also available for your reporting. 00:01:21.833 --> 00:01:30.233 uStore will also read back these output values to control which properties and property values are displayed to the customer. 00:01:30.233 --> 00:01:36.266 Note that the Excel output properties cannot be used if the property is a sub property, 00:01:36.266 --> 00:01:41.599 a dependent dropdown list, or configured to take values from a datasource. 00:01:41.600 --> 00:01:48.300 The remaining cells are used to help with the price calculations. More on these later. 00:01:48.300 --> 00:01:52.600 There are several notes in the sheet indicated by the red triangle. 00:01:52.600 --> 00:01:59.200 These are there to help you understand what is in each cell, or how the information is formatted. 00:01:59.200 --> 00:02:06.066 To help you check your formulas and test different pricing scenarios, you can enter values into the input cells 00:02:06.066 --> 00:02:10.499 to simulate uStore entering the values into the sheet. 00:02:10.500 --> 00:02:21.800 For example, let's say that user id 1, which is the admin user, was ordering a quantity of 10 of product id 123. 00:02:21.800 --> 00:02:25.633 We can add some custom calculations to the sheet. 00:02:25.633 --> 00:02:39.299 00:02:39.300 --> 00:02:49.433 Let's say the price of all products ordered by the admin user is 1, and for any other user the price is 99. 00:02:49.433 --> 00:02:52.799 00:02:52.800 --> 00:02:57.566 Now, I will set the output cell to use this value. 00:02:57.566 --> 00:03:06.132 If another user orders, uStore would put their user id here and the price returned to uStore would be 99. 00:03:06.133 --> 00:03:12.099 So, you can see how perhaps the user's company name or department or a custom user field 00:03:12.100 --> 00:03:17.233 could be used to apply a discount or a surcharge to some customers. 00:03:17.233 --> 00:03:25.733 But this is not a useful example, since all products would get the same price, and we haven't taken the quantity into consideration. 00:03:25.733 --> 00:03:34.933 I just want to explain the spreadsheet here. For a better example of product pricing, refer to the Excel Pricing Best Practices video. 00:03:34.933 --> 00:03:42.999 So, let's move on to the new product properties table. Simply enter the name of a product property in the first column. 00:03:43.000 --> 00:03:49.733 The property name should be exactly the same as defined in the back office and is case sensitive. 00:03:49.733 --> 00:03:59.099 If your store is using multiple cultures, or languages, you should enter the property name in the language that is the store setup culture. 00:03:59.100 --> 00:04:05.866 This input field indicates whether the property was used in the product that is currently being priced. 00:04:05.866 --> 00:04:12.766 uStore will enter 1 here if the product has this property, and 0 if not. 00:04:12.766 --> 00:04:19.832 This is really helpful because it means you can use just one spreadsheet, add all your product properties to the table, 00:04:19.833 --> 00:04:28.166 and your formulas can use the property in the price calculation only if it is used by the product currently being priced. 00:04:28.166 --> 00:04:32.732 uStore will put the customer's selected property value into this cell. 00:04:32.733 --> 00:04:39.233 For example, if the customer selected Gloss paper, then Gloss would appear here. 00:04:39.233 --> 00:04:46.666 The values that uStore will put into the spreadsheet are the values that you define for the property in the back office. 00:04:46.666 --> 00:04:51.066 For example, these are the values of the paper type property. 00:04:51.066 --> 00:04:59.032 Notice that the Value for Calculation is automatically calculated. You should not change the formula in this column. 00:04:59.033 --> 00:05:06.799 And in most cases your price calculations should use the value in this cell, and not the input cell. 00:05:06.800 --> 00:05:10.900 You will see why as I explain the Cheapest option. 00:05:10.900 --> 00:05:20.033 This feature will work only for products in stores set to use single page ordering workflow in uStore NG mode. 00:05:20.033 --> 00:05:24.366 It is designed to help uStore provide automatic price updates 00:05:24.366 --> 00:05:30.699 when the customer has not yet selected all the product properties that affect the product price. 00:05:30.700 --> 00:05:37.566 So, if the product being priced is using the automatic price feature, uStore puts 1 here. 00:05:37.566 --> 00:05:45.499 If the product is a composite product, or the store is not using single page mode, this cell will be 0. 00:05:45.500 --> 00:05:53.600 Here I have not yet selected the page size, but a price is returned. If I click A3, the price is updated. 00:05:53.600 --> 00:06:03.800 If I click A4, the price goes back to what I had before any selection was made, so the cheapest option for the page size property is A4. 00:06:03.800 --> 00:06:09.100 For our spreadsheet, let's say that Recycled is the cheapest paper type. 00:06:09.100 --> 00:06:15.833 Notice that the value for calculation is still Gloss – because the user has selected Gloss already. 00:06:15.833 --> 00:06:20.766 If I remove this, to simulate that the customer has not yet selected the paper type, 00:06:20.766 --> 00:06:25.399 then you will see that the value for calculation is now Recycled. 00:06:25.400 --> 00:06:33.233 So, your calculations for the price should use the value for calculation column, not the property value. 00:06:33.233 --> 00:06:44.766 I'll add a simple price formula here so that if the value for calculation equals "Gloss", I'll set 2 and other paper types will be 1. 00:06:44.766 --> 00:06:49.032 Ah! This is a really annoying Excel setting. 00:06:49.033 --> 00:06:55.799 Excel thinks that tables columns need to have the same formula, so if you get these values repeated in the table, 00:06:55.800 --> 00:06:59.400 you need to turn off this autocorrect setting. 00:06:59.400 --> 00:07:04.433 Go to File, Options, Proofing. 00:07:04.433 --> 00:07:15.566 Click AutoCorrect Options. On the AutoFormat As You Type tab, uncheck the option Fill formulas in tables to create calculated columns. 00:07:15.566 --> 00:07:22.066 This will stop Excel from duplicating the formulas in the table column. 00:07:22.066 --> 00:07:26.932 For the lamination, the cheapest option is no lamination. 00:07:26.933 --> 00:07:39.233 And just to set a price, I will say if the value for calculation is no lamination, the price is 0 and if it is laminated, then we will add 3. 00:07:39.233 --> 00:07:46.666 Now, if the product being priced set to use multi-page mode, then automatic pricing would be 0. 00:07:46.666 --> 00:07:50.799 Notice that the cheapest option is now ignored. 00:07:50.800 --> 00:07:56.800 00:07:56.800 --> 00:08:02.033 If you will add several properties to the table, you may need to add more rows to the table 00:08:02.033 --> 00:08:07.533 by right-clicking a row and selecting insert and then Table Rows above. 00:08:07.533 --> 00:08:14.199 If you are on the last row of the table, you can add the row either above or below. 00:08:14.200 --> 00:08:19.100 00:08:19.100 --> 00:08:24.500 The price of all the properties in the table is summed here by this formula. 00:08:24.500 --> 00:08:29.766 Notice that it is only adding the price if the property is used in the product. 00:08:29.766 --> 00:08:37.299 If I say the paper type property is not used on this product, then you can see the total is updated. 00:08:37.300 --> 00:08:42.166 So, let's add our properties table price into the total price. 00:08:42.166 --> 00:08:46.632 00:08:46.633 --> 00:08:55.266 The Property Output cells are used to control the properties displayed to the customer based on any other information in the Excel file. 00:08:55.266 --> 00:09:04.332 This feature is only available if the property is not a sub-property, dependent dropdown or taking values from a data source. 00:09:04.333 --> 00:09:15.066 The included and excluded cells can contain a list of the property values you want to display or hide from the user under certain circumstances. 00:09:15.066 --> 00:09:24.299 For example, if the user selects lamination, I would like to hide or exclude Matt and Gloss paper types. 00:09:24.300 --> 00:09:35.066 So, I can create an expression: if the lamination property value selected by the customer equals no lamination, 00:09:35.066 --> 00:09:42.166 then empty string, else, Gloss semicolon and Matt. 00:09:42.166 --> 00:09:49.066 Notice that this time, I am using the property value rather than the value for calculation. 00:09:49.066 --> 00:09:56.966 This is so that all paper types will be visible to the customer if they haven’t yet selected a value for lamination. 00:09:56.966 --> 00:10:07.032 In some cases, your cheapest option setting might end up hiding some values on other properties if you use the value for calculation. 00:10:07.033 --> 00:10:15.633 You can see that if the input value is no lamination, there are no excluded options, so all paper types will be displayed. 00:10:15.633 --> 00:10:24.633 If I set Matt lamination, then Gloss and Matt paper types will not be available for the customer to choose in the storefront. 00:10:24.633 --> 00:10:32.666 And, if the customer had previously selected Gloss, the value for calculation formula is now using the cheapest option 00:10:32.666 --> 00:10:36.999 because Gloss has been excluded from the available options. 00:10:37.000 --> 00:10:43.833 Note that the Excel is not able to add these values to the options that you have set in uStore admin. 00:10:43.833 --> 00:10:50.133 You are only able to include or exclude the values that are defined in the back office. 00:10:50.133 --> 00:10:53.866 Note that the values are case sensitive. 00:10:53.866 --> 00:11:00.199 If you enter a value here that is not defined on the property, it will be ignored. 00:11:00.200 --> 00:11:06.900 Instead of excluding matt and gloss, I could have included all the other options in this cell. 00:11:06.900 --> 00:11:12.866 In general, you would probably want to use the one that requires the least amount of typing. 00:11:12.866 --> 00:11:22.099 It is also important to note that the way the Value for Calculation formula works is to search for the value in the list. 00:11:22.100 --> 00:11:26.766 Therefore, your property values should not have values that repeat. 00:11:26.766 --> 00:11:36.666 For example, if you created a property in the uStore backoffice called "embossed" and had values "embossed" and "not embossed", 00:11:36.666 --> 00:11:46.532 then the Excel formula would find "embossed" in the list even when the option you are trying to include or exclude is "not embossed". 00:11:46.533 --> 00:11:54.666 The correct method would be to make the values true and false, or distinct for each type of embossing. 00:11:54.666 --> 00:12:02.566 The selection field can be used to set a property value that is used behind the scenes and not displayed to the customer. 00:12:02.566 --> 00:12:09.799 For example, I could have a printer property that identifies which of my printers will be used to print the product. 00:12:09.800 --> 00:12:17.933 By default, in the uStore backoffice, the property can be setup with a default printer that I expect will be used. 00:12:17.933 --> 00:12:24.499 But in Excel you could use a formula to set a different printer under certain circumstances. 00:12:24.500 --> 00:12:35.000 For example, if the total quantity is greater than 1000, I want to use a different printer which is more cost effective for me. 00:12:35.000 --> 00:12:46.500 00:12:46.500 --> 00:12:51.966 Naturally, this can be used to change the cost or the price of the product. 00:12:51.966 --> 00:12:56.932 Now, in this example, the printer property is hidden from the customer. 00:12:56.933 --> 00:13:02.633 You could also use the selection column on a property that is visible to customers, 00:13:02.633 --> 00:13:07.199 but this could be confusing to the customer since they would select something, 00:13:07.200 --> 00:13:12.066 and it would suddenly change back to what you set here in the selection column. 00:13:12.066 --> 00:13:16.866 So, if you want to force a particular selection on a visible property, 00:13:16.866 --> 00:13:25.999 you should also use the included or excluded options to display only the option that you set in the selection column. 00:13:26.000 --> 00:13:31.233 The visibility option can show or hide the property in the storefront. 00:13:31.233 --> 00:13:37.833 For example, you may want to prevent the customer from selecting combinations of properties which are irrelevant. 00:13:37.833 --> 00:13:44.666 If you had an embossing property you would want to hide embossing when the customer selects lamination, 00:13:44.666 --> 00:13:50.232 and vice versa you would want to hide lamination when the customer selects embossing. 00:13:50.233 --> 00:13:55.766 So, you can add formulas here to show or hide each property as needed. 00:13:55.766 --> 00:14:04.432 Let's look at these input values. For Dynamic products, you may want to use a customization dial in the price calculation. 00:14:04.433 --> 00:14:09.899 The sample here in the template is for a customization dial named Branch. 00:14:09.900 --> 00:14:17.533 If the product being priced has a customization dial named branch, uStore would put the value entered by the customer here, 00:14:17.533 --> 00:14:21.433 and you could use the value in your pricing formulas. 00:14:21.433 --> 00:14:27.666 To add additional customization dials to the spreadsheet, you need to use a named cell. 00:14:27.666 --> 00:14:34.199 The cell should be named inCustomization underscore and the name of the dial. 00:14:34.200 --> 00:14:40.300 If your product has a City dial, you could use the City in the price formula. 00:14:40.300 --> 00:14:48.800 The fulfilment property works in the same way, but fulfilment properties are entered by the store administrator when processing the order, 00:14:48.800 --> 00:14:54.933 so usually fulfillment properties will affect costs rather than prices. 00:14:54.933 --> 00:15:02.766 The cell naming for fulfillment properties is inPropertyFulfillment underscore and the property name. 00:15:02.766 --> 00:15:06.066 The examples shown in this video are fairly basic 00:15:06.066 --> 00:15:11.999 and designed to show more about how the spreadsheet works than to create any real price examples. 00:15:12.000 --> 00:15:20.033 There is a separate video that provides Excel Pricing best practices and includes more realistic pricing samples.