Actual Cost Rollup
HelpID: 497022131, 497022129, 497022130
The Standard Cost Maintenance windows in GP Manufacturing do not allow use of Actual Cost (FIFO/LIFO PERPETUAL) Items. In an Actual Cost environment it is difficult to see what the total cost of production will be which in turn makes it difficult to produce Quotes or Estimates for customers.
The Actual Cost Rollup utility provides a place to set a pending cost change and date for actual cost raw materials, perform an Actual Cost Rollup, and view items that have a new cost because of the rollup.
Actual Cost Rollup also works with Standard Cost items and can perform a risk-free roll-up on those items too. Since Actual Cost Rollup does not have the ability to CHANGE the cost of standard cost items it can be used to perform planning activities without the chance of accidentally revaluing inventory.
Note: the SQL tables mentioned below for Actual Cost Rollup intentionally mirror the standard cost/rollup tables in GP Manufacturing. For example CT00003 in Manufacturing is WCT00003 in the Actual Cost Rollup.
Cost Rollup Overview
A roll-up process, as the name suggests, starts at the lowest level components and works upward through bills of materials to the topmost items. When all items are rolled up in one extended process, all component revaluations are posted upward through bills of materials, posting changes as the process continues.
A value called a Low Level Code, automatically maintained in the Item Engineering Data screen, tells the system how far down in a bill of materials each item exists as a component. For example, if an item does not appear in any bill of materials, the Low Level Code is 0. If the item is used in a bill of materials and the parent item has a Low Level Code of 0, then the component’s Low Level Code is set to 1. If the same component is used in a different bill of materials and the Low Level Code of that parent item is 3, then the Low Level Code is set to 4.
The highest value Low Level Code is maintained for each item. It is not necessary to know that the Low Level Code could be 1 or 4 depending on the parent item. It is only necessary to maintain the highest value, 4 in our example. The Low Level Code shows the lowest level an item appears in any bill of materials.
When recalculating costs, items with higher Low Level Codes must be calculated first. The accumulated costs for these items are source values for items with lower Low Level Codes. For example, item A is a parent item with a Low Level Code of 3. That indicates it is used as a component in at least one bill of materials where the parent item has a Low Level Code of 2. Let’s call this second item part B. If Item A is not properly valued when the calculations for part B are made, the wrong cost for part B will be calculated. It is necessary to revalue part A with the higher Low Level Code before revaluing part B.
Items that are purchased components have no calculation performed. The current/standard cost is used unless a value is manually entered. The first step in a full roll-up process is to set the proposed cost of purchased items to the value of their Current Cost (or a pending new cost from a vendor). The current cost value may be skewed by some spot purchase, rush charges, etc. The proposed values are written to the Item Cost Maintenance screen and can be examined and changed if necessary.
Items marked “Make” in the Item Resource Planning Maintenance screen with a bill of materials are rolled up. The material costs for a “Make” item are calculated by examining the bill of materials for the item. Each component is taken and the quantity specified is multiplied by the shrinkage factor and rounded to the appropriate decimal place for the unit of measure schedule of the component. (See How Shrinkage/Scrap Factors Apply to Standard Cost later in this chapter.) The projected costs of the components are then used to calculate the cost of the materials in the current item. When all component costs have been calculated, the resulting number increased by the shrinkage factor for the finished item.
If there are any overhead costs for the component materials (fixed or variable) those costs are extended in the same manner as above. When manufactured items are used as components in a bill of materials, the manufactured component may have labor, machine, and overhead costs. These costs as well are accumulated into the labor, machine, and appropriate overhead buckets for the current assembly.
In addition to the labor and machine costs brought forward from the components, the labor and machine costs that are expected to be consumed during production of the new assembly are calculated using the routing for the assembled item. Each step in the routing for the assembly is examined. Overhead for the labor and machine ID’s is also projected.
If Setup Time is found in a routing step (time needed to setup the work center and begin production), it is calculated, costed and prorated across either the Standard Quantity or the Average Quantity (See Proration of Setup Time later in this chapter). Setup time is assumed to occur only once. The time specified in the step of the routing is multiplied by the setup labor cost as specified in the Setup Labor Code. The calculated amount is divided by the selected quantity (Standard or Average) to provide the projected labor cost for this step of the routing.
If the Setup Labor Code specifies any overhead factors (fixed or variable), those Labor Fixed/Variable Overhead costs are calculated as defined in the Labor Code Definition as either a percentage of labor cost or a fixed amount. This total as well is divided by the selected quantity to provide a cost per item for this step of the router. The Labor Code for the labor time of the router step is then used to obtain the production labor cost. This cost is multiplied by the Labor Time field on the routing step. This value, added to the setup labor cost calculated above, is the total labor cost for this step of the routing.
If the Labor Code specifies any overhead factors (fixed or variable), those costs are calculated as defined in the Production Labor Code either as a percentage of the labor cost or a fixed amount. If a Machine ID is specified, its costs are examined and the cost for one parent item is calculated either as a cost per piece or cost per hour times the Machine Time listed in the routing. If any overhead factors are specified in the Machine Definition, the overhead costs are calculated.
All labor, machine, and overhead costs for all the steps in the routing are calculated and accumulated. The estimated costs of outsourced services are listed in the workstation records and are added into the cost calculations.
If a shrinkage factor is listed for the parent item, the labor, machine and overhead costs calculated from the router are increased by the shrinkage factor. Shrinkage at the parent level assumes that extra parent items are produced and discarded to obtain the desired final quantities. The cost of labor, machine, and overhead must be extended for these scrapped finished items.
Each make item is calculated in this manner, calculating first those items with the highest Low Level Codes. Then progressively lower and lower Low Level Coded items are processed until all items are rolled up.
Projected costs are recorded in a table and presented to the user through the Rollup Inquiry screen for review once the roll-up is completed. If necessary, corrections can be made and the roll-up performed again.
Proration of Setup Time
Setup Time in each step of the routing reflects an amount of work (and thus a cost) that occurs one time per production run. The cost of this time must be prorated across the quantity produced. During an actual production run, the setup time is prorated across the quantity produced. During the calculation of rollup costs, some production quantity needs to be assumed and used to prorate setup time.
Setup Time can be prorated against the Standard Quantity or the Average Quantity. A flag in Costing Preference Defaults controls this. Check the Prorate Setup Cost box to perform any proration of costs. (This is strongly recommended.) If the flag is not checked, setup costs are ignored and not added to the standard cost. Once that box is checked, a field becomes available that allows the user to select proration of setup costs against the Average Quantity or the Standard Quantity.
The Average Quantity is found in the Item Engineering Data screen (Cards-> Manufacturing -> Inventory -> Item Engineering Data) This value is used as the default quantity to manufacture when a manufacturing order is created for an item. If the prorate flag is set and the Average Quantity is selected as the proration value, the quantity in this field will be assumed to be the standard production quantity for the calculation of rollup costs.
The Standard Quantity is found in the Standard Cost Changes screen (Cards Manufacturing -> Inventory Standard Cost Changes). This window can be used ONLY with Standard Cost items. This field allows a different quantity to be used to calculate standard costs than is used to recommend production. If the prorate flag is set and the Standard Quantity is selected as the proration value, the quantity in this field will be assumed to be the standard production quantity for the calculation of standard costs.
How Shrinkage/Scrap Factors Apply to Standard Cost
Scrap or shrinkage can be accounted for in two different places: in raw material or components and in the finished goods. Individual components have shrinkage factors listed in the bills of materials while finished items have a shrinkage factor listed in the Item Resource Planning Maintenance screen.
Component and Item shrinkage factors affect the total cost of a manufactured item. However, they effect the cost in different manners.
Components are assumed scrapped in discrete units and the calculated amount of scrap is rounded up to the next unit of measure. For example, if a 10% scrap factor is applied to a component measured in FEET with 0 decimal places, and 3 feet are listed on the bill of material, the actual material requirement to allow for shrinkage should be 3.3 feet. Dynamics GP rounds the component requirements up to 4 feet. If the unit of measure has one decimal place, instead of rounding to the whole unit, the system will round requirements to the tenth. With two decimal places, the requirements are rounded to the hundredths.
Finished scrap/shrinkage factors are applied as a percentage against the cost. Since this shrinkage factor is applied to all of the components as well as the finished item, the system does not examine the number of decimal places as different items may have different unit of measure schedules with different quantity decimal settings. The pure percentage of shrinkage is applied with no rounding.
Item Cost Maintenance
Navigation: Cards >> Inventory >> Item Cost Maintenance
All fields are always editable. Note that for Actual Cost Items you do not have the ability to set a Fixed Overhead value (for example) on these items. If you choose to include Overheads they will be included in the rollup, but when the material is purchased the Overheads will not be part of the cost.
Item Cost Maintenance can be used with Actual and Standard Cost items.
The Rollup will use Pending Values when the Rollup As Of Date is on or after the Effective Date (See Actual Cost Rollup Utility for more information).
The information for this window is stored in wIVItemCost (WCT00003).
Actual Cost Rollup
Navigation: Utilities >> Manufacturing >> Actual Cost Rollup
Rollup
If you have made changes to Bills of Material make sure the Low Level Code calculation is update. Click GoTo >> Low Level Code Utility.
Click the Rollup button to run the rollup utility. You will be asked to confirm before the utility starts. The Rollup follows the same logic that is used by Manufacturing, so the rollup includes:
- Labor rates and overheads
- Machine rates and overheads
- Routing Times
- Bills of Material Component Quantities and Shrinkage
- Item Engineering Standard Cost Shrinkage
- In dates/out dates/Effective Dates
Please consult the GP Manufacturing documentation for details about the cost rollup process.
Rollup As Of Date
Enter a date for the roll-up. If you have entered pending changes you can see the effect of the new pricing by setting the As Of Date to a future date that is on or after the pending price change.
Custom Updates (A, B, C)
Actual Cost Rollup has three empty SQL stored procedures that can be modified to add custom logic. The stored procedures are:
- wspActualCostRollA
- wspActualCostRollB
- wspActualCostRollC
For example, if you are using a 3rd party material costing module you may want to change one of the stored procedures to retrieve current or pending pricing from that software. The current and pending costs are stored in WCT00003. Changing costs in this table have no impact on anything in GP, this table is used only by the Actual Cost Rollup utility.
Sync Item Numbers
The Actual Cost Rollup utility uses three tables that mirror Manufacturing costing tables:
- WCT00003 (mirror of CT00003)
- WICIV0323 (mirror of ICIV0323)
- WSCV00010 (mirror of SCV00010)
The utility does not maintain an active integration so if you add new items on a frequent basis, run Sync Item Numbers to make sure that Actual Cost Rollup is current. The Sync utility inserts new records into the Actual Cost Rollup tables that are new in the Item Master (IV00101).
Pull Costs From MFG
If you want to use Actual Cost Rollup to do planning rollups of standard cost data, run this update to pull current costing information from Manufacturing into WCT00003 (wIVItemCost).
Pull Last Receipt Cost
This utility updates the Pending Cost from the Last Receipt Cost. This update assumes the following:
- A Default Site is specified for the Item on Item Quantities Maintenance
- A Primary Vendor is specified for the Item-Site in Item Quantities Maintenance for the Default Site
If the above setup exists, the update will retrieve the Last Receipt cost for the Primary Vendor in the Default Site.
Pull Vendor Pricing
This utility updates the Pending Cost from WilloWare’s Vendor Pricing. This update assumes the following:
- A Default Site is specified for the Item on Item Quantities Maintenance
- A Primary Vendor is specified for the Item-Site in Item Quantities Maintenance for the Default Site
- Vendor Pricing exists for the Base Unit Of Measure
If the above setup is true, Vendor Pricing will be pulled into the Pending Material Cost field using either the CURRENT price or a FUTURE price. The Future price will be retrieved based on the Rollup As Of Date (a price that is Effective On or Before the As Of Date).
Update INV Actual Cost
After performing a Rollup you can push the updated Total Cost for Actual Cost items into the Item Maintenance Current Cost field. Note that a Manufacturing Order Receipt will use costs from the FIFO/LIFO Layers of the raw materials and will likely arrive at a different cost and that new cost will then update the Current Cost field. This utility updates ONLY FIFO/LIFO Perpetual items even if you have standard cost items included in the rollup.
Rollup Inquiry
Navigation: Inquiry >> Manufacturing >> Rollup Inquiry
The Rollup Inquiry window shows items where the Total Cost changed because of the rollup. It would be possible for the cost of one component to increase and another to decrease so that the changes cancel out and there is no net change to the Total Cost.
To create a SmartList looking at costing for all items, use wCostRollup (WICIV0323) in MFG PowerPack. This table contains several array fields. The “3” (3rd element) if each array contains the most recently rolled-up costs. The “1” (1st element) contains the most recent “pre-rollup” values. NOTE: this does not correspond to how GP Manufacturing populates the ICIV0323 table.