SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillschina-xlsx-author

china-xlsx-author

Office & productivity

View repository

Updated 10-15 23:43

About this skill

china-xlsx-author

Purpose

Create professional A-share Financial Analysis Excel Model — structured workbooks for Chinese equity analysis.

Data Sources

Primary: iFind MCP (Tier-1 Paid) / AkShare MCP (Tier-2 Free Alternative)

get_financials(ticker, "income")     → Income statement data
get_financials(ticker, "balance")    → Balance sheet data
get_financials(ticker, "cashflow")   → Cash flow data
get_quote(ticker)                    → Market data

Secondary Sources

  • Cninfo — source filings
  • Brokerage research reports — template references

Workflow

Step 1: Workbook Structure

Standard A-share model structure:

Sheet Content
Cover Company, date, version, disclaimer
Assumptions All model inputs, drivers
Income Statement Historical + forecast P&L
Balance Sheet Historical + forecast BS
Cash Flow Statement Historical + forecast CF
Working Capital WC analysis, assumptions
Valuation DCF, comps, sensitivity
Charts Key visuals
Checks Sum checks, balances

Step 2: Formatting Standards

Chinese financial model formatting:

Element Format
Headers Bold, background color
Inputs Blue font, light blue background
Calculations Black font, no background
Hardcodes (to avoid) Red font
Negative numbers Red font or (XXX)
Percentage % format, 1 decimal
Currency ¥ or 10,000 yuan
Dates YYYY-MM-DD or YYYY年MM月

Color coding:

蓝色 = 输入 (Inputs)
黑色 = 公式 (Calculations)
红色 = 警告 (Warnings / hardcodes)
绿色 = 链接 (Links)
灰色 = 标签 (Labels)

Step 3: Income Statement Layout

Standard P&L format (CAS):

Item FY2021 FY2022 FY2023 FY2024E FY2025E
Operating Revenue
Less: Operating Costs
= Gross Profit
Gross Margin
Less: Taxes and Surcharges
Less: Selling Expenses
Less: Administrative Expenses
Less: R&D Expenses
Less: Financial Expenses
Add: Investment Income
Add: Changes in Fair Value
Less: Credit Impairment Loss
Less: Asset Impairment Loss
Add: Gains from Asset Disposal
= Operating Profit
Add: Non-operating Income
Less: Non-operating Expenses
= Total Profit
Less: Income Tax Expense
= Net Profit
Of which: Net Profit Attributable to Parent
Of which: Net Profit Excluding Non-recurring Items
EPS (yuan/share)

Step 4: Balance Sheet Layout

Standard BS format (CAS):

Item FY2021 FY2022 FY2023 FY2024E FY2025E
Assets
Cash and Cash Equivalents
Trading Financial Assets
Notes Receivable
Accounts Receivable
Prepayments
Inventories
Other Current Assets
Total Current Assets
Long-term Equity Investments
Fixed Assets
Construction in Progress
Intangible Assets
Goodwill
Other Non-current Assets
Total Non-current Assets
Total Assets
Liabilities
Short-term Borrowings
Notes Payable
Accounts Payable
Contract Liabilities
Employee Benefits Payable
Taxes Payable
Other Payables
Total Current Liabilities
Long-term Borrowings
Bonds Payable
Provisions
Total Non-current Liabilities
Total Liabilities
Owners' Equity
Share Capital
Capital Reserve
Surplus Reserve
Undistributed Profits
Equity Attributable to Parent
Minority Interests
Total Owners' Equity
Total Liabilities and Equity

Step 5: Assumptions Sheet

Key assumptions to centralize:

Category Assumption Value Source
Revenue growth FY2024E X%
Revenue growth FY2025E X%
Gross margin FY2024E X%
SG&A % of revenue FY2024E X%
Tax rate Effective X%
D&A % of PPE X%
CapEx % of revenue X%
NWC % of revenue X%
Terminal growth X%
WACC X%

Step 6: Valuation Sheet

DCF layout:

Item Value Notes
Enterprise value ¥XX billion
Less: Net debt ¥XX billion
Equity value ¥XX billion
Shares outstanding XX billion shares
Value per share ¥XX
Current price ¥XX
Upside/(Downside) X%

Football field:

Method Low Mid High
P/E
P/B
EV/EBITDA
DCF
Range ¥XX ¥XX ¥XX

Step 7: Checks Sheet

Essential checks:

Check Formula Target Result
BS balances Assets - L - E 0
CF ties Ending cash - Beg cash - Net CF 0
Revenue growth (Rev - Rev_prev) / Rev_prev Reasonable
Margin check GP / Revenue Reasonable
Debt schedule ST + LT debt = Total
Retained earnings RE beg + NI - Div = RE end
Depreciation D&A / PPE Reasonable

Step 8: Charts & Presentation

Key charts to include:

Chart Purpose
Revenue & profit bridge Historical + forecast
Margin trends Gross, operating, net
Valuation multiples Historical range
DCF sensitivity Tornado chart
Peer comparison Comps scatter plot

China-Specific Excel Conventions

Unit Standards

Unit Usage
yuan Per-share items
10,000 yuan Most financial items
100 million yuan Large totals, market cap

Naming Conventions

Item Convention
Sheet names Short, Chinese preferred
Cell references Named ranges for key cells
File naming [Ticker][Company][Date]_v[X]

Formula Conventions

Convention Example
Sheet references 'Income Statement'!B10
Named ranges Revenue, WACC, Shares
Chinese function names SUM, IF, VLOOKUP

Quality Checks

Before finalizing:

  • All sheets present and linked
  • No hardcodes in calculation cells
  • Historicals cross-checked
  • CAS conventions applied
  • BS balances and CF ties
  • Valuation reasonable
  • Charts update automatically
  • All checks green
  • Documentation complete

Data Source Mode Switch: Set env var IFIND_DATA_SOURCE_MODE to control data source preference.

  • ifind-only (strict): Use iFind only, error if unavailable
  • ifind-fallback (default): iFind preferred, fallback to AkShare
  • akshare-only, wind-only (Wind only), wind-fallback (Wind first, fallback to iFind → AkShare): Skip iFind, use AkShare only