Calculate Inventory Item Average Cost

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 >

Calculate Inventory Item Average Cost

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

Implement the Average Cost of each Inventory Item calculation as follows:

Implementing this Average Inventory Cost methodology (which, once started, will be an internally maintained calculation) for tracking the Value of Inventory Items within the General Ledger System

1.Reset the AverageCostInventory option to True ("T"), so the Inventory Valuation will be 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"), re-setting the Inventory Valuation to the Average Cost paid for an Inventory Item, if LIFO (Last In/First Out) was the previously selected Inventory Valuation Method, it will be turned off (LIFO will be Un-Checked), automatically.

2.Once the AverageCostInventory option set to True ("T"), go to the Calculate Inventory Item Average Cost From

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 Inventory Item Average Cost FormChoose 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 that is defined in the Sales-Purchase Items Form.

 

Once this Average Cost of Inventory Inventory Valuation Method is implemented (see the Inventory Valuation Methods chapter for more information) for all Inventory Items:

The current PriceCOGS Value (Average Cost), and (total COGS Value of what is in stockInv. Value displayed on the Sale-Purchase Item Form - should be calculated when you start using the Average Inventory Cost methodology.

The Calculate Average Item Cost Form provides this (and other) functionality.

To open the Calculate Average Item Cost Form,

a)From the Backstage Menu System Select Maintenance and Choose Inventory Tracking, or

b)From the Quick Access Menu, Select Maintenance and Choose Inventory Tracking.

 

Select the Calculate Inventory Item Average Cost option

 

HelpFilesCalculateAverageItemCost

Calculate Average Item Cost Form

 

Then, Calculate the Average Inventory Item Cost be performing these three steps in the order listed below:

1.Click the Select/Unselect All option to insert a Check in each record's Update column.

2.Click on the Calculate Cost option to calculate both the COGS Value (Average Cost - including Sales tax - paid for the last 5 purchases of each Inventory Item) and the Price (Current Cost - most recent cost paid - exclusive of Sales Tax) of each Inventory Item - not including Kits - defined in the Sales-Purchase Items Form.

3.The Checks in the Update field will be removed.

4.At this point you may manually change the Average Cost field Value(s) if there are any specific Inventory Items whose calculation requires adjustment.

5.Click the Select/Unselect All option to again insert a Check in each record's Update column.

6.Click the Update Accounts option so this (initial) calculation of the Average Cost of your Company's Inventory Items will be posted to Sales-Purchase Items Form's Price (using the Current Cost) and COGS Value (using the Average Cost) fields, and the Inv. Value is also recalculated (by multiplying the Quantity On Hand by the updated COGS Value).

 

Thereafter, the COGS Value and the Inv. Value Amounts for the Inventory Item defined in the Sales-Purchase Items Form are automatically maintained by the system.

 

HelpFilesPurchaseItemsInventoryInfoAverageFields

Inventory Info box - Average Inventory Cost initial calculation

 

It's important to know that the Amount stored in the Price field in the Sales-Purchase Items Form is only used to insert a default Value of an Inventory Item being added to a Purchase Order and/or Bill,

When an Inventory Item is being added to a Purchase Order and/or Bill, the User is required to change that Value to the actual Price that - will be/is being - charged by the Vendor,

A new Price Value may be entered in the Sales-Purchase Items Form (perhaps because you always have to change the existing default Price consistently to the same - but different - Value)

By doing so, in the future, when creating a Purchase Order and/or Bill you may not have to manually update the Price field on the Purchase Order and/or Bill as frequently .

 

Understanding the Calculate Average Item Cost Form

Inventory Listing - When the Calculate Average Item Cost Form is opened, only those Sale-Purchase Items which have been identified as an Inventory Item and have actually been Purchased (versus those which were defined, but for which no Inventory has yet been acquired) will be listed.

 

HelpFilesCalculateAverageItemCostInventoryListing

 

A tabular (spreadsheet style) Record Listing of the Inventory Item records is displayed.

Note: Those Inventory Items that have been marked as Inactive will not be included in this Inventory Listing.

 

There are five columns of data in this Inventory Items Record Listing:

1)Update? - This is a Check Box field used to "select" this Inventory Item.

2)Item - The Sale-Purchase Item ID (Purchase Category Code) assigned to the Inventory Item

3)Description - The Description of this Inventory Item

4)Current Cost - This is the Price - excluding Sales Tax - that was entered manually in the Sales-Purchase Items Form, or previously updated using this procedure

5)Average Cost - This is the most recently calculated Average Cost - including Sales Tax - for the Inventory Item

 

Each column's Header Name describes the data contained in that column.

Clicking on a Header Name will set the order in which the Inventory Items will be listed.

Clicking on the same Header Name will set the order in the opposite direction (ascending vs. descending).

The Header Name that is determining the Order of the list will have an Icon indicating the Order displayed next to that Header Name.

 

Find - Use the Find option to locate a specific (set of) Inventory Item record(s):

 

HelpFilesCalculateAverageItemCostFindClear

 

Enter text to search... - Type in the characters that represent the Inventory Item(s) to be located.

All alphabetic characters - regardless of how they are entered - are treated as both lower case and upper case letters during the search process.

Click the Find option to display the results:

 

HelpFilesCalculateAverageItemCostFind

Search Results using the Find option

 

Make the changes needed:

oModify the Average Cost and/or

oCheck the Update box

Click the Update Accounts option if an Average Cost modification was made.

Click the Calculate Cost and the Update Accounts options if the updates represent Inventory Item record(s) that need to be recalculated (because they represent a new entry, and/or after an Inventory Adjustment).

Click the Clear option to display all of the Inventory Item records.

 

At the bottom of the Calculate Average Item Cost Form are the Action options

Actions - Click the option that provides the desired function:

 

HelpFilesCalculateAverageItemCostActions

Calculate Average Item Cost - Action options

 

Select/Deselect All - Select this Action Icon to Check (or Un-Check) each Update? field's Check Box (see the "Inventory Listing" section above).

 

HelpFilesCalculateAverageItemCostActions-UpdateColumn

oClick this option and Check Marks are placed in each Update? box.

oClick this option again and all of those Check Marks are removed.

 

Calculate Cost - Click on the Calculate Cost Icon on this Calculate Average Inventory Item Cost Form to update both the Average Cost (this corresponds to the COGS Value field in the Sales-Purchase Items Form) column, and the Current Cost (this corresponds to the Price field in the Sales-Purchase Items Form) column for each listed Inventory Item

 

HelpFilesCalculateAverageItemCostActions-RecalculateAverageCost

Calculate Average Cost?

 

oConfirm that this recalculation is needed.

oThe (re-)Calculated Average Cost (e.g., using the 5 most recent Purchases of the Inventory Item, the Purchase Price paid, and Quantity received for each Inventory Item is summed and then divided by that Quantity) column, and the Current Cost (e.g., Purchase Price paid - exclusive of Sales Tax - for the most recent Purchase of this Inventory Itemcolumn are updated on this Calculate Average Item Cost Form for each Checked Inventory Item.

 

Update Accounts - This option is used to selectively identify the Inventory Items for which the Average Cost is to be posted to the COGS Value field in the Sales-Purchase Items Form, and the total Value (Average Cost * Quantity On Hand) is to be posted to the Inv. Value field in the Sales-Purchase Items Form.

oUpdate? - Check this box for each Inventory Item which should have its COGS Value (Average Cost) and total Inv. Value (Average Cost * Quantity On Hand) updated on the associated Sales-Purchase Items Form.

oUpdate Accounts - Click the Update Accounts option to post these Values to  the associated Sales-Purchase Items Form.

 

HelpFilesCalculateAverageItemCostActions-UpdateItemCostNow

Update Item Cost Now?

 

a)For the 5 most recent Purchases of the Inventory Item, the Purchase Price paid, and Quantity received for each Inventory Item is summed and then divided (total price paid divided by total number received) to determine the COGS Value (Average Cost).

b)The Quantity On Hand multiplied by the recalculated COGS Value (Average Cost) is entered in Inv. Value field (this is the total Value of what is in stock) for that Inventory Item

oA summary of the updates is then displayed at the bottom of the Calculate Average Item Cost Form

 

HelpFilesCalculateAverageItemCostActions-UpdateItemCostResult

 

oA sample of the Sale-Purchase Item Form with these type of Updates is shown below.

 

HelpFilesSale-PurchaseItemsInventoryInfoBoxValues

Sale-Purchase Items Form - Inventory Info box - Inventory Value fields