Расчет себестоимости изделия

 

Цель задачи: ознакомиться с технологией расчета себестоимости изделия, реализованной в Excel.

Предлагается, пользуясь встроенными функциями Excel:

1) рассчитать себестоимость 1 изделия, выпускаемого на предприятии;

2) проанализировать структуру затрат предприятия в себестоимости изделия с целью снижения себестоимости производства изделий.

При округлении в расчетах учитывать 3 знака после запятой.

Исходные данные для задания 7 представлены в таблице 1.8, которая для удобства восприятия информации разбита на блоки:

Таблица 1.8

Наименование затрат	Значение
Время, затраченное на 1 изделие, час	4,5
Количество рабочего времени в месяц на изготовление изделий, час	155
Фонд оплаты труда бригады по производству изделий в месяц (ФОТ), руб.	20000
Начисления на ФОТ
Пенсионный фонд, %	33,2
Фонд соц. страхования по временной утрате нетрудоспособности, %	1,4
Фонд безработицы, %	1,6
Фонд несчастных случаев, %	1,8
Стоимость 1 КВт электроэнергии, руб./КВт	0,8
Количество электроэнергии в месяц, используемой для производства изделий, КВт	550,0
Стоимость других ресурсов для производства изделий в месяц, руб.	2500
Расход материала на 1 изделие, кг	1,50
Стоимость 1 т материала, руб.	98000,00
Внутризаводские расходы в месяц, руб.	350000
% внутризаводских расходов на цех по производству изделий	35,00
Внутрицеховые расходы в месяц, руб.	120000
% внутрицеховых расходов на изделие	45,00

 

Для расчетов понадобятся следующая функция Excel:

· ОКРУГЛ() – для нахождения округления числа.

Алгоритм расчетов

Рассчитаем количество изготавливаемых в месяц изделий в Excel по формуле (см. рис. 9):

=ОКРУГЛ(B3/B2;3),

где B3 – ячейка Excel, в которой хранится значение величины количества рабочего времени в месяц на изготовление изделий;

B2 – ячейка Excel, в которой хранится значение времени, затраченного на изготовление 1 изделия.

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

Рассчитаем затраты на оплату труда на изделия в месяц в Excel по формуле:

=ОКРУГЛ(B4*(100+B6+B7+B8+B9)/100;3),

где В4 – ячейка Excel, в которой хранится значение величины ФОТ;

В6-В9 – ячейки Excel, в которых хранится значения величин начислений на ФОТ.

Рассчитаем стоимость электроэнергии на изготовление изделий в месяц в Excel по формуле:

=ОКРУГЛ(B10*B11;3),

где В10 – ячейка Excel, в которой хранится значение величины стоимости 1 КВт электроэнергии;

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

Рассчитаем стоимость других ресурсов для производства изделий в месяц по формуле в Excel:

=B12,

где В12 – ячейка Excel, в которой хранится значение величины стоимости других ресурсов для производства изделий в месяц.

Рассчитаем расходы в месяц на материал для изготовления изделий в Excel по формуле:

=ОКРУГЛ(B13*D4*B14/1000;3),

где В13 – ячейка Excel, в которой хранится значение величины расхода материала на изготовление 1 изделия;

В14 – ячейка Excel, в которой хранится значение величины стоимости 1т материала;

D4 – ячейка Excel, в которой хранится значение количества изделий, изготавливаемых в месяц.

Рассчитаем стоимость в месяц внутризаводских и внутрицеховых расходов на изготовление изделий в Excel по формуле:

=ОКРУГЛ((B15*B16/100+B17)*B18/100;3),

где В15 – ячейка Excel, в которой хранится значение величины общих внутризаводских расходов в месяц;

В16 – ячейка Excel, в которой хранится значение величины процента внутризаводских расходов, направляемых на цех, в котором производятся изделия;

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

В18 – ячейка Excel, в которой хранится значение величины процента внутрицеховых расходов на изготовление изделий.

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

=D7+D10+D12+D15+D17,

где D7 – ячейка Excel, в которой хранится значение величины затрат на оплату труда при изготовлении изделий в месяц;

D10 – ячейка Excel, в которой хранится значение величины стоимости в месяц электроэнергии на изготовление изделий;

