{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Read_me","Schlüssel","1_Umlage","2_Umlage","Wiederhergestellt_Tabelle1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"Read_me","index":0,"n_rows":10,"n_cols":1,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Original von: https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html"]
["Heruntergeladen am: 20/01/2018  11:21:00"]
[]
["1. Original war fehlerhaft: 2_Umlage!C11 muss korrekt lauten: ='1_Umlage'!J28"]
["2. Die Grafik gemeinkostenumlage-1.gif auf der Seite https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html zeigt auch den falschen Wert 625."]
["3. Die vorliegende Rechnung korrigiert diesen Fehler und rechnet korrekt auf den Cent genau so dass die gerundete Summe der einzelnen Summanden auch wirklich der Summe der gerundeten Summanden entspricht."]
["4. Damit kann diese Gemeinkostenumlage auch in der Praxis mit Buchungssystemen verwendet werden."]
[]
["Bernd Plumhoff"]
["2020-07-25T16:40:00"]

{"sheet":"Schlüssel","index":1,"n_rows":26,"n_cols":16,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[]
["Umlage 1 - Kst.- Gemeinkosten auf alle anderen KSt."]
[null,null,"allg. Kostenstellen",null,null,null,null,null,null,null,"Hilfskostenstellen",null,null,"Hauptkostenstellen"]
["Schlüsselbezeichnung","Gesamt","GF","Sekretariat","RW","Controlling","Personal","ÖA/Werbung","Auszub.","Betriebsrat","Werkstatt 1","Werkstatt 2","Fuhrpark","Fertigung1","Fertigung2","Fertigung3"]
["pro Kopf",{"$f":"SUM(C5:P5)","v":102},1,1,3,1,2,3,3,1,12,10,null,20,20,25]
["m²",{"$f":"SUM(C6:P6)","v":2685},50,40,100,30,50,50,null,15,250,350,100,500,550,600]
["gleich",{"$f":"SUM(C7:P7)","v":14},1,1,1,1,1,1,1,1,1,1,1,1,1,1]
["Gewichtung",{"$f":"SUM(C8:P8)","v":16},2,1,1,1,1,2,1,1,1,1,1,1,1,1]
[]
[]
["Umlage 2 - allg. KSt. und Hilfskostenstellen auf Hauptkostenstellen"]
[]
[]
["umzulegende KSt.","Schlüssel","Fertigung1","Fertigung2","Fertigung3","Gesamt"]
[]
["GF","Gewichtung",0.3,0.4,0.3,{"$f":"SUM(C16:E16)","v":1}]
["Sekretariat","Gewichtung",0.4,0.5,0.1,1]
["RW","Gewichtung",0.3,0.13,0.56999999999999995,1]
["Controlling","gleich",1,1,1,3]
["Personal","pro Kopf",20,20,25,65]
["ÖA/Werbung","Gewichtung",0.3,0.42,0.28000000000000003,1]
["Auszub.","gleich",1,1,1,3]
["Betriebsrat","pro Kopf",20,20,25,65]
["Werkstatt 1","Gewichtung",0.25,0.2,0.55000000000000004,1]
["Werkstatt 2","Gewichtung",0.2,0.2,0.6,1]
["Fuhrpark","Gewichtung",0.4,0.3,0.3,1]

{"sheet":"1_Umlage","index":2,"n_rows":30,"n_cols":18,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["Umlage der Kostenstellengemeinkosten auf alle Kostenstellen"]
[]
[null,null,null,"Verwaltungskostenstellen",null,null,null,null,null,null,null,"Hilfskostenstellen",null,null,"Hauptkostenstellen",null,null,"Gesamt"]
["Bezeichnung","Schlüssel","KSt.- Gemeinkosten","GF","Sekretariat","RW","Controlling","Personal","ÖA/Werbung","Auszub.","Betriebsrat","Werkstatt 1","Werkstatt 2","Fuhrpark","Fertigung1","Fertigung2","Fertigung3"]
["Reise- u. Übernachtung","Gewichtung",10000,{"$f":"RoundToSum($C5/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":1250},625,625,625,625,1250,625,625,625,625,625,625,625,625,{"$f":"SUM(D5:Q5)","v":10000}]
["Aufw. Aufsichtsrat","gleich",3000,{"$f":"RoundToSum($C6/Schlüssel!$B$7*Schlüssel!C$7:P$7)","v":214.28},214.28,214.28,214.28,214.28,214.28,214.29,214.29,214.29,214.29,214.29,214.29,214.29,214.29,3000]
["Bewirtung","Gewichtung",2000,{"$f":"RoundToSum($C7/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Geschenke","Gewichtung",1000,{"$f":"RoundToSum($C8/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Gebühren, Beiträge","gleich",500,{"$f":"RoundToSum($C9/Schlüssel!$B$7*Schlüssel!C$7:P$7)","v":35.72},35.72,35.72,35.72,35.72,35.72,35.71,35.71,35.71,35.71,35.71,35.71,35.71,35.71,499.99999999999989]
["Kfz-Kosten","Gewichtung",4500,{"$f":"RoundToSum($C10/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":562.5},281.25,281.25,281.25,281.25,562.5,281.25,281.25,281.25,281.25,281.25,281.25,281.25,281.25,4500]
["Geräte Ausstattungsgegens","Gewichtung",200,{"$f":"RoundToSum($C11/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":25},12.5,12.5,12.5,12.5,25,12.5,12.5,12.5,12.5,12.5,12.5,12.5,12.5,200]
["Prüfungen Jahresabschl.","gleich",10000,{"$f":"RoundToSum($C12/Schlüssel!$B$7*Schlüssel!C$7:P$7)","v":714.28},714.28,714.28,714.28,714.28,714.28,714.29,714.29,714.29,714.29,714.29,714.29,714.29,714.29,10000]
["sonst. Bewirtschaftungsk.","m²",5500,{"$f":"RoundToSum($C13/Schlüssel!$B$6*Schlüssel!C$6:P$6)","v":102.42},81.94,204.84,61.45,102.42,102.42,0,30.73,512.1,716.95,204.84,1024.21,1126.6300000000001,1229.05,5500.0000000000009]
["Energiekosten","m²",6000,{"$f":"RoundToSum($C14/Schlüssel!$B$6*Schlüssel!C$6:P$6)","v":111.73},89.39,223.47,67.040000000000006,111.73,111.73,0,33.520000000000003,558.66,782.12,223.46,1117.32,1229.05,1340.78,6000]
["Versicherungen","pro Kopf",5000,{"$f":"RoundToSum($C15/Schlüssel!$B$5*Schlüssel!C$5:P$5)","v":49.02},49.02,147.06,49.02,98.04,147.06,147.06,49.02,588.23,490.2,0,980.39,980.39,1225.49,5000]
["Rechts-, Beratungskosten","Gewichtung",5000,{"$f":"RoundToSum($C16/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":625},312.5,312.5,312.5,312.5,625,312.5,312.5,312.5,312.5,312.5,312.5,312.5,312.5,5000]
["Buchführungskosten","Gewichtung",1000,{"$f":"RoundToSum($C17/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Bürobedarf","Gewichtung",2000,{"$f":"RoundToSum($C18/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Telekommunikation","Gewichtung",2500,{"$f":"RoundToSum($C19/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":312.5},156.25,156.25,156.25,156.25,312.5,156.25,156.25,156.25,156.25,156.25,156.25,156.25,156.25,2500]
["Aufw. f. Versand","Gewichtung",2000,{"$f":"RoundToSum($C20/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Bücher, Zeitschriften","Gewichtung",1000,{"$f":"RoundToSum($C21/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Nebenk. d. Geldverkehrs","Gewichtung",500,{"$f":"RoundToSum($C22/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":62.5},31.25,31.25,31.25,31.25,62.5,31.25,31.25,31.25,31.25,31.25,31.25,31.25,31.25,500]
["Schadensersatz","gleich",250,{"$f":"RoundToSum($C23/Schlüssel!$B$7*Schlüssel!C$7:P$7)","v":17.850000000000001},17.850000000000001,17.850000000000001,17.850000000000001,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,250.00000000000006]
["Berufsbekleidung","Gewichtung",1500,{"$f":"RoundToSum($C24/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":187.5},93.75,93.75,93.75,93.75,187.5,93.75,93.75,93.75,93.75,93.75,93.75,93.75,93.75,1500]
["Ausgleichsabgabe Schwerbe","gleich",2000,{"$f":"RoundToSum($C25/Schlüssel!$B$7*Schlüssel!C$7:P$7)","v":142.85},142.85,142.85,142.85,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,2000.0000000000009]
["Fort- u. Weiterbildung","Gewichtung",2000,{"$f":"RoundToSum($C26/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Sonstige Betriebl. Aufw.","Gewichtung",1500,{"$f":"RoundToSum($C27/Schlüssel!$B$8*Schlüssel!C$8:P$8)","v":187.5},93.75,93.75,93.75,93.75,187.5,93.75,93.75,93.75,93.75,93.75,93.75,93.75,93.75,1500]
["Gesamt (gerundete Summe)",null,{"$f":"SUM(C5:C27)","v":68950},{"$f":"SUM(D5:D27)","v":5975.6500000000005},{"$f":"SUM(E5:E27)","v":3639.0799999999995},3994.0999999999995,3596.2399999999993,3730.94,6073.7099999999991,3565.82,3532.03,5077.7499999999991,5408.0299999999988,3847.0600000000004,6540.6799999999994,6754.83,7214.079999999999,{"$f":"SUM(D28:Q28)","v":68949.999999999985}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Kontrolle",{"$f":"R28-C28","v":0}]

{"sheet":"2_Umlage","index":3,"n_rows":21,"n_cols":11,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Umlage der allgemeinen Kostenstellen und Hilfskostenstellen auf die Hauptkostenstellen"]
[]
["umzulegende KSt.","KST- Einzel-kosten","Umlage 1","Gesamt","Fertigung1","Fertigung2","Fertigung3","Gesamt"]
[]
["GF",111666,{"$f":"TRANSPOSE('1_Umlage'!D28:N28)","v":5975.6500000000005},{"$f":"SUM(B5:C5)","v":117641.65},{"$f":"RoundToSum($D5/Schlüssel!$F16*Schlüssel!C16:E16)","v":35292.49},47056.66,35292.5,{"$f":"SUM(E5:G5)","v":117641.65}]
["Sekretariat",34627,3639.0799999999995,{"$f":"SUM(B6:C6)","v":38266.080000000002},{"$f":"RoundToSum($D6/Schlüssel!$F17*Schlüssel!C17:E17)","v":15306.43},19133.04,3826.61,{"$f":"SUM(E6:G6)","v":38266.080000000002}]
["RW",96834,3994.0999999999995,100828.1,{"$f":"RoundToSum($D7/Schlüssel!$F18*Schlüssel!C18:E18)","v":30248.43},13107.65,57472.02,100828.1]
["Controlling",83875,3596.2399999999993,87471.24,{"$f":"RoundToSum($D8/Schlüssel!$F19*Schlüssel!C19:E19)","v":29157.08},29157.08,29157.08,87471.24]
["Personal",53765,3730.94,57495.94,{"$f":"RoundToSum($D9/Schlüssel!$F20*Schlüssel!C20:E20)","v":17691.060000000001},17691.060000000001,22113.82,57495.94]
["ÖA/Werbung",239170,6073.7099999999991,245243.71,{"$f":"RoundToSum($D10/Schlüssel!$F21*Schlüssel!C21:E21)","v":73573.11},103002.36,68668.240000000005,245243.71000000002]
["Auszub.",147397,3565.82,150962.82,{"$f":"RoundToSum($D11/Schlüssel!$F22*Schlüssel!C22:E22)","v":50320.94},50320.94,50320.94,150962.82]
["Betriebsrat",471,3532.03,4003.03,{"$f":"RoundToSum($D12/Schlüssel!$F23*Schlüssel!C23:E23)","v":1231.7},1231.7,1539.63,4003.03]
["Werkstatt 1",125225,5077.7499999999991,130302.75,{"$f":"RoundToSum($D13/Schlüssel!$F24*Schlüssel!C24:E24)","v":32575.69},26060.55,71666.509999999995,130302.75]
["Werkstatt 2",2398512,5408.0299999999988,2403920.0299999998,{"$f":"RoundToSum($D14/Schlüssel!$F25*Schlüssel!C25:E25)","v":480784},480784.01,1442352.02,2403920.0300000003]
["Fuhrpark",26992,3847.0600000000004,30839.06,{"$f":"RoundToSum($D15/Schlüssel!$F26*Schlüssel!C26:E26)","v":12335.62},9251.7199999999993,9251.7199999999993,30839.059999999998]
["1. Umlage",null,null,null,{"$f":"'1_Umlage'!O28","v":6540.6799999999994},{"$f":"'1_Umlage'!P28","v":6754.83},{"$f":"'1_Umlage'!Q28","v":7214.079999999999},{"$f":"SUM(E16:G16)","v":20509.589999999997}]
["2. Umlage",{"$f":"SUM(B5:B15)","v":3318534},48440.409999999989,3366974.4099999997,778516.54999999993,796796.77,1791661.09,3366974.4100000006]
["KSt. - Einzelkosten",null,null,null,738060,854000,650360,{"$f":"SUM(E18:G18)","v":2242420}]
["Gesamt Haupkostenstellen ",null,null,null,{"$f":"SUM(E16:E18)","v":1523117.23},{"$f":"SUM(F16:F18)","v":1657551.6},{"$f":"SUM(G16:G18)","v":2449235.17},{"$f":"SUM(H16:H18)","v":5629904},null,"Kontrolle",{"$f":"H19-SUM(E18:G18)-SUM(B5:B15)-SUM('1_Umlage'!C5:C27)","v":0}]
[]
["Gemeinkostenzuschlagssatz",null,null,null,{"$f":"(E17+E16)/E18","v":1.0636767065008266},{"$f":"(F17+F16)/F18","v":0.94092693208430911},{"$f":"(G17+G16)/G18","v":2.7659683406113538},{"$f":"(H17+H16)/H18","v":1.5106376147198119}]

{"sheet":"Wiederhergestellt_Tabelle1","index":4,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}