Задача о замене оборудования

=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,2a + 251,6b – 50,39g – 19,75δ + e  (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 расч. д.ед.