Compare two sales forecasting methods:

·

**Static Forecast:** Orders placed based on a single, initial forecast for the entire season.

·

**Dynamic Forecast:** Orders placed for a short initial period, then updated based on actual sales and used for the remaining season.

**Data Setup:**

1.

**Products:** Select 5-10 products (P1, P2, P3, P4, P5).

2.

**Initial Forecast:** Enter an initial sales forecast unit for each product. For example, 200 units for P1. 100 units for P2, 250 units for P3……

3.

**Sales Period:** Set the sales period to 90 days

4.

**Daily Sales:** Calculate daily sales for each product by dividing the forecast by the sales period. Example, P1= 200/90= 2.22 units per day.

5.

**Initial Order:** Specify the initial order quantity for the first 20 days. E.g. 44 units for P1

6.

**Sales Monitoring Period:** Define the sales monitoring period as 14 days

7.

**Costs & Prices:** Set a cost price for each product assuming a 63% gross margin

**Dynamic Forecast Simulation:**

1.

**Actual Sales:** Enter actual daily sales for each product during the monitoring period (14 days).

2.

**Updated Forecast:** Based on actual sales data, revise the initial forecast for the remaining period (Days 21-90). This can be done using various methods like historical averages, trend analysis, or simply adjusting based on observed trends. Update the forecast.

3.

**Remaining Order:** Calculate the remaining order quantity for each product by subtracting the initial order from the updated forecast for the remaining period.

**Static Forecast Simulation:**

1.

**Lost Sales/Excess Inventory:** In a separate section, track lost sales (opportunities missed due to insufficient stock) or excess inventory (unsold units) for the static forecast scenario based on the initial forecast and actual sales data.

**Analysis:**

1.

**Sales Revenue:** Calculate sales revenue for both scenarios by multiplying the actual daily sales (Dynamic) or forecasted daily sales (Static) by the selling price.

2.

**Profit:** For the dynamic scenario, calculate profit by subtracting the cost price from the sales revenue

3.

**Leftover Stock:** Calculate leftover inventory for the dynamic scenario by subtracting actual sales from the total received quantity (initial order + remaining order).

4.

**Markdown Revenue:** If there’s leftover stock, calculate markdown revenue by multiplying the leftover quantity by a markdown percentage (e.g., 50%).

5.

**Total Revenue:** Calculate the total revenue for the dynamic scenario by summing sales revenue and markdown revenue (if applicable).

**Comparison:**

Compare the total revenue, lost sales/excess inventory, and profit between the two scenarios to assess the effectiveness of the dynamic forecasting approach.