≡× МЕНЮ

• Постройки
• Отделка
• Кладка
• Раствор
• Расчет

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

" № 28/2011

Александр Зеляев, заместитель начальника отдела
проектного планирования управления инвестиционных программ ОАО «АВТОВАЗ»

Павел Зырянов, начальник управления инвестиционных программ
ОАО «АВТОВАЗ»

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

Установка ограничений

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

Всю информацию заносят в одну таблицу, например, как показано на скриншоте (см. рисунок). В примере условий несколько. Во-первых, это свободные суммы, которые есть у компании на счетах, и время, в течение которого эти деньги не нужны в обороте. На скриншоте это диапазоны ячеек H12:K12 и H11:K11.

Во-вторых, надо внести ставки по депозитам, которые предлагают банки (D17:G25). Наконец, в-третьих, предстоит ввести сведения об объемах неиспользованных лимитов по каждому из банков (С17:С25).

Настройка файла для расчетов

После надо создать табличные блоки для вывода промежуточных и итоговых данных. В примере это разделы «Суммы к размещению на депозитах по срокам в днях, руб.», «Итого размещено, руб.», «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» и «Доходность от размещения на депозитах, итого».

В блоке «Суммы к размещению на депозитах по срокам в днях, руб.» (H17:K25) отразятся результаты. Excel здесь заполнит все сам.

В разделы «Итого размещено, руб.» (С17:С25)) и «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» (M17:P25) потребуется вручную ввести формулы. Они нужны, чтобы суммировать депозиты и вычислить доход от вклада. Итоговый результат будет в ячейке «Доходность от размещения на депозитах итого, в рублях».

Поиск решения

Запускаем процедуру «Поиск решения». Для этого надо кликнуть по одноименной кнопке на вкладке панели инструментов «Данные». В нашем примере это будет выглядеть так. В появившемся диалоговом окне в графе «Установить целевую ячейку» надо поставить ячейку С30 «Доходность от размещения на депозитах итого, в рублях». Затем выбрать «максимальное значение». Диапазон, в котором будут отражаться результаты решения, то есть суммы вкладов в различных банках (H17:K25).

И в этом же диалоговом окне необходимо задать ограничения. Они у нас следующие

– все свободные деньги должны быть размещены на депозитах (Н27:К27 = Н12:К12);

– сумма вкладов в одном банке с учетом лимита (L17:L25 <= C17:C25).

Затем надо нажать кнопку «Выполнить». Программа найдет самое выгодное решение.

КСТАТИ. В Excel настройка «Поиск решения» по умолчанию отключена. Включают ее так: кнопка «Office», затем «Параметры Excel» > «Надстройки» > «Перейти», в появившемся окне поставить галочку «Поиск решения» и нажать «Ок».

Скачать файл с готовыми формулами и инструкцию пользователя .

Подготовлено в сотрудничестве с редакцией журнала

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

Функция ПЛТ

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

ПЛТ(ставка;кпер;пс;бс;тип)

• Ставка - процентная ставка по ссуде.
• Кпер - общее число выплат по ссуде.
• Пс - приведённая к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
• Бс - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент «бс» опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение «бс» равно 0.

Функция СТАВКА

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

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

• Кпер - общее число периодов платежей для ежегодного платежа.
• Плт - выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
• Пс - приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
• Бс (необязательный аргумент) - значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
• Тип (необязательный аргумент) - число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
• Прогноз (необязательный аргумент) - предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Функция ЭФФЕКТ

Возвращает эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты. Полное описание функции

Практический курс «Программирование в Excel для финансистов» разработан для бухгалтеров, финансовых аналитиков, специалистов в сфере управленческого учета, а также специалистов, которые часто сталкиваются с обработкой больших объемов финансовых данных и хотят изучить возможности автоматизации этих процессов с помощью создания программ в MSExcel.

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

С помощью полученных знаний вы легко сможете самостоятельно автоматизировать свою рутинную работу (сверить две выписки, сформировать график платежей, произвести сверку актов, загрузить курсы валют с сайта Центрального Банка РФ, сделать консолидированный ОДДС (Cash-flowstatement) по выпискам нескольких компаний холдинга и многое другое).

Тренинг носит практическую направленность, поэтому для участия в нём слушателям необходимо принести ноутбук с установленным Microsoft Office не ниже 2010 версии (для Windows) с возможностью создания макросов.

Программа обучения

Практический курс «Программирование в Excel для финансистов» состоит из двух блоков:

Базовый блок

Данный блок предлагает освоить (освежить) базовые знания в области программирования на языке VisualBasic (forExcel).

1) Интерфейс редактора Visual Basic

• Окно редактора VBA Безопасность макросов, рекомендации по выбору уровней безопасности
• Функциональные окна и их назначение. Работа в редакторе

2) Простая запись макроса

• Просмотр и корректировка программного кода, созданного автоматически
• Оптимизация кода
• Создание и отладка процедуры Sub
• Ввод программного кода в «ручном режиме», варианты запуска макросов. Упрощенный ввод программного кода в ручном режиме

3) Основы программирования в Visual Basic (изучение на основе редактора VB для MSExcel)

• Использование служебного блока WITH … END WITH
• Объявление переменных «Область жизни» переменной (локальные и глобальные переменные)
• Типы переменных Возможности использования сложных (объектных) переменных
• Управляющие операторы языка Visual Basic
• Циклы в Visual Basic
• Встроенные функции VB for Office
• Основные функции обработки данных разных типов. Преобразование типов данных
• Создание пользовательских функций
• Отличие от процедур. Различные варианты передачи параметров (переменных) в функцию Практическая отработка навыков разработки функций

4) Отладчик программ

• Возможные ошибки при написании программ. Ошибки первого уровня (синтаксические), второго уровня (логические) и Runtime-ошибки
• Возможности по использованию отладчика для поиска ошибок в программах

5) Заключительная часть

• Основы объектной модели MSExcel
• Основные свойства и методы объекта Range Варианты использования. Практическое применение для обработки данных
• Объект Selection. Практическое использование объекта

По итогам обучения на базовом блоке слушатели смогут:

• Самостоятельно записывать макросы, вносить в них правки, оптимизировать код макросов
• Писать процедуры в «ручном режиме», запускать их в различных режимах
• Создавать пользовательские функции для автоматизации обработки данных в MSExcel
• Использовать переменные и понимать особенности применения различных типов переменных
• Разбираться в различиях объявления переменных
• Выявлять ошибки в программах, используя отладчик редактора Visual Basiс

Продвинутый блок

Данный блок предлагает освоить сложные навыки знания в области создания программ на языке Visual Basic (for Excel).

1) Объектно-ориентированное программирование (изучение на основе редактора VB для MSExcel)

• Понятие объекта, классов, событий и методов

2) Объектная модель MSExcel

• объект Workbook (коллекция WorkBooks)
• объект Worksheet (коллекция WorkSheets)
• объект Range - основы использования

3) Свойства, методы и возможности использования. Работа со справочной системой Microsoft (MSDN )

4) Перехват событий в среде VB for Office

5) UserForm и элементы управления для использования на формах пользователя:

• TextBox (текстовое поле)
• Label (надпись, метка)
• ListBox (список) и ComboBox (поле со списком)
• CheckBox (флажок) и OptionButton (переключатель)
• CommandButton (кнопка)

второстепенные:

• TabStrip (набор вкладок)
• MultiPage (набор страниц)
• ToggleButton (выключатель)
• ScrollBar (полоса прокрутки)
• SpinButton (счетчик)
• Image (рисунок)

6) Диалог открытия файлов FileDialog

• Синтаксис, варианты использования

7) Организация доступа к файловой системе компьютера

8) Организация взаимодействия между приложениями. Управление MSWord из программ MSExcel

9) Практические работы

• Практическая работа 1. Разработка функции «Курс ЦБ РФ»
• Практическая работа 2. Разработка программы «Сверка выписок»
• Практическая работа 3. Разработка программы «Формирование консолидированного ОДДС»

11) Итоговое тестирование

• По итогам обучения слушатели проходят тестирование на усвоение пройденного материала.
• При успешной сдаче итогового теста слушателям выдается Удостоверение о повышении квалификации.

По итогам обучения на продвинутом блоке слушатели смогут:

• Понимать и использовать особенности Объектно-Ориентированного программирования в MSExcel
• Создавать программы (1) для автоматической обработки данных, используя объектную модель MSExcel
• Уметь создавать и использовать в программах формы пользователя
• Работать из программ c файловой системой компьютера (с файлами и директориями)
• Управлять приложениями MSOffice из создаваемых программ
• Подгружать данные через Интернет и автоматизировать данную загрузку
• Обрабатывать данные из нескольких файлов

