Logo GenDocs.ru

Поиск по сайту:  

Загрузка...

Андрусевич В.В. Методика решения задач. Информационные технологии управления - файл 1.doc


Андрусевич В.В. Методика решения задач. Информационные технологии управления
скачать (1038.5 kb.)

Доступные файлы (1):

1.doc1039kb.06.12.2011 14:21скачать

содержание
Загрузка...

1.doc

Реклама MarketGid:
Загрузка...
Правительство Москвы

Московский комитет образования

Московский городской педагогический университет

ЭКОНОМИЧЕСКИЙ ФАКУЛЬТЕТ


Методика решения задач
Информационные технологии управления

по специальности 061100 «менеджмент»

форма обучения экстернат

МОСКВА 2001
МЕТОДИКА РЕШЕНИЯ ЗАДАЧ
АНДРУСЕВИЧ В.В.

СОДЕРЖАНИЕ


1. Линейная оптимизация 3

2. Регрессия. Метод наименьших квадратов 10

3.Линейная регрессия 15

4. Транспортная задача 18

5. Анализ и сравнение инвестиционных проектов 29

Литература 36


^

1. Линейная оптимизация



Планирование деятельности фирмы
ЗАДАЧА 1. Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 кв. м досок, а для изделия модели В – 4 кв. м. Фирма может получать от поставщиков до 1700 кв. м досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если изделие модели А приносит 2 доллара прибыли, а изделие модели В приносит 4 доллара прибыли?
Решение. Составим математическую модель. Введем обозначения: x - количество изделий модели А, а y - количество изделий модели В, выпускаемых в течение недели. Прибыль от реализации этих изделий равна 2x + 4y долларов. Эту прибыль нужно максимизировать. Функция, для которой ищется экстремум (максимум или минимум), называется целевой функцией.

Беспредельному увеличению количества изделий, а значит, и росту целевой функции препятствуют ограничения. Ограничено количество материала для производства полок. Отсюда следует ограничение 3x + 4y  1700.

На производство изделия А требуется 0.2 часа, а на производство изделия В требуется 0.5 часа. Время производства ограничено 160 часами, поэтому имеет место неравенство 0.2x + 0.5y  160.

Кроме того, количества изделий – неотрицательные числа, поэтому x  0, y  0.

Задача оптимизации записывается следующим образом.
2x + 4y  max,

3x + 4y  1700,

0.2x + 0.5y  160,

x  0, y  0.
Решим сформулированную задачу линейного программирования в Excel. Создайте новую рабочую книгу, сохраните ее под именем Задача1. Дайте первому листу имя Полки.

Введите в ячейки рабочего листа Excel данные задачи (Таблица 1). Не вводите сразу нули в ячейки столбца В, о них речь пойдет ниже. Во избежание фатальных ошибок делайте все в соответствии с приведенным далее описанием. В ячейки А1, А2, А3, А5, А6, А8, А9 введите текст Переменные, Изделие А и т.д. Этот текст не нужен для поиска решения, но его наличие придает наглядность и удобство для автора и пользователя решения задачи. Текстом являются и формулы и числа, введенные в ячейки столбцов С и D. Поэтому эти текстовые формулы следует ввести, не ставя знака равенства = в первую позицию строки формул, и можно не использовать знака умножения *.
Таблица 1





A

B

C

D



Переменные












Изделие А

0

x






Изделие В

0

y




















Целевая функция












Прибыль

0

2x+4y




















Ограничения












Материал

0

3x+4y

<=1700



Время изготовления

0

0.2x+0.5y

<=160


Ячейкам В2 и В3 присвойте имена x и y. Для этого щелкните на ячейке В2, затем на поле Имя строки ввода и редактирования и вместо В2 введите латинскую букву х и обязательно нажмите клавишу Enter. Затем щелкните на ячейке В3 и на поле Имя строки ввода и редактирования и вместо В3 введите латинскую букву у и обязательно нажмите клавишу Enter. После этого введите начальные значения переменным х и у. Для этого снова выделите ячейку В2 (или х) и введите в строку формул =0 , нажмите клавишу Enter. Аналогичные операции проделайте для ячейки В3. Операции присвоения имен переменным и задания их начальных значений (заполнить поле имени и строку формул) для каждой из ячеек можно было произвести за один прием.

Введите, наконец, настоящие формулы, соблюдая синтаксис программы Excel. Сначала в ячейку В6 введите формулу целевой функции (прибыли). Для этого выделите эту ячейку, введите в нее (или в строку формул) следующие символы: =2*х+4*у и нажмите клавишу Enter. Аналогично в ячейки В9 и В10 введите формулы =3*х+4*у и =0.2*х+0.5*у.

Только после этого, если все сделано правильно, в ячейках В6, В9 и В10 должны появиться нули, то есть значения целевой функции и выражений, задающих ограничения задачи линейного программирования, для начальных значений переменных х=0 и у=0. При появлении в ячейке набора символов типа #ССЫЛКА! или сообщения об ошибке проверьте правильность всей введенной информации во всех ячейках столбца В.

^ Только после этого экран будет соответствовать таблице 1 и примет следующий вид.



