Решение задач оптимизации

Лабораторная работа 30. Доходность акций

Вам необходимо подготовить отчет о работе АО «Пирамида». Для этого подготовьте следующую таблицу:

Пятый столбец таблицы содержит формулы для расчета чистой прибыли от продаж (ЧПП) акций. Для текущего года формула вычисления ЧПП выглядит так:

ЧПП=(ЦАТ-ЦАП+ПП)/ЦАП

где ЦАТ - цена акций в этом году,

ЦАП - цена акций в прошлом году,

ПП - прибыль по акциям в прошлом году.

То есть в ячейку Е4 занести формулу: =(В4-В3+С3)/В3.

Заполните столбец Е соответствующими формулами.

Упражнение№1

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

Упражнение№2

Постройте гистограмму для столбцов «Прибыль по акциям» и «Дивиденды».

Упражнение№3

Постройте круговую диаграмму для показателя «Дивиденды».

Упражнение№4

Постройте смешанную диаграмму для показателей «Прибыль по акциям» и ЧПП.

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

Цель работы: Знать, как использовать программу Excel для решения сложных задач оптимизации. Научиться формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, и производить поиск оптимального набора переменных.

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

	Прибор А	Прибор В	Прибор С
Тип 1
Тип 2
Тип 3

Стоимость изготовленных приборов одинакова.

Ежедневно на склад поступает 500 микросхем типа 1 и по 400 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

1. Запустите программу Excel и откройте новую рабочую книгу.

2. Дважды щелкните на ярлычке листа и присвойте ему имя Организация производства.

3. В ячейке А2, А3 и А4 занесите дневной запас комплектующих – числа 500, 400 и 400, соответственно.

4. В ячейке C1, D1 и Е1 занесите нули – в дальнейшем значения этих ячеек будут подобраны автоматически.

5. В ячейках диапазона С2:Е4 разместите таблицу расхода комплектующих.

6. В ячейках В2:В4 нужно указать формулы для расчета расхода комплектующих по типам. В ячейке В2 формула будет иметь вид = $C$1*C2+$D$1*D2+$E$1*E2, а остальные формулы можно получить методом автозаполнения (обратите внимание на использование абсолютных и относительных ссылок).

7. В ячейку F1 занесите формулу, вычисляющую общее число произведенных приборов: для этого выделите диапазон С1:Е1 и щелкните на кнопке Автосумма на стандартной панели инструментов.

8. Дайте команду Сервис ®Поиск решения – откроется диалоговое окно Поиск решения.

9. В поле Установить целевую укажите ячейку, содержащую оптимизируемое значение (F1). Установите переключатель Равной максимальному значению (требуется максимальный объем производства).

10. В поле Изменяя ячейки задайте диапазон подбираемых параметров – С1:Е1.

11. Чтобы определить набор ограничений, щелкните на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диапазон В2:В4. В качестве условия задайте <=. В поле Ограничение задайте диапазон А2:А4. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке Ок.

12. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Е1. В качестве условия задайте >=. В поле Ограничение задайте число 0. Щелкните на кнопке Ок.

13. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Е1. В качестве условия выберите пункт цел. Это условие не позволяет производить доли приборов. Щелкните на кнопке Ок.

14. Щелкните на кнопке Выполнить. По завершении оптимизации откроется диалоговое окно Результаты поиска решения.

15. Установите переключатель Сохранить найденное решение, после чего щелкните на кнопке Ок.

16. Проанализируйте полученное решение. Кажется ли оно очевидным? Проверьте его оптимальность, экспериментируя со значениями ячеек С1:Е1. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решений.

17. Сохраните рабочую книгу.

Дата добавления: 2014-12-25