{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["ACL1","Factor_Table","Version","User Notes"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":1,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"ACL1","index":0,"n_rows":71,"n_cols":24,"rows_kept":71,"cut":false,"hidden":false,"merged":[[29,20,29,21],[29,22,29,23],[29,12,29,13],[29,14,29,15],[29,16,29,17],[29,18,29,19],[45,2,45,3],[40,0,44,0],[46,0,49,0],[29,8,29,9],[29,10,29,11],[29,4,29,5],[29,6,29,7],[30,0,39,0]]}
["Penalty Calculation Methodology Worksheet - Version Date: 03/19/2025"]
[]
[null,null,null,null,null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge",null,"Degree of Toxicity of the Discharge"]
[null,null,null,"0 = Discharged material poses negligible risk","Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item"]
[null,null,null,"1 = Discharged material poses minor risk","Select Item",null,"Select Item",null,"Select Item","1 = Discharged material poses minor risk","Select Item","1 = Discharged material poses minor risk","Select Item","1 = Discharged material poses minor risk","Select Item","1 = Discharged material poses minor risk","Select Item","1 = Discharged material poses minor risk","Select Item","1 = Discharged material poses minor risk","Select Item",null,"Select Item"]
[null,null,null,"2 = Discharged material poses moderate risk","Select Item",null,"Select Item",null,"Select Item","2 = Discharged material poses moderate risk","Select Item","2 = Discharged material poses moderate risk","Select Item","2 = Discharged material poses moderate risk","Select Item","2 = Discharged material poses moderate risk","Select Item","2 = Discharged material poses moderate risk","Select Item","2 = Discharged material poses moderate risk","Select Item",null,"Select Item"]
[null,null,null,"3 = Discharged material poses above moderate risk","Select Item",null,"Select Item",null,"Select Item","3 = Discharged material poses above moderate risk","Select Item","3 = Discharged material poses above moderate risk","Select Item","3 = Discharged material poses above moderate risk","Select Item","3 = Discharged material poses above moderate risk","Select Item","3 = Discharged material poses above moderate risk","Select Item","3 = Discharged material poses above moderate risk","Select Item",null,"Select Item"]
[null,null,null,"4 = Discharged material poses significant risk","Select Item",null,"Select Item",null,"Select Item","4 = Discharged material poses significant risk","Select Item","4 = Discharged material poses significant risk","Select Item","4 = Discharged material poses significant risk","Select Item","4 = Discharged material poses significant risk","Select Item","4 = Discharged material poses significant risk","Select Item","4 = Discharged material poses significant risk","Select Item",null,"Select Item"]
[null,null,null,null,null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses",null,"Actual or Potential Harm to Beneficial Uses"]
[null,null,null,"0 = Negligible","Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item"]
[null,null,null,"1 = Minor"]
[null,null,null,"2 = Below Moderate"]
[null,null,null,"3 = Moderate"]
[null,null,null,"4 = Above Moderate"]
[null,null,null,"5 = Major"]
[null,null,null,null,null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement","Select Item","Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement",null,"Susceptibility to Cleanup or Abatement"]
[null,null,null,"Discharger cleans up > 50% of discharge in a reasonable amount of time","Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item"]
[null,null,null,"< 50% of Discharge Susceptible to Cleanup or isn't cleaned up timely"]
[null,null,null,null,null,"Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement","Select Item","Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement",null,"Deviation from Requirement"]
[null,null,null,null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item",null,"Select Item"]
[null,null,null,"Minor"]
[null,null,null,"Moderate"]
[null,null,null,"Major"]
[]
[]
[]
[]
[]
["Discharger Name/ID:"]
[null,null,null,null,"Violation 1",null,"Violation 2",null,"Violation 3",null,"Violation 4",null,"Violation 5",null,"Violation 6",null,"Violation 7",null,"Violation 8",null,"Violation 9",null,"Violation 10"]
["Discharge Violations","Step 1",null,"Potential for Harm Score (Generated from Button)"]
[null,"Step 2",null,"Per Gallon Factor (Generated from Button)"]
[null,null,null,"Gallons"]
[null,null,null,"Statutory Maximum per Gallon"]
[null,null,null,"High Volume "]
[null,null,null,"Total",null,{"$f":"IF(E35=0,E32*E33*E34,E32*E33*E35)","v":0},null,{"$f":"IF(G35=0,G32*G33*G34,G32*G33*G35)","v":0},null,{"$f":"IF(I35=0,I32*I33*I34,I32*I33*I35)","v":0},null,{"$f":"IF(K35=0,K32*K33*K34,K32*K33*K35)","v":0},null,{"$f":"IF(M35=0,M32*M33*M34,M32*M33*M35)","v":0},null,{"$f":"IF(O35=0,O32*O33*O34,O32*O33*O35)","v":0},null,{"$f":"IF(Q35=0,Q32*Q33*Q34,Q32*Q33*Q35)","v":0},null,{"$f":"IF(S35=0,S32*S33*S34,S32*S33*S35)","v":0},null,{"$f":"IF(U35=0,U32*U33*U34,U32*U33*U35)","v":0},null,{"$f":"IF(W35=0,W32*W33*W34,W32*W33*W35)","v":0}]
[null,null,null,"Per Day Factor (Generated from Button)",{"$f":"E32","v":0},null,{"$f":"G32","v":0},null,{"$f":"I32","v":0},null,{"$f":"K32","v":0},null,{"$f":"M32","v":0},null,{"$f":"O32","v":0},null,{"$f":"Q32","v":0},null,{"$f":"S32","v":0},null,{"$f":"U32","v":0},null,{"$f":"W32","v":0}]
[null,null,null,"Total Days"]
[null,null,null,"Statutory Max per Day"]
[null,null,null,"Total",null,{"$f":"E37*E38*E39","v":0},null,{"$f":"G37*G38*G39","v":0},null,{"$f":"I37*I38*I39","v":0},null,{"$f":"K37*K38*K39","v":0},null,{"$f":"M37*M38*M39","v":0},null,{"$f":"O37*O38*O39","v":0},null,{"$f":"Q37*Q38*Q39","v":0},null,{"$f":"S37*S38*S39","v":0},null,{"$f":"U37*U38*U39","v":0},null,{"$f":"W37*W38*W39","v":0}]
["Non-Discharge Violations","Step 3",null,"Per Day Factor"]
[null,null,null,"Total Days"]
[null,null,null,"Multiple Day Violation Reduction"]
[null,null,null,"Statutory Max per Day"]
[null,null,null,"Total",null,{"$f":"IF(E43>0,E41*E43*E44,E41*E42*E44)","v":0},null,{"$f":"IF(G43>0,G41*G43*G44,G41*G42*G44)","v":0},null,{"$f":"IF(I43>0,I41*I43*I44,I41*I42*I44)","v":0},null,{"$f":"IF(K43>0,K41*K43*K44,K41*K42*K44)","v":0},null,{"$f":"IF(M43>0,M41*M43*M44,M41*M42*M44)","v":0},null,{"$f":"IF(O43>0,O41*O43*O44,O41*O42*O44)","v":0},null,{"$f":"IF(Q43>0,Q41*Q43*Q44,Q41*Q42*Q44)","v":0},null,{"$f":"IF(S43>0,S41*S43*S44,S41*S42*S44)","v":0},null,{"$f":"IF(U43>0,U41*U43*U44,U41*U42*U44)","v":0},null,{"$f":"IF(W43>0,W41*W43*W44,W41*W42*W44)","v":0}]
[null,null,"Initial Amount of the ACL",null,null,{"$f":"F36+F40+F45","v":0},null,{"$f":"H36+H40+H45","v":0},null,{"$f":"J36+J40+J45","v":0},null,{"$f":"L36+L40+L45","v":0},null,{"$f":"N36+N40+N45","v":0},null,{"$f":"P36+P40+P45","v":0},null,{"$f":"R36+R40+R45","v":0},null,{"$f":"T36+T40+T45","v":0},null,{"$f":"V36+V40+V45","v":0},null,{"$f":"X36+X40+X45","v":0}]
["Add'l Factors","Step 4",null,"Culpability"]
[null,null,null,"History of Violations"]
[null,null,null,"Cleanup and Cooperation"]
[null,null,null,"Total",null,{"$f":"F46*E47*E48*E49","v":0},null,{"$f":"H46*G47*G48*G49","v":0},null,{"$f":"J46*I47*I48*I49","v":0},null,{"$f":"L46*K47*K48*K49","v":0},null,{"$f":"N46*M47*M48*M49","v":0},null,{"$f":"P46*O47*O48*O49","v":0},null,{"$f":"R46*Q47*Q48*Q49","v":0},null,{"$f":"T46*S47*S48*S49","v":0},null,{"$f":"V46*U47*U48*U49","v":0},null,{"$f":"X46*W47*W48*W49","v":0}]
[null,null,null,"Maximum for this Violation",{"$f":"E33*E34+E38*E39+E42*E44","v":0},null,{"$f":"G33*G34+G38*G39+G42*G44","v":0},null,{"$f":"I33*I34+I38*I39+I42*I44","v":0},null,{"$f":"K33*K34+K38*K39+K42*K44","v":0},null,{"$f":"M33*M34+M38*M39+M42*M44","v":0},null,{"$f":"O33*O34+O38*O39+O42*O44","v":0},null,{"$f":"Q33*Q34+Q38*Q39+Q42*Q44","v":0},null,{"$f":"S33*S34+S38*S39+S42*S44","v":0},null,{"$f":"U33*U34+U38*U39+U42*U44","v":0},null,{"$f":"W33*W34+W38*W39+W42*W44","v":0}]
[null,null,null,"Amount for this Violation",null,{"$f":"IF(F50=0,0,IF((E51)<F50,E51,F50))","v":0},null,{"$f":"IF(H50=0,0,IF((G51)<H50,G51,H50))","v":0},null,{"$f":"IF(J50=0,0,IF((I51)<J50,I51,J50))","v":0},null,{"$f":"IF(L50=0,0,IF((K51)<L50,K51,L50))","v":0},null,{"$f":"IF(N50=0,0,IF((M51)<N50,M51,N50))","v":0},null,{"$f":"IF(P50=0,0,IF((O51)<P50,O51,P50))","v":0},null,{"$f":"IF(R50=0,0,IF((Q51)<R50,Q51,R50))","v":0},null,{"$f":"IF(T50=0,0,IF((S51)<T50,S51,T50))","v":0},null,{"$f":"IF(V50=0,0,IF((U51)<V50,U51,V50))","v":0},null,{"$f":"IF(X50=0,0,IF((W51)<X50,W51,X50))","v":0}]
[]
[null,"Step 5","Total Base Liability Amount",null,null,{"$f":"F52+H52+J52+L52+N52+P52+R52+T52+V52+X52","v":0}]
[null,"Step 6",null,"Economic Benefit"]
[null,"Step 7",null,"Other Factors as Justice May Require",null,{"$f":"F54*E56","v":0}]
[null,null,null,"Staff Costs",null,{"$f":"F56+E57","v":0}]
[null,"Step 8",null,"Ability to Pay & to Continue in Business",null,{"$f":"F57*E58","v":0}]
[null,"Step 9",null,"Minimum Liability Amount",{"$f":"IF(E55=0,,E55*1.1)","v":0}]
[null,null,null,"Maximum Liability Amount",{"$f":"E51+G51+I51+K51+M51+O51+Q51+S51+U51+W51","v":0}]
[null,"Step 10","Final Liability Amount",null,null,{"$f":"IF(F57>E60,E60,IF(F57<E59,MIN(E59,E60),F57))","v":0}]
[]
[]
[]
[null,null,null,"Penalty Day Range Generator"]
[]
[null,null,null,"Start Date of Violation="]
[null,null,null,"End Date of Violation="]
[]
[null,null,null,"Maximum Days of Violation (Step 3) =",{"$f":"IF((ISBLANK(E68)),0,E68-E67+1)","v":0},"Days"]
[null,null,null,"Minimum Days of Violation (Step 3) =",{"$f":"IF(E70>=60,(ROUNDDOWN(36+((E70-60)/30),0)),(IF(E70>30,(ROUNDDOWN((E70-30)/5+30,0)),(IF(E70<=30,E70,\"x\")))))","v":0},"Days"]

{"sheet":"Factor_Table","index":1,"n_rows":4,"n_cols":11,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
[null,1,2,3,4,5,6,7,8,9,10]
["Minor",5.0000000000000001E-3,7.0000000000000001E-3,0.01,0.02,0.04,0.08,0.14000000000000001,0.2,0.3,0.35]
["Moderate",7.0000000000000001E-3,1.2999999999999999E-2,2.5000000000000001E-2,0.05,0.1,0.15,0.27,0.4,0.5,0.6]
["Major",0.01,0.02,0.04,0.08,0.15,0.28000000000000003,0.41,0.6,0.8,1]

{"sheet":"Version","index":2,"n_rows":29,"n_cols":2,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
["Date","Changes"]
["2010-06-09","Fixed calculation for violations 5-10"]
["2010-06-22","Fixed # of days calculation"]
["2010-06-23","Added workbook protection, formatting"]
["2010-06-24","Fixed Macro Error"]
["2013-11-19","Fixed # of days calculation to extend past 1100"]
["2014-01-10","Added several rows to calculate the maximum for each violation and to calculate max and min penalties"]
[null,"Added User notes"]
["2014-02-03","Corrected data validation for Culpability, Cleanup & Cooperation, and History of Violations entries"]
["2014-02-04","Removed workbook protection"]
["2015-03-11","Added row to days of discharge to be able reduce the number of days and to calculate correct maximum"]
["2015-03-20","Corrected daily discharge violation total"]
["2017-09-25","Updated the penalty calculator to account for the 2017 Enforcement Policy changes:"]
[null,"Updated the Potential Harm Factor by swapping the dropdown menus in rows 3 and 9 and changing their names"]
[null,"Updated the values in the Susceptibility to Cleanup and Abatment dropdown to more closely align with the new policy"]
[null,"Updated the Per Gallon and Per Day Factors for Discharge by updating the values in the Factor_Table worksheet"]
[null,"Updated the code to account for the swap of the factors. Also, fixed typos in code."]
[null,"Updated the calculation for the Addn' factors"]
[null,"Updated the calculation of the penalty date range generator"]
[null,"Deleted the old date range columns under the Factor_Table sheet"]
["2017-10-05","Changed notes on the calculator"]
[null,"Updated culpability data validation range"]
[null,"Switched steps 7 and 8"]
["2017-10-11","Updated the Instructions box to state which fields require input and which are calculated."]
[null,"Updated the factors so that the name of the factor is a separate field from the dropdown so you can see which factor you are working with."]
[null,"Removed \"Module 3\" code, because it was related to the old date range columns."]
["2025-02-25","Updated steps 6 through 8 to align with the order in the updated policy. Removed the ability to collapse days of violation for discharge-related violations"]
[null,"Changed Ability to Pay to be evauated after all other adjustments. "]
["2025-03-19","Updated wording to properly reference new policy."]

{"sheet":"User Notes","index":3,"n_rows":10,"n_cols":1,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
["User notes for the Penalty Calculation Methodology worksheet"]
[]
["Yellow cells need the user to enter information. White cells are calculated from values entered in the yellow cells. Green cells are calculated from the selected factors."]
[]
["To start the Discharge Violations, select in the green section: (1)the degree of toxicity of the discharge; (2) the potential for harm to beneficial uses;  (3) the discharge’s susceptibility to cleanup or abatement for each violation or group of violations; and (4) the Deviation from Requirement.  The values are calculated once the “Button” is pressed."]
[]
[" If you need to change a factor, you need to press the “Button” again to have the new values appear."]
[]
["For gallons, enter the number of gallons to be fined. This number may be different than the total number of gallons discharged."]