Proposed Cost Update

HelpID: 497022039

Proposed Cost Update is a utility for setting a new standard cost on purchased items prior to doing a Standard Cost Rollup & Revaluation. In a standard cost environment, a “rollup and revalue” process uses costs from lower levels to calculate the cost of upper level assemblies.  The costs at the lowest level are purchased parts, and the routing for the assembly that uses the purchased parts.

While the Rollup & Revalue will calculate the cost of made items, Dynamics GP does not have a utility to set the standard cost on purchased parts.  Usually this is done manually on a part-by-part basis.  Proposed Cost Update provides several methods by which the standard cost of purchased items can be set for a large number of items automatically.

Setup

Navigation: Tools >> Setup >> Manufacturing >> MFG PowerPack Setup

Mark the box next to “Proposed Cost Update”.

Restart Dynamics GP.

Usage

Navigation: Tools >> Utilities >> Inventory >> Proposed Cost Update

Use the Proposed Cost Update window to select a range of Item Numbers or Item Classes for which you want to update the Proposed Standard Cost.  Proposed Cost Update can also import a spreadsheet of new costs (see Excel Cost Import).

The Proposed Standard Cost is found on the Manufacturing Standard Cost Changes window (Cards >> Manufacturing >> Inventory >> Standard Cost Changes).  The table is ICIV0323. Only Items with a Periodic costing method can be updated.

Update By: This drop-down list contains three options:

• Item Number
• Item Class
• Import (see Excel Cost Import)

The labels for the fields below it will change based on the selection.  For example, showing “From Item Number” when Item Number is selected or “From Item Class” when Item Class is selected.

When Import is selected, most of the fields on the window will be disabled.  The Update button will be disabled, and the Import button will be enabled.  See the Import Cost Update section below.

Make/Buy Setting:

Treat EITHER as MAKE: items with a Make/Buy setting of Either will NOT be included in the update.

Treat EITHER as BUY: items with a Make/Buy setting of Either will be included in the update.

From: Enter a value (Item or Item Class) or select one from the Lookup.

To: Enter a value (Item or Item Class) or select one from the Lookup.

Material Cost: Select a Material Cost Source:

• Current Cost: the Current Cost from Item Maintenance, which is also the last purchase receipt cost.
• Receipt Cost – Average: the average of all purchase receipts within the specified date range.
• Receipt Cost – Mode: the most frequently occurring cost from receipts within the specified date range.
• Receipt Cost – Maximum: the maximum cost from purchase receipts during the specified date range.
• Receipt Cost – Minimum: the minimum cost from purchase receipts during the specified date range.
• Receipt Cost – First: the first cost (oldest) from purchase receipts during the specified date range.
• Receipt Cost – Last: the last cost (newest) from purchase receipts during the specified date range.
• Fixed Percent: calculates the new Proposed Cost as a percent increase/decrease of the Standard Cost.
• Custom: See section Custom Cost Update

All of the “Receipt Cost” methods listed above pull from IV_Purchase_Receipts table (IV10200) where Purchase Receipt Type = 5 (POP Receipts).

The proposed cost is not updated if the method used to arrive at the new cost would result in zero dollars.  The new cost might be zero, for example, if there were no receipts for the item during the selected date range.

Dates From/To: If the Material Cost Source is one of the “Receipt Cost” methods, enter the dates to use for calculating the receipt cost.

Percent: If “Fixed Percent” is selected for Material Cost Source, enter the percent by which the current Standard Cost should be increased or decreased.  The calculation is:

New Cost = Standard Cost + (Standard Cost * Percent)

Fixed Overhead: Select an update method for the Fixed Overhead:

• No Change
• Percent of Material Cost
• Dollar Amount
• Custom (See section Custom Cost Update)

The Percent of Material Cost calculation is:

Fixed OH = New Material Cost * Percent

Whether the update method is a dollar amount or percent, the Fixed OH amount is rounded to the correct currency decimals for the Item.

Variable Overhead: Select an update method for the Fixed Overhead:

• No Change
• Percent of Material Cost
• Dollar Amount
• Custom (see section Custom Cost Update)

The Percent of Material Cost calculation:

Variable OH = New Material Cost * Percent

Whether the update method is a dollar amount or percent, the Variable OH amount is rounded to the correct currency decimals for the Item.

After running Proposed Cost Update, the next step is to Roll-Up and Revalue inventory in the Manufacturing Standard Cost Changes window. It is can be opened from the Proposed Cost Update window by clicking the Goto button >> Standard Cost Changes. Or, via Cards >> Manufacturing >> Inventory >> Standard Cost Changes.

Leaving the Item Number blank on Standard Cost Changes will allow the window to look for all pending changes and perform a roll-up to parent items. Select “All Changed Items” then click ROLL UP. When that process completes, click REPLACE COSTS. The Replace Costs process will create a journal entry, saved in a Batch (it does not POST the journal entry). Locate the financials Batch and post it.

Excel Cost Import

File Contains: Only enabled when Update By = Import.  Mark or unmark these options to control what fields are updated by the Import.  For example, since the file must contain a specific set of columns, the Material Cost column could be zero because you only want to import an update to Fixed Overhead.  Unmark the Material Cost and Variable Overhead option to exclude those from the update (they will NOT be updated, so they will retain their original value after the update).

At least one option must be marked.

IMPORT Button: This button is only enabled when Update By = Import. Click the Import button, then select an Excel spreadsheet.  You will be asked to confirm you are ready to import before the process begins (you may cancel at this point).

Proposed Cost Update can import a list of Items and New Standard Costs from an Excel spreadsheet. The spreadsheet must have this layout shown below.

Data must start in the first row (no column headers).  The spreadsheet can have any number of additional columns, but the first five columns must be:

A = Item Number

B = New Standard Cost

C = New Fixed Overhead

D = New Variable Overhead

E = Zero Material Cost (1 = true, 0 = false)

F = Zero Fixed Overhead Cost (1 = true, 0 = false)

G = Zero Variable Overhead Cost (1 = true, 0 = false)

H = empty (for status message)

Column H will be populated during the import with any error conditions encountered.

Columns E, F and G refer to setting the “Set To Zero” flags on the Standard Cost Changes window.

The checkbox will only be marked if the associated “File Contains” checkbox is marked, and the associated column in the import contains $0, and the associated “Zero Cost” column is 1 (true). For example, to set the “Set To Zero” flag, “Fixed Overhead Updates” must be marked, Column C must be $0, and Column F must be 1.

The following errors will be reported, and will prevent the row from being processed:

• Invalid Item Number
• New Cost <= $0.00 (only if Material Cost is being updated).
• New Cost = the current Standard Cost (only if Material Cost is being updated).
• New Cost has an incorrect number of decimal places (i.e. $0.255 but the Item is set to have 2-Currency Decimals).  (Only if Material Cost is being updated).
• Fixed or Variable Overhead Amount had the incorrect number of decimals (only if Fixed or Variable Overhead is being updated).
• Item has a Manufacturing BOM and is set to be a MAKE item in Item Engineering, or it is a Make/Buy item and the Make/Buy Setting on Proposed Cost Update is to Treat as Make.

If the Fixed/Variable Overhead options are marked, you can still exclude specific items from the update.  To leave the Fixed or Variable Overhead Amount unchanged, enter -1.00 in column C (Fixed Overhead) or column D (Variable Overhead).  If the field contains zero, the overhead amount will be updated to $0.00.

Custom Cost Update

Installing MFG PowerPack creates three stored procedures in the company database:

wspProposedCost – returns the new Proposed Cost for an Item

wspFOHCost – returns the new Fixed Overhead Cost for an Item

wspVOHCost – returns the new Variable Overhead Cost for an Item

All three have the same syntax, wspProposed Cost is shown below:

CREATE PROCEDURE [dbo].[wspProposedCost]

— Do not change the input/output params

@Item varchar(30),

@DtFrom datetime,

@DtTo datetime,

@Cost decimal(19,5) output

AS

BEGIN

— SET NOCOUNT ON added to prevent extra result sets from

— interfering with SELECT statements.

SET NOCOUNT ON;

— Modify logic as needed.

select @Cost = 0

END

The logic can be modified as needed to support your business requirements. The logic needs to return a new cost for the Item using the @Cost parameter.

Tables

SQL Name	Dexterity	Description
BM010115	BOM_Line	Bill Of Material Line File
CT00004	IC_COST_Rollup_Tracking	IC_COST_Rollup_Tracking
ICIV0323	IC_IV_STANDARD	IC_IV_STANDARD
IV00101	IV_Item_MSTR	Item Master
IV00102	IV_Item_MSTR_QTYS	Item Quantity Master
IV40400	IV_Item_Class_SETP	Item Class Setup
IVR10015	IV_Item_ENG	Item Engineering File