Анализът на чувствителността в Excel ни помага да проучим несигурността в изхода на модела с промените във входните променливи. Той прави основно стрес тестване на нашите моделирани предположения и води до прозрения с добавена стойност.
В контекста на оценката на DCF, Анализът на чувствителността в Excel е особено полезен във финансите за моделиране на цената на акциите или чувствителността на оценката към предположения като темпове на растеж или цена на капитала.
В тази статия разглеждаме следния анализ на чувствителността в Excel за DCF моделиране професионално.
- Една таблица с променливи данни
- Таблица с две променливи данни
- Търсене на цел
Най-важното - Изтеглете анализ на чувствителността в шаблона на Excel
Научете една променлива и две променливи ТАБЛИЦА ДАННИ Типове в Excel
Анализ на чувствителността в Excel
# 1 - Анализ на чувствителността на таблицата с една променлива в Excel
Нека вземем примера Финанси (модел на дивидентна отстъпка) по-долу, за да разберем този подробно.
Постоянният растеж на DDM ни дава справедливата стойност на акцията като настояща стойност на безкраен поток от дивиденти, нарастващи с постоянна скорост.
Формулата на Gordon Growth е както по-долу -
Където:
- D1 = Стойност на дивидента, която ще бъде получена през следващата година
- D0 = Стойност на дивидента, получен през тази година
- g = темп на растеж на дивидента
- Ke = процент на отстъпка
Сега, нека приемем, че искаме да разберем колко чувствителна е цената на акциите по отношение на очакваната възвръщаемост (ke). Има два начина да направите това -
- Магарешки начин :-)
- Ами ако Анализ
# 1 - Магарешки път
Анализът на чувствителността в Excel, използващ магарешкия начин, е много ясен, но труден за изпълнение, когато са включени много променливи.
Искате ли да продължите да правите тези 1000 предположения? Очевидно не!
Научете следния анализ на чувствителността в техниката на Excel, за да се спасите от неприятностите.
# 2 - Използване на една таблица с променливи данни
Най-добрият начин да направите анализ на чувствителността в Excel е да използвате таблици с данни. Таблиците с данни предоставят пряк път за изчисляване на множество версии в една операция и начин за преглед и сравнение на резултатите от всички различни вариации заедно на вашия работен лист. По-долу са стъпките, които можете да следвате, за да приложите едномерен анализ на чувствителността в Excel.
Стъпка 1 - Създайте таблицата в стандартен формат.
В първата колона имате входните предположения. В нашия пример входните данни са очакваната норма на възвръщаемост (ke). Също така, имайте предвид, че под заглавието на таблицата има празен ред (оцветен в синьо в това упражнение). Този празен ред служи за важна цел за тази едномерна таблица с данни, която ще видите в стъпка 2.
Стъпка 2 - Свържете референтния вход и изход, както е дадена снимката по-долу.
Пространството, предоставено от празния ред, сега се използва за предоставяне на входни данни (очаквано връщане на Ke) и изходна формула. Защо се прави така?
Ще използваме „Ами ако Анализ“, това е начин да инструктираме Excel, че за Input (ke), съответната формула, предоставена в дясната страна, трябва да се използва за преизчисляване на всички останали входове.
Стъпка 3 - Изберете инструмента за анализ какво да правите, за да извършите анализ на чувствителността в Excel.
Важно е да се отбележи, че това е разделено на две стъпки.
- Изберете диапазона на таблицата, започвайки от лявата страна, започвайки от 10% до долния десен ъгъл на таблицата.
- Щракнете върху Данни -> А ако анализ -> Таблици с данни
Стъпка 4 - Отваря се диалоговият прозорец Таблица с данни.
Диалоговият прозорец търси два входа - ред ред и вход колона. Тъй като разглеждаме само един вход Ke, ще предоставим вход с една колона.
Стъпка 5 - Свържете въвеждането на колона
В нашия случай всички въведени данни се предоставят в колона и следователно ще се свържем с входа на колоната. Входът на колона е свързан с очакваното връщане (Ke). Моля, обърнете внимание, че входът трябва да бъде свързан от оригиналния източник, а не от този, който е вътре в таблицата 
Стъпка 6 - Насладете се на изхода
# 2 - Анализ на чувствителността на таблицата с две променливи в Excel
Таблиците с данни са много полезни за анализ на чувствителността в Excel, особено в случая на DCF. След като се установи базов случай, анализът на DCF винаги трябва да бъде тестван при различни сценарии на чувствителност. Тестването включва изследване на нарастващия ефект на различни промени в предположенията (цена на капитала, темпове на растеж на терминала, по-нисък ръст на приходите, по-високи капиталови изисквания и др.) Върху справедливата стойност на акциите.
Нека вземем анализа на чувствителността в Excel с финансов пример за анализ на дисконтирани парични потоци на Alibaba.
С базовите предположения за разходите за капитал от 9% и постоянен темп на растеж от 3% , стигнахме до справедливата оценка от 191,45 милиарда долара.
Нека сега приемем, че не сте напълно съгласни с разходите за капиталовите предположения или предположенията за темповете на растеж, които съм взел в оценката на IPO на Alibaba. Може да искате да промените предположенията и да получите достъп до въздействието върху оценките.
Един от начините е ръчно да промените предположенията и да проверите резултатите от всяка промяна. (кодова дума - метод на магаре!)
Ние обаче сме тук, за да обсъдим много по-добър и ефективен начин за изчисляване на оценката с помощта на анализ на чувствителността в Excel, който не само спестява време, но и ни предоставя начин да визуализираме всички изходни подробности в ефективен формат.
Ако извършим анализа What-if в excel по професионален начин върху горните данни, тогава ще получим следния изход.
- Тук редовите входове се състоят от промени в капиталовите разходи или WACC (7% до 11%)
- Входящите данни в колона се състоят от промени в темповете на растеж (1% до 6%)
- Точката на пресичане е оценката на Alibaba. Например, използвайки основния ни случай от 9% WACC и 3% темпове на растеж, получаваме оценката на 191,45 милиарда долара.
С този фон, нека сега разгледаме как можем да подготвим такъв анализ на чувствителността в Excel, използвайки двуизмерни таблици с данни.
Стъпка 1 - Създайте структурата на таблицата, както е дадено по-долу
- Тъй като имаме два набора предположения - цена на капитала (WACC) и темпове на растеж (g), трябва да подготвите таблица, дадена по-долу.
- Можете да превключвате входовете за редове и колони. Вместо WACC, може да имате темпове на растеж и обратно.
Стъпка 2 - Свържете точката на пресичане с изходната клетка.
Точката на пресичане на двата входа трябва да се използва за свързване на желания изход. В този случай искаме да видим ефекта от тези две променливи (WACC и темп на растеж) върху стойността на собствения капитал. Следователно, ние сме свързали пресичащата се клетка с изхода.
Стъпка 3 - Отворете таблица с двумерни данни
- Изберете таблицата, която сте създали
- След това кликнете върху Данни -> Ами ако Анализ -> Таблици с данни
Стъпка 4 - Осигурете входовете за редове и входове за колони.
- Редът е цената на капитала или Ke.
- Входът на колоната е скоростта на растеж.
- Моля, не забравяйте да свържете тези входове от първоначалния източник на предположение, а не от никъде в таблицата.
Стъпка 5 - Насладете се на изхода.
- Повечето песимистични стойности на продукцията се намират в горния десен ъгъл, където цената на капитала е 11%, а темпът на растеж е само 1%
- Най-оптимистичната стойност за IPO на Alibaba е, когато Ke е 7%, а g е 6%
- Основният случай, който изчислихме за 9% ке и 3% темпове на растеж, е в средата.
- Този двуизмерен анализ на чувствителността в таблицата на Excel предоставя на клиентите лесен анализ на сценарии, който спестява много време.
# 3 - Търсене на цел за анализ на чувствителността в Excel
- Командата Goal Seek се използва, за да доведе една формула до определена стойност
- Това се прави чрез промяна на една от клетките, която е посочена от формулата
- Goal Seek иска справка за клетка, която съдържа формула (Set клетката). Той също така иска стойност, която е цифрата, която искате клетката да бъде равна
- И накрая, Goal Seek иска клетка да се промени, за да отведе Set клетката до необходимата стойност
Нека да разгледаме DCF на оценката на IPO на Alibaba.
Както знаем от DCF, че темповете на растеж и оценката са пряко свързани. Нарастващите темпове на растеж увеличават цената на акциите на акциите.
Нека приемем, че искаме да проверим при какъв темп на растеж цената на акциите ще достигне $ 80?
Както винаги, можем да направим това ръчно, като променим темповете на растеж, за да продължим да виждаме въздействието върху цената на акциите. Това отново ще бъде досаден процес. Може да се наложи многократно да въведем темпове на растеж, за да гарантираме, че цената на акциите отговаря на 80 долара в нашия случай.
Въпреки това можем да използваме функция като Goal Seek в Excel, за да разрешим това с лесни стъпки.
Стъпка 1 - Щракнете върху клетката, чиято стойност искате да зададете. (Клетката Set трябва да съдържа формула)
Стъпка 2 - Изберете Tools, Goal Seek от менюто и се появява следният диалогов прозорец:
- Командата Goal Seek автоматично предлага активната клетка като Set set.
- Това може да се надпише с нова препратка към клетка или да кликнете върху съответната клетка в електронната таблица.
- Сега въведете желаната стойност, която тази формула трябва да достигне.
- Щракнете в полето „Към стойност“ и въведете стойността, която искате да е равна на избраната формула.
- Накрая щракнете в полето „Чрез промяна на клетката“ и въведете или щракнете върху клетката, чиято стойност може да бъде променена, за да постигнете желания резултат.
- Щракнете върху бутона OK и електронната таблица ще промени клетката до стойност, достатъчна за формулата да достигне целта ви.
Стъпка 3 - Насладете се на изхода.
Goal Seek също ви информира, че целта е постигната.
Заключение
Анализът на чувствителността в Excel увеличава вашето разбиране за финансовото и оперативното поведение на бизнеса. Както разбрахме от трите подхода - едномерни таблици с данни, двуизмерни таблици с данни и цел за търсене, че анализът на чувствителността е изключително полезен във финансовата област, особено в контекста на оценките - DCF или DDM.
Можете обаче да получите разбиране за макро ниво на компанията и индустрията като цяло. Можете да разработите случаи, които да отразяват чувствителността на оценката към промените в лихвените проценти, рецесията, инфлацията, БВП и т.н. върху оценката. Трябва да се използва мисъл и здрав разум при разработването на разумни и полезни случаи на чувствителност.
Какво следва?
Ако сте научили нещо за анализ на чувствителността в Excel, моля, оставете коментар по-долу. Кажи ми какво мислиш. Много благодаря и се погрижете. Честито обучение!
Можете също така да разгледате тези статии по-долу, за да научите повече за оценките и корпоративните финанси -
- Формула за ценова чувствителност
- Анализ на риска - методи
- Анализ на безпроблемната работа в Excel
- Анализ на Парето в Excel
