❑Implement the Average Cost of each Inventory Item calculation as follows:
❖You must run the Start Inventory Tracking/Job Costing setup wizard before using this Calculate Inventory Item Average Cost Form
➢Important Note: If your Company is using the General Ledger System, see the "For General Ledger System Users" discussion at the end of this chapter!
•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.
✓The Inventory Valuation will be based on the Average Cost paid for an Inventory Item versus (whatever was) the previously selected Inventory Valuation method.
1.Initially, the AverageCostInventory option in Company Settings should be set to False ("F").
2.Go to the Calculate Inventory Item Average Cost Form
▪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 Form, follow the instructions below 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.
4.This is a One Time Process.
a.Once this process is executed:
i.the Calculate Inventory Item Average Cost menu option is no longer displayed
ii.the AverageCostInventory option in Company Settings is set to True ("T").
iii.the ITJCIsInstalled option in Company Settings is set to True ("T").
b.The Start Inventory Tracking/Job Costing option in the File Menu is no longer available.
i.Open the User Options Form and select the Inventory Options tab
User Options Form - Inventory Options tab
ii.On the Inventory Options tab ensure that your Company's Default Warehouse is identified and the Default Job Cost Invoice Code is selected.
iii.See the Inventory Options chapter for more information.
iv.Follow the detailed instructions in the "Step by Step Instructions" discussion below.
❑Step by Step Instructions on how to complete the implementation of this Average Cost of Inventory Inventory Valuation Method (see the Inventory Valuation Methods chapter for more information) for all Inventory Items:
•The current Price, COGS Value (Average Cost), and (total COGS Value of what is in stock) Inv. Value displayed on the Sale-Purchase Item Form - will be calculated automatically after you start using the Average Inventory Cost methodology.
•The Calculate Average Item Cost Form provides the initial Average Inventory Cost calculation for all of the Sale Purchase Items identified as Inventory Items.
✓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.
•Calculate Inventory Item Average Cost - Inventory List - When the Calculate Average Item Cost Form is opened and the Retrieve Data option is selected (see below), 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.
➢Note: Those Inventory Items that have been marked as Inactive will not be included in this Inventory Listing.
Calculate Average Item Cost Form
✓There are many columns of data in this Inventory Items Record Listing:
1)Update box - This is a Check Box field used to "select" this Inventory Item.
2)Item ID - The Sale-Purchase Item ID (Purchase Category Code) assigned to the Inventory Item
3)Description - The Description of this Inventory Item
4)On Hand - Quantity of this Inventory Item that is currently on hand
5)Current COGS Value - This is the Price - including Sales Tax* - that was entered manually in the Sales-Purchase Items Form, or previously updated using this procedure
6)Proposed Average Cost - This is the most recently calculated Average Cost - including Sales Tax - for the Inventory Item
7)Proposed Inventory Value - This is the most recently calculated Average Cost multiplied by the Quantity on hand - including Sales Tax - for the Inventory Item
8)Gross - This is the most recently calculated Average Cost multiplied by the sum of all of the Quantity - including Sales Tax - for the Inventory Item
9)Quantity - This is the sum of all of the Quantities of the Inventory Item
* The only time Sales Tax is not calculated as part of an Inventory Item's cost is when the Canada Tax option is set to True ("T") in Company Settings because Canadian Sales Tax is initially posted as an Asset (see the Canada Sales Tax chapter for more information).
✓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.
•Columns - Select which columns of data are to be displayed:
Available Columns
✓Click the asterisk (¬) at the left of the data grid's Header row (see the mouse pointer in the illustration above) to display a list of all of the column names - and associated data - which may be displayed.
✓By default, all Column Names will be Checked.
✓Remove the Check mark (Click on the Check to remove it) to hide that column of data from the data grid.
•Once the Form is opened and the Columns to be displayed are selected, Calculate the Average Inventory Item Cost be performing these steps in the order listed below:
1.Click the Retrieve Data option
2.Click the Update field's box (it is the Check box on the left side of the data grid's Header next to the Asterisk used to view the list of available columns) to insert a Check in every record's Update box.
3.This will highlight each Inventory Item in Orange.
4.Click the Update Items option to calculate both the COGS Value (Average Cost - including Sales tax - paid for all purchases of each Checked 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.
5.Answer Yes to the Update request.
Update "COGS Value" and "Inventory Value" for selected items?
6.All the Checks in the Update field will be removed automatically.
•At this point you may manually change the Proposed Average Cost field Value(s) if there are any specific Inventory Items whose calculation requires adjustment.
7.If changes are made, Click the Update field's box to again insert a Check in each of those record's Update column.
8.Click the Update Items option so this (initial or updated) calculation of the Average Cost of your Company's Inventory Items will be posted
9.Answer Yes to the Update request if displayed again..
Update "COGS Value" and "Inventory Value" for selected items?
10.The process is completed message is displayed. Click OK to exit.
11.The Sales-Purchase Items Form's COGS Value and the Inv. Value is recalculated (by multiplying the Quantity On Hand by the newly calculated COGS Value).
•Once you have Logged out and Logged in again:
✓The AverageCostInventory option is set to True ("T") in Company Settings. thus completing the Inventory Tracking/Job Costing Start-up procedure.
✓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.
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 .
❖For General Ledger System Users:
✓Once this Average Cost of each Inventory Item calculation process is executed (Update Items was selected) a Journal Journal Entry must be performed.
▪This is because the Gross Value of your Company's Inventory has changed during the Average Cost Update..
✓To determine the specific Journal Journal Entry that's required:
1)On the Balance Sheet created for Today, locate and note the Value of the Inventory Asset Account
2)On the Inventory List (Grid) As Of report created for Today, locate and note the Value of the Inventory's As Of Value column's Grand Total displayed at the bottom of the Grid.
3)Determine the Inventory Value difference:
a)If the Balance Sheet's Inventory Value:was less than the As Of Value on the Inventory Listing report, Subtract the Balance Sheet Inventory Asset Account's Value from the Inventory List (Grid) As Of Value Total Value.
b)If the Balance Sheet's Inventory Value:was greater than the As Of Value on the Inventory Listing report, Subtract the the Inventory List (Grid) As Of Value Total Value from the Balance Sheet Inventory Asset Account's Value.
▪The Inventory Value difference (in either case) will be used in the Journal Journal Entry
✓The General Ledger's Inventory Valuation related Mandatory Accounts are:
1)Inventory Asset Account - The Gross Value of all On Hand Inventory Items
2)Inventory Adjustment Expense Account - The Expense Account for all Inventory Adjustments
▪These two accounts will be used to make the required Journal Journal Entry
✓Making the required Journal Journal Entry
1)If the Balance Sheet's Inventory Value:was less than the As Of Value on the Inventory Listing (Grid) report (the usual case):
a.Debit the the Inventory Asset Account' for the Amount of the Inventory Value difference
b.Credit the Inventory Adjustment Expense Account for the same Amount of the Inventory Value difference
2)If the Balance Sheet's Inventory Value:was greater than the As Of Value on the Inventory Listing (Grid) report (a possible):
a.Credit the the Inventory Asset Account' for the Amount of the Inventory Value difference
b.Debit the Inventory Adjustment Expense Account for the same Amount of the Inventory Value difference