{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["3 stage","Sheet2","Sheet3","Sheet1"],"not_rendered":{"charts":4,"images":0,"macros":0,"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":"3 stage","index":0,"n_rows":26,"n_cols":12,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["Heuristic Algorithms"]
[]
["Year 0",2016]
["Sales in year 0",2]
["Asset intensity in year 0",-0.15]
[]
[null,"Yr. 1-5","Yr. 6-10","Yr. 11+",null,"Cost of capital",null,null,0.1]
["Growth (g)",{"$f":"(25/B4)^(1/5)-1","v":0.6572270086699934},0.4,0.03]
["Profitability (p)",0.02,0.04,0.08]
["Asset intensity (a)",-0.15,-0.15,-0.15]
[]
[]
["Year","Sales","g","p","a","EBIAT","NOA","FCF","Cum. FCF","EV","EV/EBIAT","EV/Sales"]
[{"$f":"B3","v":2016},{"$f":"B4","v":2},null,{"$f":"$B$9","v":0.02},{"$f":"$B$5","v":-0.15},{"$f":"B14*D14","v":0.04},{"$f":"B14*E14","v":-0.3},null,null,{"$f":"NPV($I$7,H15:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A14)","v":81.592800322670186}]
[{"$f":"A14+1","v":2017},{"$f":"B14*(1+C15)","v":3.3144540173399868},{"$f":"$B$8","v":0.6572270086699934},0.02,{"$f":"$B$10","v":-0.15},6.6289080346799742E-2,-0.49716810260099797,{"$f":"B15*(D15-(E15-(E14/(1+C15))))","v":0.2634571829477978},{"$f":"I14+H15","v":0.2634571829477978},{"$f":"NPV($I$7,H16:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A15)","v":89.488623171989417},{"$f":"J15/F15","v":1349.9753308360641},{"$f":"J15/B15","v":26.999506616721284}]
[2018,5.4928027165305888,{"$f":"$B$8","v":0.6572270086699934},0.02,{"$f":"$B$10","v":-0.15},0.10985605433061178,-0.82392040747958828,0.4366083592092021,0.7000655421569999,{"$f":"NPV($I$7,H17:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A16)","v":98.000877129979173},{"$f":"J16/F16","v":892.08444383998665},{"$f":"J16/B16","v":17.841688876799736}]
[2019,9.1028210151304005,{"$f":"$B$8","v":0.6572270086699934},0.02,{"$f":"$B$10","v":-0.15},0.18205642030260802,-1.3654231522695601,0.72355916509257989,1.4236247072495798,{"$f":"NPV($I$7,H18:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A17)","v":107.07740567788451},588.15506478653197,11.763101295730641]
[2020,15.085440841362907,{"$f":"$B$8","v":0.6572270086699934},0.02,{"$f":"$B$10","v":-0.15},0.30170881682725814,-2.2628161262044357,1.1991017907621342,2.6227264980117138,{"$f":"NPV($I$7,H19:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A18)","v":116.58604445491082},386.41908340935748,7.7283816681871498]
[2021,24.999999999999996,{"$f":"$B$8","v":0.6572270086699934},0.02,{"$f":"$C$10","v":-0.15},0.49999999999999994,-3.7499999999999991,1.9871838737955638,4.6099103718072776,{"$f":"NPV($I$7,H20:$H$25)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A19)","v":126.25746502660637},252.51493005321277,5.0502986010642559]
[2022,34.999999999999993,{"$f":"$C$8","v":0.4},{"$f":"$C$9","v":0.04},-0.15,1.3999999999999997,-5.2499999999999991,2.899999999999999,7.5099103718072762,{"$f":"NPV($I$7,H21:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A20)","v":135.98321152926701},97.130865378047886,3.8852346151219153]
[2023,48.999999999999986,{"$f":"$C$8","v":0.4},{"$f":"$C$9","v":0.04},-0.15,1.9599999999999995,-7.3499999999999979,4.0599999999999987,11.569910371807275,{"$f":"NPV($I$7,H22:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A21)","v":145.52153268219374},74.245679939894785,2.9698271975957913]
[2024,68.59999999999998,{"$f":"$C$8","v":0.4},{"$f":"$C$9","v":0.04},-0.15,2.7439999999999993,-10.289999999999997,5.6839999999999984,17.253910371807272,{"$f":"NPV($I$7,H23:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A22)","v":154.38968595041311},56.264462809917326,2.250578512396693]
[2025,96.039999999999964,{"$f":"$C$8","v":0.4},{"$f":"$C$9","v":0.04},-0.15,3.8415999999999988,-14.405999999999993,7.9575999999999967,25.211510371807268,{"$f":"NPV($I$7,H24:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A23)","v":161.87105454545443},42.136363636363619,1.6854545454545449]
[2026,134.45599999999993,{"$f":"$C$8","v":0.4},{"$f":"$C$9","v":0.04},-0.15,5.3782399999999972,-20.168399999999988,11.140639999999994,36.352150371807262,{"$f":"NPV($I$7,H25:$H$25,)+($H$26/($I$7-$D$8))/(1+$I$7)^($A$25-A24)","v":166.91751999999988},31.035714285714281,1.2414285714285711]
[2027,138.48967999999994,{"$f":"$D$8","v":0.03},{"$f":"$D$9","v":0.08},{"$f":"$D$10","v":-0.15},11.079174399999996,-20.773451999999988,11.684226399999995,48.036376771807255,{"$f":"($H$26/($I$7-$D$8))","v":171.92504559999992},15.517857142857141,1.2414285714285713]
[{"$f":"A25+1","v":2028},{"$f":"B25*(1+C26)","v":142.64437039999993},{"$f":"$D$8","v":0.03},{"$f":"$D$9","v":0.08},{"$f":"$D$10","v":-0.15},{"$f":"B26*D26","v":11.411549631999995},{"$f":"B26*E26","v":-21.396655559999989},{"$f":"B26*(D26-(E26-(E25/(1+C26))))","v":12.034753191999995},{"$f":"I25+H26","v":60.071129963807252},{"$f":"($H$26*(1+D8)/($I$7-$D$8))","v":177.08279696799991},15.517857142857142,1.2414285714285713]

{"sheet":"Sheet2","index":1,"n_rows":95,"n_cols":6,"rows_kept":95,"cut":false,"hidden":false,"merged":[]}
["a)"]
["I didn't specify if we were talking about trailing or forward multiples, who knows the diff?"]
["trailing: Dec 2021 value and 2021 sales (so past sales), foward: Dec 2021 value and 2022 sales (future sales)"]
["Let's go with trailing"]
["trailing","forward","25 growing at 40%"]
["4*25=100","4*35=140"]
["6*25=150","6*35=210"]
[]
["b)"]
["Ask value and multiple: 126.3 and 5.1 trailing, so right at midpoint"]
["show my 3 stage model"]
["once you build model, all you have to do is change the blue cells and get the result"]
["go through assumptions"]
["all values were given except first stage growth, can back it out from 25M in 2021"]
["show how to calc value in 2021"]
[]
["c)"]
["stay with model"]
["she is using the right multiple given projections, but if g lower or higher a, would her multiple still be right?"]
["Both would lower the correct multiple if in the future"]
["Do changes in first stage affect multiple? Show they don't."]
["Only future growth, profitability, asset intensity affect evolution of future cash flows, and therefore the multiple"]
["Past growth affects EV through effect on base level of sales"]
[]
["d)"]
["Post-money","125/(1.5)^5=16.5"]
["Pre-money","16.5-5=11.5"]
["Stake","5/16.5=30.4%"]
[]
["e)"]
["upon conversion must own 30.4%, since ratio is 1:1 must get same preferred as converted common"]
["total shares","1M/(1-30.4%)=1,436,266 shares"]
["VC shares","30.4%*total=436,266"]
["share price","5M/436,266=11.46"]
[]
["f)"]
["before she gets diluted, so",null,"30.4%/(1-15%)=35.7%"]
["after nothing changes",null,0.30399999999999999]
[]
["g)"]
["ask someone to draw on board"]
["draw slopes 100% until 5M FV, then 30.4%"]
["participating preferred: get Liq. Pref plus 30.4% of what's left after that in case of sale"]
["this is the double-dip feature of participating"]
["what if IPO? no participation"]
["will convert if 30.4%x>5, or x>16.5 (draw dashed line)"]
[]
["h)"]
["Value of VC stake","5+30.4%(125-5)=41.5"]
["IRR","(41.5/5)^(1/5)-1=52.7%"]
["Cash on cash","41.5/5=8.3"]
["unfair because getting more than 50% target return on which valuation was based"]
[]
["i)"]
["what value does VC need to get 50% IRR?",null,null,"30.4%*125=38"]
["check","(38/5)^(1/5)-1=50%"]
["participation value","38-5=33"]
["counter offer stake","33/(125-5)=27.5%"]
[]
["j)"]
["post-money","5/27.5%=18.2"]
["pre-money","18.2-5=13.2"]
["Total shares","1M/(1-27.5%-15%)=1,738,343"]
["VC shares","27.5%*total=477,592"]
["share price","5M/VC shares=10.5"]
[]
["k)"]
["ask someone to draw diagrams"]
["label slopes: 27.5 for participating, 30.4 for simple"]
[]
["l)"]
["must cross at 125M by construction, supposed to have equal 50% IRR for VC"]
["depends on her view on true value, simple convertible if she believes >125"]
["what are the incentives of each one?"]
["simple convertible gives stronger incentives for founder at start, then stronger incentives for VC beyond that"]
[]
["m)"]
["Ask someone to write it out on board"]
["VC stake","30.4%/(1-15%)=35.7%"]
["Post money VC","5/35.7%=14.0",null,null,null,{"$f":"5/(125/1.5^5)","v":0.30375000000000002}]
["Pre-money VC","14-5=9",null,null,null,{"$f":"F80/(1-15%)","v":0.3573529411764706}]
["Total shares","old shares/(1-new stake)",null,null,null,{"$f":"1/(1-F81)","v":1.5560640732265447}]
["shares","stake*total"]
["price","post-money/total shares"]
[]
[null,"Pre-invest","Powers","Options"]
["Pre-money",null,9,14]
["Post-money",null,14,{"$f":"F89","v":16.460905349794238}]
["Price",null,{"$f":"14*1000000/C95","v":8.9970588235294109},{"$f":"D88*1000000/D95","v":8.9917695473251023},null,{"$f":"5/((5/(125/1.5^5)/0.85))/0.85","v":16.460905349794238}]
[]
["Shares"]
["Clarke",1000000,{"$f":"B92","v":1000000},{"$f":"C92","v":1000000}]
["Powers",null,{"$f":"F81*C95","v":556064.07322654466},{"$f":"C93","v":556064.07322654466}]
["Option pool",null,null,{"$f":"15%*D95","v":274599.54233409616}]
["Total",{"$f":"SUM(B92:B94)","v":1000000},{"$f":"F82*1000000","v":1556064.0732265448},{"$f":"C95/(1-15%)","v":1830663.615560641}]

{"sheet":"Sheet3","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sheet1","index":3,"n_rows":88,"n_cols":7,"rows_kept":88,"cut":false,"hidden":false,"merged":[]}
["Question 1"]
["d) from pset 2"]
["VC stake",0.30375000000000002]
[]
["a) see graph on the right"]
["see alternative versions discussed in class"]
["at the bottom"]
[]
["V","P"]
[0,0]
[5,5]
[30,{"$f":"B11+$B$3*(A12-5)","v":12.59375}]
[]
["b)"]
["IRR",0.52654955196607878]
["Cash on cash",8.2900000000000009]
[]
["c)"]
["required stake",{"$f":"(5*1.5^5-5)/120","v":0.27473958333333331}]
[]
["d)"]
["Post-money",{"$f":"5/B19","v":18.199052132701421}]
["Pre-money",{"$f":"B22-5","v":13.199052132701421}]
["New shares",{"$f":"B19/(1-B19-15%)","v":0.47759167043911266}]
["Share price",{"$f":"5/B24","v":10.46919431279621}]
[]
["e)"]
["see graph"]
[]
[]
["V","P"]
[0,0,0]
[5,5,5]
[{"$f":"A33/B3","v":16.460905349794238},5,{"$f":"$C$33+$B$19*(A34-$C$33)","v":8.1487643604252398}]
[125,{"$f":"A35*$B$3","v":37.96875},{"$f":"$C$33+$B$19*(A35-$C$33)","v":37.96875}]
[200,{"$f":"A36*$B$3","v":60.750000000000007},{"$f":"$C$33+$B$19*(A36-$C$33)","v":58.574218749999993}]
[]
["f)"]
[125]
["Dowside protection: PCP"]
["Incentives to add value: CP"]
[]
["g) no discount/cap",{"$f":"0.5/25","v":0.02}]
["h) 30% discount",{"$f":"B43/(1-0.3)","v":2.8571428571428574E-2}]
["i) 20 million cap",{"$f":"B44","v":2.8571428571428574E-2},"Using the discount. Using the cap gives:",null,null,null,{"$f":"0.5/20.5*(1-0.2)","v":1.9512195121951223E-2}]
[]
[]
["Total shares after 1st round",null,{"$f":"1/(1-15%-$B$3)","v":1.8306636155606411}]
["Powers shares",null,{"$f":"$B$3*C48","v":0.55606407322654472}]
["Founder + option shares",null,{"$f":"C48-C49","v":1.2745995423340963}]
["share price 1st round",null,{"$f":"5/C49","v":8.9917695473251005}]
["2nd VC shares",null,{"$f":"3/3.75","v":0.8}]
[]
["j) no protection"]
["Powers' stake",null,{"$f":"C49/(C48+C52)","v":0.21137787056367435}]
[]
["k) full ratchet"]
["Powers stake",null,{"$f":"(5/3.75)/($C$50+5/3.75+$C$52)","v":0.39124401271319209}]
[]
["l) weighted average anti dilution"]
["New conversion price",null,{"$f":"C51*(C48+3/9)/(C48+C52)","v":7.3966742651824742}]
["Powers stake",null,{"$f":"(5/C61)/($C$50+5/C61+$C$52)","v":0.24575897369758729}]
[]
["m) Drag along, redemption rights"]
[]
[]
[]
["V","P",null,null,"2X liquidation preference"]
[0,0]
[10,10]
[30,{"$f":"B70+$B$3*(A71-10)","v":16.074999999999999}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["V","P",null,null,"Participation capped at 2X investment"]
[0,0]
[5,5]
[{"$f":"5+5/$B$3","v":21.460905349794238},10]
[{"$f":"10/$B$3","v":32.921810699588477},10]
[50,{"$f":"$B$3*A88","v":15.187500000000002}]