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

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

Пример финансового анализа предприятия в Excel

Задача – изучение результатов финансовой деятельности и состояния предприятия. Цели:

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

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

Анализ финансового состояния предприятия подразумевает

• анализ баланса и отчета о прибылях и убытках;
• анализ ликвидности баланса;
• анализ платежеспособности, финансовой стабильности предприятия;
• анализ деловой активности, состояния активов.

Рассмотрим приемы анализа балансового отчета в Excel.

Сначала составляем баланс (для примера – схематично, не используя все данные из формы 1).

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

С помощью простейших формул мы отобразили динамику по статьям баланса. Таким же образом можно сравнивать балансы разных предприятий.

Какие результаты дает аналитический баланс:

1. Валюта баланса в конце отчетного периода стала больше в сравнении с начальным периодом.
2. Внеоборотные активы приращиваются с более высокими темпами, чем оборотные.
3. Собственный капитал предприятия больше, чем заемный. Причем темпы роста собственного превышают динамику заемного.
4. Кредиторская и дебиторская задолженность приращиваются примерно в одинаковом темпе.



Статистический анализ данных в Excel

Для реализации статистических методов в программе Excel предусмотрен огромный набор средств. Часть из них – встроенные функции. Специализированные способы обработки данных доступны в надстройке «Пакет анализа».

Рассмотрим популярные статистические функции.

В примере большая часть данных выше среднего, т.к. асимметрия больше «0».

ЭКСЦЕСС сравнивает максимум экспериментального с максимумом нормального распределения.

В примере максимум распределения экспериментальных данных выше нормального распределения.

Доброго дня вам, уважаемый посетитель.

Используя данные таблицы, вы можете выполнить базовый финансовый анализ коммерческого банка. Для этого перенесите данные из финансовой отчётности в расчётные таблицы, затем нажмите кнопку «обновить» и показатели будут рассчитаны автоматически.

Обратите внимание: начиная с 2016 года, отчётность банков изменилась. Новая версия сервиса анализа финансового состояния и результатов деятельности банка находится здесь: .

1. Введите исходные данные баланса банка и отчёта о финансовых результатах в светло-голубые ячейки, заменив цифры примера.
2. Потом прокрутите страницу вниз, нажмите кнопку «обновить» - все данные будут пересчитаны. Их останется только выделить, скопировать и перенести к себе в документ.

На этой странице вы можете выполнить базовый анализ финансово-имущественного положения банка и его финансовых результатов:

1. Горизонтальный анализ динамики активов
2. Вертикальный анализ структуры активов
3. Горизонтальный анализ динамики пассивов
4. Вертикальный анализ структуры пассивов
5. Горизонтальный анализ динамики источников собственных средств
6. Вертикальный анализ структуры источников собственных средств
7. Горизонтальный анализ динамики внебалансовых обязательств
8. Вертикальный анализ структуры внебалансовых обязательств
9. Горизонтальный анализ динамики финансовых результатов

Выводы по анализу строятся на основе выявленных проблем: примеры проблем, выявляемых при анализе .

Примеры мероприятий для устранения выявленных проблем: примеры мероприятий для ВКР .

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

Для оценки стоимости вы можете оставить заявку на бирже. Если никто не подойдёт - просто удалите заявку и всё.

С уважением, Александр Крылов. Связаться со мной вы можете с помощью vk.com/aldex .

Перед вводом данных ознакомьтесь, пожалуйста, с вот этой статьёй:

Если таблица не помещается, откройте её в новом окне: анализ финансового состояния и результатов работы банка

Финансовый анализ:

