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

Тема: АНАЛИЗ В MICROSOFT EXCEL ФИНАНСОВОГО СОСТОЯНИЯ ПРЕДПРИЯТИЯ НА ОСНОВАНИИ ДАННЫХ БАЛАНСА

Цель занятия. Изучение технологии расчета активов и пассивов баланса,

анализ финансового состояния предприятия в электронных таблицах.

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

Краткая справка. В структуре активов баланса выделяют две группы:

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

нематериальные активы; незавершенное строительство и долгосрочные инвестиции).

Порядок работы

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

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

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

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

Внеоборотные активы (ячейка ВЗ) = СУММ(В4:В7);

Запасы и прочие оборотные активы (В9) = СУММ (В10:В14);

Расчеты и денежные средства (В 16) = СУММ (В17:В19);

Оборотные активы (В8) = В9 + В15 + В16.

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

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

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

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

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

Рис. 16.1. Таблица расчета активов баланса

Порядок работы

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

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

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

Собственный капитал (В3) = СУММ (В4:В8);

Долгосрочные обязательства (В9) = СУММ (В10:В11);

Краткосрочная кредиторская задолженность(В 14) = = СУММ

(В15:В20);

Краткосрочные обязательства (В 12) = В13 + В14 + В21 + В22.

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

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

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

Рис. 16.2. Таблица расчета пассивов баланса

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

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

Порядок работы

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

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

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

Внеоборотные активы (В4) = 'активы'!ВЗ; Оборотные активы (В6) =

'активы'!В8; Баланс (В8) = В4 + В6; Внеоборотные активы, % к итогу (В5) =

В4/В8; Оборотные активы, % к итогу баланса (В7) = В6/В8;

Собственный капитал (В 10) = 'пассивы'!ВЗ; Долгосрочные обязательства (В 12) = 'пассивы'!В9; Краткосрочные обязательства (В 14) = 'пассивы'!В12;

Баланс (В16) = В10 + В12 + В14; Собственный капитал, % к итогу баланса

(В11) = В1 О/В 16; Долгосрочные обязательства, % к итогу баланса (В 13) =

В12/В16;

Рис. 16.3. Таблица

расчета агрегированного аналитического баланса

Краткосрочные обязательства, % к итогу баланса (В 15) = = В14/В16.

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

Рис. 16.4. Агрегированный аналитический баланс

4.Переименуйте Лист 3 электронной книги, присвоив ему имя

«Агрегированный баланс».

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

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

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

реформированном аналитическом балансе 1 рассчитываются чистые активы,

которые очищены от коммерческой и кредиторской задолженностей и используются при анализе эффективности деятельности предприятия.

Внеоборотные активы предприятия рассчитываются как

производственных внеоборотных активов и прочих внеоборотных активов.

Порядок работы

Уставный капитал оплаченный (В 16) = 'пассивы'!В4; Добавочный капитал

(В 17) = 'пассивы'!В5;

Резервы, прибыль, фонды (фактические), целевое финансирование (В 18) =

'пассивы'!В6 + 'пассивы'!В7; Собственный капитал (фактический) (В 15) = SUM (В16:В18); Долгосрочные финансовые обязательства(В20) =

'пассивы'!В9; Краткосрочные кредиты и займы (В21) = 'пассивы'!В12;

Финансовые обязательства (В 19) = SUM (В20:В21); ИТОГО ВЛОЖЕННЫЙ КАПИТАЛ (В22) = В15 + В19.

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

4.Переименуйте лист электронной , книгипрсвоив ему имя

«Реформированный баланс 1».

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

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

Краткая справка. Реформированный аналитический баланс2 предназначен для анализа финансовой устойчивости и платежеспо-

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

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

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

Порядок работы

1.На очередном свободном листе электронной книги«Анализ баланса»

«Реформированный баланс 1».

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

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

Запасы и прочие оборотные активы (В6) = = 'Реформир_баланс1!В8

(В5) = SUM(B6:B9); АКТИВЫ ВСЕГО (B10) = В4 + В5. Собственный капитал (фактический) (В 12) = = 'Реформирбаланс 1'! В15;

Долгосрочные финансовые обязательства(В 13) = =

