My LLM skills

• diagram-generator: https://github.com/GBSOSS/ai-drawio.git@16621c0
• planning-with-files: https://github.com/OthmanAdi/planning-with-files.git@dcb92c0
• ui-ux-pro-max-skill: https://github.com/nextlevelbuilder/ui-ux-pro-max-skill.git@7f22b57
• ui-skills: https://github.com/ibelick/ui-skills.git@fff1d1a
• vercel-deploy: https://github.com/vercel-labs/agent-skills/tree/main/skills/vercel-deploy
• agent-browser: https://github.com/vercel-labs/agent-browser/tree/main/skills/agent-browser
• react-best-practices: https://github.com/vercel-labs/agent-skills/tree/main/skills/react-best-practices
• news-aggregator-skill: https://github.com/cclank/news-aggregator-skill.git@9874519
• qmd-skill: https://github.com/levineam/qmd-skill.git@main
• humanizer-zh: https://github.com/op7418/Humanizer-zh.git@main
• pretty-mermaid: https://github.com/imxv/Pretty-mermaid-skills.git@main
• firecrawl: https://github.com/firecrawl/cli/tree/main/skills/firecrawl-cli@main
• frontend-design: https://github.com/anthropics/skills/tree/main/skills/frontend-design@main

Automate the process of reading vendor PDF quotations and populating a CDW pricing sheet by mapping quote line items to a Bill of Materials (BOM) sheet. Perform automatic reconciliation to catch pricing errors.

User uploads a vendor PDF quotation and asks to map/populate/reconcile it against their BOM or SINDEV sheet.

1. PDF quotation — uploaded file from a vendor (CDW, Insight, Techdata, etc.)
2. BOM sheet — the master Bill of Materials (e.g. "SINDEV") already in the workbook
3. Target CDW sheet — the sheet to populate (e.g. "CDW SINDEV SW" or "CDW SINDEV HW")

1. Read the BOM sheet (e.g. SINDEV) to understand:

   • All items (column D = Item name)
   • Type (column C = HW or SW)
   • Vendor (column E), Manufacturer (column F), Description (column G)
   • Quantity (column H)
   • The row number for each item (used to derive the SN = row_number − 1)

2. Read the target CDW sheet to understand:

   • Existing column headers (row 1)
   • Any rows already populated (to avoid overwriting)
   • The standard column layout. Common patterns:
     • A=SN, B=ITEM, C=VENDOR, D=MANUFACTURER, E=DESCRIPTION, F=QTY
     • G=UNIT COST, H=TOTAL 3YRS, I=YEAR 4, J=YEAR 5, K=TOTAL
     • L=NET TOTAL label, M=NET TOTAL formula
   • Existing formulas to understand the user's preferred formula patterns

3. Confirm the column layout with the user if it differs from the expected pattern.

1. Use code_execution with PyMuPDF (fitz) to extract text from the PDF:

   import fitz
   doc = fitz.open(pdf_path)
   for page in doc:
       text = page.get_text()

2. Identify the quote structure — look for:

   • Section headers that indicate pricing periods:
     • "UPFRONT 3-YEAR" or similar → 3-year bundled cost
     • "YEAR 4" / "YEAR-ON-YEAR FOR 4TH YEAR" → Year 4 renewal
     • "YEAR 5" / "YEAR-ON-YEAR FOR 5TH YEAR" → Year 5 renewal
     • "YEAR 4 AND CONSECUTIVE YEAR" → same annual cost for Y4, Y5, and beyond
   • Line items with: Product Code, Description, Quantity, prices
   • Totals at the bottom: NET AMOUNT, GST, TOTAL AMOUNT

3. CRITICAL — Determine price column semantics:

   • PDF quotes may label columns as "NET PRICE" and "NET AMOUNT"
   • Do NOT assume which is per-unit vs total — VERIFY by checking: does (per_unit × qty) = total?
   • Example: If NET AMOUNT = 11,960.38, qty = 4, and NET PRICE = 47,841.52, then NET AMOUNT is per-unit (11,960.38 × 4 = 47,841.52)
   • This varies by vendor — always verify with the first line item

4. Extract every line item into a structured list:

   {product_code, description, quantity, per_unit_price, total_price, section (3yr/Y4/Y5)}
   

This is the most judgment-intensive step. Present the proposed mapping to the user BEFORE writing anything.

1. Match by description/product type — map each quote line item to a BOM row:

   • Look for keyword matches (e.g. "RHEL" → "Red Hat Enterprise Linux", "VEEAM" → "Veeam Backup")
   • Multiple quote lines may map to ONE BOM row (e.g. base license + add-on = combined unit cost)

2. Handle bundled items:

   • If a BOM has separate rows for base product + add-on (e.g. "RHEL Server" + "Satellite for Server"), and the quote bundles them together, mark the add-on row as "INCLUDED"
   • The combined cost goes on the base product row

3. Handle quantity mismatches:

   • Quote qty may differ from BOM qty (e.g. quote has 6 individual licenses, BOM shows 1 solution with "60 workload")
   • Use the quote's quantity for the CDW sheet (it reflects actual licensing)
   • Items with qty=1 that serve a group (like a single CAL license for 2 servers) must NOT be multiplied by the group qty — divide by group size if treating as per-unit

4. Build the unit cost formula:

   • Combine all per-unit prices that map to the same BOM row using a SUM formula
   • Example: =11960.38+15622.72 (RHEL base + Smart Management per-unit prices)
   • Show the breakdown so the formula is auditable

5. Present the mapping table to the user:

   | BOM Row | SN | Item | Qty | Unit Cost (formula) | Source Lines |
   

   • Flag any items marked "INCLUDED"
   • Flag any qty mismatches
   • Flag any items in the BOM that have NO matching quote line
   • Wait for user approval before proceeding

After user approves the mapping:

1. Write each row with formulas (not static values):

   • SN = BOM row number − 1
   • ITEM, VENDOR, MANUFACTURER, DESCRIPTION = from BOM sheet
   • QTY = from quote (may differ from BOM)
   • UNIT COST = formula showing per-unit price breakdown (e.g. =2046.85+(67.83/2)+8.43)
   • TOTAL 3YRS = =G*F (unit cost × qty)
   • YEAR 4 = formula with per-unit renewal × qty (e.g. =2606.38*6)
   • YEAR 5 = formula with per-unit renewal × qty
   • TOTAL = =SUM(H:J) (3yr + Y4 + Y5)

2. For "INCLUDED" items: Write "INCLUDED" in UNIT COST, leave other cost columns blank

3. Update the NET TOTAL formula (typically in M1) to cover all rows: =SUM(K2:K{last_row})

4. Apply formatting:

   • Dark blue header row (#2F5496) with white bold text, centered
   • Alternating light blue (#D6E4F0) / white row banding
   • Font: Aptos Narrow 11pt throughout
   • Number format: #,##0.00 on all currency columns
   • SN and QTY columns: centered
   • TOTAL column (K): bold
   • NET TOTAL: gold accent label (#BF8F00), light yellow value cell (#FFF2CC)
   • Medium dark blue outer border, thin gray inner grid
   • Proper column widths

Always perform this step — it's the error-catching safety net.

1. Sum all PDF line items (using NET PRICE / total column) to get the PDF grand total

2. Compare against the PDF's stated total (NET AMOUNT at bottom) — these should match exactly. If not, you missed a line item.

3. Compare our sheet total vs PDF total and explain any differences:

   • Legitimate differences: "Year 4 and consecutive" counted once in PDF but in both Y4 and Y5 in our sheet
   • Errors to fix: qty mismatches (like the CAL double-count), missed line items, wrong per-unit extraction

4. Report the reconciliation:

   Our NET TOTAL:    XXX,XXX.XX
   PDF NET AMOUNT:   XXX,XXX.XX
   Difference:       X,XXX.XX
   
   Breakdown:
   - [Item]: Our value vs PDF value — [explanation]
   

• Always VERIFY which PDF column is per-unit vs total — check with multiplication
• Some vendors put per-unit in "NET AMOUNT" and total in "NET PRICE" (counterintuitive)

• When a line item has qty=1 but serves multiple units (e.g. 1 CAL for 2 servers), divide by the number of units if including in per-unit cost: =(67.83/2)
• When the quote uses individual license counts (e.g. 6 Veeam licenses) but the BOM shows a solution count (e.g. 1 × 60 workload), use the quote's qty

• "Year 4 and consecutive year" = same annual cost for Y4 and all subsequent years → put the SAME value in both Y4 and Y5 columns
• Year 4 and Year 5 may have DIFFERENT prices (e.g. Veeam Y4=2606.38, Y5=2895.98) — don't assume they're equal
• The PDF total typically counts "consecutive year" sections once, so expect a difference equal to one year's renewal

• UNIT COST: show the per-unit breakdown as a formula (e.g. =11960.38+15622.72) for auditability
• TOTAL 3YRS: always =UNIT_COST * QTY (formula, not static)
• YEAR 4/5: show per-unit × qty as formula (e.g. =2606.38*6)
• TOTAL: always =SUM(TOTAL_3YRS:YEAR5)
• NET TOTAL: always =SUM(TOTAL_col_first_row:TOTAL_col_last_row)

• Use the ACTUAL manufacturer, not the vendor/reseller
• CDW/HPE may resell Red Hat, Veeam, Trellix, Tenable — use the real manufacturer name
• If unsure, check the BOM sheet's Manufacturer column or ask the user

• The mapping decision itself — always present for user approval
• Ambiguous bundling — ask the user when it's unclear if items should be combined
• Currency conversion — if the BOM is in SGD and the quote is in USD, flag this and ask for the FX rate

You can save this as a Project file or reference document. Next time you upload a vendor quote, just say "use the quote-to-BOM workflow" and I'll follow this exact process — including the reconciliation step that caught the 67.83 CAL error today. '''