Графическое представление годовой прибыли и стоимости замены машины

Размещено на http://www.allbest.ru/

Задание

В соответствии с индивидуальным вариантом исходных данных разработать компоновку листа ЭТ Excel для вычисления и графического представления годовой прибыли и стоимости замены машины в зависимости от возраста и поколения машины. Провести расчеты и сформировать таблицу изменение технико-экономических характеристик машины в зависимости от расчетных показателей морального и физического старения b,--g,--d,--j.

Разработать компоновку листа ЭТ Excel для поиска оптимальной политики замены машины с использованием функциональных уравнений Р.Беллмана. Провести расчеты суммарной прибыли и оптимальных сроков замены машины при номинальных значениях заданных показателей и в различных сочетаниях - при отклонениях на 50% от номиналов. Выявить наименее и наиболее благоприятные условия функционирования машины в плановом периоде. Построить графики, иллюстрирующие зависимость суммарной прибыли и оптимальных сроков замены машины от параметров R/P, a,--b--и--g. Рассчитать потери суммарной прибыли в зависимости от возраста исходной машины.

Разработать компоновку листа ЭТ Excel для поиска суммарной прибыли и объема инвестиций при заданных сроках замены машины.

Подготовить макрос для выполнения "обратного хода" метода динамического программирования, формирования диаграмм распределения дисконтированной величины суммарной прибыли по годам планового периода и компьютерного моделирования суммарной прибыли и оптимальных сроков замены машины при случайных изменениях коэффициента дисконтирования a и заданных параметров физического и морального старения?b,--g,--d,--j.

Показать распределения дисконтированной величины суммарной прибыли по годам планового периода для номинальных, наименее и наиболее благоприятных условий.

Провести компьютерное моделирование, варьируя случайные значения параметров a,--b,--g,--d,--j в диапазоне от 50% до 150% их заданных номинальных значений. На каждом шаге моделирования фиксировать потери суммарной прибыли при отказе от замены машины в течение всего планового периода. Обработать и прокомментировать результаты моделирования.

Провести расчеты для решения двухкритериальной задачи о замене машины с использованием суммарной прибыли в качестве одного критерия, величина которого максимизируется, и объема инвестиций в качестве другого критерия, который минимизируется при выборе оптимальной политики замены машины. Сформировать множество недоминируемых по Парето альтернативных решений и выбрать итоговое решение, применив методы уступок и аддитивной свертки.

компьютерный моделирование excel дисконтированный

Таблица исходных данных

Наименование показателя	Обозначение показателя	Номинальное значение	Диапазон изменения
Коэффициент дисконтирования		0,05	0,025 0,075
Коэффициент цепного роста производительности новых поколений машин		0,04	0,02 0,06
Коэффициент снижения производительности с увеличением возраста машины		0,03	0,015 0,045
Коэффициент цепного роста стоимости новых поколений машин		0,035	0,018 0,053
Коэффициент цепного роста затрат на замену машины в зависимости от ее возраста		0,015	0,008 0,023
Соотношение стоимости и производительности новой машины 1-го поколения	R/P	1,5
Продолжительность планового периода	N	31

Компоновка листа ЭТ Excel для расчета значений годовой прибыли и стоимости замены машины

Графики зависимости годовой прибыли от возраста и поколения машины:

Номинальные значения расчетных показателей

Нижние граничные значения расчетных показателей

Верхние граничные значения расчетных показателей

Графики зависимости стоимости замены от возраста и поколения машины:

Номинальные значения расчетных показателей

Нижние граничные значения расчетных показателей

Верхние граничные значения расчетных показателей

Изменение технико-экономических характеристик машины в зависимости от расчетных показателей морального и физического старения

Показатели темпов морального и физического старения машины	Номинальные значения расчетных показателей	Нижние граничные значения	Верхние граничные значения
Рост производительности новых поколений машин в плановом периоде, S31(0) / S1(1)	3,47	2,56	6,36
Падение производительности действующей машины без ее замены в плановом периоде, S1(1) / S31(31)	2,43	1,95	3,75
Увеличение стоимости машин новых поколений в течение планового периода, C31(1) / C1(1)	2,81	2,18	4,64
Увеличение затрат на обновление действующей машины в течение планового периода, C31(31) / C1(1)	1,56	1,40	1,95

При принятых исходных данных производительность машин новых поколений растет опережающими темпами по сравнению с затратами на обновление машин. При номинальных значениях расчетных показателей производительность машины за 31 год увеличивается в 3,47 раза, тогда как затраты на обновление машины возрастают лишь в 2,81 раза. Если за 31 год эксплуатации машина ни разу не обновляется, то ее производительность за этот период снижается в 2,43 раза.

Поиск оптимальной политики замены машины

Суммарная прибыль и инвестиции при оптимальной политике замены машины

Формулы в ячейках B6, C6, B41, C41:

=МАКС((1+$AM$18)^(B$5-$A6-1)/(1+$AN$18)^$A6;(1+$AM$18)^(B$5-1)-$A$2*((1+$AO$18)^(B$5-$A6-1)*(1+$AP$18)^$A6))

=МАКС((1+$AM$18)^(C$5-$A6-1)/(1+$AN$18)^$A6+B7/(1+$AL$18);(1+$AM$18)^(C$5-1)-$A$2*((1+$AO$18)^(C$5-$A6-1)*(1+$AP$18)^$A6)+B$6/(1+$AL$18))

=ЕСЛИ((1+$AM$18)^(B$5-$A6-1)/(1+$AN$18)^$A6>=(1+$AM$18)^(B$5-1)-$A$2*(1+$AO$18)^(B$5-$A6-1)*(1+$AP$18)^$A6;0;$A$2*(1+$AO$18)^(B$5-$A6-1)*(1+$AP$18)^$A6)

=ЕСЛИ((1+$AM$18)^(C$5-$A6-1)/(1+$AN$18)^$A6+B7/(1+$AL$18)>=(1+$AM$18)^(C$5-1)-$A$2*(1+$AO$18)^(C$5-$A6-1)*(1+$AP$18)^$A6+B$6/(1+$AL$18);0;$A$2*(1+$AO$18)^(C$5-$A6-1)*(1+$AP$18)^$A6)

Результаты расчетов при различных сочетаниях исходных показателей

Наиболее благоприятные условия характеризуются максимальным значением ?, минимальными значениями ?, ?, ?, ?. Наименее благоприятные условия характеризуются максимальными значениями ?, ?, ?, минимальными значениями ?, ?,

При номинальных условиях суммарная прибыль составляет 18,205 расч.д.ед., при неблагоприятных условиях снижается до 10,543 расч.д.ед., при наиболее благоприятных условиях суммарная прибыль возрастает до 37,012 расч.д.ед.

При номинальных и благоприятных условиях требуется три и четыре замены машины: на 8-м - 16-м, - 24-м и на 7-м - 14-м - 21-м, - 27-м годах планового периода. При наименее благоприятных условиях замену машины необходимо произвести три раза - в 8-м, - 16-м, - 24-м годах планового периода.

Графики зависимости суммарной прибыли и оптимальных сроков замены машины от расчетных показателей физического и морального старения, срока окупаемости и коэффициента дисконтирования

Ориентировочно можно считать, что в зависимости от технико-экономических характеристик машины срок окупаемости в 1,5 ч 2 раза превышает расчетную величину показателя R/P.

Таким образом, при сроке окупаемости машины 2 ч 3 года замену машины следует проводить четыре раза: на 6-м, 12-м, 19-м и 26-м годах рассматриваемого планового периода; при сроке окупаемости 4 ч 5 года замену машины следует проводить три раза: на 9-м, 17-м и 25-м годах; при сроке окупаемости 6 ч 8 лет машину следует заменить два раза - на 12-м и 23-м годах; при большей продолжительности срока окупаемости не следует производить замену в течение всего планового периода.

