Бизнес-анализ в MS Excel

1. ИТ в экономике

ЛЕКЦИЯ 1
БИЗНЕС-АНАЛИЗ В MS EXCEL
7/15/2018, слайд 1

2. ИТ в экономике

1.1. Бизнес-анализ в MS Excel
7/15/2018, слайд 2

3. Бизнес-анализ

Бизнес-анализ – это один из ключевых инструментов
управления, который позволяет получить достоверную
картину текущего состояния дел в компании по всем ее
направлениям, бизнес-подразделениям и функциональным
единицам.
Кроме того одной из важнейших задач бизнес-аналитики
является возможность оценивать и выявлять основные
закономерности развития компании, действительные
факторы и причины тех или иных изменений, успехов или
неудач в текущей и стратегической деятельности компании.
7/15/2018, слайд 3

4. Бизнес-анализ

Грамотное использование инструментов бизнес-анализа
позволяет также прогнозировать влияние различных
внешних и внутренних факторов на функционирование
компании.
От качества и эффективности процессов анализа
деятельности компании напрямую зависит достоверность
и
экономическая
обоснованность
принимаемых
управленческих решений и успех выбранной стратегии
развития организации и ее отдельных направлений.
7/15/2018, слайд 4

5. Задачи бизнес-аналитики

Основные задачи бизнес-аналитики:
обеспечение достоверной информации в нужном разрезе
для принятия управленческих решений;
определение уровня текущей эффективности бизнеспроцессов;
оценка потребностей бизнеса, текущих и стратегических
требований компании к эффективной организации бизнеспроцессов;
формирование стратегии развития компании в целом
или отдельных ее направлений;
7/15/2018, слайд 5

6. Задачи бизнес-аналитики

Основные задачи бизнес-аналитики:
определение путей достижения намеченных целей;
оценка необходимости изменений во внутренних
и внешних процессах компании;
оценка и контроль рисков развития;
обеспечение
координированной
работы
всех
подразделений компании.
7/15/2018, слайд 6

7. Инструменты для анализа данных

Бизнес-анализ с помощью стандартных продуктов Microsoft
7/15/2018, слайд 7

8. Инструменты для анализа данных

Бизнес-анализ с помощью стандартных продуктов Microsoft
7/15/2018, слайд 8

9. Бизнес-анализ в MS Excel

Бизнес-анализ в MS Excel (план лекций):
1. Итоговые таблицы. Сводные таблицы;
2. Финансовые функции и таблицы данных;
3. Решение оптимизационных задач. Использование
инструмента «Поиск решения»;
4. Проверка различных возможностей с помощью
сценариев;
5. Сортировка данных. Фильтрация данных;
6. Создание макросов.
7/15/2018, слайд 9

10. ИТ в экономике

1.2. Сортировка данных
7/15/2018, слайд 10

11. Сортировка данных

Сортировка данных помогает быстро визуализировать
данные и лучше понимать их, упорядочивать и находить
необходимую информацию.
Виды сортировки:
• простая сортировка (одноуровневая);
• сложная сортировка (многоуровневая).
Сортировать можно текстовые значения, числовые
значения, значения даты и времени;
по цвету ячейки, цвету шрифта, значку; с учетом регистра,
по части значений в столбе и т.д.
7/15/2018, слайд 11

12. Сортировка данных

Простая сортировка: Данные – Сортировка
7/15/2018, слайд 12

13. Сортировка данных

Стандартная ошибка при сортировке: сортировка по
одному выделенному столбцу
7/15/2018, слайд 13

14. Сортировка данных

Сложная сортировка: Данные – Сортировка
7/15/2018, слайд 14

15. Сортировка данных

Сложная сортировка, пример
7/15/2018, слайд 15

16. Сортировка данных

Настраиваемая сортировка по месяцам года
7/15/2018, слайд 16

17. ИТ в экономике

1.3. Итоговые таблицы
7/15/2018, слайд 17

18. Итоговые таблицы

Использование операции «Промежуточные итоги»
позволяет выполнить детальный анализ сводных показателей
для групп данных, объединенных каким-либо общим
признаком.
Подведение промежуточных итогов проводится в
предварительно отсортированной таблице.
7/15/2018, слайд 18

19. Итоговые таблицы

Создание итоговой таблицы –
Данные – Промежуточный итог
7/15/2018, слайд 19

20. Итоговые таблицы

Операция определяет вид «итоговой» функции.
Итоги выводятся под столбцом, который был выбран в
разделе «Добавить итоги по..».
7/15/2018, слайд 20

21. Итоговые таблицы

«Заменить текущие итоги» – заменяет все полученные
ранее итоги новыми. Если убрать флажок, то новые будут
вставлены под полученными ранее, а не заместят их. Это
позволяет выполнить процедуру «Итоги» несколько раз.
7/15/2018, слайд 21

22. Итоговые таблицы

Удаление итогов из таблицы
7/15/2018, слайд 22

23. Итоговые таблицы

Практическая работа 1
7/15/2018, слайд 23

24. ИТ в экономике

1.4. Сводные таблицы
7/15/2018, слайд 24

25. Сводные таблицы

Сводная таблица – это отчет, позволяющий просмотреть
данные в более удобном и понятном виде.
Сводные таблицы упрощают обобщение, анализ, изучение
и представление данных.
Их можно создавать с помощью Мастера сводных таблиц
и быстро настраивать в зависимости от того, как вы хотите
отобразить результаты.
Также можно создавать сводные диаграммы на основе
сводных таблиц, которые будут автоматически обновляться
при их изменении.
7/15/2018, слайд 25

26. Сводные таблицы

С помощью сводных таблиц можно сделать практически
любой «разрез» таблицы, получить итоги по любым
данным.
Сводная таблица во многом объединяет возможности
других обобщающих инструментов, таких как Итоги,
Фильтр, Консолидация.
С помощью сводной таблицы можно произвольным
образом изменить структуру исходных данных, располагая
заголовки строк и столбцов удобным образом, а также
получить все необходимые промежуточные итоги и
сортировки.
7/15/2018, слайд 26

27. Сводные таблицы

Макет сводной таблицы
7/15/2018, слайд 27

28. Сводные таблицы

Создание сводной таблицы –
Вставка – Сводная таблица
7/15/2018, слайд 28

29. Сводные таблицы

Макет сводной таблицы
7/15/2018, слайд 29

30. Сводные таблицы

Практическая работа 1
7/15/2018, слайд 30

31. Сводные таблицы

Выбор операции в сводной таблице
7/15/2018, слайд 31

32. Сводные таблицы

Добавление структуры в сводную таблицу
7/15/2018, слайд 32

33. Сводные таблицы

Добавление структуры в сводную таблицу
Переименовываем
группу
7/15/2018, слайд 33

34. Сводные таблицы

Практическая работа 1
7/15/2018, слайд 34

35. Сводные таблицы

Фильтрация в сводных таблицах
7/15/2018, слайд 35

36. Сводные таблицы

Изменение данных в исходной таблице не влечет
автоматической реакции сводной таблицы.
Для выполнения перерасчета необходимо выбрать
кнопку Обновить из группы Данные (вкладка Анализ).
7/15/2018, слайд 36

37. ИТ в экономике

1.5. Финансовые функции и таблицы данных
7/15/2018, слайд 37

38. Финансовые функции и таблицы данных

Табличный процессор MS Excel может решать довольно
сложные финансовые задачи. Данное направление расчетов
возложено на финансовые функции.
В
финансовых
вычислениях
поступления задаются
положительными
числами, выплаты –
отрицательными.
7/15/2018, слайд 38

39. Финансовые функции и таблицы данных

Задача (практическая работа 2):
Составить схему погашения займа в 70 000 руб., выданного сроком
на 3 года под 17% годовых.
Финансовая модель должна содержать:
1. Сумму займа на начало погашения;
2. Общую сумму платежа за месяц;
3. Сумму платежа по процентам за каждый период;
4. Сумму основного платежа по займу в каждом периоде;
5. Сумму займа на конец периода (остаток долга);
6. Долю процентов в общей сумме платежа;
7. Общую сумму платежей по процентам нарастающим итогом.
7/15/2018, слайд 39

40. Финансовые функции и таблицы данных

Входные данные:
7/15/2018, слайд 40

41. Финансовые функции и таблицы данных

Финансовая функция ПЛТ:
Платеж по инвестиции (займу) на условиях постоянства сумм выплат (равными
долями) и постоянства процента. Сумма процентов делится на количество платежей.
Платеж производится в каждый период, состоит из основного платежа и платежа
по процентам.
Аргументы функции:
Кпер – число периодов платежей по кредиту (если платеж осуществляется
ежемесячно, например на 5 лет, то следует написать 5*12).
Ставка – процентная ставка (если платеж осуществляется ежемесячно и если
ставка 17% годовых, то следует написать или 17%/12 или 0,17/12).
Пс – сумма на текущий момент.
Бс (может не заполняться) – будущая стоимость.
Тип – число 0 – оплата в конце периода, число 1 – оплата в начале периода (по
умолчанию – 0).
7/15/2018, слайд 41

42. Финансовые функции и таблицы данных

Решение задачи в Excel:
7/15/2018, слайд 42

43. Финансовые функции и таблицы данных

Финансовая модель (ежегодные выплаты)
Сумма % + Сумма осн.
долга
Сумма займа + Сумма
осн. долга
Сумма % / Сумма
платежа, ежегодная
Общая сумма
(предыдущая) +
Сумма %
7/15/2018, слайд 43

44. Финансовые функции и таблицы данных

Финансовая модель (ежемесячные выплаты)
7/15/2018, слайд 44

45. Финансовые функции и таблицы данных

Финансовая модель
равными долями)
(ежегодные
выплаты,
платеж
7/15/2018, слайд 45

46. Финансовые функции и таблицы данных

Финансовая модель (ежемесячные выплаты, платеж
равными долями)
7/15/2018, слайд 46

47. Финансовые функции и таблицы данных

Задача (практическая работа 2):
1 Определите общую сумму платежей по процентам при
разных ставках годового процента (таблица данных).
2 Определите сумму основного платежа по займу за
последний период при разных ставках годового процента
и разных сроках погашения (таблица данных).
7/15/2018, слайд 47

48. Финансовые функции и таблицы данных

1 Определите общую сумму платежей по процентам при
разных ставках годового процента (таблица данных).
= Сумма платежа,
ежегодная
Разные ставки годового
процента
7/15/2018, слайд 48

49. Финансовые функции и таблицы данных

1 Определите общую сумму платежей по процентам при
разных ставках годового процента (таблица данных).
Годовой процент
7/15/2018, слайд 49

50. Финансовые функции и таблицы данных

2 Определите сумму основного платежа по займу за
последний период при разных ставках годового процента
и разных сроках погашения (таблица данных).
7/15/2018, слайд 50

51. Источники

1. Бизнес-анализ с помощью стандартных продуктов Microsoft:
http://download.microsoft.com/documents/rus/business/enterprise/s
eminars/BI.pdf
2. Сайт компании Норбит, эксперта на рынке ИТ-консалтинга:
http://www.norbit.ru/zadachi/biznes-analiz/
3. Справка
и
обучение
https://support.office.com/ru-ru/article/
Microsoft
Office:
7/15/2018, слайд 51