Inventory & Job Data Maintenance Procedures

This Help File Page was last Modified on 07/02/2018

<< Click to Display Table of Contents >>

Navigation:  Inventory Tracking & Job Costing System >

Inventory & Job Data Maintenance Procedures

This Help File Page was last Modified on 07/02/2018

The Inventory Tracking and Job Costing Set Up procedures should have been completed.

Here are a few other capabilities which should be reviewed and more importantly, understood

They provide additional functionality that will be quite useful in performing certain Inventory Tracking and Job Costing System processes.

 

Special Features for Inventory Tracking

There are five special functions/capabilities documented in the chapters outlined below which will make using the Inventory Tracking & Job Costing System a more powerful and productive process.

1.Kits - This chapter explains how to create User Defined Kits from existing Inventory items.

a.These User Defined Kits may then be used for:

i.creating Proposals

ii.creating Sales Invoices

iii.creating Purchase Orders

iv.recording Purchases

v.identifying and tracking Inventory Usage within the Job Costing module

vi.identifying and tracking Inventory items that were used while completing a Service Request in the Service Tracking System.

 

2.ADI Import - This chapter explains how to selectively import an existing ADI Inventory list (i.e., spreadsheet) into the Sale-Purchase Items Form.

a.ADI is a wholesale inventory and materials provider to more than 80,000 installer contractors in North America - many of whom are Security System installers.

b.Accommodating these users, the complete ADI inventory list may be imported (in whole or in part) into the Sale-Purchase Items table as Inventory items.

 

3.Transfers - This chapter explains how to Transfer Inventory from one Warehouse to another.

a.It is often necessary to pass equipment from one Warehouse, Technician, and/or their Truck, etc., to another.  

b.The Warehouse Transfer Form allows you to Transfer Inventory and accurately track the movement of those Inventory Items.

 

4.Edit Warehouse Inventory Levels - This chapter explains how to Set new, and Modify existing Reorder (re-purchase) rules for Inventory Items, and how to adjust these numbers based on the actual quantities found while, or after taking a physical Inventory.

a.Allows you to adjust one or more of the Inventory Count(s) displayed in the Warehouse Inventory Form for any Inventory Item.

 

5.Calculate Average Item Cost - This chapter explains what occurs when the Average Cost of Inventory option is implemented (see the Inventory Valuation Methods chapter for detailed information):

a.It will only effect all Sale-Purchase Items identified as an Inventory Item

b.The COGS Value (Average Inventory Item's Cost) and Inv. Value (total Value of what's in stock) displayed on the Sale-Purchase Item Form - will be (re-)calculated automatically (by the system).

c.However, for General Ledger System Users, the recommended Inventory Valuation method is Last In/First Out ("LIFO")!

 

Inventory Valuation Methods

There are three Inventory Valuation Methods available (also see the "An Analysis of the various Inventory Tracking System Processes" section in the Understanding how Inventory Values & Counts are Calculated chapter)

1.Original Price - The cost originally entered in the Price field on the Sale-Purchase Items Form

2.LIFO - Last In / First Out - The COGS Value is updated to the Price Paid, each time the Inventory Item is Purchased, when that Price is different [for General Ledger System Users, the recommended Inventory Valuation method is Last In/First Out ("LIFO")].

3.Average Inventory Cost - The COGS Value is calculated, as needed, initially using the Calculate Inventory Item Average procedure, and afterword, automatically as additional Inventory Items are Ordered and Received.

 

Original Price (Cost) - By default, the Value of each Inventory Item is the original Price (Cost) assigned to that Inventory Item when it was entered in the Sale-Purchase Category Form.

Whenever an Inventory Item is Purchased or Sold, that original Price is the Value that is used in the associated General Ledger System transaction. 

If using this Original Price Valuation Method, future Purchases - regardless of the Cost paid for an Inventory Item - do not change the COGS Value Value (Cost) which the Inventory Tracking System and the General Ledger System will use to Value that Inventory Item.  

 

LIFO - From time to time, Inventory Items may be Purchased for more, or less, than the Price that was entered as the original Price Value on the Sale-Purchase Item Form.  

If using the Original Price Valuation Method (explained above) these Purchases do not change the Price or COGS Value (Cost) which the Inventory Tracking System and the General Ledger System uses to Value that Inventory Item.  

So the Inventory Item continues to be Valued in Inventory Related Reports and General Ledger System's Financial Statements and Reports using the Price amount that was initially entered in the Sales-Purchase Items Form.  

 

