{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["ReadMe","InputSheet","ACCOUNTS","AUTHOR","INVOICES","CostSharingOptions"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":5,"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":"ReadMe","index":0,"n_rows":32,"n_cols":17,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["THIS SPREADSHEET CALCULATES  AMOUNTS OWING FOR MAINTENANCE BY EACH PROPERTY OWNER ON A PRIVATE DEAD-END ROAD"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Original October, 2020 - George Zurakowski"]
[]
["1.","Application: ",null,"This spreadsheet calculates costs of snowplowing or road maintenance with options for seasonal/full-time and other weights. "]
[]
["2.","Cost sharing formula:  ",null,"Based on a pro-rata approach: Each parcel owner’s share of costs incurred is determined as follows: Pro-rated cost share is "]
[null,null,null,"based upon the percentage of roadway extending from the start of the Private Road to the intersection of each"]
[null,null,null,"driveway (can use mid-point) where a residence exists."]
[]
["3.","Doubts on cost-sharing  ",null,"See the CostSharingOptions sheet. Please note that for a private road exiting at both ends onto a public road the best cost-",null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,"approach?",null,"sharing approach is probably an equal split among all property owners, barring other factors such as seasonal "]
[null,null,null,"versus full-time use, commercial versus homes, etc."]
[]
["4.","Where to start:",null,"Begin by inputting data on the InputSheet. You will enter a name/address, then the distance from the public road to each driveaway "]
[null,null,null,"(driveway mid-point, say in miles, km, feet or metres, as you like) for each property access. Enter OTHER and SEASONAL WEIGHTS."]
[]
[null,null,null,"For snow removal, for example, some residents may only use their properties a few times in winter, so they may be assigned a WEIGHT"]
[null,null,null,"between 0% & 100%. OTHER WEIGHTS can be used to account for hilly terrain, flat road, etc. This can be less than or more than 100%."]
[]
[null,null,null,"Finally, place an X beside each OWNER's name that is participating in the cost-sharing, presumably all for summer road maintenance"]
[null,null,null,"but maybe a smaller number of names if the sheet is being used for snow-removal cost-sharing."]
[]
["5.","What happens:",null,"The InputSheet will calculate and filter the rows containing Active (X) OWNERS and produce side by side results. It will also output a table"]
[null,null,null,"into the ACCOUNTS sheet where date and payment information can be entered for each owner against that OWNER's allocated cost."]
[]
[null,null,null,"After all data has been entered on the InputSheet, only the ACCOUNTS sheet should be used. If changes to the number of ACTIVE"]
[null,null,null,"OWNERS is made on the InputSheet, the ACCOUNTS figures may become misaligned, requiring re-entry."]
[]
[null,null,null,"The INVOICES Sheet can be used for keeping a record of Contractor costs."]
[]
["6.","Questions?",null,"mail@georgezurakowski.com",null,null,"(October, 2020)"]

{"sheet":"InputSheet","index":1,"n_rows":37,"n_cols":21,"rows_kept":37,"cut":false,"hidden":false,"merged":[[10,12,10,13],[11,12,11,13],[10,1,10,2],[11,1,11,2],[2,12,3,19],[8,1,8,2],[8,12,8,13],[9,1,9,2],[7,12,7,13],[7,1,7,2],[9,12,9,13],[3,6,3,7],[16,1,16,2],[12,1,12,2],[14,1,14,2],[1,1,1,7],[30,1,30,2],[22,1,22,2],[24,1,24,2],[23,1,23,2],[18,1,18,2],[19,1,19,2],[20,1,20,2],[15,1,15,2],[21,1,21,2],[17,1,17,2],[13,1,13,2],[31,1,31,2],[25,1,25,2],[26,1,26,2],[27,1,27,2],[28,1,28,2],[29,1,29,2]]}
["ENTER NAMES, DISTANCES to all Driveway Midpoints (km) & WEIGHTS"]
[null,"Change only green items on this side. Place an \"X\" for those opting in."]
[null,null,null,null,null,null,null,null,null,null,null,null,"This side is generated from the \"INPUT\" side at left - Do not touch"]
[null,null,"ENTER SEASONAL TOTAL COST HERE:",null,null,null,10000]
[]
["ACTIVE","OWNER",null,"DISTANCE ","DISTANCE ","OTHER  %","SEASONAL","TOTAL %","TOTAL  %","$ COST for",null,"ACTIVE","OWNER",null,"DISTANCE ","DISTANCE ","OTHER  %","SEASONAL","TOTAL %","TOTAL  %","$ COST for"]
["X",null,null,"(km)","WEIGHT %","  WEIGHT","WEIGHT %","  FACTOR","  WEIGHTS "," ACTIVES",null,"X",null,null,"(km)","WEIGHT %","  WEIGHT","WEIGHT %","  FACTOR","  WEIGHTS "," ACTIVES"]
[null,"Owner 1",null,1.1000000000000001,{"$f":"D8/$D$33","v":5.0667894979272224E-2},100,100,{"$f":"IF(A8=\"\",0,E8*G8/100*F8/100)","v":0},{"$f":"H8/$H$33","v":0},{"$f":"$G$4*I8","v":0},null,"x","Owner 2",null,1.2,{"$f":"O8/$D$33","v":5.5274067250115147E-2},100,20,{"$f":"IF(L8=\"\",0,P8*R8/100*Q8/100)","v":1.1054813450023029E-2},{"$f":"S8/$H$33","v":4.0677966101694905E-2},{"$f":"$G$4*T8","v":406.77966101694904}]
["x","Owner 2",null,1.2,5.5274067250115147E-2,100,20,{"$f":"IF(A9=\"\",0,E9*G9/100*F9/100)","v":1.1054813450023029E-2},4.0677966101694905E-2,406.77966101694904,null,"x","Owner 4",null,2,9.2123445416858588E-2,100,100,{"$f":"IF(L9=\"\",0,P9*R9/100*Q9/100)","v":9.2123445416858588E-2},0.33898305084745761,3389.8305084745762]
[null,"Owner 3",null,0.9,4.1455550437586364E-2,100,100,{"$f":"IF(A10=\"\",0,E10*G10/100*F10/100)","v":0},0,0,null,"X","Owner 5",null,2.1,9.6729617687701525E-2,100,50,{"$f":"IF(L10=\"\",0,P10*R10/100*Q10/100)","v":4.8364808843850762E-2},0.17796610169491525,1779.6610169491526]
["x","Owner 4",null,2,9.2123445416858588E-2,100,100,{"$f":"IF(A11=\"\",0,E11*G11/100*F11/100)","v":9.2123445416858588E-2},0.33898305084745761,3389.8305084745762,null,"X","Owner 7",null,0.31,1.4279134039613081E-2,100,100,{"$f":"IF(L11=\"\",0,P11*R11/100*Q11/100)","v":1.4279134039613083E-2},5.2542372881355937E-2,525.42372881355936]
["X","Owner 5",null,2.1,9.6729617687701525E-2,100,50,{"$f":"IF(A12=\"\",0,E12*G12/100*F12/100)","v":4.8364808843850762E-2},0.17796610169491525,1779.6610169491526,null,"X","Owner 9",null,2.2999999999999998,0.10594196222938737,100,100,0.10594196222938738,0.38983050847457629,3898.305084745763]
[null,"Owner 6",null,3,0.13818516812528789,100,100,0,0,0]
["X","Owner 7",null,0.31,1.4279134039613081E-2,100,100,1.4279134039613083E-2,5.2542372881355937E-2,525.42372881355936]
[null,"Owner 8",null,3.8,0.1750345462920313,100,100,0,0,0]
["X","Owner 9",null,2.2999999999999998,0.10594196222938737,100,100,0.10594196222938738,0.38983050847457629,3898.305084745763]
[null,"Owner 10",null,5,0.23030861354214646,100,100,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,{"$f":"H24/$H$33","v":0},0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,{"$f":"H26/$H$33","v":0},0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,null,null,null,0,null,null,0,0,0]
[null,"SUMS",null,{"$f":"SUM(D8:D32)","v":21.71},{"$f":"SUM(E8:E32)","v":0.99999999999999989},null,null,{"$f":"SUM(H8:H32)","v":0.27176416397973285},{"$f":"SUM(I8:I32)","v":1},{"$f":"SUM(J8:J32)","v":10000},null,null,"SUMS",null,null,null,null,null,{"$f":"SUM(S8:S32)","v":0.27176416397973285},{"$f":"SUM(T8:T32)","v":1},{"$f":"SUM(U8:U32)","v":10000}]
[]
[]
["NOTES:","OTHER WEIGHT :","can be due to lesser or higher burden for some reason (road difficulty, say) and can be less or more than 100%",null,null,null,null,null,null,null,null,null,"The above sums are for checking purposes only."]
[null,"DISTANCE  :","can be any distance measures - miles, km, meters, feet etc - change to suit."]

{"sheet":"ACCOUNTS","index":2,"n_rows":39,"n_cols":9,"rows_kept":39,"cut":false,"hidden":false,"merged":[[2,1,2,6],[6,1,7,2],[11,1,11,2],[12,1,12,2],[10,1,10,2],[8,1,8,2],[9,1,9,2]]}
[null,"Record of Owner Payments and Amounts Owing"]
[]
[null,"Period Covered:  November 2020 - April 2021"]
[]
[]
[null,null,null,null,"(mm/dd/yy)",null,"(mm/dd/yy)"]
["ACTIVE","OWNER",null,"$ COST for","Payment","Payment","Payment","Payment","Balance"]
["X",null,null," ACTIVES"," Date 1",1," Date 2",2,"Owing"]
["x","Owner 2",null,406.77966101694904,null,null,null,null,{"$f":"IF(A9=\"\",\" \",D9-F9-H9)","v":406.77966101694904}]
["x","Owner 4",null,3389.8305084745762,null,null,null,null,{"$f":"IF(A10=\"\",\" \",D10-F10-H10)","v":3389.8305084745762}]
["X","Owner 5",null,1779.6610169491526,null,1000,null,null,779.66101694915255]
["X","Owner 7",null,525.42372881355936,null,null,null,null,525.42372881355936]
["X","Owner 9",null,3898.305084745763,null,null,null,null,3898.305084745763]
[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,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,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,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,null,null,null,null," "]
[null,null,null,null,null,null,null,null," "]
[null,null,null,null,null,null,null,null," "]
[null,null,"SUMS",{"$f":"SUM(D9:D35)","v":10000},null,{"$f":"SUM(F9:F35)","v":1000},null,{"$f":"SUM(H9:H35)","v":0},{"$f":"SUM(I9:I35)","v":9000}]
[]
[]
[null,"This sheet is generated from the data entered in the \"InputSheet\""]

{"sheet":"AUTHOR","index":3,"n_rows":3,"n_cols":1,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
[]
[]
["This cost-sharing spreadsheet was developed by Gorge Zurakowski :-)"]

{"sheet":"INVOICES","index":4,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":false,"merged":[[4,1,4,2],[9,0,9,3],[2,0,2,4],[3,3,3,4],[4,3,4,4],[5,3,5,4],[7,0,7,4],[17,3,17,4],[18,3,18,4],[19,3,19,4],[20,3,20,4],[12,3,12,4],[13,3,13,4],[14,3,14,4],[15,3,15,4],[16,3,16,4],[21,3,21,4],[22,3,22,4],[23,3,23,4],[24,3,24,4],[25,3,25,4],[26,3,26,4],[27,3,27,4],[28,3,28,4],[29,3,29,4],[30,3,30,4]]}
["Date: ","2012-04-15"]
[]
["Blah, blah,  blah,  Road Association of Some town in Some Country"]
[]
["Contact: ","Some person(s)"]
["Telephone: ","616-xxx-xxxx","616-zzz-zzzz"]
[]
["RECORD of INVOICES"]
[]
["TIME PERIOD:   December 10, 2011 to April 15, 2012"]
[]
["(mm/dd/yy)"]
["Date","Contractor","Amount","Comments"]
["2011-12-08","Contractor 1",1000,"Snow Clearing"]
["2011-12-21","Contractor 2",500,"Widening road using farm tractor because too much snow"]
["2012-03-04","Contractor 3",500,"some other"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"TOTAL",{"$f":"SUM(C14:C31)","v":2000}]

{"sheet":"CostSharingOptions","index":5,"n_rows":58,"n_cols":12,"rows_kept":58,"cut":false,"hidden":false,"merged":[[52,4,52,5],[8,0,8,3],[19,0,19,3],[31,0,31,3],[39,10,39,11],[43,0,43,3],[49,4,49,5],[50,4,50,5],[51,4,51,5]]}
["VARIOUS APPROACHES TO COST-SHARING FOR PRIVATE ROAD RESIDENTS ",null,null,null,null,null,null,null,null,null,"(Snowplowing, for instance)"]
[]
["There are various philosophies on the division of road-maintenance costs!"]
["For illustrative and comparison purposes, I have created a scenario where there are 4 owners at 1km, 5 km, 6km and 8 km from the main road. The total yearly cost"]
["of a service (snowplowing, for example) is assumed to be $100 per km. Thus the annual cost for this assumed 8km road is $800. This does not consider the seasonal"]
["versus permanent residents issue or part-season questions or road section difficulty."]
[]
[]
["Incremental Distance Approach",null,null,null,"Thinking: How much extra to plow/maintain next resident?"]
[null,null,null,null," This approach subsidizes distant owners at the expense of those close to the junction with public road or those with big "]
[null,null,null,null," intervals to previous owner. Owners are not charged for their proportional use of the road, just their last segment."]
["M          | |"]
["A          | |"]
["I          | |","<--$100-->","<--------------------  $400  ---------------------------->",null,null,null,"<--$100-->","<---------- $200 -------->"]
["N          | |","**********1","************","*************","*************","************5","************6","*************","************8"]
["           | |"]
["R          | |"]
["D          | |"]
[]
["Simple Group Approach",null,null,null,"Thinking: Divide all costs equally. Works reasonably well for a road with access from both ends but not a dead end road"]
[null,null,null,null,"where the vast majority of traffic is out to the main road and back. Folks close to the main road subsidize distant folks. "]
[null,null,null,null,"For example, the first resident 1km from the main road pays $200 yet the cost to clear the road to his place is only $100."]
[null,null,null,null,"No consideration is given to kilometric road usage or that most users must traverse/use segments other than their own."]
["M          | |",null,null,null,"The first owner might opt out of the sharing agreement and just pay $100 to get the road plowed to his place?"]
["A          | |"]
["I          | |","<--$200-->","<--------------------  $200  ---------------------------->",null,null,null,"<--$200-->","<---------- $200 -------->"]
["N         | |","**********1","************","*************","*************","************5","************6","*************","************8"]
["           | |"]
["R          | |"]
["D          | |"]
[]
["Distance Factor Approach",null,null,null,"Thinking: Costs based on distance travelled (usage factor). Distance to public road from each resident is measured and "]
[null,null,null,null,"each resident pays for the percentage of roadway extending to the public road from his driveway."]
[null,null,null,null,"Examples: Resident at the 8km mark pays 8/20 X $800 =$320. The resident 1km from the road pays 1/20 X $800 = $40."]
[null,null,null,null,"Rationale: The further portions are only used by those living there. The closer portion(s) are used by everyone. "]
["M          | |"]
["A          | |"]
["I          | |","<---$40-->","<--------------------  $200  ---------------------------->",null,null,null,"<--$240-->","<---------- $320 -------->"]
["N          | |","**********1","************","*************","*************","************5","************6","*************","************8"]
["           | |","\"1/20\"",null,null,null,"\"5/20\"","\"6/20\"",null,"\"8/20\"",null,"1+5+6+8=20"]
["R          | |"]
["D          | |"]
[]
["Food  for Thought",null,null,null,"An experiment illustrates why residents closer to the main road pay less than the cost of plowing to their residences."]
[null,null,null,null,"Assume that our four residents initially lived at 1km with driveways radiating from a cul-de-sac. Only 1km, so only $100"]
[null,null,null,null,"total. They would split the costs evenly. Each woud pay $25. Now let's pretend that 3 of these residents move 0.1, 0.2  "]
[null,null,null,null,"and 0.3 km further up the road. The total bill is now for 1.3km or $130. Should this change the bill for the first resident?"]
[null,null,null,null,"Below are the results for the 3 computations (Incremental, Simple Group, Distance (Usage) Factor:"]
[]
[null,null,null,null,"Approach",null,"House 1","House 2","House 3","House 4",null,"Total"]
[null,null,null,null,"Incremental",null,100,10,10,10,null,{"$f":"SUM(G51:J51)","v":130}]
[null,null,null,null,"Simple Group",null,32.5,32.5,32.5,32.5,null,130]
[null,null,null,null,"Distance Factor",null,28.26,31.09,33.909999999999997,36.74,null,{"$f":"SUM(G53:J53)","v":130}]
[]
[null,null,null,null,"The Distance Factor comes close to keeping House #1 near $25 when all 4 were at 1km and also results in intuitive"]
[null,null,null,null,"modest increases for Houses 2, 3 and 4. But look what happens when the neighbours move further out using"]
[null,null,null,null,"the example in the \"Distance Factor Approach\" in pink, above."]
[null,null,null,null,"House #1 now pays $40. So the further his neighbours go, the more it costs House #1 - h'mmm ???. I can't answer this."]