COMPLETE_DOCUMENTATION.md•32.3 kB
# Excel Finance MCP Server - Complete Professional Guide
**Version 2.0.0 | Enterprise Edition**
---
## Table of Contents
1. [Introduction & Quick Start](#introduction--quick-start)
2. [Basic Financial Operations](#part-i-basic-financial-operations)
- Excel File Management
- Financial Calculations
- Expense Tracking
- Basic Cash Flow Analysis
3. [Intermediate Financial Analysis](#part-ii-intermediate-financial-analysis)
- Loan Amortization & Analysis
- Tax Calculations
- Financial Statements
- Rental Property Management
4. [Advanced Analytics & Modeling](#part-iii-advanced-analytics--modeling)
- 13/52-Week Cash Flow Forecasting
- DCF Valuation Models
- Scenario Analysis
- Executive Dashboards
5. [Enterprise Risk Management](#part-iv-enterprise-risk-management)
- Monte Carlo Simulations
- Sensitivity Analysis
- Portfolio Risk Assessment
6. [Regulatory Compliance](#part-v-regulatory-compliance)
- ASC 606 Revenue Recognition
- SOX Controls Testing
- Audit Preparation
7. [Property Management Suite](#part-vi-property-management-suite)
- Lease Lifecycle Management
- Investment Analysis
- Maintenance & CapEx Planning
8. [Professional Reporting](#part-vii-professional-reporting)
- Intelligent Chart Generation
- Executive Presentations
- Board Packages
9. [Quick Reference Guide](#quick-reference-guide)
---
## Introduction & Quick Start
This MCP (Model Context Protocol) server transforms Excel into an enterprise-grade financial platform. It provides accounting professionals with automated tools that typically require expensive software or consulting services.
### Installation (5 minutes)
```bash
# Clone the repository
git clone https://github.com/your-org/excel-finance-mcp.git
cd excel-finance-mcp
# Install dependencies
npm install
# Build the server
npm run build
# Start the server
npm start
```
### Basic Setup Example
```typescript
// Your first financial calculation
financial_calculate_loan_payment({
principal: 500000, // Loan amount
annualRate: 0.045, // 4.5% interest
years: 30, // 30-year mortgage
generateExcel: true // Create Excel file
})
// Result: Monthly payment of $2,533.43 with full amortization schedule in Excel
```
---
## Part I: Basic Financial Operations
### Chapter 1: Excel File Management
**Creating a Basic Financial Workbook**
```typescript
excel_create_workbook({
worksheets: [
{
name: "Income Statement",
data: [
["Revenue", 1000000],
["COGS", -600000],
["Gross Profit", 400000],
["Operating Expenses", -250000],
["EBIT", 150000]
]
}
]
})
```
**Reading Financial Data**
```typescript
excel_read_workbook({
filePath: "Q4_Financials.xlsx",
sheetName: "Balance Sheet",
range: "A1:C50"
})
```
### Chapter 2: Essential Financial Calculations
**NPV Calculation Example**
```typescript
financial_calculate_npv({
rate: 0.10, // 10% discount rate
cashFlows: [-100000, 30000, 40000, 50000, 60000],
generateExcel: true
})
// Result: NPV = $38,016.53
// Interpretation: Positive NPV indicates project should be accepted
```
**IRR Calculation Example**
```typescript
financial_calculate_irr({
cashFlows: [-250000, 80000, 85000, 90000, 95000, 100000],
guess: 0.10 // Initial guess 10%
})
// Result: IRR = 23.44%
// Interpretation: Exceeds 10% hurdle rate, project is viable
```
### Chapter 3: Expense Tracking
**Department Expense Analysis**
```typescript
expense_categorize_transactions({
transactions: [
{
date: "2024-01-15",
description: "Oracle Database License",
amount: 45000,
vendor: "Oracle Corp",
department: "IT"
},
{
date: "2024-01-16",
description: "Q1 Marketing Campaign",
amount: 25000,
vendor: "AdAgency Inc",
department: "Marketing"
}
],
categories: ["Software", "Marketing"],
generateSummary: true
})
// Generates categorized expense report with variance analysis
```
### Chapter 4: Basic Cash Flow Analysis
**Simple Cash Flow Projection**
```typescript
cash_flow_projection({
startingBalance: 500000,
periods: 12, // 12 months
revenues: [
{ month: 1, amount: 150000, description: "Product sales" },
{ month: 1, amount: 25000, description: "Service revenue" }
],
expenses: [
{ month: 1, amount: -100000, description: "Operating costs" },
{ month: 1, amount: -30000, description: "Payroll" }
],
generateExcel: true
})
// Creates monthly cash flow with ending balances and burn rate analysis
```
---
## Part II: Intermediate Financial Analysis
### Chapter 5: Loan Amortization
**Commercial Loan Analysis**
```typescript
financial_calculate_loan_amortization({
principal: 2000000,
annualRate: 0.055, // 5.5% rate
years: 10,
paymentFrequency: "monthly",
extraPayment: 5000, // Additional principal payment
generateExcel: true
})
// Output includes:
// - Payment schedule with principal/interest breakdown
// - Total interest saved: $145,232
// - Payoff acceleration: 18 months earlier
```
### Chapter 6: Tax Calculations
**Corporate Tax Planning**
```typescript
tax_calculate_corporate({
grossIncome: 5000000,
deductions: {
operatingExpenses: 3000000,
depreciation: 200000,
interestExpense: 150000
},
credits: {
researchDevelopment: 50000,
greenEnergy: 25000
},
state: "CA", // California state tax
generateExcel: true
})
// Result:
// Federal Tax: $283,500
// State Tax: $114,400
// Effective Rate: 23.7%
// After-tax Income: $1,277,100
```
### Chapter 7: Financial Statement Generation
**Three-Statement Model**
```typescript
reporting_generate_financial_statements({
period: "2024-Q1",
data: {
revenue: 2500000,
cogs: 1500000,
operatingExpenses: 600000,
interest: 50000,
taxes: 84000,
depreciation: 100000,
capex: 200000,
workingCapitalChange: -50000,
dividends: 100000,
beginningCash: 800000
},
comparativePeriod: "2023-Q1",
generateExcel: true
})
// Generates:
// - Income Statement with margins
// - Balance Sheet with ratios
// - Cash Flow Statement (indirect method)
// - Key metrics dashboard
```
### Chapter 8: Rental Property Analysis
**Investment Property Evaluation**
```typescript
rental_calculate_noi({
propertyId: "123-Main-St",
year: 2024,
monthlyRent: 25000,
occupancyRate: 0.92,
operatingExpenses: {
propertyTax: 35000,
insurance: 15000,
maintenance: 20000,
management: 30000,
utilities: 10000
}
})
// NOI Calculation:
// Gross Rental Income: $300,000
// Vacancy Loss: ($24,000)
// Effective Income: $276,000
// Operating Expenses: ($110,000)
// Net Operating Income: $166,000
// Cap Rate (if value = $2M): 8.3%
```
---
## Part III: Advanced Analytics & Modeling
### Chapter 9: Rolling Forecast Models
**13-Week Cash Flow Forecast**
```typescript
analytics_13_week_cash_flow({
startDate: "2024-01-01",
beginningCash: 2000000,
revenueStreams: [
{
name: "Subscription Revenue",
weekly: [180000, 185000, 190000], // Pattern continues
collectionDays: 30,
badDebtRate: 0.02
},
{
name: "Professional Services",
weekly: [50000, 45000, 60000],
collectionDays: 45,
badDebtRate: 0.03
}
],
expenses: [
{
category: "Payroll",
weekly: 150000,
timing: "weekly"
},
{
category: "Rent",
amount: 100000,
timing: "monthly",
dayOfMonth: 1
}
],
creditFacility: {
limit: 500000,
currentDrawn: 0,
interestRate: 0.08
}
})
// Output:
// - Weekly cash position forecast
// - Borrowing requirements identified in weeks 7 and 11
// - Recommended actions: Accelerate collections or delay payables
// - Minimum cash: $125,000 (Week 11)
// - Credit facility utilization peaks at $375,000
```
### Chapter 10: DCF Valuation
**Company Valuation Model**
```typescript
analytics_dcf_valuation({
// Historical financials
currentRevenue: 50000000,
currentEBITDA: 10000000,
// Projection assumptions
projectionYears: 5,
revenueGrowthRates: [0.20, 0.18, 0.15, 0.12, 0.10],
ebitdaMargins: [0.20, 0.21, 0.22, 0.23, 0.24],
// DCF inputs
discountRate: 0.12, // WACC
terminalGrowthRate: 0.03,
// Other assumptions
capexAsPercentOfRevenue: 0.04,
nwcAsPercentOfRevenue: 0.10,
taxRate: 0.21,
generateExcel: true
})
// Valuation Output:
// Enterprise Value: $165,000,000
// Less: Net Debt: ($20,000,000)
// Equity Value: $145,000,000
//
// Sensitivity Analysis:
// WACC 11%: $178M | WACC 13%: $153M
// Terminal Growth 2.5%: $158M | 3.5%: $173M
```
### Chapter 11: Scenario Analysis
**Strategic Planning Model**
```typescript
analytics_scenario_analysis({
baseCase: {
revenue: 100000000,
growthRate: 0.10,
profitMargin: 0.15,
capexIntensity: 0.05
},
scenarios: [
{
name: "Economic Recession",
adjustments: {
growthRate: -0.05,
profitMargin: 0.10,
probability: 0.25
}
},
{
name: "Market Expansion Success",
adjustments: {
growthRate: 0.25,
profitMargin: 0.18,
probability: 0.30
}
},
{
name: "Status Quo",
adjustments: {},
probability: 0.45
}
],
yearsToProject: 3,
generateExcel: true
})
// Weighted Outcome:
// Expected Revenue Year 3: $142M
// Expected EBITDA Year 3: $21.3M
// Value at Risk (95% confidence): ($8.5M)
// Upside Potential (95% confidence): $34.2M
```
### Chapter 12: Executive Dashboards
**C-Suite KPI Dashboard**
```typescript
analytics_executive_dashboard({
kpis: [
{
name: "Revenue Run Rate",
current: 120000000,
target: 150000000,
category: "financial",
importance: "high"
},
{
name: "EBITDA Margin",
current: 0.22,
target: 0.25,
category: "financial"
},
{
name: "Customer Acquisition Cost",
current: 1500,
target: 1200,
category: "operational"
},
{
name: "Cash Runway",
current: 18, // months
target: 24,
category: "liquidity"
}
],
period: "2024-Q1",
comparePeriod: "2023-Q4",
generateVisualizations: true
})
// Dashboard includes:
// - Traffic light indicators (Red/Yellow/Green)
// - Trend sparklines
// - YoY and QoQ comparisons
// - AI-generated insights: "CAC trending unfavorably, consider optimizing marketing spend"
```
---
## Part IV: Enterprise Risk Management
### Chapter 13: Monte Carlo Simulations
**Project Risk Assessment**
```typescript
analytics_monte_carlo_simulation({
scenarioName: "New Product Launch ROI",
iterations: 10000,
// Define the model
formula: "(revenue - costs - marketing) / initial_investment",
// Define variable distributions
variables: [
{
name: "revenue",
distributionType: "triangular",
parameters: {
min: 5000000, // Pessimistic
mode: 8000000, // Most likely
max: 12000000 // Optimistic
}
},
{
name: "costs",
distributionType: "normal",
parameters: {
mean: 3000000,
stdDev: 500000
}
},
{
name: "marketing",
distributionType: "uniform",
parameters: {
min: 500000,
max: 1500000
}
},
{
name: "initial_investment",
distributionType: "constant",
parameters: {
value: 2000000
}
}
],
confidenceLevels: [0.05, 0.25, 0.50, 0.75, 0.95],
generateExcel: true
})
// Simulation Results:
// Expected ROI: 1.85 (185% return)
// Standard Deviation: 0.73
//
// Confidence Intervals:
// 5th Percentile: 0.65 (65% return) - Worst case
// 25th Percentile: 1.35
// 50th Percentile: 1.80 (Median)
// 75th Percentile: 2.30
// 95th Percentile: 3.10 - Best case
//
// Probability Analysis:
// P(ROI < 1.0): 15% - Probability of loss
// P(ROI > 2.0): 42% - Probability of exceeding target
//
// Value at Risk (VaR 95%): Project will return at least 65%
```
**Portfolio Risk Analysis**
```typescript
analytics_monte_carlo_portfolio_risk({
portfolioName: "Investment Portfolio 2024",
iterations: 50000,
timeHorizon: 252, // Trading days
assets: [
{
name: "US Equities",
allocation: 0.40,
expectedReturn: 0.10,
volatility: 0.15,
distribution: "normal"
},
{
name: "Corporate Bonds",
allocation: 0.30,
expectedReturn: 0.05,
volatility: 0.08,
distribution: "normal"
},
{
name: "Real Estate",
allocation: 0.20,
expectedReturn: 0.08,
volatility: 0.12,
distribution: "lognormal"
},
{
name: "Commodities",
allocation: 0.10,
expectedReturn: 0.06,
volatility: 0.20,
distribution: "student_t",
degreesOfFreedom: 5 // Fat tails
}
],
correlations: [
[1.00, 0.15, 0.30, -0.10], // Equities
[0.15, 1.00, 0.20, -0.05], // Bonds
[0.30, 0.20, 1.00, 0.25], // Real Estate
[-0.10, -0.05, 0.25, 1.00] // Commodities
],
riskMetrics: ["VaR", "CVaR", "maxDrawdown", "sharpeRatio"]
})
// Portfolio Risk Metrics:
// Expected Annual Return: 7.8%
// Portfolio Volatility: 9.2%
//
// Value at Risk (95% confidence):
// 1-Day VaR: -$1,450,000 (on $100M portfolio)
// 1-Month VaR: -$6,800,000
//
// Conditional VaR (Expected Shortfall):
// CVaR (95%): -$2,100,000
//
// Maximum Drawdown (95th percentile): -12.5%
// Sharpe Ratio: 0.85
//
// Stress Test Results:
// 2008 Crisis Scenario: -23.4%
// COVID-19 Scenario: -18.2%
// Rising Rates Scenario: -8.7%
```
### Chapter 14: Sensitivity Analysis
**Business Model Sensitivity**
```typescript
analytics_sensitivity_analysis({
baseModel: {
revenue: 10000000,
cogs: 6000000,
opex: 2500000,
taxRate: 0.21
},
sensitivityVariables: [
{
name: "revenue",
range: [-0.20, -0.10, 0, 0.10, 0.20], // ±20% in 10% steps
type: "percentage"
},
{
name: "cogs",
range: [-0.15, -0.075, 0, 0.075, 0.15],
type: "percentage"
}
],
outputMetric: "netIncome",
generateTornadoDiagram: true,
generateHeatMap: true
})
// Sensitivity Results:
// Most Sensitive Variable: Revenue (±$2.0M impact)
//
// Two-Way Sensitivity Table:
// Revenue Change
// COGS -20% -10% 0% +10% +20%
// -15% $2.31M $2.89M $3.47M $4.05M $4.63M
// -7.5% $1.97M $2.55M $3.13M $3.71M $4.29M
// 0% $1.63M $2.21M $2.79M $3.37M $3.95M
// +7.5% $1.29M $1.87M $2.45M $3.03M $3.61M
// +15% $0.95M $1.53M $2.11M $2.69M $3.27M
//
// Break-even Points:
// Revenue needs to decline by 27.9% to reach break-even
// COGS can increase by 44.6% before break-even
```
---
## Part V: Regulatory Compliance
### Chapter 15: ASC 606 Revenue Recognition
**Software Company Revenue Recognition**
```typescript
compliance_asc606_revenue_recognition({
contracts: [
{
contractId: "ENT-2024-001",
customerName: "Fortune 500 Corp",
contractValue: 1200000,
startDate: "2024-01-01",
endDate: "2024-12-31",
performanceObligations: [
{
id: "SOFTWARE_LICENSE",
description: "Perpetual software license",
standAloneSellingPrice: 800000,
recognitionMethod: "point_in_time",
recognitionDate: "2024-01-01"
},
{
id: "IMPLEMENTATION",
description: "Implementation services",
standAloneSellingPrice: 200000,
recognitionMethod: "over_time",
startDate: "2024-01-01",
endDate: "2024-03-31",
percentComplete: 0.75 // 75% complete
},
{
id: "SUPPORT",
description: "Annual support",
standAloneSellingPrice: 200000,
recognitionMethod: "over_time",
startDate: "2024-01-01",
endDate: "2024-12-31"
}
],
paymentTerms: {
upfront: 600000,
quarterly: 150000,
terms: "Net 30"
}
}
],
reportingPeriod: "2024-Q1",
generateJournalEntries: true,
generateDisclosures: true
})
// ASC 606 Analysis Output:
//
// Step 1: Contract Identified ✓
// Step 2: Performance Obligations Identified (3)
// Step 3: Transaction Price Determined: $1,200,000
// Step 4: Price Allocation:
// - Software License: $800,000 (66.7%)
// - Implementation: $200,000 (16.7%)
// - Support: $200,000 (16.7%)
// Step 5: Revenue Recognition Q1:
// - Software License: $800,000 (Jan 1)
// - Implementation: $150,000 (75% × $200,000)
// - Support: $50,000 (3/12 × $200,000)
// - Total Q1 Revenue: $1,000,000
//
// Journal Entries:
// Dr. Cash $600,000
// Dr. Accounts Receivable $150,000
// Cr. Revenue $1,000,000
// Cr. Contract Liability ($250,000)
//
// Remaining Contract Liability: $200,000
```
### Chapter 16: SOX Controls Testing
**Section 404 Compliance Testing**
```typescript
compliance_sox_controls_test({
controlPeriod: "2024-Q1",
controls: [
{
controlId: "REV-001",
controlName: "Revenue Authorization",
controlType: "preventive",
frequency: "each_occurrence",
keyControl: true,
testParameters: {
populationSize: 2500, // Total transactions
riskRating: "high",
confidenceLevel: 0.95,
tolerableErrorRate: 0.05
}
},
{
controlId: "FIN-001",
controlName: "Journal Entry Review",
controlType: "detective",
frequency: "monthly",
keyControl: true,
testParameters: {
populationSize: 450,
riskRating: "medium",
confidenceLevel: 0.90
}
}
],
testResults: [
{
controlId: "REV-001",
sampleSize: 59, // Statistically calculated
exceptionsFound: 1,
rootCause: "Missing approval for contract modification"
},
{
controlId: "FIN-001",
sampleSize: 25,
exceptionsFound: 0
}
],
generateTestingReport: true,
generateManagementAssertion: true
})
// SOX Testing Results:
//
// Control REV-001 (Revenue Authorization):
// - Statistical Sample Size: 59 (based on high risk)
// - Exceptions: 1 (1.7% error rate)
// - Conclusion: Control Operating Effectively
// - Extrapolated Error: $425,000 (not material)
//
// Control FIN-001 (Journal Entry Review):
// - Statistical Sample Size: 25
// - Exceptions: 0 (0% error rate)
// - Conclusion: Control Operating Effectively
//
// Overall Assessment:
// - Controls Operating Effectively
// - No Material Weaknesses Identified
// - No Significant Deficiencies Identified
//
// Management Assertion: Ready for External Audit
```
### Chapter 17: Audit Preparation
**Year-End Audit Readiness**
```typescript
compliance_audit_preparation({
auditPeriod: "2024-FY",
materialityThreshold: 500000,
areas: [
{
area: "Revenue",
assertions: ["existence", "completeness", "accuracy", "cutoff"],
riskAssessment: "high",
plannedProcedures: [
"Confirm balances with top 20 customers",
"Vouch sales to shipping documents",
"Test cutoff 5 days before/after year-end"
]
},
{
area: "Inventory",
assertions: ["existence", "valuation"],
riskAssessment: "medium",
plannedProcedures: [
"Observe physical inventory count",
"Test inventory pricing",
"Review obsolescence reserve"
]
}
],
preparePBC: true, // Prepared by Client list
generateLeadsheets: true
})
// Audit Preparation Package:
//
// PBC List Generated:
// 1. Trial Balance (all accounts)
// 2. Revenue detail by customer
// 3. A/R aging schedule
// 4. Inventory detail by SKU
// 5. Fixed asset rollforward
// 6. Debt agreements and compliance certs
//
// Lead Sheets Created:
// - Revenue leadsheet with analytics
// - A/R confirmation control sheet
// - Inventory rollforward with variances
//
// Risk Areas Identified:
// - Revenue recognition for multi-element arrangements
// - Inventory obsolescence reserve adequacy
// - Related party transaction disclosures
//
// Estimated Audit Timeline: 3 weeks
// Ready for Auditor Walkthrough: Yes
```
---
## Part VI: Property Management Suite
### Chapter 18: Lease Management
**Lease Expiration Analysis**
```typescript
property_lease_expiration_analysis({
leases: [
{
leaseId: "LEASE-001",
propertyId: "BLDG-A",
tenantName: "Tech Startup Inc",
leaseStartDate: "2021-01-01",
leaseEndDate: "2024-12-31",
currentRent: 25000, // Monthly
leaseType: "office",
squareFeet: 5000,
renewalOptions: [{
optionPeriod: 36, // months
renewalRate: 27000
}]
},
{
leaseId: "LEASE-002",
propertyId: "BLDG-A",
tenantName: "Law Firm LLP",
leaseStartDate: "2022-06-01",
leaseEndDate: "2025-05-31",
currentRent: 35000,
leaseType: "office",
squareFeet: 7000
}
],
timeframe: "next_12_months",
marketData: {
averageRentPerSqFt: 65,
vacancyRate: 0.08,
monthsToLease: 4
}
})
// Lease Expiration Analysis:
//
// LEASE-001 (Tech Startup Inc):
// - Expires in: 9 months
// - Current Rent: $25,000/month ($60/sqft annually)
// - Market Rent: $27,083/month ($65/sqft)
// - Below Market By: 7.7%
// - Renewal Probability: 82%
// - Strategy: INCREASE TO MARKET
// - Recommended Action: Begin renewal discussions in 60 days
// - Proposed Rent: $26,500 (6% increase)
//
// Financial Impact Summary:
// - Total Expiring Annual Rent: $300,000
// - Revenue at Risk: $54,000 (if vacant 4 months)
// - Potential Rent Increase: $18,000/year
// - NPV of Renewal (3 years): $895,000
```
### Chapter 19: Property Investment Analysis
**Commercial Property ROI Analysis**
```typescript
property_investment_analysis({
property: {
propertyId: "123-MAIN",
propertyName: "Main Street Office Building",
acquisitionPrice: 15000000,
acquisitionDate: "2020-01-15",
currentMarketValue: 17500000,
propertyType: "office",
squareFeet: 50000,
units: 25,
financingDetails: {
loanAmount: 10000000,
interestRate: 0.045,
loanTermYears: 25,
monthlyPayment: 55581,
remainingBalance: 8900000
},
operatingMetrics: {
grossRentalIncome: 2400000, // Annual
operatingExpenses: 960000,
netOperatingIncome: 1440000,
occupancyRate: 0.92
}
}
})
// Investment Analysis Results:
//
// Return Metrics:
// - Current Cap Rate: 8.23%
// - Cash-on-Cash Return: 10.45%
// - Total Return (including appreciation): 23.4%
// - IRR Since Acquisition: 18.7%
// - Monthly Cash Flow: $71,251
//
// Current Position:
// - Market Value: $17,500,000
// - Outstanding Debt: $8,900,000
// - Current Equity: $8,600,000
// - Equity Growth: $3,600,000 (72% increase)
//
// Risk Assessment:
// - LTV Ratio: 50.9% (Conservative)
// - DSCR: 2.16x (Strong coverage)
// - Market Risk: LOW
// - Break-even Occupancy: 57%
//
// 5-Year Projection:
// - Year 1 Cash Flow: $855,000
// - Year 5 Cash Flow: $1,023,000
// - Terminal Value: $20,500,000
// - 5-Year IRR: 15.2%
//
// Recommendation: HOLD - Strong cash flow with appreciation potential
```
### Chapter 20: Maintenance Management
**Predictive Maintenance Analysis**
```typescript
property_maintenance_analysis({
propertyId: "PORTFOLIO",
maintenanceRequests: [
{
requestId: "MNT-001",
propertyId: "BLDG-A",
requestType: "emergency",
category: "hvac",
description: "AC unit failure - Suite 300",
requestDate: "2024-01-15",
priorityLevel: 1,
estimatedCost: 5000,
actualCost: 5500,
status: "completed",
completedDate: "2024-01-16",
assignedVendor: "CoolAir HVAC",
tenantSatisfactionRating: 4
},
// ... additional maintenance records
],
propertyUnits: 50,
propertySquareFeet: 100000
})
// Maintenance Analysis Output:
//
// Performance Metrics:
// - Total Requests YTD: 247
// - Avg Response Time: 4.2 hours
// - Avg Completion Time: 18.5 hours
// - Cost Per Unit: $1,847/year
// - Cost Per Sq Ft: $0.92/year
// - Tenant Satisfaction: 4.3/5.0
//
// Category Breakdown:
// - HVAC: 35% of requests, $125,000 total
// - Plumbing: 28% of requests, $89,000 total
// - Electrical: 20% of requests, $64,000 total
//
// Predictive Insights:
// - HVAC systems showing increased failure rate
// - Recommended: Schedule preventive maintenance Q2
// - Estimated savings from prevention: $45,000
//
// Seasonal Trends:
// - Peak activity: July-August (HVAC)
// - Low activity: April-May
// - Budget allocation should reflect seasonality
```
---
## Part VII: Professional Reporting
### Chapter 21: Intelligent Chart Generation
**Executive Chart Package**
```typescript
chart_create_financial_charts({
chartType: "executive_dashboard",
data: {
revenue: {
actual: [10, 11, 12, 13, 14, 15], // Millions
budget: [10, 10.5, 11, 11.5, 12, 12.5],
months: ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
},
profitability: {
grossMargin: [0.42, 0.43, 0.44, 0.43, 0.45, 0.46],
ebitdaMargin: [0.18, 0.19, 0.20, 0.19, 0.21, 0.22],
netMargin: [0.12, 0.13, 0.14, 0.13, 0.15, 0.16]
},
kpis: {
cashPosition: 5200000,
dso: 45, // Days Sales Outstanding
currentRatio: 2.1,
quickRatio: 1.8
}
},
intelligentInsights: true,
professionalFormatting: true
})
// Generated Executive Dashboard:
//
// Chart 1: Revenue Performance
// - Waterfall chart showing variance to budget
// - Trend line with R² = 0.94
// - AI Insight: "Revenue growing 5% above budget, driven by new customer acquisition"
//
// Chart 2: Margin Analysis
// - Multi-line chart with benchmark overlay
// - Shaded areas for industry quartiles
// - AI Insight: "Margins expanding due to operational leverage, approaching top quartile"
//
// Chart 3: Cash & Liquidity
// - Gauge charts for key ratios
// - Traffic light indicators
// - AI Insight: "Strong liquidity position, DSO improvement needed"
//
// Formatting Applied:
// - Corporate color scheme
// - Executive annotations
// - Board-ready legends and labels
```
### Chapter 22: Board Presentations
**Quarterly Board Package**
```typescript
reporting_generate_board_package({
period: "2024-Q1",
sections: [
{
title: "Financial Performance",
content: "financial_statements",
comparePeriods: ["2024-Q1", "2023-Q4", "2023-Q1"]
},
{
title: "Strategic Initiatives",
content: "project_status",
projects: ["Digital Transformation", "Market Expansion", "Cost Optimization"]
},
{
title: "Risk Assessment",
content: "risk_dashboard",
includeMonteCarloAnalysis: true
},
{
title: "Forward Outlook",
content: "forecast",
scenarios: ["base", "upside", "downside"]
}
],
executiveSummary: true,
generatePDF: true
})
// Board Package Contents:
//
// Executive Summary (1 page):
// - Q1 revenue up 15% YoY to $35M
// - EBITDA margin expanded 200bps to 22%
// - Successfully launched Product 2.0
// - Guidance raised for full year
//
// Financial Performance (3 pages):
// - P&L with variance analysis
// - Balance sheet with ratio trends
// - Cash flow and liquidity analysis
// - Segment performance breakdown
//
// Strategic Initiatives (2 pages):
// - Digital: 75% complete, on budget
// - Market: Entered 3 new markets, pipeline strong
// - Cost: Achieved $2M savings, tracking to $8M annual
//
// Risk Assessment (2 pages):
// - Monte Carlo shows 85% probability of meeting guidance
// - Key risks: Supply chain, talent retention
// - Mitigation strategies in place
//
// Forward Outlook (2 pages):
// - Base case: 18% growth, 23% EBITDA margin
// - Probability-weighted outcome: $152M revenue
// - Capital allocation priorities defined
```
---
## Quick Reference Guide
### Essential Formulas & Calculations
```typescript
// Quick NPV Check
NPV = Σ(Cash Flow_t / (1 + r)^t) - Initial Investment
// Quick IRR Estimate (for 3-year project)
IRR ≈ (Total Cash Flow / Initial Investment)^(1/3) - 1
// Cap Rate
Cap Rate = Net Operating Income / Property Value
// DSCR (Debt Service Coverage Ratio)
DSCR = Net Operating Income / Annual Debt Service
// Current Ratio
Current Ratio = Current Assets / Current Liabilities
// Quick Ratio
Quick Ratio = (Current Assets - Inventory) / Current Liabilities
```
### Common Tool Patterns
```typescript
// Standard Financial Analysis Pattern
1. Load historical data
2. Calculate key metrics
3. Generate projections
4. Run sensitivity analysis
5. Create visualizations
6. Export to Excel
// Compliance Workflow
1. Identify requirements
2. Map controls
3. Test controls
4. Document exceptions
5. Generate reports
6. Prepare for audit
// Investment Analysis Flow
1. Gather property/investment data
2. Calculate returns (IRR, NPV, Payback)
3. Assess risks
4. Run scenarios
5. Generate recommendation
6. Create board presentation
```
### Troubleshooting Guide
| Issue | Solution |
|-------|----------|
| Excel file not generating | Check file path permissions and ensure generateExcel: true |
| Monte Carlo taking too long | Reduce iterations to 1000 for testing, then scale up |
| Memory errors with large datasets | Process in batches of 10000 rows |
| Formula errors in calculations | Verify all inputs are numbers, not strings |
| Chart not displaying correctly | Ensure data arrays have matching lengths |
### Best Practices for Accountants
1. **Always validate inputs** - Use sample data first before full datasets
2. **Document assumptions** - Include notes in all projections
3. **Version control models** - Save iterations with timestamps
4. **Cross-check calculations** - Verify against manual calculations
5. **Maintain audit trails** - Keep source documentation linked
6. **Use appropriate precision** - Financial: 2 decimals, Ratios: 3-4 decimals
7. **Follow GAAP/IFRS** - Ensure compliance with accounting standards
8. **Regular reconciliation** - Monthly tie-outs to GL
9. **Sensitivity test everything** - ±10% minimum on key variables
10. **Peer review complex models** - Four-eyes principle for valuations
---
## Appendix: Complete Tool Reference
### Financial Tools (30+)
- `financial_calculate_npv` - Net Present Value
- `financial_calculate_irr` - Internal Rate of Return
- `financial_calculate_loan_payment` - Loan calculations
- `financial_calculate_loan_amortization` - Full schedules
- `financial_depreciation_schedule` - MACRS, straight-line
- `financial_break_even_analysis` - Volume and pricing
- `financial_ratio_analysis` - Complete ratio suite
### Analytics Tools (15+)
- `analytics_monte_carlo_simulation` - Risk simulation
- `analytics_dcf_valuation` - Company valuation
- `analytics_sensitivity_analysis` - What-if analysis
- `analytics_13_week_cash_flow` - Short-term forecasting
- `analytics_52_week_forecast` - Annual projections
- `analytics_executive_dashboard` - KPI tracking
- `analytics_scenario_comparison` - Multiple scenarios
### Compliance Tools (10+)
- `compliance_asc606_revenue_recognition` - Revenue compliance
- `compliance_sox_controls_test` - SOX testing
- `compliance_audit_preparation` - Audit readiness
- `tax_calculate_corporate` - Tax planning
- `tax_calculate_estimated` - Quarterly estimates
### Property Tools (6+)
- `property_lease_expiration_analysis` - Lease management
- `property_investment_analysis` - ROI analysis
- `property_maintenance_analysis` - Maintenance optimization
- `property_capex_analysis` - Capital planning
- `rental_calculate_noi` - NOI calculations
- `rental_project_cash_flow` - Property cash flows
### Reporting Tools (12+)
- `reporting_generate_financial_statements` - Full statements
- `reporting_create_management_report` - Management packages
- `reporting_variance_analysis` - Budget vs actual
- `chart_create_financial_charts` - Intelligent charts
- `excel_create_workbook` - Excel generation
- `excel_update_worksheet` - Excel manipulation
---
**End of Documentation**
*Version 2.0.0 | Last Updated: 2024*
*© Excel Finance MCP Server - Enterprise Edition*