Оптимальные сроки замены машины не зависят от величины коэффициента ?, характеризующего темпы снижения годовой производительности действующей машины (темпы физического старения машины). При увеличении коэффициента ?, характеризующего темпы роста производительности машин новых поколений, оптимальные сроки замены машины сокращаются с 16 лет до 10 - 11 лет.



При возрасте исходной машины 10 лет потери суммарной прибыли за весь плановый период составят 6%.

Компоновка листа Excel для поиска суммарной прибыли и объема инвестиций при заданных сроках замены машины

Формулы в ячейках AS10, AS11, AS12, BY10, BZ10:

=ЕСЛИ(AS9<$AT2;(1+$AM$18)^($AT1-1)/(1+$AN$18)^(AS9-$AT1);ЕСЛИ(AS9<$AT3;(1+$AM$18)^($AT2-1)/(1+$AN$18)^(AS9-$AT2);ЕСЛИ(AS9<$AT4;(1+$AM$18)^($AT3-1)/(1+$AN$18)^(AS9-$AT3);ЕСЛИ(AS9<$AT5;(1+$AM$18)^($AT4-1)/(1+$AN$18)^(AS9-$AT4);ЕСЛИ(AS9<$AT6;(1+$AM$18)^($AT5-1)/(1+$AN$18)^(AS9-$AT5);ЕСЛИ(AS9<$AT7;(1+$AM$18)^($AT6-1)/(1+$AN$18)^(AS9-$AT6);ЕСЛИ(AS9<$AT8;(1+$AM$18)^($AT7-1)/(1+$AN$18)^(AS9-$AT7);0)))))))

=ЕСЛИ(AS9=$AT2;$A$2*(1+$AO$18)^($AT1-1)*(1+$AP$18)^(AS9-$AT1);ЕСЛИ(AS9=$AT3;$A$2*(1+$AO$18)^($AT2-1)*(1+$AP$18)^(AS9-$AT2);ЕСЛИ(AS9=$AT4;$A$2*(1+$AO$18)^($AT3-1)*(1+$AP$18)^(AS9-$AT3);ЕСЛИ(AS9=$AT5;$A$2*(1+$AO$18)^($AT4-1)*(1+$AP$18)^(AS9-$AT4);ЕСЛИ(AS9=$AT6;$A$2*(1+$AO$18)^($AT51)*(1+$AP$18)^(AS9-$AT5);ЕСЛИ(AS9=$AT7;$A$2*(1+$AO$18)^($AT61)*(1+$AP$18)^(AS9-$AT6);0))))))

=1/(1+$AL$18)^(AS9-1)

=СУММПРОИЗВ(AS10:BW10;AS12:BW12)-СУММПРОИЗВ(AS12:BW12;AS11:BW11)

=СУММПРОИЗВ(AS12:BW12;AS11:BW11)

Макрос для выполнения обратного хода метода динамического программирования и компьютерного моделирования

Sub Замена31()

'

' Замена31 Макрос

' Макрос записан 20-08-2000 (Новикова Н)

'

Dim i, j

For j = 1 To 1

Calculate

' Копирование матрицы суммарной прибыли и матрицы инвестиций

' для выполнения обратного хода

Range("A1:AF74").Select

Application.CutCopyMode = False

Selection.Copy

Range("A75").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=

False, Transpose:=False

' Горизонтальная прокрутка

Horizontal_31

' Обновление двух нижних строк

Range("B152:AG153").Select

Selection.Copy

Range("C152").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:

False, Transpose:=False

For i = 1 To 30

If Range("B153").Value > 0 Then

While Range("A80").Value <> 1

Vertical_31_1

Vertical_31_2

Wend

Else

Vertical_31_1

Vertical_31_2

End If

Horizontal_31

' Обновление двух нижних строк

Range("B152:AG153").Select

Selection.Copy

Range("C152").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:

False, Transpose:=False

Next i

' Восстановление матриц суммарной прибыли и инвестиций

While Range("A80").Value <> 1

Vertical_31_1

Vertical_31_2

Wend

Range("C149:AG153").Select

Selection.Copy

Range("C155").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Selection.Sort Key1:=Range("C155"), Order1:=xlAscending, Header:=xlGuess _

, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

DataOption1:=xlSortNormal

Range("C160:AG160").Select

Selection.Copy

Range("C162").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Selection.Sort Key1:=Range("C162"), Order1:=xlAscending, Header:=xlGuess _

, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

DataOption1:=xlSortNormal

Range("Q193").Select

Selection.Copy

Range("P193").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Rows("195:195").Select

Application.CutCopyMode = False

Selection.Insert Shift:=xlDown

Range("A193:P193").Select

Selection.Copy

Range("A195").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Next j

End Sub

Sub Horizontal_31()

' Горизонтальная прокрутка матрицы суммарной прибыли

' и матрицы инвестиций

Range("B79:AF145").Select

Selection.Copy

Range("C79").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=

False, Transpose:=False

Range("AG79:AG145").Select

Selection.Copy

Range("B79").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=

False, Transpose:=False

Range("AG79:AG145").Select

Application.CutCopyMode = False

Selection.ClearContents

End Sub

Sub Vertical_31_1()

' Вертикальная прокрутка матрицы суммарной прибыли

Range("A80:AF80").Select

Selection.Copy

Range("A111").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=

False, Transpose:=False

Range("A81:AF111").Select

Application.CutCopyMode = False

Selection.Copy

Range("A80").Select

ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _

IconFileName:=False

Range("A111:AF111").Select

Selection.ClearContents

End Sub

Sub Vertical_31_2()

' Вертикальная прокрутка матрицы инвестиций

Range("A115:AF115").Select

Selection.Copy

Range("A146").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=

False, Transpose:=False

Range("A116:AF146").Select

Application.CutCopyMode = False

Selection.Copy

Range("A115").Select

ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _

IconFileName:=False

Range("A146:AF146").Select

Selection.ClearContents

End Sub

Результаты компьютерного моделирования



Регрессионный анализ результатов моделирования

Полученная регрессионная модель для суммарной прибыли

f = 24,0755 - 251,2? + 251,6? - 50,39? - 19,75д + ?

(R/P = 1,5)

характеризуется высоким значением коэффициента детерминации и позволяет с высокой точностью предсказывать величину суммарной прибыли в зависимости от расчетных показателей физического и морального старения машины. В частности, расчетное значение суммарной прибыли для номинальных условий равно 19,283 расч.д.ед. (на 5,9% больше номинальной величины 18,205 расч.д.ед.).

Распределение оптимальных сроков замены машины по результатам моделирования

Распределение потерь при отказе от замены машины по данным моделирования

Потери прибыли при отказе от замены машины изменяются в диапазоне от 25% до 55%, средняя величина потерь составляет 36,9%.

Анализ распределения суммарной прибыли и затрат по годам планового периода для номинальных условий. При оптимальных сроках замены машины в 8-м, 16-м и 24-м годах распределения суммарной прибыли и затрат по годам планового периода рассчитывается следующим образом:

1. Годовая прибыль для машины 0-го поколения возраста от 1 до 7 лет 0,93 0,91 0,88 0,85 0,83 0,81 0,78 Дисконтированные значения 0,93 0,86 0,80 0,74 0,68 0,63 0,58. Всего за 1 - 7 г.г. 5,22.

2. Затраты на замену машины в 8-м году -1,63, дисконтированная величина -1,16. Годовая прибыль для новой машины 8 поколения 1,32, дисконтированная величина 0,94. Всего в 8-м году -0,23, за 8 лет 4,99.

3. Годовая прибыль для машины 8 поколения возраста от 1 до 7 лет 1,28 1,24 1,21 1,17 1,14 1,11 1,07. Дисконтированные значения 0,86 0,80 0,74 0,68 0,63 0,58, 0,54. Всего за 9 - 15 г.г. 4,84, за 15 лет 9,83.

4. Затраты на замену машины в 16-м году -2,15, дисконтированная величина -1,03. Годовая прибыль для новой машины 16 поколения 1,80, дисконтированная величина 0,87. Всего в 16-м году -0,16, за 15 лет 9,67.

