<< Click to Display Table of Contents >> Calculate Advanced Costs_detailed 
Copyright © 2018 EarthSoft, Inc • Modified: 12 Dec 2018 

The Advanced Costs feature enables advanced cost calculations. Advanced costs are calculated based on the following input parameters:
•Groups of samples, each group representing a different tier of costs
•Numbers of samples in a group
•Turnaround Time
•Report Level
•Discount factors, depending on the number of samples in a group.
Discount factors allow for a variety of scenarios, characteristic to laboratory rules for contracts. Advanced Costs are calculated based on several Discount Factors, as enumerated here:
•base  The standard price per sample
•base_number_of_samples  The minimum number of samples to be allowed to have discounts based on sample volume
•base_disc  The price of the sample with the sample volume discount
•base_number_of_samples2  Minimum number of samples to be allowed to have an additional discount
•base_disc2  The price of each sample with the second rate of sample volume discount
•base_number_of_analytes  The Maximum quantity of analytes per sample with no extra costs
•extra_analyte_cost  Cost of each extra analyte that exceeds the base_number_of_analytes
•extra_analyte_disc  Cost of each extra analyte that exceeds the base_number_of_analytes, but for samples that exceeded base_number_of_samples
•overall_number_of_analytes  The minimum number of analytes to be allowed to have discounts
•discount_percent  Percent of discount per analyte for a sample that exceeded the overall_number_of_analytes
•discount_amount  Amount of discount per analyte for a sample that exceeded the overall_number_of_analytes
•base_disc_mag_YN  When set to Y, the discount will be calculated for samples sent per MAG; when NULL/N, it will be calculated per all samples sent to the lab.
Advanced Costs data can be entered into EQuIS in two ways :
1.Directly into the EQuIS database through EDP, by using a specially designed EDD file and SPM format
2.Via Advanced Costs Form in SPM
Advanced cost parameters may be uploaded to the EQuIS Database using the SPM_REF EDD format installed by default in the directory \Program Files\EarthSoft\SPM\Format\SPM\
An example EDD named "SPM_Example_EDD.xlsx" is supplied in the same folder.
Location of SPM_RE.xsd format and SPM_Example_EDD.xls
The SPM EDD file with costs consists of the following costrelated tabs:
•rt_spm_cost_calc, designed to populate data into reference table rt_spm_cost_calc
•rt_spm_costs_calc_mag, designed to populate data into reference table rt_spm_costs_calc_mag
•rt_spm_container, designed to populate data into reference table rt_spm_container
•smp_task_cost
rt_spm_cost_calc tab in SPM EDD cost file
rt_spm_cost_calc_mag tab in SPM EDD cost file
rt_spm_container tab in SPM EDD cost file
Upon uploading the SPM EDD into the database, rt_spm_cost_calc, rt_spm_cost_calc_mag and rt_spm_container are populated.
rt_spm_cost_calc in EQuIS
rt_spm_cost_calc_mag in EQuIS
Advanced Cost Settings can also be setup in the rt_spm_cost_calc table, to allow for management of the costs related to A/QC samples.
The significance of the OA/QC discount is described below:
Significance of QA/QC costs settings in rt_spm_costs_calc
Another way of entering cost data into EQuIS is by directly using the Advanced Costs form in SPM. The data entered in this form consists of the following parameters:
•Costs per group of samples, including discount depending on number of samples
•Turnaround Time
•Report Level
•Number of Samples.
Using the Advanced Costs form directly in SPM does not allow for use of the more complex Discount Factors.
Advanced Cost Form workflow:
•Select a company and one or more analyte groups
•Enter cost information
•Enter Turnaround Time (Days) and Report Level
•The # Samples field is for the numbers of samples that would represent different tiers of costs.
•Click Save.
•Creating a row in RT_SPM_COST_CALC and RT_SPM_COST_CALC_MAG.
Advanced Costs form in SPM
The information entered in the Advanced Costs forms is stored in rt_spm_cost_calc_mag, rt_spm_cost_calc tables.
Based on the parameters stored in the rt tables, advanced costs can be calculated. The calculations rely in setting up in SPM:
•Laboratory Contract with Advanced Costs
•Tasks where collected samples are assigned to the Laboratory Contracts with Advanced Costs.
The Advanced Cost option in the lab contract allows for estimating cost:
•By Method Analyte Group (MAG) or
•At the analyte level.
Setting up Contract Items for Discounts applied to MAG or Analyte
Contract MAG details, with Advanced Cost
Contract Data Saved in EQuIS rt_spm_cost_calc
Contract Data Saved in EQuIS rt_spm_cost_calc_mag
After the Laboratory Contracts with Advanced Costs were created, Tasks are created. Within a Task, the collected samples are assigned to an existent Laboratory Contracts with Advanced Costs.
Task Containg Samples Assigned to Laboratory Contracts with Advanced Costs
Upon saving and scheduling the Task in SPM, the dt_spm_scheduled_sample_cost is populated with the calculated advanced costs.
dt_spm_scheduled_sample_cost populated with Calculated Advanced Costs
To analyze the obtained results as presented in Figure 16, a cost of $319.55 was calculated for a MAG consisting of six analytes. The six analytes pertaining to the one MAG are grouped together, with only the first analyte showing the actual cost displayed while the remaining analytes have costs assigned as "zero". This is an intended feature; all 6 analytes, grouped together under the MAG, cost $319.55.
Calculation = $(200 +49.86+49.86+49.86+49.86)*0.8 = $319.55
•1 sample
•6 analytes in one MAG
•Base = $200. The standard price per sample
•base_number_of_analytes = 2. The Maximum quantity of analytes per sample with no extra costs
•extra_analyte_cost = $49.86. Cost of each extra analyte that exceeds the base_number_of_analytes. With 6 analytes in total and 2 analytes as base , 4 analytes exceed the base number, hence a cost of 49.86 will be applied for these 4 analytes
•overall_number_of_analytes = 5. The minimum number of analytes to be allowed to have discounts. With 4 analytes in excess, we are within the allowable number of analytes to receive discounts
•discount_percent = 20%. Percent of discount per analyte for a sample that exceeded the overall_number_of_analytes. 6 analytes exceed 5, defined as overall_number_of_analytes. Accordingly, a 20% discount will be applied to all analytes.
Create a Costs Summary Report in SPM
Costs Summary Report in SPM