Перейдем к непосредственному решению задачи. Выделите ячейку В6, в которой вычисляется целевая функция и вызовем Решатель (меню Сервис – Поиск решения). В появившемся диалоговом окне Поиск решения (см. ниже) в поле ввода Установить целевую ячейку уже должен содержаться адрес $B$6 ячейки, содержащей целевую функцию. Если это по каким-либо причинам не так, введите в это поле набор символов $B$6. Установите переключатель Равной максимальному значению.

Перейдем к полю ввода Изменяя ячейки. В данном случае достаточно щелкнуть кнопку Предположить и в поле ввода Изменяя ячейки появится адрес блока ячеек $B$2:$B$3. При необходимости введите этот адрес вручную.


Перейдем к вводу ограничений. Щелкните кнопку Добавить. Появится диалоговое окно Добавление ограничения (см. ниже). В поле ввода Ссылка на ячейку укажите адрес $B$9 первого ограничения задачи. Правее расположен список с условными операторами (откройте его и посмотрите). Выберите оператор <=. В поле ввода Ограничение введите число 1700.


Не выходя из диалогового окна ^ Добавление ограничения, нажмите кнопку Добавить и аналогично введите второе ограничение $B$10<=160. Ввод ограничений закончен. Нажмите кнопку ОК. Вы вновь окажетесь в диалоговом окне Поиск решения и увидите введенные ограничения.

Справа имеются кнопки Изменить и Удалить. С их помощью можно изменить или удалить ограничение. Если Вы используете Excel 5.0/7.0, то должны ввести еще ограничение неотрицательности переменных $B$2: $B$3>=0.


Нажмите кнопку Параметры. Вы окажетесь в диалоговом окне Параметры поиска решения.

Чтобы узнать назначение полей ввода этого окна, нажмите кнопку Справка. Менять ничего не стоит, вполне разумные параметры установлены по умолчанию. Например, неоправданное увеличение точности или числа итераций может привести к значительному росту времени решения. Поэтому установим только два флажка: Линейная модель (так как наши ограничения и целевая функция являются линейными по переменным х и у) и Неотрицательные значения (для переменных х и у). В Excel 5.0/7.0 этот последний флажок отсутствует, поэтому и нужно было вводить ограничение неотрицательности переменных.

Щелкните кнопку ОК и Вы окажетесь в исходном окне. Теперь задача полностью готова для работы встроенного в Excel алгоритма решения. Нажмите кнопку Выполнить. Появляется диалоговое окно Результаты поиска решения. В нем указано сообщение. Решение найдено. Все ограничения и условия оптимальности выполнены. На выбор предлагаются варианты Сохранить найденное решение или Восстановить исходные значения. Если у Вас не появилось желание изменить условия задачи повторить весь процесс или часть его сначала, выберите первое.




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

После нажатия кнопки ОК вид таблицы в документе меняется: в ячейках х и у появляются оптимальные значения 300 и 200 изделий А и В соответственно. При этом целевая функция достигает максимального значения 1400. Кроме того, в ячейках В9 и В10 появились числа 1700 и 160, что означает полное использование недельных лимитов материалов и времени.


Числовые значения данного примера специально подобраны, поэтому в ответе получились круглые числа, и в оптимальной точке происходит полное использование производственных ресурсов, то есть, ограничения выполняются, как равенства. Если слегка поменять числа, то такой идеальной картины в оптимальной точке наблюдаться не будет. На самом деле эту задачу надо формулировать как целочисленную, поскольку нельзя допустить дробное число полок. Тем не менее и дробное решение в данном случае дает хорошее приближение оптимального решения задачи.

Если Вы предусмотрели получение отчетов, то в документе появятся дополнительные листы с соответствующими названиями. Приведем наиболее интересный из отчетов – Отчет по устойчивости.

^ Для полного понимания этих отчетов и извлечения из них полезной информации требуются существенные знания методов оптимизации. Эта задача перед нами не ставится.

^

2. Регрессия. Метод наименьших квадратов



В экономике часто возникает задача подбора функциональной зависимости для двух наборов данных. Независимые переменные Xi называют факторами, а зависимые Yi - откликами. Функция Y=f(X) позволяет предсказывать значение отклика для факторов, не входящих в исходный набор данных.

^ Решим следующую задачу. Дан набор точек (Xi , Yi ), i=1, …, n. Пусть имеется класс функций (линейные, квадратичные, экспоненциальные функции и т.д.). Требуется найти функцию Y=f(X) из данного класса такую, чтобы ее значения f(Xi) приближали значения Yi наилучшим образом.

В качестве критерия качества выбора функции часто принимают сумму квадратов отклонений величин f(Xi) от величин Yi, и решают задачу минимизации этой суммы:
( Yi - f(Xi))2  min.

ЗАДАЧА 2. Дан набор точек (Xi , Yi ), представленный в таблице. Найти коэффициенты m и b прямой линии Y=mX + b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.


Xi

0

1

2

3

4

Yi

3

1

6

3

7


Решение. Решим задачу в Excel. Создайте новую рабочую книгу, сохраните ее под именем Задача2. Дайте первому листу имя Регрессия1.

В ячейки А1 и В1 впишем текстовые обозначения X и Y. Разместим координаты точек в диапазоне А2:В6, как показано ниже. В ячейки А8 и В8 впишем текстовые обозначения m и b. Ячейкам А9 и В9 дадим имена коэффициентов m и b, вписывая эти буквы в поле имени ячейки и нажимая затем клавишу Enter. В ячейках А9 и В9 поместим начальные значения коэффициентов m и b. , равные нулю (=0 и клавиша Enter).

