Model Formula Reference
Account Balances
References the account balance amounts from your chart of accounts on the Financial Report. You can reference the account balance of any category in the past (actuals) or future (forecasts).
For past months, use x - (n) to reference a number of previous months.
For future months, use x + (n) to reference a number of future months.
For parent accounts make sure to select the total numbers instead, for example: Account Balance: Total Income
Date Functions
Learn how to use date function and variables on Prune.
Quarterly
Returns a value only on the first month of each quarter, otherwise returns zero. Passing 2 or 3 as the second argument will cause it to return a value only on the 2nd/3rd month of each quarter.
Month Number
Returns the month for the referenced column as a number between 1 and 12
Month Value
Returns the month and year for the referenced column in the format YYYY-MM.
Days in Month
Returns the number of days in the month of the referenced column.
Weekdays in Month
Returns the number of weekdays in the month of the referenced column.
Months from Today
Returns the number of months between the current date and the date of the cell being calculated.
Hiring Plan Functions
You can pull hiring plan numbers to use them on the Models feature.
Employee Count
Payroll.Employee Count. The employee count for active employees on the hiring plan. Can reference the total or narrow down by department or role.
New Hires
Payroll.New Hires. The number of new hires on the hiring plan in a given month. Can reference the total or narrow down by department or role.
Total Base Salary
Payroll.Total Base Salary. The sum of all employees’ salary on the hiring plan. Can reference the total or narrow down by department or role.
Contractor Count
Payroll.Contractor Count. The contractor count for active contractors on the hiring plan. Can reference the total or narrow down by department or role.
New Contractors
Payroll.New Contractors. The number of contractor new hires on the hiring plan in a given month. Can reference the total or narrow down by department or role.
Ranges and Aggregates
You can get a range of values and adjust them to show aggregates.
Ranges
Specify two arguments inside the brackets.
Revenue[x:x-5]
This example produces a list of all values of “Revenue” between the cell’s month and 5 months before (inclusive). In order to do math with the results, you must use a helper function to turn the list into a single value. The dates used for ranges are relative to each cell, so they can’t be used to compute running totals over all time. For that, use a running total formula like:
Test Formula[x] + Cumulative Variable[x-1]
Sum
Returns the sum of a list of values.
Average
Returns the average (mean) of a list of values.
Median
Returns the median of a list of values.
Min
Returns the smallest of a list of values.
Max
Returns the largest of a list of values.
Rounding
Here’s a list of helpful commonly used pre-built functions to use in the formula section.
Round
Rounds to the nearest whole number.
Round Up
Round_up. Rounds up to the nearest whole number.
Round Down
Round_down. Rounds down to the nearest whole number.