Анализ издержек и оптимизация функционирования предприятия в условиях ограниченных ресурсов средствами электронных таблиц, страница 2

Так как мы заполнили всю таблицу, то теперь у нас появилась возможность провезти анализ издержек предприятия, разложив суммарные издержки на постоянные и переменные методом наименьших квадратов, где  b=,  а= Yc - b* Хс.

Разложим постоянные и переменные издержки для каждого вида продукции, воспользовавшись   встроенной функцией Excel  ЛИНЕЙН.

Таблица 3 – постоянные и переменные издержки продукции А

Таблица 4 – постоянные и переменные издержки продукции Б

Рассматриваемое предприятие производит и реализует 2 вида продукции А и Б. Для их создания используется одно и тоже сырьё, количество которого у предприятия в данный момент  ограничено. Поэтому нужно определелить оптимальный месячный объём производства для каждого из продуктов, с целью максимизации прибыли всего предприятия в данном периоде. Для этого воспользуемся  надстройкой «Поиск решения».

Таблица 5 – Оптимизация производственной программы предприятия

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

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

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

В результате мы получаем таблицу:

Таблица 6 – Оптимизация производственной программы предприятия продукции А и Б

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

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

Рисунок 1 – график финансовой устойчивости по продукции А

Рисунок 2 – График финансовой устойчивости по продукции Б

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