Как распределить стоимость доставки по стоимости продукта в Excel
Обновлено: 20.11.2024
Себестоимость производства продукции – это определение затрат в денежном выражении на единицу товаров, работ или услуг. В расчет включаются прямые и косвенные затраты. Прямые — это затраты на материалы, заработная плата рабочих и т. д. Косвенные затраты: плановая прибыль, транспорт и т. д.
Мы не будем подробно рассматривать расчет статей. Автоматизируем процесс расчета плановой себестоимости продукции с помощью формул Excel. Наша задача — создать таблицу средствами Excel, чтобы при подстановке данных автоматически учитывалась себестоимость товаров, работ, услуг.
Расчет себестоимости товаров в торговле
Рассчитывать себестоимость продукции лучше из сферы торговли. Там меньше затрат. По факту - закупочная цена, выданная поставщиком; транспортные расходы по доставке товара на склад; пошлины и таможенные сборы, если мы ввозим товары из-за границы.
Берем определенную группу товаров. Рассчитаем себестоимость продукции для каждого из них. Последняя колонка - плановый коэффициент себестоимости производства - покажет уровень затрат, которые компания понесет на доставку продукции.
Заполняем таблицу:
Уровень затрат на доставку товаров 1 и 4 составит 10%, 2 и 3 - 15%.
Формулы расчета плановой себестоимости продукции в Excel
Каждая компания рассчитывает плановую себестоимость по-своему. Ведь предприятия несут разные затраты в зависимости от вида деятельности. Любой расчет должен содержать расшифровку затрат материалов и заработной платы.
Расчет плановой себестоимости продукции начинается с определения себестоимости сырья и материалов, используемых для производства товаров (которые принимают непосредственное участие в технологическом процессе). Расходы сырья включаются в расходы по утвержденным предприятием нормативам за вычетом технологических потерь. Эти данные можно взять в технологическом или производственном отделе.
Нормы расхода сырья отразим в таблице Excel:
Здесь удалось автоматизировать только одну колонку – колонку с расходом с учетом технологических потерь. Формула = E3 + E3 * F3.
Зная нормы, мы можем рассчитать стоимость материалов (расчет идет на тысячи штук):
Следующая статья прямых затрат — заработная плата производственных рабочих. Учитывается основной оклад и дополнительный. Принципы начисления заработной платы (сдельная, повременная, от выработки) Вы можете узнать в бухгалтерии.
В нашем примере расчет заработной платы осуществляется по нормам выработки: сколько должен получить работник определенной квалификации за единицу рабочего времени.
Данные для расчетов следующие:
Цена рассчитывается по формуле: = C3 * D3.
Теперь мы можем рассчитать базовую заработную плату работников:
Для заполнения первых двух столбцов, не считая номера по порядку, мы связали данные этой таблицы с данными предыдущей. Формула расчета бонуса = C3 * 30%. Базовый оклад = C3 + D3.
Дополнительной заработной платой считаются все выплаты, предусмотренные законодательством, но не связанные с производственным процессом (отпуска, вознаграждение за выслугу лет и т. д.).
Другие данные для расчета себестоимости продукции мы добавили в таблицу сразу:
Для оценки расчета себестоимости упаковки принимаются условные показатели амортизации ОС, проценты доначисления заработной платы и налогов, обязательные страховые взносы.
Функция ВПР может использоваться для расчета стоимости доставки на основе указанного веса товара в Excel.
Как рассчитать стоимость доставки на основе определенного веса в Excel?
Создайте таблицу, содержащую два списка «Вес» и «Стоимость», как показано в таблице ниже.В этом случае, чтобы рассчитать общую стоимость доставки на основе веса, указанного в F4, выполните следующие действия.
Общая формула
=ВПР(искомое_значение, табличный_массив, индекс_столбца,[диапазон_искомого_значения])* искомое_значение
Аргументы
- Искомое_значение: значение критерия, которое вы ищете. Он должен находиться в первом столбце диапазона table_array.
- Table_array: таблица содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата.
- Col_index: конкретный номер столбца (это целое число) table_array, из которого вы возвращаете совпадающее значение.
- Range_lookup: это логическое значение, которое определяет, будет ли функция ВПР возвращать точное или приблизительное совпадение.
=ВПР(F4,B3:C7,2,1)*F4
<р>2. Нажмите клавишу Enter, чтобы узнать общую стоимость доставки.
Примечания: в приведенной выше формуле
- F4 – это ячейка, содержащая указанный вес, на основе которого будет рассчитываться общая стоимость доставки;
- B3:C7 — это table_array, содержащий столбец веса и столбец стоимости;
- Число 2 представляет столбец стоимости, из которого вы получите совпадающую стоимость;
- Число 1 (можно заменить на TRUE) означает, что будет возвращено приблизительное совпадение, если точное совпадение не найдено.
Как работает эта формула
- Из-за режима приблизительного соответствия значения в первом столбце должны быть отсортированы в порядке возрастания в случае возврата неверного значения.
- Здесь формула =ВПР(F4,B3:C7,2,1) ищет стоимость для определенного веса 7,5 в первом столбце диапазона таблицы. Поскольку 7,5 не найдено, он соответствует следующему наименьшему весу 5 и получает свою стоимость 7,00 долларов. И, наконец, эта стоимость умножается на определенный вес 7,5, чтобы получить общую стоимость доставки.
Как рассчитать стоимость доставки с минимальной оплатой в Excel?
Предположим, транспортная компания установила правило, согласно которому минимальная стоимость доставки составляет 7 долларов США независимо от веса. Вот формула для вас:
=МАКС(ВПР(F4,B3:C7,2,1)*F4,7)
Применяя приведенную выше формулу, общая стоимость доставки составит не менее 7 долларов США.
Связанные функции
Функция ВПР
Функция ВПР в Excel ищет значение путем сопоставления с первым столбцом таблицы и возвращает соответствующее значение из определенного столбца в той же строке.
Связанные формулы
Поиск значений из другого листа или книги.
В этом руководстве объясняется, как использовать функцию ВПР для поиска значений из другого листа или книги.
Нажмите, чтобы узнать больше.
Значения поиска на нескольких листах с динамическим именем листа
Во многих случаях вам может потребоваться собрать данные на нескольких листах для сводки. В этой статье представлены методы, позволяющие легко находить определенные значения на листах с динамическим именем листа.
Нажмите, чтобы узнать больше.
Виртуальный просмотр и возврат совпадающих значений в нескольких столбцах.
Обычно применение функции ВПР может вернуть совпадающее значение только из одного столбца. Иногда может потребоваться извлечь совпадающие значения из нескольких столбцов на основе критериев. Вот решение для вас.
Нажмите, чтобы узнать больше.
ВПР для возврата нескольких значений в одной ячейке.
Обычно при применении функции ВПР, если есть несколько значений, соответствующих критериям, можно получить только результат первого из них. Если вы хотите вернуть все совпадающие результаты и отобразить их все в одной ячейке, как вы можете этого добиться?
Нажмите, чтобы узнать больше.
Просмотр и возврат всей строки совпавшего значения.
Обычно использование функции просматривания может возвращать результат только из определенного столбца в той же строке. Эта статья покажет вам, как вернуть всю строку данных на основе определенных критериев.
Нажмите, чтобы узнать больше.
Лучшие инструменты для повышения производительности в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите выполнять свою повседневную работу быстро и качественно?Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
В этом примере мы собираемся найти альтернативные варианты доставки материалов, сохраняя при этом общие транспортные расходы на минимальном уровне. Скажем, у компании есть склады в Сент-Луисе, Лос-Анджелесе и Бостоне. Шесть торговых точек расположены по всей территории Соединенных Штатов. Эти торговые точки принимают заказы от клиентов. Затем компания отгружает продукцию с одного из складов. Цель компании — удовлетворить потребности в продукции всех шести торговых точек из имеющихся запасов на складах. При доставке товаров в торговые точки компания стремится максимально снизить стоимость доставки.
Объяснение компонентов книги
Рабочая книга [которую вы скачали] немного сложна, поэтому я попытался объяснить компоненты рабочей книги по отдельности:
В этом примере мы собираемся определить самый дешевый способ доставки товаров со складов в торговые точки.
Ячейка G24 — последняя использованная ячейка, общая стоимость доставки для всех заказов.
Решатель заполнит значения в диапазоне ячеек D12:F17 таким образом, чтобы минимизировать затраты на доставку со складов до торговых точек. Другими словами, решение минимизирует значение в ячейке G24, корректируя значения диапазона ячеек D12:F17, выполняя следующие ограничения:
- Количество единиц товара, требуемое каждой торговой точкой, должно равняться отгруженному количеству. Другими словами, все заказы будут выполнены. Эти ограничения могут быть выражены следующими спецификациями:
C12=G12, C14=G14, C16=G16, C13=G13, C15=G15 и C17=G17 - Количество единиц, оставшихся в запасах каждого склада, не должно быть отрицательным. Другими словами, склад не может отгружать больше, чем его запасы. Следующее ограничение показывает это: D22>=0 E22>=0 F22>=0
- Настраиваемые ячейки не могут быть отрицательными, поскольку отправка отрицательного количества единиц не имеет смысла. В диалоговом окне «Параметры решения» есть удобная опция: Сделать неограниченные переменные неотрицательными. Убедитесь, что этот параметр включен.
Сложнее всего установить проблему. Вы должны знать, как добавлять ограничения. См. эту ссылку, чтобы узнать о добавлении ограничений в диалоговом окне «Параметры решения». В этом примере вам нужно ввести девять ограничений.
Шаги по настройке диалогового окна «Параметры решателя»
Выполните следующие действия, чтобы настроить диалоговое окно "Параметры решателя". Это диалоговое окно появляется, когда вы выбираете Данные ➪ Анализ ➪ Решатель.
Шаг 1:
Заполните поле "Установить цель" следующим значением: $G$24.
Шаг 2:
Выберите переключатель параметра «Минимум» в разделе «Управление».
Шаг 3:
Выберите ячейку с $D$12 по $F$17, чтобы заполнить поле путем изменения ячеек переменных. В этом поле будет показано значение $D$12:$F$17.
Шаг 4:
Шаг 5:
Установите флажок "Сделать неограниченные переменные неотрицательными".
Шаг 6:
Выберите Simplex LP в раскрывающемся списке Select a Solding Method.
Шаг 7:
Теперь нажмите кнопку "Решить". Вы найдете диалоговое окно Solver Results, показанное на следующем рисунке. Нажмите OK, вы закончили с вашим результатом.
Поиск решения отображает решение, показанное на следующем рисунке.
Это решение, созданное Solver.
Общая стоимость доставки составляет 55 515 долларов США, и все ограничения соблюдены. Обратите внимание, что поставки в Майами осуществляются как из Сент-Луиса, так и из Бостона.
Подробнее о Солвере
Решатель — это сложный инструмент. Эта глава просто знакомит с основами инструмента. Если вы хотите узнать больше о Solver, я настоятельно рекомендую веб-сайт Frontline Systems. Компания Frontline Systems разработала Солвер для Excel. На его веб-сайте есть несколько учебных пособий и много полезной информации. Вы также можете скачать подробное руководство с их веб-сайта о Solver. Вы также можете найти дополнительные продукты Solver для Excel, которые могут решать гораздо более сложные задачи.
Подробнее…
Загрузить рабочий файл
Скачать рабочий файл по ссылке ниже:
Кавсер
Как использовать решатель в Excel (решение задач линейного программирования)!
Задача упорядочения с использованием алгоритма Джонсона планирования n-заданий на 2-х машинах [Sol]
Решайте проблемы секвенирования с помощью Excel Solver!
Как назначить работу с помощью Evolutionary Engine?
Не удается загрузить файл примера. Он продолжает направлять меня на mailchimp, а затем я вижу ошибку, потому что я уже подписан.
Проверьте, пожалуйста, свою электронную почту, Смит!
Здравствуйте,
Надеюсь, вы поможете мне в этом, используя Excel
Компания Kellogg’s Cornflake Company была основана в 1906 году братьями Келлогг, которые первоначально управляли санаторием в Мичигане, США. Они экспериментировали с разными способами приготовления каш, не теряя полезных свойств. Их философия заключалась в том, что «улучшение питания ведет к улучшению здоровья». С 1938 года по настоящее время Kellogg's открыла производственные предприятия в Великобритании, Канаде, Австралии, Латинской Америке и Азии. Kellogg’s в настоящее время является ведущим мировым производителем сухих завтраков. Его продукция производится в 3 странах; Бельгия (местоположение 50,4) общей вместимостью 25000 тонн и стоимостью реализации за единицу 6$; Китай (местоположение 39, 116) с общей мощностью 28000 тонн и стоимостью дистрибуции за единицу 9$; и Франция (местоположение 48, 2) с общей мощностью 37000 тонн и затратами на продажу за единицу 5$, при этом продается более чем в 3 странах; Греция (места 37, 23) с общим спросом 15000 тонн и затратами на сбыт на единицу 9$; Ирландия (местоположение 53, 6) с общим спросом 18000 тонн и затратами на сбыт на единицу 4$; и Люксембург (местоположение 49, 6) с общим спросом 28000 тонн и затратами на сбыт на единицу 9$. Компания производит широкий ассортимент зерновых продуктов, в том числе известные бренды Kellogg’s Corn Flakes, Rice Krispies, Special K, Fruit n’ Fibre, а также злаковые батончики Nutri-Grain. Бизнес-стратегия Kellogg ясна и сфокусирована: • на развитии бизнеса по производству хлопьев — сейчас существует 40 видов хлопьев • на расширении бизнеса по производству снеков — путем диверсификации полуфабрикатов • на использовании конкретных возможностей роста.
Вопросы
1. Чтобы свести к минимуму общие затраты на распределение, спроектируйте модель расположения объекта, чтобы определить правильное место для создания распределительного центра.
2. Спроектируйте распределительную сеть и создайте отчет с ответами, чтобы определить поток товаров между поставщиками и рынками.
3. Возможно ли закрыть один рынок поставок для минимизации общих затрат, если известно, что фиксированные затраты таковы: Бельгия (18 000 долл.), Китай (17 000 долл.) и Франция (19 000 долл.)? Создайте отчет об ответах, чтобы показать свои результаты.
Привет, Ахмед!
Я записал вашу проблему, надеюсь, мы сможем ее решить.
Спасибо.
Рассчитать операционные расходы на доставку при «полной загрузке» — непростая задача. Много времени тратится на «угадывание» одних затрат и упущение других. Но если вы не получите точного представления о том, каковы на самом деле ваши операционные расходы, вы никогда не сможете найти способы их снижения.
Ниже приведена разбивка всех основных центров затрат, связанных с выполнением операций, и способы их расчета. Мы создали его как основу для построения вашей бизнес-модели.
Как рассчитать стоимость доставки
Это одна из самых частых ошибок в расчетах операционных расходов. Если вы берете свои расходы из «контрактной рейтинговой таблицы», то вы, вероятно, получите скидку более чем на 3-7% в зависимости от службы доставки, которую вы используете. Еще один часто неверный способ оценки стоимости доставки — это предположение, что вы можете взять среднюю стоимость вашей «зональной диаграммы» или, что еще хуже, предположить, что большинство отправлений отправляется в одну «зону». Все эти методы часто используются, но крайне неточны.
Ответ на вопрос "как рассчитать стоимость доставки" заключается в том, чтобы взять счет, который вы получаете в конце месяца, и проработать его в обратном порядке. Это даст вам реальную стоимость каждой отправки, которая, скорее всего, вас шокирует.
Два лучших способа расчета стоимости доставки
<р>1.Возьмите общую сумму счета за период времени, указанный в счете, и разделите ее на общее количество отгрузок, указанное в счете.Допустим, вы оплатили счет на общую сумму 15 736,27 долларов США за определенный период времени, а общее количество номеров отслеживания (отправлений) за этот период составило 2 675. Рассчитайте стоимость доставки одного товара: 15 736,27 долларов США / 2 675 = 5,88 долларов США. Таким образом, фактическая стоимость доставки одного заказа составляет 5,88 доллара США. Не беспокойтесь о весе или зонах, сделайте это для пары счетов, чтобы рассчитать реальную стоимость доставки, прежде чем учитывать другие факторы. Это должно обеспечить базовый уровень реальной стоимости. Используйте партнерские отношения с EasyPost, чтобы успешно масштабировать свой бизнес.
<р>2. Возьмите данные из счета и вставьте их в электронную таблицу для более глубокого анализа. По крайней мере, вы захотите включить следующие точки данных: вес, окончательная стоимость, состояние доставки.
Создавайте сводные таблицы по весу, конечной стоимости, состоянию доставки и любым другим факторам, которые вы хотите проанализировать.
Совет. Если вы не знаете, как создавать сводные таблицы, узнайте здесь. Сводные таблицы — бесценный инструмент для понимания ваших операций.
Сводная таблица должна позволить вам рассчитать среднюю стоимость на основе веса и состояния доставки. Используя эту информацию, вы теперь можете правильно сравнить свою реальную стоимость с тем, что вам предоставит 3PL.
Знание реальной стоимости доставки позволяет вам увидеть, на что идет большая часть ваших расходов. Взятие расходов непосредственно из счета-фактуры позволяет вам правильно учесть дополнительные сборы, которые вы увидите у большинства перевозчиков (например, надбавка за доставку, надбавка за топливо, плата за проживание и т. д.).
Затраты на оплату труда
Второй наиболее часто неправильно рассчитываемой статьей затрат на операции является трудозатраты на сбор, упаковку и доставку ваших заказов. Большинство людей берут почасовую ставку сотрудника и оценивают стоимость на основе времени, необходимого для доставки заказов в данный час. Отраслевой термин для этого — "Единицы в час" (UPH).
Вот пример неправильного расчета: Джо получает 15 долларов США в час, а его UPH – 120. Следовательно, 15/120 долларов США = 0,125 доллара США за единицу стоимости.
Эта формула не включает затраты на пособия, расходы на временных работников и время простоя. Время простоя — лучший символ простоя сотрудников, поскольку сотрудники-люди никогда не работают на 100% продуктивно.
Лучший подход к расчету затрат на рабочую силу похож на доставку. Возьмите счет-фактуру (если вы работаете с временным агентством) или запросите в своей бухгалтерии полную сумму расходов за определенный период времени (скажем, 1 месяц). Это будет включать в себя любые сборы и/или льготы, которые будут добавлены сверх часовой ставки в размере 15 долларов США. Затем, используя тот же период времени, подсчитайте общее количество заказов, которые вы отправили в этом месяце (не используйте оценки, получите подтвержденное количество).
Например. Общие затраты на работу склада в этом месяце с учетом льгот, сборов временного агентства и среднего времени простоя = 18 570,46 долл. США, а общий объем отгрузок со склада в этом месяце = 17 560. Разделите 18 570,46 доллара на 17 560, и вы получите 1,06 доллара. Это означает, что вы платите полную стоимость доставки в размере 1,06 доллара США за доставку. Делайте это в течение нескольких месяцев, чтобы установить хороший базовый уровень средних затрат на оплату труда.
Расходы на хранение:
Стоимость хранения часто упускается из виду при учете операций, поскольку обычно она относится к другому разделу учета, поэтому обычно не включается в себестоимость проданных товаров. Однако очень важно знать стоимость хранения, если вы пытаетесь сравнить затраты на выполнение операций внутри компании и на аутсорсинг для 3PL.
Истинные затраты на хранение – это фактические затраты на хранение ваших товаров. Если у вас есть собственный склад, то это будет легко посчитать с ежемесячной арендной платой за склад. Но обычно это не так, поскольку многие малые и средние предприятия используют хранилище, связанное с их офисом. Лучший способ расчета затрат в этой ситуации заключается в следующем: возьмите площадь, которую вы выделили для своей операционной группы, и посчитайте процент этой площади по сравнению с общей площадью. Используя этот процент, вы можете определить долю общей арендной платы и страховки, которая должна применяться к вашей деятельности. Например:
- Офис = 30 000 кв. футов
- «Операционная» площадь офиса = 5000 кв. футов (17 % офисных площадей)
- Ежемесячная арендная плата за офис = 100 000 долларов США в месяц.
Отсюда легко определить, сколько вы платите за размещение своих операций в офисе. Просто вычтите 17% из вашей ежемесячной арендной платы в размере 100 000 долларов США (что равняется 17 000 долларов США), и вы получите общую стоимость аренды для ваших операций. Чтобы узнать стоимость хранения для каждого пакета, разделите месячную арендную плату за хранение (17 000 долл. США) на общее количество заказов за месяц (допустим, 20 000). 17 000 / 20 000 = 0,85 доллара США за заказ.
Теперь, когда вы знаете, что стоимость доставки этих продуктов составляет 0,85 доллара США за заказ, вы можете использовать эту цифру для определения следующих шагов складского хранения. Если вы можете поддерживать плату за хранение 0,85 доллара США за заказ на внутреннем складе, то, во что бы то ни стало, продолжайте хранить свои продукты на внутреннем складе. Но если вы не можете этого сделать, используйте цифру в 0,85 доллара США в качестве отправной точки, чтобы определить, какой 3PL обеспечит вам наилучшую стоимость хранения.
Стоимость поставки
Помимо затрат на хранение, затраты на снабжение также часто упускают из виду эксплуатационные расходы. Это легко сделать, вам не обязательно покупать расходные материалы по установленному графику, поскольку вы строите свой бизнес, и легко думать, что такие вещи, как лента, наполнитель или коробки, в любом случае не стоят много. Правда в том, что эти затраты могут расти на удивление быстро, и полезно знать, как они влияют на стоимость вашего заказа.
Как и стоимость рабочей силы, не рассматривайте ее как переменную на единицу, особенно когда вы пытаетесь выяснить, сколько ленты должно быть выделено для пакета. Это редко срабатывает. Вместо этого вы должны суммировать все свои запасы за определенный период времени. При расчете средней стоимости поставок лучше всего брать более длительные сроки, например, 3-6 месяцев. Этого времени достаточно, чтобы получить хорошее экономическое представление обо всех расходных материалах, которые вы покупаете.
Это может показаться сложным, но если вы заказываете расходные материалы в определенном месте, таком как Amazon или Uline, вы сможете получить стоимость предыдущих заказов на поставку. После извлечения вы должны применить их к объему в том же таймфрейме. Например:
5768 долл. США — это общая стоимость коробок, лент, бумажных заполнителей и транспортных этикеток за последние 4 месяца. За 4 месяца вы отправили 11 348 заказов. Разделите 5 768 на 11 348, и вы получите 0,51 доллара США, то есть стоимость поставки за отгрузку в этом квартале.
Заключение
При правильном расчете эти основные области позволят вам увидеть реальную стоимость ваших операций. Как только вы вооружитесь этими знаниями, вы сможете начать правильно сравнивать «яблоки с яблоками». Знание фактической стоимости выполнения — доставки, труда, хранения и расходных материалов — дает вам бесценные знания при определении операционной модели вашего бизнеса. Если вы пойдете внутрь, вы будете знать, сколько это должно стоить. Если вы решите воспользоваться аутсорсингом, вам будет проще договориться о цене.
Читайте также: