Balance Sheet and Cash Planning
      Back to home
      1. Help Center
      2. Plan
      3. Balance Sheet and Cash Planning

      Inventory Forecasting

      This article walks through the steps to setting up an inventory forecast using either the DIO or Inventory Turnover option.

      Option 1: Days Inventory Outstanding (DIO)

       

      Step 1: Calculate Daily COGS

      Step 2: Set Up the Days Inventory Assumption

      Step 3: Configure Balance Sheet Drivers

      Step 1: Calculate Daily COGS

      1. Navigate to Custom Tables and create a new line called "Inventory COGS".
      2. Add a custom driver for inventory calculation:
        • If all COGS accounts apply, select "COGS - All".
        • If only certain COGS accounts apply, select the relevant COGS accounts or rollups.
        • Multiple drivers may be required depending on the chart of accounts structure.
      3. Create another custom line called "Daily COGS".
      4. Add a custom driver with the formula:
        • Inventory COGS ÷ 30 (to calculate daily values).

      Step 2: Set Up the Days Inventory Assumption

      1. Navigate to Settings > Assumptions.
      2. Create a new assumption account called "Days Inventory".
      3. If Days Inventory needs to vary by month: Instead of an assumption account, create a custom table line to enter values manually.

      Step 3: Configure Balance Sheet Drivers

      1. Navigate to the Drivers Button on the right panel.
      2. Scroll down to the Balance Sheet section.
      3. This method requires two drivers to forecast inventory balances:

      Driver 1: Increase Driver

      • Multiplies Daily COGS by the Days Inventory Assumption to calculate the inventory balance for each forecasted month.

      Driver 2: Decrease Driver

      • Reduces the Inventory Account by the prior month's balance to ensure accurate month-to-month forecasting.

      Option 2: Inventory Turnover Ratio

       

      Step 1: Calculate Inventory Turnover Ratio

      Step 2: Configure Balance Sheet Drivers

      Step 1: Calculate Inventory Turnover Ratio

      • Create a custom table line named "Inventory Turnover Ratio".
      • Add a Global Driver with the formula:
        • COGS ÷ Average Inventory (between last month and this month).
      • Apply this driver to actuals using a date range from min date to cutover date.
      • Create a Periodic Growth Driver in the same table to calculate a trailing average for the Inventory Turnover Ratio.
        • A longer range (e.g., 12 months) smooths out fluctuations.
        • A shorter range (e.g., 3 months) captures more recent trends.

      Step 2: Configure Balance Sheet Drivers

      • Navigate to the Drivers Button > Balance Sheet Section.
      • Since this methodology targets a monthly balance, two drivers are required:

      Driver 1: Increase Driver

      • Multiplies the trended Inventory Turnover Ratio by the COGS for the current month.
      • Ensure the correct COGS rollups are selected.
      • Adjust for any chart of accounts changes or use multiple drivers if necessary.

      Driver 2: Decrease Driver

      • Reduces the Inventory Account by the prior month's balance to maintain an accurate forecasted balance.