^ В ячейках С2:С6 будем вычислять Yi =m Xi + b . Для этого в ячейку С2 впишем формулу =m*A2+b и нажмем клавишу Enter. Аналогично в С3 впишем формулу =m*A3+b и нажмем клавишу Enter. Аналогично для ячеек С4, С5, С6. Начиная с ячейки С3 операция вписывания формул можно проделать копированием содержимого ячейки С2 в ячейки С3 – С6 (кнопки Копировать и Вставить на панели инструментов). При этом программа Excel сама скорректирует формулы нужным образом, заменяя А2 на А3 и т.д.

^ В ячейках D2:D6 будем вычислять ошибки приближения Yi – (m Xi + b). Для этого в D2 поместим формулу =В2-С2 (Enter). В в D3 поместим формулу =В3-С3 (Enter) и т.д. Начиная с ячейки D3 операция вписывания формул можно проделать копированием содержимого ячейки D2 в ячейки D3 – D6 (кнопки Копировать и Вставить на панели инструментов). При этом программа Excel сама скорректирует формулы нужным образом, заменяя В2 на В3 и С2 на С3 и т.д.

^ В ячейку D8 впишите текст Сумма квадратов отклонений.



Наконец, в ячейке D9 вычислим сумму квадратов отклонений. Воспользуемся функцией СУММКВ(диапазон_ячеек_D2:D6). Для этого вызовем мастер функций, нажав на кнопку со значком fx, расположенную на панели инструментов. Появится диалоговое окно мастера функций. В списке, расположенном слева, выделим Математические функции. Затем в списке расположенном справа с помощью линии прокрутки найдем функцию СУММКВ и выделим ее. Ниже этих списков Вы видите синтаксис применения этой функции. У нее может быть несколько аргументов, разделяемых точкой с запятой. У нас таких аргументов 5 (ячейки D2 - D6). Однако, можно воспользоваться одним аргументом, указывая сразу диапазон ячеек D2:D6, содержимое каждой из ячеек должно возводиться в квадрат и суммироваться.


После выделения функции СУММКВ нажмите кнопку ОК. Откроется следующее окно, в котором в окошке Число 1 и следует указать диапазон ячеек D2:D6. К нашему удивлению мудрая программа уже не только сообразила, что нам нужно и не только сама вставила требуемый аргумент, но и подсчитала результат – 104 для начальных данных. Однако диапазон ячеек она указала не совсем точно. Исправьте неточность или введите в первое окошко символы $D$2:$D$6 сами. Нажмите кнопку ОК.


Окно закроется и в ячейке D9 появится результат вычисления суммы квадратов отклонений для заданных нами начальных данных m =0 и b=0, (для уравнения линии Y=0), равный 104.

Теперь все готово для решения задачи оптимизации. Выделим ячейку D9 и вызовем Решатель (меню Сервис – Поиск решения). В появившемся окне абсолютный адрес $D$9 целевой ячейки уже установлен.


Устанавливаем флажок ^ Равной минимальному значению. Введем в окошко Изменяя ячейки абсолютные адреса $A$9:$B$9 диапазона ячеек, по которым будет минимизироваться значение целевой функции (неизвестные параметры m и b). Это можно сделать с клавиатуры, а также выделяя диапазон ячеек А9:В9 мышью. Ограничений в данной оптимизационной задаче нет.

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

^ Нажимаем кнопку ОК. Результат вычислений представлен ниже.

Таким образом, оптимальные значения коэффициентов линейной функции по критерию суммы квадратов отклонений равны m=1 и b=2 . Следовательно, оптимальной является линия Y=X+2. При этом оптимальное значение суммы квадратов отклонений равно 14.


^

3.Линейная регрессия



Так как задача отыскания функциональной зависимости очень важна, в введен специальный набор функций для решения этой задачи. Эти функции основаны на методе наименьших квадратов. При этом относительно исходных данных делаются некоторые статистические предположения, а в качестве результата выдаются не только коэффициенты функции, но и ряд других статистических характеристик.
ЗАДАЧА 3. Решить задачу 2 с помощью функции , предназначенной для расчета линейной регрессии.
Решение. Для решения поставленной задачи воспользуемся статистической функцией ЛИНЕЙН.

Скопируем рабочий лист Регрессия1 предыдущей задачи на новый лист, которому дадим имя Регрессия2 (для изменения имени рабочего листа достаточно двойного щелчка на нем и последующего набора с клавиатуры). Можно просто сохранить документ Задача2 под новым именем Задача3 и ввести новое имя рабочего листа.

^ Статистическая функция ЛИНЕЙН имеет следующий вид и синтаксис:
ЛИНЕЙН(Изв_знач_Y; Изв_знач_X; Константа; Стат).
В рассматриваемой задаче Изв_знач_Y находятся в диапазоне ячеек В2:В6, Изв_знач_X находятся в диапазоне ячеек В2:В6. Два последних аргумента – логические величины. Если аргумент Константа – ИСТИНА или опущен, то свободный член b в регрессионном уравнении может быть любым, а если значение аргумента Константа – ЛОЖЬ, то b принудительно полагается равным нулю. Если последний аргумент Стат – ЛОЖЬ или опущен, то вычисляются только коэффициенты m и b , а если ИСТИНА, то выдаются дополнительные статистические характеристики. Вместо ИСТИНА и ЛОЖЬ в функции можно вводить аргументы 1 и 0, что удобнее.

Так как функция ЛИНЕЙН возвращает (вычисляет и выдает) сразу несколько значений, формулу с этой функцией надо вводить как табличную. Если мы хотим вывести полную статистику, то надо выделить блок из пяти строк и двух столбцов. С помощью мыши выделим блок F2:G6, вызовем мастер функций, нажав на кнопку со значком fx на панели инструментов. В мастере функций выберем в качестве категории Статистические функции, а среди них – функцию ЛИНЕЙН. Нажмем кнопку ОК.


В открывшемся окне функции ЛИНЕЙН с клавиатуры латинским шрифтом (!!!) в качестве первого аргумента укажем блок ячеек В2:В6, в качестве второго аргумента – блок А2:А6.



В третьем и четвертом поле ввода поставим 1. Не щелкаем (!!!) по кнопке ОК, а нажимаем одновременно комбинацию клавиш (находясь в диалоговом окне!) Shift – Ctrl – Enter. Получаем таблицу результатов статистики данных решаемой задачи, вычисленную с помощью функции ЛИНЕЙН.

В ячейку F2 записан коэффициент m , в G2 - коэффициент b. Они в точности совпадают с решениями, полученными методом наименьших квадратов в предыдущей задаче. Под этими коэффициентами (в ячейках F3 и G3) стандартные отклонения (то есть среднеквадратичные отклонения, или квадратные корни из величин дисперсий) для этих коэффициентов.

^ В ячейку F4 записан так называемый коэффициент детерминации R2. Этот коэффициент лежит на отрезке [0,1]. Считается, что чем ближе этот коэффициент к 1, тем лучше уравнение регрессии описывает зависимость.

^ В ячейке G4 находится стандартная ошибка для оценки Y.

В ячейку F5 записано значение F-статистики, а в G5 – количество степеней свободы. Смысл этих величин можно выяснить в учебнике по статистике.

В последней строке таблицы результатов (ячейки F6 и G6) находятся регрессивная сумма квадратов (10) и сумма квадратов отклонений (14). Последнее число совпадает с полученным ранее числом, находящимся в ячейке D9.


^

4. Транспортная задача



ЗАДАЧА 4. Фирма, занимающая выпечкой хлеба, имеет пекарни в четырех городах. Хлеб поставляется в шесть населенных пунктов. Объемы производства, объемы потребления и цены доставки товара от каждой пекарни к каждому пункту известны и представлены в следующей таблице.



^ Стоимость перевозки единицы продукции

Объемы производства

2

5

3

7

6

5

150

4

1

4

6

5

6

100

4

4

1

4

3

3

80

5

3

5

3

4

3

70

50

40

60

140

70

40

Объемы потребления


Фирме требуется удовлетворить потребности всех населенных пунктов при минимальных расходах на перевозку.
Решение. В данной задаче имеет место сбалансированная модель, в которой суммарный объем производства равен суммарному объему сбыта.

Составим сначала математическую модель задачи.

^

Математическая модель транспортной задачи



Обозначим через V1, V2, V3, V4 объемы производства предприятий, а через W1, W2, W3, W4, W5, W6 – объемы потребления населенных пунктов. Через Pij (i=1,…, 4; j=1,…,6) обозначим цену перевозки единицы товара i-го предприятия j-му потребителю. Например, P23 – цена перевозки единицы продукции второго предприятия в третий населенный пункт. Через Xij (i=1,…, 4; j=1,…,6) обозначим объем продукции, поставляемой i-м предприятием j-му потребителю. Тогда стоимость перевозки Xij единиц продукции i-го предприятия j-му потребителю равна Pij*Xij. Стоимость перевозок всей продукции i-го предприятия потребителям равна Pi1*Xi1 + Pi2*Xi2 + … + Pi6*Xi6.

^ Полная стоимость перевозок всей продукции от поставщиков к потребителям является целевой функцией транспортной задача и подлежит минимизации:
Z = P11*X11 + P12*X12 + … + P16*X16 +

+ P21*X21 + P22*X22 + … + P26*X26 +

+ …+

+ P41*X41 + P42*X42 + … + P46*X46  min.

Ограничениями задачи являются балансовые равенства для всех для все поставщиков:
X11+X12+X13+X14+X15+X16=V1,

X21+X22+X23+X24+X25+X26=V2,

X31+X32+X33+X34+X35+X36=V3,

X41+X42+X43+X44+X45+X46=V4,
и всех потребителей:
X11+X21+X31+X41=W1,

X12+X22+X32+X42=W2,

X13+X23+X33+X43=W3,

X14+X24+X34+X44=W4,

X15+X25+X35+X45=W5,

X16+X26+X36+X46=W6.
Последним набором ограничений транспортной задачи являются условия неотрицательности переменных, то есть
Xij  0 для всех i=1,…, 4; j=1,…,6.
Заметим, что в более общей несбалансированной задаче в первой группе ограничений знаки равенства = должны быть заменены на знаки неравенства  , которые означают что производитель не может поставить больше товара, чем произвел его. Соответственно во второй группе ограничений знаки равенства = должны быть заменены на знаки неравенства  , которые означают что при корректной постановке задачи исключается недопоставка. При этом предполагается, что общая потребность в товарах не может быть больше, чем их запасы в пунктах отправления.
^ Решение задачи в Excel
Запустим программу , сохраним документ под названием, например, Задача4, дадим рабочему листу название, например, Транспорт.

