Hi members, I need some help with a custom report, I’m trying to see the item costs of the items contained in an inventory kit. The route I have gone is invoicing the Kit, I sell the kit and then create a report based on the date of sale.
The problem I’m having is I cannot get the both the item description and price visible on the same report
Any help would be appreciated
MC
Kit
Custom report inventory item selected
Custom report inventory item selected and defaultPurchaseUnitPrice selected
@martincrowe, your difficulty comes from the fact that inventory kits do not have costs associated with them. Manager does not track inventory kits, because they have no physical existence. Therefore, they have no average cost. You cannot determine the cost of an inventory kit while it is in stock, except by looking up the current average costs of all its components. And you cannot create any custom report with average costs, because those are not represented in postings to the general ledger, but are calculated on the fly when needed.
Inventory kits are shortcuts for selling combinations of inventory items together. When you sell one, the cost of that sale is calculated at that moment from the average cost of its constituent items. Accordingly, the cost of goods sold for an inventory kit often vary from sale to sale.
The two versions of your custom report both are designed to display only one variable:
The first displays every inventory item in any type of transaction on 24-4-2023. Apparently, you transacted only one inventory kit that day. You included no other selections, so you got no other information. Note that the result would be identical if you had sold or purchased the individual components separately. In fact, from your report, it is not actually possible to know whether you sold the individual items or the inventory kit. Nor is it possible to tell this was a sale. It could have been a write-off or purchase.
The second report displays your default purchase prices for every inventory item in any transaction of any type on 24-4-2023, and no other information. This is not the actual costs of those items, but the default purchase prices you entered when you defined the individual components. Those numbers could be different. If your accounting history were more extensive, this list would include information from items sold, purchased, written off, transferred, credited back to customers, etc.
When you want multiple pieces of information about transactions, you need to include multiple selection criteria. You will also need to apply appropriate filters (the Where statements). Otherwise, you will see many undesirable entries.
If your custom report date range was expanded, you might see all sorts of transactions, which would become very confusing (assuming this is a real business). Simplified test examples can be useful for troubleshooting custom reports, but they can also hide shortcomings of your definition.
To get information about the cost of inventory kits sold, you need to be looking at debits to the Inventory - cost account. Because the inventory kit transaction is handled as sales of its components, you will need to search separately for debits related to individual items. You cannot do that in a single report, because progressive application of filters will end up screening out all items.
@Tut thanks for your reply, Im pretty much trying any work around that would give me a purchase price or average price for an inventory kit on a single report.
I defined a single day because I could create an invoice on a non working date and it would be the only transaction that day. I’m ok with the report using default purchase cost because its probably most suitable to us anyway.
I there any way to get a cost ? let it be average or default purchase cost.
You could use a Production Order to create an Inventory Item with the same components as the kit. This would have a cost based on the cost of the components used
@Joe91 Thanks, I tried that and worked well but I was concerned It could affect my inventory on hand, I will have 40-60 kits and there could be 10 - 100 items in each one. I also looked at creating a phantom purchase order which seemed to work fine but it will not update any changes in cost after its been created.
Forget the purchase order. That will just cause other problems.
Based on what you have said, I don’t believe you should be using inventory kits. It sounds like you are creating these kits and stocking them. If so, you should take the advice offered and use the production order to create other finished inventory items. Inventory kits are not to be used for items you stock in the condition you sell them. Inventory kits should only be used for items that are stocked as individual components that are sold together as a bundle. They are only a convenience for selling. Their only purpose is to save time, keeping you from having to enter a bunch of individual components that are sold together.
@Tut At this point I’m just creating the kits to group the items together for resale purposes, to establish cost, and like you said make processing quicker, If I go the production order route I think I will end up with a load of production orders sitting on the system increasing our overall inventory value. It would be practical to have them left for look up, reference and to copy to new kits / BOM’s that’s I went why with Inventory kits.
Production orders do not change the value of your inventory unless you add non-inventory costs to them. They simply transfer value from individual components to the finished good.
@Tut I’ve explored the production orders in more detail and I’ve come up against this issue, it would be typical for us not to stock high cost components in inventory because some of our suppliers will deliver to us next day, If the item is not in current inventory my production order will not let see the expected cost of the finished item, a typical senario for this would be preparing a quotation for a client, is there a work around for this ?
The workaround is to put whatever sales price you want into your sales quote. That price can be based on costs of items in stock, quotes or price lists of your suppliers, or wild guesses. All these approaches are common.
These are all common approaches I agree but would probably be tedious and time sucking because some of our finished goods have between 60 - 100 items and one quote alone could have multiple finished goods. Thanks for your help either way.
I will not pretend that cost estimating is never tedious. That is why some organizations have estimating and proposal preparation departments. If you think costing 100 parts is tedious, try preparing a bid on something with thousands of components and significant subcontracts. Time consuming, yes, but done every day in businesses around the world.
You are asking for an accounting system—designed primarily to record what has already occurred—to produce a cost for something with components you may not have purchased yet. Where do you expect the program to get that information? To me, this seems like a routine and straightforward task. Conceptually, you create a list for each of your finished goods with the last known costs of its components. Periodically update those lists. Frequency of update depends on market volatility in your supply chain and pace at which you issue quotations or make sales.
Implementation of that concept within Manager is easy with the production order. Here is an example of a finished good (Carnarvon tigers) with two components and separate labor costs:
The cost for one unit is $372.58. You could easily compute costs for more by editing the production order.
Suppose the item had three components, one of which was out of stock and therefore had no average cost. The system shows insufficient quantities and withholds the final cost. It’s easy to tell what needs updating, editng, or procurement:
You have complete flexibility. You could build this finished item (rather than call it an inventory kit) in advance and the system would know its cost. Or you could wait for an order, create the production order, and see if you needed to buy more parts.
Hey @Tut maybe I wasn’t explaining myself good enough…
I have created the items already and we would have purchased them in the past, example…
We are building a race car for a client. One of the many assemblies on the finished car is the power train, the chassis and other components are in stock and we have started the build, we would wait until almost last minute to purchase the powertrain because of of its cost and the effect that several of these would have on our cash flow.
Another purpose for using the kits was when I send a client a quote what the see on the quote is the Kit name " V6 3ltr Renault engine" an not a full Bill of materials for the engine containing 1000’s of parts.
I know now I’m probably looking for too much from the accounting system.
@martincrowe, nothing you have explained invalidates my recommended solution.
I’m not sure what you are saying here. If you are saying you cannot implement my solution because you already created the items, so what? Change. Make the former inventory kits inactive. Define new inventory items for the finished goods instead. If you are are just saying you have purchased the powertrain before, again, so what? You might decide to use the prior cost in developing your quote. Or you could get a current quote from the powertrain manufacturer. Or you could make up a number. For something as expensive as a powertrain, I assume you would want a current quote. But your customer also would not expect a quote instantly for something as major as a race car. You would have a few days to develop your bid.
Nothing prevents you from issuing a production order, then modifying it when you have more exact numbers. The production order is purely an internal document.
You would merely define an inventory item with that name instead of an inventory kit. You would use a production order to record the manufacture of the finished engine from the thousands of parts.
You seem to be strongly resisting the use of production orders, which can give you the exact information you said you wanted in your opening post. Instead, you want to use inventory kits. Although they would do the job, deriving their costs in advance requires a lot of work. Why not use the built-in capability?
Thanks again @tut I’m hesitant because I think the ability to look at a Production order BOM for reference or revision of cost is kind of awkward if the items are not in stock. We check several BOM’s with hundreds of items on different levels daily for costing purposes, I just thought if there was a simple way (report with Finished item - lower level items, Qty and cost) to check that without having to go through multiple processes would make my job a little easier.
Thanks very much for your input, I’m happy to close this topic. FYI the system is really good and I like it a lot …except for this bit !!
Well, you could implement a scheme with custom fields. Give inventory items a code for each finished item they are part of. Search the Inventory Items tab for a code and you will get a list with average costs of those items. Copy that to a spreadsheet, then adjust quantities. Fill in any missing costs.
So there are a couple methods that would work. But you need to abandon your fixation on a report that would require information that doesn’t exist.
Good luck.
