Таблица ограничений

Лабораторная работа № 3

Применение технологий MS Excel для решения задач финансового планирования

Методические указания:

1. Повторить теоретические вопросы бюджетного планирования.

2. Повторить общие правила и порядок работы в "Excel".

3. Изучить технологию работы при выполнении в "Excel" операций "Пакет анализа", "Регрессия" и "Поиск решения".

4. Изучить методику автоматизированной разработки бюджета.

5. Выполнить приведенный вариант в последовательности, указанной в практикуме.

Применение технологий "Excel" для разработки бюджета предприятия

Известно, что одной из групп факторов, оказывающих влияние на прибыль предприятия в ходе производства товаров (оказания услуг), является распределение фонда накопления, сформировавшегося на текущий год, на год последующий, т. е. формирование бюджета или его элементов. Как распределить средства по статьям бюджета предприятия, чтобы прибыль за очередной год была бы максимальна?

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

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

Например, в 2008 г. проводятся выборы в Государственную думу. Руководитель предприятия избирается в депутаты этой думы. Может или нет он принять решение на понижение заработной платы своим рабочим — потенциальным избирателям. Вероятнее всего — нет. Он может принять решение о ее повышении хотя бы на несколько процентов. Это и будет ограничением. Аналогичным образом определяются и другие ограничения.

Задача 1. Выполнить оптимальное распределение средств предприятия по статьям бюджета на очередной 2008 финансовый год. Задачу решить с использованием режимов "Пакет анализа" и "Поиск решения" программы "Excel". Распределение средств по статьям бюджета в предыдущие годы, а также величина прибыли, имеющей место при этом, приведены в табл. .1.

При решении задачи распределения денежных средств необходимо учесть следующие ограничения: сумма денежных средств, выделяемых на каждую из статей бюджета, должна быть равна величине расходуемых средств ежегодного фонда накопления, на развитие основного и вспомогательного производства должно быть затрачено не менее 40% расходуемых средств фонда накопления, резерв денежных средств должен быть равен 10% фонда накопления предыдущего года, средства на виды обеспечения и совершенствование управления должны составлять не более 15% средств, выделенных на развитие производства, все искомые переменные должны быть больше нуля, минимальная величина фонда заработной платы должна быть не менее 1,5 млн руб. Эти ограничения были получены в результате оценки финансово-экономической ситуации, выходящей за рамки решения данной задачи.

Решение.

Распределение финансовых средств по расходным статьям бюджета предприятий является одной из важнейших финансово-экономических задач финансового и производственного планирования. Для решения этой задачи используются различные методы и подходы. В лабораторной работе использован подход, в основу которого положены следующие технологические операции:

• формирование целевой функции;

• формирование системы ограничений;

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

Для формирования целевой функции по данным, приведенным в таблице 1, строится уравнение регрессии (оно является целевой функцией).

Для этого выполните следующие основные операции:

• данные, приведенные в табл. 1, скопируйте в электронную таблицу "Excel" (табл. 2);

• вызовите режим "Пакет анализа", "Регрессия" (рис. 1);

Рис.1

Рис.2

• введите входной интервал Y (матрица-столбец значений ежегодной прибыли предприятия);

• введите входной интервал X (ежегодная величина денежных средств по статьям бюджета предприятия);

• в диалоговом окне "Регрессия" →"Параметры вывода"→"Выходной интервал" укажите поле ячеек, в которые будет помещена выходная информация отчета.

Промежуточные и окончательные данные вычислений коэффициентов уравнения регрессии приведены в табл.3;

Примечание. 1. В последующем величина фонда накопления условно приравнивается к доходной части бюджета предприятия.
2. Величина средств в табл. 1.3 приведена в млн руб.

• введите параметры вывода информации в окне "Параметры вывода";

• в окне "Остатки" укажите необходимые результаты;

• нажмите "ОК" и получите коэффициенты уравнения регрессии.

	Таблица 3
	Коэффициенты
Y-пересечение	22,50385
Переменная X 1	1,168809
Переменная X 2	8,192479
Переменная X 3	1,219967
Переменная X 4	11,44293
Переменная X 5	2,519023
Переменная X 6	-21,0899
Переменная X 7	-0,25292
Переменная X 8	0,435921
Переменная X 9	0,92861

Уравнение регрессии, принимаемое в последующем в качестве целевой функции, имеет следующий вид:

ПРИБЫЛЬ=22.50384846+1.1688093999*Х1+8.192479317*Х2+1.219966726-Х3+11.44293488*Х4+2.519022677*Х5-21.08993704*Х6-0.252922644*Х7+0.435921187*Х8+0.928609786*Х9.

Система ограниченийформируется на основе анализа исходных данных задачи.

• Первое, основное ограничение

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

Х1+Х2+Х3+Х4+Х5+Х6+Х7+Х8+Х9=ФН(2007)

• Второе ограничение

