Suppose the 4-sigma bound on error is [-2,+2]. Then sigma=1 and the average error is zero. So if the error is normally distributed, then norm.s.dist(x, true) gives is the fraction of times that error is less than or equal to x.

Because the shape is symmetric, the fraction of times that error exceeds x is the same as the fraction of times error is less than -x or norm.s.dist(-x,true).

Now suppose the uncertainty x has a 4-sigma bound of [L, U]. The average is (L+U)/2 and sigma is (U-L)/4. Then z=(t-average)/sigma has a 4-sigma bound of [-2,+2]. So the probability of being x being less than the target t is norm.s.dist(z,true). The proportion of times that x exceeds the target t is norm.s.dist(-z,true).

Excel solver, as a default, assumes that the variables m1, m2, and m3 are non-negative (which is correct, in this case, since m1,m2, and m3 are mass targets.). Note that in other problems --- where you want to allow the variables to be negative --- you would simply uncheck the appropriate box in excel solver.

Problems:

1. The orbiter design team can use their abilities to reduce the amount of weight in the orbiter so that its weight is as low as m1. If the orbiter has a weight target of 130 and m1<130, then the design team's lowest mass orbiter will meet the weight target. But the team will only know the value of m1 after they have thoroughly researched the design. Suppose the lower and upper four sigma bounds on m1 are 10 and 190. So the average is 100, the range (190-10) is 180 and the sigma (range/4) is 45. The probability the engineer can design and produce an orbiter that meets a weight target of 130 is the probability that m1<130 which for z1=(target-mean)/sigma=(130-100)/45, can be calculated in excel using norm.s.dist(z1, true). If the engineer fails to meet these specs, then the project fails. (In class, we discussed how to mitigate the risk of the engineering failing to meet these specs.) Note that the larger the value of z, the greater the probability of the engineer meeting the weight target of 130. (5 points)
2. Let m2 be the lowest weight external tank the engineering team can design. Suppose the lower and upper four sigma bounds on weight are 15 and 105. What is the probability that an external tank with a weight of 90 or less can be designed? (5 points). Hint: Use the same approach as in (1) with different values.
3. Let m3 be the lowest weight solid rocket booster the booster team can design. Suppose the lower and upper four sigma bounds are 30 and 90. What is the probability a solid rocket booster with a weight of 90 or less can be produced? (5 points). Hint: Use the same approach as in (2) with different values. The probability is not exactly equal to 100%.
4. Once the shuttle is produced, a launcher in Florida must carry it into space. Suppose that the maximum total weight that the launcher can lift is normally distributed with a four sigma lower bounds of 220 and an upper bound of 400. This is the launcher's capacity. If the actual weight of the shuttle is less than this uncertain maximum total weight, then the launcher can launch the shuttle. The target weight for the launcher is the sum of the target weights for each of the subsystems. If the designers exactly meet targets of their targets, then the probability of being able to launch the shuttle is the probability that the launcher's uncertain capacity exceeds this target weight. Compute the z-score as was done in 1. Since the launcher's chance of success decreases with the weight of the shuttle, lower values of z increase the chances of success and the probability the launcher can launch a shuttle with weight target is norm.s.dist(-z, true). If the design team produces a shuttle with an orbiter mass of 130, an external tank mass of 90 and a booster mass of 90, what is the probability the launcher can launch the shuttle. (Hint: assigning requirements of 130 to the orbiter, 90 to the external tank and 90 to the solid rocket booster implies that the total target weight for the shuttle is 130+90+90=310. Compute the z-score for this total weight target and then use norm.s.dist(-z,true). (5 points)
5. What is the overall probability of these targets of 130, 90 and 90 being met by the design team and being launched by the launcher. (Hint: multiply the four probabilities in 1, 2, 3,4. ( 5 points)

6: Suppose we tighten the requirement for the external tank to make the shuttle lighter. What is the probability that setting a weight target of 130 for the orbiter, 75 for the external tank and 90 for the solid rocket booster (with a launch target of 130+75+90) will lead to a shuttle that can be designed and launched.? (5 points): Hint this involves redoing (2) & (4) and recalculating the Z-score. Then compute the product of the probabilities as in Q5.

It is recommended that the next two questions be solved with excel solver. If excel solver is installed on your computer, you can find it by clicking on the data menu at the top of your spreadsheet and looking at the rightmost option under data. If it is not installed on your computer, you can find it using excel/file/options/add-ins, clicking go for excel-adds and then activating excel solver in the pop-up menu.

7. Using the lower and upper bounds provided in Q1 through Q3, calculate the weights targets for the orbiter, external tank, and solid rocket booster (values of m1, m2 and m3) which maximize the probability of creating a shuttle that can be designed and launched (10 points.) Your answers for weight targets should be rounded to the nearest whole number (i.e., no fractions, no numbers to the right of the decimal, etc.) Remember that the weight of the shuttle will be M=m1+m2+m3. Since excel solver will run better if the initial values of m1, m2, and m3 which it is given are realistic, consider using the values of the weight for m1, m2 and m3 in 1,2,3 as your starting values.

8. Now suppose the upper and lower bounds on m1 are changed from 190 and 10 to 130 and 70, the upper and lower bounds on m2 are changed from 105 and 5 to 90 and 30 and the upper and lower bounds on m3 are left unchanged at 90 and 30. Thus the ranges on m1 and m2 have been changed to 60 but their mean value has not changed. What weight targets m1, m2, m3 for the orbiter, external tank, and solid rocket booster (with M=m1+m2+m3) maximize the probability of creating a shuttle that can be designed and launched? (10 points.). There is no change in the range for the launcher.

If you do not have excel solver, try to load it into your spreadsheet. If you cannot load it, then solve 7 and 8 with simple trial and error.

If you can use excel solver, go to data/solver, and a menu pops up. Under `set objective', specify the cell in which the probability of a design success is calculated, set 'to' to Max, set `by changing cells' to the cells in which trial values of m1, m2 and m3 are inputted, set `select a solving method' to GRG nonlinear and press solve. For example, suppose you have inputted the trial values of m1, m2, and m3 into cells A1, A2, and A3 on your spreadsheet. Using the logic in the previous questions, you have developed equations which use the values in A1, A2, and A3 to calculate the probability of success. Suppose this probability of success is reported in cell A4. The under `set objective', you put cell A4. Under `by changing cells', you put cells A1: A3 (or A1, A2, A3).