Microsoft Office Tutorials and References
In Depth Information
Note You can rename a table in Excel from the Design tab on the Table Tools contextual tab of
the ribbon, to arrive at the Table Name text box that is displayed in the Properties group.
At this point, the missing link is a relationship between the Sales and the Prediction tables
so that you can use the predictive factor in the Sales table. You can do that in PowerPivot
by clicking the Create Relationship button and configuring it as it is shown in Figure 5-28.
FIguRE 5-28 The relationship between the Sales and Prediction tables.
Now you can use PowerPivot relationships to define two new columns in the Sales table;
you can call them PredictiveFactor and PredictedSales. The formulas, shown in Table 5-1,
are pretty easy.
TABLE 5-1 Formulas to predict sales in 2005 based on 2004 data.
The PredictiveFactor column computes the increase in sales of the month, by using the
RELATED function. The PredictedSales column uses the predictive factor to compute the
value of sales, correcting in this way the sales in 2004 with the predictive factor.
It is now very easy to create a PivotTable based on the PowerPivot data model, add it to a
worksheet, and produce the report shown in Figure 5-29.