31:Td9f,viewof options_econPerfWalkthrough = { const terms = { none: _lang(nbText.projectSetup.blocks.impacts.terms.none, { key: language.key }), low: _lang(nbText.projectSetup.blocks.impacts.terms.low, { key: language.key }), moderate: _lang(nbText.projectSetup.blocks.impacts.terms.moderate, { key: language.key }), high: _lang(nbText.projectSetup.blocks.impacts.terms.high, { key: language.key }), incremental: _lang( nbText.projectSetup.blocks.impacts.terms.incremental, ), transformative: _lang( nbText.projectSetup.blocks.impacts.terms.transformative, ) }; const dataProdImpact = [ { label: `10% (${terms.incremental})`, value: 0.1 }, { label: `20%`, value: 0.2 }, { label: `30% (${terms.moderate})`, value: 0.3 }, { label: `40%`, value: 0.4 }, { label: `50% (${terms.transformative})`, value: 0.5 } ]; const inputProdImpact = Inputs.select(dataProdImpact, { label: makeTooltipLabel({ labelText: _lang( nbText.projectSetup.blocks.impacts.inputs.prodImpactDropdown.label, ), tooltipText: _lang( nbText.projectSetup.blocks.impacts.inputs.prodImpactDropdown.tooltip, ) }), format: (x) => x.label, value: dataProdImpact.find((d) => d.value == 0.3) }); const dataAdoption = [ { label: `0.5% (${terms.low})`, value: 0.005 }, { label: `1% (${terms.moderate})`, value: 0.01 }, { label: `2% (${terms.high})`, value: 0.02 } ]; const inputAdoption = Inputs.select(dataAdoption, { label: makeTooltipLabel({ labelText: _lang( nbText.projectSetup.blocks.impacts.inputs.adoptionRateDropdown.label, ), tooltipText: _lang( nbText.projectSetup.blocks.impacts.inputs.adoptionRateDropdown.tooltip, ) }), format: (x) => x.label, value: dataAdoption.find((d) => d.value == 0.01) }); const dataCisImpact = [ { label: `0% (${terms.none})`, value: 0 }, { label: `10% (${terms.low})`, value: 0.1 }, { label: `25% (${terms.moderate})`, value: 0.25 }, { label: `50% (${terms.high})`, value: 0.5 } ]; const inputCisImpact = Inputs.select(dataCisImpact, { label: makeTooltipLabel({ labelText: _lang( nbText.projectSetup.blocks.impacts.inputs.cisImpactDropdown.label, ), tooltipText: _lang( nbText.projectSetup.blocks.impacts.inputs.cisImpactDropdown.tooltip, ) }), format: (x) => x.label, value: dataCisImpact.find((d) => d.value == 0.25) }); // const dataTimeHorizon = d3.range(selectProjectYears.value, 16 + 1, 1).map(d => ({label: `${d} years`, value: d})) // const inputTime = Inputs.select(dataTimeHorizon, { // label: makeTooltipLabel({ // labelText: 'Time horizon', // tooltipText: "By default, the time horizon matches the project's active duration. Extending the time horizon beyond this period assumes continued adoption after project funding ends, with benefits projected up to this extended timeframe." // }), // format: x => x.label, // value: dataTimeHorizon.find(d => d.value == selectProjectYears.value) // }); return Inputs.form( { inputProdImpact, inputAdoption, inputCisImpact // inputTime, }, { template: (inputs) => htl.html`

${Object.values( inputs )}

` } ); }32:Tac2,{ const langBasePath = nbText.investmentReturns.blocks.insightReview.setupBlock; // const langBasePath = tester; const langMissingTemplate = _lang(langBasePath.missingMessage, { key: language.key }); const admin0List = _.uniq( adminCropChoices.regions.map((d) => d.admin0_name) ).map(d => _lang(td.admin0_name.values?.[d])).sort(); const cropList = adminCropChoices.crops.sort((a,b) => _lang(a.translation) > _lang(b.translation)); const truncateSize = 3; const admin0ListTruncated = admin0List.slice(0, truncateSize); const cropListTruncated = cropList.slice(0, truncateSize).map((d) => _lang(d.translation)); if (admin0List.length == 0 || cropList.length == 0) { return md`${langMissingTemplate}`; } // new code const insertCropCount = cropList.length; const insertCropWord = cropList.length == 1 ? _lang(nbText.supportNbText.words.crop.singular, { key: language.key }) : _lang(nbText.supportNbText.words.crop.plural, { key: language.key }); const insertCropList = `(**${cropListTruncated.join(", ")}${ cropList.length > truncateSize ? "..." : "" }**)`; const insertCrop = `**${insertCropCount} ${insertCropWord}** ${insertCropList}`; const insertRegionCount = adminCropChoices.regions.length; const insertRegionWord = adminCropChoices.regions.length == 1 ? _lang(nbText.supportNbText.words.region.singular, { key: language.key }) : _lang(nbText.supportNbText.words.region.plural, { key: language.key }); const insertRegion = `${insertRegionCount} ${insertRegionWord}`; const insertCountryCount = admin0List.length; const insertCountryWord = admin0List.length == 1 ? _lang(nbText.supportNbText.words.country.singular, { key: language.key }) : _lang(nbText.supportNbText.words.country.plural, { key: language.key }); const insertCountryList = `(**${admin0ListTruncated.join(", ")}${ admin0List.length > truncateSize ? "..." : "" }**)`; const insertCountry = `**${insertCountryCount} ${insertCountryWord}** ${insertCountryList}`; // insert to template const langTemplate = _lang(langBasePath.template, { key: language.key }); const langTemplateItems = [ { name: "crop_string", value: insertCrop }, { name: "region_string", value: insertRegion }, { name: "country_string", value: insertCountry }, { name: "cost_string", value: formatUSD({locale: language.locale})(rangeProjectCost) }, { name: "year_string", value: selectProjectYears.label } ]; const formattedTemplate = Lang.reduceReplaceTemplateItems( langTemplate, langTemplateItems ); return md`${formattedTemplate}`; }33:T8cd,bigNumbers = { const y = selectProjectYears?.value; const e = walkthroughEconomicIndicators?.enriched?.find(d => d.year === y); const data = dataPickMetric.map((d) => { let value; if (d.key === "irr" && e?.irr != null) { value = e.irr; // IRR as percentage } else if (d.key === "mirr" && e?.mirr != null) { value = e.mirr; // MIRR as percentage } else if (d.key === "npv" && e?.discounted_benefit_cum != null && e?.discounted_cost_cum != null) { value = +(e.discounted_benefit_cum - e.discounted_cost_cum).toFixed(1); // NPV, 1 dp } else if (d.key === "bcr") { value = e?.discounted_bcr; } return { ...d, value }; }); return htl.html`

${data.map( (d) => htl.html`

` )}

`; }34:Tbe0,{ function formatAbbreviatedNumber(num) { if (num == null || isNaN(num)) return "—"; const absNum = Math.abs(num); if (absNum >= 1e9) return (num / 1e9).toFixed(1) + "B"; if (absNum >= 1e6) return (num / 1e6).toFixed(1) + "M"; if (absNum >= 1e3) return (num / 1e3).toFixed(1) + "K"; return num.toString(); } const selectedYear = selectProjectYears?.value; const enriched = (walkthroughEconomicIndicators?.enriched ?? []) .filter(d => d.year <= selectedYear); const headerNames = [ "Year", "Benefit", "Cost", "Cashflow", "Discounted Benefit (cum)", "Discounted Cost (cum)", "Discounted NPV (cum)", "Discounted BCR", "IRR", "MIRR" ]; return html`

${headerNames.map(h => html``)} ${enriched.map((d, i) => { const isFinal = i === enriched.length - 1; const npv_i = d.discounted_benefit_cum != null && d.discounted_cost_cum != null ? d.discounted_benefit_cum - d.discounted_cost_cum : null; return html``; })}

${h}
${d.year}	${d.project_benefit != null ? `$${Math.round(d.project_benefit).toLocaleString()}` : "—"}	${d.cost != null ? `$${Math.round(d.cost).toLocaleString()}` : "—"}	${d.cashflow != null ? `$${Math.round(d.cashflow).toLocaleString()}` : "—"}	${d.discounted_benefit_cum != null ? `$${Math.round(d.discounted_benefit_cum).toLocaleString()}` : "—"}	${d.discounted_cost_cum != null ? `$${Math.round(d.discounted_cost_cum).toLocaleString()}` : "—"}	${npv_i != null ? `$${Math.round(npv_i).toLocaleString()}` : "—"}	${d.discounted_bcr != null ? d.discounted_bcr.toFixed(2) : "—"}	${d.irr != null ? `${(d.irr ).toFixed(2)}%` : "—"}	${d.mirr != null ? `${(d.mirr).toFixed(2)}%` : "—"}

`; }35:T7f7,plotIndicator = { const projectYearCutoff = walkthroughSelections.project_years; const selector = pickMetricWalkthrough; const plotData = walkthroughEconomicIndicators?.enriched ?? []; // Always include all years for consistent x-axis const fullYears = d3.range(0, 9); // 0 to 8 inclusive const completePlotData = fullYears.map(y => { const row = plotData.find(d => d.year === y); return row ?? { year: y }; }); const ruleYValue = selector.key === "bcr" ? 1 : 0; return Plot.plot({ width, marginRight: 80, inset: 5, x: { label: _lang(nbText.investmentReturns.blocks.exploreIndicators.projectYear), ticks: fullYears, tickFormat: "d", labelAnchor: "center", labelArrow: "none" }, y: { label: selector.label, tickFormat: selector.yFormat }, marks: [ Plot.axisY({ anchor: "right", label: selector.label, tickFormat: selector.yFormat, tickSize: 0, color: (d) => { return d >= 0 ? "black" : "#EC5A47"; } }), // horizontal reference line Plot.ruleY([ruleYValue], { stroke: "#333", strokeDasharray: [3] }), // line Plot.line(completePlotData, { x: "year", y: selector.columnName, stroke: "#444", strokeOpacity: 0.7, strokeWidth: 2, tip: true }), // dots Plot.dot(completePlotData, { x: "year", y: selector.columnName, r: 3, fill: "#444", stroke: "#fff", strokeWidth: 0.5, tip: { format: { x: true, y: selector.yFormat } } }), // highlight selected year Plot.dot(completePlotData.filter(d => d.year === projectYearCutoff), { x: "year", y: selector.columnName, r: 7, fill: "#fff", stroke: d => d[selector.columnName] >= 0 ? "#333" : "#EC5A47", strokeWidth: 2 }) ] }); }36:Tec23,nbText = { // text in the notebook // recommendation: only use objects for better tab-completion return { supportNbText: { labels: { quickInsights: { en: "Quick Insights", fr: "Aperçu Rapide" }, language: { en: "Language", fr: "Langue" }, tocTitle: { en: "In this notebook", fr: "Dans ce notebook" }, crop: { en: "Crop", fr: "Récolte" }, sort: { en: "Sort", fr: "Trier" }, sortTotals: { en: "Sort totals", fr: "Trier les totaux" }, sortBars: { en: "Sort bars", fr: "Trier les barres" }, confidenceIntervals: { en: "Confidence intervals", fr: "Intervalles de confiance" }, highlightAez: { en: "Highlight AEZ", fr: "Surligner AEZ" }, highlightPractice: { en: "Highlight a practice", fr: "Surligner une pratique" } }, words: { toBe: { singular: { en: "is", fr: "est" }, plural: { en: "are", fr: "sont" } }, and: { en: "and", fr: "et" }, percentage: { singular: { en: "percentage", fr: "pourcentage" }, plural: { en: "percentages", fr: "pourcentages" } }, crop: { singular: { en: "crop", fr: "récolte" }, plural: { en: "crops", fr: "récoltes" } }, country: { singular: { en: "country", fr: "pays" }, plural: { en: "countries", fr: "des pays" } }, region: { singular: { en: "region", fr: "région" }, plural: { en: "regions", fr: "régions" } }, subregion: { singular: { en: "subregion", fr: "sous-région" }, plural: { en: "subregions", fr: "sous-régions" } }, commodity: { singular: { en: "commodity", fr: "produit" }, plural: { en: "commodities", fr: "produits" } }, overview: { en: "overview", fr: "Vue d’Ensemble" }, practice: { singular: { en: "practice", fr: "pratique" }, plural: { en: "practices", fr: "pratiques" } }, observation: { singular: { en: "observation", fr: "observation" }, plural: { en: "observations", fr: "observations" } }, increase: { en: "increase", fr: "augmentation" }, decrease: { en: "decrease", fr: "diminution" } } }, indicators: { general: { terms: { indicator: { en: "Indicator", fr: "Indicateur" } } }, irr: { label: { en: "IRR", fr: "IRR" }, labelLong: { en: "Internal rate of return", fr: "Taux de rendement interne" }, labelFull: { en: "Internal rate of return (IRR)", fr: "Taux de rendement interne (TRI)" }, tooltip: { en: "Internal Rate of Return — the rate of growth a project is expected to generate, with higher values suggesting a more-desirable project", fr: "Taux de rendement interne - taux de croissance qu'un projet est censé générer, les valeurs les plus élevées indiquent un projet plus souhaitable" }, info: { what: { en: "The IRR is the discount rate that makes the net present value (NPV) of all cash flows (both positive and negative) from a particular project equal to zero. In simpler terms, it's the rate of growth a project is expected to generate.", fr: "Le TRI est le taux d'actualisation qui rend la valeur actuelle nette (VAN) de tous les flux de trésorerie (positifs et négatifs) d'un projet donné égale à zéro. En termes plus simples, il s'agit du taux de croissance qu'un projet est censé générer." }, interpret: { en: "The IRR is used to evaluate the attractiveness of a project or investment. If the IRR of a new project exceeds a company's required rate of return, that project is considered desirable. The higher the IRR, the more desirable the project.In most development or adaptation contexts, an IRR above **10–12%** is considered strong. Values below **6%** often suggest weak financial performance, unless there are compelling co-benefits such as resilience, equity, or sustainability. IRR values above the discount rate, coupled with a positive NPV and benefit-cost ratio above 1, are strong indicators of project viability.", fr: "Le TRI est utilisé pour évaluer l'attractivité d'un projet ou d'un investissement. Si le TRI d'un nouveau projet dépasse le taux de rendement exigé par l'entreprise, ce projet est considéré comme souhaitable. Plus le TRI est élevé, plus le projet est souhaitable. Dans les contextes de développement ou d'adaptation, un TRI supérieur à **10–12 %** est considéré comme solide. Des valeurs inférieures à **6 %** suggèrent souvent une performance financière faible, sauf si des co-bénéfices importants (résilience, équité, durabilité) sont présents. Un TRI supérieur au taux d'actualisation, combiné à une VAN positive et un ratio bénéfice-coût supérieur à 1, indique une viabilité robuste du projet." }, tell: { en: "IRR focuses on the rate of return a project generates. It's unique in that it provides a single percentage figure that can easily compare different investments' efficiency. However, it doesn't give you the actual monetary value of the investment's return.", fr: "Le TRI se concentre sur le taux de rendement qu'un projet génère. Il est unique en ce sens qu'il fournit un pourcentage unique qui permet de comparer facilement l'efficacité de différents investissements. Cependant, il ne donne pas la valeur monétaire réelle du rendement de l'investissement." } }, overview: { en: "**Internal Rate of Return (IRR)** — The Internal Rate of Return (IRR) is a financial metric used to evaluate the profitability of an investment or project. It represents the annual rate of return at which the net present value (NPV) of all (discounted) cash flows from the investment equals zero. A project is generally considered to be a good investment if its IRR exceeds the rate of return that could be earned elsewhere at a similar risk level (the opportunity cost of capital).", fr: "**Taux de rendement interne (TRI)** — Le taux de rendement interne (TRI) est une mesure financière utilisée pour évaluer la rentabilité d'un investissement ou d'un projet. Il représente le taux de rendement annuel auquel la valeur actuelle nette (VAN) de tous les flux de trésorerie (actualisés) de l'investissement est égale à zéro. Un projet est généralement considéré comme un bon investissement si son TRI dépasse le taux de rendement pouvant être obtenu ailleurs à un niveau de risque similaire (le coût d'opportunité du capital)." } }, mirr: { label: { en: "MIRR", fr: "MIRR" }, labelLong: { en: "Modified Internal Rate of Return", fr: "Taux de rendement interne modifié" }, labelFull: { en: "Modified Internal Rate of Return (MIRR)", fr: "Taux de rendement interne modifié (MIRR)" }, tooltip: { en: "Modified Internal Rate of Return — accounts for both financing costs and reinvestment rate, offering a more realistic profitability measure than IRR.", fr: "Taux de rendement interne modifié — tient compte à la fois des coûts de financement et du taux de réinvestissement, offrant une mesure de rentabilité plus réaliste que le TRI." }, info: { what: { en: "MIRR adjusts IRR by including the cost of borrowing (finance rate) and the rate of reinvestment for interim cash flows, offering a more accurate reflection of a project’s profitability.", fr: "Le MIRR ajuste le TRI en tenant compte du coût de l'emprunt (taux de financement) et du taux de réinvestissement des flux de trésorerie intermédiaires, offrant une mesure plus précise de la rentabilité du projet." }, interpret: { en: "MIRR is useful when reinvestment of cash flows is expected or required, as it assumes reinvestment at a realistic rate rather than the IRR’s often-unrealistic assumptions. A higher MIRR indicates a more profitable and financially viable project. In most public investment contexts, a MIRR above **10%** suggests strong performance, while values below **6%** may indicate marginal viability unless justified by additional non-financial benefits.", fr: "Le MIRR est utile lorsque le réinvestissement des flux de trésorerie est prévu ou nécessaire, car il suppose un taux de réinvestissement plus réaliste que celui utilisé dans le calcul du TRI. Un MIRR plus élevé indique un projet plus rentable et financièrement viable. Dans la plupart des contextes d'investissement public, un MIRR supérieur à **10%** reflète une performance solide, tandis que des valeurs inférieures à **6%** peuvent indiquer une viabilité limitée, sauf en cas de co-bénéfices importants." }, tell: { en: "MIRR provides a conservative and realistic return metric, especially when reinvestment assumptions are important. Unlike IRR, MIRR avoids multiple or misleading rates.", fr: "Le MIRR fournit une mesure de rendement plus prudente et réaliste, en particulier lorsque les hypothèses de réinvestissement sont importantes. Contrairement au TRI, le MIRR évite les taux multiples ou trompeurs." } }, overview: { en: "**Modified Internal Rate of Return (MIRR)** — MIRR improves upon IRR by assuming reinvestment of cash flows at a realistic rate and incorporating financing costs. It gives a clearer picture of project profitability in practical investment scenarios.", fr: "**Taux de rendement interne modifié (MIRR)** — Le MIRR améliore le TRI en supposant un réinvestissement des flux de trésorerie à un taux réaliste et en intégrant les coûts de financement. Il fournit une image plus claire de la rentabilité d’un projet dans des scénarios d’investissement concrets." } }, npv: { label: { en: "NPV", fr: "NPV" }, labelLong: { en: "Net Present Value", fr: "Valeur actuelle nette" }, labelFull: { en: "Net Present Value (NPV)", fr: "Valeur actuelle nette (VAN)" }, tooltip: { en: "Net Present Value — the overall value of an investment after subtracting costs and taking into account discounting, with positive values suggesting a profitable investment", fr: "Valeur actuelle nette - valeur globale d'un investissement après soustraction des coûts et prise en compte de l'actualisation, les valeurs positives indiquant un investissement rentable" }, info: { what: { en: "NPV is the sum of the present values of incoming and outgoing cash flows over a period of time. It provides a method for evaluating and comparing projects with cash flows spread out over many years, factoring in the time value of money.", fr: "La VAN est la somme des valeurs actuelles des flux de trésorerie entrants et sortants sur une période donnée. Elle permet d'évaluer et de comparer des projets dont les flux de trésorerie s'étalent sur plusieurs années, en tenant compte de la valeur temporelle de l'argent." }, interpret: { en: "NPV represents the net value (in today's dollars) of all future cash flows from the project. A value above 0 means the investment adds value; below 0 means it destroys value. The higher the NPV, the greater the net benefit generated by the project. However, NPV is expressed in absolute monetary terms, so it should be interpreted relative to project size — a $1 million NPV on a $2 million investment is stronger than on a $50 million investment. Always compare NPV alongside other metrics like IRR or MIRR to get a fuller picture of performance.", fr: "La VAN représente la valeur nette (en valeur actuelle) de tous les flux de trésorerie futurs du projet. Une valeur supérieure à 0 signifie que l'investissement crée de la valeur ; une valeur inférieure à 0 indique une perte nette. Plus la VAN est élevée, plus le bénéfice net du projet est important. Cependant, la VAN est exprimée en termes monétaires absolus, donc elle doit être interprétée en fonction de la taille du projet — une VAN de 1 million USD sur un projet de 2 millions est plus forte que sur un projet de 50 millions. Il est recommandé de toujours interpréter la VAN avec d'autres indicateurs comme le TRI ou le MIRR pour une évaluation complète." }, tell: { en: "NPV gives a dollar value that represents the difference between the present value of cash inflows and outflows. Unlike IRR, it can help you understand the scale of an investment's value, which is crucial when determining the actual profitability of large projects.", fr: "La VAN donne une valeur monétaire qui représente la différence entre la valeur actuelle des entrées et des sorties de fonds. Contrairement au TRI, elle peut vous aider à comprendre l'ampleur de la valeur d'un investissement, ce qui est crucial pour déterminer la rentabilité réelle de grands projets." } }, overview: { en: "**Net Present Value (NPV)** — NPV measures an investment's profitability for development projects by comparing incoming and outgoing money, considering the time value of money. It accounts for changes in money value over time due to inflation or interest rates. A positive NPV indicates that the project's benefits are greater than its costs, marking it as a favorable investment.", fr: "**Valeur actuelle nette (VAN)** — La VAN mesure la rentabilité d'un investissement pour les projets de développement en comparant l'argent entrant et l'argent sortant, en tenant compte de la valeur temporelle de l'argent. Elle tient compte des variations de la valeur de l'argent dans le temps dues à l'inflation ou aux taux d'intérêt. Une VAN positive indique que les bénéfices du projet sont supérieurs à ses coûts, ce qui en fait un investissement favorable." } }, bcr: { label: { en: "BCR", fr: "RCA" }, labelLong: { en: "Benefit cost ratio", fr: "Ratio avantages-coûts" }, labelFull: { en: "Benefit cost ratio (BCR)", fr: "Rapport coûts-avantages (RCA)" }, tooltip: { en: "Benefit-Cost Ratio — the benefits of a project compared to its costs, with values greater than 1.0 suggesting that the benefits outweigh the costs", fr: "Rapport coûts-avantages - les avantages d'un projet par rapport à ses coûts, les valeurs supérieures à 1,0 suggérant que les avantages l'emportent sur les coûts" }, info: { what: { en: "BCR is a financial ratio that compares the relative costs and benefits of a project. It's calculated by dividing the present value of benefits by the present value of costs.", fr: "Le RCA est un ratio financier qui compare les coûts et les bénéfices relatifs d'un projet. Il est calculé en divisant la valeur actuelle des bénéfices par la valeur actuelle des coûts." }, interpret: { en: "The discounted BCR compares the present value of benefits to the present value of costs. A BCR greater than 1.0 indicates that discounted benefits exceed discounted costs, suggesting the project generates net value. For example, a BCR of 1.8 means that for every dollar of cost (in present value), the project returns 1.8 dollars in benefits. A BCR below 1.0 implies the investment may not be economically worthwhile.", fr: "Le RCA actualisé compare la valeur actuelle des avantages à la valeur actuelle des coûts dans le temps. Une valeur supérieure à 1,0 signifie que le projet génère plus de valeur qu'il n'en coûte, après prise en compte de la valeur temporelle de l'argent. C'est un indicateur utile pour comparer les options d'investissement lorsque les budgets sont limités." }, tell: { en: "BCR simplifies investment outcomes to a ratio, which is particularly useful for quick comparisons and decision-making when evaluating multiple projects. It indicates whether the project is worth the investment but, like IRR, doesn't provide the project's value in terms of currency.", fr: "Le RCA simplifie les résultats de l'investissement sous la forme d'un ratio, ce qui est particulièrement utile pour effectuer des comparaisons rapides et prendre des décisions lors de l'évaluation de plusieurs projets. Il indique si le projet vaut l'investissement mais, comme le TRI, il ne fournit pas la valeur du projet en termes de devises." } }, overview: { en: "**Benefit-Cost Ratio (BCR)** — This metric simply compares the benefits (or returns) of a project to its costs, with both values adjusted to present-day terms to account for the time value of money. A BCR greater than 1 indicates that the benefits of a project exceed its costs, suggesting the investment is likely to be profitable and beneficial. For projects focused on supporting farmers, a BCR above 1 means that for every dollar spent, the project is expected to return more than a dollar in benefits, making it a good investment that can improve farmers' productivity and livelihoods. Conversely, a BCR less than 1 would imply that the costs outweigh the benefits, signaling that the project may not deliver sufficient value to justify the investment.", fr: "**Rapport coûts-avantages (RCA)** — Cet indicateur compare simplement les avantages (ou le rendement) d'un projet à ses coûts, les deux valeurs étant ajustées au jour le jour pour tenir compte de la valeur temporelle de l'argent. Un RCA supérieur à 1 indique que les bénéfices d'un projet dépassent ses coûts, ce qui suggère que l'investissement est susceptible d'être rentable et bénéfique. Pour les projets axés sur le soutien aux agriculteurs, un RCA supérieur à 1 signifie que pour chaque dollar dépensé, le projet devrait rapporter plus d'un dollar de bénéfices, ce qui en fait un bon investissement susceptible d'améliorer la productivité et les moyens de subsistance des agriculteurs. À l'inverse, un RCA inférieur à 1 signifie que les coûts l'emportent sur les bénéfices, ce qui indique que le projet n'apporte peut-être pas une valeur suffisante pour justifier l'investissement." } } }, overview: { blocks: { block1: { en: "Understanding the economic impacts of adaptation investments in agriculture is vital for investment planning. This notebook provides a detailed financial analysis for agricultural projects focused on adaptation, crucial for assessing the viability and strategic value of such investments.", fr: "Comprendre les impacts économiques des investissements d'adaptation dans l'agriculture est vital pour la planification des investissements. Ce notebook fournit une analyse financière détaillée des projets agricoles axés sur l'adaptation, essentielle pour évaluer la viabilité et la valeur stratégique de ces investissements." }, block2: { en: "Use this tool to evaluate the returns on a potential adaptation investment through projected economic performance indicators, including the:", fr: "Utilisez cet outil pour évaluer le rendement d'un investissement potentiel en matière d'adaptation au moyen d'indicateurs de performance économique projetés, notamment les suivants:" }, block3: { en: "Designed for high-level insights (national, regional, or continental), this tool helps investors gauge the likelihood of net-positive economic benefits from their projects. It focuses on projects that introduce innovations to farmers, enhancing crop or livestock yields, reducing climate hazard impacts, and achieving gradual farmer adoption over time. Together, these metrics allow investors to understand not only the profitability, but also the relative risk and efficiency of their investments, making them important indicators to gauge financial health of the (adaptation) project, for making informed decisions.", fr: "Conçu pour des analyses de haut niveau (national, régional ou continental), cet outil aide les investisseurs à évaluer la probabilité que leurs projets aient des retombées économiques nettes positives. Il se concentre sur les projets qui introduisent des innovations auprès des agriculteurs, améliorant les rendements des cultures ou du bétail, réduisant les impacts des risques climatiques et permettant une adoption progressive par les agriculteurs au fil du temps. Ensembles, ces mesures permettent aux investisseurs de comprendre non seulement la rentabilité, mais aussi le risque relatif et l'efficacité de leurs investissements, ce qui en fait des indicateurs importants pour évaluer la santé financière du projet (d'adaptation) et prendre des décisions en connaissance de cause." }, block4: { en: "It is important to note that negative economic indicators might not deter investment if the initiative supports broader goals or offers significant societal or environmental benefits. In such cases, the project might be considered valuable for its potential to improve farmers' livelihoods or environmental sustainability, even if if the costs are greater than value of production added or de-risked from climate hazards.", fr: "Il est important de noter que des indicateurs économiques négatifs peuvent ne pas décourager l'investissement si l'initiative soutient des objectifs plus larges ou offre des avantages sociétaux ou environnementaux significatifs. Dans ce cas, le projet peut être considéré comme intéressant pour sa capacité à améliorer les moyens de subsistance des agriculteurs ou la durabilité de l'environnement, même si les coûts sont supérieurs à la valeur de la production ajoutée ou à la réduction des risques liés aux aléas climatiques." } } }, projectSetup: { h1: { en: "Project Set-Up", fr: "Configuration de Project" }, blocks: { opener1: { en: "Agricultural projects can have different levels of production outcomes (in terms of production gains), while also helping in reducing climate impacts. The project set-up allows investors to gauge project cashflows and financial metrics by adjusting such parameters.", fr: "Les projets agricoles peuvent avoir différents niveaux de résultats de production (en termes de gains de production), tout en contribuant à réduire les impacts climatiques. Le montage du projet permet aux investisseurs d'évaluer les flux de trésorerie et les paramètres financiers du projet en ajustant ces paramètres." }, opener2: { en: "To begin, select target crops and locations. Then, specify project cost and duration. Finally, set-up the innovation impact on yields and climate hazard reduction and its annual rate of adoption.", fr: "Pour commencer, sélectionnez les cultures et les lieux cibles. Ensuite, précisez le coût et la durée du projet. Enfin, définissez l'impact de l'innovation sur les rendements et la réduction des risques climatiques, ainsi que son taux d'adoption annuel." }, locations: { h2: { en: "Locations and Commodities", fr: "Lieux et Marchandises" }, text: { en: "Choose the regions where your project occurs and the commodities it applies to.", fr: "Sélectionnez les régions où votre projet se déroule et les produits auxquels il s'applique." }, inputs: { countryDropdown: { tooltip: { en: "Pick countries to include", fr: "Sélectionnez les pays à inclure" } }, regionDropdown: { tooltip: { en: "Refine country selections by region", fr: "Affiner la sélection des pays par région" } }, commodityDropdown: { tooltip: { en: "Choose which commodities to include", fr: "Choisir les produits à inclure" } } } }, costs: { h2: { en: "Costs and Duration", fr: "Couts et Durée" }, text: { en: "Choose your project's cost and the length of time you expect to spend paying for it. Payments will be divided evenly across this span.", fr: "Choisissez le coût de votre projet et la durée pendant laquelle vous prévoyez de le financer. Les paiements seront répartis de manière égale sur cette période." }, inputs: { costInput: { label: { en: "Project cost", fr: "Coût du projet" }, tooltip: { en: "The total cost of the project in USD", fr: "Le coût total du projet en USD" } }, projectYearDropdown: { label: { en: "Project years", fr: "Années du projet" }, tooltip: { en: "The number of years across which project funds are disbursed", fr: "Le nombre d'années au cours desquelles les fonds du projet sont dispersés" }, terms: { year: { singular: { en: "year", fr: "année" }, plural: { en: "years", fr: "années" } } } } }, calloutTemplate: { en: "**:::money:::** over **:::time:::**", fr: "**:::money:::** sur **:::time:::**" } }, impacts: { h2: { en: "Impacts", fr: "Impacts" }, text: { en: "These parameters allow you to explore how the impact of an innovation changes project returns. You can adjust how much the innovation improves production yields, the rate at which your target community might adopt it, and how much it reduces the negative impacts of climate variability.", fr: "Ces paramètres vous permettent d'étudier comment l'impact d'une innovation modifie les résultats du projet. Vous pouvez ajuster la mesure dans laquelle l'innovation améliore les rendements de production, le taux auquel votre communauté cible pourrait l'adopter et la mesure dans laquelle elle réduit les effets négatifs de la variabilité climatique." }, terms: { none: { en: "none", fr: "aucun" }, high: { en: "high", fr: "élevé" }, incremental: { en: "incremental", fr: "incrémental" }, low: { en: "low", fr: "faible" }, moderate: { en: "moderate", fr: "modéré" }, transformative: { en: "transformative", fr: "transformative" } }, inputs: { prodImpactDropdown: { label: { en: "Production impact", fr: "Impact sur la production" }, tooltip: { en: "How much does adoption of an innovation improve the yield of selected crops or livestock?", fr: "Dans quelle mesure l'adoption d'une innovation améliore-t-elle le rendement des cultures ou du bétail sélectionnés?" } }, adoptionRateDropdown: { label: { en: "Yearly Adoption rate", fr: "Taux d'Adoption Annuel" }, tooltip: { en: "What is the annual rate of innovation adoption amongst those who have not adopted?", fr: "Quel est le taux annuel d'adoption de l'innovation parmi ceux qui ne l'ont pas adoptée?" } }, cisImpactDropdown: { label: { en: "Reduction of climate impacts", fr: "Réduction des Impacts Climatiques" }, tooltip: { en: "How much does the innovation reduce the negative impacts of climate variability?", fr: "Dans quelle mesure l'innovation réduit-elle les effets négatifs de la variabilité climatique?" } } } } } }, investmentReturns: { h1: { en: "Investment Returns", fr: "Retours d'Investissements" }, blocks: { insightReview: { setupBlock: { missingMessage: { en: "_Missing region and/or crop selections, please update selections_", fr: "_Région et/ou sélections de cultures manquantes, veuillez mettre à jour les sélections_" }, template: { en: "You are exploring an intervention about :::crop_string::: across **:::region_string:::** in :::country_string:::. Your project is set to cost **:::cost_string:::**, divided into equal payments over **:::year_string:::**.", fr: "Vous étudiez une intervention portant sur :::crop_string::: à travers **:::region_string:::** dans :::country_string:::. Le coût de votre projet est estimé à **:::cost_string:::**, répartis en paiements égaux sur **:::year_string:::**." } }, indicatorBlock: { en: "With a **:::prod_impact_label::: production impact**, a **:::adoption_rate_label::: yearly adoption rate**, and a **:::cis_impact_label::: reduction of climate impacts**, after **:::year_string:::** you are expected to see", fr: "Avec un **impact sur la production de :::prod_impact_label:::**, un **taux d'adoption annuel de :::adoption_rate_label:::** et une **réduction des impacts climatiques de :::cis_impact_label:::**, après **:::year_string:::** vous devriez voir" } }, exploreIndicators: { h2: { en: "Explore One of These Indicators in Detail", fr: "Examinez l'Un de Ces Indicateurs en Détail" }, projectYear: { en: "Project year", fr: "Année du projet" } }, infoListHeaders: { what: { en: "What it is", fr: "Qu'est-ce que c'est" }, interpret: { en: "Interpretation", fr: "Interprétation" }, tell: { en: "What it tells us", fr: "Ce qu'il nous apprend" } }, closer1: { en: "Each of these indicators plays a vital role in the evaluation process, offering different perspectives on an investment's financial viability. Together, they provide a comprehensive understanding, enabling investors and managers to make well-informed decisions.", fr: "Chacun de ces indicateurs joue un rôle essentiel dans le processus d'évaluation, en offrant différentes perspectives sur la viabilité financière d'un investissement. Ensemble, ils fournissent une compréhension globale, permettant aux investisseurs et aux gestionnaires de prendre des décisions en connaissance de cause." } } }, summary: { h1: { en: "Summary", fr: "Résumé" }, blocks: { block1: { en: "The internal rate of return, net present value, and benefit-cost ratio offer valuable insight into the potential returns for an agriculture adaptation investment, which can help project makers evaluate and compare investment opportunities. In this tool, these indicators provide an economic performance analysis for agriculture projects based on key project criteria. This analysis can help guide organizations and farmers in making informed investment decisions aligned with their financial goals.", fr: "Le taux de rendement interne, la valeur actuelle nette et le ratio coûts-avantages donnent une idée précieuse du rendement potentiel d'un investissement dans l'adaptation de l'agriculture, ce qui peut aider les responsables de projets à évaluer et à comparer les possibilités d'investissement. Dans cet outil, ces indicateurs fournissent une analyse de la performance économique des projets agricoles sur la base de critères clés. Cette analyse peut aider les organisations et les agriculteurs à prendre des décisions d'investissement éclairées et conformes à leurs objectifs financiers." }, block2: { en: "There are many factors to consider when calculating the returns for an investment, and the calculations above are founded on several assumptions — such as a fixed NPV discount rate (:::npvDisountRate:::) and farm BCR (:::farmBcr:::). More information about the methods can be found in the :::methodsSourcesTitle::: section below.", fr: "De nombreux facteurs doivent être pris en compte pour calculer le rendement d'un investissement, et les calculs ci-dessus reposent sur plusieurs hypothèses, telles qu'un taux d'actualisation fixe de la VAN (:::npvDisountRate:::) et un RCA de l'exploitation (:::farmBcr:::). De plus amples informations sur les méthodes sont disponibles dans la section Méthodes & Sources ci-dessous." } } }, methods: { h1: { en: "Methods & Sources", fr: "Méthodes & Sources" }, intro: { en: "The tool utilizes the best available production value datasets covering all of sub-Saharan Africa, enabling cross-country, crop, and livestock comparisons. While suitable for macro-scale analysis to determine economic viability and regional differences, the data's applicability diminishes at smaller scales. For projects with defined targets and contexts, we recommend engaging with local communities and governance through participatory approaches to refine investment targeting and design. \n \n This Spotlight implements a model developed for the Accelerating the Impact of CGIAR Climate Research in Africa (AICCRA) program.", fr: "L'outil utilise les meilleurs ensembles de données disponibles sur la valeur de la production couvrant l'ensemble de l'Afrique subsaharienne, ce qui permet des comparaisons entre les pays, les cultures et le bétail. S'il convient à l'analyse macroéconomique pour déterminer la viabilité économique et les différences régionales, l'applicabilité des données diminue à plus petite échelle. Pour les projets dont les objectifs et les contextes sont définis, nous recommandons de s'engager avec les communautés locales et la gouvernance par le biais d'approches participatives afin d'affiner le ciblage et la conception des investissements. \n \n Ce *Spotlight* met en œuvre un modèle développé pour le programme AICCRA (Accelerating the Impact of CGIAR Climate Research in Africa)." }, section1: { h2: { en: "Datasets", fr: "Ensembles de Données" }, subsections: { en: ` - **[Spatial crop production](https://radiantearth.github.io/stac-browser/#/external/digital-atlas.s3.amazonaws.com/stac/public_stac/exposure_catalog/mapspam2017/collection.json)** data for 2017 comes from MapSPAM 2017 V2r3 (Spatial Production Allocation Model) - **Spatial livestock distribution** data comes from the [Gridded Livestock of the World - 2015](https://dataverse.harvard.edu/dataverse/glw_4) dataset. - **National producer price** data comes from [FAOstat](https://fenixservices.fao.org/faostat/static/bulkdownloads/Prices_E_Africa.zip) - **National production and yield** data comes from [FAOstat](https://fenixservices.fao.org/faostat/static/bulkdownloads/Production_Crops_Livestock_E_Africa.zip) - **[Administrative boundaries](https://radiantearth.github.io/stac-browser/#/external/digital-atlas.s3.amazonaws.com/stac/public_stac/boundary_catalog/geoBoundaries_SSA/collection.json)** are provided by geoBoundaries 6.0.0. The gbHumanitarian boundaries are used and if not available then the gbOpen boundaries are substituted.`, fr: ` - **[Les données spatiales sur la production végétale](https://radiantearth.github.io/stac-browser/#/external/digital-atlas.s3.amazonaws.com/stac/public_stac/exposure_catalog/mapspam2017/collection.json)** pour 2017 proviennent de MapSPAM 2017 V2r3 (Modèle d’Allocation de Production Spatiale). - **Les données spatiales sur la répartition des animaux** proviennent de [Gridded Livestock of the World - 2015](https://dataverse.harvard.edu/dataverse/glw_4). - **Les données nationales sur les prix à la production** proviennent de [FAOstat](https://fenixservices.fao.org/faostat/static/bulkdownloads/Prices_E_Africa.zip) - **Les données sur les productions et les rendements nationaux** proviennent de [FAOstat](https://fenixservices.fao.org/faostat/static/bulkdownloads/Production_Crops_Livestock_E_Africa.zip) - **[Les frontières administratives](https://radiantearth.github.io/stac-browser/#/external/digital-atlas.s3.amazonaws.com/stac/public_stac/boundary_catalog/geoBoundaries_SSA/collection.json)** sont fournies par geoBoundaries 6.0.0. Les frontières gbHumanitarian sont utilisées et, si elles ne sont pas disponibles, les frontières gbOpen sont substituées.` } }, section2: { h2: { en: "Methods", fr: "Méthodes", }, intro: { en: `This tool implements the methodology developed by [Philip Thorton](https://scholar.google.com/citations?user=Wx_me7EAAAAJ&hl=en) used to inform the [Economic and Financial Analysis (EFA)](https://github.com/CIAT/AICCRA_EFA/blob/main/Documents/AICCRA%20EFA%2027-07-20.docx) for the [Accelerating Impacts of CGIAR Climate Research for Africa (AICCRA)](https://aiccra.cgiar.org/) project.`, fr: `Cet outil met en œuvre la méthodologie développée par [Philip Thorton](https://scholar.google.com/citations?user=Wx_me7EAAAAJ&hl=en) et utilisée pour informer [l'Analyse Économique et Financière (AEF)](https://github.com/CIAT/AICCRA_EFA/blob/main/Documents/AICCRA%20EFA%2027-07-20.docx) pour le [projet AICCRA (Accelerating Impacts of CGIAR Climate Research for Africa)](https://aiccra.cgiar.org/) project.`, }, subsection1: { h3: { en: "Data preparation", fr: "Préparation des données", }, subsection: { en: `#### Producer Prices 1. FAOstat production in USD is averaged by commodity and country for the period 2015-2019. 2. Data gaps for combinations of country and commodity are filled by using, in order of preference: a) the average of neighbouring countries; b) the regional average for the worldbank region the country belongs to; c) the continental average for sub-Saharan Africa. 3. The producer prices are then merged with country level (admin0) administrative boundaries and rasterized. 4. Code availability: [fao_producer_prices.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices.R) #### Livestock Value of Production 1. As spatial livestock production comes from 2000, we rescaled values to 2017 using the proportional national change in faostat production between 2000 and 2017. The scaling method preferentially adds production increases to the middle of the production distribution for a country and not the tails. For example, areas without production in 2000 do not suddenly become producers and heavy producing areas do not increase as much as intermediate producing areas. 2. Livestock production scaled to 2017 is then multiplied by the corresponding producer price data to generate livestock value of production in USD in 2017. 3. Meat and milk are added together for sheep, goats and cattle. 4. Values are extracted for administrative areas, summed and converted into tabular form. 5. Code availability: [fao_producer_prices_livestock.R]( https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices_livestock.R) #### Crop Value 1. MapSPAM crop production is multiplied by the corresponding producer price data to live crop value of production in USD in 2017. 2. Code availability: [fao_producer_prices.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices.R)`, fr: `#### Prix des Producteurs 1. La production de la FAOstat en USD est calculée en moyenne par produit et par pays pour la période 2015-2019. 2. Les données manquantes pour les combinaisons de pays et de produits sont comblées en utilisant, par ordre de préférence : a) la moyenne des pays voisins ; b) la moyenne régionale pour la région de la Banque mondiale à laquelle le pays appartient ; c) la moyenne continentale pour l'Afrique subsaharienne. 3. Les prix à la production sont ensuite fusionnés avec les limites administratives du pays (admin0) et tramés. 4. Disponibilité du code: [fao_producer_prices.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices.R) #### Valeur de la Production Animale 1. Étant donné que la production animale spatiale date de 2000, nous avons remis à l'échelle les valeurs pour 2017 en utilisant la variation nationale proportionnelle de la production faostat entre 2000 et 2017. La méthode de mise à l'échelle ajoute de préférence les augmentations de production au milieu de la distribution de la production pour un pays et non aux queues. Par exemple, les régions sans production en 2000 ne deviennent pas soudainement productrices et les régions à forte production n'augmentent pas autant que les régions à production intermédiaire. 2. La production animale ramenée à 2017 est ensuite multipliée par les données de prix à la production correspondantes pour générer la valeur de la production animale en USD en 2017. 3. La viande et le lait sont additionnés pour les ovins, les caprins et les bovins. 4. Les valeurs sont extraites pour les zones administratives, additionnées et converties sous forme de tableau. 5. Disponibilité du code: [f]ao_producer_prices_livestock.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices_livestock.R) #### Valeur des Cultures 1. La production végétale de MapSPAM est multipliée par les données de prix à la production correspondantes pour obtenir la valeur de la production végétale en USD en 2017. 2. Disponibilité du code: [fao_producer_prices.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_producer_prices.R)`, } }, subsection2: { h3: { en: "Calculations", fr: "Calculs", }, subsections: { en: `#### Yield and Production Variability - FAOstat yield and production data from 2000-2022 are used to calculate the [coefficient of variation(CV)](https://en.wikipedia.org/wiki/Coefficient_of_variation) for crops (yield) and livestock (production). We use production data rather than yields for livestock because livestock yield data appears to be highly calculated in FAOstat and shows very little variability over time, whereas production data is variable. - Commodity yields and production amounts in many countries show a strong increase over time. Where temporal trends are significant, we detrend to the data (linear) before calculating the CV. - Data gaps in CV for combinations of country and commodity are filled by using, in order of preference: a) the average of neighbouring countries; b) the regional average for the World Bank region the country belongs to; c) the continental average for sub-Saharan Africa. - Code availability: [fao_production_cv.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_production_cv.R) #### Economic Benefits We estimate project-level economic benefits by heuristically modeling how innovations affect value of production (VoP) and climate-related losses across time, space, and user-defined adoption rates. The main steps are: 1. **VoP under Innovation Adoption**: We apply user-defined adoption rates over time, shrinking the pool of non-adopters each year. For instance, with an initial VoP of 1000 and 10% adoption: year 1 sees 100 under innovation, year 2 adds 90 (from the remaining 900), totaling 190, and so on. 2. **Production Impact Gains**: We multiply the total VoP under adoption by the selected production impact (e.g. 20% yield increase). The marginal gain is the difference between VoP with and without innovation. 3. **Avoided Climate Losses**: To estimate avoided losses due to innovations that reduce climate impacts, we assume: - Yield/production follows a normal distribution - Adoption reduces yield variability (CV) proportionally For each crop and area, we generate two normal distributions (mean = 1): one for non-adopters (baseline CV) and one for adopters (reduced CV). The avoided loss proportion is the difference in expected loss in the lower tail (below 0) of each distribution. 4. **Total Marginal Benefit**: The total VoP under adoption is adjusted upward using the avoided loss proportion. The marginal benefit from climate impact reduction is added to the production gain, giving total marginal benefit. 5. **Total Project Benefit**: The total benefit is calculated as: \`\` Total Benefit = Marginal Benefit – (Marginal Benefit / BCR) \`\` where BCR = 1.62 (assumed from Harris & Orr, 2014). We then discount both benefits and costs over time using the selected discount rate to obtain time-adjusted values for indicator computation. 6. **Code Availability**: See [4_roi.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/4_roi.R) #### Economic Indicators The four indicators presented — IRR, MIRR, NPV, and BCR — capture different aspects of financial performance and can diverge for several reasons: 1. **Timing of cash flows**: IRR and MIRR reflect return rates over time and can appear high when large benefits arrive late in a project. However, NPV and BCR account for discounting, so delayed benefits reduce their values even if the project is profitable in the long run. 2. **Scale of investment**: Small investments with modest benefits can generate high IRR or MIRR due to efficiency, but the total net gain (NPV) or cost-effectiveness (BCR) may still be low. 3. **Discount rate effects**: Since NPV and BCR rely on discounting, a high discount rate (default 8%) can make delayed gains appear smaller. IRR and MIRR are unaffected by this rate in their core calculation, but MIRR incorporates reinvestment assumptions (also 8% here). 4. **Cumulative structure**: Each year’s values represent cumulative discounted returns to date, which helps track how financial indicators evolve across the life of the project. These indicators should be used together for a balanced view of financial viability. **Methodological Notes on Indicator Calculations** This notebook computes four financial indicators — Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR), Net Present Value (NPV), and Discounted Benefit-Cost Ratio (BCR) — using the following methodology: 1. **Cash Flow Construction**: - A project cash flow is created by subtracting user-defined project costs from estimated economic benefits. - Benefits are derived from the value of increased agricultural production (VoP) and the reduction of climate-related yield losses, both calculated annually based on user inputs. - Adoption rate is applied progressively over time, reducing the pool of non-adopters and reflecting diminishing marginal expansion. 2. **Discounting**: - All future cash flows are discounted to present value using a user-specified discount rate (default: 8%). - Cumulative discounted benefits and cumulative discounted costs are stored for each project year. 3. **Net Present Value (NPV)**: - NPV is calculated as the sum of discounted net returns (benefit minus cost) up to the selected year. - A positive NPV implies the project’s benefits exceed its costs in present-value terms. - **Note**: We use discrete annual discounting \`PV = FV / (1 + r)^t\`, not continuous compounding. 4. **Discounted Benefit-Cost Ratio (BCR)**: - BCR is calculated as the ratio of cumulative discounted benefits to cumulative discounted costs. - A BCR > 1 indicates a financially efficient project. - Unlike some simplified BCR approaches, this version uses full discounted benefit and cost streams. 5. **Internal Rate of Return (IRR)**: - IRR is estimated via a secant method to find the discount rate that sets the net present value of the project’s cumulative cash flow to zero. - IRR reflects the annualized rate of return implied by the cash flows. 6. **Modified Internal Rate of Return (MIRR)**: - MIRR addresses limitations of IRR by assuming: - Negative cash flows (investments) are financed at the discount (finance) rate - Positive cash flows (returns) are reinvested at the same (reinvestment) rate - MIRR is calculated from the future value of positive flows and the present value of negative flows using the formula: \`\`MIRR = (FV of inflows / |PV of outflows|)^(1/n) - 1\`\`, where \`n\` is the project duration. - We assume the finance and reinvestment rates are equal to the discount rate used in NPV and BCR (default: 8%). 7. **Cumulative Time Horizon**: - Each metric is calculated cumulatively: i.e., IRR and MIRR at year 3 reflect performance based on the first 4 years of project cash flows (year 0 to year 3). - This provides a dynamic view of how financial indicators evolve as the project progresses. 8. **Edge Case Handling**: - If IRR or MIRR cannot be calculated (e.g., due to all-positive or all-negative flows), the result is returned as null. - Indicators are rounded (NPV to 1 decimal, BCR to 2 decimals, IRR and MIRR to 1 decimal %) for clarity. All indicator calculations are implemented natively within the Observable notebook (no external APIs), enabling real-time interactivity and transparency. An earlier and more technical version of this methodology was developed into a tool using Shiny R and can be found on the github [AICCRA_EFA](https://github.com/CIAT/AICCRA_EFA).`, fr: `#### Variabilité du Rendement et de la Production - Les données de rendement et de production de la FAOstat pour la période 2000-2022 sont utilisées pour calculer le [coefficient de variation (CV)](https://en.wikipedia.org/wiki/Coefficient_of_variation) des cultures (rendement) et du bétail (production). Nous utilisons les données de production plutôt que les rendements pour le bétail parce que les données de rendement du bétail semblent être très bien calculées dans la FAOstat et montrent très peu de variabilité dans le temps, alors que les données de production sont variables. - Les rendements des produits et les quantités produites dans de nombreux pays montrent une forte augmentation au fil du temps. Lorsque les tendances temporelles sont significatives, nous procédons à une désintégration des données (linéaire) avant de calculer le CV. - Les données manquantes dans les CV pour les combinaisons de pays et de produits sont comblées en utilisant, par ordre de préférence: a) la moyenne des pays voisins; b) la moyenne régionale pour la région de la Banque mondiale à laquelle le pays appartient; c) la moyenne continentale pour l'Afrique subsaharienne. - Disponibilité du code: [fao_production_cv.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/fao_production_cv.R) #### Bénéfices économiques Nous estimons les bénéfices économiques d’un projet en modélisant de manière heuristique comment les innovations influencent la valeur de la production (VoP) et les pertes liées au climat dans le temps, l’espace, et selon les taux d’adoption définis par l’utilisateur. Les principales étapes sont : 1. **VoP sous adoption de l’innovation** : Nous appliquons des taux d’adoption progressifs définis par l’utilisateur, réduisant chaque année la part de non-adoptants. Par exemple, pour une VoP initiale de 1000 et un taux d’adoption de 10 %, la première année voit 100 sous innovation, la deuxième ajoute 90 (des 900 restants), totalisant 190, et ainsi de suite. 2. **Gains liés à l’impact sur la production** : Nous multiplions la VoP totale sous adoption par l’impact de production sélectionné (par exemple, +20 % de rendement). Le gain marginal est la différence entre la VoP avec et sans innovation. 3. **Pertes climatiques évitées** : Pour estimer les pertes évitées grâce à des innovations réduisant les impacts climatiques, nous supposons : - Une distribution normale des rendements/productions - Une réduction proportionnelle de la variabilité des rendements (CV) liée à l’adoption Pour chaque culture et zone, nous générons deux distributions normales (moyenne = 1) : une pour les non-adoptants (CV de référence), une pour les adoptants (CV réduite). La proportion de perte évitée correspond à la différence entre les queues gauches (en dessous de 0) de ces distributions. 4. **Bénéfice marginal total** : La VoP totale sous adoption est ajustée à la hausse via la proportion de perte évitée. Le bénéfice marginal lié à la réduction des impacts climatiques est ajouté au gain de production pour obtenir le bénéfice marginal total. 5. **Bénéfice total du projet** : Le bénéfice total est calculé comme : \`\` Bénéfice total = Bénéfice marginal – (Bénéfice marginal / BCR) \`\` où BCR = 1.62 (valeur tirée de Harris & Orr, 2014). Les bénéfices et coûts sont ensuite actualisés à l’aide du taux d’actualisation sélectionné afin d’obtenir des valeurs ajustées dans le temps pour le calcul des indicateurs. 6. **Disponibilité du code** : Voir [4_roi.R](https://github.com/AdaptationAtlas/hazards_prototype/blob/main/R/4_roi.R) --- #### Indicateurs économiques Quatre indicateurs — TRI (IRR), TRI modifié (MIRR), VAN (NPV) et RCA actualisé (BCR) — capturent différents aspects de la performance financière et peuvent diverger selon plusieurs facteurs : 1. **Calendrier des flux de trésorerie** : Le TRI et le MIRR reflètent les taux de rendement dans le temps et peuvent être élevés si les bénéfices arrivent tardivement. La VAN et le RCA appliquent l’actualisation, ce qui diminue la valeur des bénéfices différés, même si le projet reste rentable à long terme. 2. **Échelle de l’investissement** : Des projets de petite taille avec des bénéfices modestes peuvent afficher un TRI ou MIRR élevé, mais des gains nets totaux (VAN) ou une efficacité (BCR) faibles. 3. **Effets du taux d’actualisation** : La VAN et le BCR sont sensibles au taux d’actualisation (par défaut 8 %). Un taux élevé réduit la valeur des bénéfices futurs. Le TRI et le MIRR n’y sont pas sensibles dans leur formule de base, mais le MIRR incorpore les hypothèses de réinvestissement (également à 8 % ici). 4. **Structure cumulative** : Chaque année reflète les retours cumulés actualisés jusqu’à ce point, ce qui permet de suivre l’évolution de la viabilité financière dans le temps. Ces indicateurs doivent être utilisés ensemble pour une lecture équilibrée de la performance financière. --- #### Notes méthodologiques sur le calcul des indicateurs 1. **Construction des flux de trésorerie** - Les flux sont construits en soustrayant les coûts du projet (uniformément répartis) des bénéfices économiques estimés. - Les bénéfices incluent l’augmentation de la VoP et les pertes climatiques évitées, calculés annuellement. - Le taux d’adoption est appliqué progressivement, réduisant la base des non-adoptants chaque année. 2. **Actualisation** - Tous les flux futurs sont actualisés selon \`PV = FV / (1 + r)^t\` (actualisation discrète annuelle). - Les coûts et bénéfices actualisés sont enregistrés cumulativement par année. 3. **Valeur actuelle nette (VAN)** - La VAN est la somme des flux nets actualisés jusqu’à l’année sélectionnée. - Une VAN positive indique que les bénéfices surpassent les coûts en valeur actuelle. 4. **Ratio coût-bénéfice actualisé (RCA)** - Le BCR est le ratio des bénéfices actualisés cumulés sur les coûts actualisés cumulés. - Un BCR > 1,0 suggère un projet efficient financièrement. - Cette approche utilise les séries temporelles complètes actualisées. 5. **Taux de rendement interne (TRI)** - Estimé par méthode des sécantes, il est le taux qui annule la VAN du flux cumulatif. - Le TRI exprime le rendement annuel implicite du projet. 6. **Taux de rendement interne modifié (TRIM)** - Corrige les limites du TRI en supposant que : - Les investissements sont financés au taux d’actualisation - Les retours sont réinvestis à ce même taux - Calcul : \`\` MIRR = (VF des flux positifs / |VA des flux négatifs|)^(1/n) - 1 \`\` où \`n\` est la durée du projet (en années). - Les taux de financement et de réinvestissement sont fixés au taux d’actualisation (par défaut 8 %). 7. **Horizon temporel cumulatif** - Tous les indicateurs sont calculés de manière cumulative : par ex. le TRI à l’année 3 utilise les flux des années 0 à 3. - Cela permet d’analyser la performance au fil du projet. 8. **Traitement des cas limites** - Si le TRI ou le MIRR ne peuvent être calculés (tous les flux étant positifs ou négatifs), la valeur retournée est null. - Les résultats sont arrondis pour clarté : VAN à 1 décimale, BCR à 2, TRI/MIRR à 1 décimale (%). Tous les calculs sont réalisés localement dans le notebook Observable (aucune API externe), assurant interactivité et transparence. Une version antérieure et plus technique de cette méthodologie a été implémentée dans l’outil R Shiny [AICCRA_EFA](https://github.com/CIAT/AICCRA_EFA).` } } } } }; }37:Tc0c,styling = { return !toggleCustomStyling ? "no custom styling" : htl.html`` }38:T4cf,function tocOneliner({ selector = "h1", // selectors to include (can comma-separate, like "h1,h2,h3") heading = "In this notebook", // heading for ToC delim = ' | ', // delimiter between links skip = [], // text content for headers to exclude } = {}) { // return a one-liner table of contents // allow for skips // modified from https://observablehq.com/@mbostock/toc return Generators.observe(notify => { let headings = []; function observed() { let h = Array.from(document.querySelectorAll(selector)); h = h.filter((d) => !skip.includes(d.textContent)) if (h.length !== headings.length || h.some((h, i) => headings[i] !== h)) { notify(html`${heading}
${Array.from(headings = h, (h, i) => { const end = i === headings.length - 1 return Object.assign( html`${DOM.text(h.textContent)}${end ? '' : delim}`, {onclick: e => (e.preventDefault(), h.scrollIntoView())} ); })}`); } } const observer = new MutationObserver(observed); observer.observe(document.body, {childList: true, subtree: true}); observed(); return () => observer.disconnect(); }); }39:T769,walkthroughEconomicIndicators = { const selection = walkthroughSelections; const data = dataFinanceSelectedAdminCrop.filter( (d) => d.adoption == selection.adoption && d.prod_impact == selection.prod_impact && d.cis_impact == selection.cis_impact && d.discount_rate == selection.discount_rate ); const years = data.map(d => d.year); const benefit = data.map(d => d.project_benefit); const cost = data.map(d => d.cost); const cashflow = benefit.map((b, i) => b - cost[i]); const discountRate = selection.discount_rate; // === NPV cumulative === const npvDiscreteCumulative = (values, rate) => values.map((_, i) => values .slice(0, i + 1) .reduce((acc, val, j) => acc + val / Math.pow(1 + rate, j), 0) ); const npv = npvDiscreteCumulative(cashflow, discountRate); const npv_benefit = npvDiscreteCumulative(benefit, discountRate); const npv_cost = npvDiscreteCumulative(cost, discountRate); const discounted_bcr = npv_benefit.map((b, i) => npv_cost[i] === 0 ? null : b / npv_cost[i]); const proj_irr = calculateIRR(cashflow); const proj_mirr = calcMIRR(cashflow, discountRate, discountRate); const proj_npv = npv.at(-1); const proj_bcr = discounted_bcr.at(-1); // === Enriched table const enrichedData = years.map((year, i) => { const cashflowToDate = cashflow.slice(0, i + 1); return { year, project_benefit: benefit[i], cost: cost[i], cashflow: cashflow[i], discounted_benefit_cum: npv_benefit[i], discounted_cost_cum: npv_cost[i], discounted_bcr: discounted_bcr[i], irr: calculateIRR(cashflowToDate)*100, mirr: calcMIRR(cashflowToDate, discountRate, discountRate)*100, npv: npv[i] }; }); return { summary: { proj_npv, proj_irr, proj_mirr, proj_bcr }, enriched: enrichedData }; }3a:T874,dataPickMetric = { function _getIt(path) { return _lang(path); } return [ { key: "irr", columnName: "irr", label: _getIt(nbText.indicators.irr.label), tooltip: _getIt(nbText.indicators.irr.tooltip), labelLong: _getIt(nbText.indicators.irr.labelLong), labelFull: _getIt(nbText.indicators.irr.labelFull), info: { what: _getIt(nbText.indicators.irr.info.what), interpret: _getIt(nbText.indicators.irr.info.interpret), tell: _getIt(nbText.indicators.irr.info.tell) }, yFormat: (d) => `${d.toFixed(1)}%` }, { key: "mirr", columnName: "mirr", label: _getIt(nbText.indicators.mirr.label), tooltip: _getIt(nbText.indicators.mirr.tooltip), labelLong: _getIt(nbText.indicators.mirr.labelLong), labelFull: _getIt(nbText.indicators.mirr.labelFull), info: { what: _getIt(nbText.indicators.mirr.info.what), interpret: _getIt(nbText.indicators.mirr.info.interpret), tell: _getIt(nbText.indicators.mirr.info.tell) }, yFormat: (d) => `${d.toFixed(1)}%` }, { key: "npv", columnName: "npv", label: _getIt(nbText.indicators.npv.label), tooltip: _getIt(nbText.indicators.npv.tooltip), labelLong: _getIt(nbText.indicators.npv.labelLong), labelFull: _getIt(nbText.indicators.npv.labelFull), info: { what: _getIt(nbText.indicators.npv.info.what), interpret: _getIt(nbText.indicators.npv.info.interpret), tell: _getIt(nbText.indicators.npv.info.tell) }, yFormat: (d) => formatCurrencyAuto(d, 1) }, { key: "bcr", columnName: "discounted_bcr", label: _getIt(nbText.indicators.bcr.label), tooltip: _getIt(nbText.indicators.bcr.tooltip), labelLong: _getIt(nbText.indicators.bcr.labelLong), labelFull: _getIt(nbText.indicators.bcr.labelFull), info: { what: _getIt(nbText.indicators.bcr.info.what), interpret: _getIt(nbText.indicators.bcr.info.interpret), tell: _getIt(nbText.indicators.bcr.info.tell) }, yFormat: d3.format(".2f") } ]; }3b:Tae8,{ const text = `### v1.1.0: Support feedback - #37: update data to 'roi_data_v2.parquet' from bucket. Updated dataset source text and methods citations. - #83: excluded 'Sudan' admin region (filtered out from country dropdown) ### v0.6.1: Project Cost text box ### v1.0.0: adding language conversion - language toggle ### v0.6.1: Project Cost text box - Use text box as input for project cost - Typing in options, ignore $/, then parse as number - Have cutoff in wrangling to return if cost is zero ### v0.6: Checking calculations, rejigger cashflows - Cashflow should be calculated using current year cost, not cumulative - Note: data for admin0 is the sum of the individual admin1's ### v0.5 - naming some cells for embeds - adding first pass of methods and sources ### Outstanding notes - lingering multi-select boxes - Incorporating "additional benefit" of CIS climate impacts (compared to CIS being zero) with the big numbers - External connection to Risk data ### v0.4.1: updating CIS impact values (2024-03-22) - uses 0, 0.1, 0.25, and 0.5 for climate reduction percentages ### v0.4 Fork: Tooltips and more - Adding text explaining the indicators - Adding tooltips to explain choices - Consolidate chart: remove time horizon - Wording/defaults for dropdowns 2024-03-19 - Add tooltips to selectors - Move CIS impact up as control value - Use methods and sources as title for reference - Project years uses a dropdown - Update section names - organization - title-case - Add Pete's info about terms and understanding - Updated defaults for dropdowns - Add indicator info - explain terms - dynamic text based on chart indicator - Chopping block - timeseries with separate curves 2024-03-20 - Remove time horizon - Removed input - Chart restricted to project span only - Update wording for CIS control - Add tooltips to number cards ### v0.2: Tighten up organization (2024-03-11) - Tightened up investment summary - Formatted crop names in dropdown ### v0.1: start organizing, multi-select 2024-03-08 Admin - Simplify notebook title - Cull chopping block: test plots and financial values calculations - Remove internal understanding under appendix Multi-select crop and region - external multi-select, define region table; crop multi Adopting organization from spec sheet - Sections and walkthrough of choices Applying styling - Default styling to true - Horizontal a bunch of inputs - Styling results, boxy Merge in v0.1.1: CSS updates and multi-select massaging `; const newestVersion = text.split('###') .filter(d => d) .map(d => d.trim()) ?.[0] .split('\n') ?.[0] return makeDevNotes({ text: md`${text}`, title: newestVersion ?? "Dev notes", emojis: "🪵✏️" }) }3c:[{"id":"3f7ec78f9b6255db9cf25ba193d8489a5e72c6d42162d7d5fbe1593cabc0e7ae6c6de6a65fb478da936ccec3a2cfb68b7e668faadde8c21096c96ac7ecefce3f","href":"https://static.observableusercontent.com/files/3f7ec78f9b6255db9cf25ba193d8489a5e72c6d42162d7d5fbe1593cabc0e7ae6c6de6a65fb478da936ccec3a2cfb68b7e668faadde8c21096c96ac7ecefce3f","name":"roi_data@1.parquet","type":"application/octet-stream","lastModified":1711122922339.342,"size":46526287},{"id":"ce41ad11cc9fae4b5699d4c684e543050d16805bb0be510e4b82a6b1bbb049d4406dc43fb2a92f5a700f4f3a458514dfa387e0c18a261550b576d52eb828a394","href":"https://static.observableusercontent.com/files/ce41ad11cc9fae4b5699d4c684e543050d16805bb0be510e4b82a6b1bbb049d4406dc43fb2a92f5a700f4f3a458514dfa387e0c18a261550b576d52eb828a394","name":"Periscopic-Alliance-Bioversity_Adaptation-Atlas_Notebook-Title_Spotlight-3@1.png","type":"image/png","lastModified":1711390742773.626,"size":1706957},{"id":"3f79acd500484d90a828b20bb5dcd70c254c997c8dc72fd84fed195d8855d4f8b6679db0c08233d6048f97b278ac70c2cf515f2d1898cf38a545c68c709f2d8a","href":"https://static.observableusercontent.com/files/3f79acd500484d90a828b20bb5dcd70c254c997c8dc72fd84fed195d8855d4f8b6679db0c08233d6048f97b278ac70c2cf515f2d1898cf38a545c68c709f2d8a","name":"Estimate-economic-returns-fr.webp","type":"image/webp","lastModified":1724434204804.998,"size":88938},{"id":"61c1089f5c085a5f607aeb33a68e3d79c59c88ae5ce4393a9a4a8a3f886d260a70bb36482a7990ae1bfe64382ef3c86aa24aa6e56062e0101d47159c2eb6740d","href":"https://static.observableusercontent.com/files/61c1089f5c085a5f607aeb33a68e3d79c59c88ae5ce4393a9a4a8a3f886d260a70bb36482a7990ae1bfe64382ef3c86aa24aa6e56062e0101d47159c2eb6740d","name":"Estimate-economic-returns.webp","type":"image/webp","lastModified":1724434204804.998,"size":88180},{"id":"6775f7f81ae22c87a45011aae6d5f06d38c70a40803a94cc16599d7e2638da38e46739d0263aa6e29fd4a97ab95cb3499a217504f05513dd63a207a227b2da21","href":"https://static.observableusercontent.com/files/6775f7f81ae22c87a45011aae6d5f06d38c70a40803a94cc16599d7e2638da38e46739d0263aa6e29fd4a97ab95cb3499a217504f05513dd63a207a227b2da21","name":"roi_data_v2(1).parquet","type":"application/octet-stream","lastModified":1724434204804.998,"size":47811907},{"id":"5bc819ff7784e4c7d3a01c5fa646f6283befcefba12470450a6e38746ebb9d837dc4c933376c33fa63c283f4043572321c814f0e94d79a16a9ebbad27b8a0ec8","href":"https://static.observableusercontent.com/files/5bc819ff7784e4c7d3a01c5fa646f6283befcefba12470450a6e38746ebb9d837dc4c933376c33fa63c283f4043572321c814f0e94d79a16a9ebbad27b8a0ec8","name":"translation_v2-1.json","type":"application/json","lastModified":1724434204804.998,"size":99335},{"id":"9bdbab92ce028e3846e0ac093ffcb1d4efcdd01e67d93b0e133dfe972a699e892f764b8844dfa453c72008bbd9ecf4c1bc2ecf4b7487240746196a81b4a973b2","href":"https://static.observableusercontent.com/files/9bdbab92ce028e3846e0ac093ffcb1d4efcdd01e67d93b0e133dfe972a699e892f764b8844dfa453c72008bbd9ecf4c1bc2ecf4b7487240746196a81b4a973b2","name":"roi_data_v2_optimized.parquet","type":"application/octet-stream","lastModified":1746524254271.286,"size":40720945}] 2d:{"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":"$31","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":"$32","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":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":2245,"value":"$34","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":"$35","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":"$36","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":"$37","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":"$38","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":"$39","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":"$3a","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":"$3b","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W3c","stdlib":"1","titling":"auto"}