However, from an accounting standpoint, you may want to alter this default behavior by having that Price Value re-set, based on the most recent Cost paid on an Invoice for the Re-Purchase of each Inventory Item.  

This automatic COGS Value (Cost) field is re-set - based on the most recent price paid (Cost) - and is referred to as Last In - First Out, or "LIFO" for short.

Check the LIFO (Last In First Out) box on the Inventory Options tab of the User Options Form to initiate this Inventory Valuation Method.

 

HelpFilesUserOptionsInventoryOptionsLIFO

User Options - Inventory Options tab - Check the LIFO (Last In First Out) box

 

Thereafter, whenever an Inventory Item is Purchased or Sold, this (LIFO) Value is immediately updated and becomes the Value that is used in the associated General Ledger System transactions.

For those Companies using the General Ledger System, the recommended Inventory Valuation method is Last In/First Out ("LIFO")

 

Average Cost of Inventory (Another approach to Inventory Valuation is to use the Average Inventory Cost for the Value that is used in the associated Inventory Related Reports

Using the default Original Price or optional LIFO Inventory Valuation Method may create anomalies when comparing those General Ledger System and Inventory Valuations in Inventory Related Reports.

This valuation drift occurs over time as Costs are reset but the previous history and timing of those Cost resets is unclear.

 

A different approach in establishing and maintaining the Value of the Inventory is to continuously Average the Cost of Inventory so that all Price (Cost) changes are taken into consideration whenever the Value of an Inventory Item is reported.

By Averaging the Cost of each Inventory Item - by adjusting the Cost of each Inventory Item whenever it is Purchased - should eliminate this valuation drift.

 

Once implemented, the Inventory Cost Averaging Calculation records each Purchase of an Inventory Item, the specific Price (Cost) paid for that item, and the Quantity of each Inventory Item that was Purchased.

Inventory Cost Averaging Calculation - Inventory Item that is currently "in stock" is determined by adding the Quantity of new Inventory Purchases and subtracting the Quantity of new Inventory Sales.

An Average Inventory Cost table is internally maintained by the system for each Sale-Purchase Item that has been identified as an Inventory Item

This table contains the following information:

a.Quantity of each Inventory Item that was Purchased

b.Unit Price Paid for each of those Inventory Items

c.Total Price Paid for the Purchase of those Inventory Items

d.Current Count (the Quantity On-Hand) of each Inventory Item

e.Total Cost Paid for all of the Inventory Items that are currently On-Hand

f.Average Cost of each Inventory Item that is On-Hand.

The current Average Cost Value is displayed on the Sale-Purchase Item Form for each Inventory Item

 

This example of the Average Inventory Cost table shows how it is accessed and updated as Inventory is Purchased and Sold:

 

 

Quantity

Unit Price

Total Price

Count

Total Cost

Avg. Cost

1st Purchase

1

$3.00

$3.00

1

$3.00

$3.00

2nd Purchase

2

$3.00

$6.00

3

$9.00

$3.00

3rd Purchase

3

$1.00

$3.00

6

$12.00

$2.00

1st Sale

-1

 

 

5

$10.00

$2.00

4th Purchase

1

$4.00

$4.00

6

$14.00

$2.33

2nd Sale

-1

 

 

5

$11.67

$2.33

 

Sample Average Cost Valuation Calculations

 

For the Purchase of an Inventory Item is entered, a corresponding entry is made in this Average Inventory Cost table with:

a)The Quantity that was Purchased

b)The Unit Price Paid

c)The Total Price Paid

d)A new Count is calculated by adding the Quantity that was just Purchased to the previous Count.

e)The new Total Cost Paid is calculated by adding the Total Price Paid to the previous Total Cost

f)The Average Cost is re-calculated by dividing the new Total Cost be the new Count

 

When a Sale of an Inventory Item is entered, an entry is made in this Average Inventory Cost table with:

a)The Quantity that was Sold

b)A new Count is calculated by subtracting the Quantity Sold from the previous Count.

c)The Total Cost Paid which is recalculated by subtracting the result of the Quantity Sold multiplied by the previously calculated Average Cost.

d)The Average Cost is re-calculated by dividing the new Total Cost be the new Count

 

Implementing the Average Inventory Cost internally maintained calculation (Inventory Valuation methodology) for tracking the Value of Inventory Items within the General Ledger System.

Note: By default, the AverageCostInventory option in the Company Settings dialog - accessible from within the Company tab of the User Options Form - is set to False ("F").

 