D12 – ячейка Excel, в которой хранится значение величины стоимости в месяц других ресурсов для производства изделий;

D15 – ячейка Excel, в которой хранится значение величины расходов в месяц на материал для изготовления изделий;

D17 – ячейка Excel, в которой хранится значение величины стоимости в месяц внутризаводских и внутрицеховых расходов на изготовление изделий.

Рассчитаем себестоимость изготовления 1 изделия в Excel по формуле:

=ОКРУГЛ(D19/D4;3),

где D19 – ячейка Excel, в которой хранится значение величины всех учтенных расходов в месяц, которые предприятие затрачивает при изготовлении изделий;

D4 – ячейка Excel, в которой хранится значение количества изделий, изготавливаемых в месяц.

Алгоритм расчетов на листе Excel с указанием используемых формул и полученными результатами представлен на рисунке 9.

Расчеты структуру затрат предприятия в себестоимости изделия представлены на рис. 9. Анализ полученных результатов по структуре затрат позволил сделать следующие выводы:

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

· следующими по размерам доли в себестоимости изготовления изделий являются затраты по ФОТ (чуть более 19%). За счет пересмотра тарифных ставок или сокращения численности персонала бригады, которая непосредственно занимается производством изделий, возможно снижение этой части затрат. При этом нужно учесть, что уменьшение ФОТ на 100 руб. приводит к уменьшению обязательных платежей по обслуживанию ФОТ (38% от ФОТ) на 38 руб. Итого экономия ФОТ на 100 руб. приводит к экономии этой части затрат на производство изделий на 138 руб.;

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

 

Рис. 9. Алгоритм расчетов по 7 заданию

РЕШЕНИЕ ЗАДАЧИ ОПТИМИЗАЦИИ

 

Цель задачи: ознакомиться с технологией решения задачи оптимизации с помощью средств Excel.

Предприятие выпускает 3 вида продукции (вектор-столбец Х _j). Каждый вид продукции может быть получен при помощи трех различных технологических процессов, которые имеют ограничения по размеру затрат ресурсов предприятия по времени производственного процесса (вектор-столбец В _j). Ресурсы, необходимые для производства каждого вида продукции каждой из имеющихся технологий определяется матрицей затрат ресурсов А _{і j}. Доход от реализации каждого вида продукции определяется вектором-столбцом С _і.

Предлагается, пользуясь встроенными функциями и мастером поиска решения Excel:

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

2) провести анализ полученных результатов.

Исходные данные для задания 8 представлены в таблице 1.9:

Таблица 1.9

Значение индекса j\i	Элементы матрицы А і j			Вектор В j
	1	2	3
1	1	2	1	7
2	-3	2	6	8
3	-2	0	-5	9
Вектор С j	2	-10	3

 

Для расчетов понадобится следующая функция Excel:

· СУММПРОИЗВ() – для расчета произведения векторов (получения суммы произведения компонент векторов).

 

Алгоритм расчетов

Рассматриваемая задача относится к классу задач оптимизации, постановка которых сводится к следующему: максимизировать c ₁ x ₁+ c ₂ x ₂+ c ₃ x ₃ при ограничениях:

a ₁₁ x ₁ + a₁₂x₂ +. + a₁₃x₃ ≤ b ₁;

a ₂₁ x ₁ + a₂₂x₂ +. + a₂₃x₃ ≤ b ₂;

a ₃₁ x ₁ + a₃₂x₂ +. + a₃₃x₃ ≤ b₃,

x_j ≥ 0, j =1, 2,., n.

где a _1j, a _2j, a _3j – ресурсы, необходимые для обработки единицы 1-го вида продукции x_j имеющимися в распоряжении способами (технологиями) соответственно (j= 1,2,3; x_j – принимают целые значения);

b ₁, b ₂, b ₃ – ограничения на ресурсы предприятия для каждой і -й технологии соответственно (і = 1,2,3).

Запишем поставленную задачу в общем виде: максимизировать при ограничениях

, i= 1,2,., m,

x_j ≥ 0, j =1, 2,., n.

В матричном виде задача записывается следующим образом: максимизировать С ^TХ при ограничениях:

AХ ≤В;

Х ≥ 0.

Для корректного использования мастера поиска решения Excel необходимо проделать следующую подготовку к расчетам (см. рис. 10):

1) На листе Excel расположим ячейки, куда поместим первоначальные значения вектора Х (эти значения, пусть несущественно, влияют на точность расчетов. Поэтому присвоим всем компонентам вектора Х значение 1).

2) На листе Excel расположим ячейку, куда поместим значение целевой функции c ₁ x ₁+ c ₂ x ₂+ c ₃ x ₃, которое рассчитаем по формуле:

=СУММПРОИЗВ(B8:D8;B11:D11),

где B8:D8 – компоненты вектора С в Excel;

B11:D11 – компоненты вектора Х, задаваемого пользователем, в Excel.

3) На листе Excel расположим ячейки, куда поместим построчное перемножение матрицы А и вектора Х. Для первой строки формула Excel имеет вид:

=СУММПРОИЗВ(B4:D4;B$11:D$11),

где B4:D4 – компоненты первой строки матрицы А в Excel;

B$11:D$11 – компоненты вектора Х, задаваемого пользователем, (для удобства работы в Excel по столбцам используется знак $, фиксирующий строку, в которой помещены значения компонент вектора Х).

Рис. 10. Подготовка к расчетам по 8 заданию

Для расчетов воспользуемся возможностями Excel.

Для этого в закладке «Сервис» мышкой выберем подзакладку «Поиск решения». На экране появится диалоговое окно (см. рис. 11):

Рис. 11. Диалоговое окно подзакладки «Поиск решения»

 

В окне «Установить целевую ячейку:», нажимая мышкой на цветную кнопку рядом с этим окном, мышкой выделяем ячейку на листе Excel, в которой должно находиться решение (на рис. 10 – это ячейка G3). На рис. 11 показан результат этой операции.

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

В окне «Изменяя ячейки:» аналогичным способом, с помощью мышки, выберем ячейки на листе Excel, в которых располагаются значения компонент вектора Х (в начале расчетов ячейки пустые: см. рис. 10). На рис. 11 показан результат этой операции.

В окне «Ограничения:», нажимая мышкой на кнопку «Добавить» рядом с этим окном, определяем ограничения по поиску решения задачи. Появляется меню «Добавление ограничения», состоящее из 3 окон и 4 кнопок (см. рис.12).

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

После этого нажимаем кнопку «Добавить» для определения следующего ограничения:

Х ≥ 0.

Нажимаем мышкой цветную кнопку рядом с окном «Ссылка на ячейку» и выделяем ячейки, в которых расположены компоненты вектора Х. В среднем окне, нажимая мышкой на стрелочку, устанавливаем вариант выбора «>=». В окне «Ограничение:» с помощью клавиатуры устанавливаем значение «0». На рис. 13 показан результат этой операции.

Рис. 12. Окно «Добавление ограничения» для первого ограничения задачи

Рис. 13. Окно «Добавление ограничения» для второго ограничения задачи

 

После этого нажимаем кнопку «Добавить» для определения следующего ограничения:

AХ ≤В.

Нажимаем мышкой цветную кнопку рядом с окном «Ссылка на ячейку» и выделяем ячейки, в которых расположены компоненты вектора Х, перемноженные построчно на компоненты матрицы А. В среднем окне, нажимая мышкой на стрелочку, устанавливаем вариант выбора «<=». В окне «Ограничение:» с помощью мышки, нажав на цветную кнопку рядом с окном, выделяем ячейки, в которых расположены компоненты вектора В. На рис. 14 показан результат этой операции.

Рис. 14. Окно «Добавление ограничения» для третьего ограничения задачи

 

Все ограничения задачи определены – нажимаем на кнопку «ОК» диалогового окна «Добавление ограничений». На рис. 15 показан результаты внесения всех ограничений по задаче.

Рис. 15. Результаты внесения всех ограничений в окне «Поиск решения»

 

Далее нужно определить параметры решения задачи оптимизации. Для этого нажимаем мышкой на кнопку «Параметры» и назначаем значения параметров поиска решения. После выбора параметров нажимаем на кнопку «ОК» диалогового окна «Параметры поиска решения». На рис. 16 показан результат этой операции.

Рис. 16. Результаты выбора параметров поиска решения задачи

 

ПРИМЕЧАНИЯ: