visual estitrack™

Visual EstiTrack™

Understanding Inventory Reconciliation(Supplement)

November 2005

Visual EstiTrack™ Understanding Inventory Reconciliation

Henning Industrial Software, Inc. Page 1 Visual EstiTrack™ 11/2005

OVERVIEW: Methods of Reconciling Inventory Values

Most companies that maintain raw material, work in process and finished goods inventories post netchanges to these asset accounts on a monthly basis. The net changes posted to these accounts representthe change in value from one month to another.

Not every business uses the same method for calculating inventory values and net changes. Somebusinesses simply perform physical inventory counts each month for each item within each of the threeinventory classifications (i.e., Raw Material (RM), Work in Process (WIP) and Finished Goods(FG)) todetermine month ending values of these inventories. They then compare the current month’s values to theprevious month’s values to determine the net changes in these inventory accounts and use the generaljournal to post the net changes calculated. There are a wide variety of methods that businesses use tocalculate actual inventory value. This includes using actual cost, standard cost or at times a percentage ofthe item’s selling price.

Actual cost is typically used on valuation of raw material (i.e., directly purchased items) because this costcan be easily determined by the purchased cost of the item.

What is more complicated for businesses is the determination of WIP and FG values when manufactured“added value” and overhead expenses are added to the cost of the raw materials. Many companies wantto calculate their work in process and finished goods cost at actual value but lack the discipline toaccurately account for each added value cost and overhead components.

Only through the very detailed tracking and reporting of each added value and overhead component canan accurate cost value be derived. The four major component costs tracked in Visual EstiTrack includesmaterial, labor, burden/overhead and other (outside processes and miscellaneous costs). The shop order isthe container for recording these component costs. It is only through the accurate capturing of thesecomponent costs that an accurate actual value is calculated and assigned to the WIP and FG items beingproduced.

This means all labor hours for the shop order must be accurately reported; both employee labor andworkcenter burden/overhead rates must be accurately defined and maintained. In addition, all rawmaterial and manufactured components must be accurately consumed and all other costs entered andassigned to the appropriate shop order to get a complete and accurate actual cost.

If a business fails to completely capture and enter any of the actual cost details (i.e.,material, labor, burden/overhead and other costs) the calculated actual cost of an itemwill be inaccurate thus distorting the final WIP and FG inventory valuations.

Visual EstiTrack provides a formalized method for tracking actual job costs through shop order time andmaterials reporting. Visual EstiTrack’s Inventory Monthly Reconciliation screen provides thebookkeeper an interactive worksheet designed to summarize actual cost details in order to generate themonthly net changes accounting entry for RM, WIP and FG inventory values. In addition, the InventoryMonthly Reconciliation screen will calculate the movements of cost of goods sold (i.e., absorption ofcosts into WIP and recognizing the costs of goods shipped/sold).

If a business lacks the discipline to accurately record shop order component costs then theVisual EstiTrack s Monthly Inventory Reconciliation screen cannot be used and analternative manual accounting method for Raw Material, WIP and FG valuations must beused.



Alternative manual inventory accounting methods include using a standard cost for finished good items,using a standard cost for work in process items based on a percentage of completion or using a percentageof the selling price of an item.

In the end, there are a wide variety of methods used by businesses to valuate inventoryand it is your responsibility to determine which method works best for your company.

The remainder of this document describes Visual EstiTrack’s formalized Inventory MonthlyReconciliation method of calculating actual inventory values and cost of goods sold and the differentoptions available.

PROCEDURES: Monthly Inventory/Cost of Goods (COG) Adjustments

To accurately reflect profit and loss on the Income Statement it is important that you make appropriatemonthly adjustments to move expenses back and forth between the Income Statement and the BalanceSheet. The goal is to have the expenses that are reflected in cost of goods occurring during the samefiscal period as the sales of the goods sold.

While many accountants choose to calculate and enter these monthly adjustment entries manually, VisualEstiTrack’s Inventory Reconciliation Screen provides the means to calculate and generate the necessarymonthly adjustment entries directly via the general journal so that it can be posted to the general ledger.

The following outlines the typical monthly procedure used to make inventory and cost of goods generalledger adjustments. These monthly adjustments are used to “absorb” expenses from the Incomestatement to the Balance sheet’s WIP account for shop orders that are in process. Visual EstiTrack’sshop orders track all WIP component costs (i.e., direct labor, burden/overhead, material and othersubcontracting/miscellaneous costs) that occur during the life of the shop order.

These summarized component costs are used to move costs from the Balance sheet inventory accounts(i.e., either FG if you are using FG inventory or WIP if you are not using FG Inventory) to the IncomeStatement’s cost of goods sold accounts when FG items are shipped/sold or when a WIP to COGS shoporder is closed to match these expenses with the shipment/sales of items actually sold during the month.

In Visual EstiTrack™ these adjustment entries must be made as monthly general journal entries.

Visual EstiTrack supports a wide variety of inventory accounting methods. It is important that youdetermine your type of business operation to determine which inventory accounting method applies toyou. For example, some businesses don’t track Raw Material or Finished Good inventory. They simplybuy directly to WIP (i.e., shop orders) and then ship directly from WIP without reporting a finished gooditem. In this case, costs are reported directly to WIP via the shop order and then moved directly to cost ofgoods sold from WIP when the item is shipped/sold or when the shop order is closed. In other cases,businesses formally track Raw Material, WIP and FG inventory and all the movements between eachinventory account. While other businesses use a combination of both of these methods.

Let’s take a closer look at Visual EstiTrack’s Inventory Reconciliation criteria screen and options.



Inventory Reconciliation Criteria Fields and Options defined

The Reconciliation Criteria tab allows the user to define the accounting fiscal period, business specificoptions to customize the cost accounting methods and specific general account numbers used to generatethe general journal monthly entries. What follows is a description of each field and option.

Accounting Month and YearProvides selection of the Account Month and Year to reconcile inventory and generate the appropriateGeneral Journal entry.

Accounting Division / Accounting DepartmentProvides selection of the specific Accounting Division / Department to reconcile inventory and generatethe appropriate Division / Department General Journal entry. These fields are available upon checking theAdjust Inventory by Division / Department and/or the Adjust Cost of Goods Sold by Division /Department checkboxes. Selecting a Division and Department will calculate using transactions assignedto the selected Division / Department combination. Selecting a Division only will calculate usingtransactions assigned to the selected Division and all Departments of the Division. Selecting aDepartment only will calculate using transactions assigned to the selected Department and all Divisionsthe Department belongs to. And leaving both fields blank (right-click on the field to clear) will calculateall transactions of the company. This provides the ability to generate Division / Department entries whenthe accounts for each Division / Department have different account numbers. The Accounting Divisionand Departments are entered in Visual Books via the Tables submenu.

Default General Ledger Account Numbers



Provides one time entry of standard account numbers used to generate the General Journal Entries. EachDivision / Department combination will have its own default account numbers. You can use the sameaccount numbers in multiple default entries to combine the account types into the same general ledgeraccount number.

Adjust Inventory Accounts by Division / DepartmentCheck if you want to reconcile each Division/Department separately. This allows selection of an accountDivision / Department to post the Inventory accounts to. The Raw Material and Finished Goods item’sInventory Division / Department combination is defined by the Product code assigned to each Inventoryitem. The Work in Process item’s Inventory Division / Department combination is defined by the Productcode assigned to each Shop Order. Each product code is in turn assigned to an accounting Division /Department.

Adjust Cost of Goods Sold by Division / DepartmentCheck if you want to make reconcile each Division/Department separately. This allows selection of anaccount Division / Department to post the Cost of Goods Sold to. The Raw Material and Finished Goodsitem’s Cost of Goods Sold Division / Department combination is defined by the Product code assigned toeach Inventory item. The Work in Process item’s Cost of Goods Sold Division / Department combinationis defined by the Product code assigned to each Shop Order. Each product code is in turn assigned to anaccounting Division / Department.

Post Raw Material Inventory by Distribution CodesCheck if you separate the Raw Material account into multiple sub-accounts. This option posts to RawMaterial based on the item’s Distribution code. Each Distribution code can be assigned a Raw Materialaccount number. If the Distributions code’s Raw Material account number is blank that item’stransactions are posted to the default Raw Material account number entered on the Reconciliation Criteriascreen. Multiple Distribution codes can have the same Raw Material account number.

Post Finished Goods Inventory by Product CodesCheck if you separate the Finished Goods account into multiple sub-accounts. This option posts toFinished Goods based on the item’s Product code. Each Product code can be assigned a Finished Goodaccount number. If the Product code’s Finished Goods account number is blank that item’s transactionsare posted to the default Finished Goods account number entered on the Reconciliation Criteria screen.Multiple Product codes can have the same Finished Goods account number.