HelpFilesUserOptionsCompany-CompanySettings-AverageCost Inventory

User Options - Company tab - Company Settings dialog - Average Cost Inventory option

 

1.If the AverageCostInventory option set to True ("T"), Inventory Valuation will be set based on the Average Cost paid for an Inventory Item versus (whatever was) the previously selected Inventory Valuation method.

When AverageCostInventory is set to True ("T"), setting the Inventory Valuation to the Average Cost paid for an Inventory Item, if LIFO (Last In/First Out) was the selected Inventory Valuation Method, it will be turned off (LIFO will be Un-Checked) automatically.

2.Once the AverageCostInventory option set to True ("T"), from the Quick Access Menu, Select the Maintenance Menu and Choose the Inventory Tracking sub-menu, then Select the Calculate Inventory Item Average Cost option.

3.On the Calculate Average Item Cost Form, Choose the Calculate Cost option to update both the Average Price (COGS Value field) and the current Total Value (Inv. Value field) of each Inventory Item defined in the Sales-Purchase Items Form.

 

Summary of the effects on the Sale-Purchase Item Form as they relate to these Inventory Valuation Methods:

The COGS Value for an Inventory Item will be the default Price inserted for the Inventory Item when entering a Bill.  

 

HelpFilesBillFormInventoryDetailLineItemPrice

 

How and whether the Price and/or COGS Value fields will be displayed within the Inventory Info box on the Sale-Purchase Item Form is based on which Inventory Valuation Method has been elected.  

The differences in how these Price and/or COGS Value fields are displayed are as follows:

Original Price (Cost) - If the Original Price Inventory Valuation Method is in effect, the Price field will be displayed as a normal mandatory field on the Sale-Purchase Item Form if this is an Inventory Item.

 

HelpFilesPurchaseItemsInventoryInfoFields

Inventory Info box - Original Price Inventory Valuation Method selected

 

Price - Enter the Price that should be used for calculating the Cost of Goods Sold Value of this Inventory Item.

COGS Value - This field is filled in by the system once the Price is entered (see illustration above).

oWhen the Original Price Inventory Valuation Method is in effect, the contents of the Price and COGS Value fields will remain unchanged.

 

LIFO - If the LIFO Inventory Valuation Method is in effect, the contents of this COGS Value field will be updated each time another Inventory Item of this type is Purchased with the Cost re-set to the most current Price that charged on a Bill.

For those Companies using the General Ledger System, the recommended Inventory Valuation method is Last In/First Out ("LIFO")

 

HelpFilesPurchaseItemsInventoryInfoFieldsLIFO

Inventory Info box - LIFO Inventory Valuation Method selected

 

Price - Enter the Price that should be used initially for calculating the Cost of Goods Sold Value of this Inventory Item.

COGS Value - This field is filled in by the system once the Price is entered (see illustration above).

oWhen the LIFO Inventory Valuation Method is in effect, the contents of the COGS Value field will change to the current Price Paid each time there is an additional Purchase of this Inventory Item.

 

Average Inventory Cost - If the Average Inventory Cost Inventory Valuation Method is in effect, the contents of the COGS Value and Inv. Value fields will be updated each time another Inventory Item of this type is Purchased 

a)the Value of the COGS Value field is re-set to the Average Inventory Cost based on what is charged on the current Bill averaged with what was previously charged for these Inventory Items currently 'in-stock' (i.e. Quantity On Hand plus WIP - Work In Progress - Quantity), and

b)the Value of the Inv. Value field is recalculated based on the COGS Value multiplied by the Quantity of the Inventory Items that are currently still 'in-stock' (i.e. Quantity On Hand plus WIP - Work In Progress - Quantity). 

 

HelpFilesPurchaseItemsInventoryInfoAverageFields

Inventory Info box - Average Cost Inventory Valuation Method selected

 

COGS Value - This system maintained field is updated based on what is charged on the most current Bill, averaged with what was previously charged (including Sales Tax) for these Inventory Items currently still 'in-stock'. (see illustration above).

Inv. Value (total value of what is actually 'in-stock' On Hand and WIP for this Inventory Item) - This will be a system maintained field which is also updated, as needed, using the Calculate Average Inventory process.

Price - The Price that was entered initially for calculating the Cost of Goods Sold Value of this Inventory Item.

oThis Price Value may be changed manually when appropriate, usually because it is inserted as the default Value for the Price when creating a Purchase Order and/or entering a Bill, and the most recent Price being charged for this Item by Vendors has changed substantially.