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

Тема: Вычислительные функции табличного процессора Microsoft Excel для финансового анализа.

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

Вид работы: групповой

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

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

Исходные данные представлены на рисунке 1, результаты работы – на рисунке 5, 7, 10.

Ход работы

1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке Microsoft Office выполните Пуск – Все программы – Microsoft Office Excel).

Рисунок 1 – Исходные данные для задания 1

2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. руб.)», начиная с ячейки A1.

3. На третьей строке введите названия колонок таблицы – «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполните таблицу исходными данными согласно заданию 1.

Краткая справка: Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки).

4. Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход – Расход,

для этого в ячейке D4 наберите формулу = B4 – C4.

Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).

5. Для ячеек с результатом расчетов задайте формат – «Денежный» с выделением отрицательных чисел красным цветом (рис. 2) (Главная – Выравнивание – вкладка Число – формат Денежный – отрицательные числа – красные. Число десятичных знаков задайте равное 2).

Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.

Рисунок 2 – Задание формата отрицательных чисел красным цветом

6. Рассчитайте среднее значение Дохода и Расхода, пользуясь мастером функций (кнопка ). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функций СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего значения (В11), запустите мастер функций (Формула – Вставить функцию - категория Статистические – СРЗНАЧ) (Рис. 3). В качестве первого числа выделите группу ячеек с данными для расчета среднего значения – В4:В10.

Аналогично рассчитайте «Среднее значение» расхода.

Рисунок 3 – Выбор функции расчета среднего значения СРЗНАЧ

7. В ячейке D3 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования () на панели инструментов или функцией СУММ (Формула – Вставить функцию - категория Математические – СУММ).В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10 (рис. 4).

Рисунок – 4. Задание интервала при суммировании функцией СУММ

8. Произведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Главная –Выравнивание – вкладка Выравнивание - отображение Объединение ячеек. Задайте начертание шрифта – полужирное; цвет – по вашему усмотрению. Конечный вид таблицы приведен на рис. 5.

Рисунок 5 – Таблица расчета финансового результата (задание 1)

9. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели.

Для этого выделите интервал ячеек с данными Дни недели и Финансовый результат и выберите команду Вставка – Диаграммы – Линейчатая (Рис. 6).

Рисунки 6 – Конечный вид диаграммы задания 1

10. Произведите фильтрацию значений дохода, превышающих 4200 руб.

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

Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой Главная – Сортировка и фильтр - Фильтр. В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся значений этого поля. Выберите команду для фильтрации – Условие (рис. 7).

Рисунок 7 – Выбор варианта фильтрации

В открывшемся окне Пользовательский автофильтр задайте «Больше 4200» (рис 8).

Произойдет отбор данных по заданному условию.

Проследите, как изменились вид таблицы (рис. 9) и построения диаграмма.

Рисунок 8 – Задание условия фильтрации

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

Рисунок 9 - Вид таблицы после фильтрации данных

Задание 2. Заполнить таблицу, произвести расчеты, выделите минимальную и максимальную суммы покупки (рис. 10); по результатам расчета построить круговую диаграмму суммы продаж.

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

Сумма = Цена*Количество;

Всего = сумма значений колонки «Сумма».

Рисунок 10 – Исходные данные для задания 2

Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАК (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки E3:E10).

Задание 3. Заполнить ведомость учета брака, произвести расчеты, выделить минимальную, максимальную и среднюю сумму брака, а также средний процент брака; произвести фильтрацию данных по умолчанию процента брака; произвести фильтрацию данных по умолчанию процента брака < 9%, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 11).

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

Сумма брака = Процент брака * Сумма затрат.

Рисунок 11 – Исходные данные для задания 3

Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Главная – Выравнивание – вкладка Число/формат – Процентный).

Задание 4. Заполнить таблицу анализа продаж, произвести расчет, выделить минимальную и максимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9300 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукции (рис. 12).

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

Всего = Безналичные платежи + Наличные платежи;

Выручка от продажи = Цена * Всего.