How do I include freight costs into the average cost of my items

At present unless I am mistaken you cannot do this in xTuple. I, however, have exactly the same requirement. I am just waiting on a Business Specification from my customer in order to define the requirement and the expected Inventory and GL transactions so I have a full pisture of how it should work.

At that point I will post a Specification on xTuple.org defining how I intend to proceed and gather comments/requirements from others such as yourself. Once the specification is settled I will develop that requirement as an xTuple package. At this stage I don't have a clear picture of development effort.

I'm looking for ways to make this development affordable for all so suggestions are welcome. If there is enough interest, perhaps we could look at a featureMob much the same approach that xTuple used to fund their email integration.

Here is the initial draft of the Import Shipment and Costing Guide for your reference. We are still looking for a way on how to distribute the package for testing.

What if we were to simplify this discussion and specification by elimination of two concepts/features:

1) Freight allocation to lines. While freight is typically assigned an amount for a total order, xtuple has an existing concept of freight-by-line. Part of the presumed difficulty of this discussion is how to take a fixed amount of freight (or charges) for an entire order and distribute it to each line. We could simply force the operator to do the distribution manually and accept the existing freigh-by-line concept. Put your freight portion on each line manually at PO order entry.

2) Third party invoice/voucher association to freight on this order. This one is complicated. Users have expressed a desire to even modify allocated charges when a freight or misc charge invoices is received at a later date after inventory has initially been received and average cost set. Personally this seems like a bad idea to me since the beginning. If we discontinue the inclusion of that concept in the discussion, the process is greatly simplified and might move forward to an xtuple that allows us to include freight charges in the average cost of the product.

If we eliminate the above two concepts/features from the current specification and accept the existing feight-by-line concept from the user's standpoint, would that help us get this going?

I'd start over with a new specification that did not attempt to integrate either functionality if I get some idea that it will be making a contribution.

Comments?

Hi,

We have just finished our Import Shipment and Costing (isC v1.0 rc) package. Among the features included are as follows:

1. Charges Definition: This is a list of all charges to be used in importing the products (ie. Freight, Duties, Taxes, Brokerage, etc.)
Each charges were tagged with allocation type (Volume, Net Weight, Gross Weight, Qty, Amount) which is used to allocate the charge to the shipment items.
Each charge is also assigned with the Charge Clearing GL Account to accumulate all actual charges accounted in A/P before posting the charges to the Item's Average Cost.

2. Import Items. This is a list of all items used in Import Shipment. Each entry here were tagged with Commodity Class Code, Volume per Qty, Net Weight per Qty and Gross Weight per Qty, which are also used in allocating the charges.

3. Import Shipment Entry: A routine that creates import shipment details together with Charges, Shipment Items (extracted from Purchase Orders), and Costing.

4. Import Voucher: Creates a voucher from Misc. Payable Voucher Screen. This creates a Voucher Distribution tagged as Import Charge, and get its GL Account from Charges Definition.
GL Entry: Dr. <>
Cr. AP Account

5. Import Voucher Cost Posting. This routine uses the Average Cost Updating routine (InvValueAdjustment) to post Costs itemsite.
It also generate GL Entries as follows:
Dr. Inventory Account
Cr. <>

For those who wanted to request for a testing db, email us at ossph@mail.com.

Hi,
We have just finished our Import Shipment and Costing (isC v1.0 rc) package. Among the features included are as follows:
1. Charges Definition: This is a list of all charges to be used in importing the products (ie. Freight, Duties, Taxes, Brokerage, etc.)
Each charges were tagged with allocation type (Volume, Net Weight, Gross Weight, Qty, Amount) which is used to allocate the charge to the shipment items.
Each charge is also assigned with the Charge Clearing GL Account to accumulate all actual charges accounted in A/P before posting the charges to the Item's Average Cost.
2. Import Items. This is a list of all items used in Import Shipment. Each entry here were tagged with Commodity Class Code, Volume per Qty, Net Weight per Qty and Gross Weight per Qty, which are also used in allocating the charges.
3. Import Shipment Entry: A routine that creates import shipment details together with Charges, Shipment Items (extracted from Purchase Orders), and Costing.
4. Import Voucher: Creates a voucher from Misc. Payable Voucher Screen. This creates a Voucher Distribution tagged as Import Charge, and get its GL Account from Charges Definition.
GL Entry: Dr. <>
Cr. AP Account
5. Import Voucher Cost Posting. This routine uses the Average Cost Updating routine (InvValueAdjustment) to post Costs itemsite.
It also generate GL Entries as follows:
Dr. Inventory Account
Cr. <>
For those who wanted to request for a testing db, email us at ossph@mail.com.

