Python-скрипт для unit-экономики цеха: расчет заказов с экспортом в Excel

[kirilljsx]

В цехе каждый заказ - это мини-бизнес. Python-скрипт быстро считает unit-экономику: выручку, затраты, маржу на единицу. Экспорт в Excel для B2B-оценки у клиента на столе.

Ручной эксель для заказов - сплошной геморрой. Ошибки в формулах, копи-паст по листам, время уходит на минуты вместо секунд. Скрипт берет вводные: объем, цена металла, трудозатраты, ЧПУ-программирование - и выдает полную картину рентабельности. Полезно для металлообработки, где маржа висит на волоске от поставок и режимов резания.

Что такое unit-экономика в цеховом деле

Unit-экономика - разбор одного заказа или единицы изделия. Считаем выручку минус все затраты: материалы, электроэнергия, износ фрез, зарплата оператора ЧПУ. Без этого B2B-клиенты давят ценой, а ты в минусе сидишь. В металлообработке типичный юнит - деталь под станок: сталь по кг, время на Fanuc, постобработка.

Пример: заказ на 1000 шестеренок. Цена клиенту 500 руб/шт, металл 150 руб, резка 1 мин/шт по 20 руб/мин, упаковка 20 руб. Ручной расчет в экселе - час, с ошибками. Скрипт жмет все за 5 сек, плюс сценарии: если металл подорожает на 10%, маржа упадет? Видно сразу. Логично перейти к формулам - они простые, но автоматизация спасает от рутины.

• Выручка: объем * цена_ед.
• Переменные затраты: металл_кг * цена_кг + время_ЧПУ * ставка_мин + труд_ручной.
• Постоянные: амортизация станка / год * доля_заказа + электроэнергия.
• Маржа: (выручка - переменные - постоянные) / выручка * 100%.

Показатель	Формула	Пример для 1 детали
Выручка	volume * price	500 руб
Затраты var	metal + cnc_time * rate	250 руб
Маржа	(rev - costs)/rev *100	50%

Нюанс: не забудь налоги - минус 20% от прибыли для чистой картины.

Ключевые формулы для цеха

В цеху затраты делят на переменные (растут с объемом) и постоянные (аренда, оклады). Переменные - основной удар: цена лента-ленты, фрезы по номеру, время по G-коду. Постоянные распределяют по заказам, чтоб не завысить. Скрипт их балансирует автоматически.

Реал: фрезеровка алюминия. 500 деталей, G-code на Fanuc генерит 2 мин/деталь, инструмент 0.5 руб/мин. Металл 100 руб/кг, вес детали 0.2 кг. Без скрипта менеджер в экселе путает объемы. С ним - таблица с break-even: сколько нужно сбыть, чтоб в ноль. Подводит к вводу данных и расчету в коде.

1. Ввод: dict с volume, price, metal_cost, cnc_time, labor_rate.
2. Расчет: revenue = volume * price; var_costs = (metal_cost * weight * volume) + (cnc_time * volume * rate).
3. Fixed: станок_аморт / год * (volume / год_произв).
4. Profit = revenue - var_costs - fixed; ROI = profit / fixed * 100.

Код-сниппет для старта:

import pandas as pd

# Ввод данных заказа
data = {
    'volume': 1000,
    'price': 500,
    'metal_kg': 0.2,
    'metal_price': 100,
    'cnc_min': 1.5,
    'cnc_rate': 20,
    'fixed_month': 50000  # аморт + аренда
}
revenue = data['volume'] * data['price']
var_costs = (data['metal_kg'] * data['metal_price'] * data['volume']) + (data['cnc_min'] * data['volume'] * data['cnc_rate'])
margin = (revenue - var_costs) / revenue * 100
print(f'Маржа: {margin:.1f}%')

Тестируй на своих заказах - меняй metal_price, смотри просадку.

Полный скрипт с экспортом в Excel

Скрипт на pandas - король для цеха. Читает ввод, крутит циклом сценарии (базовый, пессимист, оптимист), пишет в xlsx с листами. Формулы в экселе подтянутся, клиент откроет и сам пощелкает. Нет xlwings - не надо Excel запускать, чистый Python.

Пример для B2B: клиент из нефтегаза кидает чертеж, ты грузишь параметры - скрипт выдает таблицу: цена заказа, LCOF (стоимость единицы), payback. В металлообработке это убивает конкурентов - цифры на столе, без домыслов. Переходим к коду: копипасть, запусти pip install pandas openpyxl.

• Установка: pip install pandas openpyxl xlsxwriter.
• Сценарии: базовый, +10% металлу, -20% времени ЧПУ.
• Экспорт: writer = pd.ExcelWriter(‘unit_ceh.xlsx’); df.to_excel(writer, ‘Расчеты’).
• Графики: plt.plot, сохрани в лист.

Полный скрипт готов к работе:

import pandas as pd
import numpy as np

def calc_unit(data):
    revenue = data['volume'] * data['price']
    var_costs = (data['metal_kg'] * data['metal_price'] * data['volume']) + \
                (data['cnc_min'] * data['volume'] * data['cnc_rate'])
    fixed = data['fixed_month'] / 20  # на день, грубо
    profit = revenue - var_costs - fixed * data['days']
    margin = (profit / revenue) * 100
    return {'revenue': revenue, 'var_costs': var_costs, 'fixed': fixed * data['days'], 'profit': profit, 'margin': margin}

# Данные
base_data = {'volume': 1000, 'price': 500, 'metal_kg': 0.2, 'metal_price': 100, 'cnc_min': 1.5, 'cnc_rate': 20, 'fixed_month': 50000, 'days': 10}

scenarios = [
    base_data,
    {**base_data, 'metal_price': 110},  # пессимист
    {**base_data, 'cnc_min': 1.2}  # оптимист
]

results = []
for i, scen in enumerate(['Базовый', 'Металл+10%', 'Время-20%']):
    res = calc_unit(scenarios[i])
    res['scenario'] = scen
    results.append(res)

df = pd.DataFrame(results)

# Экспорт
with pd.ExcelWriter('unit_ekonomika_ceh.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Unit_расчет')
    workbook = writer.book
    worksheet = writer.sheets['Unit_расчет']
    # Форматы
    num_format = workbook.add_format({'num_format': '#,##0.00'})
    worksheet.set_column('A:F', None, num_format)

print('Файл unit_ekonomika_ceh.xlsx готов. Открой - клиентам тащи.')

Сценарий	Выручка	Var затраты	Fixed	Прибыль	Маржа %
Базовый	500000	250000	25000	225000	45.0
Металл+10%	500000	270000	25000	205000	41.0
Время-20%	500000	230000	25000	245000	49.0

Добавь свой инструмент: time по API справочника резки.

Сценарии и риски в unit

Сценарии - must have. Базовый по текущим ценам, пессимист с ростом металла (как в 2024), оптимист с новой фрезой. Скрипт крутит np.array, строит NPV если проект долгий. В цеху риски: брак 2%, задержка поставок +3 дня.

Для B2B: клиент видит таблицу, ты аргументируешь цену. Без этого торг до последнего рубля. Код гибкий - допили под свой цех: добавь энергию кВт*ч, зарплату сдельную. Таблица рисков подводит к тюнингу.

1. Брак: margin * (1 - брак_%).
2. Задержка: fixed * extra_days.
3. Скидка клиенту: price * (1 - discount).

Тестируй риски в скрипте - меняй dict и смотри.

Таблицы для клиента

Экспорт - не просто дамп, а с форматированием. Pandas + xlsxwriter: цвета для маржи <30% красным, графики pie для затрат. Клиент открывает, видит dashboard. В B2B это конвертит лиды - цифры не врут.

Пример листа: pie выручка/затраты, line маржа по сценариям. Код выше пишет базу, допили format. Полезно для презентаций: скопируй в PPT. Логично кодами для графиков.

import matplotlib.pyplot as plt

df_pie = pd.Series({'Var': 250000, 'Fixed': 25000, 'Profit': 225000})
fig, ax = plt.subplots()
df_pie.plot.pie(autopct='%1.1f%%')
plt.savefig('zатраты_pie.png')

Вставь png в эксель через скрипт или вручную.

Когда скрипт окупается

Скрипт тратит минуты на расчет 10 заказов - эксель часами. В B2B оценка за 5 мин дает преимущество: клиент уходит к тебе. Окупаемость - первый выигранный тендер. Осталось допилить под API цен металла и импорт чертежей - тогда вообще огонь.

Дальше думай интеграцию: G-code time из симулятора ЧПУ, цены из 1C. Без этого ручной ввод, но уже в разы быстрее. Тестируй на реальных заказах цеха.