The six field groups for your parts pricing spreadsheet are: buying rate, selling rate, price unit and conversion, conditional total, delivery location, and tax treatment.
Key takeaways for what fields belong in a parts pricing spreadsheet?
- Store buying and selling rates separately. The buying price for an item is different from the selling price, so they are stored separately according to Frappe .
- Record the unit of measure (UOM) for each price. You can mention the UOM, such as "KG" or "grams," in the Item Price so the correct price applies based on the UOM selected in the transaction according to Frappe .
- Use conditional logic for totals. The WorksheetFunction.SumIfs method adds the cells in a range that meet multiple criteria according to Microsoft .
- Note the delivery location for tax. The point of delivery or the point at which possession is transferred by the seller to the purchaser determines the rate of tax to be collected according to the New York State Department of Taxation and Finance NY State Tax .
- Classify merchandise sales correctly. California's sales tax generally applies to the sale of merchandise, including vehicles, in the state according to the California Department of Tax and Fee Administration CA CDTFA .
Record buying and selling rates separately, alongside units, conditional totals, delivery location, and tax inputs. Frappe documents that buying and selling prices are stored separately.
Parts pricing spreadsheet: six field groups to record
The Item Price record allows you to log the selling and buying rate of an item, according to Frappe .
- Buying rate : Record the cost you pay for the part.
- Selling rate : Record the price you charge the customer.
- Price unit and conversion : Identify the unit and any packing conversion.
- Conditional total setup : Define criteria for specific total calculations.
- Delivery location : For a New York sale, record the point of delivery or the point at which possession is transferred by the seller to the purchaser.
- Tax treatment : Identify whether the charge is for merchandise or fabrication.
Keep the buying and selling rates in distinct columns.
Buying and selling rate fields from Frappe
Record the buying rate and the selling rate in distinct columns within your parts pricing spreadsheet. According to Frappe , the buying price for an item is different from the selling price and thus they are stored separately. Keeping these values in separate cells prevents accidental overwriting of your cost basis when you update your customer-facing quote. This distinction allows you to track the amount you pay to acquire the part independently from the amount you charge the customer for it.
When you build your spreadsheet, create one column dedicated to the buying rate and a second column dedicated to the selling rate. Do not combine these figures into a single cell or calculate the selling rate by adding a fixed margin to the buying rate within the same cell. By maintaining two separate entries, you preserve the original cost data even if your selling price changes due to market conditions or customer negotiations.
Your spreadsheet should reflect the separate storage of these two price types. This structural separation is a fundamental part of how price lists are managed in the system. When you enter data for a new part, input the purchase cost in the buying rate field and the quote amount in the selling rate field. This approach ensures that your cost tracking remains accurate and distinct from your revenue tracking.
See Set 8 price book inputs across 4 apps for steady quotes .
Unit and conditional total fields from Frappe and Microsoft
Record the price unit and packing conversion together in your spreadsheet so the correct rate applies based on the selected unit of measure. For example, if Rice is sold in 1 KG and 500-gram packets, you can mention the UOM ("KG" or "grams") in the Item Price, and the correct price will be applied based on the UOM selected in the transaction, according to Frappe . This distinction matters when a part comes in different package sizes; the unit field tells the system which rate to pull.
When a part is sold in fractional quantities, document the packing conversion explicitly. The default is 0; you can use non-integer UoM like 1.5 kg oats for 1 Packing Unit, according to Frappe . If you buy by the kilo but sell by the box, record the ratio. For instance, 1 Box = 10 Kilos, and 1 Kilo buying price is 10rs, according to Frappe .
Conditional totals let you apply a different price only when specific conditions are met. In the Price Lists documentation, if you tick the checkbox and make a transaction with a Box of Tomatoes, the price will be automatically set as 100 since the price of 1 Box (10 Kilos) is 100, according to Frappe . Unit conversion like that box price is separate from conditional summation. In a spreadsheet, a conditional sum adds the values that meet every criterion. The criteria ranges define where the system looks for matches. Criteria_range1, criteria_range2... - One or more ranges in which to evaluate the associated criteria, according to Microsoft .
The criteria themselves determine which rows contribute to the total. Criteria1, criteria2... - One or more criteria in the form of a number, expression, cell reference, or text that define which cells will be added, according to Microsoft . Cells contribute only when all corresponding criteria are true. Use this setup to total the matching values. A separate rate-selection calculation is still needed when the rate itself depends on quantity.
| Field Component | Documented Example or Rule | Source Publisher |
|---|---|---|
| Price Unit | UOM ("KG" or "grams") applied based on selection | Frappe |
| Packing Conversion | 1 Box = 10 Kilos; 1 Kilo buying price is 10rs | Frappe |
| Conditional Total | Price automatically set as 100 for 1 Box (10 Kilos) | Frappe |
| Criteria Ranges | One or more ranges to evaluate associated criteria | Microsoft |
Copy the unit and conversion columns into your sheet and test one part with two different package sizes to verify the rates switch correctly.
Delivery and tax fields from state tax publishers
Record the delivery location before entering tax rates. The point of delivery or the point at which possession is transferred by the seller to the purchaser determines the rate of tax to be collected, according to New York State Department of Taxation and Finance . This location input anchors the tax calculation to a specific geographic point rather than the seller's address.
The combined sales and use tax rate equals the state rate (currently 4%) plus any local tax rate imposed by a city, county, or school district, according to New York State Department of Taxation and Finance . An additional sales tax rate of 0.375% applies to taxable sales made within the Metropolitan Commuter Transportation District (MCTD), according to New York State Department of Taxation and Finance . Your spreadsheet should have separate cells for the state base rate, local district rates, and any applicable district surcharge to reflect these components accurately.
| Tax Component | Published Rate or Condition | Source Publisher |
|---|---|---|
| State base rate | Currently 4% | New York State Department of Taxation and Finance |
| Local district rate | Imposed by a city, county, or school district | New York State Department of Taxation and Finance |
| MCTD surcharge | 0.375% for taxable sales within the district | New York State Department of Taxation and Finance |
Distinguish between merchandise sales and charges for producing, fabricating, or processing tangible personal property in your tax-treatment field. Not all parts-related charges have identical treatment. Do not assume a single tax rate applies to every line item on a repair invoice.
When calculating totals for specific tax scenarios, the WorksheetFunction.SumIfs method (Excel) adds the cells in a range that meet multiple criteria, according to Microsoft . Each cell in sum_range is summed only if all the corresponding criteria specified are true for that cell, according to Microsoft . For example, criteria can be expressed as 32, "32", ">32", "apples", or B4, according to Microsoft . This logic allows you to isolate taxable items from exempt ones based on the specific conditions defined in your spreadsheet columns.
Copy the delivery location and tax rate components into your spreadsheet for one New York-based customer to verify the total calculation.
Parts pricing reference table by publisher
The following table summarizes one input for each of the six field groups, attributing each behavior to its specific publisher.
| Field Group | Publisher | Documented Behavior or Input |
|---|---|---|
| Buying rate | Frappe | Buying Price for an item is different from Selling Price and thus they're stored separately, according to Frappe . |
| Selling rate | Frappe | Item Price is the record in which you can log the selling and buying rate of an item, according to Frappe . |
| Price unit and conversion | Frappe | You can mention the UOM ("KG" or "grams") in the Item Price, and the correct price will be applied based on the UOM selected in the transaction, according to Frappe . The default is 0; you can use non-integer UoM like 1.5 kg oats for 1 Packing Unit, according to Frappe . 1 Box = 10 Kilos, and 1 Kilo buying price is 10rs, according to Frappe . |
| Conditional total | Frappe | If you tick this checkbox and make a transaction with a Box of Tomatoes, then the price will be automatically set as 100 since the price of 1 Box (10 Kilos) is 100, according to Frappe . |
| Delivery location | New York State Department of Taxation and Finance | In New York, the point of delivery or the point at which possession is transferred by the seller to the purchaser |
| Tax treatment | California Department of Tax and Fee Administration | Merchandise sales, including vehicles, in the state . Charges for producing, fabricating, or processing tangible personal property . |
Use this table to verify that your spreadsheet columns align with the specific documentation provided by each source.
Six column parts sheet layout
Use the layout below to organize your parts data. Each column corresponds to one of the six field groups discussed earlier. Fill the first row with a part you regularly quote to test the structure before applying it to your full inventory.
| Buying rate | Selling rate | Price unit and conversion | Conditional total | Delivery location | Tax treatment |
|---|---|---|---|---|---|
| Example paid cost | Example customer price | Example selected unit | Example matching total | Example delivery point | Example merchandise charge |
When filling the Tax Treatment column, distinguish between different types of charges. California's sales tax generally applies to the sale of merchandise, including vehicles, in the state, according to California Department of Tax and Fee Administration . Additionally, tax applies to charges for producing, fabricating, or processing tangible personal property for your customers, according to California Department of Tax and Fee Administration . Keep these distinctions clear in your sheet so you can identify which charges fall under which category.
Illustrative example of one repair part
A shop records one part. The buying rate is 10 and the selling rate is 100. The price unit is 1 box with a packing conversion of 10 smaller units. A conditional total only adds matching lines. Two lines of 10 total 20, and that sum does not set the selling rate. Delivery location is the point of delivery. Tax treatment marks merchandise on one line and fabrication on another. The illustrative state base rate is 4.
Copy these six field groups into your existing spreadsheet and fill them for one part you regularly quote.
FAQ: using the parts sheet for your repair quotes
What should a parts pricing spreadsheet contain?
A useful parts pricing sheet records six distinct field groups: buying rate, selling rate, price unit, conditional total, delivery location, and tax treatment. Keeping these separate prevents mixing cost data with customer-facing pricing or jurisdiction-specific tax inputs. This structure supports accurate quoting by isolating each variable that affects the final invoice amount.
How does ERPNext distinguish buying and selling prices?
Buying and selling prices are stored separately, according to Frappe .
How can spreadsheet totals include only rows matching all criteria?
Spreadsheet functions can add the cells in a range that meet multiple criteria, according to Microsoft . This logic allows you to sum values only when every specified condition is true for that specific cell. Use this method to isolate parts that match specific unit or quantity thresholds before calculating totals.
Which location determines the tax rate for my New York parts sale?
For New York, it is the point of delivery or the point at which the seller transfers possession to the purchaser, according to New York State Department of Taxation and Finance .
Does California tax charges for fabricating a customer's part?
Tax applies to charges for producing, fabricating, or processing tangible personal property for your customers, according to California Department of Tax and Fee Administration . This rule distinguishes fabrication charges from simple merchandise sales in the tax treatment field. Verify whether your specific part charge falls under this definition before applying a tax rate.