Formulas

In a plain spreadsheet, a forecast row is a chain of cell formulas. You write =C4*(1+$B$2) in one cell and copy it across 24 month columns. If you plan by region, you copy the block again for each region. A change to the logic means that you must edit every copy. A missed copy gives a wrong number and no error.

In Eigenn, a formula belongs to the variable, not to a cell. You write it one time. It names other variables, and Eigenn calculates it for every period in the model and for every item of the variable's dimensions. To change the logic, you edit one formula.

To write a formula, select the ƒ button (Add formula) in the Formula column of a variable row. Press Enter to save. Press Shift+Enter to start a new line. Use Formula templates to insert a starting expression, then press Tab to move to the next placeholder.

1. A formula reads other variables by name

You write variable names, not cell addresses. A name can contain spaces, such as Operating expenses. The editor suggests variables, functions, and keywords while you type.

When you rename a variable, Eigenn rewrites every formula in the model that uses the old name. Eigenn also finds the calculation order from the references, so the order of rows in the grid does not change a result.

2. One formula runs in every period

A formula has no column address. Eigenn evaluates the same formula in each period of the model. In each period, a plain reference reads the value of the same period.

When a connected source supplies actuals for a variable, the actual values stay in the actual periods. The formula calculates the forecast periods.

Sometimes one period needs different logic. On a formula row, select that period's cell and press F2. Write the formula for that period only. The cell then shows a small ƒ mark. Actual cells cannot take a period formula. To remove the period formula, open it again and select Use all-period formula.

3. Time references read other periods

Put a time reference in square brackets after a variable name. The reference moves with the period that Eigenn calculates.

A running balance uses [previous]:

Cash[previous] + Net income

The first period has no previous value. Use a test for blank to set a start value:

if Customers[previous] = blank then Starting customers else Customers[previous] + New customers

This test works when Blanks in data for Customers is Treat as blank. With Treat as zero, the first period reads 0, not blank.

If a variable names itself without brackets, Eigenn reads its previous period. Write [previous] anyway, because it makes the formula easier to read.

4. Conditionals choose between two results

Write a conditional as if <test> then <result> else <result>. The else part is necessary.

• Compare values with =, <>, <, <=, >, and >=.
• Join tests with and and or. Reverse a test with not.
• Use = to test for blank.
• Put a second if after else to test more cases.

if month = 12 then Annual bonus else 0

month gives the calendar month of the period, from 1 to 12. The formula gives the bonus in December and zero in the other months.

Long conditionals are easier to read on several lines. Add a note after // on a line. Eigenn ignores the note when it calculates.

5. A formula runs for each dimension item

When you break a variable down by a dimension, Eigenn runs the same formula one time for each item. In each run, a reference to a variable with the same dimension reads the same item. A reference to a variable without that dimension reads its single value.

For example, Revenue is broken down by Region and uses Units sold * Price per unit. Units sold has the Region dimension. Price per unit does not.

To read one item from any formula, name the item after the dimension: Revenue by Region[Europe]. The Dimensions concept explains breakdowns and totals.

Related

• Dimensions
• Data
• Scenarios
• Planning Models and Formulas
• Build a Driver-Based Planning Model
• Revenue forecasting