Практическая работа №5.
«Экономические расчеты в MS Excel»
Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).
Задание 1. Составление плана выгодного производства.
Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р.? В — 15 р. и С — 20 р. на единицу изделия.
Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство продукции каждого вида приведены в табл. 3.
Таблица 3
Сырье для производства продукции
Сырье | Нормы расхода сырья | Запас сырья | ||
А | В | С | ||
Сырье 1 | ||||
Сырье 2 | ||||
Сырье 3 | ||||
Прибыль |
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
|
|
2. Создайте расчетную таблицу как на рис. 17. Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид:
Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).
3. Аналогично рассчитываются расходы сырья 2 и сырья 3. Используйте функцию автозаполнение.
4. Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания.
(Общая прибыль по А) = (прибыль на ед. изделий А) * (количество А).
Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С).
План выгодного производства
Сырье | Норма расхода сырья | Запас сырья | Расход сырья | ||
А | В | С | |||
Сырье 1 | ? | ||||
Сырье 2 | ? | ||||
Сырье 3 | ? | ||||
Прибыль на ед.изд | |||||
Количество | ? | ? | ? | ||
Общая прибыль | ? | ? | ? | ? |
Рис. 17. Исходные данные для задания
5. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска.
6. В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль», в качестве изменяемых ячеек — ячейки количества сырья.
7. Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:
ü расход сырья 1 < = 350;
ü расход сырья 2 < = 200;
ü расход сырья 3 < = 100,
ü а также положительные значения количества сырья А, В, С > = 0.
8. Установите параметры поиска решения рис.18. Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска
Рис. 18. Параметры поиска решения
|
|
9. Кнопкой Выполнить запустите Поиск решения. Если вы сделали все верно, то решение будет как в выводах.
10. Сохраните созданный документ под именем «План производства».
Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 р.
Задание 1. Используя файл «План производства» определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соответствующего варианта:
Вариант 1
Сырье | Нормы расхода сырья | Запас сырья | ||
А | В | С | ||
Сырье 1 | ||||
Сырье 2 | ||||
Сырье 3 | ||||
Прибыль на ед. изделия | ||||
Кол. продукции | ||||
Общая прибыль |
Вариант 2
Сырье | Нормы расхода сырья | Запас сырья | ||
А | В | С | ||
Сырье 1 | ||||
Сырье 2 | ||||
Сырье 3 | ||||
Прибыль на ед. изделия | ||||
Кол. продукции | ||||
Общая прибыль |
Вариант 3
Сырье | Нормы расхода сырья | Запас сырья | ||
А | В | С | ||
Сырье 1 | ||||
Сырье 2 | ||||
Сырье 3 | ||||
Прибыль на ед. изделия | ||||
Кол. продукции | ||||
Общая прибыль |
Вариант 4
Сырье | Нормы расхода сырья | Запас сырья | ||
А | В | С | ||
Сырье 1 | ||||
Сырье 2 | ||||
Сырье 3 | ||||
Прибыль на ед. изделия | ||||
Кол. продукции | ||||
Общая прибыль |