Data Tables

Introduction to Data Tables

• Data tables are tools used to perform sensitivity analysis.

• They allow examination of output data (e.g., a company's EPS) impacted by changes in input variables (e.g., revenues, gross margin assumptions).

• Data tables output results in a presentation-friendly matrix, commonly used by analysts to illustrate a range of possible output values.

Example of Data Tables

• Illustration of assumptions before data table construction:

  • Revenue growth ranges.

  • Cost of goods sold margin assumptions.

• Resulting data in the table showcases earnings per share (EPS) implications based on variations in cost of goods sold margins and revenue growth rates.

Types of Data Tables

• Three primary types of data tables exist:

  1. Vertical Data Table

  • Layout:

    • Input assumptions listed on the left (e.g., revenue growth rates).

    • Output variable (EPS) placed one row above the results column.

  • Process:

    • Highlight entire table for execution.

    • Indicate only the input variable's cell, e.g., replace revenue assumption in cell F25 with input values for analysis.

    • No row input cell is needed in this type of data table.

    • It's necessary to hit F9 for recalculation if Excel’s settings are manual.

    • Data table must be created within the same worksheet as input variables.

  1. Horizontal Data Table

  • Layout:

    • Output variable on the left, directly followed by results on the right.

    • Input assumptions are listed above.

  • Process:

    • Highlight the entire table.

    • In the row input cell area, indicate the input variable's position (e.g., revenue growth rate).

    • Column input cell does not need to be specified in this layout.

    • Similar in function to the vertical table, results are displayed in the organized output.

  1. Two-Sided Data Table

  • Layout:

    • Output variable located at the top left.

    • Input assumptions for one variable organized horizontally to the right and for another vertically below.

  • Process:

    • Highlight entire area of assumptions.

    • Indicate both row and column input cells when running the data table. For example:

      • Column input might link to revenue growth rates in F32.

      • Row input could link to project assumptions for cost of goods sold margin.

    • The procedure executes multiple calculations to display outputs.

    • Recalculation via F9 is still applicable after setup.

Excel Implementation Example

• Setting up a practical example using Excel involves:

  1. Creating Scenarios

  • Use a drop-down menu to select scenarios (e.g., best case, base case, weak case).

  • Implement the OFFSET and MATCH functions to determine the appropriate revenue growth rates and costs of goods sold margins based on the selected scenario.

  1. Building Data Tables

  • Guide users through practical steps to build their data tables in Excel:

    • Initiate the setup for a vertical data table first where assumptions are placed accordingly.

    • Highlight the relevant areas and execute the data table command.

    • Confirm input variables and ensure accurate output calculation.

    • Organize the next data table horizontally and repeat similar steps to ensure consistent results.

    • Finally, construct a two-sided data table following the provided layout, ensuring all assumptions are accounted for in their respective slots.

Conclusion

• Utilizing data tables in Excel provides a clear, concise method to conduct sensitivity analysis.

• It is crucial to follow correct procedures for data input and calculations to ensure accurate outputs for decision-making processes.

• The understanding of these principles is essential for effective financial analysis in practice.