• Используя данные таблицы, вы можете выполнить базовый финансовый анализ бюджетного учреждения (организации). Для этого перенесите данные из финансовой отчётности в расчётные таблицы, затем нажмите кнопку…
• На этой странице расположена бесплатная автоматическая таблица финансового анализа, аналогичная таблице Excel (xls). Просто введите исходные данные бухгалтерского баланса и отчёта о финансовых результатах в…
• На этой странице вы можете провести экспресс-анализ финансово-хозяйственной деятельности и состояния предприятия. Просто введите исходные данные на три отчётные даты и произойдёт автоматический расчёт (если нет,…
• Финансовую устойчивость определяют, во-первых, с помощью коэффициентов, а во-вторых, с помощью трёхкомпонентного показателя финансовой устойчивости Сначала приведу перечень коэффициентов: Коэффициент обеспеченности собственными средствами Коэффициент маневренности…
• Структура пассивов (Structure of liabilities) - соотношение долей различных видов обязательств и собственных средств в общей сумме пассивов предприятия. Пассивы - это источники собственных и…
• При подготовке дипломных, курсовых, магистерских и других учебных работ по финансовому анализу очень часто возникает необходимость провести анализ, имея данные за три года только на конец…
• Данная запись содержит уникальный бесплатный генератор финансовой бухгалтерской отчётности за три года. Если вам для вашей работы требуется бухгалтерский баланс и отчёт о прибылях и…
• На этой странице можно выполнить анализ финансовых результатов коммерческого предприятия.. Он позволяет проанализировать динамику и…
• На сайте сайт вы можете выполнить две задачи: Во-первых, вы можете провести финансовый анализ онлайн А во-вторых, ниже на этой странице описаны все виды анализа, которые…
• Эта запись содержит простую электронную онлайн-таблицу, которая позволяет оценить кредитоспособность организации. Оценка кредитоспособности осуществляется по методикам Неволиной Е.В. и ОАО "Сбербанк России". С уважением, Александр…

Сделать финансовый анализ состояния предприятия в Excel за 5 минут вам поможет программа QFinAnalysis.

Из данной статьи вы узнаете:

• О задачах, которые вы сможете решить с помощью программы;
• О возможностях финансового анализа в QFinAnalysis;
• Как работать с программой;

А также сможете скачать Excel файл с примером расчета финансового состояния с помощью QFinAnalysis.

С помощью QFinAnalysis вы сможете решить следующие задачи финансового анализа предприятия :

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

Программа QFinAnalysis поможет вам сделать:

• Диагностику вероятности банкротства - 23 современные методики расчета вероятности банкротства , включая методики с использованием аппарата искусственного интеллекта;
• Анализ финансовых показателей – около 20 финансовых коэффициентов (ликвидности, деловой активности, фин.устойчивости, рентабельности и др.);
• Оценку платежеспособности компании – регламентированный анализ платежеспособности по методике Правительства РФ ;
• Расчет чистых активов предприятия;

Анализ ликвидности баланса предприятия ;

Горизонтальный вертикальный анализ баланса компании;

Прогнозирование банкротства предприятия по наиболее популярным западным методикам (Альтмана, Бивера, Таффлера и др.) и адаптированным отечественным методикам (ИГЭА, Казанской школы, Зайцевой, Савицкой и др.).

Как работать с программой?

Открыв QFinAnalysis, мы попадаем в лист с меню, из которого мы можем перейти в любой лист с программой. нажимая на ссылки соответствующих разделов:

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

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

Баланс предприятия в старой форме вводим в лист "Баланс":

Баланс в новой форме вводим в лист "Новый баланс":

Переходим по страницам и получаем:

1. Анализ динамики баланса и доли по статьям в листе "аналит. баланс"

2. Коэффициенты анализа предприятия для:

• Оценки финансовой устойчивости;
• Оценки ликвидности;
• Оценки рентабельности;
• Оценки деловой активности;

– лист "коэф.":

3. Оценку ликвидности - лист "анализ ликв.":

4. Модели множественного дискриминантного анализа (MDA-модели) оценки риска банкротства предприятия:

• Двухфакторная модель Альтмана
• Двухфакторная модель Федотовой
• Пятифакторная модель Альтмана
• Модифицированная модель Альтмана для России
• Четырехфакторная модель Таффлера
• Четырех факторная модель Лиса
• Четырехфакторная модель Спрингейта
• Четырех факторная модель ИГЭА
• Модель Сайфулина-Кадыкова
• Модель Пареной-Долголаева
• Модель Республики Беларусь
• Модель Савицкой

5. Логистические модели (Logit-модели) оценки риска банкротства предприятия показывают вероятность банкротства в процентах

• Модель Альтмана-Сабато (2007)
• Модель Лина-Пьессе (2004)
• Модель ДжуХа-Техонга (2000)
• Модель ДжуХа-Техонга (2000)
• Модель Грузчинского (2003)
• Модель Грузчинского (2003)

6. Экспертная модель "Зайцевой", в которой каждому показателю можно задавать вес вручную, - лист "экспертн. модели"

• Модель Казанского Государственного Технологического Университета (КГТУ) для предприятий промышленности (которая позволяет определить класс кредитоспособности);
• Модель Бивера;

8. Модель правительства РФ (оценка платежеспособности и структуры баланса предприятия):

9. Расчет чистых активов предприятия - лист "ЧА":

• Novo Forecast Lite - автоматический расчет прогноза в Excel .
• 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Вертикальный анализ – это метод диагностики финансового состояния организации и оценка динамики изменения структуры. Цель и суть вертикального анализа финансовой отчетности является анализ изменения структуры финансовых показателей за рассматриваемый период. Данный анализ применяется для оценки структуры бухгалтерского баланса, отчета о прибылях и убытках и отчета о движении денежных средств. В статье рассмотрим как осуществляется вертикальный анализ баланса и отчета о финансовых результатах на примере предприятия ПАО “КАМАЗ”.

Направления проведения вертикального анализа бухгалтерского баланса организации несет следующие:

• Оценка структурного изменения активов / пассивов компании.
• Расчет изменения доли заемного капитала организации.
• Определение состава оборотного и внеоборотного капитала.
• Сравнение между собой структуры капитала различных компаний или компаний различной отрасли.

Вертикальный анализ может применяться не только для бухгалтерского баланса, а также для отчета о финансовых результатах (форма №2 ) при определении структуры доходов и расходов. Например, для диагностики структуры выручки или прибыли от продаж и т.д. Вертикальный анализ аналогично может использоваться для отчета об изменениях капитала (форма №3 ) и отчете о движении денежных средств (форма №4 ), но, как правило, вертикальный анализ ограничивается для баланса и отчета о финансовых результатах.

Сравнение вертикального анализ баланса с другими методами финансового анализа

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

Название анализа финансовой отчетности	Направления применения	Преимущества	Недостатки
Вертикальный анализ (аналог: структурный анализ )	Применяется для определения структуры капитала организации, финансовых показателей и изменение структуры во времени	Позволяет отследить структурные изменения активов и пассивов компании	Используется для диагностики Не дает оценку финансовому состоянию предприятия
Горизонтальный анализ (аналог: трендовый анализ )	Используется для оценки направления и прогнозирования динамики изменения финансовых показателей	Позволяет оценить динамику изменения по годам финансовых показателей	Служит в большей степени для диагностики, а не для принятия управленческих решений и оценке финансового состояния
Коэффициентный анализ	Оценка финансовых показателей характеризующих: рентабельность, финансовую устойчивость, оборачиваемость и ликвидность организации	Дает оценку эффективности тех или иных показателей хозяйственной деятельности предприятия. Внесенные нормативы позволяют выделить проблемные показатели и принять управленческие решения Используется для оценки финансовой эффективности предприятий одной отрасли	Сложно определить вероятность риска банкротства и уровень финансовой надежности
Скоринговая (рейтинговая) оценка	Комплексная оценка финансового состояния компании, платежеспособности и финансовой надежности. Применение моделей оценки вероятности банкротства, рейтинговых моделей, бальных и экспертных методов	Комплексный критерий на основе модели оценки финансового состояния позволяет определить вероятность риска банкротства	Итоговая оценка может искажаться из-за пикового завышения одного из показателя модели

Пример вертикального анализа баланса для ПАО КАМАЗ в Excel

Рассмотрим пример вертикального анализа бухгалтерского баланса для компании ПАО «КАМАЗ». Для этого необходимо скачать баланс с официального сайта компании или по ссылке → .

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

Доля нематериальных активов (F9) =C9/$C$18

Доля результатов исследований и разработок (F10) = C10/$C$18

Доля основных средств (F13) = C13/$C$18

Доля доходных вложений в материальные ценности (F14) = C14/$C$18

Доля финансовых вложений (F15) = C15/$C$18

Доля отложенных налоговых активов (F16) = C16/$C$18

Доля прочих внеоборотных активов (F17) = C17/$C$18

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

На следующем этапе можно выделить максимальные и минимальные доли в формировании внеоборотных активов на 2014 год.

Максимальную долю в формировании внеоборотных активов (66,3%) занимает основные средства, минимальная доля у результатов исследований и разработок (0,4%). Для отражения динамики изменения структуры необходимо построить диаграмму областей ⇓.

Можно отметить, что к 2016 году наблюдается снижение доли основных средств (ОС) с 66,3% до 36,1% и увеличение доли финансовых вложений с 7% до 43%. Снижение доли ОС может свидетельствовать о снижении вложений предприятия в производство и развитие долгосрочного потенциала. К основным средствам относят: здания, сооружения, оборудование, транспортные средства, инструмент и инвентарь.

Вертикальный анализ отчета о финансовых результатах

Универсальность метода позволяет его применять для анализа отчета о финансовых результатах (форма №2) и определить как изменялась доля расходов и доходов при формировании выручки. Для примера возьмем предыдущую финансовую отчетность ПАО “КАМАЗ” и отразим изменение показателей выручки за 2015 и 2016 год. Можно заметить, что выручка составляет 100%.

Доход от полученных субсидий (Е8) =C8/C7

Себестоимость продаж (Е9) =C9/$C$7

Валовая прибыль (Е10) =C10/$C$7

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

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

С 2015 до 2016 года было увеличение доли валовой прибыли с 4,6% до 9,%, снижение прибыли от продаж с 6,2% до 4,4%, уменьшение прибыли до налогообложения с 4,7% до 1,3% .

Выводы

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

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

Размещено на http://www.allbest.ru/

ФИНАНСОВЫЙ АНАЛИЗ В EXCEL

Задание № 1

Описание функции ПЛТ или ППЛАТ (ставка; кпер; пс; бс; тип)

Возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

ставка -- процентная ставка по ссуде.

кпер -- общее число выплат по ссуде.

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

тип -- число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ

Функция ППЛАТ (ПЛТ) вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке.

Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов СТАВКА и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента СТАВКА используйте 12%/12, а для задания аргумента КПЕР - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента СТАВКА используйте 12%, а для задания аргумента КПЕР - 4.

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ППЛАТ значение на величину КПЕР. Интервал выплат - это последовательность постоянных денежных платежей, осуществляемых за непрерывный период.

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

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

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить n-годичную (общее число периодов выплат) ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой i % и начальным взносом A%, . Cделать расчет для ежемесячных и ежегодных выплат. Найти суммы периодических ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных комиссионных.

Для выполнения задания заполните таблицу своими исходными данными:

Стоимость квартиры - Р

Годовая ставка i%

Срок погашения ссуды n

Начальный взнос A%

Начальный взнос в денежном выражении рассчитывается по формуле:

стоимость квартиры*А%

Ежегодные выплаты рассчитываются по функции

(ПЛТ(ставка; кпер; пс; бс; тип) либо ППЛАТ(ставка; срок; -ссуда);

ежемесячные выплаты

ППЛАТ(ставка/12; срок*12; -ссуда)), либо ПЛТ(ставка/12; срок*12; -ссуда)

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

Общие ежемесячные = ежемесячные*срок*12

Общие ежегодные = ежегодные*срок

Ежемесячные комиссионные = общие ежемесячные - ссуда

Ежегодные комиссионные = общие ежегодные - ссуда

ВАРИАНТЫ ЗАДАНИЙ

ЗАДАНИЕ №2

ЧПС (ставка; значение1; значение2; ...) или НПЗ (ставка; значение1; значение2; ...)

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

ставка -- ставка дисконтирования за один период.

Значение1, значение2,... -- от 1 до 29 аргументов, представляющих расходы и доходы.

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

ЧПС использует порядок аргументов значение1, значение2, ... для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.

Пример 1

	Описание
	Годовая ставка дисконтирования
	Начальные затраты на инвестиции за один год, считая от текущего момента
	Доход за первый год
	Доход за второй год
	Доход за третий год
	Описание (результат)
ЧПС(A2; A3; A4; A5; A6)	Чистая приведенная стоимость инвестиции (1 188,44)

В примере начальные затраты в 10 000 руб. были включены как одно из значений, поскольку выплата производилась в конце первого периода.

Пример 2

	Описание
	Годовая ставка дисконтирования. Она может представлять собой темп инфляции или процентную ставку по конкурирующим инвестициям.
	Начальные затраты на инвестиции
	Доход за первый год
	Доход за второй год
	Доход за третий год
	Доход за четвертый год
	Доход за пятый год
	Описание (результат)
ЧПС(A2; A4:A8)+A3	Чистая приведенная стоимость этой инвестиции (1 922,06)
ЧПС(A2; A4:A8; -9000)+A3	Чистая приведенная стоимость этой инвестиции с потерей 9000 на шестом году (-3 749,47)

В этом примере начальные затраты в 40 000 руб. не были включены как одно из значений, поскольку выплата производилась в начале первого периода.

Рассмотрим следующую задачу. Вас просят дать в долг 10 000 рублей и обещают вернуть через год 2 000 рублей, через два года - 4 000 рублей. Через три года - 7 000 рублей. При какой годовой процентной ставке эта сделка выгодна?

В приводимом расчете в ячейку в ячейку В7 введена формула

=НПЗ (В6; В2:В4)

Первоначально в ячейку В6 вводится произвольный процент, например 3%. После этого выбираем команду Сервиз, Подбор параметра и заполняем открывшееся диалоговое окно Подбор параметра.

В поле Установить в ячейке даем ссылку на ячейку В7, в которой вычисляется чистый текущий объем вклада по формуле

=НПЗ (В6; В2:В4)

В поле Значение указываем 10000 - размер ссуды. В поле Изменяя значение ячейки даем ссылку на ячейку В6, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 рублей. Результат вычисления выводится в ячейку В6.

В нашем случае годовая учетная ставка равна 11,79%.

Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ: Вас просят дать в долг Р рублей и обещают вернуть Р1 руб. через год, Р2 руб. - через два года и т.д. и, наконец, РН руб. через Н лет. При какой годовой процентной ставке эта сделка имеет смысл? (ЧПС(ставка; значение1; значение2; ...). Для уточнения процентной ставки использовать метод подбора параметра.

ЗАДАНИЕ №3

ПС(ставка; кпер; плт; бс; тип) или ПЗ(ставка; кпер; плт; бс; тип)

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

Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48

плт -- выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.

бс -- требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость.

Пример

Результат получается отрицательный, поскольку он представляет деньги, которые необходимо выплатить, исходящий денежный поток. Если бы за аннуитет требовалось заплатить 60 000, эта инвестиция была бы не выгодной, так как приведенная стоимость (59 777,15) аннуитета меньше данной суммы.

· Примечание. Чтобы получить месячную процентную ставку, разделите годовую ставку на 12. Чтобы узнать количество выплат, умножьте количество лет кредита на 12.

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

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

Рассмотрим следующую задачу. Допустим, что у вас просят в долг 10000 рублей и обещают возвращать по 2000 рублей в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

В прводимом расчете в ячейку В5 введена формула

=ПЗ(В4; В2; -В3)

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

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вас просят дать в долг Р руб. и обещают возвращать по А руб. ежегодно в течении Н лет. При какой процентной ставке эта сделка имеет смысл.

Для решения задачи использовать функцию

(ПС(ставка; кпер; плт; бс; тип) либо ПЗ(ставка; срок; -ежегод.выплаты)). В функции сначала берется произвольная ставка, затем уточняется методом подбора параметра.

ЗАДАНИЕ № 4

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

Возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

ставка -- процентная ставка за период.

период -- это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «кпер».

кпер -- общее число периодов платежей по аннуитету.

Пример

	Описание
	Годовая процентная ставка
	Период, для которого требуется найти проценты
	Срок займа (в годах)
	Стоимость займа на текущий момент
	Описание (результат)
ПРПЛТ (A2/12; A3*3; A4; A5)	Выплаты по процентам за первый месяц на приведенных выше условиях (-22,41)
ПРПЛТ (A2; 3; A4; A5)	Выплаты по процентам за последний год на приведенных выше условиях (начисления процентов производятся ежегодно) (-292,45)

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

Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

ставка -- процентная ставка за период.

период -- задает период, значение должно быть в интервале от 1 до «кпер».

кпер -- общее число периодов платежей по аннуитету.

пс -- приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

бс -- требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т. е. для займа, например, значение бс равно 0.

тип -- число 0 или 1, обозначающее, когда должна производиться выплата.

Заметки

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Пример

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 1000000 рублей на срок 5 лет при годовой ставке 2%.

еxcel таблица формула

ежегодная плата вычисляется в ячейке В3 по формуле

=ППЛАТ(процент; срок; - размер ссуды)

За первый год плата по процентам в ячейке В7 вычисляется по формуле

=D6*$B$1

Основная плата $B$3-B7

Остаток долга в ячейке D7 вычисляется по формуле

=D6-C7

В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона В7:D7 вниз по столбцам.

Отметим, что основную плату и плату по процентам можно было непосредственно найти с помощью фукций ОСНПЛАТ и ПЛПРОУ соответственно.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить ежегодные основные платежи, плату по процентам, общую годовую выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок Н лет.

Использовать функции

(ПЛТ(ставка; кпер; пс; бс; тип), ПРПЛТ(ставка; период; кпер; пс; бс; тип), ОСПЛТ(ставка; период; кпер; пс; бс; тип))

ППЛАТ(ставка; срок; -ссуда), ПЛПРОЦ(ставка; период; срок; - ссуда), ОСНПЛАТ(ставка; период; срок; -ссуда).

Остаток долга = долг - ОСНПЛАТ

ЗАДАНИЕ № 5

КПЕР (ставка; плт; пс; бс; тип)

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

ставка -- процентная ставка за период.

плт -- выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

пс -- приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

бс -- требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бзс для займа равно 0).

Например, если вы берете в долг 1000 рублей при годовой ставке 1% и собираетесь выплачивать по 100 рублей в год, то число выплат вычисляется следующим образом:

КПЕР(1%; -100; 1000)

В результате получаем ответ: 11.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы берете в долг Р руб. под годовую ставку i% и собираетесь выплачивать по А руб. в год. Сколько лет займут эти выплаты? Найти 2 способами

1 - й способ - использовать функции

ПС(ставка; кпер; плт; бс; тип) либо ПЗ(ставка; срок; - ежегод.вклад)

2 - й способ - использовать функцию КПЕР(ставка; -ежег.вклад; ссуда)

ЗАДАНИЕ №6

БС(ставка; кпер; плт; пс; тип) или БЗ(ставка; кпер; плт; пс; тип)

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

ставка -- процентная ставка за период.

кпер -- это общее число периодов платежей.

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

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

тип -- число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.

Приведем пример использования функции БЗ. Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 рублей при годовой ставке 6%. Вы собираетесь вкладывать по 100 рублей в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

С помощью формулы

БЗ(6%/12; 12; -100; -1000; 1)

получаем ответ 2 301,4 руб.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы собираетесь вкладывать по А у. е. в течении Н лет при годовой ставке I%. Сколько денег будет на счете через n лет?

Использовать функцию

БС(ставка; кпер; плт; пс; тип)) либо БЗ(ставка; срок; - выплата)

