Context

It is common for some customers to have highly variable purchases for the same product and want to apply a variable margin on the price of the last purchase made when generating quotes.

In Odoo, one option to do this is through price lists. However, these have a limitation: the calculation is based on the cost recorded in the product template, which always corresponds to the average cost, and does not allow defining a variable margin. To manage different margins, multiple price lists would need to be created, which can be impractical.

In this forum, we will show how to use the quote calculator to take the price of the last purchase made, apply a variable margin, and then inject the value into the quote.

Note: Studio is required.

Configuration

1. Create the quote template

First, it is necessary to create the quote template. To do this, you must activate the quote templates in:

Sales > Configuration > Settings > Activate "Quote Templates"

Once this is done, go to: Sales > Configuration > Settings > Activate “Quote Templates" > New > Name the template > In the “Quote Calculator” section, create a new record and enter by clicking the arrow

Clicking the arrow will take us to a spreadsheet with a sheet that already contains some information, from which we will only use the "Product" and "Unit Price" columns. It will be necessary to add two more columns: one for "Purchase Price" and another for "Margin".

1. Configure the purchase module

Once the spreadsheet is set up, you need to go to the purchase module and create a new menu to bring the model of the purchase order lines.

Purchase > Studio > Click on "Edit Menu" > Click on "New Menu" > Name the menu > Click on “Existing Model” and search for and select “Purchase Order Line” > Confirm > Arrange the menu within “Reports” and confirm

Once this is done, go to:

Purchase > Reports > Purchase Lines

This will display the purchase order lines, which we can insert into the previously created spreadsheet.

It is necessary to apply a domain in the list properties so that it only shows purchases with the status “Purchase Order”.

1. Apply formulas in the spreadsheet

With the model and the template in the quote calculator spreadsheet, it is necessary to use the VLOOKUP formula in the “Purchase Price” column to return the value of the last purchase made.

In the “Margin” column, enter the desired percentage. Finally, in the “Unit Price” column, enter the following formula: =purchase price cell * (1 + margin cell)

Finally, you only need to synchronize each unit price cell with the unit price field in the quote.