Примеры решения заданий. Пример 1. Баланс для трех отраслей за отчетный период представлен в следующей таблице

Пример 1. Баланс для трех отраслей за отчетный период представлен в следующей таблице.

Производящие отрасли	Потребляющие отрасли	Конечная продукция

Найдите:

1. Объемы производства валовой продукции в отчетном периоде.

2. Коэффициенты прямых материальных затрат.

3. Коэффициенты полных материальных затрат.

Определите объем валовой продукции Х ₁ ⁿ, Х ₂ ⁿ, Х ₃ ⁿ для плани-
руемого периода при плане конечной продукции Y ₁ ⁿ = 80, Y ₂ ⁿ = 110, Y ₃ ⁿ = 160.

Решение:

Выполним решение с помощью приложения Excel. Заполним данными задачи лист как показано на рис. 1.

Рис. 1. Исходные данные

1. Найдем объемы валовой продукции Х каждой отрасли в ячейках F4:F6 по формуле (1).

Для этого в ячейку F4 запишем формулу

=СУММ(В4:Е4),

а затем скопируем ее в ячейки F5 и F6. Получим: Х ₁ = 170, Х ₂ = 220,
Х ₃ = 230.

2. Рассчитаем матрицу коэффициентов прямых материальных затрат в ячейках B10:D12 по формуле (2).

Для нахождения коэффициентов первого столбца матрицы введем в ячейку В10 формулу

=В4:$F$4,

а затем скопируем ее в ячейки F5 и F6. Коэффициенты второго и третьего столбцов матрицы рассчитываются аналогично. Получим:

.

3. Рассчитаем матрицу коэффициентов полных материальных затрат в ячейках F15:H17 по формуле

Р = (Е – А)^–1.

В балансе рассматриваются 3 отрасли, поэтому в ячейках F10:H12 запишем единичную матрицу Е размерности 3´3:

.

В ячейках B15:D17 рассчитаем матрицу Е – А. Для этого в ячейку B15 введем формулу

=F10-B10,

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

.

Размерность матрицы Е – А составляет 3´3, следовательно размерность матрицы Р будет такой же. Для нахождения обратной матрицы используем функцию =МОБР(). Это функция массива.

Функции массивов обладают следующими особенностями:

¨ функцию массива можно изменять или удалять только для всего массива целиком, а для одной ячейки невозможно;

¨ аргументы функций можно изменять только для всего массива сразу, а не для одной ячейки;

¨ в строке формул функции массива записываются в фигурных скобках, например {=МОБР(А12:В13)}.

Для расчета матрицы коэффициентов полных материальных затрат Р:

1) выделим диапазон ячеек F15:H17;

2) вызовем мастер функций и выберем из категории Математические функцию МОБР(), котораявозвращает в качестве результата массив – обратную матрицу. В качестве аргумента функции укажем диапазон ячеек В15:D17, в котором вычислена матрица Е – А;

3) для получения результата нажмем одновременно клавиши <Ctrl>,<Shift> и <Enter>. В ячейках F15:H17 появится результат – матрица Р:

.

4. Найдем объемы валовой продукции планового периода по формуле (8). Для вычисления воспользуемся функцией =МУМНОЖ() категории Математические, которая также является функцией массива. Для нахождения валовой продукции планового периода выполним следующее:

1) выделим диапазон ячеек Н4:Н6;

2) с помощью Мастера функций выберем функцию =МУМНОЖ();

3) в качестве аргументов укажем: массив 1 (матрица Р) – диапазон ячеек F15:H17, массив 2 (конечная продукция планового периода Yⁿ) – диапазон ячеек G4:G6;

4) одновременно нажмем клавиши <Ctrl>,<Shift> и <Enter>. В ячейках Н4:Н6 появится результат – валовая продукция планового периода Х:

.

Таким образом, для планируемого периода объем валовой продукции Х ₁ ⁿ = 218, Х ₂ ⁿ = 264, Х ₃ ⁿ = 298 при плане конечной продукции
Y ₁ ⁿ = 80, Y ₂ ⁿ = 110, Y ₃ ⁿ = 160. Численное решение задачи представлено на рис. 2.