Hi John,

How can we possibly offer the package for testing?

Regards,

Jeff

I have a related (but slightly different) issue. I am STUNNED that xTuple keeps average cost ONLY by item and not itemsite. We have several facilities in different states and because labor rates, taxes, shipping, etc. vary by location, we need to keep separate costs per item PER SITE. For example, a widget that cost us 3.25 to make in Illinois might cost us 3.72 to make in Pennsylvania. We need our average cost to reflect that. Does anyone have a similar need? How do you manage it? Any good ideas here? We can't be the only ones with this problem.

gwalborn

I think Gwalborn sort of took the train off the track. Site by site separate cost may be important but this thread was about landed costs.

From reading the posts about landed costs in the forum, it seems to me that the off-shoot discussions may be what is giving this issue a perception of being complicated.

Yes, you have three methods you could use to distribute freight cost. Yes, other charges (in my case direct-discharge charges, steevadore fees, etc) also have a similar business need of being rolled in the cost as freight does. But Freight is by far more common and at least in my industry 10 times more significant to the total direct cost of the item that the other accessory charges.

There are multiple choices true but this is really not that complicated and I would think it would be of benefit to a large percentage of the base of users and potential users.

I'd be happy to write up a specification. Half of the needed info is already posted on these forums if you look around -- it's just sort of mixed in with the other issues so maybe it is not as clear as it could be.

And you could certainly add some slick sophistication like associating the third party charges with purchase orders for the third party charges and configure a couple an asset (or even expense) account to use as a clearing to hold the net difference between the estimated charges that were know at the time inventory was costed and possible variances between that estimate that went into your inventory cost at receiving of the Item PO and the actual cost of freight that might not be known until a little later.

Here's a concept I have not seen in the forums yet. I alluded to it in the previous paragraph. Many of the posts refer to the complexity and what-if scenarios involved with receiving the inventory at a cost on one day and then at a later date coming back and trying to realize that the freight was more than originally estimated. At that point, you have to concede to the idea of variances. You believe your freight will be $2,300 so you put that on the PO for a $10,000 load of steel. So at the time of inventory receipt, an average cost is set for the item and $12,300 is debited to the appropriate asset account. Much discussion on the forums has been about the what-if scenario of what happens if the actual freight invoice comes in at $2,500. What if by that time the goods were consumed or sold. Yes that is complicated. Too complicated. So the only sensible and simple thing to do is post the $200 variance to a configurable account.

You can even achieve it by assigning a default account for the credit side of freight transactions on Purchase orders that are calculating landed cost and then as you issue purchase orders for the actual charges, let the receipts of those orders debit the same account or a contra account. They you have your variance between the estimated freight charges you put on the PO and the actual charges all in one, neat place. You have a simple system. You get to cost the product with freight (or call it "third party charges") and the only compromise is that your inventory is costed with the estimates that were put on the PO up front instead of the actual amounts of the third party freight charges associated with the inventory.

Oh, and as far as distribution, what we did with the system I implemented was check and see if ALL line items on the PO had non-zero weight. If they were ALL non-zero, we allocated by weight. If any line had a zero weight, we allocated by product cost.

For the rare cases that doesn't fit your needs. Do a simple, obvious, work-around and enter two or three PO's for your order instead of getting it all on one.