Расписание

Продолжительность курса: 36 ак. часов

Время занятий: с 09:30 до 17:00 по субботам

• 16 ноября
• 23 ноября
• 30 ноября
• 7 декабря
• 19 декабря (четверг) с 18.45-22.00 Защита итоговой работы

Полная стоимость: 28 900 руб.

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

Курс «Excel для финансиста» поможет Вам:

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

Курс ориентирован на финансовых директоров, специалистов финансовых служб, пользователей MS Excel других специальностей и всех заинтересованных лиц.

Обучение проводит сертифицированный преподаватель Microsoft.

ПРОГРАММА КУРСА:

1. Финансовые возможности MS Excel:

• функция БС - возвращает будущую стоимость инвестиции на основе постоянной процентной ставки.
• функция ПЛТ - возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки.
• функция КПЕР - возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.
• функция ВСД - возвращает внутреннюю ставку доходности для ряда потоков денежных средств, представленных их численными значениями.
• функция ЧПС - возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также последовательность будущих выплат (отрицательные значения) и поступлений (положительные значения).
• функция МВСД - возвращает модифицированную внутреннюю ставку доходности для ряда периодических денежных потоков и учитывает как затраты на привлечение инвестиции, так и процент, получаемый от реинвестирования денежных средств.
• функция ПРПЛТ - возвращает сумму платежей по процентам для инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.
• функция ДОХОД - возвращает доходность ценных бумаг, по которым производятся периодические выплаты процентов и используется для вычисления доходности облигаций.
• функция ПС - возвращает приведенную (к текущему моменту) стоимость займа или инвестиции на основе постоянной процентной ставки.
• функция СТАВКА - возвращает процентную ставку по аннуитету за один период.
• функция ЭФФЕКТ - возвращает фактическую (эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты.
• функция БЕЗРАСПИС - возвращает будущую стоимость первоначальной основной суммы после применения ряда (плана) ставок сложных процентов и используется для вычисления будущей стоимости инвестиции с переменной процентной ставкой.
• функция ЧИСТНЗ - возвращает чистую приведенную стоимость для денежных потоков, которые не обязательно являются периодическими. Чтобы вычислить чистую приведенную стоимость для ряда периодических денежных потоков
• функция ОСПЛТ - возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянных периодических платежей и постоянной процентной ставки.

2. Статистические возможности MS Excel:

• МАКС, МИН, СРЗНАЧ, СРЗНАЧА, МЕДИАНА
• функция ПРЕДСКАЗ - вычисляет или предсказывает будущее значение по существующим значениям. Эту функцию можно использовать для прогнозирования будущих продаж, потребностей в оборудовании или тенденций потребления.
• функция ДОВЕРИТ - возвращает доверительный интервал для среднего генеральной совокупности с нормальным распределением.
• функция РОСТ - рассчитывает прогнозируемый экспоненциальный рост на основе имеющихся данных.
• функция ТЕНДЕЦИЯ - возвращает значения в соответствии с линейным трендом.
• функции СТАНДОТКЛОНА, СТАНДОТКЛОНА,В и СТАНДОТКЛОН.Г- оценивает стандартное отклонение по заданным параметрам.

3. Финансовое моделирование и инвестиционный анализ с помощью MS Excel

• Анализ ЧТО-ЕСЛИ (диспетчер сценариев, таблица данных, подбор параметров)
• Надстройка Solver (для решения многопараметрических задач)
• Построение прогнозов
• Построение диаграммы ПЛАН_ФАКТ
• Расчеты кредитов

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

Продолжительность: 16 ак.ч

Даты проведения семинара в Москве в 2018 году

Внимание! В расписании возможны изменения. Просим уточнять информацию по электронной почте [email protected]

В стоимость включено:

Набор для записей

Пособие

Кофе-брейк

Обед

Сертификат

Освоить Excel легко! Если вы придерживаетесь противоположного мнения, вам не попадалось классное пособие для изучения программы.

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

Итак, вот мой выстраданный перечень из 10 лучших книг по Excel:

1. Джон Уокенбах “Microsoft Excel 2013. Библия пользователя”

2. Джон Уокенбах “Формулы в Microsoft Excel 2013”

Знаете, о чём я пожалел после покупки этой книги? О том, что она не попала в мои руки намного раньше. Это же настоящий кладезь мудрости! Джон Уокенбах за руку проведёт вас от элементарных возможностей Excel до мастерского владения функциями. Вы научитесь работать с ячейками и диапазонами, ворочать огромными массивами данных и извлекать из них нужную информацию, обрабатывать и анализировать данные любого типа, и многое-многое другое. Под конец станете таким крутым спецом, что сможете создавать пользовательские функции в VBA самостоятельно. Берите и изучайте “Формулы в Microsoft Excel 2013” от корки до корки. Она того стоит!

3. Джон Уокенбах “Excel 2013. Профессиональное программирование на VBA”

4. Билл Джелен и Майкл Александер “Сводные таблицы в Microsoft Excel”

Кому не хочется поднять производительность работы? В разы сократить затраты времени на занудную отчётность? Почти мгновенно оценивать и анализировать данные? А как насчёт урезать длинный запутанный отчёт до лаконичного и понятного? Сложно? Ничуть! Со сводными таблицами в Microsoft Excel все эти фокусы – проще пареной репы. Если вам частенько приходится иметь дело со сложной отчётностью, труд Билла Джелена и Майкла Александера – must have в вашей библиотеке.

5. Куртис Фрай “Microsoft Excel 2013. Шаг за шагом”

6. Грег Харвей “Microsoft Excel 2013 для чайников”

7. Конрад Карлберг “Бизнес анализ с использованием Excel”

Что может быть скучнее, чем тягомотина с тоннами отчётов? Сидеть и анализировать ситуацию или разбираться с деловыми задачами приходится часами. Да бросьте! Вы серьёзно не в курсе, что всё это можно с легкостью делать в Excel? Эта книга научит вас решать любые бизнес-задачи шутя! С помощью Excel вы сможете вести электронную бухгалтерию, прогнозировать и составлять бюджет, оценивать и анализировать финансовый оборот, предсказывать спрос на продукцию, просчитывать товарный запас, управлять инвестициями, а также многое другое. К слову, пособие Карлберга придётся кстати не только предпринимателям, но и менеджерам. Вы ведь не собираетесь сидеть на месте, как пресловутый камень, который вода обходит стороной? Нет? Тогда берите “Бизнес анализ с использованием Excel”, учитесь и развивайтесь!

8. Шимон Беннинг “Основы финансов с примерами в Excel”

Любопытный факт: почти все авторы пособий по финансам в своих книгах пренебрегают Excel. И очень зря. Ведь сейчас большинство компаний выполняют расчёты именно в этой программе. Шимон Беннинг заметил эту оплошность и выпустил “Основы финансов с примерами в Excel”. В книге вы найдёте не только практические примеры, но и почерпнёте важные знания о том, как строить финансовые модели, оценивать активы, принимать финансовые решения в нестандартных условиях и так далее. Я считаю, что финансы нужно изучать в контексте работы с Excel. Вот почему рекомендую пособие “Основы финансов с примерами в Excel”, как один из лучших учебников. Труд Шимона Беннинга пригодится и студентам и профи.

Изучать Excel можно вечно. Когда-то я думал, что мой запас знаний о программе тянет на вагон и небольшую кошёлку. Оказалось, что знал я далеко не всё. В Excel есть масса хитростей. О том, как ими пользоваться, и расскажет Джон Уокенбах. Книга содержит такое количество подсказок, советов, трюков, хитростей и приёмчиков, что удержать их все в голове порой невозможно. Вместе с тем каждый из них стоит взять на заметку, чтобы сократить время работы над той или иной задачей. Либо улучшить качество этой самой работы. Так что для меня книга “Excel 2013. Трюки и советы Джона Уокенбаха” стала настольной. Чего и вам желаю!

10. Николай Павлов “Microsoft Excel. Готовые решения – бери и пользуйся!”

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

Пособиями, которые я рекомендую в этой статье, пользуюсь до сих пор. Все они принесли огромную пользу мне и обязательно принесут её вам. Безусловно, лучше обзавестись бумажными экземплярами, чтобы делать пометки и оставлять закладки. Так удобней! Вот увидите, эти книги ускорят вашу работу с Excel и вдохнут в неё новую жизнь. Если вы так не считаете, с удовольствием поспорю с вами в комментариях! =)