Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/FeatureAnalysis/Sources/FinanceStudio/Planning/RatiosTool.swift10// Module : FinanceStudio11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import SwiftUI89import SheetCore90import FinanceModels91import AnalysisKit9293/// Financial ratio analysis from mapped statement lines.94enum RatiosTool {95 static let descriptor: ToolDescriptor = {96 var t = ToolDescriptor(97 id: "finance.ratios", title: L("Financial Ratio Analysis"), group: FinGroup.planning, systemImage: "percent",98 summary: L("Liquidity, leverage, profitability, efficiency and DuPont ratios from the lines of an income statement and a balance sheet."),99 fields: [100 .range("data", L("Statements range")),101 .column("labels", L("Line labels"), numeric: false),102 .column("current", L("Current period")),103 .column("prior", L("Prior period (optional)"), optional: true, help: L("With a prior period, flow ratios use average balances.")),104 .number("days", L("Days in the period"), 365, min: 1, max: 366),105 ],106 conventions: [107 L("A ratio whose denominator is zero is left blank, never shown as infinity."),108 L("With a prior period, turnover ratios, ROA, ROE and the DuPont terms use the average of opening and closing balances; otherwise period-end balances."),109 L("Liquidity: current = current assets / current liabilities; quick = (cash + marketable securities + receivables) / current liabilities; cash ratio = (cash + marketable securities) / current liabilities."),110 L("Leverage: debt/equity, debt/assets, liabilities/assets, equity multiplier = assets / equity, interest coverage = EBIT / interest, net debt / EBITDA with EBITDA = EBIT + D&A."),111 L("Efficiency: turnovers = flow / balance; days outstanding = balance × days / flow; cash conversion cycle = DIO + DSO − DPO."),112 L("DuPont: ROE = net margin × asset turnover × equity multiplier (3-step) = tax burden × interest burden × EBIT margin × asset turnover × equity multiplier (5-step)."),113 ],114 runTitle: L("Compute Ratios"),115 run: run)116 t.extraForm = { AnyView(RatioMappingView(context: $0)) }117 t.demo = { _, v in v.set("data", range: "Statements!A1:C19"); v.set("labels", column: 0); v.set("current", column: 1); v.set("prior", column: 2) }118 return t119 }()120121 static let names: [String: String] = [122 "Liquidity": L("Liquidity"), "Leverage": L("Leverage"), "Profitability": L("Profitability"), "Efficiency": L("Efficiency"), "DuPont": L("DuPont"),123 "Current ratio": L("Current ratio"), "Quick ratio": L("Quick ratio"), "Cash ratio": L("Cash ratio"), "Working capital": L("Working capital"),124 "Debt to equity": L("Debt to equity"), "Debt to assets": L("Debt to assets"), "Liabilities to assets": L("Liabilities to assets"),125 "Equity multiplier": L("Equity multiplier"), "Interest coverage": L("Interest coverage"), "Net debt to EBITDA": L("Net debt to EBITDA"),126 "Gross margin": L("Gross margin"), "Operating margin": L("Operating margin"), "EBITDA margin": L("EBITDA margin"), "Net margin": L("Net margin"),127 "Return on assets": L("Return on assets"), "Return on equity": L("Return on equity"), "Asset turnover": L("Asset turnover"),128 "Inventory turnover": L("Inventory turnover"), "Receivables turnover": L("Receivables turnover"), "Payables turnover": L("Payables turnover"),129 "Days inventory outstanding": L("Days inventory outstanding"), "Days sales outstanding": L("Days sales outstanding"),130 "Days payables outstanding": L("Days payables outstanding"), "Cash conversion cycle": L("Cash conversion cycle"),131 "DuPont ROE (3-step)": L("DuPont ROE (3-step)"), "Tax burden": L("Tax burden"), "Interest burden": L("Interest burden"),132 "DuPont ROE (5-step)": L("DuPont ROE (5-step)"), "EBIT margin": L("EBIT margin"),133 ]134135 static let formulas: [String: String] = [136 "current assets / current liabilities": L("current assets / current liabilities"),137 "(cash + marketable securities + receivables) / current liabilities": L("(cash + marketable securities + receivables) / current liabilities"),138 "(cash + marketable securities) / current liabilities": L("(cash + marketable securities) / current liabilities"),139 "current assets − current liabilities": L("current assets − current liabilities"), "total debt / equity": L("total debt / equity"),140 "total debt / total assets": L("total debt / total assets"), "total liabilities / total assets": L("total liabilities / total assets"),141 "total assets / equity": L("total assets / equity"), "EBIT / interest expense": L("EBIT / interest expense"),142 "(total debt − cash − marketable securities) / (EBIT + D&A)": L("(total debt − cash − marketable securities) / (EBIT + D&A)"),143 "(revenue − COGS) / revenue": L("(revenue − COGS) / revenue"), "EBIT / revenue": L("EBIT / revenue"), "(EBIT + D&A) / revenue": L("(EBIT + D&A) / revenue"),144 "net income / revenue": L("net income / revenue"), "net income / total assets": L("net income / total assets"), "net income / equity": L("net income / equity"),145 "revenue / total assets": L("revenue / total assets"), "COGS / inventory": L("COGS / inventory"), "revenue / receivables": L("revenue / receivables"),146 "COGS / payables": L("COGS / payables"), "inventory × days / COGS": L("inventory × days / COGS"), "receivables × days / revenue": L("receivables × days / revenue"),147 "payables × days / COGS": L("payables × days / COGS"), "DIO + DSO − DPO": L("DIO + DSO − DPO"),148 "net margin × asset turnover × equity multiplier": L("net margin × asset turnover × equity multiplier"),149 "net income / pretax income": L("net income / pretax income"), "pretax income / EBIT": L("pretax income / EBIT"),150 "tax burden × interest burden × EBIT margin × asset turnover × equity multiplier": L("tax burden × interest burden × EBIT margin × asset turnover × equity multiplier"),151 ]152153 static func formula(_ engine: String) -> String {154 let base = engine.replacingOccurrences(of: "average ", with: "")155 let text = formulas[base] ?? base156 return base == engine ? text : text + " " + L("(average balances)")157 }158159 static func format(_ name: String) -> CellFormat {160 if name.contains("margin") || name.hasPrefix("Return") || name.hasPrefix("DuPont ROE") { return .percent(2) }161 if name.hasPrefix("Days") || name == "Cash conversion cycle" { return .fixed(1) }162 if name == "Working capital" { return .money }163 return .fixed(3)164 }165166 static func run(_ input: ToolInput) throws -> ToolResult {167 let frame = try input.frame()168 let labelColumn = try input.column("labels"), current = try input.column("current")169 let prior = input.optionalColumn("prior").flatMap { $0.index == current.index ? nil : $0 }170 let labels = labelColumn.labels171 let rows = RatioMapping.resolve(labels: labels, explicit: RatioMapping.decode(input.values.text("mapping")))172 let unmatched = RatioMapping.items.filter { rows[$0.id] == nil }173 try Fin.require(unmatched.count < RatioMapping.items.count, L("No statement line could be matched. Check the label column or map the lines manually."))174 let s = RatioMapping.statement(rows, values: current.numbers)175 let p = prior.map { RatioMapping.statement(rows, values: $0.numbers) }176 let r = FinancialRatios.compute(s, prior: p, daysInPeriod: input.values.number("days"))177178 func metric(_ name: String) -> Metric {179 r[name].map { Metric(names[name] ?? name, $0, format(name)) } ?? Metric(names[name] ?? name, text: "—")180 }181 var blocks: [ResultBlock] = [.metrics(["Current ratio", "Debt to equity", "Net margin", "Return on equity"].map(metric))]182 for category in ["Liquidity", "Leverage", "Profitability", "Efficiency", "DuPont"] {183 let list = r.ratios.filter { $0.category == category }184 var t = ReportTable(title: names[category] ?? category, rows: [[.text(L("Ratio")), .text(L("Value")), .text(L("Formula"))]]185 + list.map { [.text(names[$0.name] ?? $0.name), .finite($0.value), .text(formula($0.formula))] })186 for (i, x) in list.enumerated() { t.rowFormats[i + 1] = format(x.name) }187 blocks.append(.table(t))188 }189 var mapRows: [[Value]] = [[.text(L("Statement line")), .text(L("Matched label")), .text(current.name)] + (prior != nil ? [.text(prior!.name)] : [])]190 for item in RatioMapping.items.sorted(by: { $0.label < $1.label }) {191 let i = rows[item.id]192 mapRows.append([.text(item.label), .text(i.map { labels[$0] } ?? L("not matched (0)")), .finite(i.map { current.numbers[$0] })]193 + (prior != nil ? [.finite(i.map { prior!.numbers[$0] })] : []))194 }195 var mapTable = ReportTable(title: L("Statement lines used"), rows: mapRows)196 mapTable.columnFormats = [2: .money, 3: .money]197 blocks.append(.table(mapTable))198 if !unmatched.isEmpty { blocks.append(.warning(L("Not matched and counted as 0: %@. Ratios that depend on them are not meaningful.", unmatched.map(\.label).joined(separator: ", ")))) }199 blocks.append(.note([r.usesAverageBalances ? L("Flow ratios use the average of the prior and current balances.") : L("Period-end balances are used (no prior period)."),200 L("Ratios with a zero denominator are left blank.")]))201 return ToolResult(title: L("Financial Ratio Analysis"), subtitle: L("%@ — period “%@”", frame.label, current.name), blocks: blocks, sheetName: L("Ratios"))202 }203}204