Title: Predictive Cost Rates
Many companies need the ability to predict or forecast labor costs while factoring in an estimated pay increase for cost of living increase and raises. This is particularly important if you are using Unanet to develop multi-year cost proposals and/or to forecast existing projects. The two Options below describe how to handle an estimated lift in pay, either via:
- Option 1, using a query tool such as MS Access, the Unanet IMU (integration management utility), and the actual pay rates in the accounting system. The IMU is configured to kick off the process that generates the person import file and the import process itself which updates the Person Profile Rates tab.
- Option 2, using Cost Elements in the Cost Structures.
What’s covered in this document:
OPTION 1 - Updates to the Person Profile Cost Rate
MS Access or query tool
A database query tool such as MS Access can be used to capture the following values:
- % increase: This is the % that you want to apply for future years. For example, you may choose to apply a 3% increase year over year.
- Anniversary Date of increase: This is the month and date that you want the escalation to occur (the date application of the % increase, or the escalation anniversary). This may be the same MM/dd for everyone, or it may be the person's hire anniversary, for example.
- Category/filter to delineate the escalation: This could be any aspect to apply the increase. For example, you can have custom logic based on cost element, person org, or default person labor category.
- As an example, you may know that you apply different percentage escalations or timing/dates of escalation by department, or by labor cost element. Perhaps one labor cost element gets a 3% increase and another labor cost element gets a 4% increase.
- Number of years to go forward: This is the value that determines how many years in the future for which rates will be created. This can be handled either via multiple rows in the table or via one value that drives the number of years, e.g., "3".
The person rate data will be formatted in the Person Import file format required by Unanet. See Sample File screenshot below as well as the Help Documentation link with more details on the Person Import.
Number of Person import files
Two person files containing rates will be imported:
- The first file will represent the current actual pay rates ("begin date" in the Unanet Person Profile Rates tab or "effective date" in the person import file).
- The second file will represent the predictive pay rates where dates are only in the future.
If you have an inbound integration from an outside accounting or HR system:
- First File: the regular schedule of import files will contain the regular person import of your inbound integration (first file).
- Second File: the special predictive person rates import (second file). Make sure that the second special predictive cost rate person import is ordered to occur after the first file which is the standard person import in the IMU.
If you do not have an inbound integration into Unanet from an accounting or HR system:
- First File: in this case, the first file will be generated via the IMU via a Unanet Person Export containing the most recent cost rate (by setting the appropriate effective date in the Unanet export logic). This exported file can be a simplified Person export with just the aspects required (username, effective date, cost rate, and any other). In this case, the import of the first file will be triggered on demand and is typically not scheduled. For example, you may trigger this process when you are getting ready to submit a proposal.
Sample export logic used to generate the first file may look something like this:
- Second File: The first file will be followed by the special predictive person rates import (second file). Make sure that the second special predictive cost rate person import is ordered to occur after the standard person import in the IMU. See the sample file below.
Sample Files and File Recommendations
For any First or Second files that have multiple rows (a schedule of rate increases):
- Be sure the file lists the earliest dates first. Per the Help documentation link below regarding Person Import, "If you are importing multiple records for the same person (for example to reflect escalating rate increases over time), be sure to have the input file sorted with the earliest effective dates appearing in the file first."
For the Second file:
- The second person import file can contain just the key columns you require; it does not need to be the full Unanet Person import file with all columns. Minimally the following are required: username, cost_rate, effective_date. You may additionally add columns for exempt_status, cost_structure, and cost_element. As an example, your second file might look like the sample file below. This is file has fewer columns than all person import file options as we are focusing only on cost, date, and possibly other parameters (does not have columns for business week, time period, etc., as those are not related to this effort.) Note that the earlier dates are listed first, which is a requirement upon import if you want all rate records to apply:
The resulting Person Profile Rates tab would then look like this:
IMU (Integration management utility) configuration
The IMU pre-processor must be configured to trigger the process (MSAccess, for example) which generates the file. This trigger may be a batch file or other trigger. Please see the IMU help documentation link below for details for configuring the IMU.
- For the first file which applies to current and past cost rate, you will likely want the rate records to apply to locked periods on the Person Import; you will make these settings directly in the IMU and they correspond with the Person import settings in the screenshot below. This setting of "Allow rate adjustments for extracted time" is recommended in the event that the accounting system itself has an incorrect or outdated pay rate, since once the rate is corrected in the accounting system it will fix the cost in Unanet via time adjustments.
- For the second file which includes rates for the future, you will likely elect to leave "Allow rate adjustments for extracted time" off so that you can screen any record that is attempting to update time data in Unanet. (Records that would otherwise attempt to update locked timesheets would error out, which is a safety feature to prevent updates to locked time).
Note that if you do not have an inbound integration from an outside accounting or HR system, then the IMU will be used to generate the first person import file using the export feature of Unanet (see Number of Files section above).
Running the process
If you have the process scheduled, then there is no need to run it manually. If you instead are running the process manually, then you will run it either from the IMU folder itself, or from Task Scheduler, or from some other process you create to trigger the process.
Stored Procedure option
If you are hosting Unanet on your own servers ("On Premise"), then you may elect to use a stored procedure that writes the rate escalations directly to the Unanet database instead of applying those escalations via the Import process. The Import process if preferable since the imports are validated, while direct edits to the database are not validated.
Preventing predictive rates from being applied to real time
You will want to update the predictive cost rates in Unanet in a timely fashion such that they reflect actual raises. For example, you may estimate a predictive 3% lift for the next three years, but at the end of this year it may be determined that some people are getting more than 3% and others getting less than 3%. You will want to ensure that the actual increases are applied to replace the estimated predictive increases.
For any Unanet system where pay increases are not manually entered into Unanet but updated as an import into Unanet based on data from an accounting or payroll system, the following will happen automatically if Option 1 is configured:
- The 1st import file wipes out the predictive rates entirely and puts in any actual cost rates.
- The 2nd file puts back in the forecasted rates based on the logic in the query tool. Note that a query is checking for today's date: if the date in the file is earlier than or equal to today, it is not used.
On the other hand, if actual pay increases are not updated as an import into Unanet from an accounting or payroll system, then you will need a process (likely manual) to update the person profile cost rate for actual pay increases.
OPTION 2 - Handling the lift in the Cost Structure
This method handles the increase via a Cost Element.
Create Indirect Cost Element(s)
If you only have one type of lift, then one cost element is sufficient. If you have different types of lifts (e.g., 3% for some people, 4% for others, etc.) then you can create multiple Cost Elements. The example below addresses one type of lift across the organization using a Cost Element called "Estimated Increase".
Add this Cost Element(s) into the Cost Structures with the appropriate estimated lift, against the appropriate Labor Cost Elements ("Direct Labor")
Place the appropriate lifts in the Cost Structure - Rates tab
For Current Fiscal Year (FY17 in the screenshot below):
if the pay increases have already been applied in the person profile, the current Fiscal Year will have 0 for the Cost Element "Estimated increase"
if the pay increases have YET to be applied in the person profile, the current Fiscal year will still have the appropriate % (in this case 3%) for the Cost Element "Estimated Increase"
For Future Years (FY18 and FY19 in the screenshot below):
Enter the estimated increases. For example, 3% in one year, 6.09% in the following year
In the example below, where the current year is 2017 and pay rates in person profiles have already been applied, the Estimated Increase has been changed to 0
- Note: If the current year (in this case, 2017) has already had pay increases applied over in the person profile, the Cost Structure must be updated for current (and prior) years to be 0 for Estimated Increases.
Map to the Cost reports as necessary.
When actual pay increases are given, enter updates to pay in the appropriate Person Profiles. Follow up by removing the lift from the Cost Structure's Estimated Increase for current fiscal year, as outlined in the screenshot for Indirect Cost Rates above.
Optionally, you can schedule lifts on ODC's to address inflation similarly for planning purpose
Pros and Considerations of the Options
(Edits via import to person profile cost rate)