31:T5af,html`

Renewal Rates

${bind(html``, viewof rrCF)} ${bindText(html``, viewof rrCF, formatPercent)} crowdfunders
${bind(html``, viewof rrY1)} ${bindText(html``, viewof rrY1, formatPercent)} newcomers
${bind(html``, viewof rrY2)} ${bindText(html``, viewof rrY2, formatPercent)} second renewal

Financial Parameters

${bind(html``, viewof newMemberships)} ${bindText(html``, viewof newMemberships)} new memberships per month
${bind(html``, viewof newBudget)} ${bindText(html``, viewof newBudget, formatPercent)} budget
${bind(html``, viewof newInvestment)} CHF ${bindText(html``, viewof newInvestment, n => n / 100 / 1000000)} Mio. investment (in Jun 19)
${bind(html``, viewof newPrice)} CHF ${bindText(html``, viewof newPrice, n => n / 100)} price (from Feb 19)

`32:T1d4f,membershipsWithExpire = [ { "created": "17-04", "expire": "18-12", "prev": "18-12", "count": 1 }, { "created": "17-04", "expire": "19-01", "prev": "19-01", "count": 5812 }, { "created": "17-04", "expire": "19-03", "prev": "19-03", "count": 21 }, { "created": "17-04", "expire": "19-04", "prev": "19-04", "count": 1 }, { "created": "17-04", "expire": "19-05", "prev": "19-05", "count": 1 }, { "created": "17-04", "expire": "19-12", "prev": "19-12", "count": 2 }, { "created": "17-04", "expire": "20-01", "prev": "19-01", "count": 1627 }, { "created": "17-04", "expire": "20-02", "prev": "19-01", "count": 845 }, { "created": "17-04", "expire": "20-03", "prev": "19-01", "count": 1419 }, { "created": "17-04", "expire": "20-03", "prev": "19-03", "count": 8 }, { "created": "17-05", "expire": "19-01", "prev": "19-01", "count": 2380 }, { "created": "17-05", "expire": "19-02", "prev": "19-02", "count": 1 }, { "created": "17-05", "expire": "19-03", "prev": "19-03", "count": 19 }, { "created": "17-05", "expire": "19-04", "prev": "19-04", "count": 2 }, { "created": "17-05", "expire": "19-05", "prev": "19-05", "count": 2 }, { "created": "17-05", "expire": "19-07", "prev": "19-07", "count": 1 }, { "created": "17-05", "expire": "19-09", "prev": "19-09", "count": 1 }, { "created": "17-05", "expire": "19-10", "prev": "19-10", "count": 1 }, { "created": "17-05", "expire": "19-11", "prev": "19-11", "count": 1 }, { "created": "17-05", "expire": "20-01", "prev": "19-01", "count": 1487 }, { "created": "17-05", "expire": "20-03", "prev": "19-03", "count": 9 }, { "created": "17-05", "expire": "20-07", "prev": "19-07", "count": 1 }, { "created": "17-07", "expire": "19-01", "prev": "19-01", "count": 59 }, { "created": "17-07", "expire": "20-01", "prev": "19-01", "count": 41 }, { "created": "17-08", "expire": "19-01", "prev": "19-01", "count": 85 }, { "created": "17-08", "expire": "19-03", "prev": "19-03", "count": 3 }, { "created": "17-08", "expire": "19-10", "prev": "19-10", "count": 1 }, { "created": "17-08", "expire": "20-01", "prev": "19-01", "count": 63 }, { "created": "17-09", "expire": "19-01", "prev": "19-01", "count": 81 }, { "created": "17-09", "expire": "20-01", "prev": "19-01", "count": 71 }, { "created": "17-10", "expire": "19-01", "prev": "19-01", "count": 82 }, { "created": "17-10", "expire": "19-02", "prev": "19-02", "count": 1 }, { "created": "17-10", "expire": "20-01", "prev": "19-01", "count": 72 }, { "created": "17-11", "expire": "19-01", "prev": "19-01", "count": 120 }, { "created": "17-11", "expire": "19-03", "prev": "19-03", "count": 4 }, { "created": "17-11", "expire": "20-01", "prev": "19-01", "count": 84 }, { "created": "17-11", "expire": "20-03", "prev": "19-03", "count": 1 }, { "created": "17-12", "expire": "19-01", "prev": "19-01", "count": 319 }, { "created": "17-12", "expire": "19-03", "prev": "19-03", "count": 27 }, { "created": "17-12", "expire": "19-04", "prev": "19-04", "count": 2 }, { "created": "17-12", "expire": "19-05", "prev": "19-05", "count": 1 }, { "created": "17-12", "expire": "19-09", "prev": "19-09", "count": 1 }, { "created": "17-12", "expire": "20-01", "prev": "19-01", "count": 305 }, { "created": "17-12", "expire": "20-03", "prev": "19-03", "count": 4 }, { "created": "17-12", "expire": "20-04", "prev": "19-04", "count": 1 }, { "created": "18-01", "expire": "19-01", "prev": "19-01", "count": 1429 }, { "created": "18-01", "expire": "19-03", "prev": "19-03", "count": 60 }, { "created": "18-01", "expire": "19-05", "prev": "19-05", "count": 3 }, { "created": "18-01", "expire": "20-01", "prev": "19-01", "count": 789 }, { "created": "18-01", "expire": "20-03", "prev": "19-03", "count": 16 }, { "created": "18-02", "expire": "19-02", "prev": "19-02", "count": 522 }, { "created": "18-02", "expire": "19-03", "prev": "19-03", "count": 65 }, { "created": "18-02", "expire": "19-04", "prev": "19-04", "count": 5 }, { "created": "18-02", "expire": "19-05", "prev": "19-05", "count": 20 }, { "created": "18-02", "expire": "20-02", "prev": "19-02", "count": 43 }, { "created": "18-02", "expire": "20-03", "prev": "19-03", "count": 3 }, { "created": "18-02", "expire": "20-04", "prev": "19-04", "count": 1 }, { "created": "18-03", "expire": "19-03", "prev": "19-03", "count": 377 }, { "created": "18-03", "expire": "19-04", "prev": "19-04", "count": 11 }, { "created": "18-03", "expire": "19-05", "prev": "19-05", "count": 5 }, { "created": "18-03", "expire": "20-03", "prev": "19-03", "count": 18 }, { "created": "18-03", "expire": "20-04", "prev": "19-04", "count": 1 }, { "created": "18-03", "expire": "20-05", "prev": "19-05", "count": 1 }, { "created": "18-04", "expire": "19-04", "prev": "19-04", "count": 480 }, { "created": "18-04", "expire": "19-05", "prev": "19-05", "count": 15 }, { "created": "18-04", "expire": "19-09", "prev": "19-09", "count": 1 }, { "created": "18-04", "expire": "20-04", "prev": "19-04", "count": 22 }, { "created": "18-04", "expire": "20-05", "prev": "19-05", "count": 1 }, { "created": "18-05", "expire": "19-05", "prev": "19-05", "count": 586 }, { "created": "18-05", "expire": "19-06", "prev": "19-06", "count": 24 }, { "created": "18-05", "expire": "19-07", "prev": "19-07", "count": 3 }, { "created": "18-05", "expire": "19-09", "prev": "19-09", "count": 2 }, { "created": "18-05", "expire": "20-05", "prev": "19-05", "count": 25 }, { "created": "18-05", "expire": "20-06", "prev": "19-06", "count": 1 }, { "created": "18-06", "expire": "19-06", "prev": "19-06", "count": 228 }, { "created": "18-06", "expire": "19-07", "prev": "19-07", "count": 13 }, { "created": "18-06", "expire": "19-08", "prev": "19-08", "count": 2 }, { "created": "18-06", "expire": "19-09", "prev": "19-09", "count": 1 }, { "created": "18-06", "expire": "19-11", "prev": "19-11", "count": 1 }, { "created": "18-06", "expire": "20-06", "prev": "19-06", "count": 4 }, { "created": "18-07", "expire": "19-07", "prev": "19-07", "count": 170 }, { "created": "18-07", "expire": "19-08", "prev": "19-08", "count": 11 }, { "created": "18-07", "expire": "20-07", "prev": "19-07", "count": 4 }, { "created": "18-08", "expire": "19-08", "prev": "19-08", "count": 198 }, { "created": "18-08", "expire": "19-09", "prev": "19-09", "count": 9 }, { "created": "18-08", "expire": "19-12", "prev": "19-12", "count": 1 }, { "created": "18-08", "expire": "20-08", "prev": "19-08", "count": 3 }, { "created": "18-09", "expire": "19-09", "prev": "19-09", "count": 229 }, { "created": "18-09", "expire": "19-10", "prev": "19-10", "count": 9 }, { "created": "18-09", "expire": "19-12", "prev": "19-12", "count": 1 }, { "created": "18-09", "expire": "20-09", "prev": "19-09", "count": 2 }, { "created": "18-10", "expire": "19-10", "prev": "19-10", "count": 384 }, { "created": "18-10", "expire": "19-11", "prev": "19-11", "count": 19 }, { "created": "18-10", "expire": "19-12", "prev": "19-12", "count": 2 }, { "created": "18-10", "expire": "20-10", "prev": "19-10", "count": 3 }, { "created": "18-11", "expire": "19-11", "prev": "19-11", "count": 399 }, { "created": "18-11", "expire": "19-12", "prev": "19-12", "count": 24 }, { "created": "18-12", "expire": "19-12", "prev": "19-12", "count": 500 }, { "created": "18-12", "expire": "20-01", "prev": "20-01", "count": 6 }, { "created": "19-01", "expire": "20-01", "prev": "20-01", "count": 5 } ]33:Te31,getSeries = ({ investments = [], plannedMemberships = [], plannedExpenses = [], type, renewalRates: inputRenewalRates, prices = [], stopMonth = new Date('2023-02-01T00:00:00.000+01:00') }) => { let priceIncome = getIncomeFromPrice(defaultPrice) const { sum, timeMonth } = d3 const months = timeMonth.range(startMonth, stopMonth) let lastPM let lastPE let liq = startLiquidity const renewalRates = { ...defaultRenewalRates, ...inputRenewalRates } const renewalRate = (age, created) => age > 12 && age < 22 ? renewalRates.cf : age >= 24 ? renewalRates.y2 : renewalRates.y1 let activeMemberships = [].concat(membershipsWithExpire.filter(m => shortYM.parse(m.created) < months[0])) const updateActiveMemberships = (newRecord) => { let oldRecord = activeMemberships.find(am => ( am.prev === newRecord.prev && am.expire === newRecord.expire && am.created === newRecord.created )) if (oldRecord) { activeMemberships = activeMemberships.filter(am => am !== oldRecord) newRecord.count += oldRecord.count } activeMemberships.push(newRecord) } return months.map(month => { const ym = shortYM.format(month) const matchYm = d => d.month === ym const actualPayments = payments.find(matchYm) const newPrice = prices.find(matchYm) if (newPrice) { priceIncome = getIncomeFromPrice(newPrice.chf) } lastPM = plannedMemberships.find(matchYm) || lastPM const actualExpense = expenses.find(matchYm) lastPE = plannedExpenses.find(matchYm) || lastPE const exp = actualExpense || lastPE membershipsWithExpire.filter(d => d.created === ym).forEach(updateActiveMemberships) if ((!lastPM && !actualPayments) || !exp) { return } let renewCount = 0 const pendingRenew = activeMemberships.filter(am => am.expire === ym) pendingRenew.forEach(renewals => { const alreadyRenewedCount = sum( activeMemberships.filter(am => ( am.expire !== renewals.expire && am.prev === renewals.expire && am.created === renewals.created )), d => d.count ) const age = timeMonth.count(shortYM.parse(renewals.created), shortYM.parse(renewals.expire)) const rate = renewalRate(age, renewals.created) activeMemberships = activeMemberships.filter(am => am !== renewals) const count = Math.floor(((alreadyRenewedCount + renewals.count) * rate) - alreadyRenewedCount) if (count < 1) { return } updateActiveMemberships({ prev: renewals.expire, expire: shortYM.format(timeMonth.offset(shortYM.parse(renewals.expire), 12)), created: renewals.created, count }) // console.log(ym, count, 'renewals', rate, age) renewCount += count }) // console.log(ym, renewCount, 'total renewals') if (actualPayments) { liq += actualPayments.chf - actualPayments.chf * feeRate } else { liq += renewCount * priceIncome if (lastPM) { liq += lastPM.count * priceIncome updateActiveMemberships({ expire: shortYM.format(timeMonth.offset(shortYM.parse(ym), 12)), created: ym, count: lastPM.count }) } liq += meanAdditionalIncome } liq -= exp.chf const investment = investments.find(matchYm) if (investment) liq += investment.chf return { type, month: ym, value: String(liq / 100), memberships: sum(activeMemberships, d => d.count) } }).filter(Boolean) }34:[] 2f:{"title":"Republik Membership Puzzle","cells":[{"id":0,"value":"md`# Republik Membership Puzzle`","pinned":false,"hidden":false,"mode":"ojs"},{"id":10,"value":"md`How to become self-sustaining when financed with yearly memberships. Open source version with additional price slider for further exploration.\n\nInitially developed for internal management purposes and later published in German for our members: [full story](https://www.republik.ch/2019/01/07/unser-plan-ihr-plan). You can find the official tool towards the end of the story.\n\nIf you read German: _Natürlich haben Sie auch die Möglichkeit, die Zukunft zwar nicht zu kennen, aber zu machen. Dies deshalb, weil in unserem noch jungen Unternehmen jede einzelne Verlegerin, jeder einzelne Verleger zählt: [Mitglied werden](https://www.republik.ch/angebote) oder [jetzt verlängern](https://www.republik.ch/angebote?package=PROLONG)!_\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":69,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":309,"value":"md`#### Liquidity`","pinned":false,"hidden":false,"mode":"ojs"},{"id":200,"value":"liqChart.render({\n config: {\n ...commonChartConfig,\n unit: 'Swiss francs',\n numberFormat: '.2s',\n yNice: 0,\n yAnnotations: [{\n value: 1000000,\n label: 'minimal liq needed:',\n dy: '0.9em'\n }]\n },\n values\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":284,"value":"md`#### Active Memberships`","pinned":false,"hidden":false,"mode":"ojs"},{"id":206,"value":"memChart.render({\n config: {\n ...commonChartConfig,\n unit: 'yearly memberships',\n numberFormat: 's',\n yAnnotations: [{\n value: numMembersNeeded, \n label: 'self-sustaining:'\n }]\n },\n values: values.map(d => ({...d, value: String(d.memberships)}))\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":289,"value":"md`#### Outcome\n\n\n${bankrupt\n ? `Bankrupt in ${formatLongYM(shortYM.parse(bankrupt.month))}`\n : sufficient\n ? `Self-sufficiency around ${formatLongYM(shortYM.parse(sufficient.month))}`\n : 'Survice the next years somehow.'\n}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":237,"value":"md`[Share-Link: ?plan=${[rrCF, rrY1, rrY2, newMemberships, newBudget, newInvestment, newPrice].join('-')}](https://beta.observablehq.com/@tpreusse/republik-membership-puzzle?plan=${[rrCF, rrY1, rrY2, newMemberships, newBudget, newInvestment, newPrice].join('-')})`","pinned":false,"hidden":false,"mode":"ojs"},{"id":171,"value":"numMembersNeeded = Math.ceil(currentExpenses * newBudget * 12 / getIncomeFromPrice(newPrice))","pinned":false,"hidden":false,"mode":"ojs"},{"id":182,"value":"sufficient = yourPlan.reduce(\n (months, m) => {\n if (m.memberships >= numMembersNeeded) {\n return months.concat(m)\n }\n return []\n },\n []\n)[0]","pinned":false,"hidden":false,"mode":"ojs"},{"id":179,"value":"bankrupt = yourPlan.find(m => +m.value < 0)","pinned":false,"hidden":false,"mode":"ojs"},{"id":146,"value":"yourPlan = getSeries({\n type: 'Your Plan',\n plannedMemberships: [\n {month: '19-01', count: newMemberships}\n ],\n plannedExpenses: [\n {month: '19-04', chf: currentExpenses * newBudget}\n ],\n investments: [\n ...securedInvestments,\n {month: '19-06', chf: newInvestment}\n ],\n renewalRates: {\n cf: rrCF,\n y1: rrY1,\n y2: rrY2\n },\n stopMonth: new Date('2029-02-01T00:00:00.000+01:00'),\n prices: [\n {month: '19-02', chf: newPrice}\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":45,"value":"md`### Base Data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":33,"value":"defaultPrice = 24000","pinned":false,"hidden":false,"mode":"ojs"},{"id":31,"value":"feeRate = 0.03 // psp","pinned":false,"hidden":false,"mode":"ojs"},{"id":40,"value":"tax = 0.021","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"defaultRenewalRates = ({\n cf: 0.5,\n y1: 0.65,\n y2: 0.75\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":103,"value":"planInvestment = 100000000","pinned":false,"hidden":false,"mode":"ojs"},{"id":105,"value":"planMemberships = 675","pinned":false,"hidden":false,"mode":"ojs"},{"id":107,"value":"planBudget = 0.9","pinned":false,"hidden":false,"mode":"ojs"},{"id":144,"value":"currentExpenses = 54600000","pinned":false,"hidden":false,"mode":"ojs"},{"id":43,"value":"startLiquidity = 276326400","pinned":false,"hidden":false,"mode":"ojs"},{"id":29,"value":"meanAdditionalIncome = 2955740.888888889","pinned":false,"hidden":false,"mode":"ojs"},{"id":38,"value":"startMonth = new Date('2018-07-01T00:00:00.000+02:00')","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":61,"value":"payments = [\n { \"month\": \"18-07\", \"chf\": 8369800 },\n { \"month\": \"18-08\", \"chf\": 8287300 },\n { \"month\": \"18-09\", \"chf\": 8786100 },\n { \"month\": \"18-10\", \"chf\": 12794000 },\n { \"month\": \"18-11\", \"chf\": 96741300 },\n { \"month\": \"18-12\", \"chf\": 106932108 }\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":63,"value":"expenses = [\n { \"month\": \"18-07\", \"chf\": 42001200 },\n { \"month\": \"18-08\", \"chf\": 51330800 },\n { \"month\": \"18-09\", \"chf\": 59233600 },\n { \"month\": \"18-10\", \"chf\": 49885200 },\n { \"month\": \"18-11\", \"chf\": 53745300 },\n { \"month\": \"18-12\", \"chf\": 59187300 },\n { \"month\": \"19-01\", \"chf\": 59187300 },\n { \"month\": \"19-02\", \"chf\": 59187300 },\n { \"month\": \"19-03\", \"chf\": 59187300 }\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":141,"value":"securedInvestments = [\n {month: '18-07', chf: 76326400},\n {month: '18-11', chf: 66666666},\n {month: '19-01', chf: 33333333}\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":52,"value":"md`### Code`","pinned":false,"hidden":false,"mode":"ojs"},{"id":138,"value":"officialPlan = getSeries({\n type: 'Official Plan',\n plannedMemberships: [\n {month: '19-01', count: planMemberships}\n ],\n plannedExpenses: [\n {month: '19-04', chf: currentExpenses * planBudget}\n ],\n investments: [\n ...securedInvestments,\n {month: '19-06', chf: planInvestment}\n ]\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":173,"value":"getIncomeFromPrice = price => price - price * feeRate - price * tax","pinned":false,"hidden":false,"mode":"ojs"},{"id":65,"value":"$33","pinned":true,"hidden":false,"mode":"ojs"},{"id":224,"value":"params = new URLSearchParams(new URL(document.baseURI).search)","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"paramPlan = params.has(\"plan\") ? params.get(\"plan\").split('-').map(n => +n) : []","pinned":false,"hidden":false,"mode":"ojs"},{"id":79,"value":"viewof rrCF = new View(paramPlan[0] || defaultRenewalRates.cf)","pinned":false,"hidden":false,"mode":"ojs"},{"id":81,"value":"viewof rrY1 = new View(paramPlan[1] || defaultRenewalRates.y1)","pinned":false,"hidden":false,"mode":"ojs"},{"id":83,"value":"viewof rrY2 = new View(paramPlan[2] || defaultRenewalRates.y2)","pinned":false,"hidden":false,"mode":"ojs"},{"id":100,"value":"viewof newMemberships = new View(paramPlan[3] || planMemberships)","pinned":false,"hidden":false,"mode":"ojs"},{"id":110,"value":"viewof newBudget = new View(paramPlan[4] || planBudget)","pinned":false,"hidden":false,"mode":"ojs"},{"id":112,"value":"viewof newInvestment = new View(paramPlan[5] || planInvestment)","pinned":false,"hidden":false,"mode":"ojs"},{"id":132,"value":"viewof newPrice = new View(paramPlan[6] || defaultPrice)","pinned":false,"hidden":false,"mode":"ojs"},{"id":190,"value":"liqChart = initChart()","pinned":false,"hidden":false,"mode":"ojs"},{"id":188,"value":"memChart = initChart()","pinned":false,"hidden":false,"mode":"ojs"},{"id":193,"value":"commonChartConfig = ({\n type: 'Line',\n height: 180,\n paddingRight: 130,\n sort: 'none',\n color: 'type',\n colorSort: 'none',\n colorRange: [\n 'rgba(31, 119, 180, 0.9)',\n 'rgba(60, 173, 0, 0.9)',\n `rgba(60, 173, 0, 1)`\n ],\n x: 'month',\n xTicks: ['19-01', '21-01', '23-01'],\n timeParse: shortYM.specifier,\n timeFormat: '%Y',\n labelFilter: 'datum.type && datum.type[0] != \"H\"',\n stroke: 'datum.type[0] != \"H\"',\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":256,"value":"past = getSeries({ investments: securedInvestments, type: 'HPast' })","pinned":false,"hidden":false,"mode":"ojs"},{"id":198,"value":"values = [officialPlan, yourPlan.slice(0, officialPlan.length), past].reduce((all, scenario) => all.concat(scenario))","pinned":false,"hidden":false,"mode":"ojs"},{"id":54,"value":"shortYMSpecifier = '%y-%m'","pinned":false,"hidden":false,"mode":"ojs"},{"id":56,"value":"shortYM = ({\n specifier: shortYMSpecifier,\n parse: d3.timeParse(shortYMSpecifier),\n format: d3.timeFormat(shortYMSpecifier)\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"formatPercent = d3.format('.0%')","pinned":false,"hidden":false,"mode":"ojs"},{"id":293,"value":"formatLongYM = d3.timeFormat('%B %Y')","pinned":false,"hidden":false,"mode":"ojs"},{"id":47,"value":"md`### Dependencies`","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"d3 = require('d3')","pinned":false,"hidden":false,"mode":"ojs"},{"id":22,"value":"import { initChart, renderChart } from '@tpreusse/project-r-charts'","pinned":false,"hidden":false,"mode":"ojs"},{"id":20,"value":"import { View, bind, disposal } from \"@mbostock/synchronized-views\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":24,"value":"function bindText(element, view, format = d => d) {\n const update = () => element.textContent = format(view.value);\n view.addEventListener(\"input\", update);\n disposal(element).then(() => view.removeEventListener(\"input\", update));\n return update(), element;\n}","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W34","stdlib":"1","titling":"auto"}