Рис. 2. Числовое решение задачи

Решение задачи в режиме формул представлено на рис. 3.

Рис. 3. Решение задачи в режиме формул

Пример 2. Рассмотрим трехотраслевое производство. Данные по балансу продукции и затратам ресурсов за отчетный период приведены в следующей таблице.

Производящие отрасли	Потребляющие отрасли	Конечная продукция
Ресурсы

Задан план выпуска конечной продукции Y ₁ ^п = 80, Y ₂ ^п = 120 и
Y ₃ ^п = 150 на следующий период. Под него выделены ресурсы в следующих объемах: R ₁ ^п = 120; R ₂ ^п = 550; R ₃ ^п = 265.

1. Определить, достаточно ли выделено ресурсов под план выпуска конечной продукции.

2. В случае недостаточности ресурсов рассчитать возможный план выпуска конечной продукции при выделенных объемах ресурсов.

Решение:

Выполним решение с помощью приложения Excel. Заполним данными задачи лист как показано на рис. 4.

Рис. 4. Исходные данные

1. План выпуска конечной продукции зависит от плана выпуска валовой продукции, см. формулу (8). Поэтому, в первую очередь, необходимо рассчитать план выпуска валовой продукции. Для этого выполним следующее:

¨ Найдем объем валовой продукции в отчетном периоде (см. пример 1):

.

¨ Найдем коэффициенты прямых материальных затрат (см. пример 1):

.

¨ Найдем коэффициенты полных материальных затрат (см. пример 1):

.

¨ Найдем объем валовой продукции в планируемом периоде в ячейках Н4:Н6 (см. пример 1):

.

2. Количество ресурсов планового периода зависит от плана выпуска валовой продукции, см. формулу (13). Чтобы определить достаточно ли выделено ресурсов, необходимо рассчитать количество ресурсов, требующееся в плановом периоде. Для этого выполним следующее:

¨ По данным отчетного периода в ячейках B24:D26 найдем матрицу коэффициентов прямых ресурсов по формуле (11). Для нахождения коэффициентов первого столбца матрицы объемы ресурсов каждого вида потребленных первой отраслью разделим на объем валовой продукции первой отрасли. Введем в ячейку В24 формулу

=В8/$F$4,

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

.

¨ Рассчитаем необходимое количество ресурсов каждого вида в плановом периоде по формуле (13) в ячейках Н8:Н10 с помощью функции МУМНОЖ() (см. пример 1). Получим следующее:

.

Таким образом, для производства плановой конечной продукции в объемах Y ₁ ^п = 80, Y ₂ ^п = 120 и Y ₃ ^п = 150 требуется R ₁ ^mp = 126, R ₂ ^mp = 544 и R ₃ ^mp = 265 единиц ресурсов.

Численное решение задачи представлено на рис. 5.

Рис. 5. Численное решение задачи

Решение задачи в режиме формул приведено на рис. 6.

Рис. 6. Решение задачи в режиме формул

3. Проверим ограничение по ресурсам по формуле (14). На планируемый период были выделены ресурсы в объемах: R ₁ ^п = 120, R ₂ ^п = 550, R ₃ ^п = 265, т. е. для реализации плана конечной продукции в объемах Y ₁ ^п = 80, Y ₂ ^п = 120 и Y ₃ ^п = 150 ресурсов недостаточно. Предложенный план невозможно реализовать.

Уменьшим план конечной продукции. Рассмотрим новый план выпуска конечной продукции несколько занизив ее первоначальные объемы. Пусть Y ₁ ^п = 75, Y ₂ ^п = 114 и Y ₃ ^п = 145. В ячейках G4:G6 введите новые значения плана конечной продукции. Excel автоматически пересчитает все данные задачи. Сравните новые данные по потребностям в ресурсах R ₁ = 120, R ₂ = 527, R ₃ = 252 с выделенными. Неравенство (14) выполняется, следовательно план выпуска валовой продукции Y ₁ ^п = 75, Y ₂ ^п = 114 и Y ₃ ^п = 145 может быть принят.