How to do the calculations within the excel file
Cut Arial 11 v A = ab AutoSum v V Number ab Wrap Text DA HTI no WA Copy v 28-O Fill v Paste B I U V A v E V $ v % 9 Insert Merge & Center v HO 2.00 .00 0 Delete Format V Conditional Format Formatting as Table Format Cell Styles Sort & Filter Sensitivity Clear v Find & Select Analyze Data C28 fx A B C D E F G H I J K L M N a a 1 Individual Problem Set #2 2 3 The T-Shirt Company (TTC) is planning production of T-shirts for four styles with different Christmas embroidery. All four styles have demand that is normally 4 distributed. The best-selling style has an expected demand of 60,000 and a standard deviation of 9,600. Each of the other three styles has an expected 5 demand of 15,000 with a standard deviation of 7,500. Currently all T-shirts are produced before the start of the season. Production cost is $10 per T-shirt, and 6 they are sold for a wholesale price of $18. Any unsold T-shirts at the end of the season are discounted to $8, and they all sell at that price. It costs $1 to hold 7 the T-shirt in inventory for the entire season if it does not sell. We assume the demand is independent for all the styles. 8 9 a. How many T-shirts of each type should TTC manufacture? What is the expected profit from this policy? How many T-shirts does TTC expect to sell at a 10 discount? 11 12 13 14 15 Inputs Style 1 Style 2 Style 3 Style 4 16 Expected demand, D 60,000 15,000 15,000 15,000 17 Standard deviation of demand, o, 9,600 7,500 7,500 7,500 18 Unit costs, $10 $10 $10 $10 19 Sales price, p $18 $18 $18 $18 20 Discount price $8 $8 $8 $8 21 Inventory holding costs for season $1 $1 $1 $1. 22 23 Salvage value, s 24 Cost of understocking, Cu 25 Cost of overstocking, Co 26 27 Outputs 28 Optimal service level (critical ratio) Total 29 Optimal production quantity (Q*). 30 31 Z-value 32 Loss Function L(z) 33 Expected Loss Sales 34 Expected Sales Total 35 Expected leftover inventory 36 Expected Profit Total manufactured Total expected sold at discount Total expected profit Without Postponement With Postponement With Correlation + Ready 150% Cut Arial 11 v A = ab AutoSum v V Number ab Wrap Text DA HTI no WA Copy v 28-O Fill v Paste B I U V A v E V $ v % 9 Insert Merge & Center v HO 2.00 .00 0 Delete Format V Conditional Format Formatting as Table Format Cell Styles Sort & Filter Sensitivity Clear v Find & Select Analyze Data C28 fx A B C D E F G H I J K L M N a a 1 Individual Problem Set #2 2 3 The T-Shirt Company (TTC) is planning production of T-shirts for four styles with different Christmas embroidery. All four styles have demand that is normally 4 distributed. The best-selling style has an expected demand of 60,000 and a standard deviation of 9,600. Each of the other three styles has an expected 5 demand of 15,000 with a standard deviation of 7,500. Currently all T-shirts are produced before the start of the season. Production cost is $10 per T-shirt, and 6 they are sold for a wholesale price of $18. Any unsold T-shirts at the end of the season are discounted to $8, and they all sell at that price. It costs $1 to hold 7 the T-shirt in inventory for the entire season if it does not sell. We assume the demand is independent for all the styles. 8 9 a. How many T-shirts of each type should TTC manufacture? What is the expected profit from this policy? How many T-shirts does TTC expect to sell at a 10 discount? 11 12 13 14 15 Inputs Style 1 Style 2 Style 3 Style 4 16 Expected demand, D 60,000 15,000 15,000 15,000 17 Standard deviation of demand, o, 9,600 7,500 7,500 7,500 18 Unit costs, $10 $10 $10 $10 19 Sales price, p $18 $18 $18 $18 20 Discount price $8 $8 $8 $8 21 Inventory holding costs for season $1 $1 $1 $1. 22 23 Salvage value, s 24 Cost of understocking, Cu 25 Cost of overstocking, Co 26 27 Outputs 28 Optimal service level (critical ratio) Total 29 Optimal production quantity (Q*). 30 31 Z-value 32 Loss Function L(z) 33 Expected Loss Sales 34 Expected Sales Total 35 Expected leftover inventory 36 Expected Profit Total manufactured Total expected sold at discount Total expected profit Without Postponement With Postponement With Correlation + Ready 150%
