Run a simulation for a company in provided Excel and answer each of the following questions in one paragraph.
Specifically, the company must plan its summer season and choose: |
|
|
1-The desired objective function |
|
|
|
|
|
2- What products to markdown and by how much? |
|
|
|
3- What additional broad promotions, like 3x2 (3 for the price of 2), to apply and when? |
4- If and how much to spend in online marketing, e.g. e-mail and text messages? |
|
Copy of 522710c2 edits_9f22313f-14eb-4992-9460-30c764b4f250.xlsx Copyright PittaRosso: Artificial Intelligence-Driven Pricing and Promotion Harvard Business School Case # 522-046 Courseware # 522-710 REV: March 1, 2022 This courseware was prepared solely as the basis for class discussion. Copyright © 2021, 2022 President and Fellows of Harvard College. To order copies or request permission to reproduce materials, call 1-800-545-7685, write Harvard Business School Publishing, Boston, MA 02163, or go to www.hbsp.harvard.edu/educators. This publication may not be digitized, photocopied, or otherwise reproduced, posted, or transmitted, without the permission of Harvard Business School. Copyright &P Instructions Instructions Introductory overview for users of this supplement This tool This template can be used to simulate different marketing decisions, and how they affect the firm's multiple objectives. Specifically, the company must plan its summer season and choose: -The desired objective function - What products to markdown and by how much? - What additional broad promotions, like 3x2 (3 for the price of 2), to apply and when? - If and how much to spend in online marketing, e.g. e-mail and text messages? Note, that the selection of the objective function is often times the most important decision, as it impacts the enire strategy. The Excel contains simple optimization logic that will re-calculate its choices dynamically based on the user's choice of objectives, constraints and other parameters. This is a simplified model, limited by the capabilities of Excel and designed for specific learning experience, therefore accepting certain approximations. Excel's randomization may lead to different outcomes for the same inputs, which often happens in prediction machines. Users can run the system multiple times and take the average. How to use this tool? General guidelines Input: blue tabs; edit any light-gray background cell. Output: green tabs, read-only. Input (optional): red tabs; edit any light-gray background cell. Strategy tab (blue tab) First point of entry into the model: start here. Input: choice of objective function and basic constraints, entering values only in the empty gray cells. In Section 1 of the tab, the user can manipulate the objective function: using revenue, margin, and the writeoff penalty for older inventory. The user can also use volume sold instead of monetary value. In that case, the objctive function would be maximum volume, regardless of other parameters. Note: The units of the "value of objective function" in E12 are only meaningful if either margin or revenue are set to 100%. Otherwise, it is not interpertable, as it forms a mixed objective function. Whereas the number itself is irrelevant, this cell provides a value for the model to optimize. In Section 2, the white cells display the system's current recommendations, and the impact on inventory. the user can manipulate additional constraints, and examine the impact of each decision on the objective function. This way one can examine what would happen if they override the system and make their own decisions. Possible values: Online marketing spend - can take one of 3 levels: none, low, or high. Special promotion - can be none, or can have a promotion for the entire store, or only sports shoes, or only casual shoes Markdown values: markdown values are permenant discount for the initial price of each shoe. for simplicity, the tools only considers 4 different vintages of shoes, for both casual and sports brands. in reality, this can be done individually for each SKU or for a sub group of products. the system imposes a rule whereby a product cannot be marked-up, but only marked down over time. Output: Section 3 of the tab - system selection of marketing levers, and expected impact on the main KPIs. Advanced usage Other input (red tabs) These inputs can be edited to generate different starting points and trajectory assumptions. Baseline tab Starting values at the beginning of the summer. Impact tab Key links of marketing levers to output KPIs, based on historical analysis. Model output details (green tabs) These tabs can be used to better understand how the model works. Optimization tab Different values for each input variable are tested using sensitivity tables. The best value, corresponding to the highest result for the objective function, is automatically selected. Each choice is made sequentially, assuming to be independent from the others. This system requires some seconds to run each time, and may yield slightly different results even with the same input. Workings tab Impact of each marketing decision on its key target KPIs. Flow-through tab Model of each KPI, from left to right. Results tab Summary of impact by product category and vintage. &"Lato,Italic"&8&A&"Lato,Italic"&8&F &G&8Confidential - &"Lato,Italic"&D at &T&"Lato,Italic"&8Page &P of &N Strategy Strategy Choice of objective function and automated setting of optimal marketing levers, which can also optionally be edited manually 1. Set the objective function, translating the business strategy in mathematical terms DimensionValue usedDefaultManual % Revenues objective %100%100% Margin objective %0%0% Write-off value penalty %0%0% Use volume instead of value?FALSEFALSEFALSE Value of objective function85,967,468 2. Set any desired manual constraints to the automated system decisions note: markdowns can only increase, not decrease; the "MAX" formula prevents downward changes Manual choice of marketing levers (optional)Inventory level DimensionJuneJulyAugustJuneJulyAugustPrePost% sold Online marketing spendNoneLowHigh Special promotion3x2 casualNoneNone Markdown: Sports-New25.0%25.0%25.0%2,063,21852,29197% Markdown: Sports-135.0%35.0%35.0%928,51623,43497% Markdown: Sports-230.0%30.0%30.0%456,2540100% Markdown: Sports-3+50.0%50.0%50.0%385,7500100% Markdown: Casual-New5.0%45.0%45.0%2,400,3591,014,62758% Markdown: Casual-140.0%40.0%40.0%931,284285,63869% Markdown: Casual-230.0%30.0%30.0%488,551384,52221% Markdown: Casual-3+50.0%50.0%50.0%369,223280,22424% Total8,023,1552,040,73775% TRUETRUE 3. Examine the end-to-end business results based on your decisions DimensionTotalPrevious yearChange %JuneJulyAugust Visits12,624,97712,626,846-0%4,453,7664,465,9283,705,284 Conversion rate19.3%19.4%-1%22.0%17.0%18.6% Receipts2,430,4192,487,211-2%980,115760,116690,188 Units per receipt2.52.5-1%1.83.42.4 Volume5,982,4185,493,7319%1,762,3372,593,8651,626,215 Average full price23.6523.521%24.4122.3624.89 Revenues at full price141,482,925142,263,576-1%43,011,85257,998,74040,472,333 Markdown on products18.3%8.5%116%29.9%0.0%32.4% Overall discount %39.2%35.1%12%38.1%43.2%34.7% Revenue per unit14.3715.63-8%15.1112.6916.24 Revenues85,967,46884,457,5622%26,635,03932,915,69826,416,731 Margin %19.2%23.6%-19%21.3%12.5%25.3% Margin16,479,44720,591,766-20%5,681,4464,112,2256,685,777 Channel spend58,00059,082-2%015,00043,000 Margin net of channel spend16,421,44720,434,494-20%5,681,4464,097,2256,642,777 Inventory units at start3,666,9523,987,3328%8,023,1556,260,8183,666,952 Inventory units at end2,040,7372,275,10610%6,260,8183,666,9522,040,737 Full book value per inventory unit11.1211.080%11.2811.1111.12 Current net market value per unit6.776.98-3%7.887.786.77 Inventory full book value at start40,758,73344,844,8289%90,515,80069,562,20640,758,733 Inventory full book value at end21,027,77922,805,8608%69,562,20640,758,73321,027,779 Inventory net market value at end13,805,86815,422,67310%49,330,07228,515,30613,805,868 Season end additional write-off6,975,4127,944,29712%29,097,93716,271,8786,583,957 Economic write-off6,830,4567,863,78713%20,232,13512,243,4287,221,911 FALSE &"Lato,Italic"&8&A&"Lato,Italic"&8&F &G&8Confidential - &"Lato,Italic"&D at &T&"Lato,Italic"&8Page &P of &N Baseline Baseline Context data, can also be edited manually - 'do nothing' scenario Average discountUnits per receiptUnit values Total1.171.381.213.76 ProductsVintageJuneJulyAugustJuneJulyAugustTotalFull priceUnit cost paidWrite-off if unsoldInitial inventory SportsNew0%0%0%0.640.380.301.3227.7113.150%2,063,218 Sports10%0%0%0.050.140.280.4728.3113.6150%928,516 Sports230%30%30%0.090.140.110.3425.8112.2570%456,254 Sports3+50%50%50%0.080.110.070.2617.329.4590%385,750 CasualNew0%0%0%0.220.370.280.8819.889.970%2,400,359 Casual10%0%0%0.030.180.100.3215.168.5550%931,284 Casual230%30%30%0.010.050.030.0923.1311.4170%488,551 Casual3+50%50%50%0.040.010.030.0821.7410.9890%369,223 Visits KPIJuneJulyAugustTotal Number of visits / traffic4,363,8104,160,4523,324,72011,848,982 Conversion rate21.68%16.91%18.66% &"Lato,Italic"&8&A&"Lato,Italic"&8&F &G&8Confidential - &"Lato,Italic"&D at &T&"Lato,Italic"&8Page &P of &N Impact Impact Expected impact of marketing levers on business KPI performance based on past analysis, can also be edited manually Average discount impactUnits per receipt (UPR) impactVists impactConversion rate impactInvestment cash outlayPrice sensitivity net of UPRMemo: KPI impacted ActivityLevelVintageJuneJulyAugustJuneJulyAugustJuneJulyAugustJuneJulyAugustJuneJulyAugustJuneJulyAugustKPI 1KPI 2 Special promoNone0.00%0.00%0.00%0.00%0.00%0.00%Average discountUnits per receipt Special promo3x2 sports21.36%20.19%19.61%2.65%6.18%2.34% Special promo3x2 casual19.67%18.79%22.03%2.08%3.09%5.24% Special promo3x2 all20.60%19.88%21.03%3.03%4.60%11.19% JuneJulyAugustJuneJulyAugustJuneJulyAugust Online marketing spendNone0.00%0.00%0.00%0.00%0.00%0.00%000VisitsConversion rate Online marketing spendLow1.60%3.42%2.75%-0.98%-0.43%-0.43%15,00015,00015,000 Online marketing spendHigh2.67%6.56%5.72%-0.77%-1.31%-0.61%43,00043,00043,000 JuneJulyAugustJuneJulyAugustJuneJulyAugust Overall average discount, per % point8.78%7.60%8.41%6.62%10.21%14.55%1.04%1.40%1.46%VisitsConversion rate JuneJulyAugust DiscountsSportsNew-1.21-4.61-5.94Average discountUnits per receipt DiscountsSports1-3.71-3.42-4.98 DiscountsSports2-1.60-3.08-1.31 DiscountsSports3+-0.85-2.27-1.55 DiscountsCasualNew-1.26-3.69-1.24 DiscountsCasual1-3.27-4.12-3.30 DiscountsCasual2-1.19-0.43-0.97 DiscountsCasual3+-0.20-0.31-0.26 &"Lato,Italic"&8&A&"Lato,Italic"&8&F &G&8Confidential - &"Lato,Italic"&D at &T&"Lato,Italic"&8Page &P of &N Optimization Optimization Automated optimization iterating across all the acceptable values for the marketing levers ChoiceObjectiveValue of objective function with different simulated levels LeverMonthProductsVintageValue85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneSportsNew25%74,232,98877,486,59180,299,47282,659,10484,552,75585,967,46884,385,55181,698,48378,989,32576,253,33873,484,626 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneSports135%78,634,29180,334,60081,820,58383,090,18684,141,45884,972,55885,581,76185,967,46885,174,60683,947,06282,715,990 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneSports20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,391,67284,815,46184,238,83483,661,790 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneSports3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneCasualNew5%85,948,00385,967,46885,958,05585,919,45685,851,36185,753,45685,625,43085,466,96785,277,75285,057,46684,693,354 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneCasual140%83,658,62884,205,37784,679,07885,079,35885,405,86085,658,24385,836,17785,939,35185,967,46885,920,24885,797,428 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneCasual20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,902,59485,831,50685,754,19385,670,648 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJuneCasual3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulySportsNew0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,497,89883,931,68682,354,18580,763,71079,158,278 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulySports10%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,324,83984,249,25283,171,915 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulySports20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,621,55085,275,63484,929,71984,583,805 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulySports3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulyCasualNew45%81,867,06481,867,06482,829,26383,668,30784,381,57484,966,41185,420,14085,740,05485,923,41885,967,46885,869,414 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulyCasual10%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,949,59185,865,064 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulyCasual20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,919,91685,867,82785,811,19485,750,010 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownJulyCasual3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustSportsNew0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustSports10%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustSports20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustSports3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustCasualNew0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustCasual10%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustCasual20%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Markdown85,967,4680%5%10%15%20%25%30%35%40%45%50% MarkdownAugustCasual3+0%85,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,46885,967,468 Online marketing spend85