{"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":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 (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":26,"n_cols":16,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[]
["Phase 1 - Allocation of overhead costs to all cost centers"]
[null,null,"Overhead Cost Centers",null,null,null,null,null,null,null,"Secondary Cost Centers",null,null,"Primary Cost Centers"]
["Key","Total","Management","Secretariat","Accounting","Controlling","HR","Marketing","Trainees","Workers Council","Factory 1","Factory 2","Car Park","Production1","Production2","Production3"]
["per Head",{"$f":"SUM(C5:P5)","v":102},1,1,3,1,2,3,3,1,12,10,null,20,20,25]
["sqm",{"$f":"SUM(C6:P6)","v":2685},50,40,100,30,50,50,null,15,250,350,100,500,550,600]
["uniform",{"$f":"SUM(C7:P7)","v":14},1,1,1,1,1,1,1,1,1,1,1,1,1,1]
["Weighted",{"$f":"SUM(C8:P8)","v":16},2,1,1,1,1,2,1,1,1,1,1,1,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(C16:E16)","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":30,"n_cols":18,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["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/Keys!$B$8*Keys!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}]
["Supervisory board","uniform",3000,{"$f":"RoundToSum($C6/Keys!$B$8*Keys!C$8:P$8)","v":375},187.5,187.5,187.5,187.5,375,187.5,187.5,187.5,187.5,187.5,187.5,187.5,187.5,3000]
["Hospitality","Weighted",2000,{"$f":"RoundToSum($C7/Keys!$B$8*Keys!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Presents","Weighted",1000,{"$f":"RoundToSum($C8/Keys!$B$8*Keys!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]
["Fees, charges","uniform",500,{"$f":"RoundToSum($C9/Keys!$B$8*Keys!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]
["Car costs","Weighted",4500,{"$f":"RoundToSum($C10/Keys!$B$8*Keys!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]
["Hardware, equipment","Weighted",200,{"$f":"RoundToSum($C11/Keys!$B$8*Keys!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]
["External audit","uniform",10000,{"$f":"RoundToSum($C12/Keys!$B$8*Keys!C$8:P$8)","v":1250},625,625,625,625,1250,625,625,625,625,625,625,625,625,10000]
["Other operating expenses","sqm",5500,{"$f":"RoundToSum($C13/Keys!$B$8*Keys!C$8:P$8)","v":687.5},343.75,343.75,343.75,343.75,687.5,343.75,343.75,343.75,343.75,343.75,343.75,343.75,343.75,5500]
["Energy costs","sqm",6000,{"$f":"RoundToSum($C14/Keys!$B$8*Keys!C$8:P$8)","v":750},375,375,375,375,750,375,375,375,375,375,375,375,375,6000]
["Insurances","per Head",5000,{"$f":"RoundToSum($C15/Keys!$B$8*Keys!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]
["Legal costs","Weighted",5000,{"$f":"RoundToSum($C16/Keys!$B$8*Keys!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]
["Accounting costs","Weighted",1000,{"$f":"RoundToSum($C17/Keys!$B$8*Keys!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]
["Stationary","Weighted",2000,{"$f":"RoundToSum($C18/Keys!$B$8*Keys!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Telecommunication","Weighted",2500,{"$f":"RoundToSum($C19/Keys!$B$8*Keys!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]
["Shipping and mailing costs","Weighted",2000,{"$f":"RoundToSum($C20/Keys!$B$8*Keys!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Books, magazines","Weighted",1000,{"$f":"RoundToSum($C21/Keys!$B$8*Keys!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]
["Money transfer fees","Weighted",500,{"$f":"RoundToSum($C22/Keys!$B$8*Keys!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]
["Damages, compensation","uniform",250,{"$f":"RoundToSum($C23/Keys!$B$8*Keys!C$8:P$8)","v":31.24},15.62,15.62,15.62,15.62,31.25,15.62,15.63,15.63,15.63,15.63,15.63,15.63,15.63,249.99999999999997]
["Working clothes","Weighted",1500,{"$f":"RoundToSum($C24/Keys!$B$8*Keys!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]
["Handicapped fee","uniform",2000,{"$f":"RoundToSum($C25/Keys!$B$8*Keys!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Training and further education","Weighted",2000,{"$f":"RoundToSum($C26/Keys!$B$8*Keys!C$8:P$8)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Other operating expenses","Weighted",1500,{"$f":"RoundToSum($C27/Keys!$B$8*Keys!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]
["Total",null,{"$f":"SUM(C5:C27)","v":68950},{"$f":"SUM(D5:D27)","v":8618.74},{"$f":"SUM(E5:E27)","v":4309.37},4309.37,4309.37,4309.37,8618.75,4309.37,4309.38,4309.38,4309.38,4309.38,4309.38,4309.38,4309.38,{"$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}]

{"sheet":"2_Allocation","index":3,"n_rows":21,"n_cols":11,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["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"]
[]
["Management",111666,{"$f":"TRANSPOSE('1_Allocation'!D28:N28)","v":8618.74},{"$f":"SUM(B5:C5)","v":120284.74},{"$f":"RoundToSum($D5/Keys!$F16*Keys!C16:E16)","v":36085.42},48113.9,36085.42,{"$f":"SUM(E5:G5)","v":120284.74}]
["Secretariat",34627,4309.37,{"$f":"SUM(B6:C6)","v":38936.370000000003},{"$f":"RoundToSum($D6/Keys!$F17*Keys!C17:E17)","v":15574.55},19468.18,3893.64,{"$f":"SUM(E6:G6)","v":38936.369999999995}]
["Accounting",96834,4309.37,101143.37,{"$f":"RoundToSum($D7/Keys!$F18*Keys!C18:E18)","v":30343.01},13148.64,57651.72,101143.37]
["Controlling",83875,4309.37,88184.37,{"$f":"RoundToSum($D8/Keys!$F19*Keys!C19:E19)","v":29394.79},29394.79,29394.79,88184.37]
["HR",53765,4309.37,58074.37,{"$f":"RoundToSum($D9/Keys!$F20*Keys!C20:E20)","v":17869.04},17869.04,22336.29,58074.37]
["Marketing",239170,8618.75,247788.75,{"$f":"RoundToSum($D10/Keys!$F21*Keys!C21:E21)","v":74336.62},104071.28,69380.850000000006,247788.75]
["Trainees",147397,4309.37,151706.37,{"$f":"RoundToSum($D11/Keys!$F22*Keys!C22:E22)","v":50568.79},50568.79,50568.79,151706.37]
["Workers Council",471,4309.38,4780.38,{"$f":"RoundToSum($D12/Keys!$F23*Keys!C23:E23)","v":1470.88},1470.89,1838.61,4780.38]
["Factory 1",125225,4309.38,129534.38,{"$f":"RoundToSum($D13/Keys!$F24*Keys!C24:E24)","v":32383.59},25906.880000000001,71243.91,129534.38]
["Factory 2",2398512,4309.38,2402821.38,{"$f":"RoundToSum($D14/Keys!$F25*Keys!C25:E25)","v":480564.27},480564.28,1441692.83,2402821.38]
["Car Park",26992,4309.38,31301.38,{"$f":"RoundToSum($D15/Keys!$F26*Keys!C26:E26)","v":12520.55},9390.42,9390.41,31301.38]
["Phase 1 Allocation",null,null,null,{"$f":"'1_Allocation'!O28","v":4309.38},{"$f":"'1_Allocation'!P28","v":4309.38},{"$f":"'1_Allocation'!Q28","v":4309.38},{"$f":"SUM(E16:G16)","v":12928.14}]
["Phase 2 Allocation",{"$f":"SUM(B5:B15)","v":3318534},56021.859999999993,3374555.86,781111.51,799967.09000000008,1793477.26,3374555.86]
["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":1523480.8900000001},{"$f":"SUM(F16:F18)","v":1658276.4700000002},{"$f":"SUM(G16:G18)","v":2448146.6399999997},{"$f":"SUM(H16:H18)","v":5629904},null,"Check",{"$f":"H19-SUM(E18:G18)-SUM(B5:B15)-SUM('1_Allocation'!C5:C27)","v":0}]
[]
["Overhead rate",null,null,null,{"$f":"(E17+E16)/E18","v":1.0641694306695932},{"$f":"(F17+F16)/F18","v":0.94177572599531623},{"$f":"(G17+G16)/G18","v":2.7642946060643334},{"$f":"(H17+H16)/H18","v":1.5106376147198117}]

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