Финансово моделиране в Excel (Безплатно ръководство стъпка по стъпка + шаблон)

Съдържание

Какво е финансово моделиране в Excel?

Финансовото моделиране в Excel е процес на изграждане на финансов модел, който да представлява транзакция, операция, сливане, придобиване, финансова информация, за да се анализира как промяната в една променлива може да повлияе на крайната възвръщаемост, така че да се вземе решение за една или повече гореспоменатите финансови транзакции.

Какво е финансово моделиране в Excel?

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

В това Ръководство за безплатно финансово моделиране на Excel ще взема пример за Colgate Palmolive и ще подготвя напълно интегриран финансов модел от нулата.

Това ръководство съдържа над 6000 думи и ми отне три седмици. Запазете тази страница за бъдещи справки и не забравяйте да я споделите :-)

НАЙ-ВАЖНОТО - Изтеглете шаблона на Excel за финансово моделиране на Excel, за да следвате инструкциите

Изтеглете шаблона за финансов модел на Colgate

Научете стъпка по стъпка финансово моделиране в Excel

Финансово моделиране в обучение на Excel - Прочетете ме първо

Стъпка 1 - Изтеглете шаблона за финансов модел на Colgate. Ще използвате този шаблон за урок

Изтеглете финансовия модел на Colgate

Въведете имейл адрес Продължавайки горната стъпка, вие се съгласявате с нашите Условия за ползване и Политика за поверителност.

Стъпка 2 - Моля, обърнете внимание, че ще получите два шаблона - 1) Нерешен финансов модел на Colgate Palmolive 2) Решен финансов модел на Colgate Palmolive

Стъпка 3- Ще работите по неразрешения шаблон за финансов модел на Colgate Palmolive . Следвайте инструкциите стъпка по стъпка, за да подготвите напълно интегриран финансов модел.

Стъпка 4 - Щастливо обучение!

Съдържание

Направих лесно за навигация съдържание, за да можете да направите това Финансово моделиране

Как да изградим финансов модел?
# 1 - Финансовият модел на Colgate - Исторически
# 2 - Анализ на съотношението на Colgate Palmolive
# 3 - Проектиране на отчета за доходите
# 4- График на оборотните средства
# 5 - График за амортизация
# 6 - График на амортизация
# 7 - Друг дългосрочен график
# 8 - Попълване на отчета за доходите
# 9 - График на собствения капитал
# 10 - Споделя изключителен график
# 11 - Попълване на отчетите за паричните потоци
# 12- Препоръчва се график на дълга и лихвите
Курс за финансово моделиране
Безплатни финансови модели

Ако не сте начинаещи във финансовото моделиране, тогава разгледайте това ръководство за Какво представлява финансовото моделиране?

Как да изградя финансов модел в Excel?

Нека разгледаме как се изгражда финансов модел от нулата. Това подробно ръководство за финансово моделиране ще ви предостави ръководство стъпка по стъпка за създаване на финансов модел. Основният подход, възприет в това ръководство за финансово моделиране, е модулен. Модулната система по същество означава изграждане на основни отчети като Отчет за доходите, Счетоводен баланс и Парични потоци, използвайки различни модули / графици. Основният фокус е да се подготви всеки извлечение стъпка по стъпка и да се свържат всички поддържащи програми с основните изявления при завършване. Разбирам, че това може да не е ясно сега; обаче ще разберете, че това е много лесно, докато вървим напред. Можете да видите по-долу различни схеми / модули за финансово моделиране -

Моля, обърнете внимание на следното -

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

# 1 - Финансово моделиране в Excel - Проектирайте историята

Първата стъпка в Ръководството за финансово моделиране е да се подготвят историческите статии.

Стъпка 1А - Изтеглете 10K отчета на Colgate

„Финансовите модели са подготвени в Excel и първите стъпки започват със знанието как се справя индустрията през последните години. Разбирането на миналото може да ни предостави ценни прозрения, свързани с бъдещето на компанията. Следователно първата стъпка е да изтеглите всички финансови отчети на компанията и да ги попълните в Excel лист. За Colgate Palmolive можете да изтеглите годишните отчети на Colgate Palmolive от раздела за връзки с инвеститорите. След като кликнете върху „Годишен отчет“, ще намерите прозореца, както е показано по-долу -

Стъпка 1Б - Създайте работен лист за исторически финансови отчети

Ако изтеглите 10 000 за 2013 г., ще забележите, че са налични само две години данни за финансовите отчети. Въпреки това, за целите на финансовото моделиране в Excel, препоръчителният набор от данни е да има последните 5 години финансови отчети. Моля, изтеглете последните 3 години от годишния отчет и попълнете историческия.
Много пъти тези задачи изглеждат твърде скучни и досадни, тъй като може да отнеме много време и енергия, за да форматирате и поставите Excel в желания формат.
Не трябва обаче да се забравя, че това е работата, която трябва да направите само веднъж за всяка компания, а също така попълването на историческите данни помага на анализатор да разбере тенденциите и финансовите отчети.
Така че, моля, не пропускайте това, изтеглете данните и попълнете данните (дори ако смятате, че това е работа на магарето ;-))

Ако искате да пропуснете тази стъпка, можете директно да изтеглите историческия модел на Colgate Palmolive тук.

Отчет за доходите на Colgate с исторически населени места

Исторически данни на баланса на Colgate

# 2 - Анализ на съотношението

Втората стъпка във Финансовото моделиране в Excel е да се извърши анализ на съотношението.

Ключът към изучаването на финансовото моделиране в Excel е да можете да извършвате фундаментален анализ. Ако елементарният анализ или Ratio Analysis е нещо ново за вас, препоръчвам ви да прочетете малко в интернет. Възнамерявам да направя задълбочен анализ на съотношението в една от предстоящите ми публикации, но ето бърза снимка на съотношенията на Colgate Palmolive.

ВАЖНО - Моля, обърнете внимание, че актуализирах анализа на съотношението на Colgate в отделна публикация. Моля, разгледайте този изчерпателен анализ на съотношението.

Стъпка 2А - Вертикален анализ на Colgate

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

Резултати от вертикалния анализ

Маржът на печалбата се е увеличил с 240 базисни точки от 56,2% през 2007 г. на 58,6% през 2013 г. Това се дължи главно на намалените разходи за продажби
Оперативната печалба или EBIT също показа подобрени маржове, като се увеличи от 19,7% през 2007 г. на 22,4% през 2012 г. (увеличение от 70 базисни точки). Това се дължи на намалените продажби на общи и административни разходи. Имайте предвид обаче, че маржовете на EBIT са намалели през 2013 г. до 20,4% поради увеличените „Други разходи“. Също така проверете разликата между EBIT и EBITDA
Маржът на чистата печалба се е увеличил от 12,6% през 2007 г. на 14,5% през 2012 г. Маржът на печалбата през 2013 г. е намалял до 12,9%, главно поради увеличените „други разходи“.
Печалбите на акция непрекъснато се увеличават от FY2007 до FY2012. Въпреки това имаше лек спад в EPS от FY2013
Също така имайте предвид, че амортизацията и амортизацията се предоставят отделно в отчета за доходите. Той е включен в себестойността на продажбите.

Стъпка 2Б - Хоризонтален анализ на Colgate

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

Нека разгледаме хоризонталния анализ на Colgate.

Резултати от хоризонталния анализ

Виждаме, че нетните продажби са се увеличили с 2,0% през 2013 г.
Също така обърнете внимание на тенденцията в цената на продажбите. Виждаме, че те не са нараснали в същото съотношение има Продажби.
Тези наблюдения са изключително удобни, докато правим финансово моделиране в Excel.

Стъпка 2C - Коефициенти на ликвидност на Colgate

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

Основни акценти на коефициентите на ликвидност

Текущото съотношение на Colgate е по-значително от 1.0 за всички години. Това предполага, че текущите активи са по-значими от текущите пасиви и може би Colgate има достатъчна ликвидност.
Бързото съотношение на Colgate е в диапазона 0,6-0,7; това означава, че Colgates Cash и пазарните ценни книжа могат да плащат до 70% от текущите задължения. Това изглежда като добра ситуация за Colgate.
Цикълът на събиране на пари е намалял от 43 дни през 2009 г. на 39 дни през 2013 г. Това се дължи главно на намаляването на периода на събиране на вземания.

Също така, разгледайте тази подробна статия за Цикъла на преобразуване на пари в брой.

Стъпка 2D - Коефициенти на оперативна рентабилност на Colgate

Доходността съотношава способността на компанията да генерира печалба спрямо продажбите, активите и собствения капитал

Основни моменти - Коефициенти на рентабилност на Colgate

Както можем да видим от горната таблица, Colgate има ROE от близо 100%, което предполага отлична възвръщаемост на притежателите на собствения капитал.

Стъпка 2Е - Анализ на риска на Colgate

Чрез Анализ на риска се опитваме да преценим дали компаниите ще могат да плащат своите краткосрочни и дългосрочни задължения (дълг). Изчисляваме коефициенти на ливъридж, които се фокусират върху достатъчността на активите или генерирането им от активи. Цените, които се разглеждат, са

Съотношение дълг / собствен капитал
Съотношение на дълга
Съотношение на лихвеното покритие
Съотношението дълг / собствен капитал непрекъснато нараства до по-високо ниво от 2.23x. Това означава увеличен финансов ливъридж и рискове на пазара
Съотношението на лихвеното покритие обаче е много високо, което показва по-малък риск от неизпълнение на плащането на лихви.

# 3 - Финансово моделиране в Excel - Проектирайте отчета за доходите

Третата стъпка във Финансовото моделиране е прогнозирането на Отчета за доходите, при което ще започнем с моделиране на елементите Продажби или Приходи.

Стъпка 3А - Прогнози за приходи

За повечето компании приходите са основен двигател на икономическите резултати. Добре проектираният и логичен модел на приходи, отразяващ точно вида и размера на потоците от доходи, е изключително важен. Има толкова много начини за създаване на график на приходите, колкото са бизнесите. Някои често срещани видове включват:

Ръст на продажбите: Предположението за ръст на продажбите за всеки период определя промяната спрямо предходния период. Това е прост и често използван метод, но не дава представа за компонентите или динамиката на растежа.
Инфлационни и обемни / смесени ефекти: Вместо просто предположение за растеж се използват ценови инфлационен фактор и обемен фактор. Този полезен подход позволява моделиране на постоянни и променливи разходи в многопродуктовите компании и отчита движението на цената спрямо обема.
Единичен обем, промяна в обема, средна цена и промяна в цената: Този метод е подходящ за фирми, които имат прост микс от продукти; позволява анализ на въздействието на няколко ключови променливи.
Размер и растеж на пазара в долари: Пазарен дял и промяна в дела - Полезно за случаите, когато е налична информация за пазарната динамика и където тези предположения вероятно ще бъдат от основно значение за решението. Например, телекомуникационната индустрия.
Размер и растеж на пазарния дял : Това е по-подробно от предходния случай и е полезно, когато ценообразуването на пазара е ключова променлива. (За компания с ценова дисконтираща стратегия, например, или най-добрия от породата нишови играчи с премиум цени), напр. Пазарът на луксозни автомобили
Обемен капацитет, степен на използване на капацитета и средна цена: Тези предположения могат да бъдат важни за бизнеса, където производственият капацитет е от съществено значение за решението. (При закупуване на допълнителен капацитет например или за определяне дали разширяването ще изисква нови инвестиции.)
Наличност на продукта и ценообразуване
Приходите се определят от инвестиции в капитал, маркетинг или НИРД
Приходи въз основа на инсталирана база (продължаващи продажби на части, еднократна употреба, услуги и добавки и др.). Примерите включват класически бизнес с бръснач и бизнеси като компютри, където продажбите на услуги, софтуер и надстройки са от съществено значение. Моделирането на инсталираната основа е от ключово значение (нови допълнения към пода, износване в земята, продължаващи приходи на клиент и т.н.).
Въз основа на служители: Например приходи на фирми за професионални услуги или фирми, базирани на продажби, като брокери. Моделирането трябва да се съсредоточи върху нетната численост на персонала, приходите на служител (често се основава на платени часове). По-подробните модели ще включват старшинство и други фактори, влияещи върху ценообразуването.
Магазин, съоръжение или квадратни кадри: Фирмите за търговия на дребно често се моделират въз основа на магазините (стари магазини плюс нови магазини през всяка година) и приходите на магазин.
Въз основа на фактора заетост: Този подход е приложим за авиокомпании, хотели, кина и други бизнеси с ниски пределни разходи.

Проектиране на приходи на Colgate

Нека сега разгледаме отчета на Colgate 10K 2013. Отбелязваме, че в отчета за доходите Colgate не е предоставил сегментна информация; като допълнителна информация обаче, Colgate предостави някои подробности за сегменти на страница 87 Източник - Colgate 2013 - 10K, страница 86

Тъй като нямаме допълнителна информация за характеристиките, ще проектираме бъдещите продажби на Colgate въз основа на тези налични данни. Ще използваме подхода за ръст на продажбите по сегменти, за да извлечем прогнозите. Моля, вижте долната снимка. Изчислихме годишния темп на растеж за всеки елемент. Сега можем да приемем процент на ръст на продажбите въз основа на историческите тенденции и да проектираме приходите по всяка част. Общите нетни продажби са общата сума на сегмента за орална, лична и домашна грижа и хранене на домашни любимци.

Стъпка 3Б - Прогнози за разходи

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

Прогнози за разходите за Colgate

За проектиране на разходите, вертикалният анализ, направен по-рано, ще бъде полезен. Нека разгледаме вертикалния анализ -

Тъй като вече сме предвидили Продажби, всички останали разходи са някои маржове на тези Продажби.
Подходът е да се вземат насоките от историческите маржове на разходите и разходите и след това да се прогнозира бъдещият марж.
Например цената на продажбите е била в диапазона от 41% -42% през последните пет години. На тази основа можем да разгледаме прогнозирането на маржовете.
По същия начин продажбите, общите и административните разходи в миналото са били в диапазона от 34% -36%. На тази основа можем да приемем бъдещия марж на разходите за обществени и административни разходи. По същия начин можем да продължим с друг набор от разходи.

Използвайки горните полета, можем да намерим действителните стойности чрез обратни изчисления.

За изчисляване на провизията за данъци използваме предположението за ефективна данъчна ставка.

Също така имайте предвид, че не попълваме реда „Разходи за лихви (доходи)“, тъй като ще имаме преглед на Отчета за доходите на по-късен етап.
Разходи за лихви и доходи от лихви.
Също така не сме изчислили амортизация и амортизация, която вече е включена в себестойността на продажбите.
Това попълва Отчета за доходите (поне засега!)

# 4- Финансово моделиране - График на оборотните средства

След като приключихме отчета за доходите, четвъртата стъпка във финансовото моделиране е да разгледаме Графика за оборотни средства.

По-долу са описани стъпките, които трябва да се следват за График за оборотни средства

Стъпка 4А - Свържете нетните продажби и разходите за продажби

Стъпка 4Б - Справка за данните на баланса, свързани с оборотния капитал

Справка за минали данни от баланса
Изчислете нетния оборотен капитал
Пристигане при увеличаване / намаляване на оборотния капитал
Имайте предвид, че не сме включили краткосрочен дълг и парични средства и парични еквиваленти в оборотния капитал. Ще се занимаваме отделно с дълга и парите и паричните еквиваленти.

Стъпка 4C - Изчислете коефициентите на оборот

Изчислете исторически съотношения и проценти
Използвайте крайния или средния баланс
И двете са приемливи, докато се поддържа последователност

Стъпка 4D - Попълнете предположенията за бъдещи оборотни позиции

Определени артикули без виден драйвер обикновено се приемат в постоянни суми
Уверете се, че предположенията са разумни и съответстват на бизнеса

Стъпка 4Е - Проектирайте бъдещите баланси на оборотния капитал

Стъпка 4F - Изчислете промените в оборотния капитал

Пристигане в Парични потоци въз основа на отделни договорени покупки
Уверете се, че знаците са точни!

Стъпка 4G - Свържете прогнозирания оборотен капитал към баланса

Стъпка 4H - Свържете оборотния капитал с Отчета за паричните потоци

# 5 - Финансово моделиране в Excel - График за амортизация

След приключване на графика за оборотен капитал, следващата стъпка в това финансово моделиране е проектът Capex на Colgate и прогнозите за амортизацията и активите. Colgate 2013 - 10K, Страница 49

Амортизацията не се предоставя като отделен ред; той обаче е включен в себестойността на продажбите
В такива случаи, моля, разгледайте отчетите за паричните потоци, където ще намерите разходите за амортизация и амортизация. Също така имайте предвид, че фигурите по-долу са 1) Амортизация 2) Амортизация. И така, какво е амортизационното число?
Краен баланс за ЛПС = начален баланс + капитал - амортизация - корекция за продажби на активи (уравнение BASE)

Стъпка 5А - Свържете данните за нетните продажби в Графика за амортизация

Настройте договорените покупки
Референтни нетни продажби
Въвеждане на минали капиталови разходи
Пристигане в Capex като% от нетните продажби

Стъпка 5Б - Прогнозиране на елементите на капиталовите разходи

За да се прогнозират капиталовите разходи, има различни подходи. Една често срещана практика е да се разгледат съобщенията за пресата, прогнозите за управление, MD&A, за да се разбере виждането на компанията за бъдещите капиталови разходи
Ако компанията е предоставила насоки за бъдещи капиталови разходи, тогава можем да вземем тези цифри директно.
Ако обаче номерата на Capex не са директно достъпни, тогава можем да го изчислим грубо, като използваме Capex като% от продажбите (както е направено по-долу)
Използвайте преценката си въз основа на познанията в бранша и други разумни двигатели.

Стъпка 5C - Справка за минала информация

Ще използваме Краен баланс за ЛПС = Начален баланс + Капекс - Амортизация - Корекция за продажби на активи (уравнение BASE)
Сложно е да се съгласуват минали PP&E поради преизчисления, продажби на активи и т.н.
Ето защо се препоръчва да не се съгласуват миналите ЛПС, тъй като това може да доведе до известно объркване.

Амортизационна политика на Colgate

Отбелязваме, че Colgate не е предоставил изрично подробно разделяне на активите. Вместо това те са обединили всички активи в земя, сграда, машини и друго оборудване
Също така, полезният живот на машините и оборудването е осигурен в обхват. В този случай ще трябва да направим някои предположения, за да стигнем до средния полезен живот, останал за активите
Също така не се предоставят насоки за полезен живот за „Друго оборудване“. Ще трябва да изчислим полезния живот на друго оборудване

Colgate 2013 - 10K, Страница 55

По-долу е представена подробната информация за имотите, заводите и оборудването за 2012 и 2013 г.

Colgate 2013 - 10K, Страница 91

Стъпка 5D - Оценка на разпадането на имоти и съоръжения (ЛПС)

Първо намерете тежестите на активите на текущите ЛПС (2013)
Ще приемем, че тези тегла на активите от ЛПС за 2013 г. ще продължат напред
Използваме тези тегла на активите, за да изчислим разбиването на прогнозните капиталови разходи

Стъпка 5Е - Оценка на амортизацията на активите

Моля, имайте предвид, че ние не изчисляваме амортизацията на земята, тъй като земята не е амортизируем актив
За да оценим амортизацията от подобрения в сградите, първо използваме структурата по-долу.
Амортизацията тук е разделена на две части - 1) амортизация от сградни подобрения Актив, вече изброен в баланса, 2) амортизация от бъдещи подобрения на сградите.
За изчисляване на амортизацията от подобрения в сградите, изброени в актива, използваме простия метод на амортизация по права линия.
За изчисляване на бъдеща амортизация първо транспонираме Capex, като използваме функцията TRANSPOSE в Excel.
Изчисляваме амортизацията от вноски на активи от всяка година.
Освен това амортизацията за първата година се разделя на две, тъй като приемаме конвенцията за разполагане на активи в средата на годината.

Обща амортизация на подобрения в сградите = амортизация от подобрения в сградата Актив, вече изброен в баланса + амортизация от бъдещите подобрения на сградите Горният процес за оценка на амортизацията се използва за изчисляване на амортизацията на 1) Производствено оборудване и машини и 2) друго оборудване като показано по-долу.

Други видове оборудване

Обща амортизация на Colgate = Амортизация (Подобрения в сградите) + Амортизация (Машини и оборудване) + Амортизация (допълнително оборудване) След като установим реалните стойности на амортизацията, можем да поставим това в уравнението BASE, както е показано по-долу

С това получаваме данните за крайната нетна PP&E за всяка от годините

Стъпка 5F - Свържете нетната PP&E с баланса

# 6 - График на амортизация

Шестата стъпка в това финансово моделиране в Excel е да се прогнозира амортизацията. Тук имаме да разгледаме две широки категории - 1) Репутация и 2) Други нематериални активи.

Стъпка 6А - Прогнозиране на добра воля

Colgate 2013 - 10K, Страница 61

Репутацията се появява в баланса, когато дадено дружество придобие друго дружество. Обикновено е сложно да се проектира добрата воля за следващите години.
Репутацията обаче е обект на ежегодни тестове за обезценка, които се извършват от самата компания. Анализаторите не са в състояние да провеждат такива тестове и да изготвят оценки на обезценките.
Повечето анализатори не проектират добра воля; те просто поддържат това като постоянно, което ще направим и в нашия случай.

Стъпка 6Б - Прогнозиране на други нематериални активи

Както е отбелязано в доклада на Colgate за 10K, по-голямата част от крайния нематериален живот е свързан с придобиването на Sanex
„Добавки към нематериални активи“ също са сложни за проектиране
Докладът на Colgate за 10K ни предоставя подробности за следващите пет години амортизационни разходи.
Ще използваме тези оценки в нашия Финансов модел Colgate 2013 - 10K, Страница 61

Стъпка 6В - Крайните нето нематериални активи са свързани с „Други нематериални активи“.

Стъпка 6D - свържете амортизацията и амортизацията с отчетите за паричните потоци

Стъпка 6E - Свържете Capex & Addition към нематериалните към отчетите за паричния поток

# 7 - Друг дългосрочен график

Следващата стъпка в това финансово моделиране е изготвянето на другия дългосрочен график. Това е, когато се подготвяме за „остатъците“, които нямат конкретни двигатели за прогнозиране. В случая на Colgate другите дългосрочни позиции (остатъци) бяха отсрочени данъци върху дохода (пасиви и активи), други инвестиции и други задължения.

Стъпка 7А - Препратка към историческите данни от баланса

Също така изчислете промените в тези елементи.

Стъпка 7Б - Прогнозиране на дългосрочните активи и пасиви

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

Стъпка 7C - Препратка към други дългосрочни позиции към баланса

Стъпка 7D - Свържете дългосрочните позиции с Отчета за паричния поток

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

# 8 - Финансово моделиране в Excel - Попълване на отчета за доходите

Преди да продължим по-нататък в това финансово моделиране, основано на Excel, ще се върнем назад и ще разгледаме отчета за доходите
Попълнете историческите основни среднопретеглени акции и разредения среднопретеглен брой акции
Тези цифри са достъпни в доклада на Colgate за 10K

Стъпка 8А - Препратка към основните и разредените акции

На този етап приемете, че бъдещият брой първични и разредени акции ще остане същият като през 2013 г.

Стъпка 8Б - Изчислете основната и разредената печалба на акция.

С това сме готови да преминем към следващия ни график, т.е. График на акционерния капитал.

# 9 - Финансово моделиране - График на собствения капитал

Следващата стъпка в това финансово моделиране в Excel Training е да разгледате Графика за собствения капитал на акционерите. Основната цел на този график е да проектира елементи, свързани със собствения капитал, като акционерен капитал, дивиденти, обратно изкупуване на акции, постъпления от опции и др . 10K докладът на Colgate ни предоставя подробности за обикновените акции и съкровищни акции през последните години, както е показано по-долу . Colgate 2013 - 10K, Страница 68

Стъпка 9А - Споделяне на обратно изкупуване: Попълнете историческите числа

Исторически погледнато, Colgate изкупува обратно акции, както виждаме графика по-горе.
Попълнете обратно изкупуване на акциите на Colgate (милиони) в листата на Excel.
Свържете исторически разредения EPS от отчета за доходите
Историческият размер на обратно изкупените трябва да бъде посочен от отчетите за паричните потоци.

Също така, погледнете Ускорено обратно изкупуване.

Стъпка 9Б - Споделяне на обратно изкупуване: Изчислете PE множителя (EPS кратно)

Изчислете подразбиращата се средна цена, на която Colgate е извършвал обратно изкупуване на акции в исторически план. Това се изчислява като обратно изкупена сума / брой акции.
Изчислете PE множителя = подразбираща се цена на акция / EPS

Стъпка 9C - обратно изкупуване: Намиране на обратно изкупен дял на Colgate

Colgate не е обявил официално колко акции възнамерява да изкупи. Единствената информация, че техните 10K акции в отчета са, че са разрешили обратно изкупуване на до 50 милиона акции. Colgate 2013 - 10K, Страница 35

За да намерим броя на обратно изкупените акции, трябва да приемем сумата за обратно изкупуване на акции. Въз основа на историческата сума за обратно изкупуване, аз взех това число като 1500 милиона долара за всички следващи години.
За да намерим броя на обратно изкупените акции, ни е необходима прогнозната предполагаема цена на акциите на потенциалния обратно изкупуване.
Действителна цена на акцията = предполагаема PE мултиплекс EPS.
Може да се предположи, че бъдещите обратно изкупувания на PE се основават на исторически тенденции. Отбелязваме, че Colgate изкупува акции при среден обхват на PE от 17x - 25x
По-долу е моментната снимка от Reuters, която ни помага да проверим диапазона PE за Colgate

www.reuters.com

В нашия случай предположих, че всички бъдещи изкупувания на Colgate ще бъдат с PE кратно на 19x.
Използвайки PE от 19x, можем да намерим предполагаемата цена = EPS x 19
След като намерихме подразбиращата се цена, можем да видим броя на обратно изкупените акции = сума, използвана за обратно изкупуване / подразбираща се цена.

Стъпка 9D - Опции за запаси: Попълнете исторически данни

От обобщението на обикновените акции и собствения капитал ние знаем броя на опциите, упражнявани всяка година.

Освен това имаме и опционни приходи от отчетите за паричните потоци (приблизително)

С това трябва да можем да намерим ефективна стачка.

Colgate 2013 - 10K, Страница 53

Също така имайте предвид, че опциите за акции имат договорни условия от шест години и жилетка над три години. Colgate 2013 - 10K, Страница 69

С тези данни попълваме данните за опциите, както е посочено по-долу. Ние също така отбелязваме, че среднопретеглената стачка на цената на опциите за акции за 2013 г. е била $ 42, а броят на изпълнимите опции е 24,151 милиона Colgate 2013 - 10K,

Стъпка 9Е - Опции за запаси: Намерете постъпленията от опцията.

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

Стъпка 9F - Опции за акции: Прогноза Ограничени данни за складови единици

В допълнение към опциите за акции, има ограничени дялови единици, дадени на служителите със среднопретегления период от 2,2 години Colgate 2013 - 10 000 , Страница 81

Попълване на тези данни в набора от опции За по-голяма простота не сме предвидили издаването на опции (знам, че това не е правилното предположение; обаче, поради липса на данни, не извеждам повече проблеми с опциите напред. като нула, както е подчертано в сивата зона по-горе. Освен това се очаква ограничените запаси да бъдат 2.0 милиона занапред.

Освен това разгледайте метода на държавните ценни книжа.

Стъпка 9G - Дивиденти: Прогнозирайте дивидентите

Прогнозирайте прогнозни дивиденти, като използвате коефициента на изплащане на дивидент.
Фиксиран изплащане на дивидент Изплащане на акция
От 10K отчетите извличаме цялата минала информация за дивиденти.
С информацията за изплатените дивиденти можем да разберем коефициента на изплащане на дивидент = Общо изплатени дивиденти / нетен доход.
Изчислих съотношението на изплащане на дивиденти на Colgate, както се вижда по-долу - Отбелязваме, че коефициентът на изплащане на дивиденти е в обхвата от 50% -60%. Нека приемем коефициента на изплащане на дивидент от 55% през следващите години.
Също така можем да свържем прогнозирания нетен доход от отчета за доходите.
Използвайки както прогнозирания нетен доход, така и коефициента на изплащане на дивиденти, можем да намерим Общо изплатени дивиденти.

Стъпка 8H - Прогнозиране на сметката на собствения капитал в неговата цялост

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

Стъпка 9I - Свържете окончателния собствен капитал с баланса

Стъпка 9J - Свързване на дивиденти, обратно изкупуване и опции постъпва към CF

# 10 - Споделя изключителен график

Следващата стъпка в това онлайн финансово моделиране в обучението на Excel е да разгледате Графика за независими акции. Обобщение на списъка с неизплатени акции

Основни акции - действителни и средни
Заснемете предишни ефекти от опции и кабриолети, както е подходящо
Разредени акции - средно
Референтни акции, изкупени обратно и нови акции от упражнени опции
Изчислете прогнозираните сурови проценти (действителни)
Изчислете средните основни и разредени акции
Препратете прогнозираните акции към Отчета за доходите (припомнете Отчет за доходите Създайте!)
Въведете исторически споделяния неизпълнена информация
Забележка : Този график обикновено е интегриран с Графика на собствения капитал

Стъпка 10А - Въведете историческите числа от 10K отчета

Издадените акции (реална реализация на опции) и обратно изкупените акции могат да бъдат посочени от Графика за акционерния капитал
Входните данни претеглят средния брой акции и ефекта от опциите за акции за историческите години.

Стъпка 10Б - Свържете емисиите и обратно изкупуване на акции от Графика за дялово участие.

Основни акции (приключващи) = Основни акции (в началото) + Емисии на акции - Акциите са обратно изкупени.

Стъпка 10C - Намерете основните среднопретеглени акции,

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

Стъпка 10D - Свържете основни и разредени претеглени акции към Отчета за доходите

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

С това ние попълваме Графика за запазване на акциите и времето, за да преминем към следващия ни набор от изявления.

# 11 - Попълване на отчетите за паричните потоци

За нас е важно да попълним изцяло отчетите за паричните потоци, преди да преминем към следващия и последен график в това Финансово моделиране, т.е. Графика на дълга. До този етап има само няколко непълни неща

Отчет за доходите - разходите за лихви / приходи са непълни на този етап
Счетоводен баланс - паричните и дълговите позиции са непълни на този етап

Стъпка 11А - Изчислете паричния поток за финансови дейности

Също така вижте паричния поток от финансиране

Стъпка 11Б - Намерете нетно увеличение (намаление) в Пари и парични еквиваленти

Стъпка 11C = Попълнете отчетите за паричния поток

Намерете паричните средства в края на годината в края на годината.

Стъпка 11D - Свържете паричните средства и техните еквиваленти към баланса.

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

# 12- Финансово моделиране в Excel - График на дълга и лихвите

Следващата стъпка в това онлайн финансово моделиране е попълването на Графика на дълга и лихвите. Резюме на дълга и лихвите - график

Степен 12А - Създайте график на дълга

Посочете паричния поток, наличен за финансиране
Препращайте към всички източници на собствения капитал и използването на пари в брой

Стъпка 12В - Изчислете паричния поток от погасяването на дълга

Позоваване на началния паричен баланс от баланса
Приспадане на минимално парично салдо. Предполагаме, че Colgate би искал да запази минимум 500 милиона долара всяка година.

Пропуснете издаването / погасяването на дългосрочен дълг, наличните пари за револвиращ кредитен механизъм и раздела за револвер засега От 10K отчета на Colgate; отбелязваме наличните подробности за Revolved Credit Facility Colgate 2013 - 10K, Страница 35

Също така в допълнителна информация за дълга се предоставя ангажираното дългосрочно погасяване на дълга. Colgate 2013 - 10K, Страница 36

Стъпка 12C - Изчислете крайния дългосрочен дълг.

Ние използваме графика за погасяване на дългосрочен дълг, предоставен по-горе, и изчисляваме крайния баланс на дългосрочните погашения на дълга.

Стъпка 12D - Свържете дългосрочните изплащания на дълга.

Стъпка 12Е-Изчислете дискреционните заеми / плейофини.

Използвайки формулата за паричен размах, както е показано по-долу, изчислете дискреционните заеми / плащания.

Стъпка 12F - Изчислете лихвените разходи от дългосрочния дълг

Изчислете средното салдо за револвиращ кредитен механизъм и дългосрочен дълг
Направете разумно предположение за лихвен процент въз основа на информацията, предоставена в доклада 10K
Изчислете общите лихвени разходи = средно салдо на дълга х лихвен процент

Намерете общите разходи за лихви = Лихва (Револвиращ кредитен механизъм) + Лихва (Дългосрочен дълг)

Стъпка 12G - Теглене на дълг и револвер на основната връзка към паричните потоци

Стъпка 12H - Референтен текущ и дългосрочен баланс

Демаркирайте настоящата част от дългосрочния дълг и дългосрочния дълг, както е показано по-долу

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

Стъпка 12I - Изчислете лихвения доход, като използвате средното парично салдо

Стъпка 12J - Свържете разходите за лихви и приходите от лихви с отчета за доходите

Извършете проверката на баланса: Общо активи = пасиви + собствен капитал

Стъпка 12K - Одитирайте баланса

Ако има някакво несъответствие, тогава трябва да проверим модела и да проверим за грешки в свързването

Препоръчителен курс за финансово моделиране

Надявам се, че ви е харесало ръководството за безплатно финансово моделиране в Excel. Ако искате да научите финансово моделиране в Excel чрез нашите видео лекции за експерти, можете да разгледате и нашето обучение за инвестиционно банкиране. Това е предимно 99 курса пакет за обучение по инвестиционно банкиране. Този курс започва с основите и ще ви отведе до напредналото ниво на Инвестиционно банкиране. Този курс е разделен на пет части -

Част 1 - Обучение за инвестиционно банкиране - Основни курсове
(26 курса)
Част 2 - Обучение за усъвършенствано моделиране на инвестиционно банкиране
(20 курса)
Част 3 - Добавки за инвестиционно банкиране
(13 курса)
Част 4 - Курсове на фондация за инвестиционно банкиране
(23 курса)
Част 5 - Меки умения за инвестиционни банкери
(17 курса)

Изтегляне на финансови модели

Финансов модел на Alibaba
Кутия IPO Финансов модел
Шаблони за финансово моделиране
Курс по банково финансово моделиране

Какво следва?

Ако сте научили нещо ново или сте се насладили на това базирано на Excel финансово моделиране, моля, оставете коментар по-долу. Кажи ми какво мислиш. Много благодаря и се погрижете. Честито обучение!