Fabric Allocation With a Transportation Model in Excel

Two approved mills can both supply three garment units, yet placing each requirement with the cheapest available source one order at a time can produce an expensive overall allocation. One mill's limited capacity may be more valuable on a different destination. A transportation model makes those competing uses visible before purchase quantities are committed.

The model below allocates one technically interchangeable fabric across approved lanes at minimum stated variable cost. It includes a small worked solution and an Excel Solver layout. It does not choose fabric specifications, approve a supplier or authorise mixing shade lots. Those decisions define the feasible lanes before optimisation begins.

Confirm what can actually be substituted

Start with one material and planning period whose quantities can be compared in the same unit. Confirm construction, width basis, finish, buyer approval, shade and delivery requirements. Two fabrics described as the same GSM are not necessarily interchangeable. Technical and commercial owners must authorise substitution before its lower cost appears as an option in the model.

For each source, record the capacity genuinely available to this allocation after prior commitments. For each destination, record the net requirement after usable stock and confirmed receipts. Demand due next week cannot be met by a cheap lane arriving next month. Exclude an ineligible lane or constrain its quantity to zero; do not let an attractive cost compensate for missing approval or timing.

The site's inventory policy guide explains why order-specific fabric commitments differ from routine trims replenishment. This allocation is a decision about which approved source fulfils a stated requirement, rather than a calculation of an economic reorder quantity.

Make the variable cost comparable

Use the incremental cost affected by the allocation, on the same currency, unit and delivery basis. Depending on the decision, that might include fabric purchase and variable freight or only the transport cost between already-owned stocks. State which components are included and avoid counting the same freight twice.

A fixed truck charge, minimum bill, quantity discount or supplier activation fee is not automatically a constant cost per metre. Represent such conditions explicitly in a more suitable model, or restrict this simple model to a range where the linear approximation is defensible. Record tax and exchange-rate conventions where they affect comparison; a spreadsheet cannot resolve an unclear commercial term.

The following illustrative costs are USD per metre for one agreed variable-cost scope. They are invented teaching inputs, not current supplier prices.

Every lane is assumed technically approved and timely. Quantities are divisible metres, with no minimum lot or fixed dispatch cost in this example. Total available supply equals total demand, but that equality is an input condition rather than a rule that should be forced onto real data.

Write the objective and constraints in operating language

This is a linear programming (LP) formulation. Let xij be metres sent from source i to destination j, and cij the stated cost per metre. The objective is the sum of quantity multiplied by cost across all six lanes. Choose nonnegative quantities that minimise that total.

Each source's outgoing metres must be no greater than its available capacity. Each destination's incoming metres must equal its requirement. Any prohibited lane must carry zero. These constraints distinguish an inexpensive feasible plan from a calculation that quietly leaves one factory short.

The SciPy linear programming documentation describes the same objective, equality, inequality and bound structure in a computational form. Whether a team uses a spreadsheet or another solver, the physical meaning of the constraints should remain clear enough for a planner to check by hand.

If supply exceeds demand, leave the source constraints as upper bounds and allow unused capacity. If supply is insufficient, the exact-demand model is infeasible. Identify the shortage and obtain an authorised response rather than relaxing a demand equality until Solver returns an answer.

Compare a local choice with the complete solution

Suppose a planner first gives Mill 2 the whole 700 m requirement for Unit B because its rate is lower there. Mill 2 then has only 100 m left for Unit C. Mill 1 supplies Unit A's 600 m and Unit C's remaining 400 m. Cost is 700 × 4 + 100 × 3 + 600 × 4 + 400 × 8 = USD 8,700.

The complete minimum-cost allocation is different:

Its cost is 600 × 4 + 400 × 6 + 300 × 4 + 500 × 3 = USD 7,500, or USD 1,200 below that local allocation. Mill 2 saves USD 5 per metre against Mill 1 on Unit C, compared with USD 2 on Unit B. Reserving its capacity for the larger saving improves the whole plan.

That explanation is useful even after software returns the answer. A reviewer should be able to explain why moving 100 m of Mill 2 capacity from Unit B to Unit C reduces cost, while Mill 1 supplies the displaced Unit B quantity. An unexplained optimum is harder to maintain when the input sheet changes.

Lay out a small auditable Solver sheet

Place the six costs in B3:D4 and the six decision quantities in B8:D9, using the same row and column order. Put source capacities in F8:F9. Calculate source totals in E8:E9 with row sums. Calculate destination receipts in B10:D10 with column sums, and put requirements in B11:D11.

Use F12 as the objective cell with =SUMPRODUCT(B3:D4,B8:D9). Microsoft's SUMPRODUCT documentation explains the multiplication and summation of corresponding arrays. Mismatched row order can produce a plausible total for the wrong allocation, so label the matrices clearly.

Following Microsoft's Solver guidance, set the objective to minimise F12 by changing B8:D9. Add E8:E9 less than or equal to F8:F9, B10:D10 equal to B11:D11, and nonnegative decision quantities. Select the linear solving method, Simplex LP, for this linear formulation.

Confirm Solver is available in the actual Excel installation. Menu wording and add-in availability vary by platform. This sheet layout is a reproducible specification, not a claim that a particular user's Excel interface has been exercised.

Stress the assumptions before releasing orders

Recalculate when approved capacity changes. If Mill 2 falls to 400 m and Mill 1 can supply 1,400 m, the illustrative optimum sends Mill 2's 400 m to Unit C. Mill 1 supplies 600 m to A, 700 m to B and 100 m to C. Total cost becomes USD 8,600, USD 1,100 above the base case.

That increase is a scenario result, not a general value for every metre of supplier capacity. Lane eligibility, requirement and cost changes can alter which constraint matters. Test the uncertainty that could change the purchase decision instead of reporting one optimum to unnecessary decimal places.

The site's linear programming line-balancing article shows another optimisation application. Its assignment decisions are different from divisible material flows. Whole rolls, supplier activation decisions and minimum lots may require integer or mixed-integer constraints rather than this simple continuous formulation.

Preserve lot and commitment constraints during implementation

A mathematically divisible quantity may not be commercially divisible. If a destination must receive one shade lot, splitting its requirement across two otherwise approved mills may be unacceptable. Identify that restriction before solving. It may require binary assignment variables or a separately approved allocation scenario; rounding a continuous result afterwards does not enforce a single-source rule.

Likewise, a mill's capacity can already be committed to another buyer or week. Keep the reservation register and the model's available-capacity field connected. Reusing the same available metres in several independently solved sheets can produce plans that each look feasible while their combined purchases exceed supply.

Retain the original model result when a planner makes an authorised override. Recalculate its rows, columns and cost under the revised quantities, and record the operating reason. An override may be sensible because new information changes feasibility. It should remain visible as a different decision, rather than being presented as the unchanged output of the earlier model.

Release a plan with a decision record

Before converting the solution into purchase orders, reconcile each row and column, confirm delivery dates, inspect zero-flow lanes and check the objective independently. Retain the cost version, approval evidence, capacity reservations, solver status and any manual override with its reason. Verify that rounding a divisible solution into actual order quantities has not created a shortage or exceeded a commitment.

Before commercial release, have the technical owner confirm lane feasibility and the planner reserve the quantities for the stated period. Attach the checked allocation and its cost comparison to that decision. Every purchase quantity should then trace to an approved lane, a satisfied requirement and the capacity it consumes.

References

• Microsoft Solver problem definition and constraints.

• Microsoft SUMPRODUCT function.

• SciPy linear programming objective constraints and bounds.