Cut Arial 11 v A ab AutoSum v V ab Wrap Text Custom DA HTI no WE Copy v 28-O Fill v Paste B I U v A v $ v % 9 V Insert Merge & Center v HO .00 .00 0 Delete Format V Conditional Format Formatting as Table Format Cell Styles Sort & Filter Sensitivity Clear v Find & Select Analyze Data 125 fx -0.2 A B C D E F H I J K L M N 1 8 a O O $10 -0.8 2 3 Style 1 (the most popular style) sells through two different channels, while Styles 2-4 sell through only one channel. We now focus only on Style 1. The total demand is 4 still normally distributed with a mean of 60,000 and a standard deviation of 9,600. The channels are negatively correlated with each other, that is as sales in channel 1 5 increase sales through channel 2 decrease, with a correlation coefficient of -0.5. TTC is considering pooling the channel demand at a centralized location versus serving 6 each channel out of a separate location. Calculate the optimal quantities and expected profit. 7 c. In cells C16 and C17 demonstrate the calculation that leads to a combined demand of 60,000 and standard deviation of 9,600. Calculate the number of shirts 9 manufactured and expected profits under the decentralized and centralized plans (hint expected profit should be lower under decentralized). 10 11 I have created a data table for you in 115:K37 to show the values of centralization at different levels of rho (correlation coefficient). Hit F9 to have it update. 12 13 14 15 Inputs Total Channel 1 Channel 2 Rho Rho Q* 16 Expected demand, D 30,000 30,000 -0.5 17 Standard deviation of demand, og 9,600 9,600 -1 18 Unit costs, C $10 $10 -0.9 19 Sales price, p $18 $18 $18 20 Discount price $8 $8 $8 -0.7 21 Inventory holding costs for season $1 $1 $1 -0.6 22 -0.5 23 Salvage value, s -0.4 24 Cost of understocking, cu -0.3 25 Cost of overstocking, Co -0.2 26 -0.1 27 Outputs 0 28 Optimal service level (critical ratio) Decentralized Total 0.1 29 Optimal production quantity (Q*) 0.2 30 31 Z-value 0.4 32 Loss Function L(z) 0.5 33 Expected Loss Sales 0.6 34 Expected Sales 0.7 35 Expected Leftover inventory Decentralized Total 0.8 36 Expected Profit 0.9 37 1. 38 39 Without Postponement With Postponement With Correlation Centralized E[Profit] 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 O o 0 0