На развитие основного и вспомогательного производства должно быть затрачено не менее 40% расходуемых средств фонда накопления, т. е.

XI + Х2 > 0,4ФН(2007).

• Третье ограничение

Резерв денежных средств должен быть равен 10% фонда накопления предыдущего года, т. е.

Х9 = 0,1ФН(2007).

• Четвертое ограничение

Средства на виды обеспечения и совершенствование управления должны составлять не более 15% средств, выделенных на развитие производства, т. е.

0,15(Х1+Х2)>ХЗ+Х4.

• Пятое ограничение

Все искомые переменные должны быть больше нуля, т. е. Xi> 0.

• Шестое ограничение

Минимальная величина фонда заработной платы должна быть не менее 1,5 млн руб., т. е. Х6 > 1,5.

• Седьмое ограничение XI > Х2.

Порядок решения задачи оптимизации распределения доходной части бюджета предприятия следующий:

1. Начиная с 69 строки подготовьте таблицу(Рис.3 ), в которой укажите:

Рис.3

• К70— целевая ячейка;

• B69-J69 — ячейки с искомыми переменными;

• Каждой из ячеек строки B8-J8 выполнена операция присваивания (присвоено имя X1, Х2, ХЗ, Х4, Х5, Х6, Х7, Х8, Х9).Для этого выполнены команды: Формулы -→Диспетчер имен→Создать→Создание имени(Рис.4, Рис.5, Рис.6).

Рис.4

Рис.5

Рис.6

• К69 — ∑ Х_i

2. Таблица ограничений

• B75 — ячейка, в которой вычисляется сумма XI + Х2;

• C75— ячейка, в которой вычисляется сумма ХЗ+Х4;

• D75— ячейка, в которой вычисляется ограничение 0.15*(Х1+Х2);

• E75=0.1*ФН(2007)

• F75=0.4ФН(2007)

• B70-J70 — ячейки, в которых указывается произведение переменной на значение соответствующего коэффициента, например 1.168*Х1 — в ячейке В70

3. В ячейки строки, которым присвоены имена (XI,…,Х9), введите значения вектора начала поиска оптимального распределения доходной части бюджета по статьям расходной части бюджета. Как правило, эти значения должны совпадать со значениями денежных средств, приведенных в последней строке таблицы 1.

4. В ячейках строки, следующей за строкой присвоения (Рис.3), введите формулы = С_i*Х. (Сi— коэффициенты при соответствующих переменных целевой функции), которые впоследствии суммируются в ячейке К70. Ячейка, в которой производится суммирование, используется впоследствии в качестве целевой ячейки. В ячейке К69 произведите суммирование значений, находящихся в именованных ячейках, т. е. = ∑Х_i.

5.. В меню "Excel" выберите операции "Сервис", "Поиск решения"

В появившемся окне введите следующие данные (рис. 7):

- целевую ячейку;

- изменяемые ячейки, т. е. ячейки, которым присвоены имена X,…,Х9;

- ограничения в виде равенств и неравенств.

Рис.7

6. Выполните операцию "Параметры поиска решения". В открывшемся окне установить: количество итераций, точность поиска, предел сходимости, модель поиска, метод поиска (рис..3).

Рис.8

7. Вернитесь в основное окно "Поиск решения" и реализуйте команду "Выполнить"(рис.7).

После выполнения этой команды возможны два варианта. Первый — появится окно "Результаты поиска решения".

Рис.9

Если результат не получен, то необходимо неоднократно выполнить команду "Продолжить поиск".

Исходные значения, содержащиеся в именованных ячейках Х1-Х9 (B8:J8), приведены на рис.9 и выделены в аблице 5. При этом целевая функция принимает значение 109.43

Таблица 5

	Х1	Х2	Х3	Х4	Х5	Х6	Х7	Х8	Х9
Х	9,0913	9,0913		2,7273		1,5			2,49
										109,43

После решения задачи поиска оптимального значения целевой функции содержимое ячеек B8-J8 и целевой функции в ячейке К9 приняли значения, отраженные в таблица 5.

Вывод. Выделение денежных средств в 2008 г. на виды обеспечения производства, создание резерва материальных средств и комплектующих изделий, развитие инфраструктуры и капитальное строительство нецелесообразно. Основные денежные средства необходимо выделить на развитие основного и вспомогательного производства. При этом в 2008 г. следует ожидать увеличения прибыли в 3,5-4 раза по сравнению с 2007 г.

Задания для самостоятельной работы

Все данные решения задачи оставить без изменений, за исключением:

Вариант 1. Ввести ограничения:

• на виды обеспечения производства — ХЗ > 1,5;

• на развитие инфраструктуры — Х7 = 1,0.

• Вариант 2. Ввести ограничения:

• на создание резерва материальных средств и запасов комплектующих изделий — Х5 = 2,0;

• на капитальное строительство — Х8 > 1,5.