Post Cost of Goods Sold by Product CodesCheck if you separate the Cost of Goods Sold component accounts into multiple sub-accounts. Thisoption posts to the Cost of Goods Sold components (Direct Materials, Direct Labor, Burden / Overheadand Other) based on the item’s Product code. Each Product code can be assigned Cost of Goods Soldaccount numbers for Materials, Labor, Burden and Other. If the Product code’s Cost of Goods Soldcomponent account number(s) is blank that item’s transactions are posted to the default Cost of GoodsSold component account number(s) entered on the Reconciliation Criteria screen. Multiple Product codescan have the same Cost of Goods Sold account numbers.

Exclude Inventory Purchases portion of entryCheck if you directly buying to the inventory accounts on the vendor invoice. This options excludes theMaterials Purchase Receipts portion of the General Journal entry to avoid double posting the materialreceipts of the Distribution code’s Purchases (Uninvoiced) account number is the inventory accountinstead of using a formal purchases account or uninvoiced material receipts account.



Exclude Direct Work In Process portion of entryCheck this if you are posting cost of goods sold via the customer invoice and sales journal (option in thecompany table in Visual Books). This option excludes the Cost of Goods Sold components portion of theGeneral Journal entry to avoid double posting the sales/shipped component costs of the Product code’sCost of Goods Sold component costs (Material, Labor, Burden and Other) that are posted via theCustomer Invoice.

Post Rework to Rework Cost of Goods Sold AccountsCheck if you are posting rework shop orders to separate rework cost of goods sold accounts. If a shoporder is a rework order, indicated by checking the Rework checkbox on the shop order, the generatedGeneral Journal entry will use the Rework Cost of Goods Sold components account numbers entered onthe Reconciliation Criteria screen under this checkbox instead of the standard Cost of Goods Soldcomponent account numbers.



Let’s take a closer look at the components of Inventory Reconciliation.



(A) ABSORBING COSTS TO Work In Process (WIP) for Open/Active Shop Orders

1) DIRECT BUY MATERIALS (Items bought directly to the Shop Order)a. You should tie the purchase order line item to the Shop Order and Cost of Goods directly

by entering the appropriate shop order number and COGS account number into the lineitem of the Purchase Order. Be sure to select a non-inventory Distribution Code.

b. Use the receiving screen to receive in the lines items of a Purchase Order.c. Vendor invoices are directly tied to the received PO Line Items through the “Get

Receipts” button found on the Vendor Invoice maintenance screen. This marks thereceipt records as being invoiced and defaults the received quantity and appropriateCOGS account number into the vendor invoice to prepare it for posting to the generalledger.

d. The WIP account net change is adjusted through Inventory Reconciliation.

2) INVENTORIED RAW MATERIALa. You should NOT tie the purchase order line items to the shop order but directly tie the

line item to the Uninvoiced Receipts GL account. Be sure to select an inventory typeDistribution Code.

b. Use the receiving screen to receive in the line items of the purchase order.c. Vendor invoices are directly tied to the received PO Line Items through the “Get

Receipts” button found on the Vendor Invoice maintenance screen. This marks thereceipt records as being invoiced and defaults the Uninvoiced Receipts account numberinto the vendor invoice to prepare it for posting to the general ledger.

d. The Raw Materials account net change is adjusted through Inventory Reconciliation.

3) MOVING COSTS FROM RAW MATERIAL or FINISHED GOODS TO WIPa. Movements of costs from RAW MATERIAL or FINISHED GOODS to WIP are based

on shop order job card inventory usage activity during the month.b. Use the Inventory Journal Report selecting the “Used in Production” transaction type and

either the RAW MATERIAL or FINISHED GOODS inventory type to calculate theamount of material that needs to be credited (decreased) from the RAW MATERIAL orFINISHED GOODS GL accounts and debited (increased) to the WIP inventory account.

c. Make a general journal entry to Credit (reduce) either the RAW material GL account orthe Finished Good account and Debit (Increase) the WIP Inventory Account.

4) LABOR (Direct Labor on Shop Orders)a. Direct Labor payroll is typically directly expensed out on Income Statement through the

posting of the Payroll journal but then offset (removed as an expense) back to the WIPinventory account on the Balance sheet based on all the Job Card direct labor activityoccurring during the month.

b. Most clients achieve this movement by setting up a LABOR ABSORPTION account.The absorption account is used to move direct labor expenses off the income statement tothe balance sheet’s WIP account.

c. Calculate the total WIP Direct Labor by running the Production Costs report for job cardsthat were executed during selected month.

