Программа 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 рубля.
Гуру
(2724),
закрыт
13 лет назад
Зоя Денисова
Мудрец
(10053)
13 лет назад
Не поняла, при чём тут макросы.
Смысл такой, сделать ещё одну графу: Розничная цена, допустим, A – № п/п, B – оптовая цена, C – розничная цена.
Строка 2 – банан, и т. д.
В C2 надо вставить формулу =ЕСЛИ (B2>$B$9;B2*1,15;B2*1,1) и протянуть 
Аршавина 10Гуру (2724)
13 лет назад
оооо спасибо огроменное)))
вот как раз на счет формулы я и не догоняла…еще раз спасибо!!!
1. По какой формуле рассчитывают цену продажи товара по наценке?
Выберите правильный ответ:
a) Цена продажи = Себестоимость / (1 + Торговая наценка, %).
b) Цена продажи = Себестоимость * (1 + Торговая наценка, %).
с) Цена продажи = Себестоимость * (1 – Торговая наценка, %).
2. Интернет-магазин закупил партию товара по цене 680 руб. за одну единицу товара. Торговая наценка для данной категории товара составляет 35%. Какую розничную цену продажи необходимо установить для одной единицы товара?
Выберите правильный ответ:
a) 918 руб.
b) 1122 руб.
c) 840 руб.
3. Предприятие планирует выпуск нового изделия. Себестоимость одного изделия составляет 940 руб. Планируемая торговая наценка – 108%. Предприятие планирует продать партию в 1200 изделий. Сколько составит планируемая выручка от продаж всей партии изделий?
Выберите правильный ответ:
a) 1 037 760 руб.
b) 1 218 240 руб.
c) 2 346 240 руб.
4. Торговая компания закупила партию товара в количестве 3400 единиц товара на сумму 5 440 000 руб. Планируемая торговая наценка составляет 25%. Определите розничную цену продажи одной единицы товара.
Выберите правильный ответ:
a) 2000 руб.
b) 1200 руб.
c) 1600 руб.
Для того, чтобы сделать наценку на товар, нужно воспользоваться следующей формулой:
Цена с наценкой = Исходная цена + Исходная цена * Процент наценки.
Например, товар был приобретён за 1500 рублей, а процент наценки равен 10.
Окончательная цена = 1500 + 1500 * 10% = 1500 + 1500 * 0,1 = 1650 рублей.
**
В программе Excel цену товара с наценкой можно посчитать по-разному.
1) Если процент наценки для каждого товара свой, то поступаем так:
-
В один столбец записывается исходная товара.
-
Во второй столбец записывается процент наценки для данного товара.
-
Затем пишем формулу, в которой перемножается исходная цена и соответствующий процент наценки, результат складывается с исходной ценой.
Пример:
Например, для 1 товара формула будет следующей:
=B2+B2*C2
При этом формат ячеек в столбце C нужно сделать процентным – для этого есть специальная кнопка на панели инструментов “Главная”.
2) Если процент наценки одинаковый для всех товаров, то его можно записать в отдельной ячейке и сделать ссылку на неё абсолютной.
Также можно написать это значение непосредственно в формуле.
Пример:
Формула для 1 товара выглядит так:
=B2+B2*$C$10
В ячейке C10 записан процент наценки – чтобы сделать на неё абсолютную ссылку нажимаем на F4.
Затем копируем эту формулу в нижестоящие ячейки и получаем цену с наценкой для каждого товара.
Для того, чтобы посчитать наценку в процентах, нужно воспользоваться формулой:
Процент наценки = (Цена с наценкой – Исходная цена) / Исходная цена * 100%.
Например, товар был приобретён за 800 рублей, а продаётся за 1000 рублей.
Процент наценки = (1000 – 800) / 800 * 100% = 0,25 * 100% = 25%.
**
Вот пример реализации этой формулы в Excel:
В столбце B – исходная цена, в столбце C – цена с наценкой.
В столбце D будет процент наценки, формат всех ячеек этого столбца нужно сделать процентным.
Формула в ячейке D2 для 1 товара получится такой:
=(C2-B2)/B2
Так как у ячейки процентный формат, то умножать на 100% не нужно.
Теперь остаётся скопировать эту формулу во все ячейки столбца.
Подбор параметра... – это встроенный в Excel инструмент анализа , который показывает, как одно значение в формуле влияет на другое. Точнее, он определяет, какое значение следует ввести в ячейку ввода, чтобы получить желаемый результат в ячейке.
Пример использования инструмента
Давайте рассмотрим простой прием, чтобы увидеть, как работает инструемнт Подбор параметра.
Предположим, у нас есть очень простая таблица с данными, которая учитывает количество проданных единиц, розничную цену и скидку для расчета общей выручки.
Теперь предположим, что мы хотим узнать, сколько единиц нужно продать, чтобы получить доход в 20 000 рублей.
Одним из способов, это подобрать параметр вручную методом проб и ошибок.
Другой вариант – сделать обратный расчет и разделить цель на цену за единицу.
Третий вариант – наш вариант, при котором мы будем использовать функцию Подбора параметра в Excel.
Пошаговый порядок применения Подбора параметра:
- Поместите курсор в ячейку, содержащую результат, который вы хотите изменить, в нашем случае, это “Выручка, руб.” – ячейка A5.
- На вкладке «Данные» выбрать пункт «Анализ “что если”» и далее строку “Подбор параметра”.
3. Когда появится диалоговое окно установите в строке «Значение» равным нашему желаемому результату – 20 000
4. В строке «Изменяя значение ячейки» укажите ссылку на ту ячейку, в которой необходимо подобрать параметр, у нас это ячейка В2 (Количество товара, ед).
5. Нажмите ОК
В результате мы видим, что для получения дохода в 20 000 рублей необходимо продать 1176,47 единиц.
Обратите внимание, что решение отображается непосредственно в ячейке, а не в диалоговом окне.
Затем нажмите OK, чтобы согласиться с предложенным вариантом и записать значение в ячейке, или нажмите Отмена, чтобы вернуться к исходным данным.
Тот же трюк можно проделать и в отношении Цены товара, только вместо изменяемой ячейки указать ссылку, в которой установлена непосредственно Цена товара, руб. – ячейка В3
Операции с подбором параметра удобно и эффективно выполнять когда сам расчет сложный и в нем учитываются множество показателей