ЗАДАНИЕ №7

Составить отчетную ведомость реализации товаров N магазинами с месяца А по месяц В. Найти место магазина по суммарной выручке (функция РАНГ()), среднюю выручку магазина в месяц (СРЗНАЧ(массив выручки по месяцам)), процент прибыли магазина в общей выручке(суммарную выручку магазина/суммарную выручку всех магазинов). Построить 2 диаграммы (1 - процент прибыли к общей выручке, 2 - объемы реализации продукции).

Стоимость товара для каждого магазина разная.

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

				Стоимость товаров	Объемы реализации (тыс. шт.)
		сентябрь		44,45,46,47,201,202	24,25,26,27,36,38
	сентябрь
				39,38,40,41,49, 36	25,27,28,22,23,29
		сентябрь
				201,205,305,205,11,14,22	70,71,72,73,74,99,85
	сентябрь
	сентябрь			420,430,401,400, 300
		сентябрь

Размещено на Allbest.ru

Подобные документы

Составление отчетной ведомости "Магазины" в Excel 2013. Работа с таблицами семейства Microsoft Office. Построение круговой диаграммы и гистограммы, графиков. Разработка процедур для табулирования функций. Программирование функций пользователя на VBA.

курсовая работа , добавлен 03.04.2014


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

контрольная работа , добавлен 05.07.2010


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

контрольная работа , добавлен 01.10.2011


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

курсовая работа , добавлен 21.11.2012


Вычисления в Excel. Формулы и функции: Использование ссылок и имен, перемещение и копирование формул. Относительные и абсолютные ссылки. Понятиеи и типы функций. Рабочая книга Excel. Связь между рабочими листами. Построение диаграмм в EXCEL.

лабораторная работа , добавлен 28.09.2007


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

курсовая работа , добавлен 25.04.2013


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

реферат , добавлен 03.02.2013


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

контрольная работа , добавлен 25.04.2013


Создание таблицы "Покупка товаров с предпраздничной скидкой". Понятие формулы и ссылки в Excel. Структура и категории функций, обращение к ним. Копирование, перемещение и редактирование формул, автозаполнение ячеек. Формирование текста функции в диалоге.

лабораторная работа , добавлен 15.11.2010


Функции, применяемые для вычисления параметров ссуды и анализа кредитов и вкладов, представленные в Microsoft Excel. Анализ экономической проблемы, для решения которой используется финансовая функция КПЕР. Описание примеров возможного ее использования.