Models Formula Reference

Models Formula Reference

Here's a list of helpful commonly used pre-built functions to use in the formula section.

Rounding

Round

Rounds to the nearest whole number.

Round Up

rounds up to the nearest whole number. CEIL is another name for this function.

Round Down

Rounds down to the nearest whole number. FLOOR is another name for this function.

If / Then / Else

This formula is useful if you want to set a condition and have 2 results: one if the result is true and the second if the result is false.

Format is CONDITION ? RESULT_IF_TRUE : RESULT_IF_FALSE

Condition Syntax

Use the following syntax to create condition statements:

Use Case - Keeping Track of Actual Customers

In this example, we are using an if/then/else statement to keep our number of customers accurate with actual data and use it to forecast future customers. The "Actual Customers" is a list variable of our historical customers each month. This if/then/else statement is checking that if "Actual Customers" is greater than 0 in the current month, then use that value from the list for the formula "Customers" but if "Actual Customers" is equal to or less than 0, use the previous month's value for "Customers" plus the current month's expected value for "New Customers" minus the current month's expected value for "Churned Customers" to find the number expected customers in the month.

Tip
You can apply the same logic to keep track of your New Customers and Churned Customers

Use Case - Utilizing Month Variable

In this example, we are using an if/then/else statement to track our expected customers after our product launch date. The "Month Value" is a prebuilt variable that outputs the current month and year. We created a date input for our product launch date and a number input for our expected number of new customers every month. Our if/then/else statement is checking if the current month is or after the "Product Launch Date", then use the value of "Monthly New Customers" but if the current month is before the the "Product Launch Date" then use 0 since we wouldn't have any customers before we launch our product.

Ranges and Aggregates

Ranges

Specify two arguments inside the brackets.

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:

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.

Date Functions

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

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

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

The sum of all employees' salary on the hiring plan. Can reference the total or narrow down by department or role.

Contractor Count

The contractor count for active contractors on the hiring plan. Can reference the total or narrow down by department or role.

Contractor New Hires

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.

Tip
You can also breakdown contractor by departments similar to the employee side.

Account Balance

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.

Time Variable

The time variables refer to the variables next to each of the inputs and formulas

Preview Month [x]

The "x" time variable refers to the current month of the model. You can also reference the future or past months by using x+(n) or x-(n) as used in the Account Balance financial report function.

Current Month [c]

The "c" time variable refers to the current month as of today.

For this example, we are referencing the current month's Revenue Subscription, but we can also use c+(n) or c-(n) to reference the future or past month's Revenue Subscription.

Here we were able to reference the next month's expected Revenue Subscription by using the c+1 time variable.

Year to Date [ytd]

The "ytd" time variable means "year to date." Similar to the Range function, this ytd time variable will also display "Multiple Values."

In this example, we utilized the ytd time variable to reference each month's revenue subscription from January 2021 to September 2021 and then used the Average function to find the Average Revenue within this timeframe.

Tip
You can adjust for Fiscal Years on the YTD time variable by adding a number {2-12}. For example, if your fiscal year ends in June 30th, use ytd7 to calculate year to date values starting from July.