python
workers=79; eng=4; mgr=4
w=654; e=1020; m=1657
monthly_usd=workers*w+eng*e+mgr*m
monthly_usd


62374

python
# input data
units_per_month = 50000
usd_to_cny = 7

# wages per band (USD monthly)
wages = {
    'CN': {'Worker':654, 'Engineer':1020, 'Manager':1657, 'oncost':0.35},
    'VN': {'Worker':302, 'Engineer':564, 'Manager':1146, 'oncost':0.22},
    'TH': {'Worker':437, 'Engineer':781, 'Manager':1621, 'oncost':0.05}
}

# headcounts per role (from image)
roles = ['Operators','Technicians','QA','Lab','Warehouse','Admin','Maintenance','Engineers','Supervisors','Management']
hc_CN = {'Operators':55,'Technicians':2,'QA':6,'Lab':2,'Warehouse':6,'Admin':6,'Maintenance':2,'Engineers':4,'Supervisors':3,'Management':1}
hc_VN = {'Operators':65,'Technicians':3,'QA':8,'Lab':2,'Warehouse':8,'

((1.289034, 9.023238),
 (0.6558231999999999, 4.590762399999999),
 (0.8167530000000001, 5.717271),
 19753.2,
 10555.44,
 12841.5)

python
usd_to_cny=7
sale_price_rmb=352.52
bom_usd=17.95
bom_rmb = bom_usd * usd_to_cny
inbound_pct = 0.015
material_duty_rate = 0.0  # assuming EPE/BOI exemption

def country_calc(country):
    dl_usd, dl_rmb = compute_direct(country)
    # manufacturing expense scaled
    cn_over_m = cn_over
    over_m = compute_overhead_index(country)
    man_exp_rmb = 8.38 * (over_m / cn_over_m)
    materials_rmb = bom_rmb * (1 + inbound_pct + material_duty_rate)
    gross_margin = sale_price_rmb - materials_rmb - dl_rmb - man_exp_rmb
    # Tariffs to destination
    base_tariff_rate = 0.125
    extra_rate = 0.0 # only for CN; for VN/TH zero
    tariff_rmb = sale_price_rmb * (base_tariff_rate + extra_rate)
    freight_rmb = 28.2016
    profit_rmb = gross_margin - tariff_rmb - freight_rmb
    return {
        'DirectLabor_RMB': dl_rmb,
        'ManufExp_RMB': man_exp_rmb,
        'Materials_RMB': materials_rmb,
        'GrossMargin_RMB': gross_margin,
        'Tariff_RMB': tariff_rmb,
        'Freight_RMB': freight_rmb,
        'Profit_RMB': profit_rmb
    }

vn = country_calc('VN')
th = country_calc('TH')
vn, th


({'DirectLabor_RMB': 4.590762399999999,
  'ManufExp_RMB': 4.477987728570562,
  'Materials_RMB': 127.53474999999997,
  'GrossMargin_RMB': 215.91649987142947,
  'Tariff_RMB': 44.065,
  'Freight_RMB': 28.2016,
  'Profit_RMB': 143.64989987142945},
 {'DirectLabor_RMB': 5.717271,
  'ManufExp_RMB': 5.447814531316445,
  'Materials_RMB': 127.53474999999997,
  'GrossMargin_RMB': 213.82016446868354,
  'Tariff_RMB': 44.065,
  'Freight_RMB': 28.2016,
  'Profit_RMB': 141.55356446868353})

python
# Build an Excel model comparing Vietnam & Thailand unit economics for PD36
# using the 5 images' data and documented assumptions.
import pandas as pd
import numpy as np

from io import BytesIO

# -------------------- Inputs from the images --------------------
units_per_month = 50000
usd_cny = 7
sale_price_rmb = 352.52
bom_usd = 17.95  # PD36 materials cost (USD) from image 3
bom_rmb = bom_usd * usd_cny
cn_mfg_expense_per_unit_rmb = 8.38  # from image 5

# Headcount by role
roles = ["Operators","Technicians","QA","Lab","Warehouse","Admin","Maintenance","Engineers","Supervisors","Management"]
hc_CN = [55,2,6,2,6,6,2,4,3,1]
hc_VN = [65,3,8,2,8,6,3,4,3,1]
hc_TH = [65,3,8,2,8,6,3,4,3,1]

# Band mapping
bands = {
    "Operators":"Worker",
    "Technicians":"Worker",
    "QA":"Worker",
    "Lab":"Worker",
    "Warehouse":"Worker",
    "Admin":"Worker",
    "Maintenance":"Worker",
    "Engineers":"Engineer",
    "Supervisors":"Manager",
    "Management":"Manager",
}

# Monthly median wages (USD) from the first image (JETRO-derived)
band_wages = pd.DataFrame({
    "Band":["Worker","Engineer","Manager"],
    "CN_USD":[654,1020,1657],
    "VN_USD":[302,564,1146],
    "TH_USD":[437,781,1621],
    "Oncost":[0.35,0.35,0.35]  # placeholder so we can show, but country oncosts stored below
})
# Country oncost rates from the first image
oncost = {"CN":0.35,"VN":0.22,"TH":0.05}

# -------------------- Build the workbook --------------------
path = "/mnt/data/PD36_VN_TH_cost_compare.xlsx"
with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
    # Inputs sheet
    inputs_df = pd.DataFrame({
        "Parameter":["Units_per_month","USD_CNY","Sale_price_RMB","BOM_USD","BOM_RMB","Inbound_pct_on_materials","Material_import_duty","Base_tariff_rate","Extra_tariff_rate_China","Outbound_freight_RMB"],
        "Value":[units_per_month,usd_cny,sale_price_rmb,bom_usd,bom_rmb,0.015,0.00,0.125,0.20,28.2016],
        "Note":[
            "From image 1","From image 4","From image 5 (RMB)","From image 3",
            "=B4*B2","Assumption: short-sea freight/insurance ~1.5% (editable)",
            "Assumption: 0% if EPE/BOI; edit if not eligible",
            "Use same base tariff as Shenzhen table (12.5%)",
            "Only applies to China (Section 301); Vietnam/Thailand = 0%",
            "From image 5; override if your lane differs"
        ]
    })
    inputs_df.to_excel(writer, index=False, sheet_name="Inputs")
    ws_inputs = writer.sheets["Inputs"]
    ws_inputs.write_formula(4,1,"=B4*B2")  # compute BOM_RMB
    
    # Wages sheet
    band_wages.to_excel(writer, index=False, sheet_name="Wages")
    ws_wages = writer.sheets["Wages"]
    # Oncost by country block
    oncost_df = pd.DataFrame({"Country":["CN","VN","TH"],"Oncost":[oncost["CN"],oncost["VN"],oncost["TH"]]})
    oncost_df.to_excel(writer, index=False, sheet_name="Wages", startrow=len(band_wages)+2)
    
    # Headcount & role-band map
    hc_df = pd.DataFrame({
        "Role":roles,
        "Band":[bands[r] for r in roles],
        "CN":[*hc_CN],
        "VN":[*hc_VN],
        "TH":[*hc_TH]
    })
    hc_df.to_excel(writer, index=False, sheet_name="Headcount")
    ws_hc = writer.sheets["Headcount"]
    
    # Calcs sheet
    calc_cols = ["Country","Materials_RMB","Direct_labor_RMB","Mfg_expense_RMB","Gross_margin_RMB","Tariff_RMB","Freight_RMB","Profit_RMB"]
    calcs_df = pd.DataFrame({"Country":["Vietnam","Thailand"]})
    calcs_df.to_excel(writer, index=False, sheet_name="Calcs")
    ws = writer.sheets["Calcs"]
    
    # Named ranges (for readability in formulas)
    wb = writer.book
    # Inputs named ranges
    ws_inputs.name = "Inputs"
    ws_wages.name = "Wages"
    ws_hc.name = "Headcount"
    # Create names for key input cells
    wb.define_name("Units","=Inputs!$B$1")
    wb.define_name("USD_CNY","=Inputs!$B$2")
    wb.define_name("SalePrice","=Inputs!$B$3")
    wb.define_name("BOM_USD","=Inputs!$B$4")
    wb.define_name("BOM_RMB","=Inputs!$B$5")
    wb.define_name("InboundPct","=Inputs!$B$6")
    wb.define_name("MatDuty","=Inputs!$B$7")
    wb.define_name("BaseTariff","=Inputs!$B$8")
    wb.define_name("ChinaExtraTariff","=Inputs!$B$9")
    wb.define_name("Freight","=Inputs!$B$10")
    wb.define_name("CN_MfgExpense","="+str(cn_mfg_expense_per_unit_rmb))
    
    # Build helper areas on Calcs for wage per role per country and oncost per country
    # Put band wages (USD) for each country to Calcs
    ws.write_row(0, 9, ["Helper: wagesUSD by band (CN/VN/TH)"])
    ws.write_row(1, 9, ["Band","CN","VN","TH"])
    ws.write_row(2, 9, ["Worker","=Wages!$B$2","=Wages!$C$2","=Wages!$D$2"])
    ws.write_row(3, 9, ["Engineer","=Wages!$B$3","=Wages!$C$3","=Wages!$D$3"])
    ws.write_row(4, 9, ["Manager","=Wages!$B$4","=Wages!$C$4","=Wages!$D$4"])
    wb.define_name("WageCN","=Calcs!$K$3:$K$5")
    wb.define_name("WageVN","=Calcs!$L$3:$L$5")
    wb.define_name("WageTH","=Calcs!$M$3:$M$5")
    wb.define_name("BandList","=Calcs!$K$3:$K$5")  # list of band names
    
    # Oncost named values
    ws.write_row(6, 9, ["Helper: Oncost by country"])
    ws.write_row(7, 9, ["CN","VN","TH"])
    ws.write_row(8, 9, ["=Wages!$B$7","=Wages!$C$7","=Wages!$D$7"])
    wb.define_name("OncostCN","=Calcs!$K$9")
    wb.define_name("OncostVN","=Calcs!$L$9")
    wb.define_name("OncostTH","=Calcs!$M$9")
    
    # Role list with band mapping copied to Calcs for SUMPRODUCT
    ws.write_row(10,9,["Role","Band","HC_CN","HC_VN","HC_TH"])
    for i, r in enumerate(roles):
        ws.write(11+i,9,r)
        ws.write(11+i,10,bands[r])
        ws.write_formula(11+i,11,f"=Headcount!$C${2+i}")
        ws.write_formula(11+i,12,f"=Headcount!$D${2+i}")
        ws.write_formula(11+i,13,f"=Headcount!$E${2+i}")
    wb.define_name("RoleBands","=Calcs!$K$12:$K$21")
    wb.define_name("HC_CN","=Calcs!$L$12:$L$21")
    wb.define_name("HC_VN","=Calcs!$M$12:$M$21")
    wb.define_name("HC_TH","=Calcs!$N$12:$N$21")
    
    # A helper range that converts bands to wages (USD) for each country using INDEX/MATCH
    ws.write_row(10,15,["Helper: Wages per Role (USD) CN/VN/TH"])
    ws.write_row(11,15,["CN_roleUSD","VN_roleUSD","TH_roleUSD"])
    for i in range(len(roles)):
        # CN wage per role
        ws.write_formula(12+i,15, f"=INDEX(WageCN, MATCH($K${12+i}, $K$3:$K$5, 0))")
        # VN wage per role
        ws.write_formula(12+i,16, f"=INDEX(WageVN, MATCH($K${12+i}, $K$3:$K$5, 0))")
        # TH wage per role
        ws.write_formula(12+i,17, f"=INDEX(WageTH, MATCH($K${12+i}, $K$3:$K$5, 0))")
    wb.define_name("RoleUSD_CN","=Calcs!$P$12:$P$21")
    wb.define_name("RoleUSD_VN","=Calcs!$Q$12:$Q$21")
    wb.define_name("RoleUSD_TH","=Calcs!$R$12:$R$21")
    
    # Identify direct labor rows (Operators, Technicians, QA, Lab, Warehouse, Maintenance)
    direct_rows = [roles.index(r)+12 for r in ["Operators","Technicians","QA","Lab","Warehouse","Maintenance"]]
    # Create binary selector column to mark direct roles
    ws.write(10,18,"DirectFlag")
    for i in range(len(roles)):
        ws.write(12+i,18,1 if (12+i) in direct_rows else 0)
    wb.define_name("DirectFlag","=Calcs!$S$12:$S$21")
    
    # Build the main table with formulas
    ws.write_row(1,0,calc_cols)  # header
    
    # Row 2 = Vietnam
    row = 2
    ws.write(row,0,"Vietnam")
    # Materials_RMB = BOM_RMB*(1+InboundPct+MatDuty)
    ws.write_formula(row,1,"=BOM_RMB*(1+InboundPct+MatDuty)")
    # Direct labor: = SUMPRODUCT(HC_VN, RoleUSD_VN, DirectFlag) * (1+OncostVN) / Units * USD_CNY
    ws.write_formula(row,2,"=SUMPRODUCT(HC_VN,RoleUSD_VN,DirectFlag)*(1+OncostVN)/Units*USD_CNY")
    # Mfg expense: scale CN's expense by overhead payroll ratio (Admin+Engineers+Supervisors+Management)
    # Overhead selector (1 for Admin/Engineers/Supervisors/Management)
    ws.write(10,19,"OverheadFlag")
    overhead_roles = ["Admin","Engineers","Supervisors","Management"]
    for i in range(len(roles)):
        ws.write(12+i,19,1 if roles[i] in overhead_roles else 0)
    wb.define_name("OverheadFlag","=Calcs!$T$12:$T$21")
    # Monthly overhead payroll (USD) per country
    ws.write(10,20,"OverheadMonthly_USD")
    ws.write_formula(12,20,"=SUMPRODUCT(HC_CN,RoleUSD_CN,OverheadFlag)*(1+OncostCN)")
    ws.write_formula(13,20,"=SUMPRODUCT(HC_VN,RoleUSD_VN,OverheadFlag)*(1+OncostVN)")
    ws.write_formula(14,20,"=SUMPRODUCT(HC_TH,RoleUSD_TH,OverheadFlag)*(1+OncostTH)")
    wb.define_name("OH_CN","=Calcs!$U$12")
    wb.define_name("OH_VN","=Calcs!$U$13")
    wb.define_name("OH_TH","=Calcs!$U$14")
    # Mfg expense Vietnam
    ws.write_formula(row,3,"=CN_MfgExpense*OH_VN/OH_CN")
    # Gross margin
    ws.write_formula(row,4,"=SalePrice-B{0}-C{0}-D{0}".format(row))
    # Tariff
    ws.write_formula(row,5,"=SalePrice*BaseTariff")
    # Freight
    ws.write_formula(row,6,"=Freight")
    # Profit
    ws.write_formula(row,7,"=E{0}-F{0}-G{0}".format(row))
    
    # Row 3 = Thailand (same formulas but TH ranges)
    row = 3
    ws.write(row,0,"Thailand")
    ws.write_formula(row,1,"=BOM_RMB*(1+InboundPct+MatDuty)")
    ws.write_formula(row,2,"=SUMPRODUCT(HC_TH,RoleUSD_TH,DirectFlag)*(1+OncostTH)/Units*USD_CNY")
    ws.write_formula(row,3,"=CN_MfgExpense*OH_TH/OH_CN")
    ws.write_formula(row,4,"=SalePrice-B{0}-C{0}-D{0}".format(row))
    ws.write_formula(row,5,"=SalePrice*BaseTariff")
    ws.write_formula(row,6,"=Freight")
    ws.write_formula(row,7,"=E{0}-F{0}-G{0}".format(row))
    
    # Format the main table
    money_fmt = wb.add_format({'num_format':'#,##0.00'})
    for r in [2,3]:
        ws.set_row(r-1, None)  # ensure visible
        ws.set_row(r, None, money_fmt)
    for c in range(1,8):
        ws.set_column(c, c, 17, money_fmt)
    ws.set_column(0,0,12)
    
    # Summary sheet (nice view)
    summary = pd.DataFrame({
        "Country":["Vietnam","Thailand"],
        "Sales (RMB)":[sale_price_rmb,sale_price_rmb],
        "Materials incl. inbound (RMB)":[np.nan,np.nan],
        "Direct labor (RMB)":[np.nan,np.nan],
        "Mfg expense (RMB)":[np.nan,np.nan],
        "Gross margin (RMB)":[np.nan,np.nan],
        "Tariff (RMB)":[np.nan,np.nan],
        "Freight (RMB)":[np.nan,np.nan],
        "Profit (RMB)":[np.nan,np.nan]
    })
    summary.to_excel(writer, index=False, sheet_name="Summary")
    ws_sum = writer.sheets["Summary"]
    # Link to Calcs cells so user sees results
    for i in range(2):
        row = 2+i
        ws_sum.write_formula(row,2,f"=Calcs!B{2+i}")
        ws_sum.write_formula(row,3,f"=Calcs!C{2+i}")
        ws_sum.write_formula(row,4,f"=Calcs!D{2+i}")
        ws_sum.write_formula(row,5,f"=Calcs!E{2+i}")
        ws_sum.write_formula(row,6,f"=Calcs!F{2+i}")
        ws_sum.write_formula(row,7,f"=Calcs!G{2+i}")
        ws_sum.write_formula(row,8,f"=Calcs!H{2+i}")
    for c in range(1,9):
        ws_sum.set_column(c, c, 20, money_fmt)
    ws_sum.set_column(0,0,12)

path

'/mnt/data/PD36_VN_TH_cost_compare.xlsx'

python
vn, th


({'DirectLabor_RMB': 4.590762399999999,
  'ManufExp_RMB': 4.477987728570562,
  'Materials_RMB': 127.53474999999997,
  'GrossMargin_RMB': 215.91649987142947,
  'Tariff_RMB': 44.065,
  'Freight_RMB': 28.2016,
  'Profit_RMB': 143.64989987142945},
 {'DirectLabor_RMB': 5.717271,
  'ManufExp_RMB': 5.447814531316445,
  'Materials_RMB': 127.53474999999997,
  'GrossMargin_RMB': 213.82016446868354,
  'Tariff_RMB': 44.065,
  'Freight_RMB': 28.2016,
  'Profit_RMB': 141.55356446868353})

python
def fmt(x): 
    return round(x,2)

summary = {
    'Vietnam': {k: fmt(v) for k,v in vn.items()},
    'Thailand': {k: fmt(v) for k,v in th.items()}
}
summary


{'Vietnam': {'DirectLabor_RMB': 4.59,
  'ManufExp_RMB': 4.48,
  'Materials_RMB': 127.53,
  'GrossMargin_RMB': 215.92,
  'Tariff_RMB': 44.06,
  'Freight_RMB': 28.2,
  'Profit_RMB': 143.65},
 'Thailand': {'DirectLabor_RMB': 5.72,
  'ManufExp_RMB': 5.45,
  'Materials_RMB': 127.53,
  'GrossMargin_RMB': 213.82,
  'Tariff_RMB': 44.06,
  'Freight_RMB': 28.2,
  'Profit_RMB': 141.55}}

python
workers=79; usd_worker=654; oncost=1.35; rate=7
worker_cost_month=workers*usd_worker*oncost*rate
worker_cost_month


488243.70000000007

python
operators=55
op_cost=operators*654*1.35*7
op_cost, op_cost/50000


(339916.5, 6.79833)

python
def direct(op_count, usd_salary, oncost, rate=7, units=50000):
    return op_count*usd_salary*oncost*rate/units
vn=direct(65,302,1.22)
th=direct(65,437,1.05)
cn=direct(55,654,1.35)
vn, th, cn


(3.3528039999999995, 4.175535, 6.79833)

python
ratio_vn=(65*302*1.22)/(55*654*1.35)
ratio_th=(65*437*1.05)/(55*654*1.35)
6.98*ratio_vn, 6.98*ratio_th


(3.4424001070851227, 4.287116733080037)

python
rate=7
def wage(count, salary, oncost): return count*salary*oncost*rate
sz_worker_over=24; sz_eng=4; sz_mgr=4
w1=wage(sz_worker_over,654,1.35)
w2=wage(sz_eng,1020,1.35)
w3=wage(sz_mgr,1657,1.35)
w1,w2,w3,w1+w2+w3


(148327.2, 38556.0, 62634.600000000006, 249517.80000000002)

python
def overhead_total(worker_count, worker_salary, eng_count, eng_salary, mgr_count, mgr_salary, oncost, rate=7):
    return wage(worker_count, worker_salary, oncost)+wage(eng_count, eng_salary, oncost)+wage(mgr_count, mgr_salary, oncost)
vn_over=overhead_total(30,302,4,564,4,1146,1.22)
th_over=overhead_total(30,437,4,781,4,1621,1.05)
sz_over=overhead_total(24,654,4,1020,4,1657,1.35)
vn_over, th_over, sz_over, vn_over/sz_over, th_over/sz_over


(135786.0,
 166977.3,
 249517.80000000002,
 0.5441936406941709,
 0.6691999528690938)

python
8.38*0.5441936406941709, 8.38*0.6691999528690938


(4.560342709017152, 5.607895605043007)

python
55*654*1.35*7.2/50000


6.992568

python
sales=352.52
mat_vn=125.64*1.015
mat_th=mat_vn
direct_cn=6.98
ratio_vn=(65*302*1.22)/(55*654*1.35)
ratio_th=(65*437*1.05)/(55*654*1.35)
direct_vn=direct_cn*ratio_vn
direct_th=direct_cn*ratio_th
over_cn=8.38
over_vn=over_cn*( (30*302*1.22 + 4*564*1.22 + 4*1146*1.22) / (24*654*1.35 + 4*1020*1.35 + 4*1657*1.35) )
over_th=over_cn*( (30*437*1.05 + 4*781*1.05 + 4*1621*1.05) / (24*654*1.35 + 4*1020*1.35 + 4*1657*1.35) )
gross_vn=sales-(mat_vn+direct_vn+over_vn)
gross_th=sales-(mat_th+direct_th+over_th)
tariff_cn=sales*(0.125+0.2)
tariff_vn=sales*0.125
freight=28.2016
profit_vn=gross_vn-tariff_vn-freight
profit_th=sales-(mat_th+direct_th+over_th)-tariff_vn-freight
sales, mat_vn, direct_vn, direct_th, over_vn, over_th, gross_vn, gross_th, tariff_cn, profit_vn, profit_th


(352.52,
 127.52459999999999,
 3.4424001070851227,
 4.287116733080037,
 4.560342709017152,
 5.607895605043008,
 216.9926571838977,
 215.10038766187694,
 114.569,
 144.7260571838977,
 142.83378766187695)

python
import math
def rnd2(x): 
    return round(x+1e-8, 2)
def rnd3(x):
    return round(x+1e-8, 3)
def rnd4(x):
    return round(x+1e-8, 4)
sales=352.52
mat=125.64
dl_cn=6.98
over_cn=8.38
gross_cn=rnd2(sales-(mat+dl_cn+over_cn))
tariff_cn=rnd3(sales*(0.125+0.2))
freight=rnd4(28.2016)
profit_cn=rnd2(gross_cn - tariff_cn - freight)
sales, gross_cn, tariff_cn, freight, profit_cn


(352.52, 211.52, 114.569, 28.2016, 68.75)

python
sales=352.52
mat_vn=round(125.64*1.015,2)
mat_vn


127.52

python
sales=352.52
mat=round(125.64*1.015,2)
dl_vn=round(6.98*((65*302*1.22)/(55*654*1.35)),2)
dl_th=round(6.98*((65*437*1.05)/(55*654*1.35)),2)
over_vn=round(8.38*((30*302*1.22+4*564*1.22+4*1146*1.22)/(24*654*1.35+4*1020*1.35+4*1657*1.35)),2)
over_th=round(8.38*((30*437*1.05+4*781*1.05+4*1621*1.05)/(24*654*1.35+4*1020*1.35+4*1657*1.35)),2)
gross_vn=round(sales-(mat+dl_vn+over_vn),2)
gross_th=round(sales-(mat+dl_th+over_th),2)
tariff_vn=round(sales*0.125,3)
profit_vn=round(gross_vn-tariff_vn-28.2016,2)
profit_th=round(gross_th-tariff_vn-28.2016,2)
mat, dl_vn, over_vn, gross_vn, profit_vn, dl_th, over_th, gross_th, profit_th, tariff_vn


(127.52, 3.44, 4.56, 217.0, 144.73, 4.29, 5.61, 215.1, 142.83, 44.065)

python
# Let's compute wages cost per unit for China given data.
units_per_month = 50000
usd_to_cny = 7.0
oncost = {
    'CN': 0.35,
    'VN': 0.22,
    'TH': 0.05
}
salary = {
    'CN': {'Worker': 654, 'Engineer': 1020, 'Manager': 1657},
    'VN': {'Worker': 302, 'Engineer': 564, 'Manager': 1146},
    'TH': {'Worker': 437, 'Engineer': 781, 'Manager': 1621},
}
# Headcount per role by country (given)
roles = ['Operators','Technicians','QA','Lab','Warehouse','Admin','Maintenance','Engineers','Supervisors','Management']
bands = {'Operators':'Worker','Technicians':'Worker','QA':'Worker','Lab':'Worker','Warehouse':'Worker','Admin':'Worker','Maintenance':'Worker','Engineers':'Engineer','Supervisors':'Manager','Management':'Manager'}
HC = {
    'CN': {'Operators':55,'Technicians':2,'QA':6,'Lab':2,'Warehouse':6,'Admin':6,'Maintenance':2,'Engineers':4,'Supervisors':3,'Management':1},
    'VN': {'Operators':65,'Technicians':3,'QA':8,'Lab':2,'Warehouse':8,'Admin':6,'Maintenance':3,'Engineers':4,'Supervisors':3,'Management':1},
    'TH': {'Operators':65,'Technicians':3,'QA':8,'Lab':2,'Warehouse':8,'Admin':6,'Maintenance':3,'Engineers':4,'Supervisors':3,'Management':1}
}
# calculate direct labor for CN: Operators only
def monthly_cost(country, consider):
    total = 0.0
    for role, count in HC[country].items():
        if consider(role):
            band = bands[role]
            total += count * salary[country][band] * (1+oncost[country]) * usd_to_cny
    return total
cn_direct_monthly = monthly_cost('CN', lambda r: r=='Operators')
cn_direct_per_unit = cn_direct_monthly / units_per_month
cn_direct_per_unit


6.79833

python
def non_direct_roles(role):
    return role!='Operators'
cn_overhead_wages_monthly = monthly_cost('CN', non_direct_roles)
cn_overhead_wages_monthly, cn_overhead_wages_monthly/units_per_month


(249517.80000000005, 4.990356000000001)

python
def calc_overhead_unit(country, other_overhead_factor=1.0):
    overhead_wages_monthly = monthly_cost(country, non_direct_roles)
    other_overhead_monthly = (8.38*units_per_month - monthly_cost('CN', non_direct_roles)) * other_overhead_factor
    return overhead_wages_monthly/units_per_month + other_overhead_monthly/units_per_month

for c in ['VN','TH']:
    print(c, calc_overhead_unit(c))

python
for c in ['CN','VN','TH']:
    print(c, monthly_cost(c, non_direct_roles))

python
cn_direct_base = cn_direct_per_unit
calib = 6.98 / cn_direct_base
calib


1.0267227392609657

python
def direct_labor_unit(country, calibration=1.0):
    direct_monthly = monthly_cost(country, lambda r: r=='Operators')
    return direct_monthly/units_per_month * calibration

for c in ['CN','VN','TH']:
    print(c, direct_labor_unit(c, calib))

python
BOM = 125.64
def material_cost(bom, logistics=0.015, duty=0.05):
    return bom*(1+logistics)*(1+duty)
material_cost(BOM)


133.90082999999998

python
sales = 352.52
materials_vn = material_cost(BOM, 0.015, 0.05)
materials_th = material_cost(BOM, 0.015, 0.05)
direct_vn = direct_labor_unit('VN', calib)
direct_th = direct_labor_unit('TH', calib)
mfg_vn = calc_overhead_unit('VN', other_overhead_factor=1.0)
mfg_th = calc_overhead_unit('TH', other_overhead_factor=1.0)
us_base_duty = 0.125
shipping = 28.2016

def calc_profit(material, direct, mfg, base_duty=0.125, shipping=28.2016, sales=352.52):
    gross = sales - material - direct - mfg
    duty = sales * base_duty
    profit = gross - duty - shipping
    return gross, duty, profit
vn_gross, vn_duty, vn_profit = calc_profit(materials_vn, direct_vn, mfg_vn, us_base_duty, shipping, sales

(133.90082999999998,
 3.4424001070851222,
 6.105364,
 209.07140589291487,
 44.065,
 136.80480589291489,
 133.90082999999998,
 4.287116733080036,
 6.729190000000001,
 207.60286326691997,
 44.065,
 135.33626326691996)

python
# %%
import pandas as pd
import numpy as np
from math import isnan
from io import BytesIO

import xlsxwriter

# File path
path = "/mnt/data/PD36_VN_TH_cost_compare_v3.xlsx"

wb = xlsxwriter.Workbook(path)
fmt_hdr = wb.add_format({"bold": True, "bg_color": "#DDEBF7", "border":1, "align":"center"})
fmt_tbl = wb.add_format({"border":1})
fmt_money = wb.add_format({"num_format": "#,##0.00", "border":1})
fmt_pct = wb.add_format({"num_format": "0.0%", "border":1})
fmt_bold = wb.add_format({"bold": True})
fmt_yellow = wb.add_format({"bg_color":"#FFF2CC", "border":1})
fmt_note = wb.add_format({"font_color":"#333333", "text_wrap":True})
fmt_left = wb.add_format({"align":"left"})
fmt_center = wb.add_format({"align":"center"})

# -------------------- Inputs Sheet --------------------
sh = wb.add_worksheet("Inputs")

# High-level params
inputs = [
    ("月产量(台)", 50000),
    ("汇率 USD/CNY", 7.0),
    ("销售单价(人民币/台)", 352.52),
    ("BOM材料成本(人民币/台, 不含税)", 125.64),
]

# Country params
country_params_header = ["", "中国", "越南", "泰国"]
oncost = ["Oncost(社保等/工资)", 0.35, 0.22, 0.05]
mat_logistics = ["材料跨境物流比例(从中国到目的地进口, 占BOM%)", 0.015, 0.015, 0.015]
mat_import_duty = ["材料进口关税(目的地对材料征收, 占CIF%)", 0.00, 0.05, 0.05]
fg_us_base = ["成品到美国 基本关税(HTS 8513.10.20 一般税率)", 0.125, 0.125, 0.125]
fg_us_301_addl = ["成品到美国 中国加征(仅中国适用)", 0.20, 0.00, 0.00]
ship_to_us = ["成品海运到美国(人民币/台)", 28.2016, 28.2016, 28.2016]

# Write inputs
sh.write(0,0,"通用参数", fmt_bold)
for i,(k,v) in enumerate(inputs, start=1):
    sh.write(i,0,k, fmt_tbl)
    sh.write_number(i,1,v, fmt_money)

base_row = len(inputs)+3
for j, h in enumerate(country_params_header):
    sh.write(base_row, j, h, fmt_hdr)
rows = [oncost, mat_logistics, mat_import_duty, fg_us_base, fg_us_301_addl, ship_to_us]
for r, row in enumerate(rows, start=base_row+1):
    for c,val in enumerate(row):
        if c==0:
            sh.write(r,c,val, fmt_tbl)
        else:
            if "关税" in rows[r-(base_row+1)][0] or "Oncost" in rows[r-(base_row+1)][0] or "比例" in rows[r-(base_row+1)][0] or "加征" in rows[r-(base_row+1)][0]:
                sh.write_number(r,c,val, fmt_pct)
            else:
                sh.write_number(r,c,val, fmt_money)

note_row = base_row + len(rows) + 3
sh.write(note_row,0,"注：材料进口关税、到岸比例、运费均为可调参数；如在越南(EPE)或泰国(BOI)免税，请将该国“材料进口关税”改为0%。成品到美基本关税默认12.5%，中国列加征默认20%以对齐你给的中国样表，可按最新HTS/301规则修改。", fmt_note)

# Wages by band (USD/month)
wages_header = ["Band/月薪(USD)", "中国", "越南", "泰国"]
wages_rows = [
    ["Worker", 654, 302, 437],
    ["Engineer", 1020, 564, 781],
    ["Manager", 1657, 1146, 1621],
]
start = note_row + 2
for j,h in enumerate(wages_header):
    sh.write(start, j, h, fmt_hdr)
for r,row in enumerate(wages_rows, start=start+1):
    for c,val in enumerate(row):
        sh.write(r,c,val, fmt_tbl if c==0 else fmt_money)

# Headcount by role
hc_header = ["Role/HC(月产5万, 效率按85%已体现在越/泰)", "Band", "深圳_HC", "越南_HC", "泰国_HC"]
hc_rows = [
    ["Operators", "Worker", 55, 65, 65],
    ["Technicians", "Worker", 2, 3, 3],
    ["QA", "Worker", 6, 8, 8],
    ["Lab", "Worker", 2, 2, 2],
    ["Warehouse", "Worker", 6, 8, 8],
    ["Admin", "Worker", 6, 6, 6],
    ["Maintenance", "Worker", 2, 3, 3],
    ["Engineers", "Engineer", 4, 4, 4],
    ["Supervisors", "Manager", 3, 3, 3],
    ["Management", "Manager", 1, 1, 1],
]
start2 = start + len(wages_rows) + 3
for j,h in enumerate(hc_header):
    sh.write(start2, j, h, fmt_hdr)
for r,row in enumerate(hc_rows, start=start2+1):
    for c,val in enumerate(row):
        sh.write(r,c,val, fmt_tbl)

# Calibration to match CN direct labor 6.98 RMB/unit
calib_row = start2 + len(hc_rows) + 3
sh.write(calib_row, 0, "校准系数(使中国“直接人工/台”≈6.98)", fmt_tbl)
# Put a formula to compute initial factor based on below calc; set default 1.03
sh.write_number(calib_row, 1, 1.0267)

# Named ranges for ease
sh.name = "Inputs"

# -------------------- Calc: wages helper sheet --------------------
w = wb.add_worksheet("WageCalc")

# Headers
w.write(0,0,"国家", fmt_hdr); w.write(0,1,"角色", fmt_hdr); w.write(0,2,"Band", fmt_hdr)
w.write(0,3,"HC", fmt_hdr); w.write(0,4,"月薪USD", fmt_hdr); w.write(0,5,"Oncost", fmt_hdr)
w.write(0,6,"汇率", fmt_hdr); w.write(0,7,"月成本(人民币)", fmt_hdr);

countries = [("中国","C"),("越南","V"),("泰国","T")]
row = 1
for cname, code in countries:
    for r in range(len(hc_rows)):
        role, band, hc_cn, hc_vn, hc_th = hc_rows[r]
        if code=="C": hc = hc_cn
        elif code=="V": hc = hc_vn
        else: hc = hc_th
        w.write(row,0,cname, fmt_tbl)
        w.write(row,1,role, fmt_tbl)
        w.write(row,2,band, fmt_tbl)
        w.write_number(row,3,hc, fmt_tbl)
        # lookup salary and oncost using formulas from Inputs
        if band=="Worker":
            sal_col = 1
        elif band=="Engineer":
            sal_col = 2
        else:
            sal_col = 3
        # Salary formula referencing Inputs wages table
        wages_start = start+1  # first wages row
        w.write_formula(row,4, f"=INDEX(Inputs!$B${wages_start}:$D${wages_start+2}, MATCH(C{row+1}, Inputs!$A${wages_start}:$A${wages_start+2},0), "
                                 f"IF(C{row+1}=\"Worker\",1,IF(C{row+1}=\"Engineer\",2,3)))", fmt_money)
        # Oncost
        oncost_row = base_row+1  # oncost row in inputs
        w.write_formula(row,5, f"=IF(A{row+1}=\"中国\", Inputs!$B${oncost_row+1}, IF(A{row+1}=\"越南\", Inputs!$C${oncost_row+1}, Inputs!$D${oncost_row+1}))", fmt_pct)
        # Exchange rate
        exch_row = 2
        w.write_formula(row,6, f"=Inputs!$B${exch_row+1}", fmt_money)
        # Monthly RMB cost = HC * SalaryUSD * (1+Oncost) * FX
        w.write_formula(row,7, f"=D{row+1}*E{row+1}*(1+F{row+1})*G{row+1}", fmt_money)
        row += 1

# Sum helpers per country
row += 2
w.write(row,0,"合计(人民币/月)", fmt_bold)
for idx,(cname, code) in enumerate(countries):
    # sum for each country
    first = 2 + idx*len(hc_rows)
# -------------------- Summary (main table) --------------------
sum_ws = wb.add_worksheet("Summary")

sum_ws.write(0,0,"项目", fmt_hdr)
sum_ws.write(0,1,"包含项", fmt_hdr)
sum_ws.write(0,2,"中国", fmt_hdr)
sum_ws.write(0,3,"越南", fmt_hdr)
sum_ws.write(0,4,"泰国", fmt_hdr)

# Rows
labels = [
    ("销售单价","", "price"),
    ("材料成本","", "material"),
    ("人工（直接人工）","生产操作人员工资，社保和其他", "direct"),
    ("制造费用","生产管理人员工资和社保，租金，折旧，手电，机物料消耗，维修", "mfg"),
    ("毛利","","gross"),
    ("关税","","duty"),
    ("运费","","freight"),
    ("利润","","profit"),
]
for i,(lab,desc,key) in enumerate(labels, start=1):
    sum_ws.write(i,0,lab, fmt_tbl)
    sum_ws.write(i,1,desc, fmt_tbl)

# Price
sum_ws.write_formula(1,2,"=Inputs!$B$3", fmt_money)
sum_ws.write_formula(1,3,"=Inputs!$B$3", fmt_money)
sum_ws.write_formula(1,4,"=Inputs!$B$3", fmt_money)

# Material per unit by country
# = BOM * (1 + logistics%) * (1 + import duty%)
bom_cell = "Inputs!$B$4"
# find rows for material params
log_row = base_row+2
duty_row = base_row+3
sum_ws.write_formula(2,2, f"={bom_cell}*(1+Inputs!$B${log_row+1})*(1+Inputs!$B${duty_row+1})", fmt_money) # China
sum_ws.write_formula(2,3, f"={bom_cell}*(1+Inputs!$C${log_row+1})*(1+Inputs!$C${duty_row+1})", fmt_money) # VN
sum_ws.write_formula(2,4, f"={bom_cell}*(1+Inputs!$D${log_row+1})*(1+Inputs!$D${duty_row+1})", fmt_money) # TH

# Helper: sum of monthly RMB cost for Operators + others
# Build dynamic named ranges via formulas:
units_cell = "Inputs!$B$1"

# Calculate Direct labor per unit per country:
# We'll compute monthly RMB cost for Operators rows in WageCalc and divide by units, then apply calibration factor.
calib_cell = f"Inputs!$B${calib_row+1}"

def direct_formula(country_start_row):
    # Operators row is always first of each country's block in WageCalc (row 2, 12, 22 if 10 roles each)
    # WageCalc table starts at row 2 for first country; each block has len(hc_rows)=10 rows.
    return f"=(WageCalc!$H${country_start_row}/ {units_cell})*{calib_cell}"

# Determine starting rows for each country block in WageCalc:
block_size = len(hc_rows)
cn_start = 2
vn_start = cn_start + block_size
th_start = vn_start + block_size

sum_ws.write_formula(3,2, direct_formula(cn_start), fmt_money)
sum_ws.write_formula(3,3, direct_formula(vn_start), fmt_money)
sum_ws.write_formula(3,4, direct_formula(th_start), fmt_money)

# Manufacturing expense per unit:
# = (non-operator wages per month / units) + "其他制造费用/台"
# 其他制造费用/台 通过“用中国总制造费用8.38校准”: 
# other_overhead_per_unit = 8.38 - (中国非直接人工工资/台)
# 对越/泰乘以一个系数（默认1.0，可在Inputs里改：我们放在Oncost下面一行，先用常数1.0）
other_factor_row = base_row + len(rows) + 1
# put the factor row in Inputs
sh.write(other_factor_row, 0, "其他制造费用系数(相对中国，默认1.00)", fmt_tbl)
sh.write_number(other_factor_row, 1, 1.0, fmt_money)
sh.write_number(other_factor_row, 2, 1.0, fmt_money)
sh.write_number(other_factor_row, 3, 1.0, fmt_money)

# Helper: sum of non-operator monthly RMB costs
def non_operator_sum(start_row):
    # Sum H column for all roles except first row (operators) within block_size rows
    return f"=SUM(WageCalc!$H${start_row+1}:$H${start_row+block_size-1})"

# Convert to per unit
def non_operator_unit(start_row):
    return f"({non_operator_sum(start_row)})/{units_cell}"

# China other overhead per unit to match 8.38
sum_ws.write(12,0,"（辅助计算）", fmt_bold)
sum_ws.write(13,0,"中国非直接人工/台", fmt_tbl)
sum_ws.write_formula(13,2, non_operator_unit(cn_start), fmt_money)
sum_ws.write(14,0,"按8.38推导的中国“其他制造费用/台”", fmt_tbl)
sum_ws.write_formula(14,2, "=8.38 - C14", fmt_money)

# VN、TH其他制造费用/台
sum_ws.write(15,0,"越南其他制造费用/台 = 中国对应 × 系数", fmt_tbl)
sum_ws.write_formula(15,3, f"=$C$15*Inputs!$C${other_factor_row+1}", fmt_money)
sum_ws.write(16,0,"泰国其他制造费用/台 = 中国对应 × 系数", fmt_tbl)
sum_ws.write_formula(16,4, f"=$C$15*Inputs!$D${other_factor_row+1}", fmt_money)

# Now Manufacturing expense per unit
sum_ws.write_formula(4,2, f"={non_operator_unit(cn_start)} + $C$16", fmt_money)  # CN
sum_ws.write_formula(4,3, f"={non_operator_unit(vn_start)} + $C$15*Inputs!$C${other_factor_row+1}", fmt_money)  # VN
sum_ws.write_formula(4,4, f"={non_operator_unit(th_start)} + $C$15*Inputs!$D${other_factor_row+1}", fmt_money)  # TH

# Gross margin
sum_ws.write_formula(5,2, "=C2 - C3 - C4 - C5", fmt_money)
sum_ws.write_formula(5,3, "=D2 - D3 - D4 - D5", fmt_money)
sum_ws.write_formula(5,4, "=E2 - E3 - E4 - E5", fmt_money)

# Duty
# China: price*(base + 301 addl); VN/TH: price*base
base_row_inputs = base_row + 1
sum_ws.write_formula(6,2, f"=C2*(Inputs!$B${(base_row+3)+1} + Inputs!$B${(base_row+4)+1})", fmt_money)  # CN
sum_ws.write_formula(6,3, f"=D2*Inputs!$C${(base_row+3)+1}", fmt_money)  # VN
sum_ws.write_formula(6,4, f"=E2*Inputs!$D${(base_row+3)+1}", fmt_money)  # TH

# Freight
sum_ws.write_formula(7,2, f"=Inputs!$B${(base_row+5)+1}", fmt_money)
sum_ws.write_formula(7,3, f"=Inputs!$C${(base_row+5)+1}", fmt_money)
sum_ws.write_formula(7,4, f"=Inputs!$D${(base_row+5)+1}", fmt_money)

# Profit
sum_ws.write_formula(8,2, "=C6 - C7 - C8", fmt_money)
sum_ws.write_formula(8,3, "=D6 - D7 - D8", fmt_money)
sum_ws.write_formula(8,4, "=E6 - E7 - E8", fmt_money)

# Formatting widths
sum_ws.set_column(0,1,28)
sum_ws.set_column(2,4,14)

# -------------------- Notes / Sources --------------------
ns = wb.add_worksheet("Notes")
ns.write(0,0,"核对与来源（请以贵司最终归类与报关为准）", fmt_bold)
notes = [
"1) 美国对手电筒（HTS 8513.10.20.00）的“一般税率”为 12.5%。官方裁决与多家数据库一致。",
"2) Section 301 额外关税仅适用于“中国原产”货物；越南/泰国原产不适用。",
"3) 部分数据库显示 8513.10.20.00 对中国有 +15% 的加征（随年度审查可能调整）；模型中中国列默认加征 20% 以对齐你给的中国实际口径，均可在 Inputs 页修改。",
"4) 越南/泰国对“从中国进口的材料”的进口关税因 HS Code 而异（常见电子零件 0~5%）；模型以占位默认 5% 且可改。若入 EPE/BOI 等项目实际可免。",
"5) 越南/泰国到美国的海运费与中国同一航线量级相近，受市场波动较大，模型留有可调单价。",
]
for i,t in enumerate(notes, start=1):
    ns.write(i,0,t, fmt_note)

wb.close()

path

'/mnt/data/PD36_VN_TH_cost_compare_v3.xlsx'