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
•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 cost-related 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
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
•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.
•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
•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