I have clients that would be definitely be interested in this, as long as its reasonably priced. If it could be done as an add-on that would be great. Otherwise, maybe it could be part of a "distribution edition". In the mean time, we are using this work around from another user:

"As a distributor/importer we face exactly this challenge. Here’s how we manage it:
-We use standard costing. This handles the situation where the goods are already sold from inventory when you get the freight bill (It is better to use standard cost for imported items, the other items can be average if you want)
-Products that are imported have two cost elements, “Material” and “Freight”
-PO entered as normal, which creates no GL entries
-Expenses Related to import/freight (shipping cost, customs broker’s fees, customs duties, etc.) are booked to an Asset Account we call, “Freight and Import Costs”
-Once the products are received (and therefore added to inventory at the standard cost), we review the invoice and calculate the item’s landed cost offline in a spreadsheet, including any expenses (or accruals of expenses, i.e. the customs broker hasn’t invoiced us yet, but include an estimate) booked to Freight and Import Expenses.
-The voucher is entered, with the landed cost of each item entered by Cost Element. The voucher also gets a Misc. Distribution (a credit), to the Freight and Import Expenses asset account to liquidate the asset that was created.

So for us the complexity really comes in auditing that Freight and Import account regularly, and that a voucher with 80 or 90 line items takes quite some time to enter.

For us, the only thing Postbooks is missing on this is a way to distribute a group of costs across the voucher’s line item’s by some factor so that we don’t have to maintain the math offline nor enter multiple costs for a vouchered item. My preference would be to be able to use a characteristic since the items don’t have a volume measurement in them (which for us is the proper way to distribute freight costs). In marine freight you rarely approach the weight limits the carriers impose. So unless you’re moving lead around, volume is the determining factor in freight cost."

I think it is time for a Specification to be developed and discussed. Sounds like there is a bit of interest in a solution. Wesbaker if you could get the Sepcification started as you have obviously put some thought into it, I would be happy to contribute once I get my customer's input on their requirements (I'm a developer not an Accountant).

Once we have an agreed solution, I would be happy to estimate the development effort involved and we can think about how to make it cost effective to deliver. Happy for xTuple's input into that.

Dave

I'd be happy to do the specification and try to simplify it.

You have to realize my experience in other systems is high but my experience in xTuple is near as low as it could be. So there may be concepts in xTuple that could be leveraged that I will be totally unfamiliar with. Perhaps if jrogelstad sees a clean specification with all the what-if and complexity removed, he will be able to pipe up and have a simpler solution or something that fits better in the xtuple framework.

I've read fblauer's post here. Seems like it is the same post as on the older thread. I understand it but just don't have the ability to maintain standard costs, much less calculate my costs offfline for each transaction and then go key things in an erp system.

I'm willing to give up a little of the purity of actual cost to reduce work but I do have to have a dynamic self-maintaining cost to have a useful business data system for what I am doing.

fblauer, if you could get everything you need EXCEPT the ability to allocate the third party charges by volume and had to let them be allocated by weight or value (whichever made more sense) would that get you 90% to your goal or is that so important to you that you'd continue to do all the offline manual work and try to maintain standard costs?

Wes

We need the ability to adjust average cost of items to include the freight and duty. I'm not sure about the details behind how they are allocated across the order. We normally know most of the freight costs once the order has landed so can apply when receiving the order.

I'm not sure about losing interest because I will be developing this requirement for my customer regardless but I would much prefer factoring in the wider community's requirements and developing a package that meets that wider requirement.

John, could you please initiate a Specification page and allow Wes, Fred and myself to edit.

Wes, I think we should consider all the possible requirements but stick to the basic, and actual requirements of our customers for the first release. That way we keep the development contained and discrete and we can always extend the package later if necessary to include other options.

Ok, here's the boiler plate for landed costs:

http://www.xtuple.org/landed_cost

The four of you have access to edit it. Make sure you subscribe to it as well to get change notifications.

I'll also add one more thing: we recently added a utility to adjust inventory value for average cost items. We're more or less talking about a way to automate those value adjustments. You all might want to look at how that works when considering this. In 3.8.x it's in Inventory > Utilities > Adjust Average Cost Value.

Oh cool. That "boiler plate" page covers half the battle by itself. Very nice.

Wes

OK, I gave it a start. Just the conceptual stuff. Hope to get to some of the meatier stuff Friday or this weekend. But I've got to have an xtuple instance (that works) in front of me. (I seem to have broken the postbooks edition running on this notebook.)

Wes

@ Dave - I like your approach, (similar to the way fixed assets was done). Start off with the most important things, and add "nice to have" later.
@ Wes - Yes that would be sufficient for most clients. Its better than we have now. We are not tied to standard cost, and really want to avoid the offline spreadsheets. This was only a (temporary, hopefully) workaround, until we have something better.
@ John - I will have to look at it in more detail when I get a chance, but that's the kind of thing I was looking for.
Thanks.

Yes, I did, indeed, take the train off the tracks. I apologize to the group for the post (BTW, please feel free to delete that post and the follow up from the thread.) On the other hand, we do have some of the same needs when it come to landed costs and we are very interested to see how this develops. I guess the point is that xTuple needs the flexibility to handle as many of the ways to value the cost of goods as possible and to support a wide variety of scenarios. Again, please accept my apologies for the diversion.

gwalborn

OK guys, (gwalborn, fblauer, anderson, malfredo) take a look at the document so far.

I'm going to let it sit until you guys have made some comments and make sure that we are all traveling the same road before I try to put more work into it. I think some of you who are much more familiar with xtuple than I may want to do some of the sections and may see some way to make things fit in with xtuple that I'm unaware of.

Look at the tables with the T accounts. I think that explains it best. I attached the xls to the spec document so you can grab that and change them if you like.

I believe it entails all the concept at this point that I wanted to communicate.
I believe that it accomplishes the goal and gets away from most or all of the really difficult-to-address scenarios that would be involved with trying to change the average cost AFTER that initial PO receipt of the actual items in question.

Being new to the xtuple group, I do have some questions about where things go from here.

If we get a specification that this small group smiles on and jrogelstad smiles too, then what happens?

Wes, thanks for your efforts in getting the Spec started. As I said I want clarification from my customer regarding their requirements at which time I will ensure the spec meets that need, but it is likely to fall in the simple end of the spectrum.

I am happy then to spec out the technical implementation of the design until we agree on a solution from a business and technical delivery perspective. That will give us an estimate of effort in building the solution.

We can then look at how we share the costs. As I mentioned earlier I require the solution regardless and have the mandate and skills to develop the solution but I would like to investigate the most cost effective way to deliver that for all interested parties. Happy to look at any innovative approach to this.

Couple notes just to clarify existing functionality: Line item freight is simply expensed on receipt and misc. freight on the PO header is just informational.

I encourage you guys to forge ahead. Note that I'll be pretty tied up through the rest of May so I may not be terribly responsive for the next two or three weeks, but it looks like you are all on the right track. One important question to think about, should this be core or a package? That has a big impact on how it's put together.

jrogelstad, yes that does clarify. Explains why I couldn't find the parameters that indicate where to distribute it too.

It also completely changes the way to discuss "freight allocation". If the user is breaking the freight amount up himself and assigning it line-by-line then there is no concept of "allocation" needed.

That sort of brings a good idea to the table. In those cases where you need a distribution that is not covered by the readily available data (weight and value) then you can go to the extra effort to break the freight up line-by-line as the user (buyer) sees fit.

I'm going to modify the document to what I think makes most sense. "misc freight" would go away and be replaced by two fields. 1) Other Vendor Charges and 2) Third Party Charges. That will get rid of the need to select whether the field is vendor charged or third party and just be intuitive. Also makes it intuitive to just print the "Other Vendor Charges" on the PO and include in grand total and obvious that the "Third Party Charges" would not be on the PO print out.

I'd prefer to see it core and think it makes sense for most of the base to have the functionality and doesn't get in the way for anyone. Is it more difficult somehow to implement as "core" instead of a package?