Angan Sweets — Supply Chain Workbook Design

1. Workbook architecture

Use one row per document line or inventory movement. Do not overwrite historical transactions; corrections must be posted through adjustment/reversal rows. All operational tabs use immutable IDs and reference only approved master data.

Internal master data required: supplier names, approved material specifications, actual branch/factory locations, recipes/BOMs, standard yields, storage limits, prices, and approval limits are not publicly confirmed and must be maintained by the business.

Supplier Lot

→

GRN + QC

→

Raw-material Lot Ledger

→

Production Batch

→

Finished-Goods Lot

→

Branch Transfer + Receipt

→

Branch Consumption / Sale / Waste

Workbook conventionsISO dates YYYY-MM-DD; 24-hour time; currency NPR; weights stored in base kg/litre/each; operational UOM converted through the UOM master.

Costing principleUse lot-level moving landed cost for raw materials. Finished-goods batch cost equals actual accepted ingredient + packaging + production cost divided by net accepted output.

Inventory principleInventory_Lot_Ledger is the source of truth. Every receipt, issue, transfer, return, waste, and adjustment creates a signed movement quantity and value.

Perishable principleUse FEFO allocation: earliest expiry first, subject to QC release. Block expired, quarantined, rejected, or recalled lots from issue/dispatch.

2. Master-data tabs USER-MAINTAINED

Protect these tabs. Assign one owner for controlled changes and preserve an effective-from date for recipes, specifications, and prices.

Core master tabs and required columns

Tab	Unique key	Required columns	Purpose
M_Company_Sites	`SITE-001`	Site_ID, Site_Name, Site_Type (Central WH/Factory/Branch/Restaurant/Transit), Address, City, Active, Dispatch_Cutoff, Receiving_Hours, Storage_Capacity, Manager_ID	Locations; create branch records for Tripureshwor, Lazimpat, Battisputali, Baluwatar, Sanepa, Imadol, Maharajgunj, Kirtipur only after internal verification.
M_Suppliers	`SUP-001`	Supplier_ID, Legal_Name, Contact, PAN/VAT, Address, Payment_Terms_Days, Default_Currency, Lead_Time_Days, Approved_Status, Supplier_Rating, QA_Contact, Bank/Tax_Reference	Approved vendors and commercial terms.
M_Materials	`RM-DAI-001`	Material_ID, Material_Name, Category, Subcategory, Base_UOM, Purchase_UOM, Conversion_to_Base, GST/VAT_Rate, Storage_Type, Min_Temp, Max_Temp, Shelf_Life_Days, FEFO_Flag, QC_Required, Reorder_Level, Safety_Stock, Std_Cost, Active	Raw materials, packaging, consumables, and cooking oil.
M_Products	`FG-SWT-001`	Product_ID, Product_Name, Category, Sales_UOM, Base_UOM, Shelf_Life_Hours/Days, Storage_Type, Standard_Yield, Standard_Cost, Recipe_Version, Dispatch_Eligible, Active	Finished sweets, namkeen, bakery, packaged foods, restaurant pre-prep and menu ingredients.
M_BOM_Recipe	`BOM-FG-SWT-001-V03`	BOM_ID, Product_ID, Version, Effective_From, Output_Qty_Base, Input_Material_ID, Std_Qty_Base, Allowable_Variance_%, Scrap_Allowance_%, Stage, Active, Approved_By	One row per ingredient/packaging component. Include recipe standard only after operational sign-off.
M_UOM	`KG`	UOM, UOM_Name, UOM_Type (Weight/Volume/Each), Base_UOM, Conversion_Factor, Decimal_Places, Active	kg, g, litre, ml, each, box, carton, tray, packet.
M_QC_Specs	`QCS-RM-001`	QC_Spec_ID, Material_ID, Test_Name, Test_Method, Min_Value, Max_Value, Target_Value, Test_UOM, Mandatory, Sample_Size, Hold_Days, Critical_Flag	Incoming and in-process checks: temperature, odor, appearance, packaging integrity, dates; oil quality/reuse checks.
M_Storage_Locations	`LOC-FAC-CHILL-01`	Location_ID, Site_ID, Location_Name, Zone, Storage_Type, Temperature_Range, Capacity_Base_UOM, Active	Receiving, quarantine, dry store, cold store, freezer, production, dispatch, branch store.
M_Reason_Codes	`WST-EXP`	Reason_Code, Process (QC/Waste/Return/Adjustment/Variance), Description, Requires_Approval, Chargeable_To, Active	Examples: expiry, damage, temperature breach, over-issue, underweight, production loss, customer return.
M_Users_Approvals	`USR-001`	User_ID, Employee_Name, Role, Site_ID, Approval_Limit_NPR, Active, Email	Receivers, QC, production, dispatch, branch managers, finance, approvers.

