For Cost Estimate line items, it is possible to calculate different values for different years of assignment. This can be done by having additional line items which identify the different years and can be referenced in other line items. It is possible to apply different values for different years for a Cost Estimate line item.
First, upload the following two custom calculation spreadsheet templates to the Calculation Screen (click on the links to download the templates). Note: No edits are required to the templates. These will serve as a reference for the years, and a template for your custom calculation.
Year Aware.xlsx - to be used for "Year Reference" placeholder line item
Year Aware Calculation.xlsx - to be used for line item e.g. Housing Allowance
Next, create the "Year Reference" placeholder line item on an existing Cost Estimate template or new template. The line item should be set up with the following settings:
Section: Select a section named "Reference" or one that is similar. To add, edit or delete the standard sections, select <Cost Estimate Section> from the Lookup Field on the Lookup Maintenance screen.
Description: Enter "Year Reference" so that it appears on Cost Estimate screen and Cost Estimate report
Click on the Compensation/Custom Calculation (new) on the radio button and select the <Year Aware> calculation from the drop down list.
Annual Increase %: Specify "100%". This is so that the years can be differentiated by having a different value for year reference.
Exclude from Section and Subsection Total: Tick the checkbox to exclude the placeholder line item from the section and subsection totals within a Cost Estimate.
Exclude From Net Incremental Cost: Tick the checkbox to exclude the placeholder line item from the Net Incremental Cost totals within a Cost Estimate.
The "Year Reference line item will appear like this on the Cost Estimate screen.
When the "Year Reference" placeholder line item is set up, create the line entry that will have different values for different years on the same template. In the example below, this will be the Housing Allowance line item and will appear after / below the placeholder line item entry in the Cost Estimate so that the cascade feature can be used.
The line item should be set up with the following followings:
Click on the Compensation/Custom Calculation (new) on the radio button and select the <Year Aware Calculation> calculation from the drop down list.
To define the data source, select the report from the User Report for Input Values drop down list. The Column Caption in the selected User Report must match the Name of the <Year Aware Calculation> calculation spreadsheet. In this example, the user report would need an entry for each year that is possible in the spreadsheet 1-6 (Year1, Year2, Year3, Year4, Year5 and Year6) and CC_CURRENCY. Calculate each year as required and if a year is not required leave it in the report and use 0 (zero) in the SQL field.
To cascade the results of the "Year Reference" line item, select the <Year Reference> line item from the Get Value From (Line Item) lookup and select corresponding input from the Input Value to (Calculation Input) lookup i.e Year Reference, which is the Caption name used in the calculation spreadsheet.
The "Year Reference line item appear like this on the Cost Estimate screen.
The "Housing Allowance" line item will appear like this on the Cost Estimate screen.
