IDSB Banner

Lesson Three: Recurrence Formulas


A recurrence (also known as recursion) formula is one that refers to cells based on their relationships with other cells using keywords such as [THIS], [PREV], [NEXT], [FIRST] and [LAST].

Here's an example: Quarter[THIS] = Quarter[PREV]*1.2. This formula says: "This quarter equals the previous quarter multiplied by 1.2".

New users commonly ask: "If there isn't a specific cell reference, how does Quantrix know to which cell "[THIS]" is referring?" The answer: [THIS] is a moving target. When the formula is computing Quarter2, [THIS] is Quarter2 and [PREV] is Quarter1. When computing Quarter3, [THIS] is Quarter3 and [PREV] is Quarter2. The next Illustration has an example recurring formula.




Goal of Formula:
Assume that car sales will be the same each quarter.
Describe in Real Language: This quarter equals the previous quarter. Do not include Total or YTD.
Formula:
Quarter[THIS]=Quarter[PREV]skip YTD, Total

It is likely that a growth rate factor needs to be incorporated into the formula. Taking that into consideration:

Goal of Formula:
Assume that car sales will grow by 20% each quarter.
 Describe in Real Language: This quarter equals the previous quarter multiplied by 1.2. Do not include Total or YTD.
Formula:
Quarter[THIS]=Quarter[PREV] * 1.2 skip YTD, Total

Before going back to your model, take a minute to describe the goal and write out the formula you need on a piece of paper, as per the above example. Assume a quarterly product growth rate of 1.2, do not include Total or YTD.



Now return to the Sales Projection matrix in your Widgets file. Use the following steps to write a formula that assumes a monthly growth rate of 1.2 for all products.
    Step 1. Add formula field #3 to the Formula Editor.
      Click on formula 2 (it will turn blue) and hit ENTER. You could also use the "Insert Formula" button in the tool bar at the top of the screen.
    Step 2. Activate the formula field.
      Double-click next to the "3." in the Formula Editor.
    Step 3. Enter the formula.
      Enter the formula, either by typing it in or by clicking on the Formula Bar items to add them to the formula. Hit ENTER.
    Step 4. Format the result.
      To format the number of decimals, use Format > Numbers in the menu bar or the format decimals button in the toolbar.
    Step 5. Save your model.
      Click File > Save.
When you are done, your matrix should look like this.


idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -