1. Создадим на листе «Поиск решения» таблицы согласно своему варианту индивидуальной работы. Данные рассматриваемого примера приведены в Приложении 2. Исходные данные занимают на листе строки с 1 по 29.
Примечание: 1) Для расчета фонда времени персонала в ячейке В23 считаем, что в течение месяца 8 человек работают 24 дня при 8-и часовом рабочем дне. 2) Для расчета фонда времени оборудования в ячейках F14-F16 считаем, что линии могут работать в 16 часов в сутки.
2. В ячейку С33 введем целевую функцию (формулу определения прибыли в краткосрочный период), Приложение 2.
3. Для упрощения ввода ограничений при использовании «Поиска решения» введем формулы, Приложение 2:
3.1. В ячейки B36, C36, D36 введем формулы для подсчета затрат производственных мощностей, требуемых для выполнения производственной программы, т.е. времени, необходимого для изготовления искомого объема каждого вида продукции.
3.2. В ячейку В37 введем формулу для подсчета рабочего времени персонала, требуемого для выполнения производственной программы.
3.3. В ячейку В38 введем формулу для определения количества сырья, необходимого для выполнения производственной программы.
4. Выполним команду Сервис - Поиск решения.
5. В диалоговом окне «Поиск решения» в поле «Установить целевую ячейку» введем адрес ячейки С33, содержащей формулу определения прибыли (рис. 12).
6. Так как требуется определить максимальное значение прибыли, установим флажок «максимальному значению».
7. В поле «Изменяя ячейки» укажем диапазон ячеек, в которых должен быть найден объем выпускаемой продукции. Это ячейки Е6, Е7, Е8.
Рис.12. Диалоговое окно «Поиск решения»
8. Введем ограничения. Например, для ввода ограничения на трудовые ресурсы выполним следующие действия:
- щелкнем по кнопке Добавить. Появится окно «Добавление ограничения» (рис. 13).
- в поле «Ссылка на ячейку» введем адрес ячейки, в которой подсчитывается рабочее время персонала, требуемое для выполнения производственной программы. Это ячейка В37.
- выберем нужный знак сравнения. В данном случае это «меньше или равно»
- в поле «Ограничение» укажем адрес ячейки, в которой находится ограничивающее число. В данном случае это ячейка В23, в которую введен фонд времени персонала. Таким образом, мы задали условие, что время, требуемое персоналу для выполнения всего объема производства, не может превышать фонд рабочего времени персонала.
Рис. 13. Диалоговое окно «Добавление ограничения»
- щелкнем по кнопке Добавить. Введем следующее ограничение. После ввода всех ограничений щелкнем по кнопке ОК.
Помимо введенного ограничения на трудовые ресурсы в рассматриваемом примере должны быть введены так же следующие ограничения.
Ограничения на производственные мощности:
$B$36<=$F$14; $C$36<=$F$15; $D$36<=$F$16
Ограничение на сырье:
$B$38<=$E$29+$F$29
Ограничения на выпускаемую продукцию:
$E$6<=$C$14; $E$6>=$B$14; $E$7<=$C$15; $E$7>=$B$15;
$E$8<=$C$16; $E$8>=$B$16
Если объем выпускаемой продукции может измеряться только в целых числах (например, столы, стулья), то необходимо ввести соответствующие ограничения:
$E$6=целое; $E$7=целое; $E$8=целое
Если потребуется добавить, изменить или удалить какое-либо ограничение, то нужно использовать соответствующие кнопки, расположенные рядом с полем «Ограничения».
9. Окно «Поиск решения» примет вид, представленный на рисунке 14.
Рис. 14. Окно «Поиск решения» после ввода ограничений
10. Нажмем кнопку Выполнить. После выполнения поиска решения на экран будет выведено диалоговое окно «Результаты поиска решения» (рис. 15).
Рис. 15. Диалоговое окно «Результаты поиска решения»
11. Установим в этом окне флажок «Сохранить найденное решение». При этом в изменяемых ячейках (Е6:Е8) будут выведены найденные значения объема производства.
12. Создадим отчет по результатам поиска решения. Для этого в окне «Результаты поиска решения» в поле «Тип отчета» выберем Результаты. Нажмем кнопку ОК. В рабочей книге появится лист с именем «Отчет по результатам».
При выполнении пункта 2 задания в окне «Поиск решения» в поле «значению» нужно ввести конкретное значение прибыли.
Варианты для выполнения задания 3
Задание 4. Применение таблиц подстановки данных в экономических расчетах
Рассмотрим следующую задачу. Банк выдал долгосрочный кредит. Известен размер кредита, срок и ставка кредита. Погашение кредита производится равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Нужно составить план погашения кредита с использованием финансовых функций и таблиц подстановки данных с одной и двумя переменными.
Выполните следующие задания в соответствии со своим вариантом (таблица 3).
1) Назовите лист рабочей книги «Таблица подстановки 1». Составьте на этом листе план погашения кредита, выданного банком на заданный срок.
2) Назовите лист рабочей книги «Таблица подстановки 2». Рассчитайте выплаты по процентам в зависимости от размера кредита, выданного на заданный срок.