Работа 2. Модель финансового плана
Очень часто требуется быстро подготовить финансовый план. Но в некоторых случаях может не быть под рукой важных данных по затратам. В лабораторной работе описывается простой, но эффективный метод, сокращающий время подготовки финансового плана с помощью задания производственных затрат на реализованную продукцию, общих и административных расходов, выраженных в процентах.
Технология работы
В ячейку А1 занесите функцию СЕГОДНЯ().
В ячейку D3 поместите заголовок таблицы – «Финансовый план».
В строку 5, начиная с ячейки С5, поместите название месяцев (достаточно первые два, далее воспользуйтесь функцией автозаполнения).
В ячейку О5 поместите заголовок «ИТОГО».
Статьи финансового плана запишите в колонке А, начиная с ячейки А7 и в соответствии с приложенной распечаткой таблицы.
В таблице используется небольшое число исходных данных (рис.7).
Установите на столбец В процентный формат и введите соответствующие проценты.
В рабочую область таблицы введем семь формул. Большинство из них будет копироваться. При вводе формул сравнивайте полученные числа с числами на рис 8.
Формулы итогов. Суммирование выполняется на столбцы C:N, начиная с 7-ой строки и до 26-ой.
Формула валовой прибыли. Для определения валовой прибыли производственные затраты на реализованную продукцию (С9) вычитаются из объема реализованной продукции (С7). Формула копируется по строке 11.
Формула определения дохода перед начислением налогов. Вычисляется как разность между прибылью от реализации продукции (С11)и суммарными издержками (С23). Формула копируется по строке 25.
Формула роста продаж. Первоначально в ячейку С7 занесено значение 10000. Это базисное число для определения объема реализованной продукции. Формула в ячейке D7 будет увеличивать базисное число на 10% (В26):
=C7*$B$26+C7
Формула вычисления производственных затрат на реализованную продукцию. Производственные затраты на реализованную продукцию составляют 30% объема реализации. В ячейку С9 заносим формулу:
=C7*$B9 и копируем на строку 9.
Формула вычисления фонда заработной платы. Начиная с июля заработную плату необходимо увеличить на 20%. Запишите формулу, учитывая значение этой константы непосредственно в формуле.
Формула расходов.
Отражает процент комиссионных по валовой прибыли соответствующего месяца: =C$11*$B16 и заносится в ячейку С16, а затем копируется на строку 16.
Формулы, записанные в строке 16, можно рассматривать как источник и распространить по всему блоку расходов (С17:N20).
Формулу для начисления налогов с фонда заработной платы =C15*$B$21 заносим в ячейку С21 и копируем на строку 21.
Постройте линейный график, используя данные о доходе работника до вычета налогов за каждый месяц года.
Рис. 7. Исходные данные к задаче «Финансовый план»
Рис. 8. Результаты решения задачи «Финансовый план»
Работа 3. Финансовый анализ в excel
EXCEL предоставляет большой спектр функций финансового анализа : от нахождения платы по процентам, амортизации оборудования, регулярных выплат по займу до оценки эффективности капиталовложений.
Финансовая функция ППЛАТ
Функция ППЛАТ вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке.
Синтаксис:
ППЛАТ( ставка; кпер; нз; бз; тип)
Аргументы:
Ставка – процентная ставка за период;
Кпер – общее число периодов выплат;
Нз – текущее значение, т.е. общая сумма, которую составят будущие платежи;
Бз – Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, то он полагается равным 0 (например, будущая стоимость займа = 0);
Тип – число 0 или 1, обозначающее, когда должна производиться выплата. Если тип =0 или опущен, то оплата производится в конце периода, если 1 – то в начале периода.
Очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и кпер. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12% / 12, а для задания аргумента кпер - 4*12. Если вы делаете ежегодные выплаты по тому же займу, то для задания аргумета ставка используйте 12%, а для задания аргумента кпер – 4.
Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое фунцией ППЛАТ значение на величину кпер. Интервал выплат – это последовательность постоянных денежных платежей, осуществляемых за непрерывный период. Например, заем под автомобиль или заклад являются интервалами выплат. В функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом –1000, если вы вкладчик, и аргументом 1000, если вы представитель банка.
Функция ПЗ
Функция ПЗ возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПЗ аналогична функции НПЗ. Основное различие между ними заключается в том, что функция ПЗ допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от функции НПЗ, денежные взносы в функции ПЗ должны быть постоянными на весь период инвестиции.
Синтаксис:
ПЗ(норма; кпер; выплата; бз; тип)
Финансовые функции ПЛПРОЦ И ОСНПЛАТ
Основную плату и плату по процентам можно было непосредственно найти с помощью функций ОСНПЛАТ() и ПЛПРОЦ(), соответственно.
Функция ПЛПРОЦ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис:
ПЛПРОЦ (ставка; период; кпер; нз; бз; тип)
Функция ОСНПЛАТ возвращает величину выплаты за данный период на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис:
Функция ОСНПЛАТ ( ставка; период; кпер; нз; бз; тип).
Аргументы:
Период - период, за который требуется найти прибыль (должен находиться в интервале от 1 до кпер)
Кпер - общее число периодов выплат;
Ставка - процентная ставка за период;
Нз - текущее значение, т.е. общая сумма, которую составят будущие платежи;
Бз - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бз опущен, он полагается равным 0 (например, будущая стоимость займа = 0);
Тип - число 0 или 1, обозначающие, когда должна производиться выплата. Если тип=0 или опущен, то оплата производится в конце периода, если = 1, то в начале периода.
Функция НПЗ
Функция НПЗ возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Чистый текущий объем вклада – это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения).
Синтаксис:
НПЗ( ставка; первое значение; второе значение; ……)
Аргументы:
Ставка - Процентная ставка за период
первое значение, второе значение….. – от 1 до 29 аргументов, представляющих расходы и доходы. Они должны быть равномерно распределены по времени и осуществляться в конце каждого периода. НПЗ использует порядок аргументов первое значение, второе значение….. для определения порядка поступлений и платежей.