综合案例实战：XYZ公司全流程闻嗅复核

"""
金融风险管理 - q094
综合案例实战：XYZ公司全流程闻嗅复核综合判断
"""

metadata = {
    "id": "q094",
    "title": "综合案例实战：XYZ公司全流程闻嗅复核",
    "module": "综合案例实战",
    "difficulty": "beginner",
    "data_files": ["case32_xyz_profit.csv"],
    "skills": ["全流程闻嗅复核", "综合判断", "信贷建议"],
    "estimated_minutes": 15
}

"""
【题目】
读取case32_xyz_profit.csv，对XYZ公司2018年全流程闻嗅复核：

1. 计算2018H2月均收入 vs 2017FY月均收入之比（期望: 接近1.0）
2. 计算2018H2毛利率 vs 2017FY毛利率之差（期望: <3pp）
3. 计算销售费用增长率 vs 收入增长率之差（期望: <10pp）
4. 计算2018H2营业利润率 vs 2017FY营业利润率之差（期望: <5pp）

汇总四项检查结果，输出字符串报告（格式化）：
=== XYZ公司2018年闻嗅复核报告 ===
月均收入比率: {x}（期望0.95-1.05，{正常/异常}）
毛利率差异: {x}pp（期望<3pp，{正常/异常}）
费用配比背离: {x}pp（期望<10pp，{正常/异常}）
营业利润率差异: {x}pp（期望<5pp，{正常/异常}）
发现异常数量: {n}项
信贷建议: {建议}
"""

def solve():
    import pandas as pd
    from pyodide.http import open_url
    from io import StringIO

    BASE_URL = "https://data.zuihe.com/finance/"
    df = pd.read_csv(StringIO(open_url(BASE_URL + "case32_xyz_profit.csv").read()))

    h1 = df[df['period'] == '2018H1'].iloc[0]
    fy18 = df[df['period'] == '2018FY'].iloc[0]
    fy17 = df[df['period'] == '2017FY'].iloc[0]

    h2_rev = fy18['revenue'] - h1['revenue']
    h2_cost = fy18['cost'] - h1['cost']
    h2_sell = fy18['selling_expense'] - h1['selling_expense']
    h2_op = fy18['operating_profit'] - h1['operating_profit']

    monthly_ratio = round((h2_rev / 6) / (fy17['revenue'] / 12), 4)
    gm_h2 = (h2_rev - h2_cost) / h2_rev
    gm_17 = (fy17['revenue'] - fy17['cost']) / fy17['revenue']
    gm_diff_pp = round((gm_h2 - gm_17) * 100, 2)
    rev_growth = (fy18['revenue'] - fy17['revenue']) / fy17['revenue']
    sell_growth = (fy18['selling_expense'] - fy17['selling_expense']) / fy17['selling_expense']
    fee_deviation_pp = round((sell_growth - rev_growth) * 100, 2)
    op_h2 = h2_op / h2_rev
    op_17 = fy17['operating_profit'] / fy17['revenue']
    op_diff_pp = round((op_h2 - op_17) * 100, 2)

    ab1 = not (0.95 <= monthly_ratio <= 1.05)
    ab2 = abs(gm_diff_pp) > 3
    ab3 = abs(fee_deviation_pp) > 10
    ab4 = abs(op_diff_pp) > 5
    n_issues = sum([ab1, ab2, ab3, ab4])
    advice = '建议深入调查，补充说明材料' if n_issues >= 2 else '建议正常放款'

    lines = [
        "=== XYZ公司2018年闻嗅复核报告 ===",
        f"月均收入比率: {monthly_ratio}（期望0.95-1.05，{'异常' if ab1 else '正常'}）",
        f"毛利率差异: {gm_diff_pp}pp（期望<3pp，{'异常' if ab2 else '正常'}）",
        f"费用配比背离: {fee_deviation_pp}pp（期望<10pp，{'异常' if ab3 else '正常'}）",
        f"营业利润率差异: {op_diff_pp}pp（期望<5pp，{'异常' if ab4 else '正常'}）",
        f"发现异常数量: {n_issues}项",
        f"信贷建议: {advice}",
    ]
    return '
'.join(lines)

if __name__ == "__main__":
    print(solve())