Three Part Blog. Part 1 – Stock Movement Monthly Report
The following is a response to a customer question after attending out CMS Road Show in Denver, CO.
How do I get to these three reports?
1) A report of all our stock items, and how many of each we sold last year.
2) A list of what we have in stock, with quantity.
3) A report on items where our stock is low, according to the limits we have previously set.
This blog is going to cover report number 1.
1) Stock Movement Monthly – A report of all our stock items, and how many of each we sold last year.
The Stock Movement Monthly Report is very long so select a product(s) that you want to see, or Inventory group and then run the report. It does 5 calculations for you:
Monthly Movement Report Calculations. The Monthly Movement Report provides valuable information regarding your inventory movement. It is the basis and calculation used to obtain the figure information in each row of the report. We recommend you review this information closely to ensure you understand how each figure is determined before planning any future purchasing or category management options.
When reviewing, keep in mind that inventory costs are determined at the time of purchase and do not reflect adjustments made afterwards. In addition, sales revenue is based on the sale price of the inventory item at the time of sale.
Beginning Qty: For Period 1 this row shows the sum of the quantities of any subtractions made on or after the first day of the period added to the on-hand quantity. This total has the sum of the quantities of any additions made on or after the first day of the period subtracted from it to obtain the beginning quantity.
If this seems confusing, here is a formula that explains it further.
On-Hand Qty + (Sum of Subtraction Quantities) – (Sum of Addition Quantities) = Beginning Qty.
For subsequent periods, beginning quantity equals the ending quantity of the previous period. The YTD Totals column reflects the beginning quantity for the first period included in the report.
Qty In: For each period, this row shows the quantity of inventory received for the month from Accounts Payable, Job Cost, and Purchase Order transactions. This field also includes returns posted from Order Entry and Point of Sale. The YTD
Qty Sold: For each period, this row shows the monthly per-item quantity sold in Order Entry, Point of Sale, and Job Cost. This field also includes returns posted from Order Entry and Point of Sale. The YTD column reflects the sum of the quantity sold for each period included in the report.
Add Inventory: For each period, this row shows the sum of all posted adjustment transactions from Inventory with a Transaction Code that begins with 1. The YTD
Totals column reflects the sum of all inventory addition adjustments for each period included in the report.
Subtract Inventory: For each period, this row shows the sum of all posted adjustment transactions from Inventory with a Transaction Code that begins with 2. The YTD Totals column reflects the sum of all inventory subtraction adjustments for each period included in the report.
Transfer In: (This information only shows if the company is using the Multi-Location Inventory add-on.) For each period, this row shows the sum of all posted adjustment transactions from Inventory with a Transaction Code that begins with 3. These transactions represent inventory items that are transferred in from another location. The YTD Totals column reflects the sum of all inventory transfer in adjustments for each period included in the report.
Transfer Out: (This information only shows if the company is using the Multi-Location Inventory add-on.) For each period, this row shows the sum of all posted adjustment transactions from Inventory with a Transaction Code that begins with 3. These transactions represent inventory items that are transferred out to another location. The YTD Totals column reflects the sum of all inventory transfer out adjustments for each period included in the report.
Ending Qty: For each period, this row shows the sum of adding any additional quantities or adjustments and subtracting any quantities sold or subtraction adjustments from the beginning quantity for each item. If Multi-Location Inventory is used by the company, the beginning quantity is also adjusted by adding or subtracting the transfer quantity. The formula for this calculation is:
Beginning Qty + (Qty In – Qty Sold ) + (Add Inventory – Subtract Inventory ) + (Transfer In – Transfer Out).
The YTD Totals column reflects the ending quantity for the last period included in the report.
Movement %: For each period, this row shows the calculated percentage based on the following formulas:
- If the Beginning Qty + Qty In + Transfer In = 0, then 0% is shown.
- If the Beginning Qty is less than zero, then zero is used as the Beginning Qty in the calculation.
- If the Include Adjustments/Transfers in Movement % check box is unchecked, the Qty Sold / (Beginning Qty + Qty In) is shown.
- If the Include Adjustments/Transfers in Movement % check box is checked, the (Qty Sold + Subtract Inventory + Transfers Out) / (Beginning Qty + Qty In + Add Inventory + Transfers In) is shown.
The transfers in and out are only included in the calculation if Multi-Location Inventory is used. The YTD Totals column reflects the movement percentage for each period included in the report.
Value: Qty In: For each period, this row shows the sum of the cost of the inventory items received in Accounts Payable, Purchase Order, and Job Cost transactions. This sum provides the inflow and outflow of cash. The YTD Totals column reflects the sum of the cost of inventory items received for each period included in the report.
COGS: For each period, this row shows the direct expenses of inventory items that were sold to customers (commonly referred to as cost of goods sold or cost of sales) in Order Entry, Point of Sale, or Job Cost transactions. Direct expenses include the actual cost of inventory items (goods) and direct labor expenses in creating the salable goods. Cost of goods does not include any indirect expenses (i.e. office, shipping, or advertising expenses) that cannot be attributed to a particular item sold. The YTD Totals column reflects the sum of the cost of goods sold for each period included in the report.
COGS Per Unit: For each period, this row shows the average cost to make or obtain the inventory item. This average is derived using the formula: COGS / Value of Qty Sold.
Price Per Unit: For each period, this row shows the average sale price per inventory item. This average is derived using the formula: Monthly Sales Revenue / Value of Qty Sold. The YTD Totals column reflects the YTD Sales Revenue / YTD Value of Qty Sold.
Sales Revenue: For each period, this row shows the sum of the cash amounts received for each inventory item sold in Order Entry, Point of Sale, or Job Cost. This figure reflects the actual amount including promotions or discounts. The YTD Totals column reflects the sum of sales revenue for each period included in the report.
IN Turnover: For each period, this row shows the turnover ratio which is derived using the formula: COGS / ((((Beginning Qty + Ending Qty) / 2) * COGS Per Unit) / 12).
The YTD Totals column use the formula: COGS / (((Beginning Qty + Ending Qty) / 2) * COGS Per Unit).
- If COGS = 0, then IN Turnover = 0.
- If the Beginning and Ending quantities are zero, a dash is displayed in this field.
- Gross Profit %: For each period, this row shows the gross profit for the inventory item. This percentage is derived from the formula: ((Sales Revenue – COGS) /(Sales Revenue) * 100).
- If the Sales Revenue is equal to zero, then the percentage shows 0.
GMROI: For each period, this row shows the gross margin return on inventory which is derived using the formula: Markup % * Inventory Turnover. The Markup % figure is derived using the formula: (Sales Revenue – COGS) / COGS. If COGS = 0, then GMROI = 0.
Check back with the CMS blog to find out about the other 2 reports:
2) Stock Value – A list of what we have in stock, with quantity.
3) Understock – A report on items where our stock is low, according to the limits we have previously set.
Leave a Comment