Add Spread Lookups to Modeled Sheets

Create Custom Dimension Values. (f you're using custom dimensions for the spread lookup.)

Add Text Selectors to Modeled Sheets. (If you're using text selectors for the spread lookup.)

Start a New Sheet or Get to the Sheet Summary to Edit Sheets.

Security: Model includes sheets, accounts, dimensions, and formulas
permission.

A spread lookup is an optional modeled sheet function. With spread lookups, you create expressions that describe how to spread a total value from 1 account across time in another account. You can add spread lookups to

Dimension

columns or

Text Selector

columns. The spread expression must always equal 1.

Example: You can spread a value evenly over 10 months with this expression 1:10=1/10. To check that the expression equals 1:

1/10*10

10/10

You can also create more complex expressions. Example: The spread is 20 months, the expression is 1:10=1/40,11:20=3/40. 1/4 of the value spreads evenly into the first 10 months, and 3/4 of the value spreads evenly into the last 10 months. To check that the expression equals 1:

(1/40*10) + (3/40*10).

10/40+ 30/40

40/40=1

Spread lookups require:

Modeled sheets.

Dimension

Text Selector

columns: You add the spread lookup to the column. From the sheet view, users select 1 of the options to trigger the spread expression. Example: The dimension,

Asset Class

, has 3 values: Electronics
, Equipement
, and Real Estate
.

Spread expressions: You create an expression for each dimension value or text selector option. Then, when users select the value from the modeled sheet, the model knows which expression to use. Example: Electronics depreciate at 1/10 of the purchase price over 10 months. The expression is 1:10=1/10.

Source accounts: The value in the source account is what the spread lookup distributes over time. Example: Name the

Timespan

column Purchase Price
. Then users enter the cost of various assets for each month.

Calculated accounts: The calculated account references the spread lookup in the formula field. The calculated account holds the distributed values of the source account. Example: Name the calculated account Depreciation
. The value in the Purchase Price
distributes into the Depreciation
account over a time period.

Some use cases for spread lookups:

Capital depreciation: Different assets are dimension values which are associated with a depreciation expression. Users enter purchase prices in the timespan and a depreciation account calculates the spread according the depreciation expression in the spread lookup.

Sales revenue recognition: Different sales are text selectors in a row, which are associated with a revenue recognition expression. Users enter the sales value and the revenue account calculates the spread according the recognition expression.

Sales invoicing: Different invoice types are text selectors, which are associated with an invoicing expression. Users enter sales value in the timespan, and accounts receivable calculates the spread according the invoice expression.

From the

Sheet Summary

, click

Columns and Levels

From the canvas, highlight the custom dimension or text selector.

In the

Spread Lookups

section, click

Add new spread lookup

Enter a name for the lookup.

This name is what you will reference in the calculated account that holds the distributed values.

Save the sheet.

You must save the sheet before adding the expressions for the spread lookup.

Highlight the text selector column or custom dimension column that has the spread lookup.

In the

Spread Lookups

section, click the

Edit

icon next to the spread lookup name to display the list of dimension values or text selector options.

For each value, add:

選項	說明
Spread	Enter how many time periods to spread the value. Example: For a calendar with Months as the smallest period, you can enter 6, which means spread the value over 6 months.
Spread Expression	Enter the mathematical expression so that the total equals 1. Use colons to define the start and end periods. Example: 2:6 means that the spread values start 1 period after the data entry period and ends on the 6th period. For data entered in January, the spread distributes Feb-June. Enter an equal sign after the start and end periods. Example: 2:6= Use fractions or decimals to define the distribution making sure that it equals 1. Example: 2:6=1/5. Because we're skipping the first month we need the value to spread over just 5 months. To add another expression, separate the expression with a comma or semicolon depending on your locale. For USA, use commas.

選項

說明

Spread

Enter how many time periods to spread the value. Example: For a calendar with Months as the smallest period, you can enter 6, which means spread the value over 6 months.

Spread Expression

Enter the mathematical expression so that the total equals 1.

Use colons to define the start and end periods. Example: 2:6 means that the spread values start 1 period after the data entry period and ends on the 6th period. For data entered in January, the spread distributes Feb-June.

Enter an equal sign after the start and end periods. Example: 2:6=

Use fractions or decimals to define the distribution making sure that it equals 1. Example: 2:6=1/5. Because we're skipping the first month we need the value to spread over just 5 months.

To add another expression, separate the expression with a comma or semicolon depending on your locale. For USA, use commas.

When you select Spread
for the

Type

in any calculated account on the same modeled sheet, the name of the spread lookup dislpays in the drop-down menu. You can select it to indicate that the calculation should spread the value of the source account according the spread lookup expressions.

Spread (months)	Spread Expression	Description
12	3=1/4,6=1/4,9=1/4,12=1/4	Spread the value evenly into 4 months: the 3rd, 6th, 9th, and 12th.
12	1=1/6,3=1/6,5=1/6,7=1/6,9=1/6,11=1/6	Spread the value evenly over the next 12 months into every other month.

Refer to Spread Lookups from a Calculated Account.

To edit spread lookups:

Click

Spread Lookups

from the

Sheet Summary

screen.

Click the spread lookup that you want to edit.

From the list of values, highlight the value that you want to change.

From the left panel, edit the expression and save.