Как сделать таблицу расчета цен в excel?

Абсолютная ссылка в Excel&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я — ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

Как провести презентацию и заслужить доверие аудитории

Как не потерять качество бизнеса в погоне за количеством

Надоели убытки? Выявляем нерентабельных клиентов

Как стать хорошим руководителем

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

Абсолютная ссылка. Создаем таблицу для расчета цены с учетом заданных параметров

Будем учиться создавать универсальную таблицу, которая даст нам возможность рассчитать цену при нескольких заданных параметрах.

Создаем таблицу и заполняем ее шапку показателями, которые будут участвовать в расчетах.

При этом часть показателей неизменна для всех видов товара, поэтому мы их вынесем в отдельный блок «задаваемые параметры», и сделаем так, чтобы при изменении этих параметров менялись расчеты во всей таблице.

Основная задача данного урока — научиться делать абсолютную ссылку . Что это такое?

Абсолютная ссылка дает возможность ссылаться на одну и ту же ячейку при копировании формулы (в отличие от относительной ссылки). Таким образом, мы можем в первой строке сослаться на конкретную ячейку, скопировать ее и протянуть до конца списка. Весь список будет ссылаться на ту ячейку, в которой действует абсолютная ссылка. Соответственно при изменении этой ячейки меняется весь столбец или строка.

Перейдем к практике. Посчитаем процент менеджера от выручки.

Для того, чтобы сделать абсолютную ссылку, нужно произвести следующие шаги:

  1. добавить формулу;
  2. зайти в строку состояния;
  3. поставить знак $ перед буквой и перед цифрой, указывающих на адрес ячейки.

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

Для того, чтобы устранить эту проблему нужно зайти в «Сервис» главного меню, выбрать кнопочку «Параметры», найти закладку «Общие» и снять галочку в квадратике «Стиль ссылок R1C1».

Итак, мы сделали первую формулу, построенную на абсолютной ссылке. Проверим, что будет при ее копировании.

Скопируем ячейку с формулой и протянем ее до конца списка. Для проведения этой операции нужно поставить курсор на левый нижний край ячейки с формулой. Появится крестик. Нажмите на этот крестик левой клавишей мышки и тяните не отпуская пальца вниз до конца списка товаров.

Как видим, все формулы ссылаются на ячейку с процентом менеджеров, что нам и было нужно.

По аналогии считаем ретро-бонус оптовика.

Не забываем, что наша основная цель — поиск цены. Значения, которые там стояли, были выставлены произвольно для примера, поэтому сейчас можно очистить это поле для дальшейнего заведения туда формулы. Добавим ее после того, как все остальные поля будут заполнены.

Итак, продолжаем заполнять таблицу: заносим данные о себестоимости и считаем стоимость отсрочки платежа:

Как видим, при расчете стоимости отсрочки платежа использованы две абсолютные ссылки: на количество дней отсрочки и % банковской ставки.

Мы закончили с занесением параметров, необходимых для расчета цены. Можем добавлять формулу.

Напомним, что формула выглядит следующим образом:

Цена = Сумма переменных затрат, не зависимых от выручки / ( 1 — сумма переменных затрат, зависимых от выручки (в % от выручки) / 100 — маржа (в % от выручки) / 100 )

Рассчитаем цену Товара № 1:

Цена = ( 44,64 + 0, 37 ) / ( 1 — 5/100 — 15/100 — 40/100) = 112,52

Для того, чтобы рассчитать цену в excel, активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= ( 44,64 + 0, 37 ) / ( 1 — % менеджера/100 — ретро-бонус/100 — маржа/100 ) ENTER

Значения, названные словами, мы рассчитаем с помощью абсолютных ссылок. Таким образом мы будем иметь возможность, рассчитав цену на один товар, скопировать формулу для других.

Мы нашли цену, которая позволит нам покрыть переменные затраты и иметь заданный уровень маржинального дохода. Нам осталось только проверить правильность расчетов и добавить НДС к цене.

Итак, по очереди считаем (как и раньше без пробелов активизируем указанные ячейки и знаки в следующем порядке):

= Цена без НДС * 1,18 ENTER

для Валового дохода

= Расчетная цена без НДС — Себестоимость без НДС ENTER

= Валовый доход без НДС / Себестоимость без НДС * 100 ENTER

= % менеджеров + отсрочка + ретро-бонус ENTER

= Валовый доход без НДС — Затраты без НДС ENTER

= МД / Расчетная цена без НДС * 100 ENTER

Все сошлось! Проверка подтвердила правильность наших расчетов.

Подобные таблицы позволяют подставлять разные параметры для разных товаров и быстро получать результат, экономя при этом наше время. Для того, чтобы просчитать показатели для нового товара, достаточно скопировать одну их строк и вставить ее вниз таблицы. Все формулы будут идентичными.

Желаю Вам удачных расчетов!

Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой «эксель» по адресу: ngt@inbox.ru

Что нужно знать о кадровых документах

Семь правил обогащения

Как не упустить удачу

Дольмены — одна из величайших тайн третьего тысячелетия

Воспоминания о будущем

Проверьте, соблюдается ли Трудовой Кодекс в вашей компании

Ежегодный оплачиваемый отпуск. На что вы имеете право?

Использование силы мысли — залог здоровья

Где живут нассамихи

&nbsp&nbsp2011-2018 &nbsp&nbspПерепечатка материалов возможна лишь при указании прямой индексируемой гиперссылки

Как осуществлять расчеты
в программе Excel

По сравнению с Word, Excel предоставляет больше полезных возможностей по обработке табличной информации, которые могут использоваться не только в офисах, но и на домашних компьютерах для решения повседневных задач.

В этой небольшой заметке речь пойдет об одном из аспектов использования таблиц Excel – для создания форм математических расчетов.

В Excel между ячейками таблиц можно устанавливать некоторые взаимосвязи и определять для них правила. Благодаря этому изменение значения одной ячейки будет влиять на значение другой (или других) ячейки. В качестве примера предлагаю решить в Excel следующую задачу.

Три человека (Иван, Петр и Василий) ведут общую предпринимательскую деятельность, заключающуюся в оптовом приобретении овощей в одних регионах страны, их транспортировке в регионы с повышенным спросом и реализации товара по более высокой цене. Иван занимается закупкой, Василий — реализацией и оба они получают по 35 % от чистой прибыли. Петр – транспортирует товар, его доля – 30 %. По этой схеме партнеры работают постоянно и ежемесячно (или даже чаще) им приходится подсчитывать, сколько денег кому положено. При этом, необходимо каждый раз производить расчеты с учетом закупочной стоимости и цены реализации товара, его количества, стоимости топлива и расстояния транспортировки а также некоторых других факторов.

А если в Excel создать расчетную таблицу, Иван, Петр и Василий смогут очень быстро поделить заработанные деньги, просто введя необходимую информацию в соответствующие ячейки. Все расчеты программа сделает за них всего за несколько мгновений.

Перед началом работы в Excel хочу обратить внимание на то, что каждая ячейка в таблице Excel имеет свои координаты, состоящие из буквы вертикального столбца и номера горизонтальной строки.

Работа в Excel. Создание расчетной таблицы:

Открываем таблицу и подписываем ячейки, в которые будем вводить исходные данные (закупочная стоимость товара, его количество, цена реализации товара, расстояние транспортировки, стоимость топлива).

Затем подписываем еще несколько промежуточных полей, которые будут использоваться таблицей для вывода промежуточных результатов расчета (потрачено на закупку товара, выручено от реализации товара, стоимость транспортировки, прибыль).

Читать еще:  Как сделать сравнение столбцов в excel?

Дальше подписываем поля «Иван», «Петр» и «Василий», в которых будет выводиться окончательная доля каждого партнера (см. рисунок, щелкните по нему мышкой для увеличения).

На рисунке видно, что в моем примере созданные поля имеют следующие координаты:

закупочная стоимость товара (руб/кг) – b1

количество товара (кг) – b2

цена реализации товара (руб/кг) – b3

расстояние транспортировки (км) – b4

стоимость топлива (руб/л) – b5

расход топлива (л/100км) – b6

потрачено на закупку товара (руб) — b9

выручено от реализации товара (руб) – b10

стоимость транспортировки (руб) – b11

общая прибыль (руб) – b12

Теперь необходимо установить взаимосвязи между ячейками таблицы. Выделяем ячейку «Потрачено на закупку товара» (b9), вводим в нее =b1*b2 (равно b1 «закупочная стоимость товара» умножить на b2 «количество товара») и жмем Enter. После этого в поле b9 появится 0. Теперь если в ячейки b1 и b2 ввести какие-то числа, в ячейке b9 будет отображаться результат умножения этих чисел.

Аналогичным образом устанавливаем взаимосвязи между остальными ячейками:

B10 =B2*B3 (выручено от реализации товара равно количество товара, умноженное на цену реализации товара);

B11 =B5*B6*B4/100 (стоимость транспортировки равна стоимости топлива умноженной на расход топлива на 100 км, умноженной на расстояние и разделенное на 100);

B12 =B10-B9-B11 (общая прибыль равна вырученному от реализации товара за вычетом потраченного на его закупку и транспортировку);

B16 =B12/100*35 (ячейки Иван и Петр имеют одинаковые значение, поскольку по условиям задачи они получают по 35% от значения ячейки B11 (общей прибыли);

B16 =B12/100*30 (а Василий получает 30% от общей прибыли).

Эту таблицу вы можете скачать уже в готовом виде, нажав сюда, или создать самостоятельно чтобы лично посмотреть как все работает. В ней в соответствующих полях достаточно указать количество товара, его закупочную стоимость и цену реализации, расстояние, стоимость топлива и его расход на 100 км, и вы тут же получите значения всех остальных полей.

Работая в Excel, аналогичным образом можно создавать расчетные таблицы на любой случай жизни (нужно лишь спланировать алгоритм расчета и реализовать его в Excel).

В процессе работы с программой Microsoft Excel иногда возникает необходимость объединить две или больше ячеек таблицы, расположенных рядом друг с другом. Во многих случаях это обеспечивает более удобное представление информации.

Здесь рассмотрен порядок объединения соседних ячеек, а также разделения ранее объединенных ячеек на несколько.

Работу в табличном редакторе Microsoft Excel можно сделать удобней, если пользоваться закреплением строк и столбцов таблицы.

Что имеется ввиду? Представим ситуацию, когда, просматривая большую таблицу, Вы забыли названия ее столбцов. Прокручивать таблицу в начало, где расположены эти названия, долго и неудобно. Особенно, если таблица очень длинная. Чтобы избежать подобных ситуаций, первую строку таблицы, содержащую названия столбцов, можно закрепить. В таком состоянии она постоянно будет оставаться на экране, а все последующие строки при прокручивании по-прежнему будут двигаться, «заезжая» под нее.

Автосохранение в Word — весьма полезная функция. Особенно, если приходится часто работать с текстом, а компьютер подключен к электросети напрямую, без блока бесперебойного питания. Лично меня оно много раз спасало.

По умолчанию, Word автоматически сохраняет открытые документы каждые 10 минут в папке «C:UsersПапка_текущего_пользователяAppDataRoamingMicrosoftWord». Если компьютер вдруг внезапно выключился, при следующем открытии программы Word пользователю будет предложено открыть сохраненные копии, или же удалить их, если необходимость в них отсутствует.

Указанные выше параметры можно изменить, заставив компьютер осуществлять автосохранение в любой другой папке, а также увеличив или уменьшив его частоту. Затрудняюсь придумать пример, когда это может понадобиться, но автосохранение Word при желании можно вообще отключить.

Бывалые пользователи Windows привыкли к тому, что в меню «Пуск» компьютера есть папка «Автозагрузка». Файлы или ярлыки, добавленные в эту папку, автоматически стартуют вместе с операционной системой.

Этот весьма удобный инструмент по каким-то причинам не был включен в меню «Пуск» Windows 8 и Windows 10. Тем не менее, он по-прежнему присутствует во всех версиях этой ОС. Как открыть папку «Автозагрузка» в Windows 8 и Windows 10 речь пойдет в этой статье.

Почти все программы (в т.ч. и компьютерные игры) распространяются в сжатом (запакованном) виде. Для нормальной работы их нужно распаковать и правильно разместить на жестком диске. Процесс распаковки и размещения называется установкой или инсталляцией.

О том, как установить программу на компьютер, и пойдет речь в изложенной ниже статье.

На компьютере с операционной системой Windows по умолчанию все программы устанавливаются в одну и ту же папку, расположенную на системном диске. Обычно это папка «Program Files» или «Program Files (x86)», находящаяся на диске С.

Чтобы установить программу в другой раздел, необходимо во время ее инсталляции «вручную» выбрать соответствующий путь.

В то же время, в настройках Windows папку установки по умолчанию можно изменить, заставив компьютер инсталлировать программы не в раздел «Program Files», а в любое другое место.


ПОКАЗАТЬ ЕЩЕ

Устанавливаем цену правильно (практика)

Когда товар разработан и ждет своего запуска, очень важно правильно пройти один из последних этапов, предшествующих выпуску товара — правильно рассчитать и установить отпускную цену на новый продукт. Существует множество теорий и подходов к ценообразованию продукта, но на практике сложно использовать только один метод расчета. Слишком много факторов следует учесть, чтобы итоговая цена реализации товара была конкурентоспособна, обеспечивала желаемый уровень прибыли и не отпугивала покупателей.

В статье мы рассмотрим методику расчета отпускной цены для нового товара, которая учитывает желания компании по уровню рентабельности продукта, принимает во внимание конкурентное окружение товара и отвечает потребностям целевой аудитории. Пример расчета цены включает также шаблон с готовыми формулами в формате Excel.

Описанный в статье пример по расчету цены реализации нового продукта подойдет для производственных предприятий, для компаний сферы услуг, для любых фирм, создающих новые продукты, но не совсем подойдет для компаний из отрасли оптовой или розничной торговли.

Вступление от автора

Перед тем, как перейти к методике расчета стоимости продукта, хочу несколько слов сказать об одном важном этапе, который всегда предшествует разработке продукта — о планировании. Когда рождается идея о создании продукта, когда принимается решение о разработке нового товара — необходимо сделать паузу и рассчитать предварительную экономику проекта: себестоимость, цену продажи, объем продаж и прибыль.

Очевидно, что на этапе идеи, не имея полного представления о том, как будет в результате выглядеть новый продукт, сложно представить конечную стоимость товара и определить маржинальность продаж. Но от вас никто и не ждет 100% точности. Пусть это будет грубый расчет, но для того, чтобы его сделать вы выполните несколько очень полезных шагов:

  • оцените спрос на товар и сформируете свои ожидания по уровню продаж
  • проведете конкурентный анализ и определите примерную цену, по которой ваш товар будет на фоне конкурентных товаров выглядеть конкурентоспособным
  • задумаетесь над тем, сколько вы бы хотели зарабатывать с продажи продукта в месяц
  • и в результате сформируете ориентир по финальной себестоимости продукта


Рис.1 Два способа расчета целевых показателей проекта

Читать еще:  Как сделать таблицу в excel распечатать таблицу?

Или наоборот: вы оцените уровень затрат, который необходим для реализации задуманной идеи, определите конкурентную цену товара и поймете, устраивает ли вас уровень прогнозируемой прибыли. В том и другом случае вы сделаете прогноз, определите целевые показатели, которые будут являться для вас ориентиром на протяжении всего процесса разработки товара.

А теперь перейдем к описанию того, как правильно рассчитать отпускную цену на новый товар.

Описание методики

Для расчета оптимальной стоимости товара нам потребуется рассчитать три варианта идеальной цены: оптимальную цену по отношению к конкурентам, оптимальную цену по мнению потребителей и оптимальную стоимость товара с точки зрения вашей компании.

Оптимальная цена по отношению к конкурентам — такая стоимость продукта, которая позволяет товару выглядеть привлекательным на фоне конкурентов. Оптимальная цена рассчитывается на основе принципов ценовой конкуренции, описанных в конкурентной стратегии компании и обычно представляет собой коридор цен (от и до), в рамках которого цена продукта будет обеспечивать конкурентоспособность товара.

Оптимальная цена по мнению потребителей — такая стоимость товара, которую потребитель готов отдать за продукт, зная его свойства и преимущества. Данный показатель является верхним пределом цены, выше которого стоимость продукта будет считать завышенной и будет ограничивать спрос.

Оптимальная цена с точки зрения компании — такая стоимость товара, которая позволяет получать требуемый уровень прибыли с продаж. У каждой компании есть свои нормативы по минимальной рентабельности продаж или требования к прибыльности товара. Данный показатель является нижним пределом цены, ниже которого реализация продукта не будет отвечать стратегии компании.

Основная таблица для расчета

Таблица, в которой мы будем рассчитывать 3 варианта стоимости продукта, выглядит следующим образом и включает в себя показатели, которые помогут принять правильное решение о итоговой стоимости.

Краткое описание всех показателей таблицы:

  • Себестоимость — сумма затрат, которую несет компания при производстве 1 единицы продукта
  • Отпускная цена — стоимость, по которой компания продает продукт с производства
  • Коэффициент розничной наценки — коэффициент для преобразования отпускной цены в розничную цену
  • Цена для потребителя — стоимость продукта (обычно розничная цена), по которой товар продается конечному покупателю
  • Рентабельность — % прибыли от реализации 1 единицы товара, рассчитывается от отпускной цены
  • Прибыль — абсолютный доход, который получает компания от продажи 1 единицы товара

После того, как будут определены три уровня цен, можно будет принять правильное решение об окончательной стоимости товара компании, которая с одной стороны будет максимизировать доход, а с другой стороны обеспечить конкурентоспособность товара.

Коэффициент розничной наценки

В таблице используется коэффициент розничной наценки, который обозначает суммарную наценку к товару после того, как он пройдет все звенья торговых посредников и достигнет конечного покупателя. Коэффициент будет равен 1, если товар продается компанией-производителем напрямую покупателю. Но часто возникают ситуации, когда товар сначала продается оптовому дилеру, затем перепродается розничной торговой сети и только с полок магазина продается конечному покупателю. В таком случае коэффициент розничной наценки будет больше 1. Как его определить?

Есть два способа расчета: снизу-вверх и сверху вниз. Метод снизу-вверх означает умножение цены товара на все наценки, которые он проходит. Метод сверху-вниз означает определение коэффициента с помощью цен уже продающихся товаров методом деления розничных цен текущих товаров на их отпускные цены. В нашем шаблоне подробно расписаны данные способы определения коэффициента.

Расчет себестоимости

Итак, чтобы начать заполнять основную таблицу, нам необходимо рассчитать себестоимость продукта. Это действие можно выполнить двумя основными способами: делением общих прогнозируемых затрат компании на предполагаемый объем выпуска товара или суммированием всех переменных и постоянных затрат для производства 1 единицы продукта.

Первый способ расчета себестоимости

Часто компания не может точно рассчитать, сколько она тратит на производство 1 единицы товара. Но она знает, сколько штук товара планирует продавать в месяц (или год), и может оценить свои суммарные затраты на производство планируемого объема. В таком случае для получения себестоимости 1 единицы продукта ей необходимо разделить общую сумму затрат на целевой объем выпуска.

Данный расчет применим для сферы услуг и для B2B рынка.

Второй способ расчета себестоимости

Когда же компания может точно выделить затраты, требуемые на производство 1 единицы продукции, ей целесообразнее использовать более подробный способ расчета себестоимости: суммирование переменных и постоянных затрат. Переменные затраты зависят от объема выпуска продукта, а постоянные затраты не зависят от объема продаж.

Такой способ больше подходит для компаний, работающих на рынке потребительских товаров.

Анализ конкурентов

После того, как определена себестоимость товара, нам необходимо определить диапазон цен, в котором товар будет выглядеть привлекательно на фоне основных конкурентов. Для этого нам необходимо определить стратегию ценового позиционирования по отношению к каждому конкуренту, провести анализ цен конкурентов и сформировать коридор розничных цен, в котором новый товар будет привлекателен для потенциальных покупателей.

Стратегия ценового позиционирования — субъективная оценка маркетолога, которая формируется на основе знания свойств, силы бренда и интенсивности продвижения товаров конкурентов. Стратегия устанавливается по отношению к каждому конкуренту.

Исследование потребителей

Следующим шагом важно узнать, сколько готовы платить за новый товар потребители. Для этого необходимо провести опрос целевой аудитории и оценить воспринимаемую ценность нового продукта (стоимость, которую готов отдать потребитель за продукт, зная все его свойства).

Для расчета воспринимаемой ценности продукта (ВЦ) проводиться сравнительное потребительское тестирование, в рамках которого определяется ВЦ основных конкурентов и нового товара. Затем необходимо сравнить воспринимаемую ценность конкурентов с их реальной ценой и определить разницу (в %) между данными показателями.

Для того, чтобы рассчитать наиболее привлекательную розничную цену для нового товара, следует взять максимальную разницу между воспринимаемой и реальной ценой конкурентов.

Окончательный расчет

Теперь можно вернуться к основной таблице расчета и определить три цены:

  • Для определения оптимальной цены с точки зрения компании все расчеты ведутся от целевой рентабельности продукта (в нашем примере это 45%).
  • Для определения оптимальной цены с точки зрения конкурентов все расчеты ведутся от максимальной цены «коридора конкурентных цен» (в нашем примере это 380 рублей)
  • Для определения оптимальной цены с точки зрения потребителей все расчеты ведутся от цены, рассчитанной по методу воспринимаемой ценности (в нашем примере это 368 рублей)

Теперь, зная три ограничителя, мы можем определить окончательную отпускную цену нашего товара: она должна быть не ниже стоимости по рентабельности, не выше цены от конкурентов, максимально приближена к цене от потребителей.

Комментарий от автора

Не всегда три «цены — ориентира» получается такими, что описанная формула расчета окончательной цены работает. Но в любом случае эти три показателя представляют собой три разных метода установления цены, три разных точки зрения. И если между ними наблюдается сильный разброс, значит на этапе планирования были сделаны не совсем верные предположения относительно целевой себестоимости, объема продаж и нормы прибыли.

Когда существует значимый разброс, необходимо устанавливать такую цену, которая наилучшим способ отражает приоритеты компании.

Готовые решения

Скачать готовый пример расчета цены товара в формате Excel. Пример полностью подготовлен по методике, описанной в данной статье.

Калькуляция себестоимости продукции в Excel

Калькуляция себестоимости продукции – определение расходов в денежном эквиваленте, приходящихся на единицу товаров, работ либо услуг. В расчет включаются прямые и косвенные затраты. Прямые – расходы на материалы, зарплата рабочих и т.п. Косвенные расходы: плановая прибыль, транспорт и др.

Читать еще:  Как сделать фамилию и инициалы в excel?

Подробно рассматривать калькуляционные статьи мы не будем. Автоматизируем процесс расчета плановой себестоимости продукции с помощью формул Excel. Наша задача – составить таблицу средствами Excel так, чтобы при подстановке данных автоматически считалась себестоимость товаров, работ, услуг.

Калькуляция себестоимости товаров в торговле

Учиться считать себестоимость лучше начинать со сферы торговли. Здесь меньше статей затрат. По сути – закупочная цена, выставленная поставщиком; транспортные расходы на доставку товара на склад; пошлина и таможенные сборы, если мы ввозим товар из-за рубежа.

Для расчета заполним следующую таблицу:

Берем некую группу товаров. И по каждой из них рассчитаем себестоимость. Последний столбец – плановый коэффициент себестоимости – покажет уровень затрат, который понесет компания на доставку продукции.

  1. Транспортные расходы, как сообщил отдел логистики, составят 5% от закупочной цены.
  2. Размер пошлины будет отличаться по разным группам товаров. Для товара 1 и 4 – 5%. Для товара 2 и 3 – 10%. Чтобы было удобнее проставлять проценты, отсортируем данные по столбцу «Наименование товара».
  3. Для вычисления используем формулу: закупочная цена + транспортные расходы в денежном выражении + пошлина в денежном выражении.
  4. Формула для расчета планового коэффициента – себестоимость в денежном выражении / закупочную цену.

Уровень затрат на доставку товаров 1 и 4 составит 10%, 2 и 3 – 15%.

Формулы расчета плановой себестоимости продукции в Excel

Каждая компания рассчитывает плановую себестоимость по-своему. Ведь предприятия несут разные затраты в зависимости от вида деятельности. Любая калькуляция должна содержать расшифровку расходов на материалы и заработную плату.

Расчет плановой себестоимости начинается с определения стоимости используемого сырья и материалов, которые нужны для производства товаров (которые непосредственно участвуют в технологическом процессе). Затраты на сырье включаются в себестоимость по утвержденным на предприятии нормам минус технологические потери. Эти данные можно взять в технологическом или производственном отделе.

Отразим нормы расхода сырья в таблице Excel:

Здесь нам удалось автоматизировать только один столбец – расход с учетом технологических потерь. Формула: =E3+E3*F3.

Обратите внимание! Для столбца «Потери» мы выставили процентный формат. Только в таком случае программа посчитает правильно. Нумерацию строк начинаем выше шапки. Если данные перепутаются, их можно восстановить по номерам.

Зная нормы, можем рассчитать стоимость материалов (расчет ведется для тысячи единиц товара):

В этой таблице вручную придется заполнить только одну графу – «Цена». Все остальные столбцы ссылаются на данные листа «Нормы». В графе «Стоимость» работает формула: =D3*E3.

Следующая статья прямых затрат – зарплата производственных рабочих. Учитывается основная заработная плата и дополнительная. По каким принципам начисляется зарплата (сдельная, повременная, от выработки), можно узнать в бухгалтерии.

В нашем примере расчет заработной платы ведется по нормам выработки: сколько должен сделать работник определенной квалификации за единицу рабочего времени.

Данные для вычислений таковы:

Расценка рассчитывается по формуле: =C3*D3.

Теперь мы можем посчитать основную зарплату рабочих:

Чтобы заполнить первые два столбца, не считая номер по порядку, мы связали данные этой таблицы с данными предыдущей. Формула для начисления премии: =C3*30%. Основной зарплаты – =C3+D3.

Дополнительная заработная плата – это все выплаты, положенные по закону, но не связанные с производственным процессом (отпуска, вознаграждения за выслугу лет и т.п.).

Другие данные для расчета калькуляции себестоимости мы сразу внесли в таблицу:

В столбце «Расчет показателя» указано, откуда мы берем данные. Если мы ссылаемся на другие таблицы, то используем оттуда итоговые суммы.

Шаблон расчета себестоимости продукта с формулами:

Для расчета калькуляции себестоимости упаковок взяты условные показатели амортизации ОС, процентов дополнительной зарплаты и налогов, обязательных страховых взносов.

Формирование розничной цены товара в Excel инструментом ПОДБОР ПАРАМЕТРА

Программа Excel используется на любом предприятии, которое занимается продажами. Это магазины, склады, фирмы-производители, перекупщики и т.п. Одна из основных задач такого предприятия – формирование розничной цены, которая может зависеть от разных факторов и меняться с течением времени.

Научимся быстро рассчитывать в Excel повышение цены, находить ее среднее значение, а также формировать розничную цену и одновременно узнавать ожидаемую прибыль от продаж.

Как посчитать процент повышения цены

Пример очень простой. Имеем таблицу, где прописаны цены товаров. В какой-то момент поставщики (молочная ферма и хлебокомбинат) стали продавать дороже, поэтому отпускные цены тоже придется поднять. Было установлено, что процент увеличения цены на молочную продукцию составил 8, а на хлебобулочные изделия – 5.

Создаем два дополнительных столбца. В одном прописываем установленный процент повышения; в другом будет считаться новая цена. Проценты прописываем вместе со значком %.

Рассчитаем новые цены по простейшей формуле. Запишем ее один раз, а затем протянем вниз.

В ячейке E3 записываем формулу =C3+C3*D3. Расшифровать ее просто: для подсчета новой цены молока нужно прибавить 8% к старой цене. Что мы и сделали. Протянем формулу вниз и получим результаты.

Произведем дополнительное действие: сократим цену до одного знака после запятой. Выделим нужные столбцы и на вкладке ГЛАВНАЯ нажмем кнопку УМЕНЬШИТЬ РАЗРЯДНОСТЬ.

Как посчитать среднюю цену

Возьмем тот же пример, но немного видоизменим исходные данные. Мы имеем информацию по ценам в нескольких филиалах магазинов. Нужно подсчитать среднюю цену каждого товара. Т.е. найти среднее арифметическое.

Воспользуемся функций СРЗНАЧ (среднее значение). Вписываем его в ячейку G3 и выделяем 4 предыдущие цены. Программа автоматически подсчитает среднее арифметическое. Протянем формулу вниз и уменьшим разрядность, как в прошлом примере.

Как подобрать цену товара

Цена продукции на производстве не берется из головы. Она рассчитывается, исходя из нескольких параметров. В частности, следует ориентироваться на целевую цену – сумму, полученную путем анализа данных (сбор информации у конкурентов, общий мониторинг рынка и т.п.). Чтобы не погружаться в теорию экономики, рассмотрим пример.

Имеем таблицу, в которой указаны издержки на какой-то товар. Они составляют 10000 рублей. Соответственно, цена товара не может быть меньше этой суммы, иначе предприниматель уйдет в минус. Чтобы бизнес приносил доход, цену нужно складывать из издержек и ожидаемой прибыли. Поставим последнюю равной 2000 рублей. Получается, что промежуточная цена товара составит 12000 рублей.

Не забываем также про НДС (18%) – обязательный платеж предпринимателя в пользу государства. Его тоже нужно приплюсовать к цене будущего товара. В нашем случае получилось, что продавать нужно по 14160 рублей.

Но на основании аналитических данных было получено значение целевой стоимости. Оно составляет 14000 рублей. Элементарным вычислением получили, что расхождение составило -160. Значит нужно подкорректировать значения, но т.к. все они связаны между собой формулами (хоть и несложными), сделать это вручную не получится. Воспользуемся функцией ПОДБОР ПАРАМЕТРА.

Ставим курсор в ячейку С9, где вычислено значение расхождения. На вкладке ДАННЫЕ выбираем АНАЛИЗ ЧТО-ЕСЛИ – ПОДБОР ПАРАМЕТРА.

Появляется окно, которое легко заполнить.

Каждое окошечко подробно расписано, поэтому несложно прочитать то, что мы сделали. Нам нужно добиться нулевого значения в ячейке С8. Что для этого нужно сделать с ячейкой С3 (ожидаемая прибыль), рассчитает программа. Нажимаем ОК.

В ячейке РАСХОЖДЕНИЕ стоит 0, значит, итоговая цена соответствует целевой. Т.е. продаваться товар будет по 14000, как и требовалось. Программа также рассчитала, что ожидаемая прибыль при таком раскладе составит 1864,4 рубля.

Ссылка на основную публикацию
Adblock
detector