Сценарий тестирования пример excel

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

Создание сценариев в Excel

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

• банки повысят процентные ставки по кредитам;
• существенно измениться уровень инфляции;
• поднимут налоги и т.п.

Тестировать план бюджета в оригинальном файле крайне не рекомендуется. Создавая новые копии документов для теста можно нарушить адресации во множестве трехмерных ссылок формул и функций. Наиболее рациональное решения для данной ситуации – это использование сценариев Excel.



Пример сценариев в Excel

Для примера применения сценариев в практике, будем использовать простые задачи. Допустим нам нужно накопить 13 800$ за 10 лет на банковском депозите с определенной процентной ставкой. Нам нужно узнать какой будем делать ежегодный взнос на депозит. И какая процентная ставка нас устроит для накопления денежных средств.

1. Составьте таблицу так как указано на рисунке:



2. Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».



3. В диспетчере нажмите на кнопку «Добавить».



4. В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.



5. Появится окно «Значения ячеек сценария», в нем введите новое значение 7% для ячейки B1, а в B2 не изменяйте как выше указано на рисунке. И нажмите ОК.
6. Повторите выше указанные пункты с 3 по 5. Только на этот раз в 4-ом пункте укажите имя «Макс.взнос»; в 5-том пункте укажите новое значение взноса -1100 для ячейки B2, а B1 оставьте без изменений как ниже на рисунке:



7. Теперь в диспетчере сценариев нажмите на кнопку отчет.



8. Ничего не меняя жмем ОК.

Готово!!!

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

Сценарии — это инструмент MS EXCEL из группы Анализ «что-если» (

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

Рассмотрим инструмент Диспетчер сценариев на конкретном примере.

Задача

Определить, на какой срок можно получить кредит для следующих вариантов:

Необходимо составить Сценарии для следующих вариантов:

размер кредита – $8 000, ежегодн. выпл. – $1 500, годовая проц. ставка – 16%;размер кредита – $12 000, ежегодн. выпл. – $2 000, годовая проц. ставка – 15%;размер кредита – $15 000, ежегодн. выпл. – $3 000, годовая проц. ставка – 14%;размер кредита – $20 000, ежегодн. выпл. – $3 500, годовая проц. ставка – 13%;размер кредита – $25 000, ежегодн. выпл. – $4 000, годовая проц. ставка – 12%.

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

Решение

Создадим простую модель для расчета количества периодов выплаты.

Наша «Модель» состоит из одной формулы

=КПЕР(B17;B18;B16)

и 3-х значений аргументов (исходных данных).

Всего необходимо рассчитать модель для 5-и различных сценариев. В каждом сценарии задаются различные значения суммы Кредита, Ставки и суммы Ежегодных выплат.

Сначала рассмотрим создание сценариев без использования Диспетчера сценариев.

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

Но, не будем сдаваться сразу — усовершенствуем ввод наших исходных данных в модель. Будем выбирать нужный сценарий с помощью

Выпадающего списка

(см.

Файл примера лист Без сценариев2

).

Теперь мы избавились от лишних копий модели. Исходные данные вводятся в модель не непосредственно в предназначенные для этого ячейки, а выбираются с помощью формулы

=СМЕЩ(D16;;ПОИСКПОЗ($B$15;$D$15:$H$15;0)-1)

из отдельного диапазона с исходными данными.

Сравним рассмотренный нами подход с работой Диспетчера сценариев.

Для создания сценария сделайте следующее:

• Вызовите Диспетчер сценариев
  
  (

  );
  

• Нажмите кнопку Добавить;
• Введите название сценария и диапазон ячеек, в которые должны подставляться значения исходных данных (см.
  
  Файл примера лист Сценарии
  
  );

• Нажмите ОК, откроется еще одно диалоговое окно для ввода данных;

• Нажмите Добавить, чтобы ввести еще один сценарий или ОК, чтобы вернуться в окно Диспетчера сценариев.

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

Нажмите кнопку Отчет…, чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или

Сводной таблицы

.

Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов

используйте клавишу

CTRL

.

Автоматически созданный отчет в виде

Сводной таблицы

, конечно, требует доработки.

Основным недостатком инструмента Сценарии является тот факт, что значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев. Конечно, их можно вывести в отчете, но менять, по-моему, их в нем не очень удобно.

Одно
из главных преимуществ анализа данных
– предсказание будущих событий на
основе сегодняшней информации.

Сценарии
являются частью блока задач, который
иногда называют инструментами анализа
«что-если» (Анализ
«что-если». Процесс изменения значений
ячеек и анализа влияния этих изменений
на результат вычисления формул на листе,
например изменение процентной ставки,
используемой в таблице амортизации для
определения сумм платежей.).

Сценарий
— это набор значений, которые в приложении
Microsoft Office Excel сохраняются и могут
автоматически подставляться в лист.
Сценарии можно использовать для прогноза
результатов моделей расчетов листа.
Существует возможность создать и
сохранить в листе различные группы
значений, а затем переключаться на любой
из этих новых сценариев, чтобы просматривать
различные результаты. Или можно создать
несколько входных наборов данных
(изменяемых ячеек) для любого количества
переменных и присвоить имя каждому
набору. По имени выбранного набора
данных MS
Excel
сформирует на рабочем листе результаты
анализа. Кроме этого, диспетчер сценариев
позволяет создать итоговый отчет по
сценариям, в котором отображаются
результаты подстановки различных
комбинаций входных параметров.

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

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

Для
защиты сценария используются флажки,
которые выставляются в нижней части
диалогового окна Добавление
сценария.
Флажок Запретить
изменения
не позволяет пользователям изменить
сценарий. Если активизирован флажок
Скрыть,
то пользователи не смогут, открыв лист,
увидеть сценарий. Эти опции применяются
только тогда, когда установлена защита
листа.

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

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

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

2.3Пример расчета внутренней скорости оборота инвестиций.

Исходные
данные:
затраты по проекту составляют 700 млн.
руб. Ожидаемые доходы в течение последующих
пяти лет, составят: 70, 90, 300, 250, 300 млн. руб.
Рассмотреть также следующие варианты
(затраты на проект представлены со
знаком минус):

• -600;
  50;100; 200; 200; 300;

• -650;
  90;120;200;250; 250;

• -500,
  100,100, 200, 250, 250.

Решение:

Для вычисления
внутренней скорости оборота инвестиции
(внутренней нормы доходности) используется
функция ВСД
(в ранних версиях — вндох):

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

Внутренняя ставка
доходности —
это процентная ставка, принимаемая для
инвестиции, состоящей из платежей
(отрицательные величины) и доходов
(положительные величины), которые
осуществляются в последовательные и
одинаковые по продолжительности периоды.

ВСД (Значения;
Предположения)

Значения —
это массив или ссылка на ячейки, содержащие
числа, для которых требуется подсчитать
внутреннюю ставку доходности.

• Значения должны
  содержать, по крайней мере, одно
  положительное и одно отрицательное
  значение.

• ВСД использует
  порядок значений для интерпретации
  порядка денежных выплат или поступлений.
  Убедитесь, что значения выплат и
  поступлений введены в правильном
  порядке.

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

Предположение
— это
величина, о которой предполагается, что
она близка к результату ВСД.

В нашем случае
функция для решения задачи использует
только

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

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

• в
ячейке В14:

=ВСД(В5:В10)

• в
ячейке С14:

=ЕСЛИ(В14>В12);»Проект
экономически целесообразен»;

«Проект
необходимо отвергнуть»)

2.
Рассмотрим этот пример для всех комбинаций
исходных данных. Для создания сценария
следует использовать команду Сервис
| Сценарии |
кнопка Добавить.
После
нажатия на кнопку ОК
появляется
возможность внесения новых значений
для изменяемых ячеек

Для
сохранения результатов по первому
сценарию нет необходимости редактировать
значения ячеек— достаточно нажать
кнопку ОК
(
для подтверждения значений, появившихся
по умолчанию, и выхода в окно Диспетчер
сценариев.

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

Сценарий
«Скорость
оборота 1»
соответствует данным (-700; 70; 90; 300; 250;
300), Сценарий «Скорость
оборота 2»
— (-600; 50; 100; 200; 200; 300),

Сценарий
«Скорость
оборота 3»
— (-650; 90; 120; 200; 250; 250).

Нажав
кнопку Вывести,
можно
просмотреть на рабочем листе

результаты
расчета для соответствующей комбинации
исходных значений.

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

.

Заключение

Характерной
чертой современности является
стремительный научно-технический
прогресс, что требует от менеджеров и
бизнесменов значительного повышения
ответственности за качество принятия
решений. Это основная причина, которая
обусловливает необходимость научного
принятия управленческих решений.

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

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

Список
литературы

1. Официальный
   сайт компании Microsoft
   Corp.
   в Internet:
   http://www.microsoft.com/rus

2. «Анализ
   данных в Excel»
   — Джинжер Саймон: издательство –
   «Диалектика», 2004г.

3. «Microsoft
   Office
   Excel
   для студента» — Л.В. Рудикова: издательство
   – «БХВ-Петербург»; 2005г.

4. Симонович
   С., Евсеев Г. «Excel».
   – «М.: ИНФРА-М», 1998.

5. «Обучение.
   Excel
   2000». – М.: Издательство «Media»,
   2000.

6. «Основы
   информатики: Учеб. Пособие» / А.Н.
   Морозевич, Н.Н. Говядинова и др.; Под
   ред. А.Н. Морозевича. – Мн.: «Новое
   знание», 2001.

7. Ланджер
   М.
   «Microsoft Office Excel 2003 для
   Windows». – «НТ
   Пресс»
   – 2005.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #