LBL Part 7: Optimal Pricing Under Demand Uncertainty (MUST BE SOLVED USING MACROS AND VISUAL BASIC BASED ON OPTIMIZATION AND SIMULATION MODELS PROVIDED)

Though the performance was good, Donna Andriesen, the Director of Data Analytics at LBL said,

We understand the profit, volume, and re positioning risk GIVEN a set of prices that were based on the assumption of certainty. But we know that those prices are not optimal, or at least have a lot of risk, because of uncertainty. We still might be "overshooting" - that is, creating surplus where there was deficit, and vice versa. We really don't even know if the solution is feasible, because with random demand and response, we only estimated repositioning costs, and we didn't recheck for feasibility. We even plugged in zeroes for predicted negative quantities, which is reasonable from a modeling perspective, but we all know that it is not very realistic.

Since our primary decision variable is price, and optimal pricing changes with demand conditions, then shouldn't we include demand uncertainty in our optimal price finding (and, also solve for optimal repositioning in each scenario), and then have a decision that INCLUDES that uncertainty, rather than doing a risk assessment after the fact? Intuitively, since price is our decision variable, we should care most about that decision in the face of uncertainty - not ignore uncertainty until we have made a decision, then find out how much risk we are facing.

How might you design an approach that includes demand uncertainty within the optimization? In this case, it is key to understand not how the objective changes when demand changes, but how the decision variable changes when demand changes.

Donnas primary focus for this analysis is:

• How much does demand uncertainty affect optimal pricing?
• With demand uncertainty, what happens to expected profitability?
• What risk is faced both with the decision variables and the objectives?

LBL Part 8: Final Recommendations

Given these prior analyses, please provide summary observations capturing the overview insights of these projects.

In your summary, include:

• Project overview and summary, from part 0 to part 8
• Key findings from each stage of the analytical process
• What are the appropriate measures of success and key performance indicators for LBL?
• Lessons learned and recommendations from each stage

Finally, and most importantly, given all of these analyses, what would you recommend to LBL as a way to effectively improve its pricing practices? What implementation strategies would you suggest? Organizational structures? Risks? Key performance indicators?

Baseline Anal sis: As Is, Li Analysis From Descriptive statistics section: 60 Foot Boxcars Summar, Yolume Ri Q1 Destinat Avg Cost/Uni Distand Origin ion Yolume Price t e Neg is short Posis surp Operational: Q1 Transit Days Total Cost Load Financials: Q1 Total Revenue Profit Profit/Unit Time Load Dist Load Orig Dwell Destination nwell Dwell REPO Not correct - can't run optimization within data table 2 750 750 727 727 7 5 $ $ $ $ 90 351 20 1,772 67,745 201,765 11,776 4001 $ 2,363 $ 81,294 $ 245,627 $ 12,288 $ $ $ $ 591 $ 13,549 $ 43,862 $ 512 $ 250 150 125 1718 65,667 169,132 9,871 611 Existing Market Imbalance Summary Origin - Destination - Location Outfloy Infloy 596 1,208 B 355 636 351 835 1,006 452 1,404 102 135 526 575 226 877 61 482 853 Balance Repo Oui Repo In Total Rep Net Balance 1,011 (497) 0 175 175 (322) (285) 5000 5001 (785) 75 0 482 175 745 150 745 0 1845 00C0COMODO 0 COMO COCOOOO 1000 900 700 600 950 1200 600 700 850 1100 700 950 854 594 Imbalance 615 594 502 411 332 782 Need solver to fix repo!! 575 800 800 500 500 800 800 500 500 400 100 200 $ $ $ $ $ 543 543 542 542 635 655 492,092 $ 738,139 297,061 $ 356,474 251,070 $ 351,498 329,148 $ 349,720 265,888 $ 365,596 $ 246,046 $ 59,412 $ 100,428 $ 20,572 $ 99,708 $ $ $ $ $ 3,691 2,376 3,013 2,057 1,662 458,261 322,609 272,662 222,998 180,139 1845 1782 1004 1029 665 0 5 5 1004 617 665 300 200 450 0 Summary: 2,919 Origin Destination Location vipper Dyreceiver Dvell 1.5 Loaded Cost Revenue Profit Repo Cost Profit after Repo $ 1.918,319 ####### $ 584,680 $ 547,508 $ 37,172 Dvell Time Loaded time Empty Time Total Time Loaded Distance 12.948 14,773 3275 30,996 1,703,058 Approx Repo, per assumption Constraint on total car days, including empty time 2 Randomized Demand Randomize Rar 40% Yolume Predicted based on Regression Analysis Pricie is Decision Variable 60 Foot Boxcars From Part 2: Regressio Summary Yolume RiQ1 Destinat Rec. Cost/Uni Distanc Transit Int Slope OD Originion Yolume Price e Days 40% Operational: Q1 Neg is short Posis surp Financials: Q1 Total Revenue Profit Total Cost Load Destination Int Slope Profit/Unit Time Load Dist Load Orig Dwell Dvell Repo Not correct - can't run optimization within data table 727 727 5 $ $ $ 69,764 37,654 115,483 $ 88,124 $ 46,947 $ 167,978 $ $ $ 18,360 9,293 52,494 $ $ $ 197 185 261 651 251 803 67,625 36,499 96,805 279 126 502 Existing Market Imbalance Summary Origin - Destination - Location Outflow Infloy 529 1,408 603 391 984 201 838 954 482 301 Balance Repo Oul Repo In 878 0.24 - (211) (783) 1 16 - 0.24 Total Rep Net Balance 0.24 878.18 (211.12) (783.08) 0.24 116.03 112 #### #### 1,121.13 665.54 #### #### #### #### #### #### #### #### (1.72) AB (1.141 BA 11.101 BC (1.081 CB (2.31) AC (1.30) CA (1.37) AD (2.02) DA (1.02) BD (1.42) DB (2.16) CD (2.15) DC 1,789 1,118 984 727 2,456 2,051 1,219 1,793 1,149 1,798 1532 2,246 (1.69) AB (1.02) BA (0.96) BC (0.95) CB (2.32) AC (1.61) CA (1.27) AD (2.45) DA (1.08) BD (1.38) DB (2.12) CD (208) DC 750 750 575 575 800 800 500 500 800 COCOCOOOOO O COMO COCOOOO 482 745 745 543 93 $947.38 $935.10 201 $836.37 $ 687.15 $990.75 818 ##### 436 $ 715.16 540 $ 681.31 352 $908.70 298 ##### 166 $582.29 - $882.19 2.954 861 Origin Destination Location vipper Dyreceiver Dvell 2453 436 Imbalance 994 543 1080 191 244 215 181 109 324 82 $ $ $ $ $ $ 542 654,024 $ 853,405 $ 199,382 218,112 $ 311,969 $ 93,857 269,961 $ 367,856 $ 97,895 281,226 $ 319,438 $ 38,212 238,749 $ 335,421 $ 96,673 83,196 $ 96,889 $ 13,692 4,905 1,745 3,240 1,758 1,492 998 $ $ $ $ $ $ 5 5 6 609,059 236,870 293,178 190,531 161,752 105,659 0 2453 1309 1080 879 597 499 0 800 542 527 597 499 $700 635 500 500 Repositioning assumption: For each unit of imbalance 655 1.5 OUD Loaded Cost Revenue Profit Repo Cost Profit after Repo $ 1,968,170 ####### $ 619,857 $ 695,943 $ (76,086) Dvell Time Loaded time Empty Time Total Time Loaded Distance 13,784 15,843 0.94 29,629 1,797,979 Approx Repo, per assumption Notes: Change in price, volume, financials Operational: 01 60 Foot Boxcars Summary Yolume Ri Q1 Destinat Origin ion Yolume Avg Cost/Uni Distanc Transit Pricet Days Total Cost Load Financials: Q1 Total Revenue Profit Profit/Unit Time Load Dist Load Orig Dwell Destination Dwell 750 750 BA 91 (40) (150) (20) BC CB 727 727 482 482 745 67,992.07 85,761.09 (30,091.07) (34,347.10) (86,282.09) (77,649.78) (11,776.08) (12,288.08) 17,769.02 (4,256.03) 8,632.30 (512.00) 635 (201) (600) 65,907 (29,168) (72,327) (9,871) 271.97 (100.30) (375.14) (61.44) 90.66 (60.18) (225.08) (61.44) 575 Neg is short Posis surp Change Repo Existing Market Imbalance Summary Origin - Destination - Location Outfloy Infloy Balance Not correct - can't run optimization within data table (67.24) 200.07 267.32 0.24 (400.00) (399.76) (132.92) B (250.08) 36.25 286.33 . (175.00) (175.00) 111.33 (175.00) 348.33 (150.06) (498.38) (500.00) . (500.00) 1.62 3.86 (51.41) (55.27) (75.00) 0.24 (74.76) 19.97 (325.00) 0.24 Total Imbalance (75.00) 575 800 (102) AC COCOCOMODO 800 745 (52.62) 35.10 136.37 87.15 40.75 (156.12) 115.16 (18.69) 58.70 23.93 (117.71) (67.81) 7 1,214 (632) (52.62) 35.10 136.37 87.15 40.75 (156.12) 115.16 (18.69) 58.70 23.93 (117.71) (67.81) 202 (158) 38 (60) (34) 166 . 212 227 500 500 800 800 500 500 161,931.04 115,266.59 (78,949.00) (44,504.61) 18,891.13 16,358.11 (47.921.99) (30,281.81) (27,139.42) (30,175.00) 83,196.37 96,888.56 543 543 542 542 635 655 (46,664.46) 34,444.40 (2,533.02) 17,640.18 (3,035.58) 13,692.19 150,798 (85,739) 20,516 (32,467) (18,387) 105,659 607.24 (473.69) 75.56 (149.76) 167.85) 499.18 607.24 (157.90) 75.56 (89.85 167.85 499.18 (300) (170) 998 Origin Destination Location vipper Drieceiver Dvell (18.63) en Oo NUM Loaded Cost Revenue Profit Repo Cost Profit after Repo 49,851 85.028 35,177 148,436 (113,259) Dvell Tim Loaded time Loaded Distance Total Time Loaded Distance 836 1,070 (3.274.06) (1,367.79) 94.921.10 $ $ Units Average Standard Std Dev Min Max Average 107. uncertainty: 20% uncertainty KPI: "Change in" Risk Assessment Units Replicatio 212 F-Test Two-Sample for Variances F-Test Two-Sample for Variances 1 Mean Variance Observations Rasebe Picntended Pro 47015.58263 447151.4688 17475196556 5795290173 Mean Variance Observations 101 Baseline Prommended From -3121.908056 287121.8 39836318363 2.13E-10 101 101 100 1.872427031 0.000956809 1.391719552 100 100 100 (559) 0 Baseline Profit Recommended Profit $ 313,379 $ 263,876 $ (907,640) $ (438,557) | $ 644,650 $ 674,257 Prob Better $ (70,376) $ 82,106 69% Baseline Profit Recommended Pr Better? 37,172 $ (76,086) 495356.9591 541008.8632 2 60761.45294 326818.6828 3 -602963.3537 -380759.5298 -358768.0936 -154369.9019 -907640.1065 -438556.5008 70139.31208 -140599.9806 3310.990838 -285560.3836 -130781.7381 -51345.53746 9 21873.31994 363589.3503 56707.0401 454566.4535 11 161539.5612 474377.9605 1 12 -597875.2225 76138.57206 -832074.1169 -230998.257 -420854.1476 -53589.35417 15 8466.777047 -36388.3628 247471,0811 223946.1693 -334737.3973 80508.63501 4633.287037 145376.5111 1 208875.4598 130957.4049 0 -182206.4913 -62393.42492 144569.8628 -93299.269 0 22441.5091 395130.4753 1 -78853.74397 33827.51193 1 -235758.8031 368914.6659 P(F