Анализ безубыточности (4). Модель в Microsoft Excel
Автор: Александр Поляков
Полученная в предыдущих частях статьи математическая модель сводит задачу анализа безубыточности для рассматриваемого примера к следующим этапам:
1. пользователь задает значения объемов продаж дизтоплива и электроэнергии, т.е. задает значения исходных коэффициентов k5,6и k1,7
2. решается СЛАУ из двух уравнений, в результате определяются значения исходных коэффициентов 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. определяются финансовые результаты от продажи дизтоплива и электроэнергии
9. если не получено искомое значение финансовых результатов, то все этапы повторяются при других значениях параметров модели
Модель в виде связанных таблиц Microsoft Excel содержит пять блоков (скачать таблицы):
БЛОК 1. Результаты вычислений (интерфейс модели)
БЛОК 2. Входные данные
БЛОК 3. Решение СЛАУ для исходных коэффициентов k4,5 и k5,1
БЛОК 4. Решение СЛАУ для Графа затрат
БЛОК 5. Финансовые результаты
БЛОК 1 представляет собой интерфейсную часть модели, в ней сосредоточены ячейки, в которых задаются значения исходных коэффициентов k5,6 и k1,7, и ячейки, в которых с помощью ссылок собраны результаты расчетов из БЛОКА 4 и БЛОКА 5:
В таблицу включены значения только трех тарифов – для электроэнергии (tUC1), транспортных услуг (tUC2) и дизтоплива (tUC3), т.к. их поведение наиболее интересно для пользователя.
Работа с данным блоком модели довольно проста – пользователь задает значения исходных коэффициентов k5,6 и k1,7 и сразу смотрит результаты расчетов.
Например, выше в таблице представлены результаты расчетов для значений исходных коэффициентов:
k5,6=50 л – продано дизтоплива
k1,7=100 кВт∙ч – продано электроэнергии
БЛОК 2 модели состоит из трех таблиц:
В таблице 2.1 содержится вся нормативная информация – нормы, расстояния и цены продажи дизтоплива и электроэнергии.
В таблице 2.2 собраны значения всех исходных коэффициентов, которые используются далее для формирования матрицы исходных коэффициентов K[7,7] в БЛОКЕ 4.
В таблице 2.3 содержатся стоимости первичных затрат (pci) для всех четырех цехов предприятия с разделением на стоимости первичных постоянных затрат (pciF) и стоимости первичных переменных затрат (pciV). Эти значения используются далее для формирования вектора-столбца правых частей уравнений Z[7] в БЛОКЕ 4.
БЛОК 3 предназначен для поиска значений исходных коэффициентов встречных потоков затрат k4,5 и k5,1.
Значения коэффициентов при неизвестных ki,j содержатся в области ячеек (C23:D24) а правые части уравнений СЛАУ находятся в ячейках (E23:E24).
В данном случае использован способ решения СЛАУ с помощью перемножения обратной матрицы коэффициентов, которая сформирована в области ячеек (C27:D28), и вектора-столбца правых частей уравнений (E27:E28), в результате чего в ячейках (G27:G28) получаются значения искомых исходных коэффициентов k4,5 и k5,1.
В БЛОКЕ 4 модели производится решение СЛАУ, которая полностью определяется расширенной матрицей исходных коэффициентов KEXP[8,7], сформированной в области ячеек (C33:J39). Решение производится методом понижения размерности задачи путем вычисления обратной матрицы коэффициентов уравнений и умножением ее на вектор-столбец правых частей уравнений.
На основе матрицы KEXP[8,7] сформируем в области ячеек (C43:J49) расширенную матрицу коэффициентов уравнений PEXP[8,7]. Поскольку центры затрат CC6 и CC7 являются стоками, то уравнения баланса затрат для них в СЛАУ можно не учитывать, что дает возможность понизить размерность СЛАУ с 7-ми до 5-ти уравнений.
Удалив из матрицы PEXP[8,7] столбцы и строки для центров затрат CC6 и CC7, сформируем в области ячеек (C52:H56) расширенную матрицу исходных коэффициентов KEXP[6,5] уменьшенной размерности:
Сформировав в области ячеек (C59:G63) обратную матрицу коэффициентов уравнений и умножив ее на вектор-столбец правых частей уравнений (H59:H63), получим в ячейках (J59:J63) искомые значения тарифов tUCi.
Умножив значения тарифов на значения исходных коэффициентов, сформируем в области ячеек (C66:I72) матрицу стоимостей С[7,7]:
В БЛОКЕ 5 модели производится расчет себестоимости проданного дизтоплива SIN(CC6) и проданной электроэнергии SIN(CC7), а также определяются доходы и финансовые результаты от продажи дизтоплива, электроэнергии и деятельности предприятия в целом.
Следует отметить, что потенциал использования полученной модели гораздо выше, чем «просто» нахождение сценариев безубыточности для заданных в примере входных данных.
Например, можно существенно усложнить БЛОК 2 модели, задав функциональные зависимости для определения нормативных значений и цен продажи, а также добавить наборы бюджетов первичных затрат – для определения стоимости потоков первичных постоянных и переменных затрат на входах центров затрат. Но наш пример учебный, поэтому не имеет смысла слишком усложнять полученную модель. Оставим эту задачу для самостоятельной работы читателям.
Скачать модель Microsoft Excel...