Университет "Проф. д-р Асен Златаров" - Бургас             Димитрова, Ж., Р. Димитрова. Система за обучение по статистика и статистически изчисления с Microsoft Excel  

Имитационно моделиране с Excel

Под симулация най-общо се разбира провеждане на компютърни експерименти с математически модели на сложни системи от реалния свят. Тъй като компютърният експеримент се извършва с модел на системата, а не със самата система, симулацията е мощно средство за изследване на системи, с които не е възможно или е неефективно да се проведат реални експерименти. Чрез симулацията се цели да се разкрият свойствата и закономерностите на изучаваната система, да се направят обобщения, изводи и предвиждания, да се решат практически задачи. 

През последните няколко десетилетия се наблюдава нарастващ интерес към използването на симулационни (имитационни) модели в бизнеса – предимно за подпомагане на анализа на решенията и при вземане на решения в условия на неопределеност и риск. Един от подходите за отчитане на неопределеността на бизнес средата, респективно на риска, при изготвяне на разчети в областта на инвестиционния анализ, във финансовия анализ и в редица други направления на бизнес анализите е използването на стохастични (вероятностни) модели. Входящите променливи (ключови фактори) на стохастичните модели са случайни величини, поведението на които не се поддава на контрол от страна на лицата, вземащи решения. Прилагането на симулация с този вид модели позволява да се формулират хипотези и се направят изводи за възможните резултати, основани на вероятностните разпределения на случайните величини. За провеждане на симулация със стохастични модели най-често се използва методът Monte Carlo. Това е универсален метод за симулация, който намира приложение в различни области на научните изследвания и в практиката. 

В Интернет пространството се предлагат множество комерсиални програмни пакети за създаване на модели и провеждане на симулационни изчисления по метода Monte Carlo: Crystal Ball, Risk Solver, @Risk, Risk Analyzer и др. В същото време много задачи от областта на икономическия анализ и от други предметни области, изискващи прилагането на метода Monte Carlo, може да се решат с използване на табличния процесор Microsoft Excel.

Провеждането на компютърен експеримент с метода Monte Carlo обикновено преминава през следните етапи: 

1. Съставяне на математически модел, в който се определя връзката между входните величини (фактори) и резултатите във вид на математическо уравнение или неравенство. 

2. Задаване на закон за разпределение на вероятностите за тези променливи на модела, чиито стойности не е възможно да се определят точно – така наречените "несигурни" променливи. Това е ключов момент в разработването на стохастичния модел, тъй като изборът на закон за разпределение определя "поведението" на тези променливи в процеса на симулацията, а оттук и влиянието им върху крайните резултати. Когато са налични исторически данни за изминали периоди за дадена входяща променлива, те може в определена степен да се използват при избора на нейното вероятностно разпределение. Когато такива данни отсъстват, най-често се използват субективни преценки на експерти, специалисти и мениджъри, отразяващи техните предвиждания за бъдещото поведение на случайните величини. 

3. Провеждане на компютърен експеримент, при който по случаен начин се генерират стойности на случайните променливи на модела според избраните закони за разпределение на вероятностите и изчисляване на резултатите за това множество от стойности. Тази стъпка се повтаря определен брой пъти – обикновено се провеждат няколко хиляди опита и по този начин се получава извадка от възможните сценарии на бъдещото поведение на случайните величини на модела. Методът Monte Carlo всъщност представлява извадков метод. 

4. Пресмятане на основни числови характеристики на входящите величини и получените резултати и анализ на резултатите, включително и статистически анализ (формулиране на хипотези за вероятностното разпределение на резултатите), анализ на риска и т.н. 

За илюстрация на процеса на разработване на табличен модел и на провеждане на симулация по метода Monte Carlo в среда на Excel ще разгледаме следния 

Пример 1. Мениджърите във фирма "Х" трябва да направят оценка на очакваната печалба и на риска от въвеждането на нов продукт на пазара. Поради неопределеността и динамиката на икономическата ситуация не е възможно точно да се определят бъдещите стойности на входящите променливи, участващи в пресмятането на печалбата (g). Тези променливи се разглеждат като случайни величини, за които след допълнителен анализ са определени законите на вероятностното им разпределение по следния начин:

- цена на едно изделиe (p) – триъгълно разпределение с параметри 4, 6, 7;

- променливи разходи за производството на едно изделие (v) – нормално разпределение със средна стойност 3 и стандартно отклонениe 0,7;

- постоянни разходи (FC) – равномерно разпределени в интервала [10000, 15000].

- предвижда се обемът на производство (Q) да приема стойности в три интервала, като стойностите във всеки интервал са равномерно разпределени. Вероятността за всеки интервал е определена в зависимост от броя на потенциалните конкуренти по следния начин:

Интервал № Вероятност Обем на производство (брой)
1 0,3 7000 - 14000
2 0,5 14001 - 21000
3 0,2 21000 - 28000

Печалбата g се получава по формулата g = Q(p – v) – FC.

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

Основният проблем, който възниква при организиране на симулационните изчисления с Excel, е как да се представят законите за вероятностно разпределение на различните случайни величини (дискретни и непрекъснати) в табличните модели. 

В основата на симулационното моделиране в Excel стои вградената в него функция RAND() (отнася се към категорията на математическите функции). Тя генерира стойности на непрекъсната случайна величина с равномерно разпределение в интервала [0, 1):

Стойностите, които функцията връща в клетките на работния лист, са равновероятни и са независими една от друга. Тъй като те са между 0 и 1, чрез функцията RAND() всъщност се генерират вероятностите, които се използват за симулиране на стойности на други видове случайни величини (както дискретни, така и непрекъснати).

За получаването на стойности на непрекъсната случайна величина с равномерно разпределение  между кои да е две числа a и b, следва да се използва формулата: =RAND() * (b - a) + a. Например, формулата =ROUND(RAND()*10+5; 2) ще генерира случайни числа между 5 и 15 с два знака точност след десетичната запетая. Формулата =RAND()*100 ще генерира с равна вероятност случайни числа в интервала [0; 100).

За генериране на цели случайни числа с равна вероятност в произволен  интервал може да се използва функцията: RANDBETWEEN(минимум; максимум). Например, чрез =RANDBETWEEN(10; 35) ще се генерира случайно цяло число между 10 и 35.

Симулиране на стойности на дискретни случайни величини

Симулирането на стойности на дискретна случайна величина с таблично зададен закон на разпределение ще илюстрираме със следващия 

Пример 2. Дискретна случайна величина има следния закон на разпределение:

x P(X=x)
7 0,1
9 0,3
12 0,4
15 0,2

За генериране на нейните стойности в симулационния модел функцията RAND() се използва по следния начин - като се има предвид, че функцията RAND() връща случайни числа между 0 и 1 с равна вероятност, то:

10% от числата ще бъдат в интервала [0; 0,1);

30% – в интервала [0,1; 0,4);

40% – в интервала [0,4; 0,8);

20% – в интервала [0,8; 1).

Това определя правилото за преобразуване на генерираните от функцията RAND() стойности в стойности на дискретната случайна величина – ако полученото случайно число е в интервала [0; 0,1), то дискретната случайна величина трябва да получи стойност 7; ако полученото случайно число е в интервала [0,1; 0,4), то случайната величина трябва да получи стойност 9 и т.н. В таблицата по-долу е показано как чрез функцията VLOOKUP, чийто първи аргумент е функцията RAND(), генерираните случайни числа между 0 и 1 се превръщат в стойности на дискретната случайна величина (колона D).

Формулата се въвежда само в клетката A2, след което се копира нужния брой пъти. Когато случайното число, генерирано от функцията RAND(), e в интервала [0; 0,1) функцията VLOOKUP връща резултат 7; когато е в интервала [0,1; 0,4) – резултатът е 9 и т.н. 

Вв случаите, когато случайната величина трябва да получи стойности в няколко интервала, за които са определени минималните и максималните граници, както и вероятността за всеки интервал, то за симулиране на стойностите на тази случайна величина в табличния модел следва да се използва комбинация от две случайни величини – с дискретно и равномерно разпределение. Подобно вероятностно разпределение има входящата променлива Обем на производство от Пример 1:

. Чрез дискретната случайна величина ще се определи в кой интервал трябва да се намира стойността, а чрез непрекъсната величина с равномерно разпределение ще се генерира стойност от дадения интервал. Реализацията на тази задача в Excel е представена чрез следния табличен модел:


В колона A фунцията RAND() ще генерира случайни числа между 0 и 1, които се използват от функцията VLOOKUP в колони B и C за намиране на интервала и получаване съответно на минималните и максималните граници на интервалите. Както се вижда от данните от областта L1:N4 на табличния модел, 30% от генерираните от фунцията RAND() случайни числа ще се преобразуват в граници на първия интервал, 50% – на втория интервал и 20% – на третия интервал. Получените в колони B и C граници на интервалите след това се използват от функцията RANDBETWEEN за получаване на случайни числа в тези интервали. 

Важно е да се отбележи, че при провеждане на подобни симулационни изчисления е необходимо да се изключи режимът на автоматично преизчисляване на формулите в работния лист. Това може да стане чрез File/Options/Formulas, като се избере режим Manual. За да не се извърши преизчисляване на формулите при съхраняване на работната книга е необходимо да се изключи полето Recalculate before save:

x

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


Симулиране на стойности на непрекъснати случайни величини

Симулирането на стойности на непрекъсната случайна величина с нормално разпределение се извършва с използването на две функции на Excel: NORMINV и RAND(). Функцията NORMINV(вероятност; средна стойност; стандартно отклонение) изчислява от зададените вероятност, средна стойност и стандартно отклонение стойността x на нормално разпределена случайна величина. Когато първият параметър на функцията се зададе чрез функцията RAND(), вероятността ще приема равновероятни стойности между 0 и 1 и в работния лист на Excel ще се симулират стойности на случайна величина с нормално разпределение:

=NORMINV(RAND();$A$2;$B$2)

Средната стойност и стандартното отклонение се задават с абсолютни адреси, за да може формулата да се копира необходимият брой пъти .

Случайната величина с триъгълно разпределение се характеризира с три стойности: минимална (a), най-вероятна (c) и максимална стойност (b). В Excel не се съдържат вградени функции, с които да се изчисляват стойностите на функцията на вероятностната плътност, на функцията на разпределение и на инверсната функция на триъгълно разпределена случайна величина. Със средствата на Visual Basic for Applications (VBA), обаче, е възможно да се създадат такива функции. Примерен код на потребителската функция на VBA за генериране на стойности на случайна величина с триъгълно разпределение:


Function TRIANINV(ByVal p As Single, ByVal a As Single, ByVal c As Single, ByVal b As Single)
On Error GoTo 10
Dim x As Single
If p < 0 Or p > 1 Then GoTo 10
If a >= b Or a >= c Or b <= c Then GoTo 10
x = (c - a) / (b - a)
If p <= x Then
TRIANINV = a + Sqr(p * (b - a) * (c - a))
Else
TRIANINV = b - Sqr((1 - p) * (b - a) * (b - c))
End If
Exit Function
10 TRIANINV = CVErr(xlErrNum)
End Function

Функцията TRIANINV следва се извика в работния лист по следния начин: =TRIANINV(RAND(); a; c; b), където с RAND() се генерира случайно число между 0 и 1, т.е. вероятността, а е минималната стойност; c - най-вероятната стойност; b - максималната стойност на случайната величина.

За симулиране на случайна величина с логнормално разпределение в Excel се използват функциите LOGINV и RAND(). Използването на логнормално разпределение изисква данните да са преобразувани логаритмично и ln(x) да е нормално разпределено. 

Функцията LOGINV(вероятност; средна стойност; стандартно отклонение) изчислява от зададените вероятност, средна стойност и стандартно отклонение стойността x на логнормално разпределение, където средната стойност и стандартно отклонение са параметри на ln(x). За да се симулира непрекъсната случайна величина с логнормално разпределение във функцията LOGINV като първи параметър отново се използва функцията RAND(). Например, ако формулата =LOGINV(RAND();4;1,5) се копира определен брой пъти в работния лист, то ще се получат различни стойности x на логнормално разпределение за случайни стойности на вероятността, средна стойност 4 и стандартно отклонение 1,5 на ln(x).

В Exel симулирането на стойности х за показателно разпределение може да се моделира чрез  следната  формула: x = - m ln(1-p) където m е средната стойност на разпределението, а р е вероятността P(X<x). Формулата в Excel ще се бъде: =- $B$1*LN(1-RAND()), като средната се задава с абсолютен адрес - в случая $B$1.

За решаването на Пример 1 в ред 4 на работния лист следва да се въведат посочените по-долу формули, които след това се в копират в необходимия за провеждане на  симулацията брой клетки - в случая до ред 1004.



Резултатите от формулите са следните:



Всеки път, когато е необходимо да се проведе нова симулация с модела, трябва да се натисне клавишът F9.

За обобщаване и анализ на получените от симулацията резултати Excel предоставя множество функции и инструментални средства . Чрез функцията FREQUENCY  може да се създаде честотната таблица на полученото емпирично разпределение на печалбата (колона H), коeто след това да се представи графично:


За намиране на числовите характеристики на получените от симулацията множества от стойности на входящите величини и печалбата се в табличния модел следва да се използват функциите AVERAGE, STDEV, MEDIAN, MAX, MIN, SKEW, KURT и др.

Полезни за извършване на анализи са функциите PERCENTILE и PERCENTRANK. 

Чрез функцията PERCENTILE(масив; k) се изчислява k-тият перцентил на зададения масив (област) от стойности. 

Чрез функцията PERCENTRANK(масив; x; точност) се изчислява вероятността случайната величина да бъде по-малка от конкретна стойност x, например P(X<0) или да се намира в даден интервал.