Build your first model

Start from an empty workbook and build a working staff-cost model — dimensions, series, formulas and your own data — then use it to find something the company's own summary hides.

A company has three years of headcount and salary history and a hiring plan for next year, both split by department. Salaries rise each year by an approved percentage. You have been asked to model the staff budget for 2024 to 2026 and forecast 2027.

You will build it from an empty workbook. Nothing is pre-loaded, and by the end the model will have told you something about the company that its own summary hides. If you would rather see the finished model first — or compare yours against it afterwards — it is published in Demo models as Walkthrough demo.

Start a new workbook, click Axmo Model, then Show panel.

Part A — Dimensions

A dimension is an axis your figures vary along. This model has two: who, and when.

Company

Click +Categorical. Rename it Company — the top layer and its consolidation element follow the name.

Add a layer: type Directorate, then +Add Layer.

Add its first element with the green + on the layer row: choose the parent, type Alpha, press Enter. Add Beta the same way.

Now a Department layer, and five elements — Alpha-1 and Alpha-2 under Alpha, Beta-1, Beta-2 and Beta-3 under Beta. Choose the parent each time. The picker offers every element above the layer you are adding to, not only the layer directly above it, because Axmo lets a child hang from any higher layer.

The buttons to the right of each element rename it, move it, re-parent it or delete it. Save whenever you like. Check the shape against the figure below and Save.

Time

Click +Sequential and name it Time. Type already reads Time. Under Layers, tick year. Set the start to 2024. End defined by already reads end_point, so set the end to 2027. Preview — four elements — then Confirm and Save.

A sequential dimension is generated rather than typed, and its elements have a known order. That order is what [Year[-1]] will step along in Part B.

Your Data Series list will also show Time_Weight. Axmo creates it automatically whenever a Time dimension is built — it holds the length of each period in days. You did not add it, and nothing in this model uses it.

Part B — Data series

A data series is a named quantity. Input series hold values you type or load. Calculated series hold a formula, evaluated separately at every coordinate.

The four inputs

Click +Input, name it Joiners Persons, leave both dimensions connected, roll-up SUM, Save.

Three more the same way: Leavers Persons, Joiners Salary, Leavers Salary.

Roll-up is chosen per series and per dimension, and it is how the total gets made. SUM is right for people arriving and money paid out. Nothing about the data tells Axmo that, which is why you say it.

One input that is not per department

Year End Salary Increase, % — untick Company, and set the Time roll-up to NONE.

Worth pausing on. The policy is one number a year, the same in every department, so the series has no reason to vary by Company. Read at any department it gives the same figure. And NONE, because there is no meaningful total of a percentage across four years.

The calculated series

Click +Calculated, name it Headcount, bindings Company SUM and Time AVG, and the leaf formula:

Nz(Headcount[Year[-1]], 0) + Joiners Persons - Leavers Persons

Read it as a sentence: this year's headcount is last year's, plus joiners, minus leavers. [Year[-1]] steps back one year. Nz supplies 0 for 2024, where there is no previous year to step to. A series that refers to its own earlier value is a recurrence, and Axmo works out that it has to be computed year by year.

Time roll-up is AVG here because headcount is a stock. Adding four years of it would count every person four times.

The rest are the same three steps — name, settings, formula — so only the settings are given:

Two of these are worth stopping on.

Staff Cost reads the increase at [Year[-1]]. A year-end rise lands on the following year's payroll. Read it at the current year instead and 2027 would quietly get no rise at all, because the policy table ends in 2026 — the model would still compute, and the forecast would be wrong.

FORMULA roll-up means the formula runs at the total as well as at the leaves. Average Salary at Alpha is Alpha's cost divided by Alpha's headcount — not the average of its two departments' averages, which is a different and meaningless number. A ratio is never summed.

Check your list against the figure below.

Part C — Connecting to data

Make a new sheet called InputData. Paste the first table into A1 and the second into I1.

One thing to notice in the data. 2024 has joiners in every department and no leavers anywhere, and its joiner salaries are far larger than any other year's. That is the opening position arriving: the model has no separate series for the workforce that already existed, so 2024 loads it through Joiners. From 2025 the columns mean what they say.

Select the larger table, right-click, and run Create Input Range from the Axmo group at the bottom of the menu. (The Input Ranges editor does the same thing: +Add, then Use selection.) Name it Department Year data, Has headers on, Add row number off. Save.

Click +Series Loader. Target series Joiners Persons, value source Joiners Persons, duplicate handling Block. Then +Add beside Target series three more times, for Leavers Persons, Joiners Salary and Leavers Salary — the column names match the series names.

Under Dimension mappings, Company comes from the Department column and Time from the Year column. Click Apply at the top right of the loader form, then Save.

The mapping is how a spreadsheet row becomes a coordinate. Alpha-1 in the Department column is matched to the element you created in Part A, so the spelling has to agree.

Now the second table, the same way: input range Salary Increase Data, one target Year End Salary Increase, % from the YE Salary Increase column, duplicate handling Block, Time mapped to the Year column. Apply, then Save and close.

Part D — Does it work?

Press Update on the panel. The status becomes Up to date. If something is wrong, the panel tells you what and where — nothing is silently skipped.

In any empty cell with a little room to its right and below:

=DS.DATA("SC_YtY_Change := [Staff Cost Change To Previous Year, %][All_Company]; AS_YtY_Change := [Average Salary Change To Previous Year, %][All_Company]; YE_Increase := [Year End Salary Increase, %][Year[-1]]", "Year", "2024")

Format the result as a percentage with one decimal. The third argument drops 2024, which has no previous year to compare against.

Staff cost is growing at roughly three times the pay policy, while pay per head tracks the policy within a point. So the growth is people, not pay — and at company level the rises look exactly as approved.

