ratio_analysis
Calculate financial ratios from Excel/CSV data and compare them against industry benchmarks for performance evaluation.
Instructions
Perform comprehensive financial ratio analysis with industry benchmarks
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| filePath | Yes | Path to the CSV or Excel file with financial statement data | |
| sheet | No | Sheet name for Excel files (optional) |
Implementation Reference
- The core handler function for the 'ratio_analysis' tool. Reads financial data from a specified spreadsheet file and sheet, extracts key financial metrics, computes liquidity, leverage, profitability ratios, compares against industry benchmarks, generates interpretation, and returns structured JSON response.async ratioAnalysis(args: ToolArgs): Promise<ToolResponse> { const { filePath, sheet } = args; try { const data = await readFileContent(filePath, sheet); if (data.length < 2) { throw new Error('Insufficient data for ratio analysis'); } // Extract financial data (assuming standard financial statement format) const headers = data[0]; const values = data[1]; // Assuming single period const financials = { currentAssets: this.findValue(headers, values, ['current assets', 'currentAssets']), currentLiabilities: this.findValue(headers, values, ['current liabilities', 'currentLiabilities']), inventory: this.findValue(headers, values, ['inventory']), totalDebt: this.findValue(headers, values, ['total debt', 'totalDebt']), totalEquity: this.findValue(headers, values, ['total equity', 'totalEquity']), netIncome: this.findValue(headers, values, ['net income', 'netIncome']), totalAssets: this.findValue(headers, values, ['total assets', 'totalAssets']), grossProfit: this.findValue(headers, values, ['gross profit', 'grossProfit']), revenue: this.findValue(headers, values, ['revenue', 'sales']), operatingIncome: this.findValue(headers, values, ['operating income', 'operatingIncome']) }; // Calculate ratios const ratios = { currentRatio: financials.currentAssets / financials.currentLiabilities, quickRatio: (financials.currentAssets - financials.inventory) / financials.currentLiabilities, debtToEquity: financials.totalDebt / financials.totalEquity, returnOnEquity: financials.netIncome / financials.totalEquity, returnOnAssets: financials.netIncome / financials.totalAssets, grossMargin: financials.grossProfit / financials.revenue, operatingMargin: financials.operatingIncome / financials.revenue }; // Industry benchmarks const benchmarks = { currentRatio: { range: '1.2 - 2.0', status: this.evaluateBenchmark(ratios.currentRatio, 1.2, 2.0) }, quickRatio: { range: '0.8 - 1.5', status: this.evaluateBenchmark(ratios.quickRatio, 0.8, 1.5) }, debtToEquity: { range: '0.3 - 1.5', status: this.evaluateBenchmark(ratios.debtToEquity, 0.3, 1.5) } }; return { content: [{ type: 'text', text: JSON.stringify({ success: true, analysis: 'Financial Ratio Analysis', ratios: Object.fromEntries( Object.entries(ratios).map(([key, value]) => [key, Math.round(value * 100) / 100]) ), benchmarks, interpretation: this.generateRatioInterpretation(ratios, benchmarks) }, null, 2) }] }; } catch (error) { return { content: [{ type: 'text', text: JSON.stringify({ success: false, error: error instanceof Error ? error.message : 'Unknown error', operation: 'ratio_analysis' }, null, 2) }] }; } }
- Helper method to locate and extract numeric values for financial metrics from spreadsheet headers using flexible name matching.private findValue(headers: string[], values: any[], possibleNames: string[]): number { for (const name of possibleNames) { const index = headers.findIndex(h => h.toLowerCase().includes(name.toLowerCase())); if (index !== -1) { return Number(values[index]) || 0; } } return 0; }
- Helper method to assess whether a calculated ratio falls within, above, or below industry benchmark ranges.private evaluateBenchmark(value: number, min: number, max: number): string { if (value < min) return 'Below Industry Average'; if (value > max) return 'Above Industry Average'; return 'Within Industry Range'; }
- Helper method to generate a textual interpretation of the financial ratios and benchmarks, highlighting strengths and concerns.private generateRatioInterpretation(ratios: any, benchmarks: any): string { const concerns = []; const strengths = []; if (ratios.currentRatio < 1.2) concerns.push('Liquidity may be tight'); if (ratios.debtToEquity > 1.5) concerns.push('High leverage'); if (ratios.returnOnEquity > 0.15) strengths.push('Strong profitability'); return `Analysis: ${strengths.concat(concerns).join(', ')}`; }