{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Read_me","Keys","1_Allocation","2_Allocation","Wiederhergestellt_Tabelle1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":4,"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 (German) from: https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html"]
["Downloaded on: 20/01/2018  11:21:00"]
[]
["1. Original was wrong: 2_Umlage!C11 should read: ='1_Umlage'!J28"]
["2. Graphic gemeinkostenumlage-1.gif on https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html also showed wrong value 625."]
["3. This calculation corrects this error and is calculating correctly down to the cent so that the rounded sum is identical to the sum of rounded values."]
["4. Now this calculation is fit for purpose with existing accounting systems."]
[]
["Bernd Plumhoff"]
["2020-07-25T16:32:00"]

{"sheet":"Keys","index":1,"n_rows":33,"n_cols":6,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["Phase 1 - Allocation of overhead costs to all cost centers"]
[]
["Key","per Head","sqm","uniform","Weighted"]
["Total",{"$f":"SUM(B5:B18)","v":102},{"$f":"SUM(C5:C18)","v":2685},{"$f":"SUM(D5:D18)","v":14},{"$f":"SUM(E5:E18)","v":16}]
["Management",1,50,1,2]
["Secretariat",1,40,1,1]
["Accounting",3,100,1,1]
["Controlling",1,30,1,1]
["HR",2,50,1,1]
["Marketing",3,50,1,2]
["Trainees",3,null,1,1]
["Workers Council",1,15,1,1]
["Factory 1",12,250,1,1]
["Factory 2",10,350,1,1]
["Car Park",null,100,1,1]
["Production1",20,500,1,1]
["Production2",20,550,1,1]
["Production3",25,600,1,1]
[]
["Phase 2 - Allocation of Overhead Cost Centers and Secondary Cost Centers to Primary Cost Centers"]
[]
["Secondary Cost Center","Key","Production1","Production2","Production3","Total"]
["Management","Weighted",0.3,0.4,0.3,{"$f":"SUM(C23:E23)","v":1}]
["Secretariat","Weighted",0.4,0.5,0.1,1]
["Accounting","Weighted",0.3,0.13,0.56999999999999995,1]
["Controlling","uniform",1,1,1,3]
["HR","per Head",20,20,25,65]
["Marketing","Weighted",0.3,0.42,0.28000000000000003,1]
["Trainees","uniform",1,1,1,3]
["Workers Council","per Head",20,20,25,65]
["Factory 1","Weighted",0.25,0.2,0.55000000000000004,1]
["Factory 2","Weighted",0.2,0.2,0.6,1]
["Car Park","Weighted",0.4,0.3,0.3,1]

{"sheet":"1_Allocation","index":2,"n_rows":37,"n_cols":22,"rows_kept":37,"cut":false,"hidden":false,"merged":[[31,19,31,21],[32,20,32,21]]}
["Allocation of overhead costs to all cost centers"]
[]
[null,null,null,"Overhead Cost Centers",null,null,null,null,null,null,null,"Secondary Cost Centers",null,null,"Primary Cost Centers",null,null,"Total"]
["Cost Category","Key","Overhead Costs","Management","Secretariat","Accounting","Controlling","HR","Marketing","Trainees","Workers Council","Factory 1","Factory 2","Car Park","Production1","Production2","Production3"]
["Travel expenses","Weighted",10000,{"$f":"RoundToSum($C5/INDEX(Keys!B$4:E$4,,MATCH(B5,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B5,Keys!$B$3:$E$3,0))))","v":1250},625,625,625,625,1250,625,625,625,625,625,625,625,625,{"$f":"SUM(D5:Q5)","v":10000}]
["Supervisory board","uniform",3000,{"$f":"RoundToSum($C6/INDEX(Keys!B$4:E$4,,MATCH(B6,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B6,Keys!$B$3:$E$3,0))))","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]
["Hospitality","Weighted",2000,{"$f":"RoundToSum($C7/INDEX(Keys!B$4:E$4,,MATCH(B7,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B7,Keys!$B$3:$E$3,0))))","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Presents","Weighted",1000,{"$f":"RoundToSum($C8/INDEX(Keys!B$4:E$4,,MATCH(B8,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B8,Keys!$B$3:$E$3,0))))","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]
["Fees, charges","uniform",500,{"$f":"RoundToSum($C9/INDEX(Keys!B$4:E$4,,MATCH(B9,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B9,Keys!$B$3:$E$3,0))))","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]
["Car costs","Weighted",4500,{"$f":"RoundToSum($C10/INDEX(Keys!B$4:E$4,,MATCH(B10,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B10,Keys!$B$3:$E$3,0))))","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]
["Hardware, equipment","Weighted",200,{"$f":"RoundToSum($C11/INDEX(Keys!B$4:E$4,,MATCH(B11,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B11,Keys!$B$3:$E$3,0))))","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]
["External audit","uniform",10000,{"$f":"RoundToSum($C12/INDEX(Keys!B$4:E$4,,MATCH(B12,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B12,Keys!$B$3:$E$3,0))))","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]
["Other operating expenses","sqm",5500,{"$f":"RoundToSum($C13/INDEX(Keys!B$4:E$4,,MATCH(B13,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B13,Keys!$B$3:$E$3,0))))","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]
["Energy costs","sqm",6000,{"$f":"RoundToSum($C14/INDEX(Keys!B$4:E$4,,MATCH(B14,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B14,Keys!$B$3:$E$3,0))))","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]
["Insurances","per Head",5000,{"$f":"RoundToSum($C15/INDEX(Keys!B$4:E$4,,MATCH(B15,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B15,Keys!$B$3:$E$3,0))))","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]
["Legal costs","Weighted",5000,{"$f":"RoundToSum($C16/INDEX(Keys!B$4:E$4,,MATCH(B16,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B16,Keys!$B$3:$E$3,0))))","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]
["Accounting costs","Weighted",1000,{"$f":"RoundToSum($C17/INDEX(Keys!B$4:E$4,,MATCH(B17,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B17,Keys!$B$3:$E$3,0))))","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]
["Stationary","Weighted",2000,{"$f":"RoundToSum($C18/INDEX(Keys!B$4:E$4,,MATCH(B18,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B18,Keys!$B$3:$E$3,0))))","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Telecommunication","Weighted",2500,{"$f":"RoundToSum($C19/INDEX(Keys!B$4:E$4,,MATCH(B19,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B19,Keys!$B$3:$E$3,0))))","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]
["Shipping and mailing costs","Weighted",2000,{"$f":"RoundToSum($C20/INDEX(Keys!B$4:E$4,,MATCH(B20,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B20,Keys!$B$3:$E$3,0))))","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Books, magazines","Weighted",1000,{"$f":"RoundToSum($C21/INDEX(Keys!B$4:E$4,,MATCH(B21,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B21,Keys!$B$3:$E$3,0))))","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]
["Money transfer fees","Weighted",500,{"$f":"RoundToSum($C22/INDEX(Keys!B$4:E$4,,MATCH(B22,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B22,Keys!$B$3:$E$3,0))))","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]
["Damages, compensation","uniform",250,{"$f":"RoundToSum($C23/INDEX(Keys!B$4:E$4,,MATCH(B23,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B23,Keys!$B$3:$E$3,0))))","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]
["Working clothes","Weighted",1500,{"$f":"RoundToSum($C24/INDEX(Keys!B$4:E$4,,MATCH(B24,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B24,Keys!$B$3:$E$3,0))))","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]
["Handicapped fee","uniform",2000,{"$f":"RoundToSum($C25/INDEX(Keys!B$4:E$4,,MATCH(B25,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B25,Keys!$B$3:$E$3,0))))","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]
["Training and further education","Weighted",2000,{"$f":"RoundToSum($C26/INDEX(Keys!B$4:E$4,,MATCH(B26,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B26,Keys!$B$3:$E$3,0))))","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Other operating expenses","Weighted",1500,{"$f":"RoundToSum($C27/INDEX(Keys!B$4:E$4,,MATCH(B27,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B27,Keys!$B$3:$E$3,0))))","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]
["Total",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,"Check",{"$f":"R28-C28","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Worksheet Formulas"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Range","Formula"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"D5:D27","D5",{"$f":"sbGetCell(INDIRECT(U34),41)","v":"=RoundToSum($C5/INDEX(Keys!B$4:E$4,,MATCH(B5,Keys!$B$3:$E$3,0))*TRANSPOSE(INDEX(Keys!B$5:E$18,,MATCH(B5,Keys!$B$3:$E$3,0))))"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"C28:Q28","C28",{"$f":"sbGetCell(INDIRECT(U35),41)","v":"=SUM(C5:C27)"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"R5:R28","R5",{"$f":"sbGetCell(INDIRECT(U36),41)","v":"=SUM(D5:Q5)"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"R30","R30",{"$f":"sbGetCell(INDIRECT(U37),41)","v":"=R28-C28"}]

{"sheet":"2_Allocation","index":3,"n_rows":23,"n_cols":12,"rows_kept":23,"cut":false,"hidden":false,"merged":[[3,9,3,11],[4,10,4,11]]}
["Allocation of Overhead Cost Centers and Secondary Cost Centers to Primary Cost Centers"]
[]
["Allocated Cost Centers","Direct Costs","Allocation Phase 1","Total","Production1","Production2","Production3","Total"]
[null,null,null,null,null,null,null,null,null,"Worksheet Formulas"]
["Management",111666,{"$f":"TRANSPOSE('1_Allocation'!D28:N28)","v":5975.6500000000005},{"$f":"SUM(B5:C5)","v":117641.65},{"$f":"RoundToSum($D5/Keys!$F23*Keys!C23:E23)","v":35292.49},47056.66,35292.5,{"$f":"SUM(E5:G5)","v":117641.65},null,"Range","Formula"]
["Secretariat",34627,3639.0799999999995,{"$f":"SUM(B6:C6)","v":38266.080000000002},{"$f":"RoundToSum($D6/Keys!$F24*Keys!C24:E24)","v":15306.43},19133.04,3826.61,{"$f":"SUM(E6:G6)","v":38266.080000000002},null,"C5:C15","C5",{"$f":"sbGetCell(INDIRECT(K6),41)","v":"=TRANSPOSE('1_Allocation'!D28:N28)"}]
["Accounting",96834,3994.0999999999995,100828.1,{"$f":"RoundToSum($D7/Keys!$F25*Keys!C25:E25)","v":30248.43},13107.65,57472.02,100828.1,null,"D5:D15","D5",{"$f":"sbGetCell(INDIRECT(K7),41)","v":"=SUM(B5:C5)"}]
["Controlling",83875,3596.2399999999993,87471.24,{"$f":"RoundToSum($D8/Keys!$F26*Keys!C26:E26)","v":29157.08},29157.08,29157.08,87471.24,null,"E5:E15","E5",{"$f":"sbGetCell(INDIRECT(K8),41)","v":"=RoundToSum($D5/Keys!$F23*Keys!C23:E23)"}]
["HR",53765,3730.94,57495.94,{"$f":"RoundToSum($D9/Keys!$F27*Keys!C27:E27)","v":17691.060000000001},17691.060000000001,22113.82,57495.94,null,"H5:H16","H5",{"$f":"sbGetCell(INDIRECT(K9),41)","v":"=SUM(E5:G5)"}]
["Marketing",239170,6073.7099999999991,245243.71,{"$f":"RoundToSum($D10/Keys!$F28*Keys!C28:E28)","v":73573.11},103002.36,68668.240000000005,245243.71000000002,null,"E16","E16",{"$f":"sbGetCell(INDIRECT(K10),41)","v":"='1_Allocation'!O28"}]
["Trainees",147397,3565.82,150962.82,{"$f":"RoundToSum($D11/Keys!$F29*Keys!C29:E29)","v":50320.94},50320.94,50320.94,150962.82,null,"B17:H17","B17",{"$f":"sbGetCell(INDIRECT(K11),41)","v":"=SUM(B5:B15)"}]
["Workers Council",471,3532.03,4003.03,{"$f":"RoundToSum($D12/Keys!$F30*Keys!C30:E30)","v":1231.7},1231.7,1539.63,4003.03,null,"E19:H19","E19",{"$f":"sbGetCell(INDIRECT(K12),41)","v":"=SUM(E16:E18)"}]
["Factory 1",125225,5077.7499999999991,130302.75,{"$f":"RoundToSum($D13/Keys!$F31*Keys!C31:E31)","v":32575.69},26060.55,71666.509999999995,130302.75,null,"E21:H21","E21",{"$f":"sbGetCell(INDIRECT(K13),41)","v":"=(E17+E16)/E18"}]
["Factory 2",2398512,5408.0299999999988,2403920.0299999998,{"$f":"RoundToSum($D14/Keys!$F32*Keys!C32:E32)","v":480784},480784.01,1442352.02,2403920.0300000003,null,"H23","H23",{"$f":"sbGetCell(INDIRECT(K14),41)","v":"=H19-SUM(E18:G18)-SUM(B5:B15)-SUM('1_Allocation'!C5:C27)"}]
["Car Park",26992,3847.0600000000004,30839.06,{"$f":"RoundToSum($D15/Keys!$F33*Keys!C33:E33)","v":12335.62},9251.7199999999993,9251.7199999999993,30839.059999999998]
["Phase 1 Allocation",null,null,null,{"$f":"'1_Allocation'!O28","v":6540.6799999999994},{"$f":"'1_Allocation'!P28","v":6754.83},{"$f":"'1_Allocation'!Q28","v":7214.079999999999},{"$f":"SUM(E16:G16)","v":20509.589999999997}]
["Phase 2 Allocation",{"$f":"SUM(B5:B15)","v":3318534},48440.409999999989,3366974.4099999997,778516.54999999993,796796.77,1791661.09,3366974.4100000006]
["Directs Costs",null,null,null,738060,854000,650360,{"$f":"SUM(E18:G18)","v":2242420}]
["Total Primary Cost Centers",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}]
[]
["Overhead rate",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}]
[]
[null,null,null,null,null,null,"Check",{"$f":"H19-SUM(E18:G18)-SUM(B5:B15)-SUM('1_Allocation'!C5:C27)","v":0}]

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