1d:{"title":"Estimate Economic Returns on Adaptation","cells":[{"id":2205,"value":"{\n const imgSource = {\n en: FileAttachment(\"Estimate-economic-returns.webp\").url(),\n fr: FileAttachment(\"Estimate-economic-returns-fr.webp\").url()\n };\n const imgUrl = _lang(imgSource);\n return htl.html`

${notebookTitle}

`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2204,"value":"// pretty view of language toggle\nviewof prettyLanguageView = {\n return Inputs.bind(\n Inputs.radio(languages, {\n label: _lang(nbText.supportNbText.labels.language),\n format: (d) => d.label\n }),\n viewof language\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":272,"value":"tocOneliner({\n heading: `${_lang(nbText.supportNbText.labels.tocTitle)}`,\n skip: [notebookTitle, \"Chopping block\", \"Appendix\"], \n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2202,"value":"{\n const title = _lang(nbText.supportNbText.words.overview)\n const format = title.charAt(0).toUpperCase() + title.slice(1)\n return md`# ${format}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2201,"value":"md`${_lang(nbText.overview.blocks.block1)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2200,"value":"md`${_lang(nbText.overview.blocks.block2)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2199,"value":"md`${_lang(nbText.indicators.irr.overview)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2248,"value":"md`${_lang(nbText.indicators.mirr.overview)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2198,"value":"md`${_lang(nbText.indicators.npv.overview)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2197,"value":"md`${_lang(nbText.indicators.bcr.overview)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2196,"value":"md`${_lang(nbText.overview.blocks.block3)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2195,"value":"md`${_lang(nbText.overview.blocks.block4)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1788,"value":"projectSetup = {\n return md`# ${_lang(nbText.projectSetup.h1)}\n${styling}${stylingTooltip}${dropdownCSSCell}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2193,"value":"projectSetupTextBlurb = {\n const b1 = _lang(nbText.projectSetup.blocks.opener1)\n const b2 = _lang(nbText.projectSetup.blocks.opener2)\n return md`${b1}\n\n${b2}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1885,"value":"

${viewof select_country}

${viewof select_region}

${viewof select_crop}

","pinned":false,"hidden":false,"mode":"html"},{"id":2191,"value":"{\n const h2 = _lang(nbText.projectSetup.blocks.costs.h2)\n const text = _lang(nbText.projectSetup.blocks.costs.text)\n return md`## ${h2}\n${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1883,"value":"inputProjectOptions = {\n const inputProjectCost = Inputs.text({\n label: makeTooltipLabel({\n labelText: inputOptions_projectCost.label,\n tooltipText: inputOptions_projectCost.tooltip\n }),\n pattern: inputOptions_projectCost.pattern,\n placeholder: inputOptions_projectCost.placeholder,\n // value: inputOptions_projectCost.valueString\n });\n const inputProjectYears = Inputs.select(inputOptions_projectYears.data, {\n format: (x) => x.label,\n label: makeTooltipLabel({\n labelText: inputOptions_projectYears.label,\n tooltipText: inputOptions_projectYears.tooltip\n })\n });\n\n return Inputs.form(\n [\n Inputs.bind(inputProjectCost, viewof textProjectCost),\n Inputs.bind(inputProjectYears, viewof selectProjectYears)\n ],\n {\n template: (inputs) =>\n htl.html`

${Object.values(\n inputs\n )}

`\n }\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2190,"value":"inputProjectSelectionBlurb = {\n const template = _lang(nbText.projectSetup.blocks.costs.calloutTemplate)\n const templateItems = [\n {name: \"money\", value: formatUSD({locale: language.locale})(rangeProjectCost)},\n {name: \"time\", value: selectProjectYears.label},\n ]\n const formatted = Lang.reduceReplaceTemplateItems(template, templateItems)\n return md`${formatted}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1780,"value":"{\n const h2 = _lang(nbText.projectSetup.blocks.impacts.h2)\n const text = _lang(nbText.projectSetup.blocks.impacts.text)\n return md`## ${h2}\n${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1786,"value":"md`## ${_lang(nbText.projectSetup.blocks.locations.h2)}\n${_lang(nbText.projectSetup.blocks.locations.text)}\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1778,"value":"$21","pinned":false,"hidden":false,"mode":"ojs"},{"id":1777,"value":"md`# ${_lang(nbText.investmentReturns.h1, {key:language.key})}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2186,"value":"$22","pinned":false,"hidden":false,"mode":"ojs"},{"id":2185,"value":"{\n const templateDataPath =\n nbText.investmentReturns.blocks.insightReview.indicatorBlock;\n const template = _lang(templateDataPath);\n const templateItems = [\n {\n name: \"prod_impact_label\",\n value: options_econPerfWalkthrough.inputProdImpact.label\n },\n {\n name: \"adoption_rate_label\",\n value: options_econPerfWalkthrough.inputAdoption.label\n },\n {\n name: \"cis_impact_label\",\n value: options_econPerfWalkthrough.inputCisImpact.label\n },\n { name: \"year_string\", value: selectProjectYears.label }\n ];\n return md`${Lang.reduceReplaceTemplateItems(template, templateItems)}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1776,"value":"$23","pinned":false,"hidden":false,"mode":"ojs"},{"id":2245,"value":"$24","pinned":false,"hidden":false,"mode":"ojs"},{"id":2276,"value":"{\n function downloadData(data) {\n const csv = [\n Object.keys(data[0]).join(\",\"), // header\n ...data.map(row => Object.values(row).join(\",\")) // rows\n ].join(\"\\n\");\n const blob = new Blob([csv], { type: \"text/csv\" });\n const url = URL.createObjectURL(blob);\n const a = document.createElement(\"a\");\n a.href = url;\n a.download = \"data.csv\";\n a.click();\n URL.revokeObjectURL(url);\n}\n return Inputs.button(\"Download Table\", { reduce: () => downloadData(walkthroughEconomicIndicators.enriched)})\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1775,"value":"md`## ${_lang(nbText.investmentReturns.blocks.exploreIndicators.h2)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1952,"value":"indicatorSelector = {\n // bind input so tooltip can be dynamic\n return Inputs.bind(\n Inputs.select(dataPickMetric, {\n label: makeTooltipLabel({\n labelText: _lang(nbText.indicators.general.terms.indicator),\n tooltipText: pickMetricWalkthrough.tooltip,\n }),\n format: (x) => x.labelFull,\n }), viewof pickMetricWalkthrough\n )\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1951,"value":"$25","pinned":false,"hidden":false,"mode":"ojs"},{"id":1773,"value":"{\n const listWhat = _lang(nbText.investmentReturns.blocks.infoListHeaders.what)\n const listInterpret = _lang(nbText.investmentReturns.blocks.infoListHeaders.interpret)\n const listTell = _lang(nbText.investmentReturns.blocks.infoListHeaders.tell)\n\n // indicator info\n return md`### ${pickMetricWalkthrough.labelFull}\n\n- **${listWhat}**: ${pickMetricWalkthrough.info.what}\n- **${listInterpret}**: ${pickMetricWalkthrough.info.interpret}\n- **${listTell}**: ${pickMetricWalkthrough.info.tell}\n`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1949,"value":"md`${_lang(nbText.investmentReturns.blocks.closer1)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1835,"value":"md`# ${_lang(nbText.summary.h1)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2178,"value":"md`${_lang(nbText.summary.blocks.block1)}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2177,"value":"{\n const npv_rate = options_econPerfWalkthroughAdvanced.discount_rate.label;\n const farm_bcr = options_econPerfWalkthroughAdvanced.bcr;\n const methods_title = methodsTitle;\n \n const template = _lang(nbText.summary.blocks.block2)\n const templateItems = [\n {name: \"npvDisountRate\", value: npv_rate},\n {name: \"farmBcr\", value: farm_bcr},\n {name: \"methodsSourcesTitle\", value: methods_title},\n ]\n const formatted = Lang.reduceReplaceTemplateItems(template, templateItems)\n return md`${formatted}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2176,"value":"{\n const text = _lang(nbText.methods.h1)\n return md`# ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2175,"value":"{\n const text = _lang(nbText.methods.intro)\n return md` ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2174,"value":"{\n const text = _lang(nbText.methods.section1.h2)\n return md`## ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2173,"value":"{\n const text = _lang(nbText.methods.section1.subsections)\n return md` ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2172,"value":"{\n const text = _lang(nbText.methods.section2.h2)\n return md`## ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2171,"value":"{\n const text = _lang(nbText.methods.section2.intro)\n return md` ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2170,"value":"{\n const text = _lang(nbText.methods.section2.subsection1.h3)\n return md`### ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2169,"value":"{\n const text = _lang(nbText.methods.section2.subsection1.subsection)\n return md` ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2168,"value":"{\n const text = _lang(nbText.methods.section2.subsection2.h3)\n return md`### ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2167,"value":"{\n const text = _lang(nbText.methods.section2.subsection2.subsections)\n return md` ${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1769,"value":"

","pinned":false,"hidden":false,"mode":"md"},{"id":40,"value":"***\n# Appendix\nThis large section includes all the cells that power the notebook.","pinned":false,"hidden":false,"mode":"md"},{"id":2336,"value":"","pinned":true,"hidden":false,"mode":"ojs"},{"id":286,"value":"toc()","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"---\n## Work by Topic\n*Cells that power different sections*","pinned":false,"hidden":false,"mode":"md"},{"id":229,"value":"---\n### Notebook Variables\n_Main variables across notebook_","pinned":false,"hidden":false,"mode":"md"},{"id":244,"value":"// main title of the notebook\nnotebookTitle = \"Estimate Economic Returns on Adaptation\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1944,"value":"// methods section, used for any references\nmethodsTitle = \"Methods & Sources\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":2165,"value":"// format number, short notation\nformatNumCompactShort = ({ locale = \"en-US\" } = {}) =>\n new Intl.NumberFormat(locale, {\n notation: \"compact\",\n compactDisplay: \"short\"\n }).format","pinned":false,"hidden":false,"mode":"ojs"},{"id":2164,"value":"function formatUSD({ locale = \"en-US\" } = {}) {\n const formatter = formatNumCompactShort({ locale });\n\n return (number) => {\n const formattedNum = formatter(number)\n return \"$\" + formattedNum;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2163,"value":"**Language and notebook text**","pinned":false,"hidden":false,"mode":"md"},{"id":2162,"value":"import { Lang } from \"7d46679f7d0d917e\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":2161,"value":"Lang","pinned":false,"hidden":false,"mode":"ojs"},{"id":2160,"value":"languages = [\n { key: \"en\", label: \"English\", locale: 'en-US' },\n { key: \"fr\", label: \"Français\", locale: 'fr-FR' }\n]","pinned":false,"hidden":false,"mode":"ojs"},{"id":2159,"value":"// get the default language key from parameter, or use default\ndefaultLangKey = {\n const name = \"lang\";\n const list = languages.map((d) => d.key);\n const defaultKey = \"en\";\n\n const queryParam = await Lang.getParamFromList({ name, list });\n\n return queryParam ?? defaultKey;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2158,"value":"// main toggle of language key\nviewof language = Inputs.radio(languages, {\n label: \"Main language toggle\",\n format: (d) => d.key,\n value: languages.find((x) => x.key === defaultLangKey),\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2157,"value":"// helper shorthand for Lang text-getting\n_lang = Lang.lg(language.key)","pinned":false,"hidden":false,"mode":"ojs"},{"id":2156,"value":"$26","pinned":false,"hidden":false,"mode":"ojs"},{"id":2155,"value":"// data translation values\ntd = {\n let data = await FileAttachment(\"translation_v2-1.json\").json();\n\n return data\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":248,"value":"---\n### Notebook Styling\n*styling helpers*\nStylesheet is a named cell, which is necessary to include for embeds.","pinned":false,"hidden":false,"mode":"md"},{"id":251,"value":"viewof toggleCustomStyling = Inputs.toggle({label: \"Use custom styling\", value: true})","pinned":false,"hidden":false,"mode":"ojs"},{"id":253,"value":"$27","pinned":false,"hidden":false,"mode":"ojs"},{"id":1755,"value":"### Multi-Select Dropdowns\nFor country/region and crops. Use id's to find correct values.\n- detached selectors for layout using html\n- Imported from [external notebook](https://observablehq.com/d/0e35474715ee0c22)","pinned":false,"hidden":false,"mode":"md"},{"id":1982,"value":"import {dropdownInput, dropdownCSS} from \"@adaptationatlas/data-spotlight-multi-select\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1981,"value":"dropdownCSSCell = dropdownCSS // include in cell","pinned":false,"hidden":false,"mode":"ojs"},{"id":1871,"value":"debounceDelay = 500 // ms delay to respond","pinned":false,"hidden":false,"mode":"ojs"},{"id":1870,"value":"debounce = (delay, input) => {\n class DelayedEvent extends Event {};\n let id;\n input.addEventListener(\"input\", (e) => {\n if(e instanceof DelayedEvent) return;\n e.stopImmediatePropagation();\n clearTimeout(id);\n id = setTimeout(() => input.dispatchEvent(new DelayedEvent(\"input\", {bubbles: true})), delay);\n });\n return input;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1752,"value":"// admin multi-select: countries\nviewof select_country = {\n const removeAccents = (str) => str.normalize(\"NFD\").replace(/[\\u0300-\\u036f]/g, \"\")\n const data = dataAdminCountries\n .map((d) => ({\n value: d.id,\n label: _lang(d.translation)\n }))\n .sort((a, b) => removeAccents(a.label) > removeAccents(b.label));\n const selectedIds = dataAdminCountries\n .filter((d) => [\"Kenya\", \"Tanzania\"].includes(d.admin0_name))\n .map((d) => d.id);\n const text = {\n label: _lang(nbText.supportNbText.words.country.plural),\n tooltip: _lang(\n nbText.projectSetup.blocks.locations.inputs.countryDropdown.tooltip\n )\n };\n const input = dropdownInput({\n inputLabel: makeTooltipLabel({\n labelText: text.label,\n tooltipText: text.tooltip\n }),\n inputId: \"positionsTest\",\n placeholderText: \"Select Countries...\",\n options: data,\n selected: selectedIds\n });\n return debounce(debounceDelay, input);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1751,"value":"// admin multi-select: regions\nviewof select_region = {\n // filter regions based on country id's\n const filteredRegions = dataAdminRegions.filter((d) =>\n select_country.includes(d.idAdmin0)\n );\n const translateCountry = (d) => _lang(td.admin0_name.values?.[d]);\n const data = filteredRegions.map((d) => ({\n value: d.id,\n label: `${translateCountry(d.admin0_name)}: ${d.admin1_name}`\n }));\n const selectedIds = filteredRegions.map((d) => d.id);\n const text = {\n label: _lang(nbText.supportNbText.words.region.plural),\n tooltip: _lang(nbText.projectSetup.blocks.locations.inputs.regionDropdown.tooltip),\n }\n const input = dropdownInput({\n inputLabel: makeTooltipLabel({labelText: text.label, tooltipText: text.tooltip}),\n inputId: \"positionsTest\",\n placeholderText: \"Select regions...\",\n options: data,\n selected: selectedIds\n });\n\n return debounce(debounceDelay, input);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1750,"value":"// admin multi-select: countries\nviewof select_crop = {\n const data = dataCrops\n .map((d) => ({\n value: d.id,\n label: Lang.toSentenceCase(_lang(d.translation))\n }))\n .sort((a,b) => a.label > b.label);\n const selectedId = dataCrops\n .filter((d) => [\"bean\", \"goats\", \"maize\"].includes(d.item))\n .map((d) => d.id);\n const text = {\n label: _lang(nbText.supportNbText.words.commodity.plural),\n tooltip: _lang(\n nbText.projectSetup.blocks.locations.inputs.commodityDropdown.tooltip\n )\n };\n const input = dropdownInput({\n inputLabel: makeTooltipLabel({\n labelText: text.label,\n tooltipText: text.tooltip\n }),\n inputId: \"positionsTest\",\n placeholderText: \"Select crops...\",\n options: data,\n selected: selectedId\n });\n\n return debounce(debounceDelay, input);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":258,"value":"### Table of Contents","pinned":false,"hidden":false,"mode":"md"},{"id":260,"value":"$28","pinned":false,"hidden":false,"mode":"ojs"},{"id":1939,"value":"### Tooltip labels","pinned":false,"hidden":false,"mode":"md"},{"id":1938,"value":"// make a label for selector\n// html with tooltip on hover\nfunction makeTooltipLabel({\n labelText=\"Dropdown label\",\n tooltipText=\"Tooltip text\"\n}={}) {\n return htl.html`\n ${labelText}\n ${tooltipText}\n`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1937,"value":"","pinned":false,"hidden":false,"mode":"html"},{"id":1749,"value":"***\n### Economic performance walkthrough","pinned":false,"hidden":false,"mode":"md"},{"id":1748,"value":"Advanced values: these values are set to defaults, noted above in the narrative","pinned":false,"hidden":false,"mode":"md"},{"id":1747,"value":"viewof options_econPerfWalkthroughAdvanced = {\n const dataNpvDiscountRate = [\n { label: \"4%\", value: 0.04 },\n { label: \"8%\", value: 0.08 },\n { label: \"12%\", value: 0.12 },\n ];\n const inputNpvDiscountRate = Inputs.select(dataNpvDiscountRate, {\n label: \"NPV Discount Rate\",\n format: (x) => x.label,\n value: dataNpvDiscountRate.find(d => d.value == 0.08)\n });\n return Inputs.form(\n {\n // cis_impact: inputCisImpact,\n discount_rate: inputNpvDiscountRate,\n bcr: Inputs.select([1.62], { label: \"Farm BCR (only 1)\" })\n },\n {\n template: (inputs) =>\n htl.html`

${Object.values(\n inputs\n )}

`\n }\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1746,"value":"walkthroughSelections = {\n return {\n countries: select_country,\n regions: select_region,\n crops: select_crop,\n project_years: selectProjectYears.value,\n project_cost: rangeProjectCost,\n prod_impact: options_econPerfWalkthrough.inputProdImpact.value,\n adoption: options_econPerfWalkthrough.inputAdoption.value,\n cis_impact: options_econPerfWalkthrough.inputCisImpact.value,\n discount_rate: options_econPerfWalkthroughAdvanced.discount_rate.value,\n bcr: options_econPerfWalkthroughAdvanced.bcr,\n // time_horizon: options_econPerfWalkthrough.inputTime.value,\n };\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2242,"value":"function npvDiscreteCumulative(values, discountRate) {\n return values.map((_, i) =>\n values\n .slice(0, i + 1)\n .reduce((acc, val, j) => acc + val / Math.pow(1 + discountRate, j), 0)\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2241,"value":"function calcMIRR(cf, financeRate, reinvestRate) {\n const n = cf.length - 1;\n const inflows = cf.map(x => (x > 0 ? x : 0));\n const outflows = cf.map(x => (x < 0 ? x : 0));\n\n const FV_inflows = inflows.reduce(\n (acc, x, i) => acc + x * Math.pow(1 + reinvestRate, n - i),\n 0\n );\n const PV_outflows = outflows.reduce(\n (acc, x, i) => acc + x / Math.pow(1 + financeRate, i),\n 0\n );\n\n if (PV_outflows === 0 || FV_inflows <= 0) return null;\n\n return Math.pow(FV_inflows / Math.abs(PV_outflows), 1 / n) - 1;\n }","pinned":false,"hidden":false,"mode":"ojs"},{"id":2240,"value":" // === Custom IRR (secant method)\n function calculateIRR(cf, guess = 0.1, maxIterations = 100, tol = 1e-6) {\n const npv = (rate) =>\n cf.reduce((acc, val, i) => acc + val / Math.pow(1 + rate, i), 0);\n\n let r0 = guess;\n let r1 = guess + 0.05;\n\n for (let i = 0; i < maxIterations; i++) {\n const npv0 = npv(r0);\n const npv1 = npv(r1);\n const denom = npv1 - npv0;\n if (Math.abs(denom) < 1e-10) return null;\n const r2 = r1 - npv1 * (r1 - r0) / denom;\n if (Math.abs(r2 - r1) < tol) return r2;\n r0 = r1;\n r1 = r2;\n }\n return null;\n }\n ","pinned":false,"hidden":false,"mode":"ojs"},{"id":2239,"value":"$29","pinned":false,"hidden":false,"mode":"ojs"},{"id":1932,"value":"---\n### Timeseries chart","pinned":false,"hidden":false,"mode":"md"},{"id":1774,"value":"// base selector for metric type\nviewof pickMetricWalkthrough = {\n return Inputs.select(dataPickMetric, {\n label: _lang(nbText.indicators.general.terms.indicator),\n format: (x) => x.labelFull,\n value: dataPickMetric.find((d) => d.key == \"irr\")\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2238,"value":"function formatCurrencyAuto(value, precision = 1) {\n const absVal = Math.abs(value);\n\n if (absVal >= 1e9) {\n return `$${(value / 1e9).toFixed(precision)}B`;\n } else if (absVal >= 1e6) {\n return `$${(value / 1e6).toFixed(precision)}M`;\n } else if (absVal >= 1e3) {\n return `$${(value / 1e3).toFixed(precision)}K`;\n } else {\n return `$${value.toFixed(1)}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1929,"value":"$2a","pinned":false,"hidden":false,"mode":"ojs"},{"id":971,"value":"---\n## Filtering","pinned":false,"hidden":false,"mode":"md"},{"id":977,"value":"### Define admin and crop selections","pinned":false,"hidden":false,"mode":"md"},{"id":1740,"value":"// all data admin regions (remove country-wide)\n// add id column\ndataAdminCountries = {\n const tidy = T.tidy(\n await db.query(`\nselect distinct admin0_name\n from roi\n where admin1_name is null\n and admin0_name != 'Sudan'\n order by admin0_name\n`),\n T.mutateWithSummary({ id: T.rowNumber() }),\n T.mutate({ id: (d) => String(d.id) }),\n T.select([\"id\", \"admin0_name\"])\n )\n // apostophe fix\n .map((d) => ({ ...d, admin0_name: d.admin0_name.replaceAll(\"’\", \"'\") }));\n\n const translated = tidy.map(d => ({...d, translation: td.admin0_name.values?.[d.admin0_name]}))\n\n return translated\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1739,"value":"/* all data admin regions (remove country-wide)\n- add unique id's\n\nid: full id\nidAdmin0: id of admin 0 (country)\nidAdmin1: id of admin 1 (withing country)\n- use idAdmin1 to connect to countries\n*/\ndataAdminRegions = {\n const regions = await db.query(`\nselect distinct admin0_name\n , admin1_name\n from roi\n where admin1_name is not null\n order by 1, 2\n`);\n return T.tidy(\n regions,\n T.leftJoin(dataAdminCountries, {by: \"admin0_name\"}),\n T.rename({id: 'idAdmin0'}),\n T.groupBy(['admin0_name'], [\n T.mutateWithSummary({idAdmin1: T.rowNumber()}),\n T.mutate({idAdmin1: d => String(d.idAdmin1)})\n ]),\n T.mutate({id: d => `${d.idAdmin0}_${d.idAdmin1}`}),\n T.select([\n 'id',\n 'idAdmin0',\n 'idAdmin1',\n 'admin0_name',\n 'admin1_name'\n ])\n )\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":984,"value":"dataCrops = {\n const data = await db.query(`\nselect distinct crop as item\n from roi\n order by item\n`);\n const tidy = T.tidy(\n data,\n T.mutateWithSummary({ id: T.rowNumber() }),\n T.mutate({ id: (d) => String(d.id) }),\n T.select([\"id\", \"item\"]),\n T.mutate({\n label: d => d.item.charAt(0).toUpperCase() + d.item.slice(1)\n })\n );\n\n const translated = tidy.map(d => ({...d, translation: td.crop.values?.[d.item]}))\n\n return translated\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":234,"value":"---\n## Data\n*Data sources*\n- **ROI Data**: Project benefits for different admin regions and parameters\n - From s3: `risk_prototype/data/roi/roi_data.parquet`","pinned":false,"hidden":false,"mode":"md"},{"id":1737,"value":"Note: need extended sql to allow for dynamic inputs to select admin regions","pinned":false,"hidden":false,"mode":"md"},{"id":1736,"value":"import {extendDB, sql, extended} from \"@observablehq/mix-queries-and-arrays-into-sql\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":296,"value":"db = extendDB(await DuckDBClient.of({roi: FileAttachment(\"roi_data_v2(1).parquet\")}))","pinned":false,"hidden":false,"mode":"ojs"},{"id":1733,"value":"--- \n## Make the data","pinned":false,"hidden":false,"mode":"md"},{"id":1032,"value":"This section calculates the project financial values. We end up with project benefit data, with financial values calculated.","pinned":false,"hidden":false,"mode":"md"},{"id":1064,"value":"### Sum across admins and crops","pinned":false,"hidden":false,"mode":"md"},{"id":1729,"value":"We select all the data for admin regions and crops, and sum up to get the benefit across all parameter combos. This is done in a single sql query cell, otherwise a fetch of the data would be too large.\n- Using extended sql to allow for cases where no selections are made. This needs to be done in a sql cell.","pinned":false,"hidden":false,"mode":"md"},{"id":1728,"value":"// choices for admin regions and crops from selectors\nadminCropChoices = {\n const crops = dataCrops.filter(d => select_crop.includes(d.id))\n const regions = dataAdminRegions.filter(d => select_region.includes(d.id))\n \n return {\n crops,\n regions\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1727,"value":"with tbl as (\n -- get all the data based on selections\n select *\n from roi\n where ${\n adminCropChoices.crops.length && adminCropChoices.regions.length\n ? sql`admin0_name in (${ adminCropChoices.regions.map(d => d.admin0_name) })\n and admin1_name in (${ adminCropChoices.regions.map(d => d.admin1_name) })\n and crop in (${ adminCropChoices.crops.map(d => d.item) })`\n : sql`FALSE`\n }\n)\n\n-- sum the benefits for admin and crops\nselect adoption\n , prod_impact\n , cis_impact\n , bcr\n , year\n , SUM(project_benefit)::float as project_benefit\n from tbl\n group by adoption\n , prod_impact\n , cis_impact\n , bcr\n , year","pinned":false,"hidden":false,"mode":"sql","output":"recipe_dataSummed","database":"var:db"},{"id":1039,"value":"### Define project payment schedule\nBelow we define the project cost schedule\n- Cost is evenly distributed across years (\n - Ex. 1M over 5 years is 200K, 400K, 600K, 800K, 1M; the first year has no project benefit ($200K, 0 benefit)","pinned":false,"hidden":false,"mode":"md"},{"id":1436,"value":"// options for input\ninputOptions_projectCost = {\n return {\n range: [1e6, 5e8],\n label: _lang(nbText.projectSetup.blocks.costs.inputs.costInput.label),\n tooltip: _lang(nbText.projectSetup.blocks.costs.inputs.costInput.tooltip),\n step: 1e5,\n value: 50_000_000,\n format: d3.format('$.3s'),\n outputWidth: 90,\n placeholder: \"Ex. $5,000,000\",\n pattern: \"^[$]?[1-9]+[0-9,$]*$\",\n valueString: \"$50,000,000\"\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1864,"value":"// options for input\ninputOptions_projectYears = {\n const label = _lang(\n nbText.projectSetup.blocks.costs.inputs.projectYearDropdown.label,\n \n );\n const tooltip = _lang(\n nbText.projectSetup.blocks.costs.inputs.projectYearDropdown.tooltip,\n \n );\n const year = {\n singular: _lang(\n nbText.projectSetup.blocks.costs.inputs.projectYearDropdown.terms.year.singular,\n \n ),\n plural: _lang(\n nbText.projectSetup.blocks.costs.inputs.projectYearDropdown.terms.year.plural,\n \n )\n }\n return {\n range: [2, 16],\n data: d3\n .range(2, 16 + 1, 1)\n .map((d) => ({ label: `${d} ${year.plural}`, value: d })),\n label,\n tooltip,\n step: 1,\n value: 8,\n outputWidth: 90\n };\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2104,"value":"// text input, defining project cost\nviewof textProjectCost = Inputs.text({\n label: makeTooltipLabel({\n labelText: inputOptions_projectCost.label,\n tooltipText: inputOptions_projectCost.tooltip\n }),\n pattern: inputOptions_projectCost.pattern,\n placeholder: inputOptions_projectCost.placeholder,\n value: inputOptions_projectCost.valueString\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":2103,"value":"// format project cost text as a number\nrangeProjectCost = {\n const unpack = Number(textProjectCost.replace(/[,|$]/g, \"\"))\n const number = Number(unpack)\n return number\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1925,"value":"viewof selectProjectYears = {\n return Inputs.select(inputOptions_projectYears.data, {\n label: makeTooltipLabel({\n labelText: inputOptions_projectYears.label,\n tooltipText: inputOptions_projectYears.tooltip\n }),\n format: x => x.label,\n value: inputOptions_projectYears.data.find(x => x.value == inputOptions_projectYears.value)\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1057,"value":"// make payment schedule\nrecipe_paymentSchedule = Array.from({length: selectProjectYears.value}).map((_, i) => {\n return {\n year: i,\n cost: (1 / selectProjectYears.value) * rangeProjectCost,\n cost_cum: ((i + 1) / selectProjectYears.value) * rangeProjectCost\n }\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1067,"value":"#### Add project scope to data\n- Add a zero year at the start (the return rate is hardcoded to one year)\n- Also if the project cost is zero, return an empty array","pinned":false,"hidden":false,"mode":"md"},{"id":1072,"value":"recipe_dataCashflow = {\n if (rangeProjectCost == 0) return []\n const data = T.tidy(\n recipe_dataSummed,\n // I want to add a zero year with no benefit to each combo\n T.groupBy(\n [\"adoption\", \"prod_impact\", \"cis_impact\", \"bcr\"],\n [T.addRows({ year: 0, project_benefit: 0 }), T.arrange(\"year\")]\n ),\n // add payment schedule\n T.leftJoin(recipe_paymentSchedule, { by: [\"year\"] }),\n // set cost to zero for later years\n T.replaceNully({ cost: 0 }),\n // set cumulative cost to total cost for later years\n T.replaceNully({ cost_cum: rangeProjectCost }),\n // find cashflow\n T.mutate({\n cashflow: (d) => d.project_benefit - d.cost\n })\n );\n return data\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2328,"value":"{\n let selection = walkthroughSelections\n return recipe_dataCashflow\n .filter(\n (d) =>\n d.adoption == selection.adoption &&\n d.prod_impact == selection.prod_impact &&\n d.cis_impact == selection.cis_impact\n );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":2334,"value":"{\n let selection = walkthroughSelections\n return recipe_dataSummed\n .filter(\n (d) =>\n d.adoption == selection.adoption &&\n d.prod_impact == selection.prod_impact &&\n d.cis_impact == selection.cis_impact\n );\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":1292,"value":"#### Add discount rates\nDuplicate the data for each discount rate (added parameter)","pinned":false,"hidden":false,"mode":"md"},{"id":1307,"value":"recipe_discountRates = [0.04, 0.08, 0.12]","pinned":false,"hidden":false,"mode":"ojs"},{"id":1304,"value":"recipe_dataCashflowAndDiscountRate = {\n const joinData = recipe_discountRates.map((d) => {return {discount_rate: d}})\n return T.tidy(\n recipe_dataCashflow, \n T.leftJoin(joinData),\n T.select(['adoption', 'prod_impact', 'cis_impact', 'bcr', 'discount_rate', 'year', T.everything()]),\n T.arrange(['adoption', 'prod_impact', 'cis_impact', 'bcr', 'discount_rate', 'year', T.everything()])\n );\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1100,"value":"#### Calculate values","pinned":false,"hidden":false,"mode":"md"},{"id":1319,"value":"recipe_dataFinancialValues = T.tidy(\n recipe_dataCashflowAndDiscountRate,\n T.groupBy(['adoption', 'prod_impact', 'cis_impact', 'bcr', 'discount_rate'], [\n T.mutate({\n cashflowArray: (d, i, arr) => arr.map(x => x.cashflow).slice(0, i + 1),\n proj_irr: (d) => irrnpv.irr(d.cashflowArray),\n proj_npv: (d) => irrnpv.npv(d.cashflowArray, d.discount_rate),\n proj_bcr: (d) => d.proj_npv / d.cost_cum\n })\n ])\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":1326,"value":"At this point, following the recipe, we have the timeseries for all combinations of parameters, with financial values calculated. Specifically:\n- adoption\n- prod_impact\n- cis_impact\n- bcr\n- discount_rate\n\nAcross:\n- year\n\nData:\n- project_benifit\n- cost and cashflow\n- Project IRR\n- Project NPV\n- Project BCR","pinned":false,"hidden":false,"mode":"md"},{"id":1330,"value":"We can apply this to a variable for working with it:","pinned":false,"hidden":false,"mode":"md"},{"id":1331,"value":"dataFinanceSelectedAdminCrop = recipe_dataFinancialValues","pinned":false,"hidden":false,"mode":"ojs"},{"id":1861,"value":"## Format ranges","pinned":false,"hidden":false,"mode":"md"},{"id":1860,"value":"function replaceOutput({input, format=d=>d, outputWidth=100}={}) {\n // local scope\n const output = htl.html``;\n input.number.replaceWith(output);\n const update = () => (output.value = format(input.value));\n input.addEventListener(\"input\", update);\n update();\n\n return input\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1859,"value":"function forceEvent(input) {\n return Object.defineProperty(htl.html`

${input}`, \"value\", {\n get: () => input.value,\n set: (v) => {\n input.value = v;\n input.dispatchEvent(new Event(\"input\"));\n }\n });\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":238,"value":"***\n## Imports\n*External imports*\n- **irr-npv**: for calculations of irr and npv ([source](https://www.npmjs.com/package/irr-npv))\n- **yamultiselect**: for multi-select admin regions and crops ([source](https://observablehq.com/@saneef/yet-another-multi-select))","pinned":false,"hidden":false,"mode":"md"},{"id":101,"value":"irrnpv = import('https://cdn.skypack.dev/irr-npv@1.0.1?min')","pinned":false,"hidden":false,"mode":"ojs"},{"id":180,"value":"import {toc} from \"@nebrius/indented-toc\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":35,"value":"import {T} from \"@pbeshai/tidyjs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":1704,"value":"---\n## Other","pinned":false,"hidden":false,"mode":"md"},{"id":263,"value":"---\n### Dev notes\nHelper to include dev notes, but keep them out of sight","pinned":false,"hidden":false,"mode":"md"},{"id":265,"value":"function makeDevNotes({\n // adding dev notes as a html summary (collapsible)\n title = \"Plot dev notes\",\n emojis = \"👆🛠\",\n text = md`Update this text using the \\`text\\` field.`\n} = {}) {\n return htl.html`

${title} ${emojis}

${text}

`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1701,"value":"function workingSpan(text, style = `color: cornflowerblue;`) {\n // wrap text in styled span\n return htl.html`${text}`\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1802,"value":"### Version Log","pinned":false,"hidden":false,"mode":"md"},{"id":1804,"value":"$2b","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W2c","stdlib":"1"}