Задание 2. Анализ чувствительности при варьировании двумя параметрами

Задача: Исследовать, как прибыль будет зависеть от переменных издержек на единицу товара и от цены товара.

Условие задачи. Входные предложения представлены в таблице 5.

Таблица 5

Известна также зависимость спроса от цены: Спрос(Цена)=1000000-800*Цена

Решение

1. Создайте файл 3_2.xls. Заполните и рассчитайте таблицы аналогично пунктам 1-5 решения первого задания. Ячейки В11:В37 (цена) заполните диапазоном цен 480-1000, с шагом 20, а ячейки С10:N10 (себестоимость единицы продукции) от 400 до 510 с шагом 10 (см. рисунок 26). В ячейку В10 введите формулу =Прибыль.

Рисунок 26

2. Выделите диапазон B10:N37, примените команду Анализ «что-если» / Таблица данных, в появившемся диалоговом окне заполните поле Подставлять значения по столбцам ссылкой на ячейку $C$2 (Себестоимость единицы) поле Подставлять значения по строкам ссылкой на ячейку $C$1 (Цена).

Рисунок 27

3. Проведите анализ полученного решения и сделайте выводы.

Лабораторная работа 4

Использование надстройки Поиск решения для нахождения плана продукции

Предварительная подготовка

Надстройка Поиск решения (Solver) позволяет при заданных ограничениях, варьируя параметрами (значениями в ячейках) подобрать значение в целевой ячейки так, чтобы формула, введенная в ячейку, принимала нужное значение (минимальное, максимальное или равное какой-либо величине). При выполнении расчетов заданного значения применяются различные математические методы поиска. Результаты могут заноситься в ячейки автоматически или формироваться в виде отчетов. Основное применение надстройки – решение оптимизационных задач.

Надстройка Поиск решения (Solver), по умолчанию, отключена в MS Excel. В Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти (см. рисунок 28).

Рисунок 28

В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения (можете отметить и другие надстройки как на рисунке 29) и нажмите кнопку ОК (рисунок 29). Иногда после этого появляется окно настройки Microsoft Office подождите пока выполнится настройка, если программа запросит подтверждения ответьте Да.

Рисунок 29

В Excel 2003 выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. Иногда может понадобиться установочный компакт-диск Office.

Задание 1.

Задача.

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

Таблица 6

Решение