Финансовый анализ в Excel с примером

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

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

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

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

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

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

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

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

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

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

Баланс.

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

  1. Представим значения на начало и на конец года в виде относительных величин. Формула: =B4/$B$14 (отношение значения на начало года к величине баланса на начало года). По такому же принципу составляем формулы для «конца года» и «пассива». Копируем на весь столбец. В новых столбцах устанавливаем процентный формат.
  2. Значения на начало и на конец года.
  3. Проанализируем динамику изменений в абсолютных величинах. Делаем дополнительный расчетный столбец, в котором отразим разницу между значением на конец года и на начало.
  4. Значения на начало и на конец года.
  5. Покажем изменения в относительных величинах. В новом расчетном столбце найдем разницу между относительными показателями конца года и начала.
  6. Изменения.
  7. Чтобы найти динамику в процентах к значению показателя начала года, считаем отношение абсолютного показателя к значению начала года. Формула: =F4/B4. Копируем на весь столбец.
  8. Динамика в процентах.
  9. По такому же принципу находим динамику в процентах для значений конца года.
Динамика на конец года.

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

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

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

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

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

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

  1. СРЗНАЧ – Среднее значение – рассчитывает выборочное или генеральное среднее. Аргумент функции – набор чисел, указанный в виде ссылки на диапазон ячеек.
  2. Функция СРЗНАЧ.
  3. ДИСП – для вычисления выборочной дисперсии (без учета текстовых и логических значений); ДИСПА – учитывает текстовые и логические значения. ДИСПР – для вычисления генеральной дисперсии (ДИСПРА – с учетом текстовых и логических параметров).
  4. Функция ДИСП.
  5. Для нахождения квадратного корня из дисперсии – СТАНДОТКЛОН (для выборочного стандартного отклонения) и СТАНДОТКЛОНП (для генерального стандартного отклонения).
  6. Функция СТАНДОТКЛОН.
  7. Для нахождения моды совокупности данных применяется одноименная функция. Разделяет диапазон данных на две равные по числу элементов части МЕДИАНА.
  8. Функция МЕДИАНА.
  9. Размах варьирования – это разность между наибольшим и наименьшим значением совокупности данных. В Excel можно найти следующим образом:
  10. Размах варьирования.
  11. Проверить отклонение от нормального распределения позволяют функции СКОС (асимметрия) и ЭКСЦЕСС. Асимметрия отражает величину несимметричности распределения данных: большая часть значений больше или меньше среднего.
Функция СКОС.

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

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

Функция ЭКСЦЕСС.

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

Рассмотрим, как для целей статистики применяется надстройка «Пакет анализа».

Задача: Сгенерировать 400 случайных чисел с нормальным распределением. Оформить полный перечень статистических характеристик и гистограмму.

  1. Открываем меню инструмента «Анализ данных» на вкладка «Данные» (если данный инструмент недоступен, то нужно подключить настройку анализа ). Выбираем строку «Генерация случайных чисел».
  2. Анализ данных.
  3. Вносим в поля диалогового окна следующие данные:
  4. Генерация случайных чисел.
  5. После нажатия ОК:
  6. Результат генерации.
  7. Зададим интервалы решения. Предположим, что их длины одинаковые и равны 3. Ставим курсор в ячейку В2. Вводим начальное число для автоматического составления интервалов. К примеру, 65. Далее нужно сделать доступной команду «Заполнить». Открываем меню «Параметры Excel» (кнопка «Офис»). Выполняем действия, изображенные на рисунке:
  8. Настройка панели БД.
  9. На панели быстрого доступа появляется нужная кнопка. В выпадающем меню выбираем команду «Прогрессия». Заполняем диалоговое окно. В столбце В появятся интервалы разбиения.
  10. Прогрессия.
  11. Первый результат работы:
  12. Первый результат.
  13. Снова открываем список инструмента «Анализ данных». Выбираем «Гистограмма». Заполняем диалоговое окно:
  14. Гистограмма.
  15. Второй результат работы:
  16. Второй результат.
  17. Построить таблицу статистических характеристик поможет команда «Описательная статистика» (пакет «Анализ данных»). Диалоговое окно заполним следующим образом:
Описательная статистика.

После нажатия ОК отображаются основные статистические параметры по данному ряду.

Третий результат.

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