# Why is our cost for chocolate chips different from what we paid on the last purchase order?

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/fifo-cost

## Question

Why is our cost for chocolate chips different from what we paid on the last purchase order?

### Answer

**Short answer:** Fishbowl is set to Average Costing, so the cost on chocolate chips (CR403) is a weighted average of everything received, not the last price paid. The current cost is **$4.617/lb**, while the last PO (PO 9, A&B Distribution, July 11) was **$6.50/lb**.

How the average got to $4.617:

| Event | Qty (lb) | Unit cost | Running qty | Running avg |
|---|---|---|---|---|
| Stock on hand before the POs | 47.5 | $5.00 | 47.5 | $5.000 |
| PO 2, Rocky Mountain Chocolate | 30 | $1.50 | 77.5 | $3.645 |
| PO 9, A&B Distribution | 40 | $6.50 | 117.5 | $4.617 |

Nothing since then has moved it: one pound shipped on SO 1028, and a September 4 inventory import brought in 233 lb at the existing average.

Two things worth a look:

- **PO 2 at $1.50/lb is the main reason for the gap.** It is far below the $5.00 and $6.50 seen elsewhere, and the part's unit was switched from Cup to Pound on July 4, so that price may be a data-entry or unit error. Without it, the average would be about $5.69.
- **The September 4 import looks like a duplicate load.** It added 47.5 and 69 lb to both LA and MI, which are the same quantities that were already on hand, taking stock from 116.5 to 349.5 lb (exactly triple). It didn't change the unit cost, but it means about $1,076 of inventory value has no purchase behind it.

If you want cost to track the most recent purchase price instead, that's a costing-method question (the part's standard cost is currently $0, so Standard isn't set up for it either).

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Read the Fishbowl notes index — 13 topic notes
2. Checked the saved queries — 6 saved queries
3. Ran a read-only query: Find the chocolate chips part and its standard cost — 6 rows, 248 ms

   ```sql
   SELECT p.id, p.num, p.description, p.stdCost, p.activeFlag, p.uomId, u.code AS uom
   FROM part AS p
   LEFT JOIN uom AS u ON u.id = p.uomId
   WHERE p.num LIKE @q OR p.description LIKE @q
   LIMIT 101
   ```

4. Ran a read-only query: Current average cost record for chocolate chips part CR403 — 1 row, 261 ms

   ```sql
   SELECT pc.*
   FROM partcost AS pc
   WHERE pc.partId = @pid
   LIMIT 101
   ```

5. Ran a read-only query: Purchase order lines for chocolate chips CR403, newest first, with unit cost and UOM — 2 rows, 189 ms

   ```sql
   SELECT po.num AS poNum, po.dateIssued, po.dateCompleted, pos.name AS poStatus, v.name AS vendor, poi.id AS poItemId, poi.poLineItem, poi.partNum, poi.vendorPartNum, poi.qtyToFulfill, poi.qtyFulfilled, poi.unitCost, poi.totalCost, u.code AS poUom, pis.name AS itemStatus, poi.typeId
   FROM poitem AS poi
   JOIN po ON po.id = poi.poId
   LEFT JOIN postatus AS pos ON pos.id = po.statusId
   LEFT JOIN poitemstatus AS pis ON pis.id = poi.statusId
   LEFT JOIN vendor AS v ON v.id = po.vendorId
   LEFT JOIN uom AS u ON u.id = poi.uomId
   WHERE poi.partId = @pid
   ORDER BY po.dateIssued DESC, po.id DESC
   LIMIT 101
   ```

6. Ran a read-only query: Cost layers for chocolate chips CR403 — 11 rows, 247 ms

   ```sql
   SELECT cl.*
   FROM costlayer AS cl
   WHERE cl.partId = @pid
   ORDER BY cl.dateCreated DESC
   LIMIT 101
   ```

7. Ran a read-only query: UOM conversions involving lbs, the stocking unit of chocolate chips — 8 rows, 295 ms

   ```sql
   SELECT uc.id, fu.code AS fromUom, tu.code AS toUom, uc.multiply, uc.factor, uc.description
   FROM uomconversion AS uc
   JOIN uom AS fu ON fu.id = uc.fromUomId
   JOIN uom AS tu ON tu.id = uc.toUomId
   WHERE uc.fromUomId = @u OR uc.toUomId = @u
   LIMIT 101
   ```

8. Ran a read-only query: Inventory transaction history for chocolate chips CR403 with cost per event — 69 rows, 428 ms

   ```sql
   SELECT il.id, il.eventDate, il.dateCreated, ilt.name AS logType, il.changeQty, il.qtyOnHand, il.cost, il.recordId, il.tableId, il.info, lg.name AS locGroup, l.name AS location
   FROM inventorylog AS il
   LEFT JOIN inventorylogtype AS ilt ON ilt.id = il.typeId
   LEFT JOIN location AS l ON l.id = il.begLocationId
   LEFT JOIN locationgroup AS lg ON lg.id = il.locationGroupId
   WHERE il.partId = @pid
   ORDER BY il.id
   LIMIT 301
   ```

9. Ran a read-only query: Which inventory costing method the company uses — 5 rows, 190 ms

   ```sql
   SELECT sp.sysKey, sp.sysValue
   FROM sysproperties AS sp
   WHERE sp.sysKey LIKE @k
   LIMIT 101
   ```

10. Ran a read-only query: Receipt lines for chocolate chips CR403 including billed and landed cost — 2 rows, 287 ms

   ```sql
   SELECT ri.id, r.id AS receiptId, ri.poItemId, ri.qty, ri.dateReceived, ri.dateReconciled, ri.billedTotalCost, ri.landedTotalCost, ri.dateLastModified, ris.name AS status
   FROM receiptitem AS ri
   JOIN receipt AS r ON r.id = ri.receiptId
   LEFT JOIN receiptitemstatus AS ris ON ris.id = ri.statusId
   WHERE ri.partId = @pid
   ORDER BY ri.id
   LIMIT 101
   ```

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions