Как работают таблицы данных в Excel.

Упражнения к книге "Финансовое моделирование с Виктором Рыбцевым"

Курс Финансовое моделирование в Excel.

Очно и онлайн. В группах, индивидуально и в корпоративном формате.

Как работают таблицы данных в Excel. Теория

В Excel есть инструменты, значительно упрощающие проведение анализа чувствительности и сценарного анализа. Один из них – это таблицы данных (data tables).

Таблица данных позволяет узнать, какие именно значения принимает показатель (например, прибыль, EBITDA, IRR, срок окупаемости или любой другой показатель) или группа показателей, если в исходных данных будут использоваться другие значения. Такую таблицу можно построить полностью в ручном режиме. Для этого нужно в исходные данные подставить альтернативные прогнозы и выписать результат. Затем в исходные данные необходимо подставить еще один вариант альтернативных прогнозов и выписать результат.

Таблица данных (data table) подставит все варианты альтернативных прогнозов в автоматическом режиме.

Пошаговая инструкция для простой таблицы данных:

1. Создайте завершенную финансовую модель (или блок расчетов). В этом блоке расчетов должны быть разделены ячейки с исходными данными и ячейки с окончательными расчетами.

2. Выберите один показатель исходных данных, для разных значений которого вы хотите увидеть результаты расчетов в одной таблице (далее Анализируемый параметр). Например, Анализируемый параметр – цена за штуку, результаты:

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

3. На том же листе, где содержится ячейка с прогнозным Анализируемым параметром (например, ценой), создайте такую таблицу:

– на пересечении первой строки и первого столбца такой таблицы оставьте путсую ячейку;

– в первой строке, начиная со второго столбца, последовательно укажите все значения Анализируемого параметра, которые могут существовать (например, в одной ячейке цена будет 100р/шт, в следующей – 110р/шт, затем – 120р/шт);

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

4. Выделите созданную таблицу целиком (начиная с ячейки, оставшейся пустой, включая все созданные варианты цен и все добавленные формулы-ссылки).

5. На вкладке “Данные” нажмите кнопку “Анализ что-если” (What if analysis) и выберите “Таблица данных” (Data table).

6. В открывшемся диалоговом окне в поле “Подставлять значения по столбцам” выберите ячейку, в которой находится основное значение Анализируемого параметра для первоначальных расчетов, то есть исходная цена.

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

7. Нажмите “Ок”.

Если в настройках Excel отключен автоматический расчет таблицы данных, нажмите F9 (или Fn+F9 в зависимости от настроек клавиатуры).

Как работают таблицы данных в Excel. Пример

Скачайте файл-упражнение здесь:

Войдите или зарегистрируйтесь для просмотра этого контента.

Как работают файлы-упражнения

В каждом файле-упражнении есть теория и задания.

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

Задания в бесплатных файлах-упражнениях загружаются с сайта BRP ADVICE.

Для работы с файлами-упражнениями убедитесь в наличии сети Интернет и предоставьте файлу доступ к внешним данным.

Используйте расширенные версии упражнений, чтобы лучше разобраться в теме и работать без подключения к внешним данным >>>>

BRP ADVICE в цифрах

2012

компания BRP ADVICE зарегистрирована 23.08.2012

Более 500

Более 500 человек прослушали курсы Финансовое моделирование BRP ADVICE.

Более 300

Более 300 проектов антикризисного управления в разных отраслях.

Миллионы

Находим аномалии в банковских выписках с миллионами операций.

Более 20 000

пользователей финансовых моделей BRP ADVICE.

В 17 странах

в России, Швейцарии, Бельгии и других странах используют финансовые модели BRP ADVICE.

5 бесплатных

профессиональных финансовых моделей и бюджетов

Более ₽700 млрд

бюджет проектов, рассчитанных в стенах BRP ADVICE

Клиенты BRP ADVICE

Есть вопросы? Давайте обсудим!

Группа BRP ADVICE
Платежи по банковским картам осуществляются в адрес ИП Рыбцев Виктор Викторович. ИНН 782512845653. Оферта и реквизиты >>>
Головной офис и почтовый адрес: Санкт-Петербург, ул. Малая Морская, д. 11, оф. 423.

Место проведения занятий в Москве:
ул. Каланчевская, д. 15 (если прямо не указано иное).

Телефон: +7(499)394-63-64, +7(812)983-49-76, по РФ – 8(800)700-97-86