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

    

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

    

    

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

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

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

3. определяются значения исходных коэффициентов k1,4k2,1k2,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:

 

AB0030

   

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

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

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

k5,6=50 л – продано дизтоплива

k1,7=100 кВт∙ч – продано электроэнергии

 

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

   

AB0031

 

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

В таблице 2.2 собраны значения всех исходных коэффициентов, которые используются далее для формирования матрицы исходных коэффициентов K[7,7] в БЛОКЕ 4.

В таблице 2.3 содержатся стоимости первичных затрат (pci) для всех четырех цехов предприятия с разделением на стоимости первичных постоянных затрат (pciF) и стоимости первичных переменных затрат (pciV). Эти значения используются далее для формирования вектора-столбца правых частей уравнений Z[7] в БЛОКЕ 4.

   

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

 AB0032

   

Значения коэффициентов при неизвестных ki,j содержатся в области ячеек (C23:D24) а правые части уравнений СЛАУ находятся в ячейках (E23:E24).

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

 

В БЛОКЕ 4 модели производится решение СЛАУ, которая полностью определяется расширенной матрицей исходных коэффициентов KEXP[8,7], сформированной в области ячеек (C33:J39). Решение производится методом понижения размерности задачи путем вычисления обратной матрицы коэффициентов уравнений и умножением ее на вектор-столбец правых частей уравнений.   

 AB0033

   

На основе матрицы KEXP[8,7] сформируем в области ячеек (C43:J49)  расширенную матрицу коэффициентов уравнений PEXP[8,7]. Поскольку центры затрат CC6 и CC7 являются стоками, то уравнения баланса затрат для них в СЛАУ можно не учитывать, что дает возможность понизить размерность СЛАУ с 7-ми до 5-ти уравнений.

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

   

AB0034    

Сформировав в области ячеек (C59:G63) обратную матрицу коэффициентов уравнений и умножив ее на вектор-столбец правых частей уравнений (H59:H63), получим в ячейках (J59:J63) искомые значения тарифов tUCi.

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

 AB0035

   

В БЛОКЕ 5 модели производится расчет себестоимости проданного дизтоплива SIN(CC6) и проданной электроэнергии SIN(CC7), а также определяются доходы и финансовые результаты от продажи дизтоплива, электроэнергии и деятельности предприятия в целом.

AB0036    

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

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

 

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

 

    

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