Skip to main content
All CollectionsTreasuryForecastFormula
How to master formula syntax in Agicap Cashflow ?
How to master formula syntax in Agicap Cashflow ?

Formulas in Agicap allow you to create dynamic forecasts and custom key performance indicators (KPIs) to enhance your cashflow planning.

Updated this week

➡️Important Concepts to know

  • One Formula per Category: You can have only one formula per category.

  • Date Range: The date range of a formula starts from the current period (offset 0) to future offsets. All forecast values within this range will be updated.

  • Applicability: A formula is set for all offsets, scenarios, and bank accounts.

  • Combining Formulas: To apply distinct formulas for specific scenarios, banks, or periods, combine formulas with manual KPIs.

➡️Understanding Formula Syntax

Classic Formulas

The basic syntax for formulas is:

keyword (object,offset)

  • Keywords: Specify what you want to calculate.

  • Objects: The category or KPI you're referencing.

  • Offsets: Numerical value indicating the time period relative to the current one.

1_ Choice of keywords:

  • value: Returns the value written in the corresponding cell in the Cashflow Plan (includes paid, expected transactions, and remaining).

  • paid: Sum of all paid transactions (darkest color on the Cashflow graph). Equals 0 for positive offsets.

  • expected: Sum of all expected transactions (medium tone color on the Cashflow graph). Equals 0 for negative offsets.

  • objectives: Value of the corresponding forecast only.

  • remaining: Amount remaining to complete the corresponding forecast (lightest color).

  • EndBalance: Returns the end balance of the given period for the entity or bank account.

  • CurrentBalanceOfAccount(bankaccount): Today's cash balance for the selected account (dynamic value, depends on today, not the offset).

2_ Choice of objects:

Objects can be a category or a key indicator (KPI). Select them from the menus above the formula text area. You can access any category or KPI (manual or based on a formula) already created.

3_ Choice of offsets:

Offsets determine the time period:

  • 0: Current time period.

  • Positive Integer: Future periods (e.g., +1 for next month).

  • Negative Integer: Past periods (e.g., -1 for previous month).

Offsets depend on the current date. For example, if today is January:

  • period 0 refers to January

  • period -1 refers to December

  • period 2 refers to March

➡️Using Mathematical Tools

Mathematical Symbols

  • +: Addition

  • -: Subtraction

  • *: Multiplication

  • /: Division

💡Note: Use commas for decimals (0,1 for 10%). Agicap does not display decimals in the Cashflow Plan, so multiply percentages by *100 if needed.

Mathematical Functions

  • IF(condition; value_if_true; value_if_false): Returns value_if_true if the condition is met, else value_if_false.

    • Logical Symbols: <, >, <=, >=, =

    • Example: IF(value(categoryA; -1) > 0; value(categoryA; -1) * 1,2; 0)

  • MAX(value1; value2; ...): Returns the maximum value among the arguments.

    • Example: MAX(1000; value(categoryA; 0))

    • Tip: Use MAX(your_formula; 0) to prevent negative forecast values.

  • MIN(value1; value2; ...): Returns the minimum value.

  • AND and OR: Used within IF functions to check multiple conditions.

    • Syntax: condition1 AND condition2

    • Example: IF(value(categoryA; 0) > 0 AND value(categoryB; 0) > 0; 1000; 0)

  • AVERAGE(value1; value2; ...): Calculates the average of the arguments.

    • Example: AVERAGE(value(categoryA; -1); value(categoryA; -2))

  • Additional functions like LOG, EXP, FLOOR, CEILING, etc., behave similarly to Excel functions.

➡️Advanced Formulas

Special Keywords

CurrentBalanceOfAccount(bankaccount)

  • Retrieves today's cash balance for a specific account.

  • Useful for performance ratios like current runway or liquidity ratios.

  • Example: Calculating adjusted cash balance.

EndBalance(offset)

  • Returns the end balance for the specified offset for the entity or bank account.

  • Example: EndBalance(0) returns the current period's end balance.

EndBalanceOfAccount(account; offset)

  • Returns the end balance for a specific bank account at a given offset.

  • Example: EndBalanceOfAccount(Account1; 0)

Project Management Formulas

If using the Project Cashflow Management feature, you can reference expected transactions attributed to a specific project:

  • ExpectedOfProject(category; project; offset): Retrieves expected transactions within a project.

  • IgnoredExpOfProject(category; project; offset): Retrieves ignored expected transactions within a project.

➡️Examples of Complex Formulas

Increase a value by 10% every month

value(categoryA; -1) * 1,1

Set supplier forecasts to 40% of client sales

value(total_clients; 0) * 0,4

Calculate month-over-month variation

((value(categoryA; 0) - value(categoryA; -1)) / value(categoryA; -1)) * 100

Compute the percentage of client turnover in total sales

(value(clientX; 0) / value(total_sales; 0)) * 100

Handle cases with no past values using conditional logic

IF(value(category; -1) = 0; (value(category; -1) + 1000) * 1,2; value(category; -1) * 1,2)

➡️Avoiding Syntax Errors

  • Decimal Numbers: Use points . instead of commas , when writing decimal numbers. For example, write 1.5 instead of 1,5.

  • Function Arguments: Use commas , instead of semicolons ; in functions like IF or MAX. Example: IF(condition, value_if_true, value_if_false).

  • Always close parentheses ) and ensure all arguments are assigned in functions. For instance, include the else part in an IF function.

  • Limit Nesting: Avoid nesting too many fields inside a formula, as excessive nesting is not supported.

Q&A ❓

How can I reference the current cash balance in a formula?

Use CurrentBalanceOfAccount(bankaccount) to retrieve today's cash balance of a specific account. Remember, this value is dynamic and depends on the current date.

Can I create formulas that vary by scenario or bank account?

Formulas apply to all scenarios and bank accounts. To have different formulas for specific scenarios or accounts, combine formulas with manual KPIs.

How do offsets work in formulas?

Offsets indicate the time period relative to the current one:

  • 0: Current period

  • Positive integers: Future periods (e.g., +1 for next month)

  • Negative integers: Past periods (e.g., -1 for previous month)

Keep in mind that offsets are based on the current date.

What should I do if I encounter a syntax error?

Common syntax errors include:

  • Using a dot . instead of a comma , for decimals.

  • Forgetting to close parentheses.

  • Not assigning all arguments in functions. Review your formula to correct these errors.

If you need further assistance, don't hesitate to contact our chat.

Now you're ready to harness the full power of formulas in Agicap to enhance your cashflow planning! ✅

Did this answer your question?