Задача о замене оборудования excel
Идеи и принципы динамического программирования рассмотрим на простом примере, составленном так, чтобы не загромождались основные идеи техническими деталями. Поэтому задача будет предельно простой, однако за счет некоторых изменений может быть максимально приближена к реальной.
Известно, что проблема замены старого оборудования новым — одна из основных проблем индустрии.
Оборудование со временем изнашивается, стареет физически и морально; в процессе его эксплуатации падает производительность, растут эксплуатационные расходы. Поэтому задача о замене оборудования весьма актуальна.
Итак, рассматривается плановый период из нескольких лет, в начале которого имеется одна машина фиксированного возраста. В процессе работы машина дает ежегодно доход, требует эксплуатационных затрат и имеет остаточную стоимость, причем все перечисленные характеристики зависят от возраста машины. В любой год машину можно сохранить или продать по остаточной стоимости и купить новую по известной цене (которая может меняться со временем). Задача состоит в следующем: для каждого года в плановом периоде надо решить — сохранять имеющуюся в этот момент машину или продать ее и купить новую с тем, чтобы суммарная прибыль за весь плановый период была максимальной.
Переход системы S из одного состояния в другое за 1 год в зависимости от принятого решения можно изобразить графически (рис. 11.1)
Рис. 11.1. Графическая интерпретация задачи о замене оборудования
Введем понятие функции^/) — это величина суммарного дохода (прибыли) за последние п лет планового периода при условии, что в начале этого периода из п лет имеется машина возраста t.
Функции учитывающие вклад последующих
шагов в общий эффект, называются функциями Беялмана (по фамилии американского математика Р. Веллмана, создателя метода динамического программирования). С помощью этих функций ведется анализ задач динамического программирования. Если мы сумеем вычислить^^ /0) и найти политику замен, это и будет решением задачи.
Предположим, что к началу последнего года планового периода п = 1 у нас имеется машина возраста t. В нашем распоряжении две возможности.
Возможность первая: сохранить машину и, следовательно, получить за последний год доход
Введем следующие обозначения:
t — возраст машины; t = 0, 1, 2, . (Г = 0 соответствует использованию новой машины, t = 1 — использованию машины возраста 1 год и т.д.);
Z(t) — стоимость продукции, производимой за 1 год на машине возраста t
U(t) — эксплуатационные затраты за 1 год на машину возраста t;
S(t) — остаточная стоимость машины возраста /;
Т — текущее время в плановом периоде;
P(T) — цена новой машины в году Т(может меняться со временем из-за износа; для простоты будем считать, что Р(Т) = Р, т.е. цена машины не зависит от времени);
tQ — начальный возраст машины;
N — длина планового периода.
Мы сделали ряд упрощений, чтобы не осложнять анализа задачи. Так, например, мы считаем, что функции Z
В нашей задаче в качестве параметров системы S, характеризующих ее состояние, рассматривается единственный параметр — возраст машины. В качестве возможных управлений рассматриваются два — решение о сохранении имеющейся машины и решение о ее замене на новую. Условимся считать, что решения принимаются в моменты п = N;n = N— 1;. ; п = 1. Таким образом, плановый период разбит на шаги длиной в 1 год, и в каждый из них решается — сохранить или заменить машину.
Возможность вторая: продать имеющуюся машину и купить новую, что обеспечит в последний год доход
Для принятия решения необходимо вычислить функцию Веллмана/,(/), которая для нашего случая имеет вид:
Задача будет решена, если мы определим прибыль за весь плановый период, т.е. найдем значение функцииfN(t). Для начала попытаемся установить связь между выражениями./^ и/п.
Если связь будет найдена, то, последовательно двигаясь с конца (где п = 1) и зная fx(t), мы сможем найти f2(t), . fn(t), . fN(t) и тем самым решить задачу.
Итак, предположим, что с конца планового периода остается п + 1 год. В нашем распоряжении имеется машина возраста t, и мы ищем оптимальную политику для периода длиной п + 1 год. Этот период разбивается на две части (рис. 11.2).
Рис. 11.2. Разбивка планового периода на две части
Рассмотрим все возможные решения в первом году для машины возраста Г, и для каждого состояния системы найдем оптимальную политику в оставшейся части из п последних лет. Так мы получим политики на весь период из п + 1 последних лет, лучшая из которых и будет условно оптимальной для всего периода.
В случае сохранения машины доход за рассматриваемый период определяется выражением:
В случае замены машины аналогичной имеем:
Для принятия окончательного решения вычислим функцию Веллмана вида
Рекуррентные формулы (11.3, 11.6) позволяют реализовать концепцию динамического программирования и развернуть процесс нахождения оптимальной политики с конца, последовательно находя /,(0,/2(0, -,fn(t), . /д/О для различных значений /.
Мы ограничились машиной возраста t 10 лет невыгодна. Для простоты вычислений будем считать, что:
- 1) остаточная стоимость машины равна 0;
- 2) цена новой машины со временем не меняется и равна 10 у.д.е.;
- 3) длина планового периода /V равна 10 годам, т.е. S(t) = 0; Р = 10.
Тогда формулы (11.3 и 11.5) принимают вид
Используя полученные формулы, вычислим значения функций Веллмана fn(t) при различных ли/. Значения функций будем вписывать в таблицу 11.2.
Заполнение таблицы будем производить по строкам: сначала заполним первую строку, потом вторую и т.д. Заметим, что, согласно формуле (11.7), первая строка таблицы 11.2. совпадает с последней строкой таблицы 11.1.
Теперь перейдем к заполнению второй строки таблицы:
Здесь обе политики — сохранения и замены — обеспечивают одинаковую прибыль 9 ед.; выбираем в этом случае старую машину, к которой привыкли. Далее
Для того чтобы в таблице различать, в результате какой политики получается оптимальный доход (в данном случае/2(6)), будем величину оптимального дохода, соответствующую политике замены, помещать в ячейку с серым фоном. Итак, значение/2(6) в таблице будет записано в серой ячейке.
Можно показать, что/2(7) =/2(8) =/2(9) =/2(10) = 9 и соответствует замене машины.
После заполнения второй строки таблицы 11.2 заполняем третью, четвертую и т.д. В итоге белые ячейки в таблице соответствуют политике сохранения машины, а серые — политике ее замены (в табл. 11.2 эти области отделяет жирная черта).
Построенная таблица 11.2. содержит очень много ценной информации и позволяет решать целый ряд однотипных задач.
Пример 11.2. Допустим, имеется машина возраста 7 лет. Посмотрим, какова будет оптимальная политика действий для получения максимальной прибыли за 10 лет планового периода.
Величина максимальной прибыли (согласно табл. 11.2) определяется функцией Веллмана/10(7) = 60. Теперь найдем оптимальную политику, обеспечивающую эту прибыль.
Так как/10(7) вписано в ячейку с серым фоном, то для достижения максимальной прибыли необходимо в первом году рассматриваемого периода заменить машину на новую. По истечении одного года мы за 9 лет до конца планового периода будем иметь машину возраста один год. Теперь надо действовать оптимально в оставшийся период, располагая машиной возраста один год, т.е. найти/9(1) из девяти лет. Из таблицы 11.2. видно, что/9(1) на белом фоне, следовательно, во втором году надо сохранить машину. Рассматривая процесс по годам, замечаем:/3(2) — фон белый,/7(3) — фон белый, fb(4) — фон белый,/5(5) — фон серый. Последнее выражение./^) указывает на то, что по истечении пяти лет планового периода машину надо менять на новую. Действуя оптимально далее, найдем последовательно: f4( 1) — фон белый,/3(2) — фон белый,/2(3) — фон белый,/,(4) — фон белый. Итак, используя таблицу 11.2, мы найдем оптимальную политику, которую можно представить схемой:
Задачи замены оборудования по наличию того или иного признака можно разделить следующим образом.
1. По характеру замены оборудования на три типа:
- по замене оборудования длительного использования из-за неуклонно возрастающих с увеличением срока службы эксплуатационных затрат. В этих задачах определяется оптимальный срок службы оборудования, минимизирующий эксплуатационные затраты;
- по замене оборудования с целью предупреждения отказов (поломки). Требуется найти такое время замены оборудования, чтобы суммарные издержки были минимальными.
- по выбору оптимального плана предупредительного ремонта и профилактического обслуживания оборудования для уменьшения вероятности отказа.
2. По характеру учета затрат на оборудование на дискретные и непрерывные. Если расходы по ремонту и уходу за оборудованием производятся через некоторые интервалы времени, то задача дискретная, в противном случае – непрерывная.
3. По выходу из строя оборудования на детерминированные и случайные. Если расходы по ремонту и уходу за оборудованием являются постоянными или известными функциями от времени, то мы имеем детерминированную задачу замены оборудования.
4. По стратегии замены оборудования на плановые и смешанные. Если замена оборудования производится строго по плану с учетом соотношения затрат на ремонт и уход за оборудованием, то имеем задачу с плановой стратегией замены оборудования. Смешанные задачи замены оборудования ¾ это задачи, в которых придерживаются плановой стратегии замены оборудования, но если оборудование вышло из строя раньше запланированного времени, то оно заменяется.
5. По времени учета затрат на оборудование с приведением затрат и без приведения затрат. Если затраты на эксплуатацию оборудования осуществляются в разные сроки или они изменяются во времени, то следует привести затраты более поздних лет к расчетному, в этом случае имеем задачу замены оборудования с приведением затрат, в противном случае ¾ без приведения затрат.
1.2 Задача замены оборудования длительного
Пусть в эксплуатации находится некоторое оборудование. Покупная цена оборудования S. Известны затраты на эксплуатацию оборудования (уход за ним, ремонт т.д.), производимые в начале (1,2, …, t,… n) периодов. Предположим, что периоды равны году. Обозначим затраты, производимые в t – й период, через Ct . В результате старения балансовая цена оборудования непрерывно падает и зависит от периода списания, обозначим ее St. Требуется определить период списания оборудования, чтобы затраты на единицу времени были минимальны.
2. Непрерывные задачи, в которых известны зависимости Ct=F(t) St=F(t):
a) Ct и St линейно зависят от t
b) Ct и St квадратично зависят от t
Это связано с заменой оборудования, подверженного износу.
Средние затраты для дискретной задачи:
Средние затраты для непрерывной задачи:
a) линейная зависимость
b) квадратичная зависимость
Для всех этих случаев b0=S, так как S0=S.
Для дискретной задачи затраты при замене оборудования через t периодов будут минимальными, если значение средних затрат на эксплуатацию оборудования в очередном периоде t меньше значения средних затрат за все предыдущие и последующие периоды, т.е. выполняется соотношение:
После соответствующих преобразований получим:
Данное условие при любых соотношениях между величинами Ct и St является необходимым условием оптимальности стратегии, а так как Ct+1>Ct и St+1<St , то написанное условие является еще и достаточным условием оптимальности;
Для непрерывной задачи значение средних затрат Yt достигает оптимального значения в точке экстремума функции, выраженной одной из формул (7.4-7.5). Экстремум функции можно найти методами дифференциального исчисления (равенство нулю первой частной производной функции Yt по параметру t).
a) линейная зависимость
Оптимального периода списания нет. Если функции Ct и St линейные, то средние издержки эксплуатации оборудования будут постоянными, поэтому, если хотят произвести замену оборудования в любое время, достаточно обеспечить линейность характеристик Ct и St;
b) квадратичная зависимость
В этом случае средние издержки линейно зависят от периода эксплуатации.
1.3 Задача замены оборудования с учетом
приведения затрат к текущему моменту времени
Пусть в эксплуатации находится некоторое оборудование. Покупная цена нового оборудования известна и равна S. Допустим, что известны затраты на эксплуатацию оборудования в периоды 1, 2, …, n – С1, …, Сi, …, Cn. Для упрощения предположим, что цена St включена в затраты Ct. Требуется минимизировать затраты, приведенные к текущему моменту времени на единицу времени, т.е. определить, через какое время t следует производить замену оборудования, чтобы суммарные приведенные затраты на его эксплуатацию и на приобретение нового оборудования были минимальны.
Так, как капитальные вложения, связанные с заменой оборудования, осуществляются в разные сроки, то необходимо приводить более поздние затраты к текущим по формуле
где kt ¾ затраты в t периоде;
t ¾ период проведения ;
Eнп ¾ норматив для приведения разновременных затрат.
Обозначим через r = 1/(1+Енп); А тенге сегодня равны r t A=A/(1+Енп) t тенге в период t. Обозначим через Yt размер затрат, приведенных к текущему моменту времени, за все будущее время.
Чтобы затраты при замене оборудования были наименьшими должно выполняться условие (7.6) . Подставив (t+1) вместо t в целевую функцию Yt и выполнив ряд преобразований, получаем:
Аналогично, подставив (t-1) вместо t в целевую функцию Yt и выполнив ряд преобразований, находим:
Если теперь вместо Yt подставить его математическое выражение через искомый параметр, получим:
Из этого неравенства вытекают следующие правила замены оборудования:
1) если затраты на эксплуатацию оборудования в очередном периоде меньше средневзвешенных затрат за все предыдущие периоды, то оборудование не следует заменять;
2) если же затраты на эксплуатацию оборудования в очередном периоде превосходят средневзвешенные затраты за все предыдущие периоды, то оборудование следует заменять.
2 ПРИМЕР Выполнения лабораторной работы
2.1 постановка задачи
Для приведенных исходных данных определите оптимальный срок списания оборудования:
Покупная цена оборудования S=1500 у. е .
Затраты на эксплуатацию оборудования Ct= 30 *t
Норматив для приведения разновременных затрат Eнп=0,08
2.2 РЕШЕНИЕ ЗАДАЧИ
Решение задачи проводится по приведенному алгоритму.
Алгоритм 7.1. Решение задачи замены оборудования с учетом приведения затрат к текущему моменту времени.
1. Создание формы для ввода условий задачи.
Ø Откройте рабочий лист ЭТ Excel
Ø Сделать форму для ввода условий задачи (рисунок 7.1)
Весь текст на рисунке 7.1 и в дальнейшем является комментарием и на решение задачи не влияет.
2. Ввод исходных данных.
Ø В ячейки G2:G5 введите исходные данные S, Енп, А1, А2
Ø В ячейки А11:А25 введите значения t от 1 до 20, используя автозаполнение. На экране: Рисунок 7.2
3. Ввод зависимостей из математической модели (Рисунок 7.3)
3.1. Присвойте имена ячейкам G2:G5:
Ø Вставка, Имя, Присвоить…
На экране: диалоговое окно Присвоение имени
Ø Добавить
Повторите действия для ячеек G3, G4, G5.
3.2. Заполните ячейку В8:
Ø В ячейку В8 введите формулу: = 1/(1+E).
Ø Формат, Ячейки…
На экране диалоговое окно Формат ячейки
Ø Курсор в окно Число десятичных знаков
Ø Введите: 3
3.3. Заполните интервал В11:В25
Ø В ячейку В11 введите формулу: = A*A11+B*A11^2
Распространите формулу до ячейки B25
Ø Курсор в ячейку B11
Ø Курсор на правый нижний угол ячейки
Ø МН до В25
3.4. Заполните интервал С11:С25
Ø В ячейку С11 введите формулу: =$B$8^(A11-1)
Ø Распространите формулу до ячейки С25
3.5. Заполните интервал D11:D25
Ø В ячейку D11 введите формулу, для вычисления Y(t):
Распространите формулу до ячейки D25
3.6. Заполните интервал E11:E25
Ø В ячейку E11 введите формулу: = B11/(1-$B$8)
Ø Распространите формулу до ячейки E25
3.7. Заполните интервал F11:F24
Ø В ячейку F11 введите формулу:
Ø Распространите формулу до ячейки F24
3.8. Сделайте выводы по решению задачи.
Из таблицы, приведенной на рисунке 3 видно, что оптимальное время замены оборудования T= 12 лет. Минимальная величина целевой функции Y = 4754,309 у.е.
ЗАДАНИЕ
- Задача замены оборудования длительного пользования:
· По приведенным исходным данным определить уравнения регрессии Сt = F(t) и St = F(t) (выяснить тип зависимости: дискретная или функциональная; если функциональная, то линейная, квадратичная).
· Определить оптимальный период списания оборудования.
Для решения задачи можно воспользоваться методическими указаниями к лабораторной работе «Определение уравнения парной регрессии средствами ЭТ Excel».
- Задача замены оборудования с учетом приведения затрат к текущему моменту времени
· Для приведенных исходных данных определите оптимальный срок списания оборудования: покупная цена оборудования S=2000 у.е.; затраты на эксплуатацию оборудования Ct= 20 *t 2 +50*t; норматив для приведения разновременных затрат Eнп=0,08
Требования к отчету по лабораторной работе
Отчет должен содержать:
1. Условие задачи.
2. Результаты решения задачи.
3. Выводы по решению задачи.
Лабораторная работа N8
Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).
Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций.
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Поперечные профили набережных и береговой полосы: На городских территориях берегоукрепление проектируют с учетом технических и экономических требований, но особое значение придают эстетическим.
Тема:Применение метода динамического программирования к решению задачи о замене оборудования в среде Excel.
Задание.В начале планового периода, продолжительностью N = 6 лет имеется оборудование возраста лет. В таблице заданы значения:
t – возраст оборудования на начало года;
r(t) – стоимость продукции, производимой за год на единице оборудования возраста t лет, t=0,1,…T;
u(t) – расходы, связанные с эксплуатацией этого оборудования в течение года;
s(t) – остаточная стоимость оборудования возраста t лет;
р – покупная цена нового оборудования.
Сформировать по матрице максимальных прибылей оптимальные стратегии замены оборудования начального возраста и лет для планового периода продолжительностью соответственно N и N1 лет. Найти максимально возможную прибыль для обеих ситуаций и прибыль за каждый год для одной из ситуаций.
Варианты
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
| t | N | t0 | N1 | t0 | p |
| r(t) | |||||
| u(t) | |||||
| s(t) |
Методические указания для выполнения лабораторной работы
В таблице 1 заданы значения:
t – возраст оборудования на начало года;
r(t) – стоимость продукции, производимой за год на единице оборудования возраста t лет, t=0,1,…T;
u(t) – расходы, связанные с эксплуатацией этого оборудования в течение года;
s(t) – остаточная стоимость оборудования возраста t лет;
р – покупная цена нового оборудования;
N – продолжительность планового периода;
k = 1,2,…, N – номер года планового периода.
Сформировать по матрице максимальных прибылей оптимальные стратегии замены оборудования начального возраста и лет для планового периода продолжительностью соответственно N и N1 лет. Найти максимально возможную прибыль для обеих ситуаций и прибыль за каждый год для одной из ситуаций.
| t | |||||||
| r(t) | |||||||
| u(t) | |||||||
| s(t) | |||||||
| P=20 | N=5 | t0=2 | N1=4 | t1=5 | - | - | - |
Для решения задачи применим принцип оптимальности Р.Беллмана.
Обозначим через максимальную прибыль, полученную от эксплуатации оборудования возраста t лет за последние N-k+1 лет планового периода. Т.е. – максимальная прибыль за последний год, – максимальная прибыль за последние два года, если весь плановый период составляет пять лет.
Для нахождения оптимальной политики замен следует проанализировать, согласно принципу оптимальности, процесс от конца к началу. Для этого сделаем предположение о состоянии оборудования на начало последнего года (k=N).Пусть оборудование в начале последнего года периода имеет возраст t лет. Возникают две возможности:
1) сохранить оборудование, тогда прибыль за последний год составит r(t)–u(t),
2) продать оборудование по остаточной стоимости и купить новое, тогда прибыль за последний год будет равна s(t) – p + r(0) – u(0), где r(0) – стоимость продукции, выпущенной на новом оборудовании за первый год его ввода, u(0) – эксплуатационные расходы в этом году. Учитывая значение прибыли при различном образе действия (замена – сохранение), приходим к выводу, что решение о замене оборудования возраста t лет следует принять в случае, когда прибыль от нового оборудования на последнем периоде больше, чем от старого, т.е. при условии
s(t) – p + r(0) – u(0) > r(t) – u(t).
Следовательно, решение принимаем следующим образом (1)
Итак, для последнего года оптимальная политика и максимальная прибыль находятся из условия
Рассмотрим прибыль за два последних года. Пусть в начале предпоследнего года возрастоборудования равен t лет.Если в начале этого года принять решение о сохранении оборудования, то к концу года будет получена прибыль . К началу последнего года возраст оборудования будет равен (t + 1) лет, и, при оптимальной политике в последнем году, оно принесет прибыль, равную . Таким образом, общая прибыль за последние два года составит . Если же в начале предпоследнего года будет принято решение о замене оборудования, то прибыль за предпоследний год составит s(t) – p + r(0) – u(0). Поскольку приобретено новое оборудование, в начале последнего года его возраст будет равен одному году (t=1). Следовательно, общая прибыль за последние два года при оптимальной политике в последнем году составит .
Условно оптимальной в последние два года будет политика, доставляющая максимальную прибыль
Аналогично находим выражения для условно-оптимальной прибыли за три последних года, четыре и т.д. Общее функциональное уравнение примет вид
Введем исходные данные в таблицу Excel. (Рис.1)
Во все уравнения Беллмана (1) и (2) входят выражения и , поэтому их целесообразно вычислить, чтобы не подсчитывать для каждого значения k.
· Выделяем ячейки B7:H7.
· Печатаем знак равенства.
· Выделяем ячейки B5:H5.
· Печатаем знак минус.
· Выделяем ячейкиВ6:H6.
· Нажимаем Shift+Ctrl+Enter.
Для вычисления выражения выполняем следующие действия.
· Ставим курсор в ячейку B11.
· Печатаем знак равенства и, выделяя нужные ячейки, печатаем формулу
B9 – $B$10 + $B$7.ВячейкеB9находится значение , которое должноменяться, поэтому знак доллара ставить не нужно. Значения в ячейках B10иB7 не меняются при изменении t, поэтому нужно поставить знак абсолютной ссылки. Т.е. нажать клавишу F4после того, как нужная ячейка отобразится в формуле.
· Для заполнения ячеек C11-H11 используем операцию автозаполнения.
Сфокусируем курсор мыши на маркере автозаполнения ячейки В11. (Рис.2)
· Нажмем левую кнопку мыши на маркере автозаполнения и, удерживая её нажатой, переместим курсор в ячейку H11. После чего левую кнопку мыши следует отпустить. Результат последних операций отображен на рисунке 3.
Подготовим форму для вычисления значений (Рис.4)
По формуле (2) необходимо вычислить значение , оно запишется в следующем виде:
· Ставим курсор в ячейку B18.
· Запускаем Мастер функций (значок или на верхней панели).
· Выбираем Другие функции.
· В окне Категория выбираем Статистические.
· В окне Функция выбираем МАКС.
· Нажимаем OK.
· В окне МАКС указываем адреса сравниваемых ячеек. (Рис.5)
· Нажимаем ОК.
Чтобы использовать затем процедуру Автозаполнение, посмотрим какие значения в формуле
меняются с изменением t, а какие остаются неизменными. Для не изменяющихся значений нужно использовать абсолютные ссылки, т.е. с помощью клавиши F4расставлять знак доллара.
Итак, вычисляем значение .
· Ставим курсор в ячейку B17.
· Запускаем Мастер функций (значок или на верхней панели).
· Выбираем Другие функции.
· В окне Категория выбираем Статистические.
· В окне Функция выбираем МАКС.
· Нажимаем OK.
· В окне МАКС указываем адреса сравниваемых ячеек. (Рис.6)
· Нажимаем ОК.
· Далее, используя функцию Автозаполнение, необходимо заполнить ячейки С17: H17.
Аналогично, используя формулу (3) последовательно заполняем ячейки B16–Н14.После выполнения вышеописанных операций получим следующую таблицу. (Рис.7)
Полученное решение позволяет найти максимальную прибыль за период в пять лет при первоначальном возрасте оборудования t лет. Например, если первоначальный возраст оборудования составлял 3 года, то при соблюдении оптимальной политики сохранения и замены оборудования, максимальная прибыль за 5 лет составит денежных единиц.
Для нахождения оптимальной политики создадим форму аналогичную изображенной на Рис. 4.
· Поместим курсор в ячейку В25.
· Обращаемся к Мастер функций (значок или на верхней панели).
· В окне Категория выбираем Логические.
· В окне Функция выбираем ЕСЛИ.
· Нажимаем OK.
· Заполняем поля в окне функции ЕСЛИ, используя формулу (1). (Рис.8)
· Нажимаем ОК и при помощи функции Автозаполнение заполняем остальные ячейки строки.
При заполнении остальных строк необходимо использовать следующую формулу.
Для заполнения ячейки В24такжеиспользуем функцию ЕСЛИ (Рис.9) и Автозаполнение для ячеек С24-Н24.(Обратите внимание на абсолютную ссылку.)
Для удобства ячейки, соответствующие стратегии замены, следует залить каким-либо цветом и затем повторить заливку в предыдущей таблице.
После выполнения вышеописанных операций окно Excel будет выглядеть следующим образом. (Рис.10)
Эта таблица содержит много ценной информации и позволяет решать все семейство задач, в которое мы погружали исходную задачу.
Пусть, например, в начале первого года планового периода имеем оборудование возраста года. Разработаем «политику замен» на пятилетний период, доставляющую максимальную прибыль. Информация для этого имеется в таблице на Рис.10. Максимальная прибыль, которую можно получить за 5 лет при условии, что вначале первого года планового периода имелось оборудование возраста 2 года, находится в ячейке D14: .
Значение максимальной прибыли находится в зоне замен, это значит, что для достижения в течение 5 лет максимальной прибыли в начале первого года оборудование надо заменить. В течение первого года новое оборудование постареет на год, т. е., заменив оборудование и проработав на нем 1 год, мы в начале второго года планового периода будем иметь оборудование возраста 1 год. Из таблицы берем F2(1)= 75. Т.е. за два последних года планового периода можно получить максимальную прибыль 75 единиц, а за последний (пятый) год 89–75=14 единиц. Значение F2(1)= 75 располагается в области сохранения, т. е. в начале второго года оборудование (возраста 1 год) надо сохранить, и, проработав на нем год, будем иметь оборудование возраста 2 года. Находим значение F3(2) = 54. Оно находится в области замены. Следовательно, к началу четвертого года будем иметь оборудование возраста 1 год. Находим значение Оно находится в области сохранения. Находим значение
Полученное решение можно оформить в виде следующей таблицы.
| k-номер года планового периода длиной N лет | |||||
| Реальный возраст оборудования на начало года | |||||
| Максимально возможная прибыль за последние N-k+1 лет периода | |||||
| Решение | Зам. | Сохр. | Зам. | Сохр. | Сохр. |
| Возраст оборудования на начало года после принятия решения | |||||
| Прибыль за год |
Сравним полученное решение с ситуацией, когда сравнительно новое ( ) оборудование не будет заменяться все пять лет.
Общая прибыль за пять лет составит
денежных единицы, оборудование будет иметь возраст 7 лет, и его остаточная стоимость не будет превосходить двух единиц. Если же придерживаться выработанной политики (замена; сохранение; замена; сохранение; сохранение), то прибыль будет равна 89 денежным единицам, оборудование к концу периода будет иметь возраст 3 года и его остаточная стоимость будет равна 9 единицам.
Таблица на Рис.10 содержит информацию для решения и других задач. Из нее можно найти оптимальную стратегию замены оборудования с любым начальным состоянием от 0 до 6 лет и на любой плановый период, не превосходящий 5 лет. Например, решим задачу на плановый период в года, если вначале имелось оборудование возраста лет.
Известно, что оборудование со временем изнашивается, физически и морально стареет. В процессе эксплуатации падает производительность, и растут эксплуатационные расходы на текущий ремонт. Со временем возникает необходимость замены оборудования, так как его дальнейшая эксплуатация обходится дороже, чем замена. Отсюда задача о замене оборудования может быть сформулирована следующим образом.
Разработать оптимальную стратегию замены оборудования возраста лет в плановом периоде продолжительностью лет, если известны:
– стоимость продукции, производимой в течение года на оборудовании возраста лет ();
– ежегодные расходы, связанные с эксплуатацией оборудования возраста лет ();
– остаточная стоимость оборудования возраста лет;
– стоимость нового оборудования и расходы, связанные с установкой, наладкой и запуском.
В начале каждого года имеется две возможности: сохранить оборудование и получить прибыль или заменить его и получить прибыль . Прибыль от использования оборудования в последнем -м году планового периода запишется в следующем виде:
А прибыль от использования оборудования в период с -го по-й год –
Где – прибыль от использования оборудования в период с -го по -й год.
В случае, если оба управления («сохранение» и «замена») приводят к одной и той же прибыли, то целесообразно выбрать управление «сохранение».
Найти оптимальную стратегию замены оборудования возраста 3 года на период продолжительностью 10 лет, если для каждого года планового периода известны стоимость продукции, производимой с использованием этого оборудования, и эксплутационные расходы (таблица 24). Известны также остаточная стоимость, не зависящая от возраста оборудования и составляющая 4 ден. ед., и стоимость нового оборудования, равная 18 ден. ед., не меняющаяся в плановом периоде.
I этап. Условная оптимизация
1-й шаг. . Начнем процедуру условной оптимизации с последнего, десятого года планового периода. Для этого шага состояние системы: = 0, 1, 2, …, 9, 10. Функциональное уравнение (4.5) с учетом числовых данных примера принимает вид
Полученные результаты занесем в таблицу (первая строка таблицы 25).
2-й шаг. . Проанализируем девятый год планового периода. Для второго шага возможны состояния системы = 0, 1, 2, …, 9, 10. Функциональное уравнение (4.6) с учетом числовых данных примера принимает вид
Полученные результаты занесем в таблицу (вторая строка таблицы 25).
Продолжая вычисления описанным способом, постепенно заполняем всю таблицу (см. таблица 25).
II этап. Безусловная оптимизация
В начале исследуемого десятилетнего периода возраст оборудования составляет 3 года. Находим в таблице на пересечении строки и столбца = 3 значение максимальной прибыли - = 169. Найдем теперь оптимальную политику, обеспечивающую эту прибыль. Значение 169 записано слева от жирной черты в области «политик сохранения». Это означает, что в начале первого года принимается решение о сохранении оборудования. К началу второго года возраст оборудования 3 + 1 = 4 года. Расположенная на пересечении строки и столбца = 4 клетка находится слева от жирной черты, следовательно, и второй год нужно работать на имеющемся оборудовании. К началу третьего года возраст оборудования 4 + 1 = 5 лет. Расположенная на пересечении строки и столбца = 5 клетка находится справа от черты, в области «политик замены», следовательно, в начале третьего года следует заменить оборудование. К началу четвертого года возраст оборудования составит один год. Расположенная на пересечении строки и столбца = 1 клетка находится слева от черты, следовательно, четвертый год следует работать на имеющемся оборудовании. Продолжая рассуждать таким образом, последовательно находим = 104, = 85, = 67, = 58, = 37, = 18.
Цепь решений безусловной оптимизации можно изобразить символически следующим образом:
На этом шаге мы рассмотрим применение задачи замены оборудования .
Компания планирует определить оптимальную политику замены используемого в настоящее время трехлетнего механизма на протяжении следующих 4 лет ( n = 4), т.е. вплоть до начала пятого года. Таблица 1 содержит относящиеся к задаче данные. Компания требует обязательной замены механизма, который находится в эксплуатации 6 лет. Стоимость нового механизма равна 100 000 долл.
| Возраст t (года) | Прибыль r(t) (долл.) | Стоимость обслуживания c(t) (долл.) | Остаточная стоимость s(t) (долл.) |
|---|---|---|---|
| 0 | 20000 | 200 | - |
| 1 | 19000 | 600 | 80000 |
| 2 | 18500 | 1200 | 60000 |
| 3 | 17200 | 1500 | 50000 |
| 4 | 15500 | 1700 | 30000 |
| 5 | 14000 | 1800 | 10000 |
| 6 | 12200 | 2200 | 5000 |
Определение допустимых значений возраста механизма на каждом этапе является нетривиальной задачей. На рис. 1 представлена рассматриваемая задача замены оборудования в виде сети. В начале первого года имеется механизм, эксплуатирующийся 3 года (на графике рис. 1 по оси Y откладывается возраст механизма). Мы можем либо заменить его (З) , либо эксплуатировать (С) на протяжении следующего года. Если механизм заменили, то в начале второго года его возраст будет равен одному году, в противном случае его возраст будет 4 года. Такой же подход используется в начале каждого года, начиная со второго по четвертый.
Рис. 1. Схема возможной замены механизма
Если однолетний механизм заменяется в начале второго или третьего года, то заменивший его механизм к началу следующего года также будет однолетним. К тому же, в начале 4-го года 6-летний механизм обязательно должен быть заменен, если он еще эксплуатируется; в конце 4-го года все механизмы продаются (П) в обязательном порядке. На схеме сети также видно, что в начале второго года возможны только механизмы со сроком эксплуатации 1 или 4 года. В начале третьего года механизм может иметь возраст 1, 2 или 5 лет, а в начале четвертого — 1, 2, 3 или 6 лет.
Решение данной задачи эквивалентно поиску маршрута максимальной длины (т.е. приносящего максимальную прибыль) от начала первого года к концу четвертого в сети, показанной на рис. 1. При решении этой задачи используем табличную форму записи. (Числовые данные в таблице кратны тысячам долларов.)
| . | C | З | Оптимум | |
|---|---|---|---|---|
| t | r(t) + s(t + 1) - c(t) | r(0) + s(t) + s(1) - c(0) - I | f 4 (t) | Решение |
| 1 | 19,0 + 60 - 0,6 = 78,4 | 20 + 80 + 80 - 0,2 - 100 = 79,8 | 79,8 | З |
| 2 | 18,5 + 50 - 1,2 = 67,3 | 20 + 60 + 80 - 0,2 - 100 = 59,8 | 67,3 | С |
| 3 | 17,2 + 30 - 1,5 = 45,7 | 20 + 50 + 80 - 0,2 - 100 = 4,8 | 49,8 | З |
| 4 | Необходима замена | 20 + 5 + 80 - 0,2 - 100 = 4,8 | 4,8 | З |
| . | C | З | Оптимум | |
|---|---|---|---|---|
| t | r(t) - c(t) + f 4 (t + 1) | r(0) + s(t) - c(0) - I + f 4 (1) | f 3 (t) | Решение |
| 1 | 19,0 - 0,6 + 67,3 = 85,7 | 20 + 80 - 0,2 - 100 + 79,8 = 79,6 | 85,7 | С |
| 2 | 18,5 - 1,2 + 49,8 = 67,1 | 20 + 60 - 0,2 - 100 + 79,8 = 59,6 | 67,1 | С |
| 5 | 14,0 - 1,8 + 4,8 = 17,0 | 20 + 10 - 0,2 - 100 + 79,8 = 9,6 | 17,0 | З |
| . | C | З | Оптимум | |
|---|---|---|---|---|
| t | r(t) - c(t) + f 3 (t + 1) | r(0) + s(t) - c(0) - I + f 3 (1) | f 2 (t) | Решение |
| 1 | 19,0 - 0,6 + 67,1 = 85,5 | 20 + 80 - 0,2 - 100 + 85,7 = 85,5 | 85,5 | С или З |
| 4 | 15,5 - 1,7 + 19,6 = 33,4 | 20 + 30 - 0,2 - 100 + 85,7 = 35,5 | 35,5 | З |
| . | C | З | Оптимум | |
|---|---|---|---|---|
| t | r(t) - c(t) + f 2 (t + 1) | r(0) + s(t) - c(0) - I + f 2 (1) | f 1 (t) | Решение |
| 1 | 17,2 - 1,5 + 35,5 = 51,2 | 20 + 50 - 0,2 - 100 + 85,5 = 55,3 | 55,3 | З |
На рис. 2 показана последовательность получения оптимального решения. В начале первого года оптимальным решением при t = 1 является замена механизма. Следовательно, новый механизм к началу второго года будет находиться в эксплуатации 1 год. При t = 1 в начале второго года оптимальным решением будет либо использование, либо замена механизма. Если он заменяется, то новый к началу третьего года будет находиться в эксплуатации 1 год, иначе механизм будет иметь возраст 2 года. Описанный процесс продолжается до тех пор, пока не будет определено оптимальное решение для четвертого года.
Рис.2. Решение примера
Следовательно, начиная с первого года эксплуатации механизма, альтернативными оптимальными стратегиями относительно замены механизма будут (З, С, С, 3) и (З, 3, С, С) . Общая прибыль составит 55 300 долл.
Читайте также: