Question 3.8: Cost Estimating Relationship (CER) for a Spacecraft In the e...
Cost Estimating Relationship (CER) for a Spacecraft
In the early stages of design, it is believed that the cost of a Martian rover spacecraft is related to its weight. Cost and weight data for six spacecraft have been collected and normalized and are shown in the next table. A plot of the data suggests a linear relationship. Use a spreadsheet model to determine the values of the coefficients for the CER.
| Spacecraft i |
Weight (lb) x_{i} |
Cost ($ million) y_{i} |
| 1 | 400 | 278 |
| 2 | 530 | 414 |
| 3 | 750 | 557 |
| 4 | 900 | 689 |
| 5 | 1.13 | 740 |
| 6 | 1.2 | 851 |
Figure 3-7 displays the spreadsheet model for determining the coefficients of the CER. This example illustrates the basic regression features of Excel. No formulas are entered, only the cost and weight data for the spacecraft. The challenge in spreadsheet regression lies in making sure that the underlying regression assumptions are satisfied and in interpreting the output properly. The Tools| Data Analysis| Regression menu command brings up the Regression dialog box shown in Figure 3-7(a) and shows the values used for this model. The results of the analysis are generated by Excel and are displayed beginning in cell A9 of Figure 3-7(b). For the purposes of this example, the coefficients b0 and b1 of the CER are found in cells B25 and B26, respectively .
The resulting CER relating spacecraft cost (in millions of dollars) to spacecraft weight is
Cost = 48.28 + 0.6597x ,
where x represents the weight of the spacecraft in pounds, and 400 ≤ x ≤ 1,200.
