ПР_Анализ в MS Excel финансового состояния предприятия на основании данных баланса

Практическая работа №14

Тема: Анализ в MS Excel финансового состояния предприятия на основании данных баланса.

Цель: - изучение технологии расчета активов и пассивов баланса, анализ финансового состояния предприятия в электронных таблицах.

Вид работы: фронтальный

Время выполнения: 2 часа

Задания к практической работе

Задание 1. Создать таблицу активов аналитического баланса.

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

Ход работы

1.   Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2.   На Листе 1 создайте таблицу активов баланса по образцу (Рис. 1).

Рисунок 1 – Таблица расчета активов баланса

3.   Произведите расчеты в таблице активов баланса в столбце B.

Краткая справка. Формулы для расчета в ячейках столбца B:

Внеоборотные активы (ячейка B3) = СУММ(B4:B7);

Запасы и прочие оборотные активы (B9) = СУММ(B10:B14);

Расчеты и денежные средства (B16) = СУММ (B17:B19);

Оборотные активы (B8) = B9 + B15 + B16.

4.   Скопируйте набранные формулы в столбец C.

5.   Переименуйте Лист 1 электронной книги, присвоив ему имя «Активы».

6.   Сохраните созданную электронную книгу с именем «Анализ баланса».

Задание 2. Создать таблицу пассивов аналитического баланса.

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

Ход работы

1.   На Листе 2 файла «Анализ баланса» создайте таблицу пассивов баланса по образцу (рис. 2).

Рисунок 2 – Таблица расчета пассивов баланса

2.   Произведите расчеты в таблице пассивов баланса в столбце B.

Краткая справка. Формулы для расчета в ячейках столбца B:

Собственный капитал (B3) = СУММ(B4:B8);

Долгосрочные обязательства (B9) = СУММ (B10:B11);

Краткосрочная кредиторская задолженность (B14) = СУММ(B15:B20);

Краткосрочные обязательства (B12) = B13 + B14 + B21 + B22.

Скопируйте набранные формулы в столбец C.

3.   Переименуйте Лист 2 электронной книги, присвоив ему имя «Пассивы».

4.   Сохраните созданную электронную книгу.

Задание 3. Создать таблицу агрегированного аналитического баланса.

Данные с листов «Активы» и «Пассивы» позволяют рассчитать агрегированный аналитический баланс.

Ход работы

1.   На Листе 3 создайте таблицу агрегированного аналитического баланса по образцу (Рис. 3).

Рисунок 3 – Таблица расчета агрегированного аналитического баланса

2.   Произведите расчеты в таблице агрегированного аналитического баланса.

Формулы для расчета в ячейках столбца B:

Внеоборотные активы (B4) = Активы!B3;

Оборотные активы (B6) = Активы!B8

Баланс (B8) = B4 + B6;

Внеоборотные активы, % к итогу (B5) = B4/B8;

Оборотные активы, % к итогу баланса (B7) = B6/B8;

Собственный капитал (B10) = Пассивы!B3;

Долгосрочные обязательства (B12) =Пассивы!B9;

Краткосрочные обязательства (B14) =Пассивы!B12;

Баланс (B16) = B10+B12+B14;

Собственный капитал, % к итогу баланса (B11) = B10/B16;

Долгосрочные обязательства, % к итогу баланса (B13) = B12/B16;

Краткосрочные обязательства, % к итогу баланса (B15) = B14/B16.

3.   Скопируйте набранные формулы в столбец C. Ваша электронная таблица примет вид как на рис. 4.

Рисунок 4 – Агрегированный аналитический баланс

4.   Переименуйте Лист 3 электронной книги, присвоив ему имя «Агрегированный баланс».

5.   Сохраните созданную электронную книгу.

Задание 4. Создать таблицу расчета реформированного аналитического баланса 1

Краткая справка. Реформированный аналитический баланс 1 предназначен для анализа эффективности деятельности предприятия. В нем активы предприятия собраны в две группы: производственные и непроизводственные активы.

Обязательства предприятия также разделены на две группы: финансовые и коммерческие обязательства. При этом коммерческие обязательства переносятся из пассивов в активы со знаком «-». Таким образом, в реформированном аналитическом балансе 1 рассчитываются чистые активы, которые очищены от коммерческой и кредиторской задолженностей и используются при анализе эффективности деятельности предприятия. Внеоборотные активы предприятия рассчитываются как сумма производственных внеоборотных активов и прочих внеоборотных активов.

Ход работы

1.   На Листе 4 электронной книги «Анализ баланса» создайте таблицу реформированного аналитического баланса 1 по образцу (рис.5).

Рисунок – 5. Таблица реформированного аналитического баланса 1

2.   Произведите расчеты в таблице реформированного аналитического баланса 1. Используйте данные листов «Активы» и «Пассивы».

Формулы для расчета в ячейках столбца B:

Производственные внеоборотные активы (B5) =Активы!B5+Активы!B6+Активы!B15;

Прочие внеоборотные активы (B6) =Активы!B4+Активы!B7;

Внеоборотные активы (B4)=B5 + B6;

Запасы и прочие оборотные активы (B8) =Активы!B9;

Краткосрочная дебиторская задолженность (B9) =Активы!B17;

Денежные средства и краткосрочные вложения (B10) =Активы!B18+Активы!B19;

Кредиторская задолженность (B11) = - (Пассивы!B14+Пассивы!B21);

Чистый оборотный капитал (B7) =СУММ(B8:B11);

ИТОГО ЧИСТЫЕ АКТИВЫ (B12) = B4 + B7.

Уставной капитал оплаченный (B16) = Пассивы!B4;

Добавочный капитал (B17) = Пассивы!B5;

Резервы, прибыль, фонды (фактические), целевое финансирование (B18) =Пассивы!B6+Пассивы!B7;

Собственный капитал (фактический) (B15) =СУММ(B16:B18);

Долгосрочные финансовые обязательства (B20) =Пассивы!B9;

Краткосрочные кредиты и займы (B21) =Пассивы!B12;

Финансовые обязательства (B19) =СУММ(B20:B21);

ИТОГО ВЛОЖЕННЫЙ КАПИТАЛ (B22) = B15 + B19.

3.   Скопируйте набранные формулы в столбец C. Ваша электронная таблица примет вид как на рис. 6.

Рисунок – 6. Реформированный аналитический баланс 1

4.   Переименуйте лист электронной книги, присвоив ему имя «Реформированный баланс 1».

5.   Сохраните созданную электронную книгу.

Задание 5. Создать таблицу расчета реформированного аналитического баланса 2.

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

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

Ход работы

1.   На Листе 5 электронной книги «Анализ баланса» создайте таблицу реформированного аналитического баланса 2 по образцу (рис.7).

Рисунок 7 – Таблица реформированного аналитического баланса 2

2.   Произведите расчеты реформированного аналитического баланса 2. Используйте данные листов «Активы», «Пассивы» и «Реформированный баланс 1».

Формулы для расчета в столбце B:

Внеоборотные активы (B4) = 'Реформированный баланс 1'!B4

Запасы и прочие оборотные активы (B6) = 'Реформированный баланс 1'!B8;

Краткосрочная дебиторская задолженность (B7) = 'Реформированный баланс 1'!B9;

Краткосрочные финансовые вложения (B8) =Активы!B18;

Денежные средства (B9) =Активы!B19;

Оборотные активы (B5) =СУММ(B6:B9);

АКТИВЫ ВСЕГО (B10) =B4+B5;

Собственный капитал (фактический) (B12) = 'Реформированный баланс 1'!B15;

Долгосрочные финансовые обязательства (B13) = 'Реформированный баланс 1'!B20;

Краткосрочные финансовые обязательства (B14) =Пассивы!B12;

ПАССИВЫ ВСЕГО (B15) =СУММ(B12:B14);

ЧИСТЫЙ ОБОРОТНЫЙ КАПИТАЛ (B17) =B5-B14.

Таблица 8 – Реформированный аналитический баланс 2

3.   Скопируйте набранные формулы в столбец C.

4.   Ваша электронная таблица примет вид как на рис. 8.

5.   Переименуйте лист электронной книги, присвоив ему имя «Реформированный баланс 2».

6.   Сохраните созданную электронную книгу.

Задание 6. Рассчитать показатели финансовой устойчивости предприятия на основе данных таблицы «Реформированный баланс 2».

Результаты расчетов оформить в виде таблицы.

Краткая справка. Формулы для расчета:

Уровень собственного капитала = Собственный капитал/Всего пассивов;

Коэффициент покрытия внеоборотных активов собственным капиталом = Собственный капитал/Внеоборотные активы;

Коэффициент покрытия внеоборотных активов собственным и долгосрочным заемным капиталом = (Собственный капитал + Долгосрочные обязательства)/Внеоборотные активы;

Отношение величины чистого оборотного капитала к сумме активов = Чистый оборотный капитал (за вычетом краткосрочных обязательств)/Всего активов.

Задание 7. Рассчитать параметры ликвидации предприятия на основе данных таблицы «Реформированный баланс 2». Результаты расчетов оформить в виде таблицы.

Краткая справка. Формулы для расчета:

Коэффициент покрытия = Оборотные активы/Краткосрочные обязательства;

Промежуточный коэффициент покрытия = (Оборотные активы – Запасы и прочие оборотные активы)/Краткосрочные обязательства;

Абсолютная ликвидность = Денежные средства/Краткосрочные обязательства.