BOM cost rollup in Excel: unit cost, price breaks and NRE

A price break is a unit price that applies from a stated quantity upward. The quantity bought on the line picks the break, so the lookup runs on the order quantity. Overage and minimum orders change that quantity first; see quantity per board vs order quantity.

Store each part's breaks in a row, with ascending break quantities in a header row above. One possible layout: boards in B1, break quantities in H2:J2, prices in H4:J9, lines in rows 4 to 9, the summary in G10 to G14, and the NRE figure typed into G11.

E4   =C4*$B$1
F4   =IFERROR(LOOKUP(E4,$H$2:$J$2,H4:J4),H4)
G4   =IF(D4="Y",0,E4*F4)
G10  =SUM(G4:G9)
G12  =G10+G11
G13  =G12/$B$1
G14  =SUMPRODUCT((D4:D9="N")*E4:E9*F4:F9)

LOOKUP returns the price for the largest break not above the order quantity, so the breaks must ascend. Below the first break it gives #N/A, which IFERROR replaces with the first price. Where available, =XLOOKUP(E4,$H$2:$J$2,H4:J4,H4,-1) does the same job. G14 re-derives the material total and should equal G10.

NRE (non-recurring engineering) means one-off charges such as a stencil, a test fixture or set-up; your assembler's list will differ. Add them once, outside the unit prices, then divide by the boards to see their weight per board. Reel or cut-tape fees, shipping and tax also sit outside the unit price. Labour is a separate part of a quote; see BOM for assembly quotes.

• Lookup on the wrong quantity. Using the quantity per board puts every line on the 1+ break. In the table above that lifts material from 429.80 to 595.00, about 38% more.
• Unsorted breaks. LOOKUP assumes ascending order and can return a wrong price without any error.
• Priced DNP lines. A DNP line still carries a quantity and a price. Zero its cost in the formula; see DNP in a BOM.
• Numbers stored as text. Prices pasted from a web page may be text. SUM skips them and a lookup against them fails.
• Stale prices. Prices move and quotes expire. Record the source and the date beside each price.
• Filtered totals. SUM still adds rows that a filter has hidden. SUBTOTAL(109,G4:G9) leaves them out.

BOMParser does not look up or hold prices. It supplies the starting point: each line's quantity, part number, manufacturer, MPN and DNP flag in fixed columns, with every quantity checked against its designator count. The XLSX download holds values only, with no formulas, so build the rollup in your own sheet; extra columns such as a price appear only in the JSON export. For the spreadsheet route see PDF BOM to Excel, or convert a file on the BOM to CSV page. Pricing lists the plans. Free: 1 file per day, up to 10 pages, no account. Files over 10 pages are converted up to page 10 on the free allowance; a page code converts the whole file, up to 150 pages.