Suggested material categories

Dairy & perishablesMilk, cream, khoya/mawa, paneer, butter, ghee, curd; record receipt temperature, manufacturer lot, production/expiry dates.

Dry ingredientsMaida, besan, wheat flour, sugar, jaggery, glucose, spices, leavening agents, edible gum.

Nuts & inclusionsCashew, almond, pistachio, raisins, fig/anjeer, coconut, seeds, flavors and colours.

Oil lifecycleVegetable oil and frying ghee: opening quantity, fry-cycle use, filtration, quality test, reuse count/hours, discard and disposal approval.

PackagingSweet boxes, labels, paper bags, cups, containers, seals, trays, transit cartons; track as inventory and BOM components.

Finished categoriesMithai, namkeen/snacks, bakery, confectionery, chaat/pre-prep, restaurant ingredients, beverages such as lassi.

3. Operational transaction tabs

Recommended ID pattern: prefix + site/date + sequence, e.g. GRN-FAC-20260710-001. IDs must be generated once, then pasted as values. A document can have multiple line IDs: GRN_ID plus GRN_Line_ID.

Tab / record grain	Unique IDs	Key columns	Ledger effect
T_Purchase_Order one material line	PO_ID, PO_Line_ID	PO_Date, Supplier_ID, Delivery_Site_ID, Material_ID, Ordered_Qty, Ordered_UOM, Ordered_Qty_Base, Quoted_Unit_Rate, Tax_Estimate, Freight_Estimate, Expected_Delivery, Spec_ID, PO_Status, Approved_By	Commitment only; no stock movement.
T_GRN_Receipt one received supplier lot line	GRN_ID, GRN_Line_ID, Internal_Lot_ID	Receipt_DateTime, PO_ID/Line, Supplier_ID, Material_ID, Supplier_Lot_No, Manufacturer, Mfg_Date, Expiry_Date, Received_Qty, Received_UOM, Received_Qty_Base, Accepted_Qty_Base, Rejected_Qty_Base, Short/Excess_Qty, Invoice_No, Invoice_Unit_Rate, Tax, Freight, Other_Charges, Receipt_Temp, Packaging_Condition, Receiving_Location_ID, Received_By, QC_Status	Post accepted stock only after QC release; rejected quantity stays in quarantine/rejection.
T_QC_Inspection one test/result	QC_ID	Inspection_DateTime, Internal_Lot_ID, GRN_Line_ID, QC_Spec_ID, Test_Name, Result_Value, Result_UOM, Pass_Fail, Visual_Result, Temperature_Result, Date_Label_Valid, Packaging_Integrity, Disposition (Released/Hold/Reject), Hold_Reason, Inspector_ID, Approved_By	Controls availability. No release = no issue/transfer.
T_Inventory_Lot_Ledger one movement per lot/location	Ledger_ID	Movement_DateTime, Transaction_Type, Source_Doc_Type, Source_Doc_ID, Internal_Lot_ID, Material/Product_ID, Site_ID, Location_ID, In_Qty_Base, Out_Qty_Base, Net_Qty_Base, Unit_Landed_Cost, In_Value, Out_Value, Net_Value, Expiry_Date, QC_Status, Counterparty_Site_ID, Entered_By, Approved_By	System-controlled source of truth.
T_Factory_Issue one lot issued to batch	FI_ID, FI_Line_ID	Issue_DateTime, Production_Batch_ID, From_Location_ID, To_Production_Area, Internal_Lot_ID, Material_ID, Requested_Qty_Base, Issued_Qty_Base, Issue_UOM, Unit_Landed_Cost, Issue_Value, FEFO_Exception, Exception_Reason, Issued_By, Received_By	Raw-material lot out of store; WIP/production batch input.
T_Production_Batch one finished batch	Production_Batch_ID, FG_Lot_ID	Batch_Date, Product_ID, BOM_ID, Factory_Site_ID, Production_Area, Planned_Output_Qty, Actual_Gross_Output_Qty, QC_Released_Output_Qty, Rework_Qty, Production_Waste_Qty, Expected_Input_Cost, Actual_Input_Cost, Labour_Cost, Utility/Overhead_Cost, Actual_Batch_Cost, Yield_%, Cost_per_Base_UOM, Start_Time, End_Time, Supervisor_ID, QA_Release	Create finished-goods lot only for released output.
T_Production_Consumption one ingredient lot consumed	PC_ID	Production_Batch_ID, Internal_Lot_ID, Material_ID, BOM_Std_Qty_Base, Actual_Consumed_Qty_Base, Return_To_Store_Qty_Base, Net_Consumed_Qty_Base, Std_Cost, Actual_Landed_Cost, Quantity_Variance, Cost_Variance, Variance_Reason, Verified_By	Links each finished batch to every input lot for genealogy.
T_Factory_Transfer factory/central dispatch line	FT_ID, FT_Line_ID	Dispatch_DateTime, Source_Site_ID, Destination_Site_ID, FG_Lot_ID/Internal_Lot_ID, Product/Material_ID, Dispatch_Qty_Base, Unit_Cost, Dispatch_Value, Expiry_Date, Remaining_Shelf_Life_Hours, Vehicle/Carrier, Seal_No, Dispatch_Temp, Condition, Dispatch_By, Approval_Status	Stock out from source; stock in transit.
T_Branch_Receipt one transfer line received	BR_ID, BR_Line_ID	Receipt_DateTime, FT_ID/Line, Branch_Site_ID, Lot_ID, Product/Material_ID, Expected_Qty_Base, Received_Qty_Base, Accepted_Qty_Base, Rejected_Qty_Base, Quantity_Variance, Receipt_Temp, Seal_Intact, Condition, Expiry_Date, Receiver_ID, QC/Acceptance_Status, Variance_Reason	In-transit to branch inventory; rejected quantity becomes return/claim.
T_Branch_Consumption_Sales daily lot/SKU use	BC_ID	Business_Date, Branch_Site_ID, Lot_ID, Product/Material_ID, Opening_Qty, Branch_Production_Qty, Received_Qty, Sold_Qty, Restaurant_Consumed_Qty, Complimentary_Qty, Closing_Count_Qty, Theoretical_Closing_Qty, Variance_Qty, Unit_Cost, Cost_of_Consumption, Counted_By, Approved_By	Supports branch use, restaurant ingredient consumption, and sale-related reconciliation.
T_Waste_Return_Adjustment one lot adjustment	WRA_ID	DateTime, Transaction_Type (Waste/Return/Adjustment/Recall), Site_ID, Location_ID, Lot_ID, Product/Material_ID, Qty_Base, Unit_Cost, Value, Reason_Code, Photo/Reference_Link, Disposal_Method, Return_Destination, Raised_By, Approved_By, Finance_Posted	Negative for waste/adjustment out; positive for approved return in.
T_Oil_Lifecycle one oil event	OIL_ID	DateTime, Oil_Lot_ID, Fryer/Equipment_ID, Event_Type (Open/Add/Use/Filter/Test/Discard), Qty_Added, Qty_Remaining, Fry_Hours, Reuse_Count, Test_Result, Test_UOM, Result_Status, Discard_Qty, Disposal_Reference, Operator_ID, QA_Approval	Separate oil consumption/waste entries feed the lot ledger.

Minimum lot genealogy fields

Every traceable record must carry: Lot_ID, Material_or_Product_ID, Source_Doc_ID, Site_ID, Location_ID, Movement_DateTime, Qty_Base, UOM, QC_Status, Expiry_Date, Unit_Landed_Cost, User_ID, and Approval_Status.

4. Google Sheets formulas and calculation logic

Use named ranges where possible. The examples below assume columns are converted to consistent base units. Protect formula columns and use ARRAYFORMULA only after testing performance with real transaction volumes.

Calculation	Formula / logic	Decision use
Receipt quantity variance	`=Received_Qty_Base - Ordered_Qty_Base`	Supplier short/excess delivery.
Accepted quantity	`=Received_Qty_Base-Rejected_Qty_Base`	Inventory availability and supplier quality performance.
Landed cost per base UOM	`=(Invoice_Line_Value+Tax+Freight+Other_Charges-Credit_Note_Value)/Accepted_Qty_Base`	Actual procurement and inventory cost.
PO price variance	`=(Invoice_Unit_Rate-Quoted_Unit_Rate)*Accepted_Qty_Base`	Invoice/PO control and supplier negotiation.
Lot stock balance	`=SUMIFS(Ledger!In_Qty,Ledger!Lot_ID,$A2)-SUMIFS(Ledger!Out_Qty,Ledger!Lot_ID,$A2)`	Available stock by lot. Add Site_ID/Location_ID criteria for location-level balance.
Expected BOM quantity	`=Actual_Output_Qty_Base*(BOM_Std_Qty_Base/BOM_Output_Qty_Base)`	Expected ingredient use for actual production output.
Production quantity variance	`=Net_Consumed_Qty_Base-Expected_BOM_Qty_Base`	Overuse/underuse by ingredient, batch, operator, or recipe.
Production cost variance	`=Actual_Ingredient_Cost-Expected_Ingredient_Cost`	Separates price/cost impact from quantity impact.
Yield %	`=QC_Released_Output_Qty/Planned_Output_Qty`	Batch execution and process-loss tracking.
Actual batch cost	`=SUMIFS(Consumption!Actual_Cost,Consumption!Batch_ID,$A2)+Labour_Cost+Utility_Overhead+Packaging_Cost-WIP_Credit`	Finished-goods lot cost.
Actual unit cost	`=Actual_Batch_Cost/QC_Released_Output_Qty`	Margin and transfer valuation.
Transfer receipt variance	`=Accepted_Qty_Base-Dispatch_Qty_Base`	Transit shortage, damage, and branch receiving control.
Branch stock variance	`=Closing_Count_Qty-(Opening_Qty+Received_Qty+Branch_Production_Qty-Sold_Qty-Restaurant_Consumed_Qty-Approved_Waste_Qty-Returns_Qty)`	Daily branch reconciliation.
Remaining shelf life	`=Expiry_Date-NOW()`	FEFO alerts and markdown/redistribution decisions.

Recommended helper formulas

=IFERROR(XLOOKUP([@Material_ID],M_Materials!A:A,M_Materials!F:F),"")
=IF([@Expiry_Date]<TODAY(),"EXPIRED",IF([@Expiry_Date]<=TODAY()+[@Alert_Days],"EXPIRY RISK","OK"))
=IF(AND([@QC_Status]="Released",[@Available_Qty_Base]>0,[@Expiry_Date]>TODAY()),"ISSUE ALLOWED","BLOCK")
=IFERROR(([@Actual_Consumed_Qty_Base]-[@Expected_BOM_Qty_Base])/
[@Expected_BOM_Qty_Base],0)

For Google Sheets table-style references unavailable in a given sheet layout, replace bracketed references with fixed cell references. Use XLOOKUP or INDEX/MATCH according to your Workspace version.

5. Validation, approvals, and audit controls

Data validation lists

Material/Product/Supplier/Site/Location IDs from master tabs only.
QC status: Pending, Hold, Released, Rejected.
Approval status: Draft, Submitted, Approved, Rejected, Posted.
Transaction type: GRN, QC Release, Issue, Production, Transfer Out/In, Sale/Consumption, Waste, Return, Adjustment, Recall.
Reason codes sourced from M_Reason_Codes.

Conditional formatting

Red: expired lot, QC rejected, negative stock, unapproved adjustment.
Amber: expiry within alert days; receipt/transfer variance exceeds tolerance; cost variance above threshold.
Blue: lots on QC hold or in transit.
Green: released lot and completed reconciliation.

Audit fields on every transaction

Created_Timestamp, Created_By, Last_Updated_Timestamp, Last_Updated_By.
Approval_Status, Submitted_By/At, Approved_By/At, Rejection_Reason.
Source_Document_Link or scan reference, where maintained internally.
Locked_Posting_Flag after finance/stock close.

Mandatory workflow gates

GRN cannot be posted without supplier, material, received quantity, invoice reference, dates for perishable/dated materials, and receiving user. QC-required material remains in quarantine until a released QC inspection is recorded. Factory issue validates released QC status, positive available lot stock, FEFO sequence, and expiry date. Production batch must use an approved BOM version and retain every consumed input lot. Transfer cannot dispatch a blocked, expired, unreleased, or insufficient-shelf-life lot. Branch receipt must confirm quantity, condition, temperature where relevant, seal, and accepted/rejected quantity. Waste, return, and inventory adjustments require reason code, lot ID, value, and approval based on threshold. Cooking oil discard requires quality/disposal reference and authorized approval.

6. Dashboard tabs and KPIs

Create a Dashboard tab with date, site, category, supplier, branch, and product filters. Use Pivot Tables or QUERY against the ledger and transaction tabs. Do not calculate stock from manually entered summary cells.

Dashboard block	KPI	Formula concept / visual
Inventory health	Stock value, days of cover, below reorder, negative stock, expiry-risk value	Lot ledger balance × landed cost; bar by category/site; expiry heat map for 0–1, 2–3, 4–7, 8+ days.
Procurement	Purchase price variance, fill rate, receipt rejection %, supplier lead-time variance	Compare PO rate/qty/date with GRN rate/accepted qty/date by supplier and material.
Factory efficiency	Yield %, ingredient overuse %, batch cost variance, cost per kg/unit	Expected BOM and standard cost versus actual lot consumption and production output by product/BOM version.
Transfer control	Dispatch-to-receipt variance, transit loss value, on-time receipt %, shelf-life at delivery	Match FT line to branch receipt line; report unresolved transfers and disputed quantities.
Branch control	Daily stock variance, waste %, return %, restaurant consumption cost	Reconcile theoretical versus physical closing stock by branch/product/lot; rank loss reasons.
Quality & compliance	QC hold/reject %, expired stock, oil discard compliance, traceability completeness	Count lots lacking mandatory dates/QC, oil events missing test/disposal data, and batches without full input lot mapping.

Executive KPI definitions

Material cost variance: Actual accepted landed cost − expected PO/standard cost.
Ingredient usage variance: Actual net consumed quantity − BOM expected quantity for actual output.
Production yield loss: Planned output − QC-released output, valued at standard/actual batch cost.
Branch shrinkage: Theoretical closing stock − physical closing stock, valued at lot/weighted unit cost.
Waste %: Waste quantity ÷ (opening + receipts + production) × 100.
Traceability coverage: Released production/dispatch quantity with complete upstream lot link ÷ total released quantity × 100.

7. Build sequence and implementation notes

Create all M_* master tabs first and populate controlled lists marked USER-MAINTAINED.
Set base UOM and conversion factors before entering any transactions. Avoid mixing “box”, “kg”, and “piece” without conversion.
Build procurement → QC → ledger posting first; test a single supplier lot end-to-end.
Add production/BOM consumption and verify that one finished lot can be traced back to all raw-material lots.
Add transfer and branch receipt matching; then enable branch reconciliation and waste controls.
Lock formula, ID, approval, and posting columns. Give branches edit access only to their operational input tabs.
Run a weekly cycle-count reconciliation and a monthly cost/variance close. Post differences through T_Waste_Return_Adjustment, never by editing balances.

Practical starting point: begin with the highest-value and highest-risk categories—dairy, ghee/oil, nuts/dry fruits, sugar, flour, and finished sweets—then add packaging and restaurant-prep materials. This gives early visibility into price changes, yield loss, expiry risk, transfer shortages, and branch-level shrinkage without delaying rollout.