---

Inventory and WIP Value (Report)

This screen allows you to determine the value of Inventory, JOB WIP and Assembly WIP at a specified date.  You should note the Inventory As At Valuation Style parameter setting in Item Rules screen as this will affect the results of the Inventory Valuation

 

Upon entry of the date you should click on the 'Print' button and select where you wish the results to be output

 

Entry and Display fields

 

Run Valuation as at:  This is the date at which the value of your Inventory will be calculated.  It is prefilled with the system date but you can amend this by clicking on the drop-down calendar.

 

Buttons

 

Inventory Value - Print: This will print the Inventory Valuation report showing the stock quantity and value of Inventory Items at the entered date.

 

Job Order WIP - Print: This will print all the Job Orders and their WIP value at the specified date

 

Assembly Order WIP - Print: This will print all the Assembly Orders and their WIP value at the specified date

 

Cancel: This will close this panel without printing the Report

Calculation Process:

Overview:

This  describes the process of how Ostendo calculates retrospective Stock and WIP Balances for the ‘Inventory And WIP As At Valuation Report’ 

Pre-Requisites:

The accuracy of this report works on the following assumptions:

1. The current Base Cost Centre Mapping and in the case of Site Costing, the Site Cost Centre Mapping settings are the same today as the period to be reported on.
2. There have been no Global Name Changes affecting Cost Centres used in this report.
3. Site Costing (if used) will only use current Site Cost Centre Mapping settings going back to the point that Site Costing was turned on. In other words if Site Costing was turned on 3 mths ago, you will not be able to retrospectively report site costing prior to this.
4. Because this report starts from ‘todays’ settings you cannot report back to a point where those settings were different at that point in time.
5. If this report is to be used to reconcile back to the Accounting System GL, ensure the ‘Inventory As At Valuation Style’ setting on the Item Rules is set to ‘Financial History’.

Inventory As At Report Calculation Process:

Items without Actual Costing turned on - (Financial History Basis)

This report runs two routines in order to determine the Cost and Qty of an item at a point in time.

1st Routine – Determine Cost retrospectively:

• Ostendo will first establish the current stock valuation method by looking at the System Settings to determine the Financial Inventory Costing Method (Average or Standard). For the purposes of this explanation, we will assume Average Cost

• The routine will then look at current Qty On Hand globally (or by site if Site Costing is turned on) and multiple the current average cost for that item (or site) to establish a current Total Stock Value.

• Ostendo will then determine the INVENTORY cost centres specified in the database for the next process.

Firstly the Warehouse Inventory Category Warehouse is evaluated. If none specified it will check for an Inventory Cost Centre against the Warehouse itself. If no Inventory Cost centre is found in the Warehouse, the Base Cost Centre mapping Stock cost centre is used.

• Ostendo will then look at the Financial Tables and ‘unwind’ all financial journals associated with this item (site) going back to the As At Date. 

To explain this further every DR to a stock cost centre is deducted and every CR to a stock cost centre is added to the current stock value for that item (site).

• The financial tables used for this are as follows:
• FINANCIALINVENTORY
• FINANCIALPURCHASERECEIPTS
• FINANCIALSALESISSUES
• FINANCIALSALESCOSTS
• FINANCIALASSYISSUES
• FINANCIALASSYRECEIPTS
• FINANCIALJOBISSUES
• FINANCIALASSYWIPVAR
• FINANCIALPURCHASEVAR

Items with Actual Costing turned on - (Financial History Basis)

The same methodology applies for retrospective reporting of Items where Actual Costing is turned on, however instead of using the current Average Cost to establish a starting position, Ostendo will sum up the individual inventory actual cost records in order to determine the current stock value.

Financial Inventory Costing Method Of Standard Cost - (Financial History Basis)

The same methodology applies for a database set to a Financial Inventory Costing method of Standard Cost (System Settings), however the initial starting point for establishing the current stock value uses the current Standard Cost of the Item (Site).

