Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Edit Spread Lookups

A spread lookup translates entered text or a selected dimension from a row of the input sheet into values spread over time. These spread values can be used to generate values into one account based on another account.

Some example uses of spread lookups.

  • Capital Depreciation: Each asset row is associated with a depreciation method, which determines how an asset value entered in the current time period is spread into depreciation expense in future time period(s).
  • Sales Revenue Recognition: Each sales row is associated with a revenue recognition method, which determines how a sales value entered in the current time period is spread into revenue in future time period(s).
  • Sales Invoicing: Each sales row is associated with an invoicing method, which determines how a sales value entered in the current time period is spread intoaccounts receivable in future time period(s).

Before You Begin

  • Required permission: Model Management Access > Model
  • Create the modeled sheet. See Create Modeled Sheets.

How You Get There

Compass.png From the nav menu, select Modeling. In the Sheets menu, select User Assigned Sheets or Level Assigned Sheets to view the list of sheets in your model.

Edit Spread Lookups

You can edit a spread lookup from a modeled sheet's summary page.  Click Spread Lookups on Sheet Summary.  Or, you can edit lookups directly within the model sheet builder.

  1. Select the Edit link next a modeled sheet from the list of sheets. 

  2. From Sheet Summary, select Columns and Levels

  3. Select a custom Dimension or Text Selector column from the canvas (center pane).

  4. In Properties (right pane), expand Spread Lookups.

  5.  Find the spread lookup to edit and select Edit .

  6. For Spread (Months), enter a whole number greater than 0  Use a whole number greater than 0.

  1. For the Spread Expression, enter a fraction or decimal number. For example, enter a fraction like A:B=n/d,C:D=n/d where A and C are start month numbers, B and D are end month numbers. The spread proportion is n/d and can be entered as a fraction like 1/40, or as number like .025. 

  2. Save .  

The Spread Expression input area does not accept thousands separators in values. Only one range is required. Separate each range by the locale specific separator (either, or; depending on your locale).

You can associate more than one lookup to a text selector or dimension element. For example, a text selector element called Employee Type has options for Officer, Vice President, Director, Manager, Staff. You can associate this element with one monthly lookup to calculate the bonus expense and another lookup to calculate auto parking expenses. 

Example Spread Expressions

1:10=1/40,11:20=3/40
1:10=.025,11:20=.075
1:10=1/40,11:20=.075

Description Spread(Months) Spread Expression

A quarterly spread over the course of a year.

12

3=1/4,6=1/4,9=1/4,12=1/4

A spread over every other month in a year.

12

1=1/6,3=1/6,5=1/6,7=1/6,9=1/6,11=1/6

A sales ramp up to reach 100% of quota within the first year of 5 years. A newly hired sales person must sell 10% of their quota the first quarter, 50% the second quarter, 75% the third quarter, and 100% by the fourth quarter.

60

1:3=10/100,4:6=50/100,7:9=75/100,10:60=100/100

  • Was this article helpful?