使用Excel公式或VBA实现基于FIFO逻辑的出库总成本计算

API 探路者

2026-5-22

FIFO Inventory Cost Calculation for Outbound Orders

Got it, let's work through implementing FIFO (First-In-First-Out) inventory costing for your outbound shipments. The goal is to calculate the total cost for each outbound line item by using the oldest available inventory first from your inbound list.

Input Data

Inbound (IN) Inventory List

PN	Qty	Price
A	100	5
B	150	6
C	150	7
D	50	-9
E	100	5
F	5	9
G	20	6
I	5	7
J	15	7
J	30	10
K	100	10
K	50	10
A	20	8

Outbound (OUT) Shipment List

PN	Qty
A	120
B	10
C	110
D	60
E	100
J	20
J	10

FIFO Cost Calculation Logic

FIFO means we consume the oldest inventory batches first before moving to newer ones. For each outbound line item:

Match the PN to all inbound batches of the same PN, ordered by their arrival (the sequence they appear in the IN list).
Deduct the outbound quantity from the earliest available batches until the outbound qty is fully covered.
Calculate the weighted average price for the outbound line if multiple batches are used, or use the single batch price if only one is needed.
Total cost = Outbound Qty × Calculated Price.

Let’s break down key examples to clarify:

PN="A": Outbound qty is 120. We first take all 100 units from the first batch (price $5), then the remaining 20 units from the second batch (price $8). The weighted price is ((100*5)+(20*8))/120 = 5.5, total cost is 120×5.5=660.
PN="J" first outbound line (20 units): Take all 15 units from the first J batch (price $7), then 5 units from the second J batch (price $10). Weighted price is ((15*7)+(5*10))/20 = 7.75, total cost 20×7.75=155.
PN="J" second outbound line (10 units): Now 25 units remain in the second J batch. We take 10 units at $10, so total cost is 10×10=100.