2nd Routine – Determine Qty retrospectively:

• This routine will establish the current stock qty on hand for the item (Warehouse)

• To determine the Qty on hand for the As At Date, Ostendo will sum up all inventory transactions for the item (warehouse) forward from the first inventory transaction for each item. NB: Any Unit of Measure conversions are also evaluated during this process

Example:

NB:

• It should be noted that Ostendo does not hold the Average Cost of an item at a warehouse level. Only Site and global (item master) level. Therefore if you have specified different inventory cost centres at a warehouse or warehouse category level and Item Site Costing is NOT turned on, the reported cost for any specific warehouse / Item may not reflect the accurate cost. In this case the sum of all Warehouse / Item costs will be correct, however individually they could be misleading. 

If specific warehouse costs are required, we recommend turning on Item Site Costing in order to achieve this.

• Any Direct Invoices with a ‘Planned’ status will not be taken into account for the purposes of establishing Sales Cost to the Inventory Cost centre (The Cost Centre Transaction is only created upon Printing the Invoice), however the Item Qty will be accounted for as it is deducted from stock immediately (irrespective of Invoice Status) when inserted onto the Sales Invoice line. Therefore, these costs will be skewed on this report. It is recommended that all Direct Invoices are ‘Printed’ prior to running this report.

• If a Cost Centre is specified as an Inventory Cost Centre against a Warehouse or Category, then all Warehouses must have Inventory Cost Centres specified. Either all warehouses  / categories specified or none, not a mix of some specified and other not.

Inventory - (Cost History Basis)

This style of valuation has been designed more for operational purposes rather than financial reconciliation. (This setting is in Inventory Rules – As At Valuation Style)

This report runs two routines in order to determine the Cost and Qty of an item at a point in time.

1st Routine – Determine Cost retrospectively:

• This routine will look back to the Item Cost History or Site Master Item Site Cost History records going back to the As At Date specified to determine the Average or Standard cost of the item at that point in time.

2nd Routine – Determine Qty retrospectively:

• The routine will establish the current stock qty on hand for the item (warehouse)

• To determine the Qty on hand for the As At Date specified, Ostendo will sum up all inventory transactions for the item (warehouse) forward from the first inventory transaction for each item. NB: Any Unit of Measure conversions are also evaluated during this process

Finally the report will take the calculated Qty and multiple it to the Average Cost found from the 1st routine to determine the Total Stock Value at the point.

Work In Progress As At Report Calculation Process (Assembly & Job):

This report runs a routine in order to determine the retrospective WIP. This will assist when balancing the Ostendo sub ledger with the Accounting Systems GL.

Routine – Determine WIP retrospectively:

• Ostendo will first establish the current WIP value by querying the CURRENTWIP Field on each Order Header record. 

• Ostendo will evaluate the appropriate WIP Cost Centre to use. If multiple Sites are in use, the Cost Centre Mapping By Site for Transaction Type Assembly or Job Work In Progress is read to determine the appropriate Cost Centre. If none is found, the appropriate Base Cost centre Mapping is used for either Jobs or Assembly WIP.

• Ostendo will then look at the Financial Tables and ‘unwind’ all financial values associated with this Order going back to the As At Date. 

To explain this further every DR to a WIP cost centre is deducted and every CR to a WIP cost centre is added to the current WIP value for that Order.

• The financial tables used for Assembly WIP As At are as follows:
• FINANCIALASSYRECEIPTS
• FINANCIALASSYISSUES
• FINANCIALASSYWIPVAR

• The financial tables used for Job WIP As At are as follows:
• FINANCIALASSYRECEIPTS (Where Job Outputs are specified)
• FINANCIALJOBISSUES
• FINANCIALJOBWIPVAR
• FINANCIALSALESCOSTS (Where Deferred Costs are used)

• The net result is reported as the WIP for each order grouped by site for the As At Date.

Example: