IC Costing · v2.2
One database. One console per person. Every quote traceable. Now: many harnesses, one project quotation.
1 · The big idea
Earlier every costing was a separate Excel file — copied, re-typed, lost. Now there is one IC Costing DB that holds every costing, every review remark, every change and every PDF ever issued. You never open the DB. You work in your own console: a Google Sheet that shows one costing at a time, loaded from the DB and saved back to it with a button.
Think of the DB as the filing cabinet and your console as the desk: pull one file out, work on it, put it back. The cabinet remembers who changed what, when and why.
v2.2 — projects. A customer often sends one system made of many harnesses. Each harness is still its own costing (own BOM, own process, own review). A project is the folder they sit in: it knows how many of each harness go into one set, carries the one copper basis and margin tier the whole project is quoted on, and prints one quotation — price per harness × qty per set = price per set, × sets = project value.
2 · How it fits together
R&D console
Ambrish's sheet. Creates costings, fills BOM + process, submits for review, accepts or pushes back the reviewer's changes.
⇄
IC Costing DB
Single source of truth (Google Sheet, owned by ISA)
- Costings — one row per revision
- BOM_Lines · Process_Lines
- Reviews · Review_Lines
- Change_Log · Export_Log
- Users · Categories · Standard_Ops · Tiers · Terms (masters)
- Dashboard — counts & who has what open
- Projects · Project_Dashboard — one row per customer project; roll-up of its costings (v2.2)
⇄
Purchase / Approver console
Gupta ji's sheet. Loads a submitted costing, reviews rates, returns changes or approves, sets margin, exports the customer PDF, locks.
Every console has the same layout. Which menu buttons you see depends on the permissions ISA ticks against your name in the DB's Users tab. PDFs land in the shared Costing Exports Drive folder; emails go out automatically at each hand-off.
3 · Life of a costing (status flow)
Draft→In review→Changes requested→ (Apply) →Draft→In review→Approved→Quoted→Locked
Side exits: any open status → Cancelled (dead quote) · Quoted / Locked → New revision → Draft as R01, R02…
| Status | Meaning | Who can edit lines | Next button |
|---|
| Draft | Being prepared | R&D | Submit for review |
| In review | Purchase is checking rates / process | Nobody edits lines; reviewer fills the Review tab | Return to R&D or Approve |
| Changes requested | Reviewer flagged lines | R&D (after Apply) | Apply approved changes |
| Approved | Cost + margin accepted | Nobody | Export customer quotation |
| Quoted | Customer PDF has been issued | Nobody | Lock this revision |
| Locked | Frozen history — immutable | Nobody, ever | New revision when something changes |
| Cancelled | Quote is dead / lost | — | Hidden from the list by default |
4 · Who does what
R&D — prepares
Ambrish- New costing from the drawing: part facts, copper basis, materials with drawing quantities, rates with date + source, process ops with pcs/hr
- Save → Validate → Submit for review
- After a return: Apply approved changes, answer queries in Notes, submit again
- New revision on ECR / rate refresh / copper move
Purchase — verifies & approves
Gupta ji- Load the submitted costing; check copper basis, cable ₹/m, connector make (genuine vs equivalent), labour sanity
- Type corrections in the Review tab's → columns with a reason; mark each line Change / Query / OK
- Return to R&D, or Approve with the margin tier
- Export the customer quotation; Lock the revision
Admin — oversight only
ISA- Adds people and ticks permissions in DB › Users, runs
grantAccess - Edits defaults (wage, validity, T&C, company block) and masters (categories, standard ops, tiers)
- Reads the Dashboard, Change_Log and Export_Log
- DB menu › Admin: Reset to Draft (unstick a costing — nothing deleted) · Purge (delete a costing everywhere so R&D can start it again; refused once it has been quoted) · Tripwire (any hand-edit on a DB table emails ISA and is logged)
- Does not sit in the daily loop
Permissions → buttons: Create / edit · Review · Approve · Set margin · Export customer · Lock · Admin. If a button you expect is missing, ask ISA to tick it — nothing else needs to change.
5 · Your console, tab by tab
The colour code is the same everywhere: yellow = you type here grey = system / formula, leave it green = needs the "Set margin" permission orange row = a flag to fix before submitting
| Tab | What it is | You touch |
|---|
| Start Here | The load panel and this role map. Shows which costing is loaded, its status, DB version, who else has it open, and whether you have unsaved changes. | Pick a costing in B3, untick D3 to also list Locked / Cancelled. |
| Info | Part facts for the loaded costing: customer, part no, drawing no + rev + date, batch qty, copper rate ₹/kg + basis date, notes for reviewers. | R&D fills the yellow cells. Status, wage snapshot, costing ID and "supersedes" are set by the system. Rows 23–24 (v2.2): Project ID (dropdown of live projects) and Qty per set — fill both to put this harness into a project. |
| BOM | Bill of materials, up to 150 lines. Drawing qty in mm or pcs; the sheet converts to metres, adds the category allowance, and multiplies by the rate. | Category (dropdown), description, make / part no, drawing qty, rate, rate date, rate source. Allowance % and "Show to customer" pre-fill and can be overridden. |
| Process | Operations in sequence, up to 80. Picking a standard op pre-fills manpower and pcs/hr; the flag column warns if pcs/hr is outside the sanity band. | Process (dropdown), manpower, setup minutes per batch, pcs/hr as staffed. |
| Review | Fills itself when a review is open: every BOM line and op with its current qty / rate / source, and empty → columns beside them. | Reviewers type the corrected value, a reason, and Line status = Change (apply this) / Query (R&D must answer) / OK. Your name stamps automatically.
R&D answers each Change in the last two columns: R&D response (blank = accept · Push back) and R&D reply. |
| Cost Summary | Cost build-up per piece (wire, connectors, terminals+seals, sleeving, tags → RMC; + conversion → COST), margin tier → quoted price, sensitivity table, approval names. | Only the margin tier (green) — and only if you hold "Set margin". |
| Project | (v2.2) The roll-up of every costing under one project: one line per harness (latest live revision), its status, cost, tier, price, qty/set, cost and price per set, and a flag column. Header block: sets to quote, project copper basis, project tier, roll-up text, drift. | Pick the project in B3 → . Yellow header cells + . Green tier cell needs "Set margin". |
| Department | Live view of the whole department pulled from the DB Dashboard: costings by status, who is waiting on whom, who has what open, every open costing newest first, and (v2.2) the all-projects block. | Nothing — read-only. First time only: click the #REF! cell and press Allow access. |
One costing at a time. Loading another costing replaces what is on screen. If you have unsaved edits the console asks first. Nothing is lost in the DB — only what you typed since your last Save.
6 · Playbooks
R&D A · Make a new costing
— answer the four prompts: customer, part no, part name, drawing no.The costing ID is built for you, e.g. COST-ShiganQuantum-WEMM-LS-20385-R00. Batch qty, wage, validity and T&C version are copied in from Settings as a snapshot.
Info tab — drawing rev + date, and the copper rate ₹/kg with its date (from Wire Costing / Purchase). Batch qty if not 100.Copper basis is printed on the quotation and drives the price-variation clause. Never leave it at a guess without saying so in Notes.
BOM tab — one line per material from the drawing take-off: category, spec words, make / part no, drawing qty (mm for wire and sleeve, pcs for parts), rate, rate date, rate source.Wire descriptions must contain FR / FRLS or the row flags "Spec?". Rates older than 90 days flag "Rate >90d".
Process tab — pick each op in order; adjust manpower, setup min and pcs/hr to how the line is actually staffed."Below band" / "Above band" means your pcs/hr is outside the standard range for that op — fine if true, but say why in Notes.
then — fix every issue it lists; warnings (e.g. copper basis > 30 days) are not blocking.Save writes to the DB and bumps the version. Start Here shows "saved".
— status becomes In review; Purchase (and BD) receive an email with the internal review PDF attached.Write anything a reviewer must know — assumptions, open points — in Info › Notes before you submit.
Purchase B · Review a submitted costing
→ on Start Here pick the one marked In review → .The list shows ID · status · part name, and ⚠ + a name if someone else has it open.
Info tab — verify the copper basis and date against the current Wire Costing sheet.
Review tab — for each BOM line or op you disagree with: type the corrected value in the → column (qty, rate, source · men, setup, pcs/hr), write the reason, set Line status.Change = R&D applies your number with one click. Query = you need an answer first. Leave lines you accept blank or OK. Your name stamps in the By column.
On a second round (V02…) lines R&D pushed back come pre-filled in blue with their reply in the remark — rule on them again: OK to accept R&D's position, Change to insist.
— remarks are stored in the DB against this review ID (…-V01, V02…).
Either (status Changes requested; R&D emailed with every flagged line) — or, if nothing is flagged, go to playbook D.
R&D C · Answer the review: accept, push back, resubmit
Load the costing (status Changes requested). The Review tab shows the returned review with every flagged line.
For each Change line decide in the last two columns: leave R&D response blank to accept, or set Push back and write why in R&D reply.A push-back without a reply is refused — the reviewer must be able to act on it. Queries are answered in Info › Notes, not here.
— accepted lines are written into your BOM / Process cells (rate changes stamp today's date) and logged in Change_Log. Pushed-back lines are logged as PUSHED BACK with your reply and are not applied.Nothing is re-typed. Status returns to Draft.
Answer every Query in Info › Notes (and edit lines if the answer changes them).
→ . A new review (V02) opens with your pushed-back lines already in it, your reply beside the reviewer's remark, so the argument has one thread. The approver has the last word.
Approver D · Approve → quote → lock
With the costing loaded and no Change / Query lines open: Cost Summary — pick the margin tier (5.5 / 6 / 8 / 10 / 13 % or Custom). The sensitivity table shows the price at every tier.Needs the "Set margin" permission. Without it the tier stays as it was.
— confirm cost · tier · price. Status Approved; approver name recorded; R&D and BD emailed.
— choose whether to show per-line rates or only quantities + one price line. The PDF carries the company block, drawing rev, copper basis, validity date, T&C, and a Quotation / Ref No. (…-E01). Status Quoted.Lines with "Show to customer" = N are folded into "Other materials (consolidated)".
— once the quote has gone out. Locked is permanent; anything later is a new revision.
R&D E · Re-cost: ECR, rate refresh, copper move
Load the quoted / locked revision (untick "Hide Locked" on Start Here if needed).
— give the reason (ECR / Process addition / Rate refresh / Copper revision / Customer request) and the ECR number if any.R01 is created as a Draft with every line copied. The DB records "supersedes R00 · reason".
Edit what changed, then . On the first save the system compares R01 with R00 and writes every difference to Change_Log automatically — line added, line removed, qty / rate / pcs-hr changed.
Submit for review as usual. The old revision stays exactly as it was quoted.
Anyone F · Dead quote
with a reason. It drops out of the default list but stays in the DB. Locked revisions cannot be cancelled — they are history.
6½ · Projects — many harnesses, one quotation (v2.2)
When to use a project. The customer will buy the harnesses together and expects one price list. If it is a single part, ignore this section entirely — nothing else changed.
| Term | Meaning |
|---|
| Project | One row in DB › Projects: PROJ-<Customer>-<CODE>, customer, name, RFQ ref, sets to quote, project copper basis, project tier, status Open → Quoted → Locked. |
| Qty per set | How many pieces of this harness go into ONE complete system. Lives on the costing (Info › row 24). Price per set = Σ (price per piece × qty per set). |
| Quote line | The latest live revision of each part in the project. If R00 was quoted and R01 exists, R01 is the line; R00 is history and is counted as "superseded rev(s) hidden". Cancelled costings never appear. |
| Drift | A costing whose copper basis or tier differs from the project's. Flagged per line and in the header; export asks before proceeding. One basis per quotation is the goal — the T&C print the project's copper basis. |
| Project status | Derived from the lines: the header shows counts by status and the lowest one. Export is refused until every line is Approved / Quoted / Locked and has a qty per set. |
R&D G · Cost a project
— five prompts: customer, short project code, project name, RFQ ref, sets to quote.Once per customer project. The ID is built for you, e.g. PROJ-AcmeSystems-XR200. Tier defaults to 8 %.
For each harness: — the fifth prompt asks for the Project ID (paste it; qty per set starts at 1) — or, on an existing costing, fill Info › Project ID (dropdown) and Qty per set.Save refuses a Project ID that does not exist in DB › Projects — create the project first. Validate flags a project without a qty per set.
Fill BOM + Process, , — exactly as for a single part. Each harness is reviewed on its own.
Any time: Project tab › pick the project › to see all lines, what is still Draft, and which lines drift from the project basis.Loading a project does not unload your costing — the two live on different tabs.
Purchase / Approver H · Quote a project
Project tab: set sets to quote, the project copper basis + date and the project tier (green — needs Set margin) → .Every line whose copper basis differs from this shows "Cu basis" in the flag column. Ask R&D to align them, or accept the drift knowingly at export.
— writes the tier (and the resulting price) onto every line still Draft / Changes requested / In review.Approved lines are skipped on purpose: an approved price changes only through a new revision. Anyone with one of those costings loaded must reload before saving (version guard).
Review and each harness as usual (playbooks B and D). The Project tab's roll-up tells you how many are left.
— one PDF: company block, project, RFQ ref, validity, copper basis, sets; the table of harnesses with price per piece and per set; PRICE PER SET and project value × sets; T&C; sign-off. Optional annex: each harness's bill of materials, quantities only.Every Approved line becomes Quoted; the project becomes Quoted. Export ID PROJ-…-E01 in Export_Log. Refused while any line is not yet approved.
— every line Locked, project header frozen. Later changes = new revisions of the harnesses concerned; the project then shows the new revision as its line.
Do — decide qty per set from the customer's system drawing, not from the BOM. It multiplies the whole quotation.
Don't — quote a project with mixed copper bases unless you mean it. The drift warning is there so you notice, not so you click through it.
7 · The maths (so you can check it by hand)
Materials
net qty (m) = drawing mm ÷ 1000 × (1 + allowance %)
net qty (pcs) = drawing pcs × (1 + allowance %), rounded up
cost ₹/pc = net qty × rate
RMC = Σ cost of all lines
Allowance by category (defaults): Wire 2 % · Sleeve 3 % · HST 3 % · Tape 5 % · Connector / Terminal / Seal / Tag 0 %. Override per line if the drawing demands it.
Labour
run min/pc = 60 ÷ pcs/hr × manpower
setup min/pc = setup min per batch ÷ batch qty
labour ₹/pc = (run + setup) ÷ 60 × loaded wage ₹/hr
Conversion = Σ labour of all ops
Loaded wage (wage + PF + ESI + bonus + leave) is snapshotted into the costing when it is created — Settings changes later never move an old quote.
COST ₹/pc = RMC + Conversion QUOTED PRICE = COST × (1 + margin tier)
Flags you will see
| Flag | Means | Fix |
|---|
| Category? / Qty? / Rate? / Rate date? / Source? | A required BOM cell is blank | Fill it — Submit is blocked until clear |
| Spec? (FR/FRLS) | Wire description has no FR / FRLS | Add the spec words from the drawing |
| Rate >90d | Rate date older than 90 days | Get a fresh rate from Purchase, or note why the old one holds |
| Manpower? / Pcs/hr? | Op has no staffing or speed | Fill it |
| Below band / Above band | pcs/hr outside the standard range for that op | Correct it, or justify in Notes |
| ⚠ Copper basis is N days old | Warning only | Confirm with Purchase before quoting |
8 · Rules the system enforces (and why)
Snapshots
Wage, batch qty, copper basis, validity days and T&C version are copied into each costing when it is made. Changing Settings never alters an existing quote — what we quoted is what we can prove.
Version stamp
Every Save bumps the version. If someone saved after you loaded, your Save is refused with "Reload first". Nothing is ever overwritten silently.
Checkout = courtesy
Loading marks the costing as open by you. If you load one someone else has open you are warned, they are emailed, and it is logged. It expires after 24 h. It never blocks — the version stamp does the protecting.
Locked = immutable
A locked revision cannot be saved, cancelled or edited. Changes mean a new revision with a reason, and the diff is logged.
Customer PDF only after Approve
The quotation can be exported only from Approved / Quoted / Locked. Internal review packs can be exported any time.
Everything is logged
Applied review changes, push-backs, revision diffs, approvals, locks, cancellations, admin resets and purges → Change_Log. Every PDF → Export_Log with the Export ID printed on its footer.
| IC Costing ▸ | Who | Does | Allowed when |
|---|
| Refresh costing list | All | Pulls the live list of costings + masters from the DB into Start Here | Always |
| Load selected costing | All | Brings the picked costing into your console | Always |
| New costing | R&D | Creates R00 as Draft from four prompts | Create / edit |
| New revision of loaded costing | R&D | Copies the loaded revision to Rn+1 Draft with a reason | Create / edit |
| Save | R&D | Writes Info + BOM + Process to the DB, bumps version | Draft / Changes requested |
| Validate | R&D | Lists blanks and flags; shows cost + price if clean | Always |
| R&D — Submit for review | R&D | Saves, opens a review ID, emails Purchase + BD with the internal PDF | Draft / Changes requested, no issues |
| R&D — Apply approved changes | R&D | Writes every "Change" line into your cells, logs each, sets Draft | Changes requested |
| Reviewer — Save review remarks | Purchase | Stores the Review tab into the DB | In review |
| Reviewer — Return to R&D | Purchase | Sets Changes requested, emails R&D the flagged lines | In review, ≥1 Change / Query |
| Approve costing | Approver | Records approver, tier, price; unlocks customer export | In review, 0 open lines |
| Export — internal review pack (PDF) | All | Landscape A4: build-up, BOM with rate sources, process, sign-off box | Any loaded costing |
| Export — customer quotation (PDF) | Approver | Portrait A4 quotation with T&C; sets Quoted | Approved / Quoted / Locked |
| Lock this revision | Approver | Freezes the revision | Approved / Quoted |
| Cancel this costing | R&D / Approver | Marks a dead quote with a reason | Any status except Locked |
| Project ▸ Load selected project | All | Fills the Project tab with the project's lines and roll-up | Always |
| Project ▸ New project | R&D | Creates a project row from five prompts | Create / edit |
| Project ▸ Save project header | R&D / Purchase | Writes sets, copper basis, tier, notes to the project row | Create / edit; tier change needs Set margin; not Locked |
| Project ▸ Apply project tier to all open costings | Approver | Sets tier + price on every Draft / Changes requested / In review line | Set margin |
| Project ▸ Export — project quotation (PDF) | Approver | One PDF for all lines; Approved → Quoted; project Quoted | Export customer; every line Approved+ with qty/set |
| Project ▸ Lock project | Approver | Locks every line and the project | Lock; every line Approved+ |
| Discard unsaved changes (reload) | All | Reloads the DB copy over your screen | Always |
| Release my checkout | All | Clears the "open by you" mark without saving | Always |
10 · Messages you may see & FAQ
| Message | What it means · what to do |
|---|
| Reload first — X saved this at … (v7); your console has v6 | Someone saved after you loaded. Menu ▸ Discard unsaved changes (reload), redo your edit, Save. |
| Not permitted — your console does not have "…" | That permission isn't ticked for you. Ask ISA; no reinstall needed. |
| Checked out — X loaded this at … Load anyway? | They have it open. Yes is safe (they get an email); the later Save is refused until reload. |
| Cannot save — status is "In review" | Lines are frozen during review. Reviewers use the Review tab; R&D waits for Return. |
| Fix before submitting + a list | Blanks or flags. Clear them all — the list is the same one Validate shows. |
| Nothing flagged on Return | Return needs at least one Change / Query line. If everything is fine, Approve instead. |
| Cannot approve — N line(s) still Change / Query | Open lines must go back to R&D first, or be set to OK. |
| Reply needed — every "Push back" needs a reply | Fill R&D reply on each line you pushed back, then Apply again. |
| Review tab is blank after a Return | Load the costing again — the returned review loads whenever status is Changes requested. |
| Department tab shows #REF! | First use on this console: click the cell → Allow access. It then stays live. |
| Costing is stuck (In review with nobody reviewing, checkout won't clear, returned by mistake) | Ask ISA for a Reset to Draft — status goes back to Draft, nothing is deleted, the reset is logged. |
| "This one is a mess, I want to start again" | Ask ISA for a Purge. Only possible before a quotation has been issued; afterwards Cancel + New revision is the route. |
| Unknown project — X is not in DB › Projects | Create it first (Project ▸ New project) or clear Info › Project ID. Costings only join projects that exist. |
| Not ready — every quote line must be Approved… | The roll-up text names what is still open. Finish those reviews, then export. Lines without a qty per set count as not ready. |
| Basis drift — N off project copper basis. Export anyway? | Some harnesses were costed on a different copper rate than the project header. Align them (new revision or edit while Draft) or accept knowingly. |
| Nothing to apply on Apply project tier | All lines are already Approved or beyond — their price is fixed; change it via New revision. |
| Project tab shows fewer lines than costings exist | Older revisions of the same part are hidden (header says "N superseded rev(s) hidden"); Cancelled costings are excluded. |
| Too big for this console | More than 150 BOM lines or 80 ops. Ask ISA to raise the limits. |
| No recipient email — notification skipped | The role has no email in DB › Users. Tell ISA; the action itself succeeded. |
Do — write the rate source every time ("Wire Costing Sep-26", "Sumitomo quote 03.09"). Reviewers and auditors read it.
Do — use Notes for assumptions and query answers. It travels with the costing and prints on the internal PDF.
Do — Save often. Saves are cheap; unsaved edits are the only thing that can be lost.
Don't — type into grey cells or paste over formula columns (allowance, net qty, cost). Load restores them, but your numbers will be wrong meanwhile.
Don't — edit the DB directly. Every edit goes through a console so it is logged; a hand-edit on a DB table trips an alert to ISA and is logged with your name.
Don't — start a costing in Excel "to move later". Start it in the console; New costing takes four prompts.
Where things live
| Your console | Link emailed by ISA; also in DB › Users › console_link. Bookmark it. |
| PDFs | Drive › Costing WH V4.0 › Costing Exports. Every PDF's link is also in DB › Export_Log. |
| Who has what open / counts by status | Your console › Department tab (live copy of the DB Dashboard). |
| Project roll-ups | Your console › Project tab (pick + Load), or DB › Project_Dashboard (ISA). All-projects block also on Department. |
| Copper / wire rates | Wire Costing sheet (Purchase maintains). |