Republik Membership Puzzle
How to become self-sustaining when financed with yearly memberships. Open source version with additional price slider for further exploration. Initially developed for internal management purposes and later published in German for our members: full story. You can find the official tool towards the end of the story. If 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 oder jetzt verlängern!
html` <div style="width: 320px;float:left;"> <h4>Renewal Rates</h4> ${bind(html`<input type=range min=0.25 max=0.95 step=0.01 style="width:120px;">`, viewof rrCF)} ${bindText(html`<span />`, viewof rrCF, formatPercent)} crowdfunders<br /> ${bind(html`<input type=range min=0.25 max=0.95 step=0.01 style="width:120px;">`, viewof rrY1)} ${bindText(html`<span />`, viewof rrY1, formatPercent)} newcomers<br /> ${bind(html`<input type=range min=0.25 max=0.95 step=0.01 style="width:120px;">`, viewof rrY2)} ${bindText(html`<span />`, viewof rrY2, formatPercent)} second renewal<br /> </div> <div style="float:left;"> <h4>Financial Parameters</h4> ${bind(html`<input type=range min=100 max=1000 step=10 style="width:120px;">`, viewof newMemberships)} ${bindText(html`<span />`, viewof newMemberships)} new memberships per month<br /> ${bind(html`<input type=range min=0.5 max=1.5 step=0.01 style="width:120px;">`, viewof newBudget)} ${bindText(html`<span />`, viewof newBudget, formatPercent)} budget<br /> ${bind(html`<input type=range min=0 max=1000000000 step=1000000 style="width:120px;">`, viewof newInvestment)} CHF ${bindText(html`<span />`, viewof newInvestment, n => n / 100 / 1000000)} Mio. investment (in Jun 19)<br /> ${bind(html`<input type=range min=100 max=50000 step=100 style="width:120px;">`, viewof newPrice)} CHF ${bindText(html`<span />`, viewof newPrice, n => n / 100)} price (from Feb 19)<br /> </div> <br style="clear:left;"/> `
Liquidity
liqChart.render({ config: { ...commonChartConfig, unit: 'Swiss francs', numberFormat: '.2s', yNice: 0, yAnnotations: [{ value: 1000000, label: 'minimal liq needed:', dy: '0.9em' }] }, values })
Active Memberships
memChart.render({ config: { ...commonChartConfig, unit: 'yearly memberships', numberFormat: 's', yAnnotations: [{ value: numMembersNeeded, label: 'self-sustaining:' }] }, values: values.map(d => ({...d, value: String(d.memberships)})) })
Outcome
Share-Link: ?plan=
numMembersNeeded = Math.ceil(currentExpenses * newBudget * 12 / getIncomeFromPrice(newPrice))
sufficient = yourPlan.reduce( (months, m) => { if (m.memberships >= numMembersNeeded) { return months.concat(m) } return [] }, [] )[0]
bankrupt = yourPlan.find(m => +m.value < 0)
yourPlan = getSeries({ type: 'Your Plan', plannedMemberships: [ {month: '19-01', count: newMemberships} ], plannedExpenses: [ {month: '19-04', chf: currentExpenses * newBudget} ], investments: [ ...securedInvestments, {month: '19-06', chf: newInvestment} ], renewalRates: { cf: rrCF, y1: rrY1, y2: rrY2 }, stopMonth: new Date('2029-02-01T00:00:00.000+01:00'), prices: [ {month: '19-02', chf: newPrice} ] })
Base Data
defaultPrice = 24000
feeRate = 0.03 // psp
tax = 0.021
defaultRenewalRates = ({ cf: 0.5, y1: 0.65, y2: 0.75 })
planInvestment = 100000000
planMemberships = 675
planBudget = 0.9
currentExpenses = 54600000
startLiquidity = 276326400
meanAdditionalIncome = 2955740.888888889
startMonth = new Date('2018-07-01T00:00:00.000+02:00')
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 } ]
payments = [ { "month": "18-07", "chf": 8369800 }, { "month": "18-08", "chf": 8287300 }, { "month": "18-09", "chf": 8786100 }, { "month": "18-10", "chf": 12794000 }, { "month": "18-11", "chf": 96741300 }, { "month": "18-12", "chf": 106932108 } ]
expenses = [ { "month": "18-07", "chf": 42001200 }, { "month": "18-08", "chf": 51330800 }, { "month": "18-09", "chf": 59233600 }, { "month": "18-10", "chf": 49885200 }, { "month": "18-11", "chf": 53745300 }, { "month": "18-12", "chf": 59187300 }, { "month": "19-01", "chf": 59187300 }, { "month": "19-02", "chf": 59187300 }, { "month": "19-03", "chf": 59187300 } ]
securedInvestments = [ {month: '18-07', chf: 76326400}, {month: '18-11', chf: 66666666}, {month: '19-01', chf: 33333333} ]
Code
officialPlan = getSeries({ type: 'Official Plan', plannedMemberships: [ {month: '19-01', count: planMemberships} ], plannedExpenses: [ {month: '19-04', chf: currentExpenses * planBudget} ], investments: [ ...securedInvestments, {month: '19-06', chf: planInvestment} ] })
getIncomeFromPrice = price => price - price * feeRate - price * tax
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) }
params = new URLSearchParams(new URL(document.baseURI).search)
paramPlan = params.has("plan") ? params.get("plan").split('-').map(n => +n) : []
viewof rrCF = new View(paramPlan[0] || defaultRenewalRates.cf)
viewof rrY1 = new View(paramPlan[1] || defaultRenewalRates.y1)
viewof rrY2 = new View(paramPlan[2] || defaultRenewalRates.y2)
viewof newMemberships = new View(paramPlan[3] || planMemberships)
viewof newBudget = new View(paramPlan[4] || planBudget)
viewof newInvestment = new View(paramPlan[5] || planInvestment)
viewof newPrice = new View(paramPlan[6] || defaultPrice)
liqChart = initChart()
memChart = initChart()
commonChartConfig = ({ type: 'Line', height: 180, paddingRight: 130, sort: 'none', color: 'type', colorSort: 'none', colorRange: [ 'rgba(31, 119, 180, 0.9)', 'rgba(60, 173, 0, 0.9)', `rgba(60, 173, 0, 1)` ], x: 'month', xTicks: ['19-01', '21-01', '23-01'], timeParse: shortYM.specifier, timeFormat: '%Y', labelFilter: 'datum.type && datum.type[0] != "H"', stroke: 'datum.type[0] != "H"', })
past = getSeries({ investments: securedInvestments, type: 'HPast' })
values = [officialPlan, yourPlan.slice(0, officialPlan.length), past].reduce((all, scenario) => all.concat(scenario))
shortYMSpecifier = '%y-%m'
shortYM = ({ specifier: shortYMSpecifier, parse: d3.timeParse(shortYMSpecifier), format: d3.timeFormat(shortYMSpecifier) })
formatPercent = d3.format('.0%')
formatLongYM = d3.timeFormat('%B %Y')
Dependencies
d3 = require('d3')
import { initChart, renderChart } from '@tpreusse/project-r-charts'
import { View, bind, disposal } from "@mbostock/inline-inputs"
function bindText(element, view, format = d => d) { const update = () => element.textContent = format(view.value); view.addEventListener("input", update); disposal(element).then(() => view.removeEventListener("input", update)); return update(), element; }