CST Item Average Cost Upload changes item average costs in inventory organizations that use average costing, from Excel – for the whole item or for individual cost elements. Each item becomes an average cost update transaction that revalues the on-hand inventory, as with the Average Cost Update form. Download the current costs, enter a new average cost, a percentage change or a value change per row, and upload.
When to use it
- Revalue many items at once by setting new average costs.
- Apply a percentage cost increase or decrease across a set of items.
- Change the on-hand inventory value of an item by a fixed amount.
- Correct a single cost element, for example only Material or only Overhead, without touching the others.
Before you start
- The inventory organization uses the average costing method and your responsibility has access to it.
- The items are costing enabled inventory asset items.
- The inventory accounting period of the Transaction Date is open.
- You know the GL account to post the revaluation to (the Default Adjustment Account).
Choose a template
| Template | Use it for |
|---|---|
| Detail – Elemental Costs Template (default) | Changing individual cost elements. One row per cost element and level; only the uploaded elements change and the item cost is recalculated from them. |
| Summary – Item Costs Template | Changing the total item cost. One row per item; the change is spread over the existing cost elements and levels in their current proportions. |
Each template sets Upload Type to match it. Both default Upload Mode to Create; choose Create, Update to download the current costs.
Step 1 – Set the parameters
Open CST Item Average Cost Upload in Blitz Report, choose the template and set the parameters:
| Parameter | Meaning |
|---|---|
| Upload Mode | Create opens an empty sheet to paste cost changes into; Create, Update downloads the current average costs of the selected items. |
| Upload Type | Detail – Elemental Costs or Summary – Item Costs, set by the template. |
| Transaction Type | The average cost update transaction type, Average cost update by default. |
| Transaction Date | Date of the cost update transactions; must fall into an open inventory period. |
| Transaction Source Name | Optional source name recorded on new rows’ transactions. |
| Default Adjustment Account Alias | Optional GL account alias that fills and restricts the Default Adjustment Account. |
| Default Adjustment Account | GL account the revaluation is posted to, for every cost element without its own adjustment account in the sheet. |
| Organization Code | The average costing inventory organization. |
| Cost Group | Restricts the download to one cost group; defaults to the organization’s default cost group. |
| Category Set, Category, Item, Item Like, Item Description, Item Type, Excluded Item Statuses, Make or Buy, Buyer, Planner | Restrict which items are downloaded. Excluded Item Statuses defaults to the organization’s item delete status. |
Step 2 – Run to download the Excel file
Click Run. The Excel file downloads and opens with one row per item and cost group (Summary) or per cost element and level (Detail), showing the current average cost, the valued on-hand quantity and the current valuation. Items without cost layers are listed with their item cost and a quantity of zero.
Step 3 – Enter the cost changes
On each row to change, enter exactly one of:
- New Average Cost – the new average cost of the item or cost element (0 or more).
- Percentage Change – change the average cost by this percentage (-100 or more).
- Value Change – increase (positive) or decrease (negative) the on-hand inventory value by this amount; the new average cost is the new value divided by the on-hand quantity. Only possible with on-hand quantity, and the value cannot become negative.
Adjusted Average Cost, Adjusted Valuation and Valuation Change are calculated in the sheet as you type, so you can review the effect before uploading; these and the other informational columns (Description, Item Average Cost, Valued Quantity, Old Average Cost, Old Valuation) do not change the transaction. Optionally enter a Reason and a Reference. In the Detail template, Cost Element Level (This or Previous) and Cost Element identify the element to change, and you only need to upload the elements that change; in the Summary template they are left blank. To post one cost element to another account than the Default Adjustment Account, add the Material, Material Overhead, Resource, Outside Processing or Overhead Adj Account column to the template. New rows need Organization, Item and Cost Group.
Step 4 – Validate and Save
Click Validate and Save. This checks for missing required values and the change columns (exactly one entered, cost elements filled in for the Detail type and blank for the Summary type), then saves the file. Correct any rows it flags before uploading.
Step 5 – Upload the file
In Blitz Report click Upload and select the saved file. This submits the upload request, which creates one average cost update transaction per item and cost group and processes it through the inventory transaction manager.
Step 6 – Review the result report
When the request completes, a result report opens listing every uploaded row with its status and message. The new average cost takes effect once the Cost Manager has costed the transactions.
What’s produced
- Average cost update transactions that revalue the items’ on-hand inventory against the adjustment accounts.
- A result report listing every row with a status (success or error) and a message.
Common questions
Summary or Detail – which template?
Use Summary to change the total item cost, spread over the existing cost elements in their current proportions. Use Detail to change specific cost elements; the others stay as they are and the item cost is the total of its elements.
What happens if one cost element row of an item fails?
All cost element rows of an item and cost group form one transaction, so none of them is processed; the other rows show “One or more rows for the same Organization/Item/Cost Group are in error”.
Can I change the cost of an item without on-hand quantity?
Yes, with New Average Cost or Percentage Change. A Value Change needs on-hand quantity.
Do I have to fill the adjustment accounts in the sheet?
No. The Default Adjustment Account parameter is used for every cost element without its own account.
Does it support standard cost organizations?
No. Only organizations using average costing can be selected and uploaded.
Can I adjust quantities with a value change, as in the form?
No. Value change quantity adjustments are not supported.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| Specify either a New Average Cost or Percentage Change or Value Change for the cost update | None, or more than one, of the three change columns is filled in. | Enter exactly one of them. |
| Cost Element Level and/or Cost Element should be left blank when updating costs at the item level | Cost element values on a Summary row. | Clear them, or use the Detail template. |
| Both the Cost Element Level and Cost Element must be specified when updating costs at the elemental cost level | A Detail row without its cost element or level. | Fill in both. |
| Value Change can only be performed if there is quantity onhand | Value change for an item without on-hand quantity. | Use New Average Cost or Percentage Change. |
| Value Change cannot drive the inventory value negative | The decrease is larger than the current inventory value. | Reduce the amount. |
| A Default Adjustment Account or … Adjustment Account must be specified | No account for a cost element being changed. | Set the Default Adjustment Account parameter or the cost element’s adjustment account column. |
| This Organization/Item/Cost Group combination has already been entered | The same item and cost group appears twice in a Summary upload (Detail: the same cost element and level twice). | Keep one row per combination. |
| Invalid Cost Group | The cost group is disabled, a project cost group, or not valid for the organization. | Pick the cost group from the list. |