Финансовая модель в Excel

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

Составляющие модели

Финансовая модель в Excel позволяет представить в цифровом виде тенденцию изменения финансово-экономических показателей компании при изменении входных данных. Иными словами она оценивает эффективность деятельности компании и дает прогноз ее развития в случае принятия конкретного управленческого решения. Поскольку такое решение принимает определенный человек, создавая модель необходимо учесть, кто именно воспользуется результатами расчетов.

Финансовая модель бизнеса в Excel включает следующие части

• Входные данные, которые делятся на изменяемые параметры и константы;
• Алгоритм расчетов – исполняемые макросы и стандартные формулы;
• Результаты расчетов, представленные в виде утвержденных форм.

Входные данные делятся на два типа. К первым относятся показатели, которые управляющий моделью человек будет изменять. Это ассортимент выпускаемой продукции, ее цена, состав и количество персонала предприятия, и другие показатели.

К неизменным данным относятся данные, являющиеся условно неизменными. Это внешние факторы, которые не зависят от деятельности фирмы: курсы валют, инфляция, цены на сырье, инвестиции и т.д. Сюда же входят параметры, которыми компания управлять может, но в конкретном случае эта возможность не берется в расчет. Например, административные затраты или возможность отсрочки каких-то платежей.

Алгоритмы расчетов состоят из формул, исполняемых макросов, промежуточных табличных данных, используемых моделью для последующих вычислений.

Результаты расчетов оформляются в наглядном виде и являются информацией, на основе которой руководитель

принимает свое решение. Это различные отчеты и финансово-экономические показатели.

Модель, созданная для компании, обычно включает в себя финансовые коэффициенты, показатели рентабельности, планы по различным бюджетам и балансы.

Финансовая модель для инвестиционного проекта оценивает, насколько он может быть привлекательным инвестору. Это доходность проекта, сроки окупаемости вложений и другие подобные показатели.

Основные правила создания моделей бизнеса

Для того чтобы модель в Excel была удобна пользователю, необходимо руководствоваться следующими правилами:

1. Каждая из трех частей модели должна размещаться на отдельных листах;
2. Необходимо, чтобы система расчетов была понятна не только разработчику, но и пользователю;
3. Столбцы на разных листах должны быть аналогичны: например, если это даты, то на всех листах необходимо представить даты;
4. Если формулы однотипны, они размещаются в одинаковых строках;
5. Не следует в формулах подставлять непосредственно входные данные – их надо вводить в отдельные ячейки, ссылки на которые даются в формулах;
6. Нельзя допускать циклические ссылки, которые могут привести к зацикливанию программы;
7. Не следует применять скрытые столбцы или строки;
8. Имена массивов необходимо использовать только в крайних случаях;
9. Не следует слишком усложнять алгоритм расчета, для пользователя он должен быть понятен;
10. Интерфейс модели следует сделать максимально удобным пользователю.

Характеристики финансовых моделей

Прежде всего, любая модель должна быть достоверна. Для этого необходимо использовать входные данные только первого уровня. У пользователя не должно возникать сомнений в целесообразности включения в модель каких-либо данных. Это могут быть цены на материалы, зарплаты персонала компании, различные арендные ставки.

Такая модель объективно отражает эффективность бизнеса, который она анализирует.

Глубина модели должна охватывать достаточно продолжительный период времени. Например, если пятилетний период не дает положительных показателей, его следует увеличить. Возможно, на более длительном отрезке времени успеют проявиться позитивные тенденции, заложенные в проект его разработчиком.

Многие компании, особенно банковские структуры, считают обязательным построение финансовых моделей именно средствами Excel. Это объясняется желанием руководства компаний самостоятельно тестировать расчетные показатели моделей, анализируя очередной бизнес-проект.

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

Сервис Финоко разработал уникальную систему, которая позволяет перенести из табличного редактора финансовую модель, подключить ее к данным бухгалтерского учета, CRM системам, POS системам и проводить регулярный анализ выполнения поставленных целей на этапе планирования.

Необходимо заботиться о повышении квалификации разработчиков финансовых моделей. Они должны посещать различные международные семинары, где можно из первоисточников получить информацию о появлении новых стандартов в бизнесе.

Структура модели

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

Каждую группу следует выделить визуально, связывая их общепринятыми формулами. Лучше всего разделить их на отдельные листы и выделить однотипные составляющие модели одинаковым цветом.

Следует исключить скрытые или недоступные ячейки. Руководитель компании или потенциальный инвестор, анализирующий финансовую модель, сможет в этом случае самостоятельно разобраться в применяемых формулах. Достоверность входных данных не должна вызывать сомнений.

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

Модель должна быть достаточно гибкой, чтобы при изменении ее отдельных компонентов, результаты расчетов корректировались соответствующим образом. Даже самые сложные компоненты модели становятся понятными, если ее структура логична и визуально разделена.

Анализ рисков посредством финансовых моделей

При анализе рисков в моделях рассчитываются следующие показатели:

• Ставка дисконтирования. Если корректность ее расчета не вызывает сомнений, а показатель прибыли положительный, то в проект можно вкладываться.
• Безубыточность. Анализируя этот показатель, пользователь оценивает вероятность снижения продаж. Проект с запасом финансовой прочности свыше 100% обладает низким риском.
• Чувствительность проекта. Характеризует поведение анализируемого объекта в условиях изменения ключевых факторов, например объемов продаж или непосредственных издержек. В результате определяется окупаемость проекта при возможных ошибках во входных данных.

Преимущества моделей в Excel

Использование таких моделей предоставляет руководству предприятия следующие возможности:

• Выявить направления развития проекта, четко оценить его эффективность при определенных затратах на реализацию проекта.
• Анализировать множество финансовых показателей.
• Проводить анализ всевозможных изменений, вносимых в исследуемый проект.
• Гибкость моделей и доступность их для понимания любому пользователю. Всегда можно увидеть формулу, используемую для расчета определенного показателя, а входные данные изменяются как угодно.
• Все расчеты производятся последовательно и логика алгоритма не вызывает сомнений.

Для моделей в Excel предоставляется следующая информация:

• Баланс предприятия в момент подачи последнего отчета;
• Выпускаемая продукция, ее стоимость, объемы продаж потребителям, способы взаиморасчетов;
• Издержки фирмы, включая общие и непосредственные, а также зарплату работников;
• Как осуществляется финансирование;
• План инвестиций;
• Если задействуется лизинг, необходимы данные о его условиях.

Выходными результатами финансовых моделей в Excel могут быть следующие:

• Отчеты о возможных прибылях либо убытках;
• Подробный отчет о движении денег;
• Данные о балансе компании;
• Всевозможные финансовые показатели, характеризующие проект.

Когда этап планирования или моделирования бизнеса будет завершен, наступает время контроля исполнения поставленных планов. Мы предлагаем использовать сервис Финоко, который даст вам возможность:

• Автоматизировать сравнение планов в Excel и фактических данных из бухгалтерских программ (например, из 1С);
• Предоставить доступ к отчетам руководству и руководителям отделов;
• Консолидировать данные нескольких юридических лиц в одну систему отчетности;
• Гибко устанавливать измеримые цели персоналу и контролировать выполнение.

Попробуйте веб сервис Финоко, оставьте заявку и мы предоставим вам доступ к системе на один месяц бесплатно.

Расчет результативности инвестиций в EXCEL

Как быть уверенным, что инвестиции приближают нас к поставленным задачам? В инвестициях практически всегда вместе с любой задачей параллельно следует необходимость «не потерять». Не потерять в мире инвестиций – это значит получать доходность выше инфляции. Переформулировав – портфель должен иметь реальную доходность выше нуля.

При учете результатов инвестиций почти всегда необходимо быть уверенным, что на длинных сроках доходность инвестиционного портфеля выше инфляции. Второй важный элемент — это сравнение доходности с «безрисковыми» инструментами. Инвестор, вкладывая деньги в ценные бумаги, берет на себя дополнительные риски. Подразумевается, что вместе с дополнительными рисками он получает возможность более высокой доходности. Если доходность инвестиций (мы всегда говорим о длинных сроках) ниже, скажем, средней ставки депозита, то зачем брать на себя дополнительные риски?

Есть и другие важные параметры, которые следует учитывать, но все они так или иначе сводятся к необходимости считать доходность. Доходность может быть разной – среднегодовой или накопленной, но считать и понимать эти цифры очень важно для любого инвестора. Без них непонятно, приближают ли нас инвестиции к целям или наоборот – удаляют от них.

Как считать доходность?

Почему большинство инвесторов часто имеют неправильное представление о том, какова настоящая результативность их инвестиций.

Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:

А – полученный доход

В – стартовые инвестиции

Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …

Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …

IRR или Внутренняя норма доходности (ВНД)

Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.

Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах.

Если IRR меньше ставки по депозитам в Сбербанке, то надо задуматься, все ли нормально с инвестиционной стратегией.

Шаблон для расчета IRR инвестиций в EXCEL

Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.

Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.

Кроме IRR инвестиционного портфеля в шаблоне можно посмотреть общий прирост портфеля (на сколько размер портфеля отличается от объема инвестированных средств).

Учет результатов инвестиций для сложных портфелей

Важное свойство калькулятора – это возможность измерения результативности инвестиций для широко диверсифицированных портфелей. Часто встречаются ситуации, когда у инвестора несколько брокерских счетов (российский и зарубежный), часть денег размещено в ПИФах через Управляющую компании. Кроме всего, может быть открыт ОМС (Обезличенный металлические счета – используются для покупки драгоценных металлов), куплена недвижимость и тому подобное. В таком случае рассчитать результат инвестиций для итогового портфеля бывает довольно проблематично… Предлагаемый калькулятор поможет справиться с этой задачей. Достаточно регулярно (например, один раз в год) считать суммарный размер всех активов в портфеле и вносить в таблицу пополнения и изъятия.

Расчет доходности к погашению для облигаций

Хотя это и не основная функция калькулятора, но его довольно просто можно использовать для расчета доходности к погашению для облигаций. Доходность к погашению для облигаций определяется именно как IRR всего денежного потока.

Для вычисления доходности к погашению необходимо внести сумму покупки облигации и планируемые поступления в виде дивидендов.

В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.

Ограничения калькулятора

Калькулятор будет показывать, в том числе, нереализованный доход. Например, если ценная бумага выросла в цене, но еще не продана, то такой доход инвестора называется нереализованным. Поэтому предлагаемый шаблон не может быть использован для расчета налогов (НДФЛ). Нереализованный доход не считается налоговой базой.

Другие финансовые калькуляторы для EXCEL можно найти разделе Калькуляторы.

Калькулятор результативности инвестиций в EXCEL
Файл: investment_tracker.xlsx
Размер: 48684 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

Финансовая модель проекта в Excel. Сам или по шаблону?

Финансовая модель проекта в Excel. Сам или по шаблону?

Финансовая модель проекта в Excel считается правилом хорошего тона в современном бизнесе. Расчеты на салфетках и прикидки остались, но все чаще моделирование бизнеса становится уже не диковинкой, а скорее чем-то совсем обыденным. При этом моделирование только проходит этап становления, поэтому модели встречаются всё чаще, но не всегда они надлежащего качества. Очень часто модели содержат столько недостатков, что их нельзя даже считать финансовой моделью проекта. В этой статье мы разберем с вами чем расчет отличается от модели и что сделать для того, чтобы ваша модель была действительно моделью.

Что такое финансовая модель проекта?

Финансовая модель проекта является упрощённым в рамках своих допущений отображением финансовых результатов проекта для акционеров, инвесторов, кредиторов или иных заинтересованных лиц. Модель комплексно описывает финансовые потоки компании и дает ответы на вопросы о возврате инвестиций, доходах акционеров, чувствительности к внешним и внутренним факторов.

Финансовая модель проекта в Excel dashboard

Финансовая модель проекта уже становится обязательным атрибутом инвестиционного предложения. Не достаточно предоставить инвестору красивую презентацию и пообещать заоблачные 200-300% прибыли. Инвесторы и кредиторы все чаще хотят понимать, как были получены эти проценты прибыли, какие предпосылки использованы, проверить их корректность, а также протестировать различные сценарии если что-то пойдёт не так.

Что может пойти не так? Спросите вы. По опыту ни один проект не реализуется строго по плану. И дело не в плохом планировании, просто никто не в силах предсказать будущее с точностью до мельчайших деталей. Именно поэтому финансовая модель проекта должна позволять симулировать различные сценарии.

Чаше всего финансовая модель это расчёт в Excel, выполненный по определенным стандартам моделирования. Эти стандарты позволяют выполнять расчёты с минимальным количеством ошибок, делать модель удобной, читаемой и что самое главное гибкой к любому сценарию

Шаблон или индивидуальная разработка финансовой модели проекта?

Финансовую модель проекта можно сделать двумя способами: замоделировать и заполнить шаблон. Оба варианта имеют свои достоинства и недостатки.

В сети достаточно много шаблонов финансовых моделей, используя которые можно получить быстрый результат. Использование шаблона позволяет минимизировать усилия на подготовку модели, даёт быстрый и очень часто не самый хороший результат. Дело в том, что далеко не всегда движок шаблона отвечает требованиям проекта по сути бизнеса, требуемой взаимосвязи предпосылок, налогообложению и так далее.

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

Что нужно помнить, если вы взялись за разработку модели проекта

Разработать финансовую модель проекта можно и самостоятельно, используя «золотые правила» финансового моделирования. Правила помогут сделать расчет прозрачным, понятным, гибким и с минимальным количеством ошибок.

Основные правила финансового моделирования в Excel для новичков следующие:

Одна строка, одна формула

Разделать исходные данные, расчеты и результаты

Чем проще, тем лучше

Это далеко не все «золотые правила» финансового моделирования, но для новичков вполне достаточно.

Структура модели проекта

Структура, финансовая модель проекта в Excel

Вам нравятся книги с удобным оглавлением? Где все разложено по полочкам, можно за пару мгновений найти нужную главу или даже страницу в толстенной книге. Идеально, не так ли? Представьте, что финансовая модель проекта это книга с которой нужно работать. Не должно быть как у Винокура: «Тут играем, тут не играем. А тут я рыбу заворачивал.» Работать с финансовой моделью должно быть удобно.

Предлагаем вашему вниманию условную структуру финансовой модели, которую лучше придерживаться.

Разделите исходные данные расчеты и результат ваших расчетов и вам самому станет удобно работать. Сделайте один лист с исходными данными, на котором соберите абсолютно все цифры, используемые в расчетах. После чего переходите к расчетам. не здесь же, а где-то ниже или на другом листе, если данных для финансовой модели проекта оказалось совсем через-чур много. Когда закончите с расчетами соберите все результаты на отдельный лист. Поверьте, результат вам несомненно понравится.

Этапы разработки финансовой модели проекта

Разработка финансовой модели проекта не требует строгой очерёдности выполнения шагов или задач. Начать можно как с более или менее простых на ваш взгляд секций, а можно и наоборот со сложных. Можно начать как с прогнозирования затрат, так и выручки, или необходимых инвестиций. Кому как удобно.
Однако, чтобы испечь хлеб нужно замесить тесто, и определённая очерёдность разработки все же есть. Раз за разом при разработке модели я выполняю одни и те же действия. И сейчас я с вами поделюсь своим вариантом основных этапов разработки финансовой модели:

1. Планирование структуры и погружение в проект. Всегда нужно какое-то время для того, чтобы погрузиться в проект и понять какая структура расчетов, исходные данные и предпосылки будут наиболее корректно описывать бизнес-проект. Начинать проект лучше только после того, как структура вам ясна и вы достаточно хорошо понимаете проект.
2. Подготовка «рабочего места» или разработка шаблона с временной шкалой. на данном этапе готовятся листы для расчетов, готовится временная шкала и выстраивается скелет модели.
3. Сбор исходных данных и макро-прогнозов. В имеющийся скелет финансовой модели проекта подставляются исходные данные, чтобы было на основании чего выполнять соответствующие расчеты.
4. «Модульные» расчёты. Модули — это определенные самостоятельные блоки расчетов, «органы модели». К таким модулям можно отнести расчеты выручки, себестоимости или оборотного капитала.
5. Техническая проверка финансовой модели и стрессовое тестирование. Найдите кого-то, кто проверит свежим взглядом на расчеты и найдет ошибки. Самопроверка — тоже хорошо, но лучше это сделает сторонний человек, который не участвовал в разработке модели.
6. Упаковка. Финальный этап разработки финансовой модели. Просто наведите порядок, сделайте единообразное форматирование и нажмите клавишу F7

Подумайте, готовы ли вы самостоятельно сделать финансовую модель проекта или будете пользоваться каким-нибудь шаблоном решать вам. Однако я рекомендовал бы вам все же сделать модель самостоятельно или с привлечением специалиста, обязательно погрузившись во все детали. Так вы уж точно лучше поймете финансовые потоки бизнеса не только в цифрах, но и в логике формирования стоимости.

Исходные данные для финансовой модели проекта

В качестве исходных данных проекта используются абсолютно разные предпосылки их перечень может быть бесконечным. При этом по опыту есть наиболее часто встречающиеся данные:

• Данные временной шкалы и о ключевых вехах проекта.
• Данные о внешней среде проекта (макроэкономические прогнозы, рыночные прогнозы, данные по налогообложению и прочим параметрам внешней среды)
• Производственные предпосылки, такие как мощность производства, сменность, цикл выхода на полную мощность и прочее
• Данные по выручке, такие как прогноз цен, объемы реализации всего или на одну торговую точку/ представительство
• Предпосылки по затратам, включающие в себя постоянные и переменные затраты, связанные с проектом
• Инвестиции в проект. Тут важно учесть как инвестиции первоначальные так и инвестиции в поддержание
• Финансирование. Структура финансирования предполагающая финансирование акционером, через кредит или иные источники финансирования.
• Прочие предпосылки, если применимо

В случае если предполагается несколько сценариев, то некоторые из предпосылок должны быть заданы несколько раз, а модель должна подставлять эти предпосылки в модель проекта в зависимости от используемого сценария.

Если у вас еще остались вопросы, задавайте их в комментариях к статье. Спасибо за внимание!

http://www.finoko.ru/instruments/finansovaya-model/finansovaya-model-v-excel/
http://rostsber.ru/publish/stocks/tracking_investments.html
http://finversity.ru/business-2/finansovaya-model-proekta-v-excel.html