Problem Statement

Demand planning forecasts future demand to optimize inventory, minimizing stockouts and overstocking

Demand Planning: Forecasting and Stock Risk Calculation

Objective

This script is a part of a demand planning tool, designed to forecast sales based on historical data, sales adjustments, and various user inputs. It calculates sales projections and assesses potential stock risks by forecasting sales for different periods (e.g., quarterly, monthly, etc.), factoring in growth rates and inventory levels.

Key Elements of the Forecasting Logic

User Inputs

The function takes several parameters from the user interface (UI), such as dropdown menus, sliders, and input fields:

Period Selection (dropdownContainer4): User selects the period for forecasting (e.g., Quarterly, Monthly, Weekly, Daily).
Forecast Count (forecastCountInput): User specifies how many forecasting periods they want to project.
Growth Adjustment (sliderContainer): User adjusts a percentage to increase or decrease sales projections based on current performance.
Sales Data: Different dropdowns allow users to select:
- Category Sales
- Previous Sales
- Current Sales
- Inventory Levels

Sales Calculation

The core of the function is the calculation of future sales based on the following logic:

Adjusted Sales: Current sales are adjusted by a user-defined percentage (adjustmentPercentage).
Growth Rate: The growth rate is calculated based on the difference between previous and current sales. If there is no previous sales data, the growth rate is set to zero.
Period-Specific Sales: Based on the selected period (Quarterly, Monthly, etc.), the function divides the total sales for the next year into the appropriate number of periods:
- Quarterly: Sales divided by 4
- Monthly: Sales divided by 12
- Weekly: Sales divided by 52
- Daily: Sales divided by 365

Forecast Calculation

Forecast for Each Period: The function then forecasts sales for each selected period (e.g., Q1, Q2, etc.), applying the growth rate for each step. The forecast is generated by multiplying the previous period's forecast by the growth rate.

Conclusion

Stock Risk Calculation

Risk Assessment: Stock risk is calculated by comparing forecasted sales to the available inventory. For each forecasted period, it determines how much of the inventory will be used. If forecasted sales exceed available inventory, it calculates the percentage risk.
Inventory Update: The inventory is reduced by the forecasted sales for each period, providing an updated inventory level for future forecasting periods.

Output: Forecast Table

The function then generates a table using the renderTable function. This table displays:

Forecasted sales for each period
Stock risk (as a percentage of inventory)
Adjusted sales, growth rate, and the difference between current and previous sales

Detailed Breakdown of Returned Data

Category Sales: The specific sales data for the selected category.
Previous Sales: Sales data from the previous period.
Current Sales: Current sales data for the selected period.
Inventory Level: Remaining inventory after accounting for the forecasted sales.
Updated Sales: Sales projections after adjustment.
Growth Rate: The percentage change in sales based on previous and current sales.
Forecast Values: Projected sales for each forecast period (quarter, month, week, day).
Stock Risk: The calculated risk of stock shortages based on the forecasted sales and current inventory levels.
Forecast Percentage: The growth rate as a percentage for each forecast period.
Difference: The difference between current and previous sales, helping to identify how the forecasted sales deviate from past performance.

Key Concepts in Demand Planning

Sales Forecasting: The process of predicting futur

For more information visit our Microsoft app source link https://appsource.microsoft.com/en-gb/product/power-bi-visuals/officesolution1640276900203.demand_plnning_by_office_solution?tab=Overview

Product Detail

Demand Planning

Problem Statement

Demand Planning: Forecasting and Stock Risk Calculation

Objective

Key Elements of the Forecasting Logic

User Inputs

Sales Calculation

Forecast Calculation

Conclusion

Stock Risk Calculation

Output: Forecast Table

Detailed Breakdown of Returned Data

Key Concepts in Demand Planning

Other products

Lorenz Curve Chart

Financial Risk and Stock Risk Calculation

Magic Grid Advanced Analytics ChatGPT Solution

Circular Dendogram

Hanging Rootogram Chart

Spiral Plot Chart

Connected Scatter Plot chart

Custom Slicer