DOCUMENTATION

PURPOSE

The INVENTORY MANAGER is a supplementary tool designed to assist organizations that sell physical goods with tracking the inflows (purchases) and outflows (sales) of inventory stock, running quantity and inventory value balances, and calculating the Cost of Goods Sold (COGS) amount for input into the Sales accounting module of the MASTER LEDGER. Inventory that is purchased or produced and unsold remains on the balance sheet. Once sold, the value of inventory sold transfers from the balance sheet to the income statement as an expense in the form of Cost of Goods Sold. The two most widely accepted methods of calculating the value of COGS are FIFO (First-In, First-Out) and Weighted Average (WA, or AVCO).

FIFO

The First-In, First-Out inventory valuation method reduces the inventory value balance by the earliest inventory first. In times of rising inflation, this usually means a lower COGS amount due to the lower cost value of the earlier inventory stock.

WA

The Weighted Average inventory valuation method averages the unit cost across all available inventory units at the time of sale, which evens out some of the price fluctuations between earlier stock and later stock. Weighted Average cost is calculated as follows:

 Weighted Average = Total Cost of All Available Items ÷ Total Number of Available Items

Back to Top

Step 1: Import the content from the MASTER LEDGER’S ‘EXPORT LOOKUP’ tab.

Copy (Ctrl + C) the content from the MASTER LEDGER’s Export Lookup to Nexus tab and paste values (Ctrl + Shift + V) into the Import Lookup Table. The main column required is Product ID Name from the MASTER LEDGER, which is set up in the Product & Inventory Lookup tab. This column can also be input manually, if not using together with the MASTER LEDGER. The content under Product ID Name populates the dropdown lists in the next tab, ‘2 – Inventory Manager’.

Step 2: Proceed to the tab ‘2 – Inventory Manager’.

 Back to Top

Step 1: Populate the Inventory Purchases table with all purchases of inventory stock.

The Inventory Purchases table consists of four required input columns and one automated column. Add new entries to the table as additional finished inventory is purchased or produced. These entries should also be recorded in the Inventory & Assets accounting module under Add/New Asset or Inventory Purchase in the MASTER LEDGER. Space has been provided for 1000 entries.

Transaction Date: Required.

Input the date of the purchase or production of the inventory stock. Used to help determine which inventory stock is sold first, or which inventory stock is available for a weighted average cost calculation as of the date of sale.

Product ID Name: Required.

Select the type of inventory stock purchased or produced (usually finished inventory) using the dropdown menu. The options in the dropdown come from the 1 – Import Lookup tab.

Quantity: Required.

Input the quantity of inventory stock purchased or produced.

Unit Cost: Required.

Input the acquisition or production cost per unit of inventory stock.

Total Cost: Automated.

Automatically calculates the total value of inventory stock added. Calculated as Quantity × Unit Cost.

Step 2: Populate the Inventory Sales table with all sales of inventory stock.

The Inventory Sales table consists of three input/selection columns. Add new entries to the table as additional finished inventory is sold. These entries should also be recorded in the Sales accounting module under Add/New Inventory Sales Revenue in the MASTER LEDGER. Space has been provided for 1000 entries. When finished adding new entries, select Refresh All from the Data tab in the top Excel ribbon to process the added purchases and sales.

Transaction Date: Required.

Input the date of the sale of inventory stock.

Product ID Name: Required.

Select the type of inventory stock sold using the dropdown menu. The options in the dropdown come from the 1 – Import Lookup tab.

Quantity: Required.

Input the quantity of inventory stock sold.

Step 3: Refresh All and view the calculated Cost of Goods Sold (COGS) outputs.

If not already done at the end of the previous step, select Refresh All from the Data tab in the top Excel ribbon to process the added purchases and sales.

Based on the two methods offered (FIFO and Weighted Average), select one of the COGS columns to reference for the COGS amount for each sales transaction. Input the COGS amount input into the Sales accounting module in the MASTER LEDGER when adding a new Inventory Sales Revenue transaction with inventory.

As consistency is a core accounting principle, accounting best practice is to continue using the same method for all inventory sales reporting throughout the same tax year.

FIFO COGS: First-In, First-Out Cost of Goods Sold.

Calculates the total value of inventory sold per sales transaction based on the earliest inventory remaining under that Product ID Name.

WA COGS: Weighted Average Cost of Goods Sold.

Calculates the total value of inventory sold per sales transaction based on the weighted average of all available inventory under that Product ID Name as of the date of sale.

Reference Table: Running Balance

This table is for reference only and shows running balances of Quantity and Inventory Value by Product ID Name, based on all purchase and sales transactions in order of date.

Date: Transaction date of the purchase (addition) or sale (subtraction) of inventory stock.

Product ID Name: Type of inventory stock of the transaction.

Transaction Quantity: Shows whether the transaction increased the inventory balance (positive) or decreased the balance (negative).

Running QTY: Shows the running inventory stock quantity balance following the transaction for that Product ID Name.

FIFO Running Inventory: Shows the running value of inventory stock following the transaction for that Product ID Name under the First-In, First-Out method of Cost of Goods Sold valuation.

WA Running Inventory: Shows the running value of inventory stock following the transaction for that Product ID Name under the Weighted Average method of Cost of Goods Sold valuation.

Reference Table: Ending Balance

This table is for reference only and shows the final ending balances of Quantity and Inventory Value by Product ID Name, based on the latest (most recent) transactions for each unique Product ID Name tracked.

Product ID Name: Type of inventory stock tracked (requires at least one purchase entry).

As of Date: Latest (most recent) transaction date of the inventory type. Can be either a purchase or sale.

Quantity: Shows the latest (most recent) ending inventory stock quantity balance following all transactions for that Product ID Name.

FIFO Inventory: Shows the latest (most recent) ending value of inventory stock following all transactions for that Product ID Name under the First-In, First-Out method of Cost of Goods Sold valuation.

WA Inventory: Shows the latest (most recent) ending value of inventory stock following all transactions for that Product ID Name under the Weighted Average method of Cost of Goods Sold valuation.

 Back to Top