d. The total direct labor number will then be used to reduce total labor expenses by crediting(reducing) the labor absorption account and debiting the WIP account.



e. The net effect of this is to reduce the payroll expense that occurred during the month bythe direct labor that was performed on active shop orders thus moving those direct laborexpenses from the income statement to WIP on the balance sheet.

5) BURDENa. Burden (Overhead such as office salaries, rent) is typically directly expensed out on

Income Statement but then offset (removed as an expense) back to WIP inventoryaccount on the Balance sheet for the all Job Card activity occurring during the month.

b. Most clients set up a BURDEN ABSORPTION account to move burden expense off theincome statement to the balance sheet’s WIP account.

c. Calculate the total WIP Burden expense by running the Production Costs report for jobcards that were executed during selected month.

d. The total Burden number will then be used to reduce total burden expenses by crediting(reducing) the Burden absorption account and debiting the WIP account.

e. The net effect of this is to reduce the burden expense that occurred during the month bythe burden that was carried on active shop orders thus moving those Burden expensesfrom the income statement to WIP on the balance sheet.

6) OUTSIDE (SUBCONTRACTED)a. You should tie the purchase order line item to the Shop Order and Cost of Goods GL

account directly by entering the appropriate shop order number and COGS accountnumber into the line item of the Purchase Order.

b. Use the receiving screen to receive in the lines items of a Purchase Order.c. Vendor invoices are directly tied to the received PO Line Items through the “Get

Receipts” button found on the Vendor Invoice maintenance screen. This marks thereceipt records as being invoiced and defaults the appropriate COGS account number intothe vendor invoice to prepare it for posting to the general ledger.

d. The WIP account net change is adjusted through Inventory Reconciliation.

(B) MOVING FROM WIP TO COG (EXPENSES)

Many clients do not use finished goods inventory but rather ship directly out of WIP. The followingprocedure outlines the required procedure for moving costs out of WIP on the balance sheet back to thecost of goods sold on the Income statement. Make sure that ALL costs are against the shop order beforeclosing the shop order.

PLEASE NOTE: If you need to split parts out of an existing shop order to get them shipped out faster,you should use the Split Shop Order screen to separate the parts and the costs that you are finishing outinto its own shop order. In this way you can complete the processing of the split shop order and close itso you can ship the parts so the cost of the goods sold will be updated appropriately. The original shoporder would remain in WIP until it is completed.

When the Shop Order is closed and the items are shipped you should then move the summarizecomponent costs out of the WIP account and back to the cost of goods accounts. Use the Cost of Salesanalysis report or one of the Work in Process Reports to determine the component costs (i.e., labor,burden, material and other) of the shop orders that closed during the month.

Once the total costs and component costs have been determined use these numbers to:



1) Credit (decrease) WIP for the total costs of the shop orders closed during the month and debit(increase) the appropriate COG expense accounts on the Income Statement (i.e., Debit Material,Labor and Burden). This adjustment aligns the cost of goods with the actual sales of the goods.

(C) MOVING FROM WIP TO FINISHED GOODS

If you make to stock and then sell from your finished good inventory then you will need to make amonthly adjustment to move costs from your WIP asset account to your FINISHED GOODS assetaccount.

The following procedure outlines the required procedure for moving costs out of WIP on the balancesheet to Finished Goods on the Balance sheet. Make sure that ALL costs are against the shop orderbefore closing the shop order.

PLEASE NOTE: If you need to split parts out of an existing shop order to get them shipped out faster,you should use the Split Shop Order screen to separate the parts and the costs that you are finishing outinto its own shop order. In this way you can complete the processing of the split shop order and close itso you can ship the parts so the cost of the goods sold will be updated appropriately. The original shoporder would remain in WIP until it is completed.

When the Shop Order is closed you should then move the summarize component costs out of the WIPaccount to the Finished Goods account.

Use the Cost of Sales analysis report or one of the Work in Process Reports to determine the componentcosts (i.e., labor, burden, material and other) of the shop orders that closed during the month.

Once the total costs and component costs have been determined use these numbers to:

1) Credit (decrease) WIP for the total costs of the shop orders closed during the month and debit(increase) FINISHED GOODS inventory for the same amount.

2) The net effect of this transaction is that all costs stay on Balance sheet… they simply move fromone asset account to another (i.e., from the WIP inventory account to the Finished Good inventoryaccount)

(D) MOVING FROM FINISHED GOODS TO COG (Expenses)

If you sell directly out of Finished Goods inventory it is important that you calculate the costs of theFinished goods sold during the month so that you can credit (reduce) the finished goods inventoryaccount and increase (debit) the appropriate cost of goods accounts.

When finished good items are sold…

1) Use the show component costs on the Inventory Journal report filtering by the “Sale/Shipped”transaction type to determine the total and component costs of the goods sold during the month.

2) Use these summarized costs to credit (reduce) Finished Good inventory on Balance Sheet anddebit (increase) COG expenses on Income Statement by debiting the values of Labor, Burdenand Material.

3) The net effect of this adjustment is to reduce finished goods and increase the cost of goodsexpenses so the appropriate expense are in same fiscal period as the actual Sales of those items.

(E) TEMPORARY LIABILITY ACCOUNT



It is quite common that goods are received into inventory (i.e., Raw Material and WIP inventory) prior tothe actual receipt of the Vendor Invoice. Many clients want to reflect that future liability on the generalledger even though the liability is not yet reflected in Accounts Payable.

You can achieve this adjustment by running the Receiving Log report for all uninvoiced receipts. Thenumber that is shown can be used to update a temporary liability account.

Inventory Reconciliation - Processing costs using materials out of inventory

Be sure to define your default G/L chart of accounts numbers. Keep in mind that you MUST define aStandard Cost in Finished Good Inventory. The system uses Standard Cost when calculating the finishedgoods into inventory. Upon closing the Shop Order the system will recalculate using the Actual Cost andshow the adjustment in another month, if applicable.



Let’s begin with “0” balances.

I’ve created job cards to complete the first operation. Costs moved into WIP, out of raw materials andburden, labor were absorbed.

Let’s now take a look at the G/L Entries.



Next I created a purchase order for heat treating and received it in. Notice the change in WIP and OtherDirect Costs Absorption.



Next I finished the job making and completing finished goods into inventory. Notice the costs beingmoved from WIP into Finished Goods at the Standard Cost.

This is what the G/L Entries would look like if we posted now.



I will now create a shipment and close the shop order. This recalculates the FIN costs from Standard toActual and then moves expenses to COG.

Let’s look at the G/L Entries.



This is the break down.

Debit CreditSales 3400.00

COSTS OF GOODSCost of Goods Sold 2800.00

Inventory WIP 441.52Inventory Raw Materials 580.48Direct Labor Absorption 438.00Burden/OH Absorption 440.00

Other Direct Costs Absorption 900.00TOTALS: 2800.00 2800.00

Profit (Loss) 600.00

Processing costs without using materials out of inventory, but buying directly to the job.



Let’s begin with “0” balances.

I created a purchase order for materials to go directly to a specific job and received it.



If you look at the G/L Entries you will see WIP Debited and Direct Costs Absorption Credited. Uponreceiving the Vendor Invoice you will be Debiting Purchases in Cost of Goods and Crediting AccountsPayable.

Debit CreditWIP (Asset) 1501.00 Direct Costs Absorption (COG) 1501.00Direct Purchases (COG) 1501.00 AP (Liability) 1501.00

I next created job cards to complete produce these parts. Costs moved through WIP and then to FinishedGoods.



Let’s now take a look at the G/L Entries.

Since I did not post transactions until the end of the month the first line item will not occur. Also note thatthe Finished Goods is based on Standard Cost.Debit CreditWIP (Asset) 1501.00 Direct Costs Absorption (COG) 1501.00Direct Purchases (COG) 1501.00 AP (Liability) 1501.00

FIN (Asset) 268.28WIP (Asset) 1531.72

Absorptions (COGS) 1800.00



I will now create a shipment and close the shop order. This recalculates the FIN costs from Standard toActual and then moves expenses to COG.

Let’s look at the G/L Entries.



Since I did not post transactions until the end of the month the only entry remaining open will bePurchases and Payroll.Debit CreditWIP (Asset) 1501.00 Direct Costs Absorption (COG) 1501.00Direct Purchases (COG) 1501.00 AP (Liability) 1501.00

FIN (Asset) 2073.68 WIP (Asset) 273.68Absorptions (COGS) 1800.00

COGS Absorption 1800.00 COGS Absorption 1800.00

This is how it would look in the GL:

Debit CreditSales 2467.00

COSTS OF GOODSMaterials Purchased 1501.00

Actual Payroll 200.00Labor Absorption 184.02 184.02

Burden/OH Absorption 115.02 115.02Direct Costs Absorption (materials) 1500.96 1500.96

TOTALS 3501.00 4267.00

Profit/Loss 766.00

visual estitrack™

Documents