Практическая работа в Excel: «Подбор параметра и экономические расчеты»

Практическая работа в Excel: «Подбор параметра и экономические расчеты»

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Инструменты Excel 2016 для анализа данных:

• Подбор параметра - нахождение входного значения для нужного результата

• Связи между файлами - создание формул, ссылающихся на другие книги Excel

• Консолидация данных - объединение информации из нескольких источников

• Экономические функции - финансовые расчеты для бизнес-анализа

ПРАКТИЧЕСКАЯ РАБОТА

Цель: Освоить инструменты анализа данных в Excel 2016 для экономических расчетов

Время выполнения: 90-120 минут

ЧАСТЬ 1: ПОДБОР ПАРАМЕТРА ДЛЯ КРЕДИТНЫХ РАСЧЕТОВ

Задание 1.1: Создание таблицы расчета аннуитетного платежа

Пошаговая инструкция:

1. Создайте новый файл Excel 2016

2. Сохраните как «Экономические_расчеты_Фамилия.xlsx»

3. На Листе1 создайте таблицу:

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

• B5: =ПЛТ(B3/12; B4; -B2)

• B6: =B5*B4

• B7: =B6-B2

Пояснение: Функция ПЛТ рассчитывает ежемесячный платеж по аннуитетной схеме.

Задание 1.2: Использование Подбора параметра

Проблема: Клиент может платить только 25 000 руб/мес. Какую максимальную сумму кредита он может получить?

Инструкция для Excel 2016:

1. Выделите ячейку B5 (ежемесячный платеж)

2. Перейдите на вкладку «Данные»

3. В группе «Работа с данными» нажмите «Анализ "что если"»

4. Выберите «Подбор параметра»

5. Заполните диалоговое окно:

   • Установить в ячейке: B5

   • Значение: 25000

   • Изменяя ячейку: B2

6. Нажмите ОК

Результат: Excel найдет максимальную сумму кредита ≈ 1 120 000 руб.

ЧАСТЬ 2: ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА

Задание 2.1: Расчет точки безубыточности

Инструкция:

1. Создайте Лист2, переименуйте в «Точка безубыточности»

2. Создайте таблицу:

Формулы:

• B5: =B3-B4

• B6: =B2/B5

• B7: =B6*B3

Задание 2.2: Определение целевой цены

Задача: Компания хочет выйти на точку безубыточности при продаже 300 единиц. Какую цену установить?

Инструкция:

1. Данные → «Анализ "что если"» → «Подбор параметра»

2. Заполните:

   • Установить в ячейке: B6

   • Значение: 300

   • Изменяя ячейку: B3

3. Нажмите ОК

Результат: Цена должна быть ≈ 3 167 руб.

ЧАСТЬ 3: СВЯЗИ МЕЖДУ ФАЙЛАМИ

Задание 3.1: Создание связанных файлов

Инструкция:

1. Создайте новый файл «Филиал_Москва.xlsx»

2. Заполните таблицу продаж:

1. Сохраните и закройте файл

2. Вернитесь в основной файл

3. Создайте Лист3 «Консолидация_продаж»

Задание 3.2: Создание внешних ссылок

Инструкция:

1. В ячейке A2 введите: ='C:\[Филиал_Москва.xlsx]Лист1'!A2
   Примечание: Укажите правильный путь к вашему файлу

2. В ячейке B2: ='C:\[Филиал_Москва.xlsx]Лист1'!B2

3. Скопируйте формулы вниз

Пояснение: При изменении данных в файле филиала, основной файл автоматически обновится при открытии.

ЧАСТЬ 4: КОНСОЛИДАЦИЯ ДАННЫХ

Задание 4.1: Ручная консолидация по филиалам

Инструкция:

1. Создайте таблицы для трех филиалов на отдельных листах:

Лист «Филиал_Север»:

Лист «Филиал_Юг»:

1. На листе «Итоги» создайте сводную таблицу:

Формулы:

• Для «Итого»: =СУММ(B2:C2)

Задание 4.2: Автоматическая консолидация

Инструкция для Excel 2016:

1. На листе «Авто_консолидация» поставьте курсор в A1

2. Данные → «Консолидация»

3. В диалоговом окне:

   • Функция: «Сумма»

   • Ссылка: Выберите диапазон с листа «Филиал_Север»

   • Нажмите «Добавить»

   • Выберите диапазон с листа «Филиал_Юг»

   • Нажмите «Добавить»

   • Отметьте «Подписи верхней строки» и «Значения левого столбца»

4. Нажмите ОК

ЧАСТЬ 5: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ

Задание 5.1: Анализ инвестиционного проекта

Инструкция:

1. Создайте Лист «Инвестиции»

2. Постройте таблицу:

Формулы:

• B6: =ЧПС(B5;B3;B3;B3;B3;B3)-B2

• B7: =ВСД({-B2;B3;B3;B3;B3;B3})

Задание 5.2: Оптимизация инвестиционных параметров

Задача: Какая минимальная годовая доходность сделает проект безубыточным (NPV = 0)?

Инструкция:

1. Данные → «Анализ "что если"» → «Подбор параметра»

2. Заполните:

   • Установить в ячейке: B6

   • Значение: 0

   • Изменяя ячейку: B3 (годовой денежный поток)

3. Нажмите ОК

Результат: Минимальный денежный поток ≈ 1 387 000 руб.

ЧАСТЬ 6: ПРАКТИЧЕСКИЙ КЕЙС - БИЗНЕС-ПЛАН КАФЕ

Задание 6.1: Расчет окупаемости бизнеса

Инструкция:

1. Создайте Лист «Бизнес_план_кафе»

2. Заполните таблицу:

Формулы:

• B8: =B3*B4*B5

• B9: =B8*(1-B6)-B7

• B10: =B2/B9

Задание 6.2: Анализ чувствительности

Вопрос: Сколько нужно клиентов в день для окупаемости за 12 месяцев?

Инструкция:

1. Подбор параметра для ячейки B10

2. Значение: 12

3. Изменяя ячейку: B4

Результат: Необходимо 120 клиентов в день

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Какие типы экономических задач решаются с помощью Подбора параметра?

2. В чем преимущества использования связей между файлами?

3. Когда следует использовать консолидацию данных вместо ручного суммирования?

4. Как проверить корректность экономических расчетов?

КРИТЕРИИ ОЦЕНКИ

«Отлично» (5 баллов):

• Все задания выполнены полностью и корректно

• Формулы работают без ошибок

• Созданы рабочие связи между файлами

• Экономические расчеты логичны и точны

«Хорошо» (4 балла):

• Выполнены основные задания

• Есть незначительные ошибки в формулах

• Связи между файлами требуют доработки

«Удовлетворительно» (3 балла):

• Созданы базовые таблицы

• Подбор параметра работает с помощью

• Отсутствуют сложные экономические расчеты

«Неудовлетворительно» (2 балла):

• Большинство заданий не выполнено

• Формулы содержат грубые ошибки

• Отсутствует понимание принципов работы инструментов