Creating a basic formula map

  1. Highlight any Excel formula, such as C3 + C4 + C5. Do not highlight an MPCData formula.
  2. Select Generate Formula Map from the CPM Analysis menu.
  3. The cell reference for the highlighted formula is displayed in the box. You can change this formula by first clicking in the box and then clicking on another formula in the plan, or you can manually edit the location.
  4. Optionally, if you would like the formula map to show dimension member names or descriptions to easily identify each component of the formula on the map, click in the box and then click on a dimension member associated with the formula, alternatively, manually specify the cell reference of the dimension member.

    To show labels properly, the selected formula should extend in one direction only, not two directions. That is, when you analyze the formula, the arrows and leaf components should run up and down or across, but not both. If the formula extends in multiple directions, the labels for some components is meaningless.

    The analyzed formula in C4 shows a one-directional formula (valid for labels).

    ../images/image2.png

    The analyzed formula in B3 shows a two-directional formula (across periods and down the schedule). This formula is not recommended for labels.

    ../images/image3.png

    If the direction of the formula runs across one row (across multiple columns), select a member for the label that is in the same column as the formula (e.g., if periods run across the columns, select the period). If the direction of the formula runs down one column (down multiple rows), select a member for the label that is in the same row as the formula. In the following example, the selected formula in cell C4 is associated with the Period member July 2008 and the Schedule member Total Sales. Total Sales is a valid member to select for a label. July 2008 is not.

    ../images/image4.png

  5. The primary data represents the calculated result whose component values will be mapped. By default, the cell reference for the selected formula is displayed; however, you can use a different data slice for the same formula. In the preceding example, if you want a map of the schedule, rather than the periods, since the schedule lines run down the rows, you would need a formula that also runs down the rows, such as July 2008, or any other month. However, you could map the values for Quarter 3 2008 rather than the July 2008 values by selecting Quarter 3 2008 as the primary data. You cannot choose Quarter 3 2008 as the formula, because that formula runs in two directions.
    • To use a different data slice for the primary data, click in the Primary Data text box to activate it and then click on a new cell in the worksheet or you can manually specify a new cell reference. Similar to the rules applying to labels, if the direction of the formula runs across one row, across multiple columns, select a cell for the primary data that is in the same column as the formula. If the direction of the formula runs down one column, down multiple rows, select a cell for the primary data that is in the same row as the formula.
    • If the cell reference is specified automatically in the Labels text box or the Primary Data text box by clicking a cell in the worksheet, the Offset from Formula line is displayed under the text box. The value of this line indicates the relative location of the label cell to the formula cell, using this syntax: 
      (<# of rows from formula cell>, <# of columns from formula cell>)
    • A negative value means below the formula cell (for rows) or to the left of the formula cell, for columns. In the example, 0-2 indicates the A4 cell selected for the label is in the same row and two columns to the left of the C4 formula cell. The cell selected for the primary data is one column to the left of the formula cell.
    • The line does not appear if you manually specify a cell reference.
  6. To include advanced features such as variances or sensitivity in the formula map, see "Adding Advanced Features."
  7. Click OK. The task pane closes, and a new worksheet called Formula Map is inserted to the right of the current worksheet and activated.
  8. To reopen the Document Actions task pane, select Task Panes from the View menu on the main toolbar or choose one of the Design options.