Time Spreads
The objective of time spreadkeys is to allocate revenues and cost over time in input forms. For example, the end-user enters 10 000 in an input summary column for the total period (Spread value SPVAL), the whole year, or full quarter, etc. Bizview can then allocate this total amount over the underlying periods using a time spreadkey that the end-user selects for that specific row.
Access time spreadkeys by selecting Client Data > Spreadkeys:
A time spreadkey can use two type of data as the base for the allocation:
- Manually entered weights registered in association with the a spreadkey.
- 'Dynamic' weights calculated based on other columns in the input form (e.g. Last Year LY).
This topic contains the following sections:
Manual Weights
The manual method is easy to use and understand. In the below example, there are three time spreadkeys that all allocate according to weights for ISOMONTH: Even, S1, and S2. Any row in any input form that uses these time spreadkeys will get the related total amount allocated to ISOMONTH according to the 'weights' defined in the system. The base for the allocation is stored within the Bizview application database in a table and retrieved to the input form when needed.
Time Value | EVEN | S1 | S2 |
---|---|---|---|
201501 | 100 | 75 | 120 |
201502 | 100 | 78 | 125 |
201503 | 100 | 79 | 129 |
201504 | 100 | 120 | 85 |
201505 | 100 | 100 | 90 |
201506 | 100 | 75 | 110 |
201507 | 100 | 70 | 128 |
201508 | 100 | 80 | 120 |
201509 | 100 | 95 | 100 |
201510 | 100 | 115 | 80 |
201511 | 100 | 110 | 75 |
201512 | 100 | 100 | 64 |
Below is the formula used by Bizview to allocate according to these weights:
Individual period cell value = Spread value * (Per Weight / Sum Weight)
Dynamic weights
The 'dynamic' method is smarter, but a bit more complex to understand. For example, if you want each account in a result budget to be allocated over the next year periods, based on the actual cost allocation this last year, you can use a dynamic spreadkey. The data used to calculate the individual period weights are not stored in the database. Instead, these columns need to be available in the input form as extra, or help value columns. The columns that should be used for calculating the spread weights are then linked using the same spreadgroup (SPGROUP).
In the example below, we have SPGROUP 'SP1' defined for columns January - December year 2020 (Actuals from last year). The same SPGROUP is used in our SPVAL, SPKEY and January - December 2021 budgeting columns.
In the above example, we have entered the same total as actuals for last year, just to visualize that the individual periods Jan - Dec will get the same amount (calculated weight).
To create a new manual spreadkey, select New item > Spreadkey in Client Data:
A Create New Spreadkey dialog displays, as shown below:
Below is a list of spreadkey fields:
Field | Description | Example | Note |
---|---|---|---|
ID | The unique identifier of the spreadkey. | LAST YEAR | Keep short, but it must be easy to understand for end-users due to the fact that this ID is what is visible in the input form spreadkey column. |
Description | The description of the spreadkey. | Last year | Also visible when end-user press the space button when in the spreadkey column in an input form. |
Time Unit | Defines to what granularity of the time dimension the spreadkey should do the allocation. | ISOMONTH | For example, to isomonth, isoquarter, etc. |
Once you've completed the fields listed above, select Create.
A spreadkey template displays, as shown below:
The right panel contains the Spreadkey Settings, described below:
Field | Description | Example | Note |
---|---|---|---|
Spreadkey ID | The unique identifier of the spreadkey. | LAST YEAR | Not editable once created. |
Description | The description of the spreadkey. | Last year | Also visible when end-user press the space button when in the spreadkey column in an input form. |
Time Unit ID | Defines to what granularity of the time dimension the spreadkey should do the allocation. | ISOMONTH | Not editable once created. |
Active | Defines if the spreadkey is active and possible to use or not (ON/OFF). | ||
Spreadkey Type | Defines if the spreadkey is Manual (fixed), or Calculated (dynamic) based on data in the form. | 1 Weights are calculated | |
Derive Weights From | If spreadkey-type is Manual (fixed), you can inherit the allocation base data from another client. | ||
Spreadkey should only be valid for: | Makes it possible to limit what object the spreadkey should be available. | ACCOUNT | For example, if you want to limit the spreadkey to only be available when the object 'ACCOUNT' is on rows in an input form, but not available for PRODUCTS, CUSTOMERS, etc. If undefined, the key will be available for all objects within the client. |
From .. To | Makes it possible to limit to what object values the spreadkey should be available. | 3000 - 3999 | For example, if you want to limit a spreadkey to only be available for revenue accounts from account number 3000 to 3999 and similar. If not defined the key will be available for object values. |
Don't forget to select Save!
For spreadkeys with manually entered weights - use the export and import function to easily manage the weights that should be used.
- To export all weights for the client, select Export from the ribbon.
- Open the generated excel file and edit the data.
- Import the file back to Bizview using the Import option in the ribbon.
Usage
The spreadkeys themselves are not used directly in the template design when designing input forms. To enable the end-user to work with them, you need to set them up first. This functionality, however, is very much related to the SPKEY and SPVAL columns and the SPGROUP row parameters when working with form design.