public static VmScenario Build(SmScenario entity) { return(new VmScenario { ScenarioId = entity.ScenarioId, Week = entity.Week, ScheduleWeekMin = entity.ScheduleWeekMin, ScheduleWeekMax = entity.ScheduleWeekMax, ScheduleStageMin = entity.ScheduleStageMin, ScheduleStageMax = entity.ScheduleStageMax, StageMax = entity.StageMax, StageOffsetMax = entity.StageOffsetMax, PriceFloor = entity.PriceFloor, CustomerId = entity.OrganisationId, ScenarioName = entity.ScenarioName, ScheduleMask = entity.ScheduleMask, MarkdownCountStartWeek = entity.MarkdownCountStartWeek, DefaultMarkdownType = entity.DefaultMarkdownType, AllowPromoAsMarkdown = entity.AllowPromoAsMarkdown, MinimumPromoPercentage = entity.MinimumPromoPercentage }); }
public async Task<int> Create(string name, int week, int scenarioMask, int senarioWeekMin, int scenarioWeekMax, int markdownCountStartWeek, int defaultMarkdownType, DecisionState defaultDecisionState, bool allowPromoAsMarkdown, decimal minimumPromoPercentage, int organisationId, int userId, List<int> productIds, List<int> hierarchyIds) { var scenario = SmScenario.Build(name, week, scenarioMask, senarioWeekMin, scenarioWeekMax, markdownCountStartWeek, defaultMarkdownType, defaultDecisionState, allowPromoAsMarkdown, minimumPromoPercentage, organisationId,userId ); var scenarioId = await _scenarioRepository.CreateOrUpdate(scenario.Week, scenario.ScheduleWeekMin, scenario.ScheduleWeekMax, scenario.ScheduleStageMin, scenario.ScheduleStageMax, scenario.StageMax, scenario.StageOffsetMax, scenario.PriceFloor, scenario.OrganisationId,scenario.CreatedBy, scenario.ScenarioName, scenario.ScheduleMask, scenario.MarkdownCountStartWeek, scenario.DefaultMarkdownType, scenario.DefaultDecisionState); if (hierarchyIds.Any()) { var filters = hierarchyIds .Select(x => new ScenarioHierarchyFilter {HierarchyId = x, ScenarioId = scenarioId}) .ToList(); await _scenarioHierarchyFilterRepository.Create(filters); } if (productIds.Any()) { var products = productIds .Select(x => new ScenarioProductFilter { ProductId = x, ScenarioId = scenarioId }) .ToList(); await _scenarioProductFilterRepository.Create(products); } return scenarioId; }
public SmCalcProduct Calculate( SmScenario scenario, int modelId, int revisionId, List <SmDenseSchedule> schedules, Dictionary <int, SmDecayHierarchy> decayHierarchies, Dictionary <int, SmElasticityHierarchy> elasticityHierarchies, SmProduct product, List <decimal> revisedDiscounts = null, CancellationToken cancellationToken = default(CancellationToken)) { // Calculate the current markdown depth and ladder depth var depth = GetDiscountLadderDepth(product); var result = new SmCalcProduct(scenario, modelId, product, schedules, depth); // Return OK if there are no schedules for this product if (!schedules.Any()) { _logger.Warning("No schedules for {ProductId}", product.ProductId); return(result.Ok(ProductState.NoSchedules)); } // Return Fatal recommendation for bad CSPs if (product.CurrentSellingPrice <= 0) { _logger.Warning("Product {ProductId} CSP is <= 0", product.ProductId); return(result.Fatal(ProductState.InvalidCsp)); } // Return Fatal if we can't resolve the decay hierarchy if (!decayHierarchies.TryGetValue(product.HierarchyId, out SmDecayHierarchy decayHierarchy)) { _logger.Warning("Can't find Decay Hierarchy by HierarchyId {HierarchyId} for Product {ProductId}", product.HierarchyId, product.ProductId); return(result.Fatal(ProductState.InvalidDecayHierarchy)); } // Return Fatal if we can't resolve the elasticity hierarchy if (!elasticityHierarchies.TryGetValue(product.HierarchyId, out SmElasticityHierarchy elasticityHierarchy)) { _logger.Warning("Can't find Elasticity Hierarchy by HierarchyId {HierarchyId} for Product {ProductId}", product.HierarchyId, product.ProductId); return(result.Fatal(ProductState.InvalidElasticityHierarchy)); } var recommendations = new List <SmCalcRecommendation>(); foreach (var schedule in schedules) { // Apply the product mask to this schedule and skip of not aligned if (product.ProductScheduleMask != null && (schedule.ScheduleNumber & product.ProductScheduleMask.Value) != 0) { result.ScheduleProductMaskFilterCount++; continue; } // Skip products where the max markdown is exceeded if (product.ProductMaxMarkdown != null && product.ProductMaxMarkdown.Value >= schedule.MarkdownCount) { result.ScheduleMaxMarkdownFilterCount++; continue; } if (product.ProductHasExceededFlowlineThreshold == 1) { result.ScheduleExceededFlowlineThresholdFilterCount++; continue; } var crossProduct = GetCrossProduct(schedule, product.PriceLadder); result.ScheduleCrossProductCount = crossProduct.Length; foreach (var path in crossProduct) { var prices = path.Prices.Select(x => x ?? 0).ToArray(); var recommendation = new SmCalcRecommendation(scenario, schedule, prices, revisionId); var calculateResult = CalculatePricePath( product: ref result, recommendation: ref recommendation, scenario: scenario, modelId: modelId, revisionId: revisionId, decayHierarchy: decayHierarchy, elasticityHierarchy: elasticityHierarchy, priceLadder: product.PriceLadder, scheduleId: schedule.ScheduleNumber, schedulePricePath: path); if (calculateResult) { // Get top 10 by Total Revenue recommendations.InsertAfter(recommendation, 10, x => recommendation.TotalRevenue > x.TotalRevenue); } cancellationToken.ThrowIfCancellationRequested(); } } // For initial runs, calculate CSP (where revision id = 0) if (revisionId == 0) { var noChangeSchedule = SmDenseSchedule.NoMarkdowns(scenario.ScheduleWeekMin, scenario.ScheduleWeekMax); var cspPricePath = SmSchedulePricePath.Build(noChangeSchedule.WeekMin, noChangeSchedule.WeekMax, noChangeSchedule.MarkdownWeeks, product.PriceLadder.Type, product.PriceLadder.Values); var prices = cspPricePath.Prices.Select(x => x ?? 0).ToArray(); var cspRecommendation = new SmCalcRecommendation(scenario, noChangeSchedule, prices, revisionId, isCsp: true); var cspResult = CalculatePricePath( product: ref result, recommendation: ref cspRecommendation, scenario: scenario, modelId: modelId, revisionId: revisionId, decayHierarchy: decayHierarchy, elasticityHierarchy: elasticityHierarchy, priceLadder: product.PriceLadder, scheduleId: noChangeSchedule.ScheduleNumber, schedulePricePath: cspPricePath); if (cspResult) { recommendations.InsertAfter(cspRecommendation, recommendations.Count + 1, x => cspRecommendation.TotalRevenue > x.TotalRevenue, true); } result.ScheduleCount++; } if (!recommendations.Any()) { _logger.Warning("No recommendations made for {ProductId}", product.ProductId); return(result.Ok()); } // Set rank, store and set stats var results = new List <SmCalcRecommendation>(); foreach (var ordered in recommendations.Select((x, i) => new { recomendation = x, index = i })) { var recommendation = ordered.recomendation; recommendation.Rank = recommendations.Count - ordered.index; results.Add(recommendation); } ExpandProjectionWeeks(ref result, ref results, scenario, revisedDiscounts); return(result.Ok(results)); }
public bool CalculatePricePath( ref SmCalcProduct product, ref SmCalcRecommendation recommendation, SmScenario scenario, int modelId, int revisionId, SmDecayHierarchy decayHierarchy, SmElasticityHierarchy elasticityHierarchy, SmProductPriceLadder priceLadder, int scheduleId, SmSchedulePricePath schedulePricePath) { var scheduleStageMax = scenario.ScheduleStageMax; var currentMarkdownType = product.CurrentMarkdownType; var accumulatedMarkdownCount = product.CurrentMarkdownCount; var totalRevenue = 0M; var totalCost = 0M; var totalMarkdownCost = 0M; var accumulatedMarkdownCountOffset = 0; var previousPrice = product.CurrentSellingPrice; var previousQuantity = product.CurrentSalesQuantity; var accumulatedStockChange = 0; var projections = new List <SmCalcRecommendationProjection>(); var weeks = schedulePricePath.Weeks; var firstWeek = schedulePricePath.Weeks[0]; var pricePath = schedulePricePath.Prices; var salesFlexFactors = product.SalesFlexFactor; for (var week = 0; week < weeks.Length; week++) { // TODO write out price change var priceChange = pricePath[week]; var flexFactor = salesFlexFactors[week]; var weekMarkdownConstraint = product.MarkdownTypeConstraint[week]; var weekMinimumRelativePercentagePriceChange = product.MinimumRelativePercentagePriceChange[week]; var weekMinDiscountNew = product.MinDiscountsNew[week]; var weekMinDiscountFurther = product.MinDiscountsFurther[week]; var weekMaxDiscountNew = product.MaxDiscountsNew[week]; var weekMaxDiscountFurther = product.MaxDiscountsFurther[week]; var price = product.CurrentSellingPrice; var currentCostPrice = product.CurrentCostPrice; if (priceChange != null) { switch (priceLadder.Type) { case SmPriceLadderType.Percent: price = product.OriginalSellingPrice * (1 - priceChange.Value); break; case SmPriceLadderType.Fixed: price = product.OriginalSellingPrice - (1 - priceChange.Value); break; default: throw new ArgumentOutOfRangeException(); } } if (price > product.CurrentSellingPrice) { product.HighPredictionCount++; return(false); } // A change in price advances stage and resets var elasticity = 0.0M; if (previousPrice != price) { accumulatedMarkdownCount++; accumulatedMarkdownCountOffset = 0; var absolutePriceChange = previousPrice - price; var relativePriceChange = (previousPrice - price) / previousPrice; if (absolutePriceChange < product.MinimumAbsolutePriceChange) { product.MinimumAbsolutePriceChangeNotMetCount++; return(false); } if (absolutePriceChange < product.MinimumAbsolutePriceChange) { product.MinimumAbsolutePriceChangeNotMetCount++; return(false); } if (relativePriceChange < weekMinimumRelativePercentagePriceChange) { product.MinimumRelativePercentagePriceChangeNotMetCount++; return(false); } currentMarkdownType = (accumulatedMarkdownCount == 1) ? MarkdownType.New : MarkdownType.Further; if (!weekMarkdownConstraint.HasFlag(currentMarkdownType)) { product.InvalidMarkdownTypeCount++; return(false); } switch (currentMarkdownType) { case MarkdownType.New: if (weekMinDiscountNew > priceChange || weekMaxDiscountNew < priceChange) { product.DiscountPercentageOutsideAllowedRangeCount++; return(false); } else { break; } case MarkdownType.Further: if (weekMinDiscountFurther > priceChange || weekMaxDiscountFurther < priceChange) { product.DiscountPercentageOutsideAllowedRangeCount++; return(false); } else { break; } default: throw new ArgumentOutOfRangeException();; } // Use the elasticity for this calculation if (accumulatedMarkdownCount > 0 && accumulatedMarkdownCountOffset == 0) { var elasticityStage = Math.Min(accumulatedMarkdownCount, Math.Min(elasticityHierarchy.MaxStage, scheduleStageMax)); elasticity = elasticityHierarchy.TryGetValue(elasticityStage, out SmElasticity e) ? e.PriceElasticity : 1.0M; } } else { currentMarkdownType = accumulatedMarkdownCount == 0 && product.CurrentSellingPrice >= product.OriginalSellingPrice ? MarkdownType.FullPrice : MarkdownType.Existing; if (!weekMarkdownConstraint.HasFlag(currentMarkdownType)) { product.InvalidMarkdownTypeCount++; return(false); } } // Get decay var decay = 1.0M; if (accumulatedMarkdownCountOffset > 0) { var decayStage = Math.Min(accumulatedMarkdownCount, Math.Min(decayHierarchy.MaxStage, scheduleStageMax)); decay = decayHierarchy.TryGetValue(decayStage, accumulatedMarkdownCountOffset, out SmDecay d) ? d.Decay : 1.0M; } // Calculate the predicted quantity sold var predictedQuantity = (int)Math.Round(accumulatedMarkdownCount == product.CurrentMarkdownCount ? previousQuantity * decay : previousQuantity * decay * (1 - (((previousPrice - price) / previousPrice) * elasticity))); // Calculate projected stock var stock = Math.Max(product.CurrentStock - accumulatedStockChange, 0); // Ensure predicted quantity sold is non-negative var adjustedQuantity = Math.Max(predictedQuantity, 0) * flexFactor; // Ensure predicted quantity sold is not more than available stock var quantity = Math.Min(stock, adjustedQuantity); // Calculate Metrics var revenue = price * quantity; var cost = currentCostPrice * quantity; var markdownCost = (previousPrice - price) * stock; projections.Add(new SmCalcRecommendationProjection { Week = firstWeek + week, Discount = priceChange ?? product.CurrentMarkdownDepth, Price = price, Quantity = (int)quantity, Revenue = revenue, Stock = stock, MarkdownCost = markdownCost, AccumulatedMarkdownCount = accumulatedMarkdownCount, MarkdownCount = accumulatedMarkdownCount - product.CurrentMarkdownCount, Decay = decay, Elasticity = elasticity, MarkdownType = currentMarkdownType }); totalRevenue += revenue; totalCost += cost; totalMarkdownCost += markdownCost; previousPrice = price; previousQuantity = (int)quantity; accumulatedStockChange += (int)quantity; accumulatedMarkdownCountOffset++; // TODO test accumulatedStockChange > CurrentStock if (totalRevenue < 0) { product.NegativeRevenueCount++; return(false); } } var terminalStock = Math.Max((product.CurrentStock - accumulatedStockChange), 0); var sellThroughTarget = product.CurrentStock - (product.CurrentStock * product.SellThroughTarget); var sellThroughTerminalStock = terminalStock == 0 ? sellThroughTarget : terminalStock; var sellThroughRate = product.SellThroughTarget > 0 ? (sellThroughTarget / sellThroughTerminalStock) : 0; // Assign values recommendation.PricePath = pricePath.Select(x => x ?? 0).ToArray(); recommendation.IsCsp = (schedulePricePath.MarkdownCount == 0); recommendation.TotalMarkdownCount = accumulatedMarkdownCount; recommendation.TotalRevenue = totalRevenue; recommendation.TotalCost = totalCost; recommendation.TotalMarkdownCost = totalMarkdownCost; recommendation.FinalDiscount = projections.Any() ? projections.Last().Discount : 0; recommendation.StockValue = product.CurrentSellingPrice * product.CurrentStock; recommendation.EstimatedProfit = totalRevenue - totalCost; recommendation.EstimatedSales = accumulatedStockChange; recommendation.TerminalStock = terminalStock; recommendation.SellThroughRate = sellThroughRate; recommendation.SellThroughTarget = sellThroughTarget; recommendation.FinalMarkdownType = currentMarkdownType; recommendation.Projections = projections; return(true); }
public void ExpandProjectionWeeks(ref SmCalcProduct product, ref List <SmCalcRecommendation> recommendations, SmScenario scenario, List <decimal> revisedDiscounts) { // Calculate week period var scheduleMask = scenario.ScheduleMask; var firstWeek = scenario.ScheduleWeekMin; var lastWeek = scenario.ScheduleWeekMax; var weekCount = lastWeek - firstWeek + 1; // Skip if we already have n weeks var sparseRecommendations = recommendations .Where(x => x.Projections.Count < weekCount) .ToList(); if (!sparseRecommendations.Any()) { return; } // Create a week map var markdownIndex = 0; var weeks = Enumerable.Range(firstWeek, weekCount) .Select((x, i) => { var bit = x - firstWeek; var isMarkdown = ((1 << bit) & scheduleMask) != 0; return(new { Week = x, WeekIndex = i, IsMarkdown = isMarkdown, MarkdownIndex = isMarkdown ? markdownIndex++ : (int?)null }); }) .ToList(); // Foreach sparse recommendation append missing weeks foreach (var recommendation in sparseRecommendations) { var projections = recommendation.Projections; var originalSellingPrice = product.OriginalSellingPrice; var currentMarkdownDepth = product.CurrentMarkdownDepth; var trailingWeeks = weeks .Skip(projections.Count) .ToList(); var lastProjection = projections.LastOrDefault(); var trailingProjections = new List <SmCalcRecommendationProjection>(); var lastDiscount = projections.Any() ? lastProjection.Discount : currentMarkdownDepth; var previousDiscount = lastDiscount; var previousPrice = originalSellingPrice * (1 - previousDiscount); foreach (var item in trailingWeeks) { var discount = lastDiscount; var markdownCount = projections.Any() ? lastProjection.MarkdownCount : 0; var accumulatedMarkdownCount = projections.Any() ? lastProjection.AccumulatedMarkdownCount : 0; var stock = projections.Any() ? lastProjection.Stock : 0; if (revisedDiscounts != null && item.MarkdownIndex != null && item.IsMarkdown && item.MarkdownIndex <= revisedDiscounts.Count - 1) { discount = revisedDiscounts[(int)item.MarkdownIndex]; } var price = originalSellingPrice * (1 - discount); var projection = new SmCalcRecommendationProjection { Week = item.Week, Discount = discount, Price = price, MarkdownCount = markdownCount, AccumulatedMarkdownCount = accumulatedMarkdownCount, Quantity = 0, Revenue = 0, MarkdownCost = (price - previousPrice) * stock, Stock = stock, Decay = 0, Elasticity = 0 }; previousDiscount = discount; previousPrice = originalSellingPrice * (1 - previousDiscount); trailingProjections.Add(projection); lastProjection = projection; } projections.AddRange(trailingProjections); } }
public async Task <Tuple <SmScenario, List <SmProduct> > > GetScenarioData(IMarkdownFunctionSettings settings, int partitionId, IEnumerable <int> specificProductIds = null) { var isOptional = partitionId > 1; // Scenario var scenarioPath = SmS3Path.ScenarioPath(SmS3PathName.Scenario, settings); var scenario = await _s3Repository.ReadRecord <Scenario>(scenarioPath); var sellThroughPath = SmS3Path.ScenarioPath(SmS3PathName.HierarchySellThrough, settings); var sellThrough = await _s3Repository.ReadRecords <HierarchySellThrough>(sellThroughPath); if (!sellThrough.Any()) { _logger.Warning("No sell through targets in partition"); } var priceLadderValuePath = SmS3Path.ScenarioPath(SmS3PathName.PriceLadderValue, settings); var priceLadderValue = await _s3Repository.ReadRecords <PriceLadderValue>(priceLadderValuePath); if (!priceLadderValue.Any()) { _logger.Error("No price ladder values in partition"); } // Product var productPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.Product, settings); var product = await _s3Repository.ReadRecords <Product>(productPath, isOptional); if (specificProductIds != null) { product = product.Where(x => specificProductIds.Contains(x.ProductId)).ToList(); } if (!product.Any()) { _logger.Error("No products in partition"); } var productHierarchyPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductHierarchy, settings); var productHierarchy = await _s3Repository.ReadRecords <ProductHierarchy>(productHierarchyPath, isOptional); if (!productHierarchy.Any()) { _logger.Error("No product hierarchies in partition"); } var productPriceLadderPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductPriceLadder, settings); var productPriceLadder = await _s3Repository.ReadRecords <ProductPriceLadder>(productPriceLadderPath, isOptional); if (!productPriceLadder.Any()) { _logger.Error("No product price ladders in partition"); } var productSalesTaxPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductSalesTax, settings); var productSalesTax = await _s3Repository.ReadRecords <ProductSalesTax>(productSalesTaxPath, isOptional); if (!productSalesTax.Any()) { _logger.Warning("No product sales tax in partition"); } var productParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductParameterValues, settings); var productParameterValues = await _s3Repository.ReadRecords <ProductParameterValues>(productParameterValuesPath, isOptional); if (!productParameterValues.Any()) { _logger.Warning("No product parameter values in partition"); } var productMarkdownConstraintPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductMarkdownConstraint, settings); var productMarkdownConstraint = await _s3Repository.ReadRecords <ProductMarkdownConstraint>(productMarkdownConstraintPath, true); if (!productMarkdownConstraint.Any()) { _logger.Warning("No product markdown constraint values in partition"); } var salesFlexFactorPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.SalesFlexFactor, settings); var salesFlexFactor = await _s3Repository.ReadRecords <ProductSalesFlexFactor>(salesFlexFactorPath, isOptional); if (!salesFlexFactor.Any()) { _logger.Warning("No sales flex factor values in partition"); } var productMinimumAbsolutePriceChangePath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductMinimumAbsolutePriceChange, settings); var productMinimumAbsolutePriceChange = await _s3Repository.ReadRecords <ProductMinimumAbsolutePriceChange>(productMinimumAbsolutePriceChangePath, true); if (!productMinimumAbsolutePriceChange.Any()) { _logger.Warning("No product minimum absolute price change values in partition"); } var productWeekParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductWeekParameterValues, settings); var productWeekParameterValues = await _s3Repository.ReadRecords <ProductWeekParameterValues>(productWeekParameterValuesPath, true); if (!productWeekParameterValues.Any()) { _logger.Warning("No product week parameter values in partition"); } var productWeekMarkdownTypeParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductWeekMarkdownTypeParameterValues, settings); var productWeekMarkdownTypeParameterValues = await _s3Repository.ReadRecords <ProductWeekMarkdownTypeParameterValues>(productWeekMarkdownTypeParameterValuesPath, true); if (!productWeekMarkdownTypeParameterValues.Any()) { _logger.Warning("No product week parameter values in partition"); } // Sell through lookup var sellThroughLookup = sellThrough.Any() ? sellThrough.ToDictionary(x => x.HierarchyId, x => x.Value) : new Dictionary <int, decimal>(); // Sales tax lookup var productSalesTaxLookup = productSalesTax.Any() ? productSalesTax.ToDictionary(x => x.ProductId, x => Tuple.Create(x.Week, x.Rate)) : new Dictionary <int, Tuple <int, decimal> >(); var products = SmProduct.Build(_logger, product, productHierarchy, productPriceLadder, priceLadderValue, sellThroughLookup, productSalesTaxLookup, productParameterValues, productMarkdownConstraint, salesFlexFactor, productMinimumAbsolutePriceChange, productWeekParameterValues, productWeekMarkdownTypeParameterValues); return(Tuple.Create(SmScenario.Build(scenario), products)); }