SPB Git

Repository Public

RatiosTool.swift 13.1 KB 204 lines swift

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
Files main
Menu

Theme