That second line is the one worth doubting.

Part E — A report, and what it shows

Building it

Find a free area six columns wide and forty-nine rows deep. Right-click its top-left cell and run Create Output Range. (Or open the editor and use Use Selection, or type the anchor as Sheet!Cell.)

Row definition: Dimension → Company → whole dimension → Add. Column definition: Dimension → Time → Year → Add.

Then add the series to the row definition. Series → Pick → Headcount → Add, then click the f button on that row and enter 0 as the format. The others follow: Staff Cost and Average Salary as #,##0, Staff Cost Share as 0%, and both change series as 0.0%.

Give every series a format. An output cell with no format keeps whatever format was there before it, so a report that grows by a row will show yesterday's percentages under today's headcount.

Check newLine is on for the series row, leave the output type as Report, and make sure Show column headers, Show row labels and Show data are all on. Save, close, and Update.

Reading it

The company view said pay per head is tracking policy. Test it one level down.

On the panel, open Active Sheet (or Outputs), find Staff Cost report and click Filt. Remove the Department layer — the red cross on its row, or deselect the children of Alpha and Beta in the Trees panel — and Apply.

Neither directorate is anywhere near the policy, and their errors nearly cancel. Beta's headcount is 30 in every one of the four years — the directorate that is not growing at all is the one whose pay per head is running at nearly twice the approved rise.

Put the departments back and the same split appears inside each directorate.

Asking a further question without building anything

You can add a calculation to a report without adding a series to the model. In the Output Ranges editor, in the row definition, choose Series → Expression and type:

AS_Joiners := IF(Joiners Persons <> 0, Joiners Salary / Joiners Persons)

Add, format #,##0. Then the same for:

AS_Leavers := IF(Leavers Persons <> 0, Leavers Salary / Leavers Persons)

Save and Update.

There it is. Alpha replaces the people who leave with people costing slightly less. Beta replaces them at a 22% premium — and by 2027 Alpha is 9% below its leavers while Beta is still 19% above.

Neither directorate is doing anything visible at company level. One is buying growth by hiring below its own average; the other is holding headcount flat while its cost per head climbs. The summary said the pay rises were as approved, and it was right — by coincidence.

One check worth noticing

For Beta and its departments, Staff Cost Change and Average Salary Change are identical. For Alpha they are far apart. That is not a quirk: where headcount is flat the two must agree, and where it grows the gap between them is the growth. You could have found Beta's flat headcount from those two rows alone.

Where to go next

The model has room for more: headcount change year on year, staff turnover, cost per joiner. Each is one more calculated series and no new data.

Part F — The same model, the quicker way

In the parts above, we asked you to do everything by hand. That was necessary: building each piece yourself is the best way to understand what a model is made of, and why each part is there.

In your daily work, Axmo offers tools that do much of this for you. So let's build the same model again, and see how much faster the dimensions, the input series and their connections to the data can be put in place.

A new workbook, with the data already in it

Start from the workbook you built above. Right-click the InputData sheet tab, choose Move or Copy, pick (new book), tick Create a copy, and confirm. The new workbook has both data tables in the same cells.

Then add one more table on the same sheet: the structure of the Company dimension.

Company, from a range

Paste this table into InputData at L1. It has three columns and no header row, so the first row is already data:

What each column means:

• The first column is the element.
• The second is its parent. The root of the tree has none, so its cell stays empty.
• The third is the layer, and it is optional. Where it is empty, Axmo places the element from its parent. If it can't, it says so and imports nothing.

Elements and layers are created in the order the rows appear.

Select L1:N8. Open Dimensions, click +Categorical, and name the dimension Company. Open the ⋯ menu next to it and choose Import from range.

Axmo asks Replace this dimension's structure? and tells you the range builds 3 layers and 8 elements. Replace is right here, because a new dimension starts with a default top layer and root, and the range replaces them. Click Replace. The tree is the one you built by hand in Part A: two directorates and five departments. Save.

Time

Time is created exactly as in Part A. A sequential dimension is generated from a few settings, so there is nothing here to speed up.

The input ranges, without loaders

In Part C, each input range got its loader straight away. This time, create the ranges only.

Select the larger table, right-click, and run Create Input Range. Name it Department Year data, with Has headers on and Add row number off, and Save. Do the same for the smaller table, named Salary Increase Data.

Leave both without loaders. Axmo creates them in the next step.

The input series, from the data

Open Data Series, click the round ⋯ button after +Input and +Calculated, and choose Import from data source. The wizard has four steps:

1. Choose a data source: Department Year data. Next.
2. Choose dimensions: from Add a dimension…, add Company, taken from the Department column, and Time, taken from the Year column. Roll-up SUM and split-down NONE are already set. When the wizard reports Every chosen dimension is mapped, click Next.
3. Choose the dataseries to create: four are offered, all ticked — Joiners Persons, Leavers Persons, Joiners Salary, Leavers Salary. Their names come from the column headers, so they already match Part B. Year and Department aren't offered, because you mapped them as dimensions. Next.
4. Confirm: leave Also create the Series Loader that fills them ticked, set Duplicate handling to Block, and click Create dataseries.

The four series appear in the list. Save: until you do, they aren't part of the model.

Then the policy series, with the same wizard on Salary Increase Data:

• In step 2, add only Time, from the Year column, and set its roll-up to NONE, as in Part B.
• In step 3, the wizard proposes YE Salary Increase. Rename it Year End Salary Increase, % so that Part B's formulas find it.
• In step 4, choose Block, click Create dataseries, and Save.

What is left is yours

That is Parts A, B and C — the dimensions, the input series and their connections to the data — in a few minutes, and with far less to type.

What remains is the part only you can write: the calculated series, which say what your numbers mean, and the report. Add them as in Parts B and E, press Update, and you have the same model you built by hand.