RSMUS.comEvery company strives to achieve healthy profit margins, but this can become a laborious task if they sell thousands or hundreds of thousands of different items. NetSuite offers a flexible and powerful mass update tool that can be used to set fields values on key records, in mass, as opposed to updating them one by one or with inline editing. Item pricing mass updates are just one of the mass update types available but they can offer a NetSuite customer a ton of value.
Let’s say ABC Company has done their financial planning for the upcoming year and they have determined a desired GM% they want to make on different item categories in their product offering. For example, all products in their “Cabinet” category should be priced to produce a 40% GM while items in their “Desk” category should make 30%, and so on.
ABC Company can easily configure item mass updates from the general Mass Updates screen and then select Items –> Update Item Prices option as shown below.
Then, they can choose to set a particular Price Level, like List/Base Price, using the Average Cost as the basis and a percentage that, when added to the Average Cost, will produce a selling price yielding a 40% GM, for example. To calculate this percentage, use the formula ((1 / (1 – GM)) – 1) *100
EX: To set the price to achieve a 40% margin using the average cost of an item, set the Amount field to ((1 / (1 – .40)) – 1) * 100 = (1.6666667 – 1) = .6666667 * 100 = 66.667%
Below you will see how this amount was filled in and the price was also set to end in the nearest $.49 or $.99 so it is a more common price.
Lastly, we set the Criteria to only select items with a parent item of “Cabinets” for this mass pricing update.
When you preview the mass update, the columns that show are based on the columns chosen in the Results tab of the mass update screen and it will include a final column of what the new value will be once the Perform Update button is pressed.
Note how a calculated selling price of $62.99 for the first item yields a GM% of 40% using the GM% formula of (($62.99 – $37.82481752) / $62.99) * 100
Finally, validate all the items to be updated and click the Perform Update button. Now, you can create other mass updates for other item categories and GM% based on your desired results.
If you have any NetSuite customization or consulting needs, including the creation of mass updates as shown above, the NetSuite professionals at RSM can help. We are a NetSuite Solution Provider and a NetSuite Systems Integrator partner, and have more than 30 years experience implementing ERP solutions. We’ll provide you with industry insight, project management and the technical resources you need to make your project a success. Contact RSM at erp@rsmus.com or by phone at 855.437.7202 .
By: Gary Leitner – Colorado NetSuite Solution Provider
