Modeloptic's table-centric forecasting structure is very powerful for forecasting collections of items, including staff expenses. Below, we'll walk through how to construct a personnel expense forecast in Modeloptic, and explain the different table types and their uses along the way.

(Note that when constructing an actual personnel expense forecast in Modeloptic, it's often best to start with one of our presets, which you can learn more about here. Below we're going to walk through the process step by step for the sake of illustrating Modeloptic's features.)

First, we'll make a "Standard" table where we list our current staff. We'll call it "Staff List (Total Expense)" because we'll have this sum up all components of our staff expense a little later. Here is what this table's settings look like so far:

It's a "Standard" table type because it contains a collection of like elements, and it's going to serve as the parent table that other related tables will reference.

Let's add a few rows to our table, one for each existing person on our staff:

(When importing your actual roster, Modeloptic has tools to make that easy, which you can learn more about here.)

Next, we'll set up some related tables that we know we'll need, which include the following:

• Base Compensation (by Person)
• Payroll Tax Expense (by Person)
• Benefits Expense (by Person)
• Accrued Bonus Expense (by Person)

Each of these will be a "One-to-One" table type that references the main Staff List table that we created above, which means each will mirror the row structure of the Staff List table. When staff members are added, deleted, reordered, or renamed in the parent Staff List table, the change will automatically be reflected in all child One-to-One tables as well.

Here's what the settings for each of the One-to-One tables looks like now:

Note that we've put in the following settings:

• The "Use Formula for Projections at Table Level?" flag is on, which means we'll be able to use a single formula to forecast all rows in this table (versus needing to create an equivalent formula in every row and cell within the table)
• The "Aggregation Lines: Sum" flag is on, which means there we'll see a total row at the bottom of the table that sums up each of the rows above

Next, we'll add a "Free-Form" table where we'll put some key assumptions that we can then reference:

Now we'll go back to our Staff List table and add in some more detail. We want to add two custom columns:

• A text column to input the person's title
• A number column to input the person's base salary (which we can then reference in formulas)

While we're in the settings for this table, we'll turn on the same two additional flags that we did for the child tables:

• We turn on "Use Formula for Projections at Table Level?" because this table is going to sum up the child expense tables
• We turn on the "Aggregation Lines: Sum" flag to show a Total Personnel Expense row for this table

Our Staff List table now looks like this (in Advanced Mode):

Now let's go set up our Base Compensation expense calculations.

First, in the Base Compensation table's settings, we'll turn on the "Include Start & End Dates?" flag so we can indicate date ranges for each of our staff members (to account for new hires and terminations), which will add two new columns to each row:

Then we'll create a table level formula to calculate base compensation expense:

This formula:

• Looks up the value we entered into the "Base Salary" column for each person
• Divides it by 12 (since we entered an annual salary number and want that broken down into monthly amounts)

We can see that these calculations now show up in each of the months:

A few items to note:

• Even though we defined the "Base Salary" column on the "Staff List (Total Expense)" table, we can reference it from here because the tables have a Standard/One-to-One relationship. One-to-One tables can also reference columns defined on their sibling tables
• All values outside of the Start/End Date range on a given row will be shown as zero
• By convention, we enter expenses as negatives

Next, we can enter the formulas for the payroll tax table:

And for the benefits table:

And for the accrued bonus table:

Note that each of these reference the Personnel Assumptions table we created above.

Finally, we can enter the formula for the "Staff List (Total Expense)" table:

The resulting value calculations look like this:

With only a few formulas, we were able to set up our whole personnel expense forecast.

Now let's say we want to assign each of our employees to a department.

First, we create a "Department List" table and add some rows to it:

This table has the "Exclude Timeseries?" flag on since we're not going to be entering values for it, we're just going to be using it for categorizing employees.

Next, on the "Staff List (Total Expense)" table, we can add a custom column that points to this "Department List" table:

Then we can assign each person to a department:

Now we can use these department assignments to see staff expense by department. To do that, we create a "Total Staff Expense (by Department)" Analysis Table with settings that look like this:

This yields a table that looks like this:

You can think of an Analysis Table as basically a pivot table in Excel that lets you perform aggregations of other tables like this.

Now we can create a table that summarizes the key components of the expense forecast we just made:

This is a "Free-Form" table that references components of the other tables we created.

The bottom portion where we show total expense by department utilizes a feature called "Sub-Tables":

By using the "Sub-Table" row type in Advanced Mode within a Free-Form table, you can pull in the component rows of any other table, and they will automatically update if you modify the rows in the referenced table.

Modeloptic has a unique feature called a "List-Scoped" table that lets you see the detail for any individual entry (or person in this case) in isolation:

All of the rows you see in this view are pulled from the relevant component tables we created above.

We can organize all of the tables we've created into groupings for easy navigation between them:

Having the ability to operate on a group of items in aggregate this way and to have them stay in sync automatically is a powerful capability that allows you to forecast at a much higher level of abstraction than you'd be able to otherwise.

If you're interested in learning more, please feel free to contact us, or take a look through our documentation.