{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Absatz","Vorlage_Angebotseitig","Vorlage_Absatz-Angebotseitig"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":0,"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":"Absatz","index":0,"n_rows":57,"n_cols":5,"rows_kept":57,"cut":false,"hidden":false,"merged":[[5,4,5,6],[6,4,6,6],[7,4,7,6],[0,0,0,2],[1,4,1,6],[2,4,2,6],[3,4,3,6],[4,4,4,6]]}
["Excel-Tool zur manuellen Mengenangabe für die MWQ-Meldung_V1.0.4",null,null,"Legende"]
[null,"                            ",null,null,"Auszufüllen vom Melder (nur diese Felder sind bearbeitbar)"]
["Personen-GLN",null,null,null,"Vorgegeben/Berechnet"]
["Berichtszeitraum (JJJJ)",null,null,null,"Infotext"]
[null,null,null,"Abkürzung \"EW\"","Einweg"]
[null,null,null,"Abkürzung \"MW\"","Mehrweg"]
[null,null,null,"Abkürzung \"GVK\"","Getränkeverbundkarton"]
[null,null,null,"Verbund","Materialverbund, aber nicht GVK (z.B. Getränkebeutel)"]
[]
["Abgesetztes Volumen (gesamtes Unternehmen)"]
[]
["Kategorie","Packstoff","EW Volumen in Liter","MW Volumen in Liter","MWQuote (Absatzseitig)"]
["Bier","Glas"]
["Bier","Kunststoffe"]
["Bier","Aluminium"]
["Bier","GVK"]
["Bier","Verbund"]
["Bier","Eisenmetalle"]
["Bier","Keramik"]
["Berechnung MWQ Bier",null,{"$f":"SUM(C13:C19)","v":0},{"$f":"SUM(D13:D19)","v":0},{"$f":"IFERROR(D20/(D20+C20),0)","v":0}]
[]
["Wässer","Glas"]
["Wässer","Kunststoffe"]
["Wässer","Aluminium"]
["Wässer","GVK"]
["Wässer","Verbund"]
["Wässer","Eisenmetalle"]
["Wässer","Keramik"]
["Berechnung MWQ Wässer",null,{"$f":"SUM(C22:C28)","v":0},{"$f":"SUM(D22:D28)","v":0},{"$f":"IFERROR(D29/(D29+C29),0)","v":0}]
[]
["Saft","Glas"]
["Saft","Kunststoffe"]
["Saft","Aluminium"]
["Saft","GVK"]
["Saft","Verbund"]
["Saft","Eisenmetalle"]
["Saft","Keramik"]
["Berechnung MWQ Saft",null,{"$f":"SUM(C31:C37)","v":0},{"$f":"SUM(D31:D37)","v":0},{"$f":"IFERROR(D38/(D38+C38),0)","v":0}]
[]
["alkoholfreie Erfrischungsgetränke","Glas"]
["alkoholfreie Erfrischungsgetränke","Kunststoffe"]
["alkoholfreie Erfrischungsgetränke","Aluminium"]
["alkoholfreie Erfrischungsgetränke","GVK"]
["alkoholfreie Erfrischungsgetränke","Verbund"]
["alkoholfreie Erfrischungsgetränke","Eisenmetalle"]
["alkoholfreie Erfrischungsgetränke","Keramik"]
["Berechnung MWQ alkoholfreie Erfrischungsgetränke",null,{"$f":"SUM(C40:C46)","v":0},{"$f":"SUM(D40:D46)","v":0},{"$f":"IFERROR(D47/(D47+C47),0)","v":0}]
[]
["Milch","Glas"]
["Milch","Kunststoffe"]
["Milch","Aluminium"]
["Milch","GVK"]
["Milch","Verbund"]
["Milch","Eisenmetalle"]
["Milch","Keramik"]
["Berechnung MWQ Milch",null,{"$f":"SUM(C49:C55)","v":0},{"$f":"SUM(D49:D55)","v":0},{"$f":"IFERROR(D56/(D56+C56),0)","v":0}]
["Gesamt",null,{"$f":"SUM(C20,C29,C38,C47,C56)","v":0},{"$f":"SUM(D20,D29,D38,D47,D56)","v":0},{"$f":"IFERROR(D57/(D57+C57),0)","v":0}]

