Финансово-экономические расчеты в Excel (стр. 2 из 2)
Лист с формулами
| А | В | С | D | E | F | G | H |
| 1 | Выпуск(потребление) | ||||||
| 2 | Первой отрали | Второй отрали | Третьей отрали | Конечный продукт | Валовый выпуск | ||
| 3 | 0,05 | 0,1 | 0,3 | 50 | МУМНОЖ (С16:Е18; G3:G5) | ||
| 4 | A= | 0,1 | 0,1 | 0,3 | Y= | 65 | МУМНОЖ (С16:Е18; G3:G5) |
| 5 | 0,3 | 0,25 | 0,2 | 28 | МУМНОЖ (С16:Е18; G3:G5) | ||
| 6 | |||||||
| 7 | Решение | ||||||
| 8 | E= | 1 | 0 | 0 | |||
| 9 | 0 | 1 | 0 | ||||
| 10 | 0 | 0 | 1 | ||||
| 11 | |||||||
| 12 | С8‑C3 | D8‑D3 | Е8‑Е3 | ||||
| 13 | E-A= | С9‑C4 | D9‑D4 | Е9‑Е4 | det (E-A)= | МОПРЕД (С12:Е14) | |
| 14 | С10‑C5 | D10‑D5 | Е10‑Е5 | ||||
| 15 | |||||||
| 16 | МОБР (С12:Е14) | МОБР (С12:Е14) | МОБР (С12:Е14) | ||||
| 17 | (E-A) – 1 = | МОБР (С12:Е14) | МОБР (С12:Е14) | МОБР (С12:Е14) | |||
| 18 | МОБР (С12:Е14) | МОБР (С12:Е14) | МОБР (С12:Е14) | ||||
Задание 4. вариант 10
Предприятие может выпускать продукции по двум технологическим способам производства. При этом за 1 час по первому способу производства оно выпускает 20 единиц продукции, по второму способу 25 единиц продукции. Количество произведенных факторов, расходуемых за час при различных способах производства, и располагаемые ресурсы этих факторов на каждый день работы представлены в таблице. Спланировать работу предприятия так, чтобы получить максимум продукции, если общее время работы предприятия по двум технологическим способам не менее 10 и не более 24 часов.
| Факторы | Способ производства | Ресурсы | |
| 1 | 2 | ||
| Сырье | 2 | 1 | 60 |
| Рабочая сила | 2 | 3 | 70 |
| Энергия | 2 | 1 | 50 |
Обозначим количество часов работы предприятия по первому способу х1 а по второму х2. При этом за 1 час по первому способу производства оно выпускает 20 единиц продукции, по второму способу 25 единиц продукции. Таким образом суммарное количество единиц продукции должно быть максимальным при решении уравнения z=20х1+25х2. Составим систему ограничений.
| z=20x1+25x2 – max |
| 2x1+x2<=60 – ограничение на использования сырья |
| 2x1+3x2<=70 – ограничение на использования рабочей силы |
| 2x1+x2<=50 – ограничение на использование энергии |
| 10<x1+x2<24 – ограничение времени работы предприятия |
Преобразуем последнее уравнение в более удобную для решения форму.
х1+х2<=24 х1>=0
– х1‑х2<=-10 х2>=0
Графическое решение задачи
Необходимо найти значения (х1, х2), при которых функция Z= 20x1+25x2 достигает максимума. При этом х1 и х2 должны удовлетворять системе ограничений, приведенной ранее:
Решение
1. Строим область, являющуюся пересечением всех полуплоскостей, уравнения которых приведены в системе ограничений. Например, полуплоскость 2x1+x2<=60;представляет собой совокупность точек, лежащих ниже прямой, соединяющей точки с координатами (0:60) и (30; 0). Аналогично – остальные.
2. Находим градиент функции Z.
gradz = {}
Строим вектор с началом в точке (0; 0) и концом в точке ().
3. Строим прямую, перпендикулярную вектору градиента. Так как по условию мы ищем максимум функции Z, то передвигаем прямую в направлении указанном вектором. Точка максимума – последняя точка области, которую пересечет эта прямая. В нашем случае, искомая точка лежит на пересечении прямых 2х1+3х2<=70 и х1+х2<=24;
4. Решаем систему уравнений
х1+х2= 24; х1 = 22х1+3х2=70; х2 = 22;
Т.е графическое построение дало результат (2; 22).
Максимальное значение функции Z = 20*2+25*22=590.
Решение с помощью пакета Excel
| х1 | х2 | |||||
| Значения | 2 | 22 | ||||
| нижняя граница | 0 | 0 | ||||
| верхняя граница | 24 | 24 | ||||
| z | 20 | 25 | 590 | max | ||
| Коэффициенты целевой функции | ||||||
| система ограничений | Коэффициенты | Значения | Фактические ресурсы | Неиспользованные ресурсы | ||
| Сырье | 2 | 1 | 26 | <= | 60 | 34 |
| Рабочая сила | 2 | 3 | 70 | <= | 70 | 0 |
| Энергия | 2 | 1 | 26 | <= | 50 | 24 |
| Время работы | 1 | 1 | 24 | <= | 24 | 0 |
| -1 | -1 | -24 | <= | -10 | 14 | |
Вывод: Для получения максимального количества единиц продукции предприятию необходимо работать по первому способу 2 часа, а по второму 22 часа. При этом затраты сырья составят 26 ед., рабочей силы 70 ед. и энергии 26 ед. Избыточным является ресурс «сырье» на 34 ед. и ресурс «энергия» на 24 ед., недостаточным – «рабочая сила».
Лист с формулами
| A | B | C | D | E | F | G | H |
| 1 | х1 | х2 | |||||
| 2 | Значения | 2 | 22 | ||||
| 3 | нижняя граница | 0 | 0 | ||||
| 4 | верхняя граница | 24 | 24 | ||||
| 5 | Z= 20x1+25x2 | 20 | 25 | СУММПРОИЗВ (C2:D2; C5:D5) | max | ||
| 6 | Коэффициенты целевой функции | ||||||
| 7 | система ограничений | Коэффициенты | Значения | Фактические ресурсы | Неиспользованные ресурсы | ||
| 8 | Сырье | 2 | 1 | СУММПРОИЗВ (C3:D3; C8:D8) | <= | 60 | G8‑E8 |
| 9 | Рабочая сила | 2 | 3 | СУММПРОИЗВ (C3:D3; C9:D9) | <= | 70 | G8‑E8 |
| 10 | Энергия | 2 | 1 | СУММПРОИЗВ (C3:D3; C10:D10) | <= | 50 | G9‑E9 |
| 11 | Время работы | 1 | 1 | СУММПРОИЗВ (C3:D3; C11:D11) | <= | 24 | G11‑E11 |
| 12 | -1 | -1 | СУММПРОИЗВ (C3:D3; C12:D12) | <= | -10 | G12‑E12 | |
Список используемой литературы
1. Финансово-экономические расчеты в Excel. – 2-е изд., доп. – М: Информационно-издательский дом «Филинъ», 2006. – 184 с.
2. Методический указания и контрольные задания по дисциплине «Информатика» для студентов заочного факультета экономического направления обучения. Ч. 3/ Сост. В.Н. Черномаз, Т.В. Шевцова, О.А. Медведева – : ДГМА, 2007 – 40 стр.