Actual Cost

I need to figure out a way to create inventory reports that shows the Actual Cost. In IC I can use the F6 search feature and add columns to the grid to get all the info I need except for Actual Cost. I can create something similar in Gridview with the Table "I/C Items (1,3) Master", however, it still doesn't give me the Actual Cost. Is there a table or calculation I can use in Gridview to get the Actual Cost???

We are dealing with an inventory of more than 5,000 items and we need to produce clearance lists regularly. The first thing we need to look at are the items that aren't moving, the selling price, and our Actual Cost on those items. Then we can easily mark down the pricing on those items accordingly.

Hi Susan,

Before making any suggestion, perhaps I should ask what you mean by "actual cost". Do you mean "average cost" or do you have a specific number in mind, and if so how would you be calculating it? The average cost of an item is always maintained, regardless of the costing method used (except for user defined).

Yes, sorry, that's what I meant, "Average Unit Cost".

I think I actually figured this out. Now that we're on moving average instead of FIFO as our costing method, in Gridview I can use the field "Total Cost 1" and divide the qty on hand into it to get the "Average Unit Cost".

This is going to be a huge help to us with inventory control.

If we use a spreadsheet (produced from Gridview) to enter base pricing, I think we can import it to Adagio Inventory with the new pricing. How can we similarly import new costing? In other words, if we edit the "Total Cost 1" field, how do we import that into Adagio Inventory? I'm thinking we can accomplish this using Import Adjustments in Adagio Inventory using the fields, Item Number, Extended Cost (for the total amount decreased) and the Write off Acct. Does this sound like I'm on the right track?

Hi Susan,

You can't import a cost, only receipts adjust an item's cost. (OK, an adjustment does too, but you shouldn't have to be making a lot of those.)

Why are you trying to make wholesale changes to your inventory's cost? Wouldn't a physical inventory be easier?

We are preparing for year end physcial inventory and we want to clear out as much dead stock as possible in advance. We produce a list of dead stock and lower the cost on those items because they have lost their value and at the same time lower the base price (selling price). When it comes time for physical inventory we can focus just on the quantities.

Right - you're adjusting the cost because your pricing is based on a markup from cost, is that right?

Why not just calculate a "sale price" and import the sales price, and start/end dates from an Excel spreadsheet?

The main reason we are adjusting the cost is for accounting purposes. We are required to adjust (write off) the value of our inventory items to their current market value, before submitting the year end cost value of our inventory to our accountant.

It kills 3 birds with one stone to do the cost write off, reduce the selling price and produce a clearance list for our customers at the same time.

I think I can accomplish this with Gridview producing the initial report. Edit the spreadsheet with new costs and new selling prices. Import/Items to change the selling price and Import/Adjustments to lower/write off the costs.

We don't need to use a "sale price" in this case because the new price is permanentantly lowered, not temporary.

OK - you're GridView / Import solution should be fine. I'd recommend "practicing" on a copy of your data to make sure you are comfortable with all the changes (and g/l entries) the adjustment batch will create.

Well it seems the adjustment import won't work for this purpose. The help file says:

"The values of the imported Quantity and Cost must have the same sign (positive or negative) and may not both be zero. When the signs are positive, the adjustment is an increase. Negative signs indicate an adjustment decrease".

So that would mean I can't decrease the cost without decreasing the quantity.

So much for that idea.. I will have to enter the adjustments manually.

Hello Susan:

Actually you can reduce the Actual Cost without reducing the Quantity.

Look at the ICIADJ.xls file in the \Softrak\ICWIN\SAMDATA folder. To reduce just the cost, you would enter 0 for the "Stock Qty" column and a negative figure for the "Ext Cost" column. This results in a transaction where "Type" = "Cost decrease".

As suggested earlier, I would test this in Sample data first.

Great to hear this! Thanks. I will try it.

It worked like a charm. It was weird that it forced me to provide a location when there were no quantities involved, but it had no bearing on the end result. This import saved me from entering over 200 adjustments manually. Thanks again!