Для решения введем данные в ячейки рабочего листа документе программы Excel (см. ниже). Если в наличии имеется готовая таблица, выполненная, например, в документе программы Word, то просто скопируем ее в буфер обмена, затем на рабочем листе документа программы выделим ячейку А1 и нажмем кнопку Вставить на панели инструментов. После этого необходимо, может быть скорректировать шрифт и размеры ячеек, подобно тому, как это сделано на рисунке ниже. Кроме того, на всякий случай выделить блоки ячеек со скопированными числовыми данными через меню Формат -Ячейки в открывшемся диалоговом окне Формат ячеек на вкладке Число в пометить в окошке Числовые форматы пункт Числовой и нажать на кнопку ОК. Это требуется для того, чтобы скопированную из документа программы Word, вообще говоря, текстовую информацию программа Excel воспринимала именно как числа.



В том случае, если требуется ввести исходные данные вручную, можно сделать это в следующем порядке. В ячейку А1 введем текст ^ Стоимость перевозки единицы продукции. Хотя текст значительно превышает размеры ячейки на это не стоит обращать внимания, поскольку временное исчезновение части текста происходит лишь при выделении или редактировании перекрываемых этим текстом ячеек. Для того, чтобы рабочий лист имел приличный и удобный вид, иногда требуется корректировать его геометрию. Больше на этом останавливаться не будем. В ячейки А6, G1, G6, А8, G8, A13, G13, A17, G17 для наглядности вводим текст, например, как показано ниже. Текст не оказывает никакого влияния (если, конечно, ячейки с текстом не перемещать, удалять и т.п., что может повлиять на адреса, стоящие в формулах) на решение задачи, а служит лишь для удобства и лучшего понимания процесса решения и полученных результатов.

В ячейки А2:F5 вводим цены перевозок продукции от поставщиков к потребителям. Например, выделим ячейку А2, наберем с клавиатуры число и нажмем клавишу Enter. Никаких знаков равенства перед числом ставить не следует. Таким же образом введем и остальные данные исходной таблицы транспортной задачи, в том числе, объемы производства и общие объемы поставок потребителям.

Перейдем теперь ко вводу переменных и функций задачи. В блок ячеек А9:F12 введем массив переменных. Поскольку их очень много, не будем им давать им (то есть содержащим их ячейкам) специальных имен типа Х12 , тем более, что в качестве имен нельзя употреблять наборы символов, совпадающие с адресами других ячеек (в рабочем листе имеется ячейка с адресом Х12). Оставим для этих переменных уже имеющиеся имена (или адреса) ячеек А9:F12. Выделим ячейку А9 и введем символы =0 (равно нулю), задавая этой переменной нулевое начальное значение. Во избежание дальнейших неприятностей обязательно нажмите клавишу Enter. То же самое проделаем с ячейками А10, А11, …, В9,…, В12, …,F 9,…, F12, то есть со всеми ячейками блока А9:F12.

Теперь для решения задачи и контроля результатов ячейки А15:F15 введем формулы подсчета суммарных поставок потребителям. Выделим ячейку А15 и вызовем Мастер функций, нажав на кнопку Вставка функций, находящуюся на панели инструментов и обозначенную значком fx. В появившемся диалоговом окне Мастера функций в окошке Категории щелкнем мышью на строке Математические, а затем в окошке Функция с помощью полосы прокрутки найдем и выделим щелчком функцию СУММ и нажмем кнопку ОК.



Появится новое диалоговое окно ^ Мастера функций. В этом окне имеется два поля ввода аргументов функций, обозначенных Число 1 и Число 2. При необходимости появится третье, четвертое и т.д. Синтаксис функции СУММ допускает до 30 аргументов. В качестве аргумента можно использовать не одно число (ячейку), а целый блок, в частности программа уже предложила в качестве первого аргумента блок ячеек А9:А14, но слегка ошиблась. Мы ее подправим и вставим А9:А12. В этом случае в ячейке А15 будет находиться сумма чисел из ячеек А9:А12, то есть сумма поставок товара от всех производителей в первый населенный пункт. Теперь нажмем кнопку ОК.

Окно закрылось и в ячейке А15 появилось число 0, так как в ячейках блока ячеек А9:А12 в данный момент также стоят нули. В строке формул, находящейся сразу над рабочим листом справа появилась требуемая формула =СУММ(А9:А12) (если в данный момент выделена ячейка А15). Ту же процедуру ввода функций можно было бы повторить и для ячеек В15, С15, D15, E15, F15. Однако этого не требуется. Достаточно просто скопировать формулу из ячейки А15 в эти ячейки. Для этого щелкнем мышью на ячейке А15 и на панели инструментов нажмем кнопку Скопировать. Ячейка А15 станет выделенной «движущейся» рамкой. Щелкнем на ячейке В15 и нажмем на панели инструментов кнопку Вставить. Формула скопирована в ячейку В15, причем в формулу автоматически внесены необходимые исправления, то есть в ячейку В15 записана требуемая формула: =СУММ(В9:В12). Аналогичную операцию копирования проделаем для ячеек С15, D15, E15, F15. После ввода формул во всех перечисленных ячейках должны появиться нули.

Формулу в ячейку А15 можно было бы вписать, не используя мастер функций, просто вписав в нее набор символов =СУММ(А9:А12), начав его со знака равенства и набирая адрес блока ячеек А9:А12 латинскими(!!!) буквами. Мы воспользовались Мастером функций с учебными целями. Кроме того, пользователь не всегда помнит наизусть точное написание, синтаксис, количество, порядок следования и формат аргументов. Поэтому зачастую без Мастера функций невозможно обойтись.

Для себя и для возможных других пользователей внесем в ячейку А14 поясняющий текст СУММ(А9:А12) (без знака равенства!!!). Внесение аналогичных поясняющих надписей в ячейки В14 и т.д. нецелесообразно, так как может потребовать перекройки всего рабочего листа и будет загромождать его излишней информацией. Хотя это дело вкуса.

Для вычисления суммарных объемов производства (или суммарных поставок каждого поставщика) в ячейки G9:G12 впишем таким же образом аналогичные формулы. В ячейку G9 введем формулу =СУММ(А9:F9). Для разнообразия сделаем это с клавиатуры, вписывая указанный набор символов, причем символы СУММ наберем кириллицей, а (А9:F9) – латинскими буквами. Затем скопируем эту формулу в ячейки G10, G11, G12. Программа Excel внесет в копируемые формулы нужные коррективы.

В ячейке G15 вычислим суммарный объем поставок по всем потребителям с помощью формулы =СУММ(А15:F15). В ячейку G14 внесем поясняющий текст СУММ(А15:F15) без знака равенства.

Перейдем, наконец, к вычислению целевой функции задачи. Начнем с вычисления стоимости перевозок для каждого потребителя. В ячейку А19 поместим функцию, вычисляющую сумму произведений цен перевозки единицы товара от каждого поставщика на количество поставляемого товара. Такие вычисления выполняет функция СУММПРОИЗВ с подходящими аргументами. С помощью Мастера функций или с клавиатуры введем в А19 формулу =СУММПРОИЗВ(А2:А5,А9:А12). После этого скопируем обычным образом эту формулу в ячейки В19, …, F19. При правильном вводе формулы в ячейках должны появиться нули. Для пояснения в ячейку А18 впишем, опуская знак равенства, текст СУММПРОИЗВ(А2:А5,А9:А12).

В ячейке G19 вычислим значение целевой функции задачи. Эту роль может выполнить аналогичная формула =СУММПРОИЗВ(А2:F5,А9:F12), вычисляющая суммы произведений содержимого блоков ячеек А2:F5 и А9:F12. Однако, можно просто сложить стоимости перевозок по всем потребителям, то есть содержимое блока ячеек А19:F19. Остановимся на этом варианте и введем в ячейку G19 формулу =СУММ(А19:F19). В ячейку А18 впишем поясняющий текст СУММ(А19:F19).

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


Теперь можно непосредственно приступить к решению задачи оптимизации, то есть к вычислению оптимального графика поставок, минимизирующего стоимость всех перевозок товара от поставщиков к потребителям. Активизируем (выделим) щелчком мыши ячейку с целевой функцией задачи оптимизации G19 и через меню Сервис-Поиск решения вызовем Решатель. Откроется диалоговое окно Поиск решения. В поле ввода уже записан (если нет, введите его с клавиатуры сами) абсолютный адрес $G$19 ячейки, содержащей формулу целевой функции. Поскольку в задаче требуется определить минимум целевой функции, то ниже этого поля ввода следует поставить флажок Равной минимальному значению. Переменные задачи содержатся в блоке ячеек А9:F12, поэтому в поле ввода Изменяя ячейки следует ввести абсолютные адреса $А$9:$F$12 этого блока.

Осталось ввести в алгоритм Решателя ограничения задачи. Ниже поля ввода ^ Изменяя ячейки расположено окошко Ограничения. Однако непосредственно оно недоступно. Справа от него расположены три кнопки Добавить, Изменить, Удалить. Эти кнопки предназначены для команд на ввод и редактирование ограничений. Нажмите на кнопку Добавить.

Появилось диалоговое окно ^ Добавление ограничения. В этом окне имеются три поля ввода. Первое из них предназначено для ввода левой части ограничения. Напомним, что в соответствии с математической моделью в задаче имеются три группы ограничений: балансовые равенства для поставщиков, для потребителей и условия неотрицательности переменных. Начнем с ввода равенств для поставщиков. Суммы их поставок (объемы производства) вычисляются в ячейках G9:G12. Если требуется, перенесем, взяв мышью за строку заголовка, диалоговое окно Добавление ограничения так, чтобы стали доступны эти ячейки. Щелкнем по ячейке G9, содержащей сумму поставок первого поставщика. В первом поле ввода Ссылка на ячейку появится абсолютный адрес $G$9 этой ячейки. Во втором поле ввода требуется выбрать тип ограничения из выпадающего списка. В данном случае имеет место ограничение типа равенства, поэтому из списка выберем знак равенства =. Наконец, в третье поле ввода Ограничение нужно ввести число, определяющее объем производства V1 первого поставщика, которое при вводе исходных данных задачи попало в ячейку G2. Щелкнем мышью по этой ячейке и в поле ввода Ограничение появится абсолютный адрес $G$2. Ввод первого ограничения закончен. Если бы оно было единственным, то следовало бы нажать кнопку ОК. Однако, это не так. Поэтому нажмите кнопку Добавить.

После этого первое ограничение будет занесено в список Решателя, а диалоговое окно Добавление ограничения снова предстанет с чистыми полями ввода. Повторите описанную процедуру для ввода ограничения, касающегося второго поставщика, имея дело с ячейками G10 и G3. Аналогично введите третье (ячейки G11 и G4) и четвертое ограничение (ячейки G12 и G5).

Не выходя из диалогового окна Добавление ограничения, введите в список ограничений равенства для всех шести потребителей. Эти ограничения определяются парами ячеек (А15 и А6), (В15 и В6), (С15 и С6), (D15 и D6), (E15 и E6), (F15 и F6). Каждый раз вводите знак равенства и после ввода ограничения и не выходите из диалогового окна, нажимая кнопку Добавить. Делайте все это не спеша, контролируя и корректируя правильность ввода адресов ячеек.

Последнюю группу ограничений неотрицательности переменных задачи вводим аналогично. В поле ввода ^ Ссылка на ячейку введем абсолютные адреса $А$9:$F$12 блока ячеек. Этот набор символов можно набрать с клавиатуры или с помощью мыши, охватив и выделив весь блок ячеек А9:F12 одновременно. Во второе поле ввода внесите знак неравенства >=, а в третье поле ввода впишите число 0. Теперь, поскольку введено последнее ограничения задачи, нажмите кнопку ОК.

В открывшемся снова диалоговом окне ^ Поиск решения в окошке Ограничения появился весь список введенных ограничений. Используя полосу прокрутки, внимательно просмотрите их все. Если имеется ошибка или какого-либо ограничения не хватает или введено нечто лишнее, выделите нужную строку и используя кнопки Добавить, Изменить, Удалить исправьте неточность.

Теперь все готово для запуска алгоритма решения задачи линейного программирования программы Excel. Нажмите кнопку Выполнить. Спустя некоторое время в соответствующих ячейках появятся результаты вычислений и диалоговое окно Результаты поиска решения. Если все сделано правильно, результаты выглядят вполне приемлемыми, то следует согласиться с предложением Сохранить найденное решение и нажать кнопку ОК. Перед этим можно предусмотреть выдачу отчетов трех типов, в которых приведен анализ полученного решения.

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

Мы не будем останавливаться на полученном решении задачи, само по себе оно малоинтересно, поскольку является лишь учебным примером. Отметим лишь, что все ограничения в полученной оптимальном решении выполнены и само решение очевидно оптимально.

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




^

5. Анализ и сравнение инвестиционных проектов



ЗАДАЧА 5. Инвестор имеет возможность выбрать один из трех инвестиционных проектов (I, II, III), требующих начальных инвестиций (год 0) с предполагаемыми денежными поступлениями в последующие три года.


Год

I

II

III

0

-17000000

-20000000

-30000000

1

3000000

14000000

12000000

2

4000000

8000000

12000000

3

17000000

4000000

16000000


Определить для проектов чистый приведенный доход и внутреннюю доходность при годовой процентной ставке 15%. Составить отчет по сценариям. Сравнить проекты по вычисленным показателям.
Решение. Для вычисления чистого приведенного дохода (NPV) в Excel имеется функция НПЗ – чистое (нетто) приведенное значение. Внутренняя доходность (IRR) потока платежей определяется посредством функции ВНДОХ – внутренняя доходность.

^ Запустим программу Excel, дадим имя первому рабочему листу Проекты, сохраним документ.

В ячейку А1 внесем Заголовок Анализ инвестиционных проектов. В ячейки В2, А3, А5 также запишем вспомогательную текстовую информацию: Первый проект, Ставка, Год. В ячейку В3 внесем процентную ставку – 15%.

Далее, вообще говоря, можно было бы просто скопировать исходную таблицу на рабочий лист документа Excel и вычислить требуемые показатели сразу для всех трех проектов, но поскольку в задаче требуется определить сценарии, рассмотрим сначала первый проект. В вручную с клавиатуры или посредством копирования из двух первых столбцов исходной таблицы в ячейки блока А6:В9 введем номера годов и потока инвестиций и платежей первого проекта.

Исходные данные задачи для первого проекта полностью внесены на рабочий лист. Под этими данными запишем функции для вычисления требуемых экономических показателей. Но сначала введем функцию, вычисляющую простую сумму денежных вложений и поступлений первого проекта, несмотря на то, что этот подсчет проще сделать в уме. В ячейку В10 впишем формулу =СУММ(В6:В9), начинающуюся со знака равенства (обязательно!!!). При этом, чтобы избежать неприятностей в дальнейшем, адреса В6:В9 блока ячеек также обязательно следует набрать латинскими буквами. Закончить ввод формул следует нажатием клавиши Enter. Если все сделано правильно, то в ячейке В10 сразу появится результат вычислений. В ячейку А10 для удобства пользователя запишем текст СУММ(В6:В9) без знака равенства (!!!), указывающий, что вычисляется в ячейке В10. В тексте выбор букв совершенно свободен.

Далее в ячейку В11 введем формулу, вычисляющую чистый приведенный доход. Сделаем это с помощью Мастера функций. Выделим ячейку В11 мышью и на панели инструментов нажмем кнопку Вставка функций, обозначенную значком fx. В появившемся диалоговом окне Мастера функций в разделе Категория отметим мышью Финансовые. Затем в разделе Функция найдем с помощью полосы прокрутки и также отметим функцию НПЗ. Заметим, что ниже появилась строка, показывающая синтаксис этой функции. Нажмем кнопку ОК.

