{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Calculadora de Tarifas","Tutorial de preenchimento","Quadro Tarifário","Copanor"],"not_rendered":{"charts":0,"images":21,"macros":1,"external_links":1,"defined_names":0,"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":"Calculadora de Tarifas","index":0,"n_rows":58,"n_cols":40,"rows_kept":58,"cut":false,"hidden":false,"merged":[[29,17,29,22],[27,17,27,29],[28,17,28,21],[20,35,20,36],[5,20,5,22],[24,18,24,28],[25,18,25,22],[23,10,23,14],[12,31,12,33],[13,31,13,33],[10,12,10,14],[11,12,11,14],[14,12,14,13],[13,12,13,13],[16,20,16,21],[13,25,13,26],[14,25,14,26],[15,25,15,26],[13,20,13,21],[11,25,11,26],[11,20,11,21],[12,20,12,21],[5,10,5,14],[8,12,8,14],[9,12,9,14],[22,18,23,28],[9,27,9,28],[10,27,10,28],[11,27,11,28],[13,27,13,28],[14,27,14,28],[15,27,15,28],[16,27,16,28],[18,18,21,21],[15,23,15,24],[16,23,16,24],[14,20,14,21],[15,20,15,21],[20,38,20,40],[15,33,15,34],[16,33,16,34],[17,33,17,34],[18,33,18,34],[19,33,19,34],[20,33,20,34],[15,35,15,36],[15,38,15,40],[16,38,16,40],[18,38,18,40],[16,35,16,36],[17,35,17,36],[18,35,18,36],[19,35,19,36],[19,38,19,40],[3,18,3,28],[5,17,5,19],[7,17,7,19],[9,17,9,19],[10,32,10,34],[9,23,9,24],[10,23,10,24],[9,25,9,26],[10,25,10,26],[10,17,10,19],[9,20,9,21],[10,20,10,21],[17,38,17,40],[16,25,16,26],[11,23,11,24],[12,23,12,24],[13,23,13,24],[14,23,14,24],[12,25,12,26],[12,27,12,28]]}
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Simulador de Faturas (Tarifas vigentes a partir de 01/08/2021)"]
[]
[null,null,null,null,null,null,null,null,null,null,"Simulador de Faturas",null,null,null,null,null,null,"   1 - Digite o volume total consumido (m³)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,2]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"   2 - Clique no serviço oferecido",null,null,null,null,null,null,null,null,null,null,null,null,null,"Auxiliar de cálculo das macros"]
[null,null,1,"Volume Consumido",null,null,null,null,null,null,"1 - Digite o volume total consumido (m³)"]
[null,null,2,"Prestador","2.1","Copasa",null,null,null,null,"2 - Seu prestador",null,"Copanor",null,null,null,null,"   3 - Digite o número de economias* ",null,null,"Categorias",null,"Número de economias","Valor Água",null,"Valor Esgoto",null,"Valor Total",null,null,null,1,"Índice Casa/Prédio"]
[null,null,null,null,"2.2","Copanor",null,null,null,null,"3 - Selecione a sua categoria",null,null,null,null,null,null,"        relativas a cada categoria ",null,null,{"$f":"IF($M$10=\"Cesama\",F25,F18)","v":"Residencial Social"},null,null,"",null,"",null,{"$f":"SUM(X11:AA11)","v":0},null,null,null,{"$f":"IF(AF10=1,1,IF(AF10=2,M15,\"Erro\"))","v":1},"Total de Economias Residenciais"]
[null,null,null,null,"2.3","Saae Itabira",null,null,null,null,"4 - Selecione o serviço oferecido",null,{"$f":"IF(AN7=1,\"Só água\",IF(AN7=2,\"Água e Esgoto Dinâmico\",IF(AN7=3,\"Água e Esgoto Estático\",IF(AN7=4,\"Só Esgoto Dinâmico\",IF(AN7=5,\"Só Esgoto Estático\",\"\")))))","v":"Água e Esgoto Dinâmico"},null,null,null,null,null,null,null,{"$f":"IF($M$10=\"Cesama\",F26,F19)","v":"Residencial"},null,null,"",null,"",null,0]
[null,null,null,null,"2.4","Saae Passos",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Comercial",null,null,"",null,"",null,0,null,null,null,{"$f":"U6/W17","v":{"$error":"#DIV/0!"}},null,null,"Volume por Economia"]
[null,null,null,null,"2.5","SAE Ituiutaba",null,null,null,null,"Casa",null,"Quantidade de apartamentos por hidrômetro",null,null,null,null,null,null,null,"Industrial",null,null,"",null,"",null,0,null,null,null,null,null,null,"Valor Original"]
[null,null,null,null,"2.6","Cesama",null,null,null,null,"Prédio",null,1,null,null,null,null,null,null,null,"Pública",null,null,"",null,"",null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF($M$10=\"Cesama\",F30,IF($M$10=\"Copanor\",F38,F23))","v":""},null,null,"",null,"",null,0,null,null,null,"","",{"$f":"IFERROR(AF16*$W11,\"\")","v":""},null,{"$f":"IFERROR(AG16*$W11,\"\")","v":""},null,null,{"$f":"IF($M$10=\"Cesama\",F25,F18)","v":"Residencial Social"}]
[null,null,3,"Categoria","COPASA, Saae de Passos, Itabira, Sae de Ituiutaba",null,null,null,null,null,"Serviço",null,"Água","Esgoto","Total",null,null,null,null,null,"Valor Total",null,{"$f":"SUM(W11:W16)","v":0},{"$f":"SUM(X11:Y16)","v":0},null,{"$f":"SUM(Z11:AA16)","v":0},null,{"$f":"ROUNDDOWN(SUM(X17:AA17),2)","v":0},null,null,null,"","",{"$f":"IFERROR(AF17*$W12,\"\")","v":""},null,"",null,null,{"$f":"IF($M$10=\"Cesama\",F26,F19)","v":"Residencial"}]
[null,null,null,null,"3.1","Residencial Social",null,null,null,null,"Valor",null,{"$f":"IFERROR(IF(OR(M12=F46,M12=F47,M12=F48),INDIRECT(\"'\"&M10&\"'!\"&\"S14\"),\"\")*AF11,\"\")","v":0},{"$f":"IFERROR(IF(OR(M12=F47,M12=F49),INDIRECT(\"'\"&M10&\"'!\"&\"t14\"),IF(OR(M12=F48,M12=F50),INDIRECT(\"'\"&M10&\"'!\"&\"u14\"),\"\"))*AF11,\"\")","v":0},{"$f":"SUM(M18:N18)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",null,"",null,null,"Comercial"]
[null,null,null,null,"3.2","Residencial",null,null,null,null,null,null,null,null,null,null,null,null,"* Economia faz referência ao número de unidades familiares, comerciais ou produtivas que existem para um mesmo hidrômetro/ligação. Existindo em um mesma construção, com hidrômetro único, três famílias e duas lojas, têm-se 3 economias residenciais e 2 economias comerciais para 1 ligação",null,null,null,null,null,null,null,null,null,null,null,null,"","","",null,"",null,null,"Industrial"]
[null,null,null,null,"3.3","Comercial",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"","","",null,"",null,null,"Pública"]
[null,null,null,null,"3.4","Industrial",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"",null,null,null,null,null,null,null,"","",{"$f":"IFERROR(AF21*$W16,\"\")","v":""},null,{"$f":"IFERROR(AG21*$W16,\"\")","v":""},null,null,{"$f":"IF($M$10=\"Cesama\",F30,IF($M$10=\"Copanor\",F38,F23))","v":""}]
[null,null,null,null,"3.5","Pública"]
[null,null,null,null,null,{"$f":"\"\"","v":""}]
[null,null,null,null,"Cesama",null,null,null,null,null,"Agência Reguladora de Serviços de Abastecimento de Água e Esgotamento Sanitário do Estado de MG - ARSAE"]
[null,null,null,null,"3.1","Residencial Social",null,null,null,null,null,null,null,null,null,null,null,null,"Agência Reguladora de Serviços de Abastecimento de Água e Esgotamento Sanitário do Estado de MG - ARSAE"]
[null,null,null,null,"3.6","Residencial Unifamiliar"]
[null,null,null,null,"3.7","Residencial Multifamiliar"]
[null,null,null,null,"3.3","Comercial",null,null,null,null,null,null,null,null,null,null,null,"Notas: - Esta calculadora não é ajustada para cálculos em meses com mudança de tarifas. Nestas situações o prestador aplicará tanto as tarifas novas quanto as antigas de acordo com o período de leitura do                  "]
[null,null,null,null,"3.4","Industrial",null,null,null,null,null,null,null,null,null,null,null,"                     hidrômetro e com a data da mudança das tarifas, individualizando o cálculo."]
[null,null,null,null,"3.5","Pública",null,null,null,null,null,null,null,null,null,null,null,"                  - Diferenças de poucos centavos podem ocorrer devido a critérios de arredondamentos adotados. "]
[]
[null,null,null,null,"Copanor"]
[null,null,null,null,"3.2","Residencial"]
[null,null,null,null,"3.3","Comercial"]
[null,null,null,null,"3.4","Industrial"]
[null,null,null,null,"3.5","Pública"]
[null,null,null,null,null,{"$f":"\"\"","v":""}]
[null,null,4,"Serviço oferecido","COPASA",{"$f":"\"\"","v":""}]
[null,null,null,null,"4.10","Só água"]
[null,null,null,null,"4.15","Água e coleta de esgoto"]
[null,null,null,null,"4.20","Água com coleta e tratamento de esgoto"]
[null,null,null,null,"4.25","Só coleta de esgoto"]
[null,null,null,null,"4.30","Só tratamento de esgoto"]
[]
[null,null,null,null,"COPANOR"]
[null,null,null,null,"4.10","Só água"]
[null,null,null,null,"4.15","Água e Esgoto Dinâmico"]
[null,null,null,null,"4.20","Água e Esgoto Estático"]
[null,null,null,null,"4.25","Só Esgoto Dinâmico"]
[null,null,null,null,"4.30","Só Esgoto Estático"]
[]
[]
[]
[]
[null,null,null,null,"SAAE de Passos, Itabira, Sae de Ituiutaba e Cesama"]
[null,null,null,null,"4.1","Só água"]
[null,null,null,null,null,"Água e esgoto"]
[null,null,null,null,null,"Só esgoto"]

{"sheet":"Tutorial de preenchimento","index":1,"n_rows":5,"n_cols":32,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Exemplo de uso do simulador"]

{"sheet":"Quadro Tarifário","index":2,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"TABELA TARIFÁRIA COPANOR"]
[null,"Vigência: Agosto/2021 a Julho/2022"]
[null,"RESOLUÇÃO ARSAE-MG 155/2021, DE 28 DE JUNHO DE 2021"]

{"sheet":"Copanor","index":3,"n_rows":55,"n_cols":21,"rows_kept":55,"cut":false,"hidden":true,"merged":[[12,6,12,9],[13,9,14,9],[11,18,11,20],[47,4,54,4],[39,4,46,4],[11,4,14,4],[15,4,22,4],[23,4,30,4],[31,4,38,4],[11,5,14,5],[11,6,11,9],[3,15,3,19],[4,16,4,17],[4,18,4,19],[3,4,3,7],[4,4,4,9],[6,4,6,9],[7,4,7,9],[8,4,8,9],[9,4,9,9],[10,4,10,9]]}
[]
[]
[]
[]
[null,null,null,null,"Tabela Tarifária II - Tarifas Aplicáveis aos Usuários"]
[]
[null,null,null,null,"Considerar apenas as colunas correspondentes aos serviços prestados:"]
[null,null,null,null,"- Água: Abastecimento de água"]
[null,null,null,null,"- ED: esgotamento dinâmico "]
[null,null,null,null,"- EE: esgotamento estático"]
[]
[null,null,null,null,"Categoria","Faixa"," Tarifas de Aplicação",null,null,null,null,null,null,null,null,null,null,null,"Valor"]
[null,null,null,null,null,null,"Agosto/20 a Julho/21",null,null,null,null,null,null,null,null,null,null,null,"Água","ED","EE"]
[null,null,null,null,null,null,1,2,3,"Unidade",null,null,null,null,"Categoria",{"$f":"'Calculadora de Tarifas'!M11","v":0},null,null,{"$f":"(IF(P17=0,SUMIFS(G16:G55,B16:B55,P14,D16:D55,R17)*P15+SUMIFS(G16:G55,B16:B55,P14,D16:D55,P17),SUMIFS(K16:K55,B16:B55,P14,D16:D55,P17)+(P15-P17)*SUMIFS(G16:G55,B16:B55,P14,D16:D55,R17)))","v":0},{"$f":"(IF(P17=0,SUMIFS(H16:H55,B16:B55,P14,D16:D55,R17)*P15+SUMIFS(H16:H55,B16:B55,P14,D16:D55,P17),SUMIFS(L16:L55,B16:B55,P14,D16:D55,P17)+(P15-P17)*SUMIFS(H16:H55,B16:B55,P14,D16:D55,R17)))","v":0},{"$f":"(IF(P17=0,SUMIFS(I16:I55,B16:B55,P14,D16:D55,R17)*P15+SUMIFS(I16:I55,B16:B55,P14,D16:D55,P17),SUMIFS(M16:M55,B16:B55,P14,D16:D55,P17)+(P15-P17)*SUMIFS(I16:I55,B16:B55,P14,D16:D55,R17)))","v":0}]
[null,null,null,null,null,null,"Água","ED","EE",null,null,null,null,null,"Consumo (m³)",{"$f":"'Calculadora de Tarifas'!U6/'Calculadora de Tarifas'!AF11","v":0}]
[null,"Residencial Social",null,0,"Residencial Social","Fixa",4.6399999999999997,3.43,1.4,"R$/mês"]
[null,"Residencial Social",0,3,null,"0 a 3 m³",0.72,0.53,0.21,"R$/m³",{"$f":"G16+($D17-$C17)*G17","v":6.8},{"$f":"H16+($D17-$C17)*H17","v":5.0200000000000005},{"$f":"I16+($D17-$C17)*I17","v":2.0299999999999998},null,null,{"$f":"IFERROR(INDEX(IF(B16:B55=P14,D16:D55,\"Erro\"),MATCH(P15,IF(B16:B55=P14,D16:D55,\"Erro\"),1),),\"0\")","v":"0"},"a",{"$f":"IFERROR(INDEX(IF(B16:B55=P14,D16:D55,\"Erro\"),MATCH(P15,IF(B16:B55=P14,D16:D55,\"Erro\"),1)+1,),\"3\")","v":"3"},"m³"]
[null,"Residencial Social",3,6,null,"> 3 a 6 m³",0.89800000000000002,0.66400000000000003,0.26900000000000002,"R$/m³",{"$f":"K17+($D18-$C18)*G18","v":9.4939999999999998},{"$f":"L17+($D18-$C18)*H18","v":7.0120000000000005},{"$f":"M17+($D18-$C18)*I18","v":2.8369999999999997}]
[null,"Residencial Social",6,10,null,"> 6 a 10 m³",1.9159999999999999,1.417,0.57499999999999996,"R$/m³",17.158000000000001,12.68,5.1369999999999996]
[null,"Residencial Social",10,15,null,"> 10 a 15 m³",2.97,2.1970000000000001,0.89100000000000001,"R$/m³",32.008000000000003,23.664999999999999,9.5919999999999987]
[null,"Residencial Social",15,20,null,"> 15 a 20 m³",4.0540000000000003,3,1.216,"R$/m³",52.278000000000006,38.664999999999999,15.671999999999999]
[null,"Residencial Social",20,40,null,"> 20 a 40 m³",8.5950000000000006,6.36,2.5779999999999998,"R$/m³",{"$f":"K21+($D22-$C22)*G22","v":224.178},165.86500000000001,67.231999999999999]
[null,"Residencial Social",40,999999,null,"> 40 m³",10.484999999999999,7.758,3.145,"R$/m³"]
[null,"Residencial",null,0,"Residencial","Fixa",7.73,5.71,2.31,"R$/mês"]
[null,"Residencial",0,3,null,"0 a 3 m³",1.21,0.89,0.35,"R$/m³",{"$f":"G24+($D25-$C25)*G25","v":11.36},{"$f":"H24+($D25-$C25)*H25","v":8.379999999999999},{"$f":"I24+($D25-$C25)*I25","v":3.36}]
[null,"Residencial",3,6,null,"> 3 a 6 m³",1.498,1.109,0.45,"R$/m³",{"$f":"K25+($D26-$C26)*G26","v":15.853999999999999},{"$f":"L25+($D26-$C26)*H26","v":11.706999999999999},{"$f":"M25+($D26-$C26)*I26","v":4.71}]
[null,"Residencial",6,10,null,"> 6 a 10 m³",3.194,2.3639999999999999,0.95799999999999996,"R$/m³",28.63,21.162999999999997,8.5419999999999998]
[null,"Residencial",10,15,null,"> 10 a 15 m³",4.95,3.6629999999999998,1.4850000000000001,"R$/m³",53.379999999999995,39.477999999999994,15.967000000000001]
[null,"Residencial",15,20,null,"> 15 a 20 m³",6.7569999999999997,5.0010000000000003,2.0270000000000001,"R$/m³",87.164999999999992,64.483000000000004,26.102000000000004]
[null,"Residencial",20,40,null,"> 20 a 40 m³",8.5950000000000006,6.36,2.5779999999999998,"R$/m³",{"$f":"K29+($D30-$C30)*G30","v":259.065},191.68299999999999,77.662000000000006]
[null,"Residencial",40,999999,null,"> 40 m³",10.484999999999999,7.758,3.145,"R$/m³"]
[null,"Comercial",null,0,"Comercial","Fixa",18.57,13.74,5.57,"R$/mês"]
[null,"Comercial",0,3,null,"0 a 3 m³",2.39,1.77,0.72,"R$/m³",{"$f":"G32+($D33-$C33)*G33","v":25.740000000000002},{"$f":"H32+($D33-$C33)*H33","v":19.05},{"$f":"I32+($D33-$C33)*I33","v":7.73}]
[null,"Comercial",3,6,null,"> 3 a 6 m³",3.5960000000000001,2.66,1.08,"R$/m³",{"$f":"K33+($D34-$C34)*G34","v":36.528000000000006},{"$f":"L33+($D34-$C34)*H34","v":27.03},{"$f":"M33+($D34-$C34)*I34","v":10.97}]
[null,"Comercial",6,10,null,"> 6 a 10 m³",5.3920000000000003,3.99,1.6180000000000001,"R$/m³",58.096000000000004,42.99,17.442]
[null,"Comercial",10,20,null,"> 10 a 20 m³",7.2530000000000001,5.3680000000000003,2.1760000000000002,"R$/m³",130.626,96.670000000000016,39.201999999999998]
[null,"Comercial",20,40,null,"> 20 a 40 m³",9.1389999999999993,6.7640000000000002,2.742,"R$/m³",313.40599999999995,231.95000000000002,94.042000000000002]
[null,"Comercial",40,200,null,"> 40 a 200 m³",11.077,8.1969999999999992,3.323,"R$/m³",{"$f":"K37+($D38-$C38)*G38","v":2085.7259999999997},1543.47,625.72199999999998]
[null,"Comercial",200,999999,null,"> 200 m³",13.026,9.64,3.9079999999999999,"R$/m³"]
[null,"Industrial",null,0,"Industrial","Fixa",18.57,13.74,5.57,"R$/mês"]
[null,"Industrial",0,3,null,"0 a 3 m³",2.39,1.77,0.72,"R$/m³",{"$f":"G40+($D41-$C41)*G41","v":25.740000000000002},{"$f":"H40+($D41-$C41)*H41","v":19.05},{"$f":"I40+($D41-$C41)*I41","v":7.73}]
[null,"Industrial",3,6,null,"> 3 a 6 m³",3.5960000000000001,2.66,1.08,"R$/m³",{"$f":"K41+($D42-$C42)*G42","v":36.528000000000006},{"$f":"L41+($D42-$C42)*H42","v":27.03},{"$f":"M41+($D42-$C42)*I42","v":10.97}]
[null,"Industrial",6,10,null,"> 6 a 10 m³",5.3920000000000003,3.99,1.6180000000000001,"R$/m³",58.096000000000004,42.99,17.442]
[null,"Industrial",10,20,null,"> 10 a 20 m³",7.2530000000000001,5.3680000000000003,2.1760000000000002,"R$/m³",130.626,96.670000000000016,39.201999999999998]
[null,"Industrial",20,40,null,"> 20 a 40 m³",9.1389999999999993,6.7640000000000002,2.742,"R$/m³",313.40599999999995,231.95000000000002,94.042000000000002]
[null,"Industrial",40,200,null,"> 40 a 200 m³",11.077,8.1969999999999992,3.323,"R$/m³",{"$f":"K45+($D46-$C46)*G46","v":2085.7259999999997},1543.47,625.72199999999998]
[null,"Industrial",200,999999,null,"> 200 m³",13.026,9.64,3.9079999999999999,"R$/m³"]
[null,"Pública",null,0,"Pública","Fixa",15.79,11.68,4.74,"R$/mês"]
[null,"Pública",0,3,null,"0 a 3 m³",2.27,1.67,0.69,"R$/m³",{"$f":"G48+($D49-$C49)*G49","v":22.6},{"$f":"H48+($D49-$C49)*H49","v":16.689999999999998},{"$f":"I48+($D49-$C49)*I49","v":6.8100000000000005}]
[null,"Pública",3,6,null,"> 3 a 6 m³",3.4060000000000001,2.52,1.022,"R$/m³",{"$f":"K49+($D50-$C50)*G50","v":32.817999999999998},{"$f":"L49+($D50-$C50)*H50","v":24.25},{"$f":"M49+($D50-$C50)*I50","v":9.8760000000000012}]
[null,"Pública",6,10,null,"> 6 a 10 m³",5.1100000000000003,3.7810000000000001,1.5329999999999999,"R$/m³",53.257999999999996,39.374000000000002,16.008000000000003]
[null,"Pública",10,20,null,"> 10 a 20 m³",6.8710000000000004,5.085,2.0619999999999998,"R$/m³",121.968,90.224000000000004,36.628]
[null,"Pública",20,40,null,"> 20 a 40 m³",8.6579999999999995,6.4059999999999997,2.597,"R$/m³",295.12799999999999,218.34399999999999,88.567999999999998]
[null,"Pública",40,200,null,"> 40 a 200 m³",10.494,7.7649999999999997,3.1480000000000001,"R$/m³",{"$f":"K53+($D54-$C54)*G54","v":1974.1679999999999},1460.7439999999999,592.24800000000005]
[null,"Pública",200,999999,null,"> 200 m³",12.340999999999999,9.1329999999999991,3.702,"R$/m³"]