Анализ безубыточности. Пример (ч.4). Модель в Microsoft Excel

    

Автор:  Александр Поляков

    

    

В предыдущих частях статьи мы изучили топологию Графа затрат G(7,13), моделирующего хозяйственную деятельность предприятия Orion, а также определили всю необходимую нормативно-справочную информацию.

Опираясь на полученную информацию, представим расширенную матрицу исходных коэффициентов KEXP[8,7] Графа затрат G(7,13) в следующем виде:

 

Модель для анализа безубыточности на Графе затрат

   

Полученный вид расширенной матрицы исходных коэффициентов KEXP[8,7] фактически сводит задачу анализа безубыточности на Графе затрат G(7,13) для рассматриваемого примера к следующим этапам:

  1. пользователь определяет интересующие его объемы продаж дизельного топлива и электроэнергии, т.е. задает значения исходных коэффициентов k5,6 и k1,7
  2. решается СЛАУ (1) и определяются значения исходных коэффициентов k4,5 и k5,1
  3. на основе сводной таблицы нормативной информации определяются значения исходных коэффициентов k1,4, k2,1, k2,6 и k5,2
  4. на основе сводной таблицы нормативной информации определяются стоимости потоков первичных затрат pc2 и pc4, а следовательно, и значения элементов z2 и z4 вектора-столбца правых частей уравнения Z[7]
  5. формируется расширенная матрица исходных коэффициентов KEXP[8,7], производится решение СЛАУ, в результате которого находятся значения тарифов на выходах всех центров затрат
  6. формируется матрица стоимостей С[7,7], которая, в т.ч. содержит значения себестоимостей проданного дизельного топлива и электроэнергии
  7. определяется доход от продажи дизельного топлива и электроэнергии
  8. определяются финансовые результаты от продажи дизельного топлива и электроэнергии

В файле Example_AB16 содержится модель для анализа безубыточности предприятия Orion, которая содержит пять блоков:

Блок 1. Результаты вычислений

Блок 2. Входные данные

Блок 3. Исходные коэффициенты для встречных потоков затрат

Блок 4. Решение СЛАУ для Графа затрат G(7,13)

Блок 5. Финансовые результаты

Для удобства работы с моделью в Блоке 1 сосредоточены ячейки, в которых пользователь задает значения исходных коэффициентов k5,6 и k1,7, а также ячейки, в которых с помощью ссылок собраны результаты расчетов из других блоков модели – Блока 4 и Блока 5:

Модель для анализа безубыточности на Графе затрат

 

Назначение столбцов таблицы результатов вычислений:

  • k5,6 – количество проданного дизельного топлива (задает пользователь)
  • k1,7 – количество проданной электроэнергии (задает пользователь)
  • tUC1 – тариф для электроэнергии
  • tUC2 - тариф для транспортных услуг
  • tUC4 – тариф для дизельного топлива
  • Rev6 – доход от продажи дизельного топлива
  • SIN6 – себестоимость проданного дизельного топлива
  • Profit6 – финансовый результат от продажи дизельного топлива
  • Rev7 – доход от продажи электроэнергии
  • SIN7 – себестоимость проданной электроэнергии
  • Profit7 – финансовый результат от продажи электроэнергии
  • Rev0 – общий доход от продажи дизельного топлива и электроэнергии
  • SIN0 – себестоимость проданного дизельного топлива и электроэнергии
  • Profit0 – общий финансовый результат деятельности предприятия

В таблицу были включены значения только трех тарифов – tUC1, tUC2 и tUC3, т.к. поведение именно этих тарифов наиболее интересно для пользователя, их значения непосредственно определяют себестоимости проданной электроэнергии и дизельного топлива.

Работа с данным блоком модели довольно проста – пользователь задает значения исходных коэффициентов k5,6 и k1,7 в соответствии со сценарием проведения анализа безубыточности и сразу смотрит результаты расчетов.

Например, выше в таблице представлены результаты расчетов для значений исходных коэффициентов:

  • k5,6=50 л – продано дизельного топлива
  • k1,7=100 кВт∙ч – продано электроэнергии

Блок 2 модели состоит из трех таблиц:

 Модель для анализа безубыточности

   

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

Во второй таблице (2.2) содержатся значения исходных коэффициентов ki,j. При формировании расширенной матрицы исходных коэффициентов KEXP[8,7] значения исходных коэффициентов будут браться именно из этой таблицы.

В третьей таблице (2.3) содержатся стоимости потоков первичных затрат на входах четырех центров затрат {СС1,СС2,СС3,СС4}. Для каждого потока первичных затрат в отдельной ячейке выделена стоимость первичных постоянных затрат pciF, стоимость первичных переменных затрат pciV и общая стоимость первичных затрат pci на входе центра затрат ССi.

Блок 3 модели предназначен для поиска значений исходных коэффициентов встречных потоков затрат k4,5 и k5,1.

 Модель для анализа безубыточности

   

Для определения значений исходных коэффициентов k4,5 и k5,1 для встречных потоков затрат на втором этапе алгоритма решается следующая СЛАУ:

Модель для анализа безубыточности 

   

В Блоке 3 значения коэффициентов при неизвестных ki,j содержатся в ячейках (C22:D23) а правые части уравнений СЛАУ находятся в ячейках (E22:E23).

В рассматриваемой модели использован способ решения СЛАУ с помощью обратной матрицы, значения элементов которой содержатся в ячейках (C26:D27). Перемножив обратную матрицу (C26:D27) и вектор-столбец правых частей уравнений (E26:E27), получим в ячейках (G26:G27) искомые значения исходных коэффициентов k4,5 и k5,1.

В Блоке 4 модели производится решение СЛАУ, представленной расширенной матрицей исходных коэффициентов KEXP[8,7].

 Модель для анализа безубыточности

   

Сформируем в области ячеек (C42:J48) на основе расширенной матрицы исходных коэффициентов KEXP[8,7] расширенную матрицу коэффициентов уравнений PEXP[8,7], которая содержит 0-вые столбцы CC7 и CC8. Это значит, что СЛАУ несовместна и решение найдено быть не может.

Удалив столбцы и строки для центров затрат CC7 и CC8, сформируем затем в области ячеек (C51:H55) расширенную матрицу исходных коэффициентов KEXP[6,5] уменьшенной размерности:

Модель для анализа безубыточности 

   

Сформировав в области ячеек (C58:G62) обратную матрицу коэффициентов уравнений и умножив ее на вектор-столбец правых частей уравнений (H58:H62), получим в ячейках (J58:J62) значения тарифов tUCi.

Умножив значения тарифов на значения исходных коэффициентов, сформируем в области ячеек (C65:I71) матрицу стоимостей С[7,7]:

 Модель для анализа безубыточности

   

В Блоке 5 модели с помощью матрицы стоимостей С[7,7] производится расчет себестоимости проданного дизельного топлива SIN(CC6) и проданной электроэнергии SIN(CC7), а также с помощью нормативной информации определяются доходы и финансовые результаты от продажи дизельного топлива, электроэнергии и деятельности предприятия Orion в целом.

Модель для анализа безубыточности

   

В рассмотренной выше модели был использован способ решения СЛАУ с помощью нахождения обратной матрицы коэффициентов уравнений. При использовании Microsoft Excel возможны и другие варианты нахождения решения СЛАУ – например, можно воспользоваться надстройкой «Поиск решения». 


Решение СЛАУ с помощью надстройки «Поиск решения»

Microsoft Excel располагает мощным средством для решения оптимизационных задач – надстройкой «Поиск решения» (Solver). Задачи оптимизации предполагают поиск значений аргументов, доставляющих целевой функции минимальное или максимальное значение при наличии каких-либо дополнительных ограничений. Для того, чтобы воспользоваться возможностями данной надстройки, необходимо задачу решения СЛАУ свести к оптимизационной задаче. Для этого любое из уравнений СЛАУ (например, первое) нужно взять в качестве целевой функции, а оставшиеся уравнения рассматривать в качестве ограничений. Запишем СЛАУ в следующем виде:

f1=k1,1×tUC1+k2,1×tUC2+k3,1×tUC3+k4,1×tUC4+k5,1×tUC5z1=0 – целевая функция

f2=k1,2×tUC1+k2,2×tUC2+k3,2×tUC3+k4,2×tUC4+k5,2×tUC5z2=0 – ограничение

f3=k1,3×tUC1+k2,3×tUC2+k3,3×tUC3+k4,3×tUC4+k5,3×tUC5z3=0 – ограничение

f4=k1,4×tUC1+k2,4×tUC2+k3,4×tUC3+k4,4×tUC4+k5,4×tUC5z4=0 – ограничение

f5=k1,5×tUC1+k2,5×tUC2+k3,5×tUC3+k4,5×tUC4+k5,5×tUC5z5=0 – ограничение

Тогда задачу оптимизации можно сформулировать следующим образом – найти значения вектора-столбца тарифов TUC[5], доставляющие нуль целевой функции f1 при ограничениях, представленных оставшимися четырьмя уравнениями fi=0 (i=2...5).

Для решения этой задачи запишем в область ячеек (L51:L55) формулы для вычисления значений функций fi, для чего в ячейку L51 введем формулу:

=C51*$J$51+D51*$J$52+E51*$J$53+F51*$J$54+G51*$J$55-H51

и скопируем ее в четыре оставшихся ячейки (L52:L55). Вызвав «Поиск решения» (Данные/Поиск решения – в примере использовалась Microsoft Excel 2010) зададим параметры поиска решения:

  • будем оптимизировать значение целевой функции в ячейке $L$51 до 0-ля
  • изменяя значения в ячейках переменных ($J$51:$J$55)
  • в соответствии с ограничениями: $L$52=0, $L$53=0, $L$54=0, $L$55=0

Модель для анализа безубыточности 

   

Нажав кнопку «Найти решение», получим в области ячеек (J51:J55) значения тарифов, обеспечивающие 0-вое значение целевой функции при заданных ограничениях.

Модель для анализа безубыточности 

   

На рисунке видно, что значения тарифов, полученные в области ячеек (J51:J55) с помощью надстройки «Поиск решения», совпадают со значениями тарифов в области ячеек (J58:J62), полученными при решении СЛАУ с помощью нахождения обратной матрицы коэффициентов уравнений, по крайней мере – до четвертого знака после запятой. Для нашей задачи это вполне приемлемая точность решения.


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

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

 

    

Скачать модель Microsoft Excel... 

 

    

назад...   далее...