'Реформир_баланс1'!В20; Краткосрочные финансовые обязательства (В 14) = 'пассивы'!В 12; ПАССИВЫ ВСЕГО (В15) = SUM(B12:B14). ЧИСТЫЙ ОБОРОТНЫЙ КАПИТАЛ (В17) = В5 - В14.

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

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

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

5.Переименуйте лист электронной , книгиприсвоив ему имя

«Реформированный баланс 2».

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

Рис. 16.8. Реформированный аналитический баланс 2

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

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

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

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

Отношение величины чистого оборотного капитала к сумме активов=

Чистый оборотный капитал(за вычетом краткосрочных обязательств) /

Всего активов.

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

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

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

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

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

Тема: ИСПОЛЬЗОВАНИЕ MICROSOFT EXCEL ДЛЯ СОЗДАНИЯ КОМПЛЕКСНЫХ ДОКУМЕНТОВ

Цель занятия. Закрепление и проверка навыков расчетов в электронных

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

Порядок работы

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

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

Выяснить, при каком значении зарплаты прибыль будет равна500 ООО руб.

(используйте режим Подбор параметра)

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

Расходы: всего = Прямые расходы + Прочие расходы; Прибыль = Доходы:

всего - Расходы: всего; Прибыль (у. е.) = Прибыль х Курс 1 у. е.

Задание 17.2. Фирма хочет накопить деньги для реализации нового проекта.

С этой целью в течение пяти лет она кладет на счет ежегодно по1250 долл. в

конце каждого года под8% годовых (рис. 17.2). Определить, какая сумма будет на счете фирмы к концу пятого. Построитьгда диаграмму по результатам расчетов. Выяснить, какую сумму надо ежегодно класть на счет,

чтобы к концу пятого года накопить 10 ООО долл.

Рис. 17.1. Исходные данные для задания 17.1

Рис. 17.2. Исходные данные для задания 17.2

Порядок работы

Формула для расчета:

Сумма на счете = D * ((1 + j)An - l)/j.

накопления к концу пятого года10 ООО $ используйте режим Подбор

параметра.

Вид экрана для расчета с использованием функции БС приведен на рис. 17.3.

Рис. 17.3. Использование функции БС для расчета

Задание 17.3. Фирма собирается инвестировать проект в течение трех лет.

Имеется два варианта инвестирования:

1-й вариант: под 12% годовых в начале каждого года;

2-й вариант: под 14% годовых в конце каждого . года Предполагается ежегодно вносить по500 000 руб. Определить, в

какую сумму обойдется проект (рис. 17.4)

Порядок работы

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

2 ООО ООО руб.

Для подсчета количества часов аренды автомобиля установите в ячейке

«Итого» числовой формат, рассчитайте разницу дат пользования(Дата по:

—Дата с:). Вы получите количество дней использования автомобиля. Для перевода количества дней использования автомобиля в часы произведите умножение на 24 (рис. 17.5).

Расчет суммы счета произведите по формуле

Всего = «Тариф за час» х Итого

Рис. 17.5. Конечный вид счета за аренду автомобиля

Задание 17.5. Произвести анализ прибыли кредитной организации за пять лет (в Microsoft Excel) по следующим данным

Таблица расчета состоит из следующих столбцов: Год, Доходы от кредитования, Доходы от валюты, Прочие доходы, Расходы от процентов по вкладам, Расходы на заработную плату, Итого по доходам, Итого по расходам, Прибыль за год.

Самостоятельно ввести данные во все столбцы, кроме итоговых

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

Итого по доходам = сумма по всем доходным статьям;

Текстовую часть документа создайте в текстовом редактореMicrosoft Word,

таблицу расчета стоимости ТМЦ для списания создайте Microsoftв Excel,

проведите расчеты и скопируйте в текстовый документ.

Наименование организации

__________________ ___ «Утверждаю» Руководитель организации

Отдел ___________

АКТ О ПОРЧЕ ТОВАРНО-МАТЕРИАЛЬНЫХ ЦЕННОСТЕЙ

от « ____________ » ___________200_г.

Комиссия в составе: председатель ______________ ___

на основании приказа от № составила

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

ИТОГО ПО АКТУ (прописью по розничным ценам или по ценам приобретения)

члены комиссии Председатель комиссии:

Члены комиссии: _____