Диспетчер сценариев в Microsoft Excel

Лабораторная работа №3. Надстройка Поиск решения в Microsoft Excel. Диспетчер сценариев в Microsoft Excel.

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

К защите лабораторной работы студент должен предоставить файлы Поиск решения.xls и Сценарии.xls путь сохранения D:\номер группы\Анализ данных.  

Задача 1. Задача о проектах

Постановка задачи. Очень часто компаниям приходится иметь дело с проектами, бюджет которых рассчитывается на год. Пусть годовой бюджет компании равен $500 000, а запланировано только $359 822. Предположим, вы хотите, чтобы каждый проект стоил $50 000, и готовы увеличить часть средств, которая уходит на маркетинговые исследования и рекламу. Стоимость каждого проекта рассчитывается как сумма стоимости производства, накладных расходов, расходов на маркетинг и рекламу.

Какими должны быть расходы на Маркетинг и Рекламу, чтобы годовой бюджет стал равным $500 000 при стоимости каждого проекта $50 000 и расходах на маркетинг не менее, чем $6 000?.

Порядок выполнения. 

Скопируйте файл Поиск решения.xls с исходными данными  с сетевого окружения (адрес уточните у преподавателя). На листе Задача 1 вставьте соответствующие функции вместо символа * и выполните команду Сервис – Поиск решения…. В диалоговом окне Поиск решения установите следующие ограничения (рис. 10). Для добавления ограничений в список Ограничения: следует воспользоваться кнопкой Добавить. В диалоговом окне Добавление ограничения после ввода ограничения нужно нажать кнопку Добавить. После добавления всех ограничений нужно нажать кнопку ОК. (рис. 11).

Рис. 10. 

Рис. 11.

В окне Поиск решения нажать кнопку Выполнить. Сохраните и проанализируйте найденное решение.

Задача 2. Оптимизация инвестиционного портфеля

Постановка задачи. Имеются данные по инвестиционному портфелю:

Какие суммы необходимо вложить в каждый из шести активов (A,B,C,D,E,F), чтобы получить максимально возможную доходность портфеля с учетом определенных ограничений: 

1.  Общая сумма вложений не превышает 200 000.

2.  Сумма вложений в каждый актив – положительное число.

3.  Сумма вложений в каждый актив не превышает четверти от Общей суммы вложений.

4.  Сумма в надежных активах составляет не менее 2/3 от Общей суммы вложений.

5.  Сумма в долгосрочных активах составляет не менее половины от Общей суммы вложений.

Порядок выполнения. 

В рабочей книге Поиск решения.xls На листе Задача 2 задайте произвольные значения столбца Сумма вложений.

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

С помощью функции СУММ() рассчитайте общую сумму вложений в портфель и общую прибыль портфеля.

С помощью функции СУММЕСЛИ() рассчитайте значение суммы в надежных активах и суммы в долгосрочных активах. Надежным считается актив, у которого значение Надежности не менее 4. Долгосрочным считается актив, Срок выкупа по которому наступает позднее 2007 года.

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

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

Добавьте ограничения 1-5 из условия задачи (кнопка Добавить).После заполнения диалога, нажмите кнопку Выполнить. Сохраните и проанализируйте найденное решение. Задача 3. О планировании производства.

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

2     

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

Порядок выполнения. 

В рабочей книге Поиск решения.xls На листе Задача 3 задайте произвольные значения столбца Количество.

Рассчитайте значение ячейки Расход запасов (трудозатраты) как сумму произведений количества каждого вида компьютеров на нормы трудозатрат для производства 1 компьютера каждого вида. Аналогично рассчитайте Расход запасов комплектующих на производство всех компьютеров.

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

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

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

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

Постановка задачи.

Некоторая фирма имеет 4 фабрики и 4 склада в различных городах. Фабрики имеют производственные мощности для выпуска соответственно 25, 30, 50, 20 единиц продукции ежедневно. Центры распределения товаров (склады) имеют ежедневные потребности в продукции 30, 20, 60, 15 единиц соответственно. Стоимость перевозки единицы продукции с фабрик на склады приведена в таблице 

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

Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы при этом полностью использовать мощности производств и полностью обеспечить потребности складов.

Порядок выполнения. 

В рабочей книге Поиск решения.xls в таблице решения рассчитайте значения строки Объемы потребления (B16:E16) и столбца Объем производства (F12:F15) как сумму по строке и столбцу соответственно. Суммарные транспортные расходы рассчитываются как сумма произведений стоимости перевозки на количество перевезенного товара (=СУММПРОИЗВ(B3:E6;B12:E15)).

Выполните команду Сервис – Поиск решения. В качестве целевой ячейки выберите ячейку с расчетом Суммарные транспортные расходы, установите ее равной минимальному значению. Выберите ячейки, содержащие Объем перевозок (B12:E15), в качестве ячеек для изменения.

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

Постановка задачи.  Пусть имеются следующие экономические и статистические показатели деятельности магазина.

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

Порядок выполнения. 

Создайте новую рабочую книгу и сохраните ее под именем Сценарии.xls путь сохранения D:\номер группы\Анализ данных.  

На рабочем листе для расчета чистой прибыли сформируйте следующую таблицу с данными :

Рис. 12. 

4

Таблица должна содержать следующие формулы:

Для удобства создания сценариев при решении задачи присвойте имена ячейкам таблицы с исходными данными (С2:С5 , С8:С13  и С16). Для этого, выделяя соответствующую ячейку и выполняя команду Вставка–Имя–Присвоить, задайте соответствующие имена.

Создайте три сценария. Первый сценарий должен содержать первоначальные значения и меть имя Исходные данные. Для создания сценариев выполните следующие действия:

•      выполните команду Сервис-Сценарии;

•      в окне Диспетчер сценариев нажмите кнопку Добавить…;

•      в окне Добавления сценария укажите: название сценария и изменяемые ячейки в соответствии с рис. 13. 

Рис. 13.

•      в диалоговом окне Значения ячеек сценария оставьте исходные данные без изменений и нажмите ОК.

Создайте Сценарий 1 и Сценарий 2, в качестве изменяемых ячеек для каждого сценария укажите ячейки $C$2:$C$3 и $C$5, а в диалоговом окне Значения ячеек сценария для каждого сценария  укажите значения в соответствии с условием задачи.

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

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

Сохраните файл под тем же именем.

Задача 2. (задание для самостоятельной работы)

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

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