Author Topic: How to calculate average cost?  (Read 1355 times)

Will

  • Member
  • *
  • Posts: 19
How to calculate average cost?
« on: June 14, 2012, 04:51:26 PM »
Is it possible to extract the average cost for a purchase order line item based on the billed amount?

For example, let's say a line item in a purchase order is for 10 Widgets @ $10 each. Let's imagine that one bill comes in and they shipped 3 widgets at a cost of $1. Later a second bill comes in showing they shipped the final 7 widgets for $20 each. The average cost of the widgets would then be $1.43.

Can that average cost of $1.43 be pulled from Qbooks?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: How to calculate average cost?
« Reply #1 on: June 15, 2012, 06:16:25 AM »
Average cost in QB can be tricky if you don't know how it works. QB tracks the average cost automatically, so (assuming 0 inventory before the PO) an ItemQuery that immediately follows the second ItemReceipt/Bill will include the correct average cost of $14.30. QB's average cost is time sensitive, so if you (via the plug-in) or a user (via QB UI) add an Invoice record for 5 widgets BEFORE recording the second receipt, you'll have a negative Cost in QB that will then average with the second ItemReceipt cost.

You can get the result you want with a well-timed ItemQuery, but know going in that timing is everything. If what you really want is a way to determine the average cost of an item across multiple receipts/bills from a particular PO, you can get it with a PurchaseOrderQuery but you'll have to include the IncludeLinkedTxns element (with a value of "true") and do some significant parsing/processing on the response. XPATH is your best friend in this situation.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852