{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["version5","speed elasticity"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":2,"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":"version5","index":0,"n_rows":88,"n_cols":7,"rows_kept":88,"cut":false,"hidden":false,"merged":[]}
[1,"RR 514 The contribution of public transport to economic productivity - spreadsheet developed to show how the Venables' model responds to a PT intervention"]
[null,"Spreadsheet developed by Tim Hazledine"]
[2,"Implementing the Venables' model for an improvement in public transit",null,null,null,null,"NOTE: all numbers highlighted in green must be supplied by the analyst"]
[3,null,"Period 0 (base)","Period 1 (after change)","Equation","Graph-ical"]
[4,"Value of additional output from existing commuters, $millions/year",null,{"$f":"(D14-C14)*C22*C19/1000","v":0.17099708848826936},null,"A","The productivity of the original commuters benefits from the AE effects of the increase in worker numbers."]
[5,"Net value of additional output from new commuters, $millions/year",null,{"$f":"(D22-C22)*D20*D21/1000*D13","v":2.7224559240164306},null,"B","This is the tax wedge of output that doesn't get soaked up in additional travel costs."]
[6,"Travel cost savings of existing commuters, $millions/year",null,{"$f":"(C87-D87)/1000000","v":2.5624995363567846},null,"E","The original number of workers benefit from the shift in the travel cost function."]
[7,"Net benefit to new commuters, $millions/year",null,{"$f":"0.5*C19*(D22-C22)*(D15-C45*C36/D36)/1000","v":0.14392169519810227},null,"D","Rule-of-half' triangle approximating the rents to new workers net of their additional travel costs."]
[8,"Net value additional output, unaffected commuters, $millions/year",null,{"$f":"(C23+0.5*(D23-C23))*C13*C19*(D14-C14)/1000","v":0.3951640682539389},null,null,"Nearly all of this is the tax wedge."]
[9,"Total efficiency gains, $millions/year",null,{"$f":"SUM(D5:D9)","v":5.9950383123135254},null,null,"This is the productivity gain bottom line  (including travel cost savings)."]
[10]
[11,"Travel cost of the marginal (nth) commuter, daily, $",{"$f":"C15","v":43.695652173913039},{"$f":"IF(D25=1, D45, F45)","v":43.745464369255274},null,"MTC"]
[12,"Income tax rate",0.33,{"$f":"C13","v":0.33},null,"t"]
[13,"Daily productivity (wage) premium, $",{"$f":"C21*C20/C19","v":65.217391304347828},{"$f":"D85","v":65.291737864560119},null,null,"The daily before-tax productivity premium earned by CBD workers over their outside option."]
[14,"Daily after tax net CBD premium,  $",{"$f":"C14*(1-C13)*C16","v":43.695652173913039},{"$f":"D14*(1-D13)","v":43.745464369255274},null," "]
[15,"Non-wage CBD worker utility factor",1,null,null,null,"If people have a preference for/against working in CBD, apart from wages. If =1, implies no preference."]
[16," ",null,{"$f":"D12-D15","v":0},null,0]
[17,"Number of trips per work day",2]
[18,"Number of workdays per year",230]
[19,"Annual outside-option wage, $",60000,{"$f":"C20","v":60000}]
[20,"CBD wage premium ratio",0.25,{"$f":"D14*C19/D20","v":0.25028499514748048},null,null,"So, here assume a CBD wage/productivity premium of 25% on top of average outside option wage of $60K."]
[21,"Directly affected CBD commuting workers, 000s, N",10,{"$f":"C22*F22","v":10.54936483051732},{"$f":"(D12-D15)*1000000","v":0},1.054936483051732," This is the number you ADJUST to find equilibrium: ie where marginal travel cost= after tax wage premium. "]
[22,"Unaffected CBD commuting workers, 000s, U",70,{"$f":"(D44/C44)^(1/(C39+C66))*C23/C22*D22","v":70.056989751487222},null," "]
[23,"Commuting mode share public transit ",0.6,{"$f":"IF(D25=1,D26,D68)","v":0.7},null," "]
[24,"SWITCH to change source of commuting mode share  number",null,1,null,null,"If switch = 1, then spreadsheet chooses exogenous mode share; otherwise chooses modelled mode share"]
[25,"Exogenous new commuting mode share",null,0.7,null,null,"START WITH THIS --ie, the PT innovation is here described in terms of its effect on JTW mode choice shares"]
[26,"Modelled new commuting mode share"," ",{"$f":"C28*(D30/D29)^D31*D22^D32","v":0.69653549048353525},null,null,"This allows the mode share to be modelled as function of relative prices and distance (number of commuters)"]
[27,null,{"$f":"C24/((C29/C30)^C31*C22^C32)","v":0.47659694083456888}," "]
[28,"Full price of private vehicle commute, Ppv",1,1]
[29,"Full price of public transit commute, Ppt",1,0.75]
[30,"Elasticity of PV mode share wrt relative price",-0.5,-0.5]
[31,"Elasticity of PV mode share wrt distance",0.1,0.1,null,null,"Allows possibility of mode share differing according to distance of commute"]
[32,"Average trip time, minutes",23,{"$f":"D37/D36*60","v":23.026219553568215}]
[33,"Trip time of marginal commuter, TN",{"$f":"C67*C52^C66*C71*C22^(C39+C66)","v":46.000000000000028},{"$f":"C67*D52^C66*C71*D22^(C39+C66)","v":46.052439107136436}," "]
[34,"Free-flow traffic speed, km/hour",50]
[35,"Actual trip speed, km/hour",{"$f":"C35*C41","v":37.5},{"$f":"D69/D34*60","v":39.515071699692903}]
[36,"Average trip  distance, kms, d",{"$f":"C36*C33/60","v":14.375},{"$f":"D69^(2+C38)*(C70/(2+C38))/D22","v":15.164711943868646}]
[37,"Commuter/distance parameter, theta",0," "]
[38,"Sigma",{"$f":"1/(1+C38)","v":1}," "]
[39,"Exponent in trip-time model, beta",4]
[40,"Ratio free-flow trip time to actual average trip time",0.75]
[41,"Congestion parameter, b",{"$f":"C40*(1-C41)","v":1}," "]
[42,"Average travel time function scale parameter, a",{"$f":"(C33/C37)/((C55*C52)^C42)","v":0.20904522613065327}," "]
[43,"Travel cost of marginal commuter scale parameter, r",{"$f":"C15/(C22^(C39+C66))","v":1.7395552452446268},{"$f":"C44*(D52/C52)^C66","v":1.6159063754060023}]
[44,"CHECK travel cost of marginal commuter, daily, $",{"$f":"C44*C22^(C39+C66)","v":43.695652173913039},{"$f":"2*C72*C67*D52^C66*C71*D22^(C39+C66)","v":43.745464369255274},{"$f":"2*C72*C67*C52^C66*C71*C22^(C39+C66)","v":43.695652173913039}]
[45,"Private vehicle commuters, 000s",{"$f":"C22*(1-C24)","v":4},{"$f":"D22*(1-D24)","v":3.1648094491551961},null,null,"assumes new mode split applies to new commuters"]
[46,"Commuters per private vehicle",1.3]
[47,"Number affected commuting private vehicles, 000s",{"$f":"C46/C47","v":3.0769230769230766},{"$f":"D46/C47","v":2.4344688070424585},null,{"$f":"C22*(1-D68)/C47","v":2.3629205920957621}]
[48,"Number unaffected commuting private vehicles, 000s",{"$f":"C23*(1-C24)/C47","v":21.538461538461537},{"$f":"D23*(1-C24)/C47","v":21.555996846611453}]
[49,"PT commuters, 000s",{"$f":"C22*C24","v":6},{"$f":"D22*D24","v":7.384555381362123}]
[50,"PT/PV congestion equivalent factor",8,null,null,null,"number of PT commuters to generate as much congestion as one private vehicle"]
[51,"Private vehicle-equivalent scalar, rho",{"$f":"C24/$C51+(1-C24)/$C47","v":0.38269230769230772},{"$f":"D24/$C51+(1-D24)/$C47","v":0.31826923076923075},{"$f":"C52^C66","v":0.6809886805748856},{"$f":"D52^C66","v":0.63258350289733545}]
[52,"Number of non-commuter travellers per peak period, 000s",10," ",null,null,"average number of non-commuters travelling in a 2-hour peak period"]
[53,"Commuter/non-commuter equivalence factor, gamma",1,null,null,null,"number of non-commuters with same travel time impact as one commuter"]
[54,"Total equivalent peak travellers",{"$f":"C22+C54*C53","v":20}]
[55,"Trip time/km of current travellers",{"$f":"C43*(C52*C55)^C42","v":1.6},{"$f":"C43*(D52*C55)^C42","v":1.3306532663316581}," ",null,"after the innovation, what would be the new trip time if numbers travelling don't change?"]
[56,"x% change traveller numbers for linearisation",0.05,null," "]
[57,"Trip time/km of x% more travellers",{"$f":"C43*(C52*C55*(1+C57))^C42","v":1.6800000000000002},{"$f":"C43*(C52*C58)^C42","v":0.13440000000000002}," "]
[58,"Slope of linear approximation to trip-time equation, c",{"$f":"(C56-C58)/(C55-C55*(1+C57))","v":8.0000000000000071E-2}]
[59,"Intercept of linear approximation to trip-time equation, t0",{"$f":"C56-C59*C55","v":0}]
[60,"Elasticity non-commuters wrt trip time",-0.5,null,null,null,"% change in number of non-commuters for a 1% change in trip time/km"]
[61,"Slope of linear approximation to non-commuter numbers equation, m",{"$f":"C61*C53/C56","v":-3.125}]
[62,"Intercept of linear approximation to non-commuter numbers equation, M0",{"$f":"C53-C62*C56","v":15}]
[63,"Intercept of linear relation between t and N, g",{"$f":"(C60+C59*C54*C63)/(1-C59*C54*C62)","v":0.96000000000000063}]
[64,"Slope of linear relation between t and N, h",{"$f":"C59/(1-C59*C54*C62)","v":6.4000000000000043E-2}]
[null,"Trip-time elasticity with respect to N, e",{"$f":"C65*C22/C56","v":0.40000000000000024}]
[null,"Intercept of exponential trip time function, A",{"$f":"C56/((C52*C22)^C66)","v":0.93536279097600949}]
[67,"Mode share of original commuters when this is modelled endogenously",null,{"$f":"C28*(D30/D29)^D31*C22^D32","v":0.69282032302755092}]
[68,"Distance travelled by marginal commuter, kms, z",{"$f":"C37*(C38+2)/(C38+1)","v":28.75},{"$f":"D22^C39*C71","v":30.329423887737292}," "]
[null,"Distribution function scale parameter, k",{"$f":"C22*(C38+1)/(C69^(C38+1))","v":0.34782608695652173},null," "]
[null,"K",{"$f":"((C38+1)/C70)^C39","v":2.875}]
[null,"Perceived cost of commuting, $/min, w",{"$f":"C44/(2*C67*C71*C52^C66)","v":0.47495274102079355}]
[null,"CBD LAND USAGE BLOCK"]
[null,"Space needed per private vehicle entering CBD, m2",25,null,null,null,"Parking + moving around the CBD"]
[null,"Space -saving factor",0.5,null,null,null,"Assume don't get all of it back for productive use"]
[null,"Total private vehicle space, km2",{"$f":"(C48+C49)*C74/1000","v":0.61538461538461531}]
[null,"Total area CBD, km2",4," ",null,null,"The three AUs making up the Auckland CBD cover approximately 4 km2 "]
[null,"Ratio productive/total land",0.5]
[null,"Total CBD area available for productive use",{"$f":"C77*C78","v":2},null,null,null,"Subtract land used for parks, churches, roads, apartments, universities, parking  etc"]
[null,"One-off space augmentation, km2"," ",{"$f":"(D24-C24)*C48*C75*C74/1000","v":3.8461538461538451E-3},null,null,"CBD land made available for productive use because of fewer cars "]
[null,"AGGLOMERATION ECONOMIES BLOCK"]
[null,"AE employment exponent, delta",0.1]
[null,"AE available land elasticity,  neta",0.2]
[null,"AE function intercept, B",{"$f":"C14/((C22+C23)^C82)","v":42.077935574883654},{"$f":"C84*((C79+D80)/C79)^C83","v":42.094106961510271}]
[null,"Productivity premium, output/worker/day",{"$f":"C84*(C22+C23)^C82","v":65.217391304347828},{"$f":"D84*(D22+D23)^C82","v":65.291737864560119}," ",null,"This is a fairly standard production function: cf. Venables + Ciccione/Hall"]
[null,"TRAVEL COST BLOCK"]
[null,"Total travel costs of original affected commuters, $/year",{"$f":"C18*C19*C72*C33*C22*1000","v":50249999.999999955},{"$f":"C87*(C36/D36)","v":47687500.463643171}]
[null,"Total travel costs of all affected commuters, $/year",{"$f":"C87","v":50249999.999999955},{"$f":"C18*C19*C72*D33*D22*1000","v":53070989.280842051}]

{"sheet":"speed elasticity","index":1,"n_rows":29,"n_cols":11,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
["Calculating elasticity of trip time with respect to reduction in number of vehicle trips "]
[null," "," "," "," ",780," "," "]
[]
[]
[null,null,null,null,null,"trip change"]
[null," ","Tf","alpha","beta",0.05,"Vcap","marginal","new time","elasticity","speed % of freeflow"]
[null,22.9,20,0.15,4,{"$f":"F$2*(1-F$6)","v":741},{"$f":"F$2*((B7-C7)/(C7*D7))^(-1/E7)","v":786.63889644763424},{"$f":"C7+C7*(E7+1)*D7*(F7/G7)^E7","v":31.810340624999988},{"$f":"C7*(1+D7*(F7/G7)^E7)","v":22.362068125},{"$f":"((B7-I7)/I7)/F$6","v":0.48111102425147284},{"$f":"C7/B7*100","v":87.336244541484717}]
[null,22.9,18,0.15,4,{"$f":"F$2*(1-F$6)","v":741},{"$f":"F$2*((B8-C8)/(C8*D8))^(-1/E8)","v":672.02669313212709},{"$f":"C8+C8*(E8+1)*D8*(F8/G8)^E8","v":37.955403124999997},{"$f":"C8*(1+D8*(F8/G8)^E8)","v":21.991080624999999},{"$f":"((B8-I8)/I8)/F$6","v":0.82662547648224083},{"$f":"C8/B8*100","v":78.602620087336248}]
[null,22.9,16,0.15,4,{"$f":"F$2*(1-F$6)","v":741},599.01147276037852,{"$f":"C9+C9*(E9+1)*D9*(F9/G9)^E9","v":44.100465624999991},21.620093124999997,{"$f":"((B9-I9)/I9)/F$6","v":1.1839975596774881},69.86899563318778]
[null,22.9,14,0.15,4,{"$f":"F$2*(1-F$6)","v":741},543.62813442230356,{"$f":"C10+C10*(E10+1)*D10*(F10/G10)^E10","v":50.245528124999986},21.249105624999999,{"$f":"((B10-I10)/I10)/F$6","v":1.5538483399110121},61.135371179039311]
[null," ",null,null,"trip change",7.0000000000000007E-2," ",null," "," "]
[null,22.9,20,0.15,4,{"$f":"F$2*(1-F$11)","v":725.4},786.63889644763424,{"$f":"C12+C12*(E12+1)*D12*(F12/G12)^E12","v":30.846754144999991},22.169350829000003,{"$f":"((B12-I12)/I12)/F$11","v":0.47082322709901225},87.336244541484717]
[null,22.9,18,0.15,4,{"$f":"F$2*(1-F$11)","v":725.4},672.02669313212709,{"$f":"C13+C13*(E13+1)*D13*(F13/G13)^E13","v":36.327274244999998},21.665454849,{"$f":"((B13-I13)/I13)/F$11","v":0.81403134265671862},78.602620087336248]
[null,22.9,16,0.15,4,{"$f":"F$2*(1-F$11)","v":725.4},599.01147276037852,{"$f":"C14+C14*(E14+1)*D14*(F14/G14)^E14","v":41.807794344999998},21.161558869,{"$f":"((B14-I14)/I14)/F$11","v":1.1735843022595602},69.86899563318778]
[null,22.9,14,0.15,4,{"$f":"F$2*(1-F$11)","v":725.4},543.62813442230356,{"$f":"C15+C15*(E15+1)*D15*(F15/G15)^E15","v":47.288314444999997},20.657662889000001,{"$f":"((B15-I15)/I15)/F$11","v":1.5506781900801287},61.135371179039311]
[null," ",null," ","trip change",0.03," ",null," "," "," "]
[null,22.9,20,0.15,4,{"$f":"F$2*(1-F$16)","v":756.6},786.63889644763424,{"$f":"C17+C17*(E17+1)*D17*(F17/G17)^E17","v":32.836745744999988},22.567349148999995,{"$f":"((B17-I17)/I17)/F$16","v":0.4913453337735717},87.336244541484717]
[null,22.9,18,0.15,4,{"$f":"F$2*(1-F$16)","v":756.6},672.02669313212709,{"$f":"C18+C18*(E18+1)*D18*(F18/G18)^E18","v":39.689673845000016},22.337934769,{"$f":"((B18-I18)/I18)/F$16","v":0.83873052248324176},78.602620087336248]
[null,22.9,16,0.15,4,{"$f":"F$2*(1-F$16)","v":756.6},599.01147276037852,{"$f":"C19+C19*(E19+1)*D19*(F19/G19)^E19","v":46.542601944999994},22.108520388999999,{"$f":"((B19-I19)/I19)/F$16","v":1.1933251631405677},69.86899563318778]
[null,22.9,14,0.15,4,{"$f":"F$2*(1-F$16)","v":756.6},543.62813442230356,{"$f":"C20+C20*(E20+1)*D20*(F20/G20)^E20","v":53.395530044999987},21.879106008999997,{"$f":"((B20-I20)/I20)/F$16","v":1.555356040873052},61.135371179039311]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,{"$f":"640/23","v":27.826086956521738},{"$f":"179/1134","v":0.15784832451499117},{"$f":"J29*60","v":9.4708994708994698}]