5. Годовая прибыль для машины 16 поколения возраста от 1 до 7 лет 1,75 1,70 1,65 1,60 1,55 1,51 1,46 Дисконтированные значения 0,80 0,74 0,68 0,63 0,59 0,54 0,50. Всего за 17 - 23 г.г. 4,49, за 23 года 14,16.

6. Затраты на замену машины в 24-м году -2,83, дисконтированная величина -0,92. Годовая прибыль для новой машины 24 поколения 2,46, дисконтированная величина 0,80. Всего в 24-м году -0,12, за 23 года 14,04.

7. Годовая прибыль для машины 24 поколения возраста от 1 до 7 лет 2,39 2,32 2,26 2,19 2,13 2,06 2,00

Дисконтированные значения 0,74 0,69 0,63 0,59 0,54 0,50 0,46

Всего за 25 - 31 г.г. 4,16, за 24 года 18,20. Аналогично рассчитывается распределения суммарной прибыли и затрат по годам планового периода для неоптимальных вариантов - без замены, одна замена в 16-м году, две замены в 11-м и 21-м годах:



Диаграммы распределения суммарной прибыли и инвестиций по годам планового периода

В нижней части диаграмм приведены дисконтированные величины суммарной прибыли и инвестиций за весь плановый период. Для номинальных условий дисконтированная величина суммарной прибыли равна 18,2 расч. д.ед., объем инвестиций равен 3,12 расч. д.ед. Для неблагоприятных условий эти показатели соответственно равны - 10,5 расч. д.ед., 2,19 расч. д.ед.; для благоприятных условий они равны - 37,01 расч. д.ед., 7,06 расч. д.ед. Решение двухкритериальной задачи о замене машины

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

Решение двухкритериальной задачи о замене оборудования проводится в три этапа. На первом этапе формируется множество допустимых политик, перспективных с точки зрения их возможного использования в качестве оптимального решения двухкритериальной задачи. Как правило, практический интерес представляют допустимые политики с числом замен машины, не превышающем трех. Для формирования множества допустимых политик необходимо решать задачу о замене оборудования в обратной постановке: по заданным априори срокам замены машины находить соответствующие им значения суммарной прибыли и объема необходимых инвестиций. С этой целью используется компоновка листа Excel, представленная на с. 11. На втором этапе выделяется подмножество недоминируемых политик, которое является общим решением двухкритериальной задачи. На заключительном, третьем этапе методом уступок или аддитивной свертки выбирается единственная альтернатива -- окончательное итоговое решение рассматриваемой задачи.

Сформированное множество допустимых политик замены машины

В сформированное множество вошли допустимые политики, существенно различающиеся по объему инвестиций (от 0 до 4,15 расч. д.ед.). По суммарной прибыли отличие менее существенно: для большинства допустимых политик суммарная прибыль отклоняется от теоретически оптимальной не более чем на 18%.

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

Оптимальное по Парето решение двухкритериальной задачи

Выбор итогового решения двухкритериальной задачи

Используя метод уступок и положив допустимый уровень потерь суммарной прибыли 3,2% по сравнению с теоретически максимальным, найдем итоговое решение рассматриваемой задачи, которому соответствует две замены машины в 12 и 22 году планового периода. Тот же результат может быть получен методом аддитивной свертки, если весовой коэффициент для первого критерия (суммарной прибыли) находится в диапазоне от 0,95 до 0,61; соответственно весовой коэффициент для второго критерия (объема инвестиций) изменяется от 0,56 до 0,38. При итоговым решением является теоретически оптимальная политика замены машины для однокритериальной задачи: замена машины в 12-м и 22-м годах планового периода. При оптимальной политике соответствует отказ от замены машины на протяжении всего планового периода.

Суммарная прибыль при одной замене машины в 12 и 22 году планового периода составит 17,61 расч. д.ед., объем инвестиций -- 1,93 расч. д.ед. По сравнению с теоретически оптимальным решением однокритериальной задачи будет достигнуто уменьшение объема инвестиций более чем в два раза при сокращении суммарной прибыли всего на 0,8%.

Размещено на Allbest.ru