Dissicating calculated cell values for analysis

Hi, folks!

I’ve been modelling employment plans into my financial model and I’ve been trying to take into account that I might have empty cell values for their employment date in my base model, and then set different employment times in various scenarios. Therefore I’ve had to implement some “EmployeeStartDate != Empty” checks for various variables. After all my calculations are done, I’m left with a small sum of expenses related to employees that are not yet employed, and therefore should not be there. In trying to trace those expenses to find the faulty variable function, I found that it would be super helpful if I could get a breakdown of the contributions to the calculated cell value when I hover or right click a specific cell.

For instance my “Personell expenses” could be derived from salary, software needs, indirect costs of office space etc. If I were to hover a specific cell it would be awesome to see how much each of these categories contributed to the final result.

Personell Expenses – Jan – Feb
Arnold – 0 – 2000

I hover the 2000 cell and see:

Salary: 1200
Software: 300
Indirect expenses: 500

Something like this would really make modeling faster, because I would be able to identify the roots of potential calculation problems a lot faster. Now I have to check every contributing factor for mistakes until I find it, and these things happen a lot as I try to prototype calculations. If you added a % at the end (and possibly something similar when hovering the overall variable) it would also improve analysis.

Best,

Sondre

1 Like

That’s a great suggestion. You might also make the variable names links in the pop-up so you could jump right to the variable that seems incorrect. But even without links this would be very helpful.

1 Like

Hey @Kvam & @wivers - thanks for the suggestion! Our team is currently thinking about ways to audit a formula’s values, so will take this into consideration.

On your specific point around employees that haven’t started yet - have you considered using a Headcount variable/formula, that tracks the number of employees that are active in any given month, according to their start date / end date?

• Note that both `Start Date` and `End Date` are broken down by the Employee category

• End date will have to have Empty Value setting of None (right-click on End Date variable → Empty Value → none)

• The result is a flag, which returns a 1 for each month that the employee that is active with the company, and 0 when they are not

• The Total of all Employees will reflect Total Headcount for each month for the company

You can then use this Active Headcount flag to derive expenses that should be based off only employees that are active.

1 Like

Hi,

Sorry for the late reply. We’re in the middle of a funding round!
Adding a headcount formula would be very useful for me as I see that I’ve added quite a bit of repeat formulas to handle that specific case. I’ll add it to the todo-list, and thank you!