Inventory Forecasting & Planning Report - User Guide

Overview

This report provides a complete view of inventory forecasting, demand planning, cost analysis, and reorder recommendations. It combines your historical sales data, current inventory levels, cost information, and production planning to deliver actionable insights for smart inventory management.

🏷️ Basic Product Information

`Product ID`

Business Meaning: Unique identifier for each product
Calculation: System-generated product identifier
Use Case: Primary reference for joining with other systems and reports

`Product Name`

Business Meaning: Human-readable product name
Calculation: Product master data
Use Case: Display and identification purposes in reports and dashboards

`Internal SKU`

Business Meaning: Your internal SKU/part number for the product
Calculation: Product master data
Use Case: Inventory tracking, warehouse operations, and system integration

`Product Type`

Business Meaning: Product classification (Simple Product, Kit, etc.)
Calculation: Product master data classification
Use Case: Different handling logic for kits versus simple products

💰 Cost Analysis Columns

`Current Average Cost`

Business Meaning: The current average cost per unit of this product in your inventory
Calculation: Product's weighted average cost from inventory layers, or falls back to average cost from last 90 days of sales
Use Case: Accurate inventory valuation and margin calculations
Decision Impact: Helps determine true profitability and pricing strategies

`Total Inventory Value`

Business Meaning: Total dollar value of all current inventory for this product
Calculation: Sum of (remaining inventory quantity × cost) across all inventory batches
Use Case: Understanding capital allocation and investment per product
Decision Impact: Identifies which products tie up the most cash

`Recent Average COGS`

Business Meaning: Average cost of goods sold over the last 30 days
Calculation: Average cost from actual sales transactions in past 30 days
Use Case: Recent cost trend analysis and COGS validation
Decision Impact: Shows if your actual costs match your inventory cost assumptions

`Latest COGS`

Business Meaning: The most recent cost when this product was last sold
Calculation: Cost from the most recent sale transaction
Use Case: Spot current cost trends and identify cost changes
Decision Impact: Indicates immediate cost direction for purchasing decisions

`Cost Complexity`

Business Meaning: Average number of different cost layers per warehouse
Calculation: Average count of active cost batches across warehouses
Use Case: Identifying products with complex cost management
Decision Impact: High complexity may indicate need for simplified sourcing

💼 Financial Metrics

`Inventory Value`

Business Meaning: Current cash investment in on-hand inventory
Calculation: Current stock quantity × Current average cost
Use Case: Cash flow analysis and working capital management
Decision Impact: Prioritizes which products to focus on for capital efficiency

`Reorder Value Required`

Business Meaning: Cash needed for next reorder based on lead time + safety stock
Calculation: 30-day daily sales × cost × (lead time days + 20 safety days)
Use Case: Cash flow planning and budget allocation
Decision Impact: Helps plan purchasing budgets and cash requirements

`Annual Stock Turn`

Business Meaning: How many times per year you sell through your inventory investment
Calculation: (Annual sales cost ÷ Average inventory value) × 4
Use Case: Efficiency measurement of working capital usage
Decision Impact: Higher turns = better capital efficiency; target varies by industry

`Days of Cash Tied Up`

Business Meaning: How many days of sales your current inventory investment represents
Calculation: Inventory value ÷ Daily sales value (based on 30-day average)
Use Case: Liquidity analysis and cash flow optimization
Decision Impact: Lower is generally better for cash flow; balance with service levels

📊 Risk Assessment Columns

`COGS Variance`

Business Meaning: Statistical measure of how much your product costs fluctuate over time
Calculation: Standard deviation of all historical cost data from sales
Use Case: Risk assessment for cost planning and budgeting
Decision Impact: High variance = unpredictable costs requiring larger safety margins

`COGS Variance Percent`

Business Meaning: Cost volatility as percentage of average cost
Calculation: (Cost standard deviation ÷ Average historical cost) × 100
Use Case: Relative risk comparison across products
Decision Impact: 10% = stable, 50%+ = very volatile requiring risk management

`Cost Inflation Trend`

