create_budget_template
Generate a customizable Excel budget template to track actual vs planned expenses across categories like salaries, rent, and utilities for financial analysis.
Instructions
Create budget vs actual Excel template
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| categories | No | ||
| filePath | Yes |
Implementation Reference
- Core handler function in Python that generates a pandas DataFrame representing the budget vs actual Excel template structure based on input categories.@staticmethod def create_budget_template(categories: List[str]) -> pd.DataFrame: """Create budget vs actual template""" template_data = [] # Headers template_data.append(['Category', 'Annual Budget', 'YTD Budget', 'YTD Actual', 'Variance', 'Variance %']) # Revenue section template_data.append(['REVENUE', '', '', '', '', '']) for cat in ['Sales Revenue', 'Service Revenue', 'Other Revenue']: template_data.append([cat, 0, '=B{}/12*MONTH(TODAY())', 0, '=D{}-C{}', '=IF(C{}=0,0,E{}/C{}*100)']) template_data.append(['Total Revenue', '=SUM(B3:B5)', '=SUM(C3:C5)', '=SUM(D3:D5)', '=D6-C6', '=IF(C6=0,0,E6/C6*100)']) # Expense section template_data.append(['', '', '', '', '', '']) template_data.append(['EXPENSES', '', '', '', '', '']) for category in categories: template_data.append([category, 0, '=B{}/12*MONTH(TODAY())', 0, '=D{}-C{}', '=IF(C{}=0,0,E{}/C{}*100)']) next_row = len(template_data) + 1 template_data.append(['Total Expenses', f'=SUM(B9:B{next_row-1})', f'=SUM(C9:C{next_row-1})', f'=SUM(D9:D{next_row-1})', f'=D{next_row}-C{next_row}', f'=IF(C{next_row}=0,0,E{next_row}/C{next_row}*100)']) # Net Income template_data.append(['', '', '', '', '', '']) template_data.append(['NET INCOME', f'=B6-B{next_row}', f'=C6-C{next_row}', f'=D6-D{next_row}', f'=D{next_row+2}-C{next_row+2}', f'=IF(C{next_row+2}=0,0,E{next_row+2}/C{next_row+2}*100)']) return pd.DataFrame(template_data)
- src/tools/reporting-tools.ts:302-352 (registration)MCP tool registration for 'create_budget_template', including name, description, input schema, and TypeScript handler that invokes the Python implementation via bridge and optionally saves to Excel.{ name: "create_budget_template", description: "Create budget vs actual Excel template", inputSchema: { type: "object", properties: { categories: { type: "array", items: { type: "string" }, default: [ "Salaries & Wages", "Rent", "Utilities", "Marketing", "Office Supplies", "Professional Fees", "Travel", "Insurance" ] }, filePath: { type: "string" } }, required: ["filePath"] }, handler: async (args: any): Promise<ToolResult> => { try { const categories = args.categories || [ "Salaries & Wages", "Rent", "Utilities", "Marketing", "Office Supplies", "Professional Fees", "Travel", "Insurance" ]; const result = await pythonBridge.callPythonFunction({ module: 'financial_reporting', function: 'ReportTemplates.create_budget_template', args: [categories] }); if (result.success && args.filePath) { const templateData = result.data; const worksheetData: WorksheetData = { name: 'Budget vs Actual', data: templateData }; await excelManager.createWorkbook([worksheetData]); await excelManager.saveWorkbook(args.filePath); } return result; } catch (error) { return { success: false, error: error instanceof Error ? error.message : String(error) }; } } }