Data Table in a Module

Hi Hanz,

Yes you can include data tables in your modules, and what's more they are very easy to scale (i.e. extend across columns and add rows to them) unlike normal Excel data tables, which effectively lock up the rows and columns within their parent worksheet.

Here's how you insert a data table into a module:

Step 1. Ensure that you have your row and/or column input cells in the module component;

Step 2. Select an empty row within a module component;

Step 3. Insert a new category block using the Insert Category Block dialog (Build tab, Category Blocks menu, Insert Category Block), ensuring that Include categories total row check box is checked as well as the Position categories total row on top of category block check box, as shown below:

Cell block shading really helps here, as you need to make sure that you know where the top/total row is and where the category rows are.

Step 4. Set up your data table ensuring that the left-end cell block is your row input cell block and cell block(s) to the immediate right of this cell block are column input cell block(s) in the total row and data table rows in the categories, as shown below:

Step 5. Select the entire data table range, including the row and column input cell data cell blocks, then load the normal Excel Insert Data Table command (Data tab, What-If Analysis menu, Data Table), and specify your row and column input cells, as shown below:

Step 6. Click OK to insert the data table, then format it as you like, as shown below:

We often use conditional formatting to add formats to the table cells to clearly identify the target/mid value, etc.

The cool thing is that data tables within modules scale with categories, time series, etc., so unlike normal static Excel you can easily add rows/columns and they'll scale up, as shown below:

Hope this helps. I've attached this basic example for you to check out.

M.

You can also view a real example of the use of data tables in a full model in the Equity Valuation (DCF) example model (Modano tab, Open menu, Example Models), as shown below:

The Financial and Valuation Summary dashboard includes 3 data tables showing the valuation sensitivity to different revenue growth rates, operating expenditure growth rates, discount rates and terminal values, as shown below:

We're about to release thousands of scenario analysis modules with data tables in them, so you'll soon have access to heaps more modules when doing scenario analysis.

M.

Hi all can´t seem to get the data table to work as per Michael´s instructions.

I´m trying to calculate IRR compared to investment in fixed assets (a boat) and revenue volume.  Row input Assessment R27 (cost of boat) and column Rev-Exp I 20 (volume revenue input).

Not sure on the category blocks and the shading but the rows are always the same number and there is just the same IRR no matter the volume input so it doesn´t work like a normal data table

Can anyone help? also how to expand the data table?

best regards

Halldór

Hi Halldor,

Looks like each column in your table is unique, hence the formulas are different across the page. To fix this, delete everything on the right hand side of the column with "10,000" heading, change the heading cell to an Number Assumption Cells (or use an Offset from another table if these number are input in a category block somewhere else), populate the headings and drag it across 6 columns.

Then the color shading should look the same as it does in Micheal's example (all blue)

and the data table should work

T

Hi team, a bit different with the data table in DCF model (which is a single input applicable across the model), I tried to arrive at data tables for yearly results (e.g. change in yearly revenue growth and GPM impacting end-results of yearly closing cash flow)

When I input "Row input cell" and "Column input" cell from data table function, Modano showed the error that "The row input cell cannot be located in a category row". Any suggestion to overcome this issue? 

Best, 

Hoàng