Business Meaning: Whether costs are rising or falling recently (30-day vs 90-day comparison)
Calculation: ((30-day average cost - 90-day average cost) ÷ 90-day average cost) × 100
Use Case: Timing purchasing decisions based on cost trends
Decision Impact: Positive = costs rising (order sooner), Negative = costs falling (delay orders)

`Obsolescence Risk Value`

Business Meaning: Dollar value at risk if slow-moving inventory becomes obsolete
Calculation: If inventory will last more than 365 days at current sales rate, shows total inventory value, otherwise zero
Use Case: Identifying products with high obsolescence risk
Decision Impact: Prioritizes which slow movers to liquidate or discount first

📦 Current Inventory Levels

`Quantity On Hand`

Business Meaning: Physical inventory currently in your warehouses
Calculation: Sum of physical inventory across all warehouse locations
Use Case: Immediate availability for order fulfillment
Decision Impact: Base level for all availability calculations

`Quantity Incoming`

Business Meaning: Inventory on purchase orders that will arrive soon
Calculation: Sum of all outstanding purchase order quantities
Use Case: Near-term availability planning
Decision Impact: Affects timing of next purchase orders

`Quantity Pending`

Business Meaning: Inventory already allocated to customer orders
Calculation: Sum of all allocated/reserved quantities for pending orders
Use Case: Understanding what's truly available vs just on hand
Decision Impact: Critical for accurate availability promises to customers

📈 Historical Sales Data (Quantity)

`Sales Within 7 Days`

Business Meaning: Total units sold in the last 7 days
Calculation: Sum of all sales quantities where order date falls within last 7 days
Use Case: Recent demand pattern analysis and short-term trending
Decision Impact: Useful for identifying sudden demand changes or promotions impact

`Sales Within 14 Days`

Business Meaning: Total units sold in the last 14 days
Calculation: Sum of all sales quantities where order date falls within last 14 days
Use Case: Short-term demand pattern with slightly more stability than 7-day
Decision Impact: Good for weekly/bi-weekly ordering cycles

`Sales Within 30 Days`

Business Meaning: Total units sold in the last 30 days
Calculation: Sum of all sales quantities where order date falls within last 30 days
Use Case: Monthly demand analysis and trend identification
Decision Impact: Common baseline for reorder point calculations

`Sales Within 45 Days`

Business Meaning: Total units sold in the last 45 days
Calculation: Sum of all sales quantities where order date falls within last 45 days
Use Case: Intermediate-term demand analysis
Decision Impact: Useful for products with 6-8 week seasonal patterns

`Sales Within 60 Days`

Business Meaning: Total units sold in the last 60 days
Calculation: Sum of all sales quantities where order date falls within last 60 days
Use Case: Seasonal trend analysis and medium-term planning
Decision Impact: Good baseline for products with quarterly variations

`Sales Within 90 Days`

Business Meaning: Total units sold in the last 90 days
Calculation: Sum of all sales quantities where order date falls within last 90 days
Use Case: Quarterly demand analysis and seasonal pattern identification
Decision Impact: Useful for annual planning and budgeting

`Sales Within 120 Days`

Business Meaning: Total units sold in the last 120 days
Calculation: Sum of all sales quantities where order date falls within last 120 days
Use Case: Long-term trend analysis and seasonal planning
Decision Impact: Good for identifying annual cycles and long-term trends

⚡ Sales Velocity (Daily Averages)

`Sales Velocity 7 Days`

Business Meaning: Average units sold per day over the last 7 days
Calculation: Total 7-day sales ÷ 7 days
Use Case: Current demand rate for immediate planning
Decision Impact: Most reactive metric for short-term stock-out prevention

`Sales Velocity 14 Days`

Business Meaning: Average units sold per day over the last 14 days
Calculation: Total 14-day sales ÷ 14 days
Use Case: Recent demand rate with less day-to-day volatility
Decision Impact: Good for weekly reorder cycles and short lead times

`Sales Velocity 30 Days`

Business Meaning: Average units sold per day over the last 30 days
Calculation: Total 30-day sales ÷ 30 days
Use Case: Standard demand rate for most planning purposes
Decision Impact: Most commonly used velocity for reorder points and safety stock

`Sales Velocity 45 Days`

Business Meaning: Average units sold per day over the last 45 days
Calculation: Total 45-day sales ÷ 45 days
Use Case: Intermediate-term demand rate smoothing seasonal variations
Decision Impact: Good for products with moderate seasonality

`Sales Velocity 60 Days`

Business Meaning: Average units sold per day over the last 60 days
Calculation: Total 60-day sales ÷ 60 days
Use Case: Medium-term demand rate for seasonal planning
Decision Impact: Primary velocity used in your reorder calculations

`Sales Velocity 90 Days`

Business Meaning: Average units sold per day over the last 90 days
Calculation: Total 90-day sales ÷ 90 days
Use Case: Seasonal demand rate for quarterly planning
Decision Impact: Good for annual budgeting and long-term contracts

`Sales Velocity 120 Days`

Business Meaning: Average units sold per day over the last 120 days
Calculation: Total 120-day sales ÷ 120 days
Use Case: Long-term trend analysis smoothing seasonal variations
Decision Impact: Useful for annual planning and identifying long-term trends

📊 Trend Analysis

`Last 7 Days Trend`

Business Meaning: How recent demand compares to medium-term average
Calculation: (7-day velocity - 60-day velocity) ÷ 60-day velocity
Use Case: Identifying accelerating or declining demand trends
Decision Impact: Positive = accelerating demand, Negative = declining demand

📐 Stock Availability Calculations

`Available and Ordered Quantity`

Business Meaning: Total inventory including what's coming in minus what's already allocated
Calculation: Quantity on hand + Quantity incoming - Quantity pending
Use Case: Total near-term availability including purchase orders
Decision Impact: Shows complete picture of inventory position

`Available Quantity`

Business Meaning: Inventory available for new orders right now
Calculation: Quantity on hand - Quantity pending
Use Case: Immediate order fulfillment capability
Decision Impact: Critical for customer promise dates and stock-out prevention

`Quantity Available`

Business Meaning: Total available including planned production
Calculation: Base available quantity + Planned assembly production quantity
Use Case: Complete availability picture including production pipeline
Decision Impact: Most comprehensive availability metric for planning

📅 Days On Hand Analysis

`Days On Hand (7-Day Velocity)`

Business Meaning: How many days current inventory will last at recent sales pace
Calculation: Available quantity ÷ 7-day daily velocity
Use Case: Immediate stock-out risk assessment
Decision Impact: <7 days = immediate risk, >30 days = probably safe short-term

`Days On Hand (14-Day Velocity)`

Business Meaning: Days of inventory at 14-day average sales rate
Calculation: Available quantity ÷ 14-day daily velocity
Use Case: Short-term inventory planning with less volatility
Decision Impact: Good for weekly ordering cycles

`Days On Hand (30-Day Velocity)`

Business Meaning: Days of inventory at 30-day average sales rate
Calculation: Available quantity ÷ 30-day daily velocity
Use Case: Standard inventory planning metric
Decision Impact: Most common metric for inventory management decisions

`Days On Hand (45-Day Velocity)`

Business Meaning: Days of inventory at 45-day average sales rate
Calculation: Available quantity ÷ 45-day daily velocity
Use Case: Medium-term planning with seasonal smoothing
Decision Impact: Good for quarterly planning cycles

`Days On Hand (60-Day Velocity)`

Business Meaning: Days of inventory at 60-day average sales rate
Calculation: Available quantity ÷ 60-day daily velocity
Use Case: Primary metric used for your reorder timing
Decision Impact: Core metric for most reorder decisions in your system

🏭 Supply Chain Parameters

`Lead Time`

Business Meaning: Days from order placement to inventory receipt
Calculation: Product-specific lead time or system default shipping time
Use Case: Reorder timing and safety stock calculations
Decision Impact: Longer lead times require earlier ordering and higher safety stock

`Minimum Order Quantity (MOQ)`

Business Meaning: Minimum Order Quantity required by supplier
Calculation: Lowest quantity tier from supplier quotation
Use Case: Order quantity planning and cost optimization
Decision Impact: May force larger orders than immediately needed

🎯 Reorder Planning

`Safety Stock`

Business Meaning: Buffer inventory to prevent stock-outs during lead time
Calculation: Currently set to zero (60-day velocity × lead time ÷ 2 × safety factor)
Use Case: Risk management for demand variability
Decision Impact: Higher safety stock = less risk but more capital tied up

`Reorder Point`

Business Meaning: Inventory level that triggers a new purchase order
Calculation: 60-day daily velocity × lead time days
Use Case: Automated reorder triggers and inventory management
Decision Impact: Too high = excess inventory, Too low = stock-outs

📆 Recommended Order Dates

`Recommended Order Date`

Business Meaning: When to place next order based on 60-day velocity
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (60-day)
Use Case: Proactive order timing to prevent stock-outs
Decision Impact: Order before this date to maintain service levels

`Recommended Order Date (14-Day)`

Business Meaning: Order timing based on recent 14-day sales pattern
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (14-day)
Use Case: Reactive ordering based on current demand trends
Decision Impact: More responsive to recent changes but potentially more volatile

`Recommended Order Date (30-Day)`

Business Meaning: Order timing based on 30-day sales pattern
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (30-day)
Use Case: Balanced approach between responsiveness and stability
Decision Impact: Good middle ground for most products

`Recommended Order Date (45-Day)`

Business Meaning: Order timing based on 45-day sales pattern
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (45-day)
Use Case: Moderate smoothing of seasonal variations
Decision Impact: Good for products with known seasonal patterns

`Recommended Order Date (90-Day)`

Business Meaning: Order timing based on 90-day sales pattern
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (90-day)
Use Case: Long-term planning with seasonal smoothing
Decision Impact: More stable but less responsive to recent changes

`Recommended Order Date (120-Day)`

Business Meaning: Order timing based on 120-day sales pattern
Calculation: Today + (Available quantity - Reorder point) ÷ Daily velocity (120-day)
Use Case: Very stable planning for predictable products
Decision Impact: Most conservative approach with least volatility

📦 Order Quantity Recommendations

`Order Quantity - Lead Time + Safety Stock`

Business Meaning: Recommended order quantity to cover lead time plus safety buffer
Calculation: 30-day daily velocity × (lead time days + 20 safety days)
Use Case: Conservative ordering to ensure stock availability
Decision Impact: Higher quantities but lower risk of stock-outs

`30 Day Order Quantity`

Business Meaning: Quantity to cover exactly 30 days of sales
Calculation: 45-day daily velocity × 30 days
Use Case: Frequent ordering with minimal inventory investment
Decision Impact: Lower inventory but requires more frequent orders

`45 Day Order Quantity`

Business Meaning: Quantity to cover exactly 45 days of sales
Calculation: 45-day daily velocity × 45 days
Use Case: Balance between order frequency and inventory investment
Decision Impact: Good for products with moderate lead times

`60 Day Order Quantity`

Business Meaning: Quantity to cover exactly 60 days of sales
Calculation: 30-day daily velocity × 60 days
Use Case: Longer supply cycles with less frequent ordering
Decision Impact: Higher inventory but fewer orders and potentially better pricing

`90 Day Order Quantity`

Business Meaning: Quantity to cover exactly 90 days of sales
Calculation: 90-day daily velocity × 90 days
Use Case: Quarterly ordering cycles and seasonal planning
Decision Impact: Bulk ordering with seasonal considerations

`120 Day Order Quantity`

Business Meaning: Quantity to cover exactly 120 days of sales
Calculation: 120-day daily velocity × 120 days
Use Case: Long-term bulk ordering and annual contracts
Decision Impact: Maximum bulk benefits but highest inventory investment

⚠️ Risk Indicators

`Overstocked`

Business Meaning: Whether current inventory will last more than one year
Calculation: TRUE if recommended order date is more than 1 year from today
Use Case: Identifying products with excess inventory
Decision Impact: "true" indicates potential obsolescence risk requiring action

🏭 Production Integration

`Total Produced Planned Quantity`

Business Meaning: Units expected from active assembly orders
Calculation: Sum of planned production quantities from incomplete assembly orders
Use Case: Including production pipeline in availability planning
Decision Impact: Reduces need for purchased inventory when production is planned