2.2.1. Программа @Risk

В этом разделе мы рассмотрим моделирование в Excel при помощи @Risk. Отметим, прежде всего, что все функции @Risk начинаются с RISK, например: RISKDISCRETE. Функция RISKDISCRETE позволяет генерировать случайные величины, принимающие дискретные значения. Эта функция вызывается посредством формулы

=RISKDISCRETE(Values,Probs), (2.16)

где Values – название массива ячеек, содержащего допустимые значения переменой, а Probs – название массива, содержащего соответствующие вероятности. Сравните, насколько это проще использования функции VLOOKUP в Excel.

Случайные переменные с нормальной функцией распределения генерируются в @Risk при помощи функции RISKNORMAL, которая вызывается командой:

=RISKNORMAL(Mean,Stdev), (2.17)

где Mean – среднее значение, а Stdev – стандартное отклонение функции нормального распределения. Треугольное распределение задаётся командой:

=RISKTRIANG(Min,ML,Max), (2.18)

где Min, ML и Max – соответственно минимальное, наиболее вероятное (Most Likely), и максимальное значения случайной переменной.

Чтобы таблица Excel могла понять приведённые выше команды, программа @Risk должна быть загружена в память компьютера. Для этого необходимо сначала запустить программу @Risk (предполагается, что она уже установлена на компьютере) из стартового меню Windows Пуск/Программы/(Palisade Decisions Tools/@Risk for Excel. При этом Excel запустится автоматически, и её лист будет выглядеть примерно так, как показано на рис. 2.14. Из рисунка вы видите, что в окне Excel появились две новые панели инструментов: 1) панель средств поддержки управленческих решений (Decision Tools) (рис. 2.15); 2) панель инструментов @Risk (рис. 2.16).

Рис. 2.14. Вид листа Excel с установленными программами-надстройками @Risk, TopRank, PrecisionTree, BestFit и др.

К

Рис. 2.15. Панель средств поддержки управленческих решений (Decision Tools)

Рис. 2.16. Панель инструментов программы @Risk

Кнопка (Start PrecisionTree) на панели средств поддержки решений запускает программу построения дерева решений, а кнопка (Start BestFit) запускает программу BestFit, позволяющую подыскивать наилучшую аппроксимацию функции распределения случайной величины.

На панели команд @Risk кнопки (Open @Risk file) и (Save @Risk file) запускают, соответственно, команды создания и сохранения файлов @Risk. Кнопка (Define Distributions) запускает команду ввода вида функции распределения в ячейку Excel, а кнопка (Fit Distributions to Data) запускает команду подбора функции распределения, наилучшим образом аппроксимирующую заданную – экспериментальную. Функции, запускаемые кнопками (Add Output) и (Display List of Outputs and Inputs), позволяют, соответственно, определить, какие данные необходимо представить в отчёте и показать, какие параметры являются выходными, а какие – входными. Кнопка (Simulation Settings) запускает функцию установки параметров моделирования, а кнопка (Start Simulation) запускает сам процесс моделирования. Кнопка (Report Settings) позволяет установить параметры отчёта. Нажатие кнопки (Show @Risk-Model Window) приводит к появлению окна модели @Risk, а нажатие кнопки (Show @Risk-Report Window) – к появлению окна отчёта.

Рассмотрим теперь, как можно решить задачу об оптимальном заказе партии обуви при помощи @Risk. В прошлый раз, решая эту задачу, мы считали, что спрос является случайной величиной с треугольной функцией распределения. Теперь, чтобы продемонстрировать возможности @Risk, будем считать, что известен спрос за несколько предшествующих сезонов на аналогичные модели обуви, интересующие нас, как это представлено в табл. 2.3.

Таблица 2.3

Спрос на обувь

Построим на листе Excel модель, как показано на рис. 2.17.

Рис. 2.17. Модель задачи об оптимальном заказе обуви. Решение с помощью @Risk

Чтобы подобрать функцию распределения, наилучшим образом аппроксимирующую спрос за прошлые годы, выполним команду Show @Risk-Model Window. При этом появится диалоговое окно модели программы @Risk (рис. 2.18).

Рис. 2.18. Исходное окно модели @Risk

В этом окне надо выполнить команду Fit Tab меню Insert (кнопка ). Это приведёт к появлению таблицы в левой части окна. В эту таблицу надо перенести данные из табл. 2.3. Для той же цели можно воспользоваться стандартными функциями копирования/вставки данных приложений Microsoft. Чтобы свернуть окно модели @Risk и вернуться в Excel, можно нажать кнопку (Show Excel Window) на панели инструментов окна модели @Risk. После того как таблица будет заполнена, можно подобрать функцию распределения. Для этого надо выполнить команду Fitting/Specify Distributions to Fit (кнопка ). Это приведёт к появлению одноимённого диалогового окна, показанного на рис. 2.19. Здесь можно выбрать функции распределения – кандидаты для аппроксимации, запросить вывод параметров наилучшей аппроксимации и указать границы функции распределения. Завершается этот диалог нажатием клавиши ОК.

Рис. 2.19. Пример заполнения диалогового окна Specify Distributions to Fit

Для проведения собственно аппроксимации можно выполнить команду Fitting/Run Fit или нажать соответствующую ей кнопку панели инструментов окна @Risk. Программа выведет указанные функции распределения, проранжировав их в порядке ухудшения точности аппроксимации, и покажет гистограмму и график выбранной функции распределения. В правой части окна @Risk и на графике приводятся параметры аппроксимации (рис. 2.20).

Рис. 2.20. Окно результатов подбора наилучшей функции распределения

Вернёмся теперь обратно в Excel. В ячейки В6 и В8 введём функции распределения из набора @Risk, соответственно:

=RiskTriang(25000;30000;40000), (2.19)

=RiskUniform(1800;2100). (2.20)

З

Рис. 2.21. Выбор функций @Risk

После этого надо обозначить выходные параметры. В нашем случае это Общий доход. Нео

Рис. 2.22. Регистрация выходных параметров

=RiskOutput() + B6B8+B7B9-B3B4. (2.21)

Э

Рис. 2.23. Окно входных и выходных параметров модели

Для представления общей информации о результатах моделирования занесём указанные ниже функции @Risk в ячейки В13:В16:

=RiskMin(B10) (2.22)

=RiskMax(B10) (2.23)

=RiskMean(B10) (2.24)

=RiskStdDev(B10) (2.25)

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

Теперь надо сделать установки параметров моделирования. Для этого нажмите кнопку Simulation Settings на панели инструментов @Risk. Появится диалоговое окно, изображённое на рис. 2.24. Прежде всего, нам надо установить количество репликаций модели. В @Risk повторения расчетов с разными значениями случайных величин называются не репликациями, а итерациями (iterations). Мы будем использовать оба термина в одном смысле. Итак, установим на вкладке Iterations диалогового окна Simulation Settings число итераций, например, 1000 и остальные параметры, как указано на рисунках 2.24 и 2.25.

П

Рис. 2.26. Пример заполнения диалогового окна @Risk Reports

Теперь всё готово для собственно моделирования. Для его начала необходимо просто нажать на кнопку Start Simulations панели инструментов @Risk. Программа начнёт производить вычисления, за ходом которых можно следить по индикатору в нижней левой части экрана. Если на вкладке @Risk Reports была помечена ячейка Show Interactive @Risk Results Window, то после окончания вычислений на панели инструментов вы найдёте иконку свёрнутого окна результатов вычислений @Risk Results. Это окно содержит общие результаты моделирования. После разворачивания оно примет такой вид, как показано на рис. 2.27.

Рис. 2.27. Окно отчёта @Risk. Общая статистика (Summary Statistics)

Для доступа к детальному статистическому отчёту (если он был заказан в диалоговом окне @Risk Reports) надо выполнить команду Insert/Detailed Statistics, или нажать соответствующую кнопку ( ) на панели инструментов @Risk. В результате появится окно, содержащее детальный статистический отчёт по результатам моделирования. На рис. 2.28 вы можете видеть этот отчёт с частью содержащихся в нём данных. Чтобы увидеть результаты каждой отдельной итерации, надо выполнить команду Insert/Data (или нажать кнопку на панели инструментов @Risk). Часть этих данных вы видите в окне Data на рис. 2.29.

Рис. 2.28. Окно отчёта @Risk. Детальный отчёт (Detailed Statistics)

Рис. 2.29. Окно отчёта @Risk. Результаты отдельных итераций (Data)

Чтобы получить графическое представление результатов, надо щелчком мыши выделить выходные параметры (в нашем примере общий доход) в левой части окна @Risk Results (рис. 2.27–2.29) и выполнить команду Insert/ Graph/Histogram или нажать кнопку панели инструментов @Risk. Это приведёт к появлению окна графического представления результатов моделирования (рис. 2.30). Данное окно предоставляет возможность интерактивного анализа данных. Перетаскивая мышью движок в верхней части графика, на индикаторе внизу можно отслеживать значения вероятности попадания величины дохода в заданный интервал.

Рис. 2.30. Окно отчёта @Risk. Графическое представление результатов моделирования

Если в диалоговом окне @Risk Report была помечена одна из ячеек блока Place Excel Report in, то можно получить отчёт о результатах моделирования на листе Excel, подобный тому, который изображён на рис. 2.31. Чтобы скопировать график, представляющий результаты моделирования, из окна @Risk на лист Excel, надо нажать кнопку в правой части панели инструментов в окне @Risk Results. После этого график появится в новой книге Excel.

Рис. 2.31. Отчёт о результатах моделирования на листе Excel

На этом будем считать начальное знакомство с моделированием в Excel с использованием программы-надстройки @Risk законченным. Хотя многие моменты не были рассмотрены, но если вы заинтересовались, то сможете продолжать работать самостоятельно.