Roll-up schedules enable you to enter formulas to calculate the costs and prices for the product automatically. For example, you may want the L1 List price to increase based upon the C1 Standard cost.
You can set a price roll-up schedule at the following levels:
- Product
- Price Group
- Product Line
- Vendor
They are typically used on the Product Line or Price Group level to create a loaded or burdened cost or a list price for your sales team and customer service reps.
For products that belong to more than one level and have schedules set at multiple levels, price group schedules take precedence over product line, which takes precedence over vendor schedules. Price roll-up schedule formulas entered per product apply to that product only; they will override any formulas that are based on the product line, price group, and vendor.
The Price Rollup Schedule also allows you to base a price change on a newly changed price. For example, you may want the L1 List price to increase based upon the C1 Standard cost, which is also increasing. To achieve this, you can set the rollup so that the C1 Standard cost will be calculated first (Sequence=1), and then the L1 List price can be calculated based upon the new C1 Standard cost (Sequence=2).
Make sure the sequence of the roll up formulas for a product or grouping is in the correct order. For example, if C4 is your loaded cost which is based off of C1 but C1 is a multiplier of the manufacturer’s list price in L1 then you will want C1 to calculate before the calculation for C4 occurs. You can set the rollup so that the C1 Standard cost will be calculated first (Sequence=1), and then the C4 salesperson cost can be calculated based upon the new C1 Standard cost (Sequence=2).
Changes made to the price roll-up formula take effect when the base cost or price field changes to trigger the rollup, as well as when the Phantom Monitor job Future Price Rollup Update runs. This job changes Future rollups to Current and recalculates prices/costs for all products.
Note
For companies maintaining Cost by Warehouse, select the appropriate warehouse for the Price Rollup before clicking the Roll-Up Schedule button. Price Rollups are maintained by warehouse only for companies using Cost by Warehouse. The Warehouse option appears only if you are using this option.
To set the roll-up schedule on a product level
- Create or edit a product as described in Create and edit products.
- From the Setup page, click the Main tab.
- Click the Roll-Up Schedule button.
- Specify the roll up schedule: This process is best explained through the use of an example. In this example, we want the C4 Cost to be equal to the C1 Cost plus 3%.
- Select the row of the Cost Column that you want to update, Then in Base Column cell of this row, select the cost or price on which this formula will be based.
In this example, we want the C4 Cost to be calculated based on the C1 cost, so select C1 Standard. The C2, C3, and C5 Costs are not available because they are automatically updated by the system.
- In the +/- cell, select a plus sign to increase the cost or minus sign to decrease the selected cost.
Since we want to add to the cost, select the plus sign +.
- In the % / $ cell, choose whether to change the cost by a percent or a fixed dollar amount.
Since we want to increase the cost by 3 percent, select the percent sign %.
- In the Amount cell, type the fixed amount or percent by which you want to change the cost.
To add 3 percent to the C1 cost to calculate the C4 cost, type 3 here.
- Press the Tab key.
- Repeat for each price you want to change.
Note: To calculate a desired Desired Gross Profit Percent
Calculate your desired gross profit percent for the sale, based on the markup percentage. For example, if you want to make a gross profit of 25"%, type 33.33 into this box. This number should be entered as a whole percent, not a decimal value (i.e. 25 for 25%, not .25). When you enter your desired gross profit, the system calculates the correct discount and enters in into the Amount box. Press the Tab key after entering this value. , Desired MultiplierCalculate the amount based on a multiplier. For example, if you want to make 3 times the cost, type 3, and the system will calculate a 200% markup. Press the Tab key after entering this value. , or CHAIN Discount Specify chain discount percent amounts, separated by slashes. For example, 15/10/2-5 means take 15% off total, n 10% off that, then 5% off that total twice. When you specify these amounts, the system calculates the corresponding amounts and enters them into the table for you. Press the Tab key after entering this value.. in place of entering a value in the Amount field, click the drop down arrow in the Calculator column beside the Amount column.
- Select the row of the Cost Column that you want to update, Then in Base Column cell of this row, select the cost or price on which this formula will be based.
- To recalculate the cost or price fields immediately, click the Update Prices button. This will keep the value in your base cost or price field the same, but also trigger the rollup to recalculate the additional cost or price fields based on the new formula. If you do not click the Update Prices button, the field will update when the value in the base field changes.
- When you have finished, click the Roll-Up Schedule button again to close the pop-up, and then click Save.
To set the roll-up schedule on a price group, product line, or vendor level
- Go to Sales > Pricing > Price Roll-up Schedule.
- Choose whether you want to calculate the roll-up schedule on Price Group, Product Line, or Vendor. Either begin typing in the appropriate box, or click
and choose from the list that appears. For products that belong to more than one level and have schedules set at multiple levels, price group schedules take precedence over product line, which takes precedence over vendor schedules.
The full hierarchy is as follows:
- Product
- Price Group / Product Line / Vendor
- Price Group / Product Line
- Price Group / Vendor
- Product Line / Vendor
- Price Group
- Product Line
- Vendor
- Choose the Effective Date.
Tip: In addition to an effective date on the Current schedule, you can configure an entirely new rollup for a Future Date. When that future date is reached, the Current rollup will be replaced. Use the Future tab to set a future rollup.
- Specify the roll up schedule: This process is best explained through the use of an example. In this example, we'll set two schedules: the first calculates C1 Cost to be equal to L1 Manufacturer’s List with a multiplier of .65; the second calculates the C4 Cost to be equal to the C1 plus 3% load.
- C1 Cost to be equal to the L1 Manufacturer's List with a multiplier of .65
Select the row of the Cost Column that you want to update (in this case C1). Then in Base Column cell of this row, select the cost or price on which this formula will be based (L1).
Note that C2, C3, and C5 Costs are not available because they are updated automatically by the system.
- In the +/- cell, select a plus sign to increase the cost or minus sign to decrease the selected cost.
Since we want to subtract from the price, select the minus sign -.
- In the % / $ cell, choose whether to change the cost by a percent or a fixed dollar amount.
Since we want to decrease the list by calculated percent from the multiplier, select the percent sign %.
- To calculate the percent that belongs in the Amount cell, click the drop down arrow in the Calculator column beside the Amount column.
To use a multiplier of .65 for this calculation, simply enter .65 into the Enter Desired Multiplier
Calculate the amount based on a multiplier. For example, if you want to make 3 times the cost, type 3, and the system will calculate a 200% markup. Press the Tab key after entering this value. field - Press the Tab key.
- C4 Cost to be equal to the C1 plus 3% load
Select the row of the Cost Column that you want to update (C4), then in Base Column cell of this row, select the cost or price on which this formula will be based (C1).
- In the +/- cell, select a plus sign to increase the cost or minus sign to decrease the selected cost.
Since we want to add to the cost, select the plus sign +.
- In the % / $ cell, choose whether to change the cost by a percent or a fixed dollar amount.
Since we want to increase the cost by 3 percent, select the percent sign %.
In the Amount cell, type the fixed amount or percent by which you want to change the cost.
To add 3 percent to the C1 cost to calculate the C4 cost, type 3 here.
- Press the Tab key.
Note
A new Price Rollup formula only takes effect when the first base field changes, as well as when the Phantom Monitor job Future Price Rollup Update runs. If you would like the costs or prices to be recalculated based on the new rollup, you can run a Change a price by formula for the base field plus 0%. This will trigger the rollup to recalculate the prices and costs based off of the selected field. - When you have finished, click Save.