Ценообразование

ЦЕНООБРАЗОВАНИЕ

Методические указания и задания для выполнения коллоквиума

Для студентов очной формы обучения

Могилёв 2011

«Расчет ЦЕНЫ затратным методом

С ПРИМЕНЕНИЕМ СРЕДСТВ ЭЛЕКТРОННЫХ ТАБЛИЦ MS Excel»

(практическое задание)

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

Эффективность применения представленных материалов обусловлена возможностью своевременно и быстро выполнять расчеты по обоснованию цены с учетом динамичной конъюнктура рынка" href="/text/category/kontzyunktura__kontzyunktura_rinka/" rel="bookmark">конъюнктуры рынка.

Плановое обоснование цены затратным методом (прямого счета).

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

Задачи:

- компьютерная реализация установленного алгоритма планового обоснования отпускной цены на изготовляемую продукцию;

-анализ влияния на отпускную цену выявленных резервов снижения себестоимости;

-выбор приемлемого варианта планового обоснования цены с учетом рыночной конъюнктуры.

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

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

I.1. Для выполнения задания в MS Excel надо создать две таблицы.

Одна таблица будет таблицей исходных данных - это таблица 1.1, другая - результирующей таблицей – это таблица 1.2. Для этого в Рабочей книге MS Excel на первом листе с именем Лист1 создать таблицу 1.1, заполнить исходные данные своего варианта и сохранить этот документ в папке ГРУППА_N под именем Задание1.

Таблица 1.1. Исходные данные варианта №1

№пп	Показатели	Исходные данные варианта _1_
1	Сырье и материалы	28000
2	Возвратные отходы	20
3	Вспомогательные материалы	360
4	Заработная плата	10200
5	Отчисления в ФСЗН,%	35
6	Отчисления в Белгосстрах, %	1
7	Амортизация	10100
8	Прочие расходы в % от полной себестоимости	5
9	Полная себестоимость
10	Норматив рентабельности, %	15
11	Ставка акциза, %	25

I.2. Наименования столбцов таблицы 1.1. поместить в диапазоне А1-С1.

Отформатировать их так, чтобы на экране была видна вся информация.

Указание. Выделить ячейку А1. В меню Формат выбрать команду Ячейки.

На закладке Выравнивание установить флажок Переносить по словам.

Нажать кнопку Ok.

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

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

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

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

I.3. Установить для шапки таблицы полужирный шрифт, отцентрировать ее, установить цвет заливки и обрамление по контуру.

Указание. Выделить ячейки А1-С1.

- Установить «полужирный» шрифт, нажав на кнопку на панели инструментов.

- Отцентрировать данные, нажав на кнопку По центру на панели инструментов.

- Выбрать требуемый цвет, нажав на кнопку Цвет заливки на панели инструментов или панели Рисование.

Установить обрамление ячеек, для этого на панели инструментов на кнопке Внешние границы щелкнуть на стрелке. Выбрать Все границы .

I.4. Название таблицы 1.1. при желании можно расположить в столбце D

Установить для текста «полужирный» шрифт, отцентрировать текст названия таблицы.

I.5. Ввести исходные данные своего варианта для расчетов в таблицу 1.1.

в столбец С диапазон С2:С15.

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

I.6. Аналогично на втором листе активной Рабочей книги MS Excel с именем Лист 2 создать таблицу 1.2. и сохранить этот документ в папке ГРУППА_N под именем Задание1.

Таблица 1.2. в Рабочей книге MS Excel на листе с именем Лист 2.

№пп	Показатели	Сумма, руб
1	Сырье и материалы
2	Возвратные отходы
3	Вспомогательные материалы
4	Заработная плата
5	Отчисления в ФЗН
6	Отчисления в Белгосстрах
7	Амортизация
8	Прочие расходы
9	Полная себестоимость
10	Прибыль
11	Акциз
12	Отпускная цена
13	НДС
14	Отпускная цена с НДС

I.7. Отфарматировать заголовок таблицы 1.2.

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

Алгоритм решения задачи

A1. Данные строк 1-4,7 таблицы 1.1. заносятся в таблицу 1.2. без изменений.

A2. Отчисления в фонд социальной защиты населения (ФСЗН) для целей ценообразования определяются по формуле:

Офсзн = ЗП х Сфсзн / 100,

где Офсзн- отчисления в ФСЗН, руб; ЗП - заработная плата, включаемая в цену изделия, руб (стр.4 табл. 1.1. табл. 1.2.);

Сфсзн - ставка отчислений в ФСЗН, % (стр.5 табл.1.1.). В настоящее время составляет 34%. Результат расчета заносится в стр. 5 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С6 по формуле : C6 =C5*Лист1!C6/100

A3. Очисления в Белгосстрах определяются по формуле:

Остр = ЗП х Сстр 100, где

Остр - отчисления в Белгосстрах, руб.;

Сстр - установленная законодательно ставка отчислений в Белгосстрах для предприятия. В задаче принимается равной 1%.

Результат расчета заносится в стр. 6 табл. 10.2.

В MS Excel данные вычисления будут производиться в ячейке С7 по формуле: C7 =С5*Лист1!С7/100

A4. Прочие расходы определяются по формуле:

Прасх = (СМ – ВО + ВМ + ЗП + Офсзн + Остр + Ам) х Ппр / (100 - Ппр),

Где СМ- затраты на сырье и материалы, руб (стр.1 табл. 1.1. и табл. 1.2.);

ВО - возвратные отходы, руб (стр.2 табл.1.1 . и табл. 1.2);

ВМ - затраты на вспомогательные материалы (стр.3 табл.1.1. и табл. 1.2)

Ам - амортизация, руб (стр.7 табл.1.1. и табл. 1.2)

Ппр - процент распределения прочих расходов, % (стр.8 табл. 1.1.)

Результат расчета заносится в стр. 8 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С9 по формуле: С9 =(C2-C3+C4+C5+C6+C7+C8)*Лист1!C9/(100-Лист1!C9)

A5. Полная себестоимость определяется суммированием всех элементов себестоимости с учетом установленного процента прочих расходов по формуле: Сед = (СМ – ВО + ВМ + ЗП + Офсзн + Остр + Ам) х 100 / (100 - Ппр).

Результат расчета заносится в стр. 9 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С10 по формуле: C10 =(C2-C3+C4+C5+C6+C7+C8)*100/(100-Лист1!C9)

A6. Выполняется контрольная проверка элементов цены в табл.1.1:

стр.9 = сумме строк (1,3-8) за вычетом стр.2.

A7. Расчет прибыли в цене товара на основе нормативной или приемлемой для предприятия нормы рентабельности осуществляется по формуле:

П = Р х Сед / 100,

где П - прибыль, руб.; Р- норматив рентабельности, % (стр. 10 табл. 1.1.)

Результат расчета заносится в стр. 10 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С11 по формуле: C11 =C10*Лист1!C11/100

A8. Расчет акцизов для подакцизных товаров, установленных по адвалорной ставке Са, осуществляется по формуле:

А = (Сед + П) х Са / (100 - Са),

где Са –ставка акциза, % (стр.11 табл.1.1.)

Результат расчета заполняется в стр.11 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С12 по формуле: C12 =(C10+C11)*Лист1!C12/(100-Лист1!C12)

A9. Определение отпускной цены предприятия-изготовителя

Цотп = Сед + П + Ак

Результат расчета заполняется в стр. 12 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С13 по формуле: С13 = C10+C11+C12

A11. Определение НДС по формуле

НДС = Цотп х Сндс,

где Сндс - ставка налога на добавленную стоимость, %.

Результат расчета заполняется в стр. 13 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С14 по формуле: С14 = C13*Лист1!C13/100

A12. Определение отпускной цены предприятия – изготовителя с НДС

Цотп. ндс = Цотп + НДС

Результат расчета заполняется в стр. 14 табл. 1.2.

В MS Excel данные вычисления будут производиться в ячейке С15 по формуле: С15 =C14+C13

Результаты работы оформить по форме калькуляции:

Плановая калькуляция по расчету отпускной цены на ______(продукт)

№пп	Показатели	Сумма, руб
1	Сырье и материалы	28000
2	Возвратные отходы(вычитаются)	20
3	Вспомогательные материалы	360
4	Заработная плата	10200
5	Отчисления в ФСЗН	3570
6	Отчисления в Белгосстрах	102
7	Амортизация	10100
8	Прочие расходы	2755
9	Полная себестоимость	44967
10	Прибыль	6745
11	Акциз	17237
13	Отпускная цена предприятия-изготовителя	70357
14	НДС	12664
15	Отпускная цена предприятия-изготовителя с НДС	83021

Выполнил _____________________(фамилия студента)

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

Далее необходимо провести структурный анализ состава цены с построением диаграммы. На листе 3 создаем таблицу 1.3.

Таблица 1.3. Анализ структуры цены на ________( наименование продукта)

Показатели	Сумма, руб	Уд. вес в цене, %
Себестоимость	44967	0,54
Прибыль	6745	0,08
Налоги и отчисления	31309	0,38

По результатам строим диаграмму.

Указание.

Выделить диапазон ячеек для построения диаграммы, для чего выделить мышью нужные ячейки в данном случае это В3С3.

Выбрать на панели инструментов кнопку Мастер диаграмма , в окне построения диаграммы выбрать тип и вид диаграммы и нажать на кнопку Готово. Круговую диаграмму можно построить на основе одного ряда данных.

Если полученный результат отпускной цены не удовлетворяет предприятие-изготовитель с учетом сложившейся конъюнктуры рынка, то следует рассмотреть возможность снижения себестоимости за счет реализации существующих резервов и рассчитать влияние снижения себестоимости на 2, 5, 7 и 10% на отпускную цену по вышеприведенному алгоритму.

По результатам заполнить в MS Excel табл. 1.4., которую создать на Листе 4 Рабочей книги.

Таблица 1.4.Анализ влияния выявленных резервов снижения себестоимости на отпускную цену

Показатели	Сумма, руб	Снижение себестоимости на
2%	5%	7%	10%
Полная себестоимость	58,61	57,44	55,68	54,51	52,75
Прибыль	8,79	8,62	8,35	8,18	7,91
Акциз	22,47	22,02	21,34	20,89	20,22
Отпускная цена предприятия-изготовителя	94,54	91,64	88,84	86,97	84,16
НДС	17,02	16,50	15,99	15,65	15,15
Отпускная цена предприятия-изготовителя с НДС	111,56	108,14	104,83	102,62	99,31

I.8. В качестве первоначального значения полной себестоимости (стр. 1 табл. 1.4.) взять соответствующее значение из табл. 1.2 на Листе 2 и ввести в столбец "Сумма, руб.", т. е. в ячейку В3 на Листе 4 Рабочей книги.

I.9. Для получения сниженного на 2% значения себестоимости в столбец

"Снижение себестоимости на 2%" в ячейку С3 ввести формулу =$В3-$В3*2%.

Аналогично, для 5% и т. д.

То есть D3 = =$В3-$В3*5%;

E3 ==$В3-$В3*7%

F3 = =$В3-$В3*10%

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

- для определения значения прибыли (стр. 2 табл. 1.4.) формула представлена в пункте А7 алгоритма решения задачи № 1.

В MS Excel данные вычисления будут производиться в ячейке С4 по формуле: С4 =C3*Лист1!$C11/100;

- для определения значения акциза (стр. 3 табл. 1.4.) – в п. А8.

В MS Excel данные вычисления будут производиться в ячейке С5 по формуле: С5 =(C3+C4)*Лист1!$C13/(100-Лист1!$C13);

- для определения значения отпускной цены предприятия-изготовителя

(стр. 5 табл. 1.4.) – в п. А10.

В MS Excel данные вычисления будут производиться в ячейке С6 по формуле: С6 =C3+C4+C5;

- для определения значения НДС (стр. 6 табл. 1.4.) – в п. А11.

В MS Excel данные вычисления будут производиться в ячейке С7 по формуле:

С7 =C6*Лист1!$C14/100;

- для определения значения отпускной цены предприятия-изготовителя с НДС (стр. 6 табл. 1.4.) – в п. А12.

В MS Excel данные вычисления будут производиться в ячейке С8 по формуле:

С8 =C7+C6

Результаты вычислений представлены в таблице 1.4.

Замечание 1.

Абсолютные ссылки (знак $) используется в этих формулах для удобства последующего копирования этих формул вдоль соответствующих строк.

I.11. Выделить диапазон С3:С9 с формулами для определения значений показателей из строк 2-7 табл. 1.4. и затем все эти формулы скопировать вдоль соответствующих строк до конца таблицы.

I.12. Для удобства построения графика по значениям строк 1 и 7 табл. 1.4. эти значения скопировать из данной табл. 1.3. в диапазон, лежащий за пределами данной таблицы, например, B12:G13.

Указание. Для этого выделить ячейки, содержащие данные первой строки таблицы (это первоначальное значения полной себестоимости и сниженных на 2%, 5%, 7% и 10% значений себестоимости), за исключением данного из столбца "№ строки таблицы" выделить и нажать на панели инструментов пиктограмму Копировать.

Затем выделить диапазон B12:G12, вызвать контекстное меню этого диапазона, выбрать функцию Специальная вставка, а затем опцию Значение. Нажать кнопку Ok.

Аналогично с помощью функции Специальная вставка и опции Значение скопировать перенести данные 7-ой строки таблицы.

I.13. Построить график зависимости отпускной цены изготовителя с НДС от себестоимости и поместить его на новый листе под именем График1 дать название

«График зависимости отпускной цены изготовителя с НДС от себестоимости».

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

Выбрать на панели инструментов кнопку Мастер диаграмма

1-ый шаг: Выбрать тип графика и нажать на кнопку Готово.

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

Если же снижение себестоимости представляется весьма затруднительным, целесообразно рассмотреть возможность снижения установленного норматива рентабельности. В этом случае заполнить в MS Excel табл. 1.5, которую создать на Листе 5 активной Рабочей книги.

Таблица 1.5. Анализ влияния на отпускную цену норматива установленной рентабельности

№ строки таблицы	Показатели	Рентабельность, %
15	16	17	20
1	Прибыль
2	Акциз
4	НДС
5	Отпускная цена предприятия-изготовителя с НДС

I.14. Для определения значений показателей строк 1-5 табл. 1.5. в столбцы "Рентабельность, % " ввести соответствующие формулы:

для определения значения прибыли (стр. 1 табл. 1.5.) формула представлена в пункте А7 алгоритма решения задачи № 1, а именно: П = Сед х Р / 100 или П = Сед х Р %, где в качестве Сед – значение полной себестоимости принимается значение ячейки Лист!2С10 или Лист!3С3; а в качестве Р – 15, для столбца "Рентабельность, % 15", затем 16, потом 17, и наконец 20 для соответствующих столбцов.

В MS Excel данные вычисления будут производиться в ячейках С3-F3 по формулам, соответствующим своему значению рентабельности: например, для столбца "Рентабельность, % 15" нужная формула в ячейке С3 будет иметь вид:

С3 =Лист2!$C$10*15%

Замечание 2.

а). Абсолютные ссылки (знак $) используется в этих формулах также для удобства последующего копирования этих формул вдоль соответствующих строк.

б). Значения процента рентабельности можно также задавать не конкретным числом 15, 16, 17, 20, а как содержимое соответствующих ячеек С2, D2, E2,F2 Рабочего листа MS Excel.

Определение значения акциза (стр. 2 табл. 1.5.) осуществляется в соответствии с п. А8.

В MS Excel данные вычисления будут производиться в ячейке С4 по формуле:

С4 =(Лист2!$C$10+C3)*Лист1!$C$12/(100-Лист1!$C$12);

Расчет значения отпускной цены предприятия-изготовителя (стр. 3 табл. 1.5.) осуществляется в соответствии с п. А10.

В MS Excel данные вычисления будут производиться в ячейке С5 по формуле:

С5 = Лист2!$C$10+C3+C4;

Определение значения НДС (стр. 4 табл. 1.4.) выполняется в соответствии с п. А11.

В MS Excel данные вычисления будут производиться в ячейке С6 по формуле:

C6=C5*Лист1!$C14/100;

Расчет отпускной цены предприятия-изготовителя с НДС (стр. 5 табл. 1.5.) – в п. А12.

В MS Excel данные вычисления будут производиться в ячейке С7 по формуле:

C7 =C6+C5

I.15. Выделить диапазон B3:B8 с формулами для определения значений показателей из строк 1-6 табл. 1.5. и затем все эти формулы скопировать вдоль соответствующих строк до конца таблицы.

I.16. Для удобства построения графика по значениям строк 1 и 6 табл. 1.5. эти значения скопировать из данной табл. 1.5. в диапазон, лежащий за пределами данной таблицы, например, B10:F11. Выполнить это аналогично как в п. I.12.

I.17. Аналогично п. I.13. построить график зависимости отпускной цены изготовителя с НДС от норматива рентабельности для расчета прибыли, включаемой в отпускную цену и поместить его на новый лист под именем График2 и озаглавить «График зависимости отпускной цены изготовителя с НДС от норматива рентабельности».

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

I.18. Сделать выводы.

Разработанные методические материалы для проведения практических занятий по курсу «Ценообразование» будут способствовать улучшению подготовки студентов дневной и заочной и дистанционной форм обучения, обеспечивают приобретение конкретных практических навыков выполнения экономических задач по обоснованию цен на выпускаемую продукцию в системе Excel.

Таблица 1.6.Исходные данные для расчета

цены затратным методом (для выполнения практического задания)

№	Показатели	Варианты
1	2	3	4	5	6	7	8	9	10
1	Сырье и материалы	28	30	32	34	36	38	40	42	44	46
2	Возвратные отходы (вычитаются)	0,2	0,3	0,4	0,5	0,6	0,7	0,8	0,9	1	1,1
3	Вспомогательные материалы	3,6	3,8	4	4,2	4,4	4,6	4,8	5	5,2	5,4
4	Заработная плата	10,2	10,5	10,8	11,1	11,4	11,7	12	12,3	12,6	12,9
5	Отчисления в ФСЗН по ставке, %	34
6	Отчисления в Белгосстрах	1
7	Амортизация основных средств и нематериальных активов	10,1	10,2	10,3	10,4	10,5	10,6	10,7	10,8	10,9	11
8	Прочие расходы в % от полной себестоимости	5
9	Полная себестоимость
10	Норматив рентабельности для включения прибыли в цену в % от полной себестоимости	15	16	17	18	19	20	21	22	23	24
11	Ставка акциза, %	25
13	НДС по ставке, %	20

РАБОТУ «КОЛЛОКВИУМ ПО ЦЕНООБРАЗОВАНИЮ» РАСПЕЧАТАТЬ, ПОДШИТЬ В ПАПКУ И СДАТЬ.