How to Build a Should Cost Model: Excel + AI Approach
Every cost engineering team starts with Excel. It is flexible, familiar, and fast enough for a single part — until it is not.
The question is not whether to build should cost models in Excel, but whether to stay there once your BOM grows and your RFQ cycle stops waiting for your spreadsheet to load. This guide covers both paths: how to build a genuinely rigorous should cost model in Excel, and where that approach breaks down — so you know exactly what you are trading off when you move to an AI-powered platform.
The Architecture of a Should Cost Model
Before building anything, understand what a should cost model actually needs to contain. A well-structured model has six components:
Miss any of these and your model has a structural hole. Most Excel models break down at components 1 and 2 — the rate and material databases go stale and nobody updates them.
Building the Model in Excel: Tab-by-Tab
Tab 1 — Rate Library
This is the foundation of your model. Every process cost calculation draws from it. Build it once, maintain it consistently.
| Process Code | Process Name | Geography | Machine Rate (₹/hr) | Labor Rate (₹/hr) | Overhead % | Last Updated |
|---|---|---|---|---|---|---|
| MCH-3AX | 3-Axis CNC Milling | India | 2,100 | 420 | 22% | Jun 2026 |
| MCH-5AX | 5-Axis CNC Milling | India | 4,500 | 550 | 22% | Jun 2026 |
| MCH-3AX | 3-Axis CNC Milling | Germany | 9,200 | 4,100 | 28% | Jun 2026 |
| SHT-LSR | Laser Cutting | India | 1,600 | 380 | 20% | Jun 2026 |
| SHT-BND | CNC Press Brake | India | 1,200 | 350 | 20% | Jun 2026 |
| WLD-TIG | TIG Welding | India | 900 | 400 | 18% | Jun 2026 |
| SRF-ANO | Anodising (outsourced) | India | — | — | — | Jun 2026 |
Use a Geography column so the same process code can hold rates for multiple regions. Drop-down validation on geography prevents typos. The Last Updated column is not cosmetic — it is your audit trail.
Tip: Lock this tab with sheet protection. Anyone editing a rate should do so explicitly and with awareness. Accidental overwrites in the rate library corrupt every model that references it.
Tab 2 — Material Price Database
| Material Code | Description | Grade | Form | Price (₹/kg) | Region | Source | Date |
|---|---|---|---|---|---|---|---|
| AL-6061 | Aluminium | 6061-T6 | Bar/Billet | 645 | India | Supplier quote | Jun 2026 |
| AL-7075 | Aluminium | 7075-T6 | Bar/Billet | 890 | India | Supplier quote | Jun 2026 |
| SS-316L | Stainless Steel | 316L | Bar | 520 | India | LME index | Jun 2026 |
| TI-64 | Titanium | Grade 5 (Ti-6Al-4V) | Bar | 2,950 | India | Supplier quote | Jun 2026 |
| MS-CRS | Mild Steel | CR Sheet | Sheet | 78 | India | SAIL index | Jun 2026 |
Update this tab at minimum quarterly, or whenever a significant commodity price movement occurs. Tie each entry to a source — supplier quote, commodity index, or market report — so you can defend it in a negotiation if challenged.
Tab 3 — Process Time Benchmarks
This is your process knowledge base — standard time estimates for common operations, which cost engineers use as starting points before adjusting for part-specific complexity.
| Process | Feature Type | Complexity | Base Cycle Time | Adjustment Factors |
|---|---|---|---|---|
| 3-Axis Milling | Pocket (per pocket) | Standard | 8–14 min | +25% for <0.5mm radius corners |
| 3-Axis Milling | Bore (per bore) | Standard | 4–7 min | +15% per tolerance class above IT7 |
| 5-Axis Milling | Undercut feature | Complex | 18–35 min | Part-specific |
| TIG Welding | Weld seam (per 100mm) | Standard | 6–10 min | +40% for aerospace-grade inspection |
| Press Brake | Bend (per bend) | Standard | 2–4 min | +20% for material >4mm |
| Anodising | Per part (standard) | — | Market rate | ₹120–280 per part |
This tab is where institutional knowledge is encoded. Build it over time from actual process experience and calibrate against real cycle times from your shop floor or supplier audits.
Tab 4 — BOM Input
This is where users enter or paste the part data for a new should cost model. Keep it structured and use data validation drop-downs for Material Code, Process, and Geography — each pulling from the reference tabs. Free-text entry in these fields is how models break.
| Part No. | Description | Material Code | Stock Form | Stock Wt (kg) | Finished Wt (kg) | Process 1 | Cycle 1 (min) | Process 2 | Cycle 2 (min) | Ann. Volume | Geography |
|---|---|---|---|---|---|---|---|---|---|---|---|
| BKT-001 | Mounting Bracket | AL-6061 | Billet | 1.4 | 0.52 | MCH-3AX | 48 | SRF-ANO | — | 500 | India |
| PLT-002 | Cover Plate | MS-CRS | Sheet | 0.8 | 0.71 | SHT-LSR | 12 | SHT-BND | 8 | 1,200 | India |
Tab 5 — Cost Build Engine
This is the calculation layer. Each row corresponds to one part. All inputs reference Tab 4; all rates reference Tabs 1 and 2.
Core Should Cost Formula
Should Cost = Material Cost + Process Costs + Setup Cost + Inspection Cost + Overhead + SG&A + Margin
Where:
Sample output for BKT-001 — Mounting Bracket (AL-6061, 3-Axis CNC, Anodised, 500/year):
| Cost Element | Formula Basis | Value |
|---|---|---|
| Material | 1.4 kg × ₹645 × 1.15 scrap adj | ₹1,037 |
| 3-Axis CNC Milling | (48/60) × (₹2,100 + ₹420) | ₹2,016 |
| Anodising | Market rate lookup | ₹210 |
| Setup (amortised) | ₹1,800 per setup ÷ 50 batch | ₹180 |
| Inspection | 10 min × (₹420 labor + overhead adj) | ₹190 |
| Factory Overhead (22%) | On direct cost | ₹805 |
| SG&A (7%) | ₹311 | |
| Profit Margin (13%) | ₹649 | |
| Should Cost | ₹5,398 | |
Tab 6 — Output and Comparison
The final tab assembles the negotiation view: should cost vs actual quotes received. Conditional formatting on the Gap % column — red for >15%, amber for 5–15%, green for <5% — makes priority visible at a glance.
| Part No. | Should Cost | Quote 1 | Quote 2 | Quote 3 | Best Quote | Gap (₹) | Gap (%) | Action |
|---|---|---|---|---|---|---|---|---|
| BKT-001 | ₹5,398 | ₹6,200 | ₹5,900 | ₹6,800 | ₹5,900 | +₹502 | +9.3% | Negotiate Q2 |
| PLT-002 | ₹1,840 | ₹1,780 | ₹1,650 | ₹1,900 | ₹1,650 | -₹190 | -10.3% | Qualify Q3 |
Where Excel Should Cost Models Break
Version sprawl
Every time an engineer starts a new model, they copy last quarter's file. Within six months, you have twelve different versions of "SCM_v3_FINAL_revised2.xlsx" with different rate libraries, different overhead assumptions, and no audit trail.
Rate library staleness
Material prices from Tab 2 get updated in one file but not the others. One analyst is using June commodity prices; another is still on January figures. Both models are presented in the same negotiation.
BOM scale ceiling
Building an individual model for each line item of a 200-part BOM is not feasible. A cost engineer spending 4–6 hours per component simply cannot cover full BOM scope in the timeframe an RFQ demands.
No geometry awareness
Excel has no idea what the part looks like. Cycle time estimates are the engineer's best judgment from a drawing — which means model quality varies entirely by individual experience and how carefully they read the drawing.
Design changes break everything
When engineering updates a tolerance or changes a material, nobody updates the should cost model. The negotiation proceeds on a model that reflects a part that no longer exists.
No supplier tracking
Excel stores a snapshot of one quote cycle. There is no mechanism to track how supplier performance against should cost evolves over time.
The AI Approach: What Changes
An AI-powered should cost modeling platform addresses each of these limitations structurally:
Single source of truth for rates and materials
Rate libraries and material prices live in a centrally managed database, updated by the platform — not by individual engineers remembering to edit Tab 2.
Geometry-aware cost estimation
Import a CAD file and the platform reads part geometry directly — pocket depths, bore diameters, wall thickness, surface area — and assigns cycle time estimates based on geometric analysis, not engineer judgment.
BOM-level automation
Upload a 150-line BOM and the platform returns a should cost baseline for every component, simultaneously. What takes an Excel-based team two weeks happens in under an afternoon.
Design change propagation
When a component is revised, the model updates automatically. The should cost database reflects the actual current design, not last month's drawing.
Continuous supplier benchmarking
Actual quote data is stored alongside should cost models. Over time, the platform tracks which suppliers are consistently well-priced and which consistently overcharge — creating a supplier performance view that no spreadsheet can replicate.
Excel + AI Hybrid: The Transition Path
Many organisations do not switch from Excel to AI overnight — and they do not need to. A practical transition path:
Build your Excel model correctly using the tab structure above. Discipline rate library maintenance. Start capturing actual quote data systematically.
Run AI-platform models in parallel on 10–15 high-value parts. Compare outputs against your Excel models to calibrate trust.
Migrate primary workflow to the AI platform. Keep Excel for ad hoc analysis on outlier parts or processes outside the platform's coverage.
The Excel discipline you build in Phase 1 makes Phase 2 faster — you already understand the inputs and are better positioned to validate what the AI returns.
Should Cost Model Accuracy Checklist
Before using any should cost model in a live negotiation, validate these:
A model that passes this checklist can be defended line by line. A model that fails it is a liability in a room with an experienced supplier commercial team.
Should Cost Modeling at Scale with Emithran
Emithran's platform automates the full cost build — material, process, labor, overhead, margin — from your BOM or CAD data, using calibrated regional rate libraries and live material pricing.
Your cost engineers move from building models to interpreting them: analysing gaps, identifying VAVE opportunities, and building negotiation strategy — the high-value work that Excel consumes entirely.
The Excel model gets you started. Emithran gets you to scale.
When your BOM outgrows what one cost engineer can model manually, see what AI-powered should cost modeling looks like on your actual parts.
Skip the Spreadsheet — See Emithran's AI Should Cost Engine →Frequently asked questions
Is there a free should cost model template available?
The tab structure and formulas in this article give you everything needed to build a rigorous Excel-based should cost model from scratch. The critical investment is in populating the rate library and material price database with accurate, current data — the template structure itself is straightforward.
How do I estimate cycle time without CAD software?
Use the process time benchmark tab as a starting point, adjusting for part-specific complexity based on the drawing. For machined parts, break the drawing into features (pockets, bores, profiles, threads) and estimate time per feature. Sum them and add 15–20% for setup, handling, and inspection. AI platforms eliminate this estimation by reading geometry directly.
What overhead percentage should I use in a should cost model?
Overhead as a percentage of direct cost typically ranges from 15% to 30% for precision manufacturing facilities, depending on size, automation level, and geography. Indian SME precision shops typically run 18–24%; large automated European facilities can run 25–35%. Use the target supplier's geography and scale as your reference, not your own facility's overhead structure.
How do I handle should cost for purchased components like fasteners and bearings?
For standard purchased components, use market price benchmarking rather than manufacturing cost modeling. Obtain three or more quotes from distributors, apply volume discount assumptions, and add incoming inspection cost. These items do not need a manufacturing should cost model — they need a commodity price benchmark.
Should my should cost model include tooling cost?
For standard cutting tools (inserts, drills, taps), tooling cost is typically already embedded in the machine rate as a consumable cost. For special tooling or fixtures required specifically for your part, amortise the tooling investment across the expected production life and add it as a separate line item in the cost build.