Открылось второе диалоговое окно Мастера функций с полями ввода аргументов. В поле Норма запишем латинскими символами адрес ячейки В3, содержащей значение процентной ставки. Можно было бы в это поле сразу вписать число 15% или 0.15, но в этом случае мы лишили бы себя возможности мгновенно пересчитать НПЗ при изменении процентной ставки. В поле ввода Значение 1 запишем адреса ячеек блока В6:В9 (латинскими буквами!!!), содержащих величины денежных поступлений для первого проекта. Заметим, что в самом низу диалогового окна уже вычислена величина чистого приведенного дохода. Нажмем кнопку ОК.

После закрытия диалогового окна в ячейке В11 появится значение чистого приведенного дохода, а в ^ Строке формул, расположенной над рабочим листом вписана формула =НПЗ(В3,В7:В9). Для удобства в ячейку А11 запишем поясняющий формулу текст НПЗ(В3,В7:В9).

Таким же образом с помощью Мастера функций или с клавиатуры в ячейку В12 запишем формулу =ВНДОХ(В6:В9). В ячейку А12 впишем текст ВНДОХ(В6:В9).

Требуемые экономические показатели для проекта вычислены.


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

В меню выберем ^ Сервис – Сценарии. Откроется диалоговое окно Диспетчер сценариев, в котором нажмем кнопку Добавить. Откроется новое диалоговое окно Изменение сценария. В первое поле ввода Название сценария запишем Второй проект. В поле Изменяемые ячейки запишем латинскими символами абсолютные адреса $B6:$B9 блока ячеек В6:В9. Это же можно сделать, выделяя мышью этот блок целиком. Отключим внизу окна опцию запретить изменения. Нажмем кнопку ОК.

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


Появится диалоговое окно ^ Добавление сценария совершенно аналогичное открывавшемуся ранее окну Изменение сценария. Проделаем ту же процедуру и для третьего проекта, внося его исходные данные.

Далее, хотя для первого проекта уже все просчитано, в список сценариев он еще не внесен. Можно было начать именно с него. Не меняя данных в диалоговом окне Значения ячеек сценария включим в список таким же образом и первый проект. Нажмем теперь кнопку ОК. Откроется окно Диспетчер сценариев со списком, которое можно использовать сейчас или в дальнейшем для добавления, удаления, изменения сценариев. Вызов диспетчера сценариев осуществляется через меню Сервис – Сценарии.





Если в окне Диспетчера сценариев отметить второй проект и нажать кнопку Вывести, то на месте данных и вычисленных показателей первого проекта будут выведены данные и показатели второго проекта. Этим мы займемся позднее, а сейчас сделаем отчет по сценариям. Нажмем кнопку Отчет.


В диалоговом окне ^ Отчет по сценарию в поле Ячейки результата внесем адреса ячеек В10, В11, В12. Заметим, что в этот список может быть выбран по полному произволу пользователя. Выберем сначала Тип отчета – структура. Нажмем кнопку ОК. На экран будет выведен дополнительный рабочий лист с отчетом Структура сценария.

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

Перейдем, наконец, к переключениям сценариев. Вызвав Диспетчер сценариев можно на рабочий лист вывести любой сценарий, менять различные данные в каждом из них, добиваться получения приемлемых экономических показателей и т.п. Таким образом, такой документ программы Excel или подобный ему может быть основой для расчета и анализа вполне реальных инвестиционных проектов.

Приведем в заключение рабочий лист, содержащий сводную таблицу для экономических показателей всех трех проектов. Его легко сделать с помощью переключения сценариев и копирования соответствующих ячеек столбца В в ячейки столбцов С и D.

^ Сравнение проектов и их экономический анализ предоставляем читателю.



Литература





  1. Банди Б. Основы линейного программирования. М., Радио и связь, 1989.

  2. Бухвалов А.В., Идельсон А.В. Самоучитель по финансовым расчетам. – М., Мир, Пресс-сервис, 1997.

  3. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. СПб., БХВ – Санкт-Петербург, 1999.

  4. Гусева О.Л., Миронова Н.Н. Практикум по Excel. – М., Финансы и статистика, 1997.

  5. Интрилигатор М. Математические методы оптимизации и экономическая теория. – М., Прогресс, 1975.

  6. Каратыгин С.А. Access 97. Руководство пользователя. – М., ВКК, 1997.

  7. Колемаев В.А. и др. Теория вероятностей и математическая статистика. - М., Высшая школа, 1991.

  8. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. – СПб., БХВ – Санкт-Петербург, 1997.

  9. Лавренов С.М. Excel. Сборник примеров и задач. – М., Финансы и статистика, 2000.

  10. Рычков В. Самоучитель Excel 2000. – СПб., Питер, 1999.

  11. Четыркин Е.М. Методы финансовых и коммерческих расчетов. – М., Дело Лтд, 1995.

  12. Юдин Д.Б., Гольштейн Е.Г. Линейное программирование. Теория, методы, приложения. – М., Наука, 1969.



Скачать файл (1038.5 kb.)

Поиск по сайту:  

© gendocs.ru
При копировании укажите ссылку.
обратиться к администрации