{"sheet":"Vorlage_Angebotseitig","index":1,"n_rows":57,"n_cols":16,"rows_kept":57,"cut":false,"hidden":true,"merged":[[38,15,39,16],[2,4,2,6],[0,0,0,2],[8,4,8,6],[3,4,3,6],[4,4,4,6],[5,4,5,6],[6,4,6,6],[7,4,7,6]]}
["Gewichtete Artikelanzahl für Angebotsseitige Meldung (pro Verkaufsstelle)"]
[null,"                            ",null,"Legende"]
["Fernabsatz (Online Verkauf)","Nein",null,null,"Auszufüllen vom Melder (nur diese Felder sind bearbeitbar)"]
["Standort-GLN der Verkaufsstelle",null,null,null,"Vorgegeben/Berechnet"]
["Berichtszeitraum (JJJJ)",{"$f":"Absatz!B4","v":0},null,null,"Infotext"]
["Jahrestage",{"$f":" IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":366},null,"Abkürzung \"EW\"","Einweg"]
[null,null,null,"Abkürzung \"MW\"","Mehrweg"]
[null,null,null,"Abkürzung \"GVK\"","Getränkeverbundkarton"]
[null,null,null,"Verbund","Materialverbund, aber nicht GVK (z.B. Getränkebeutel)"]
[]
[]
["Kategorie","Packstoff","Angebotstage EW","Angebotstage MW","MWQ      ","Artikelanzahl im Jahresdurchschnitt EW","Artikelanzahl im Jahresdurchschnitt MW"]
["Bier","Glas",null,null,null,{"$f":"C13/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D13/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Bier ","Kunststoffe",null,null,null,{"$f":"C14/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D14/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Bier ","Aluminium",null,null,null,0,0]
["Bier ","GVK",null,null,null,0,0]
["Bier ","Verbund",null,null,null,0,0]
["Bier ","Eisenmetalle",null,null,null,0,0]
["Bier ","Keramik",null,null,null,0,0]
["Berechnung MWQ Bier",null,{"$f":"SUM(C13:C19)","v":0},{"$f":"SUM(D13:D19)","v":0},{"$f":"IFERROR(D20/(D20+C20), 0)","v":0},0,0]
[null,null,null,null,"Zielquote: 15%"]
["Wässer","Glas",null,null,null,{"$f":"C22/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D22/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Wässer","Kunststoffe",null,null,null,{"$f":"C23/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D23/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Wässer","Aluminium",null,null,null,0,0]
["Wässer","GVK",null,null,null,0,0]
["Wässer","Verbund",null,null,null,0,0]
["Wässer","Eisenmetalle",null,null,null,0,0]
["Wässer","Keramik",null,null,null,0,0]
["Berechnung MWQ Wässer",null,{"$f":"SUM(C22:C28)","v":0},{"$f":"SUM(D22:D28)","v":0},{"$f":"IFERROR(D29/(D29+C29), 0)","v":0},0,0]
[null,null,null,null,"Zielquote: 15%"]
["Saft","Glas",null,null,null,{"$f":"C31/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D31/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Saft","Kunststoffe",null,null,null,{"$f":"C32/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D32/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Saft","Aluminium",null,null,null,0,0]
["Saft","GVK",null,null,null,0,0]
["Saft","Verbund",null,null,null,0,0]
["Saft","Eisenmetalle",null,null,null,0,0]
["Saft","Keramik",null,null,null,0,0]
["Berechnung MWQ Saft",null,{"$f":"SUM(C31:C37)","v":0},{"$f":"SUM(D31:D37)","v":0},{"$f":"IFERROR(D38/(D38+C38), 0)","v":0},0,0]
[null,null,null,null,"Zielquote: 10%",null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(IF(AND(E20>=15%,E29>=15%,E38>=10%,E47>=10%,E56>=10%),\"JA\",\"NEIN\"),\"NEIN\")","v":"NEIN"}]
["alkoholfreie Erfrischungsgetränke","Glas",null,null,null,{"$f":"C40/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D40/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["alkoholfreie Erfrischungsgetränke","Kunststoffe",null,null,null,{"$f":"C41/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D41/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["alkoholfreie Erfrischungsgetränke","Aluminium",null,null,null,0,0]
["alkoholfreie Erfrischungsgetränke","GVK",null,null,null,0,0]
["alkoholfreie Erfrischungsgetränke","Verbund",null,null,null,0,0]
["alkoholfreie Erfrischungsgetränke","Eisenmetalle",null,null,null,0,0]
["alkoholfreie Erfrischungsgetränke","Keramik",null,null,null,0,0]
["Berechnung MWQ alkoholfreie Erfrischungsgetränke",null,{"$f":"SUM(C40:C46)","v":0},{"$f":"SUM(D40:D46)","v":0},{"$f":"IFERROR(D47/(D47+C47), 0)","v":0},0,0]
[null,null,null,null,"Zielquote: 10%"]
["Milch","Glas",null,null,null,{"$f":"C49/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D49/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Milch","Kunststoffe",null,null,null,{"$f":"C50/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0},{"$f":"D50/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Milch","Aluminium",null,null,null,0,{"$f":"D51/ IF(OR(AND(MOD($B$5,4)=0,MOD($B$5,100)<>0),MOD($B$5,400)=0), 366, 365)","v":0}]
["Milch","GVK",null,null,null,0,0]
["Milch","Verbund",null,null,null,0,0]
["Milch","Eisenmetalle",null,null,null,0,0]
["Milch","Keramik",null,null,null,0,0]
["Berechnung MWQ Milch",null,{"$f":"SUM(C49:C55)","v":0},{"$f":"SUM(D49:D55)","v":0},{"$f":"IFERROR(D56/(D56+C56), 0)","v":0},0,0]
[null,null,null,null,"Zielquote: 10%"]

{"sheet":"Vorlage_Absatz-Angebotseitig","index":2,"n_rows":100,"n_cols":15,"rows_kept":100,"cut":false,"hidden":true,"merged":[[38,2,38,10],[42,2,42,4],[47,2,47,4],[41,12,41,13],[41,14,41,15],[38,12,38,13],[38,14,38,15],[39,12,39,13],[39,14,39,15],[40,12,40,13],[40,14,40,15],[35,12,35,13],[35,14,35,15],[36,12,36,13],[36,14,36,15],[37,12,37,13],[37,14,37,15],[32,12,32,13],[32,14,32,15],[33,12,33,13],[33,14,33,15],[34,12,34,13],[34,14,34,15]]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Anzahl der Filialen (exkl. Online Shop)"]
[]
[]
[]
[]
[]
[null,null,"Verkaufsstelle >400m²","MWQuoten erfüllt in VkSt"]
[null,null,"Standort-GLN 1",{"$f":"IF(AND(#REF!>0.1,#REF!>0.1,#REF!>0.1,#REF!>0.15,#REF!>0.15),\"JA\",\"NEIN\")","v":{"$error":"#REF!"}}]
[null,null,"Standort-GLN 2","JA"]
[null,null,"Standort-GLN …","NEIN"]
[null,null,"Fernabsatz (online Verkauf)","NEIN"]
[null,null,"Summe VkSt",{"$f":"COUNTIF(D18:D21,\"JA\")/COUNTA(D18:D21)","v":0.25}]
[]
[null,null,"Angebotsseitig MWQ erfüllt?",null,{"$f":"IF(D22>=0.35,\"JA\",\"NEIN\")","v":"NEIN"}]
[]
[]
[]
[null,null,"ANGEBOTSMINIMUM bei Absatzseitiger Meldung"]
[null,null,"Standort-GLN Verkaufsstelle >400m²",null,"MW in allen Kategorien"]
[null,null,9008390952741,null,"Nein"]
[null,null,"Standort-GLN 2"]
[null,null,"Standort-GLN 3"]
[null,null,"Fernabsatz (online Verkauf)",null,"Ja",null,null,null,null,null,null,null,"Standort-GLN",null,"MV in allen Kategorien"]
[null,null,"Summe VkSt",null,{"$f":"COUNTIF(E30:E33,\"JA\")/COUNTA(E30:E33)","v":0.5},null,null,null,null,null,null,null,9008390952610,null,"Ja"]
[null,null,null,null,null,null,null,null,null,null,null,null,9008390952611,null,"Nein"]
[null,null,"Angebotsseitig MWQ erfüllt?",null,{"$f":"IF(L30>= 0.25 & E34>=0.35,\"JA\",\"NEIN\")","v":"JA"},null,null,null,null,null,null,null,9008390952612,null,"Ja"]
[null,null,null,null,null,null,null,null,null,null,null,null,9008390952613,null,"Ja"]
[null,null,null,null,null,null,null,null,null,null,null,null,9008390952614,null,"Nein"]
[null,null,"Exel formular",null,null,null,null,null,null,null,null,null,9008390952615,null,"Ja"]
["TestGLNFiliale",null,"Jahr",2025,null,null,null,null,null,null,null,null,9008390952616,null,"Ja"]
[9008391015376,null,"Jahrebespiele\nSJ: 2024 = 366\nNSJ: 1900 = 365\nSJ: 1600 = 366",null,null,null,null,null,null,null,null,null,"Fernabsatz (Online Verkauf)",null,"Nicht vorhanden"]
[9008391015284,null,null,null,null,null,null,null,null,null,null,null,"Summe VkSt",null,{"$f":"IFERROR(COUNTIF($K$31:$K$38,\"Ja\")/(COUNTA($K$31:$K$38)-COUNTIF($K$31:$K$38,\"Nicht vorhanden\")),0)","v":0}]
[9008391015291,null,"Method 1: Schaltjahrberechnung mit \"wenn Rest\""]
[9008391015307,null,"Kategorie","Packstoff","Angebotstage EW","Angebotstage MW","MWQ","Artikelanzahl im Jahresdurchschnitt EW","Artikelanzahl im Jahresdurchschnitt MW"]
[9008391015307,null,"Bier","Glas",366,null,null,{"$f":"E45/ IF(MOD($D$40,4)=0, 366, 365)","v":1.0027397260273974},{"$f":"F45/ IF(MOD(D34,4)=0, 366, 365)","v":0}]
[9008391015321]
[9008391015383]
[9008391015390,null,"Method 2: Schaltjahrberechnung mit \"Wenn Rest\" und richtigem Überprüfung \nPrüfen, ob das Jahr ein Schaltjahr ist\n    ' Ein Jahr ist ein Schaltjahr, wenn:\n    ' Es durch 4 teilbar ist und nicht durch 100 teilbar, oder\n    ' Es durch 400 teilbar ist.\n"]
[9008391015390,null,"Kategorie","Packstoff","Angebotstage EW","Angebotstage MW","MWQ","Artikelanzahl im Jahresdurchschnitt EW","Artikelanzahl im Jahresdurchschnitt MW"]
[9008391015413,null,"Bier","Glas",366,null,null,{"$f":"E50/ IF(OR(AND(MOD($D$40,4)=0,MOD($D$40,100)<>0),MOD($D$40,400)=0), 366, 365)","v":1.0027397260273974},{"$f":"F50/ IF(MOD(D39,4)=0, 366, 365)","v":0}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[1]