Лабораторная работа №10. Тема: Сценарии
Тема: Сценарии
Сценарий – это сохранённые как единое целое ячейки рабочего листа, содержащие значения и формулы. В качестве различных сценариев можно сохранять разные входные значения для формул и результаты их вычислений при этих значениях. Excel предлагает средства для быстрого переключения между сценариями, что позволяет просмотреть несколько сценариев и выбрать подходящий.
Сценарии применяются тогда, когда необходимо сравнить и проанализировать ряд результатов однотипных расчётов, включающих входные значения и значения, вычисленные по определённым формулам.
Рассмотренное ранее средство Подбор параметра позволяет за раз изменять значение только одной входной переменной. Сценарии же позволяют комбинировать любое количество входных значений. Кроме того, средство Подбор параметра работает только с одной формулой, в Сценариях можно сохранить результаты практически любого количества формул.
1. Создание и работа со сценариями
1.1. Создайте таблицу для расчёта амортизационных отчислений оборудования (рис. 1).
| Начальная стоимость | 180 000, 00р. |
| Остаточная стоимость | 25 000, 00р. |
| Срок эксплуатации | |
| Ежегодные амортизационные отчисления | 25 833, 33 |
Рис. 1. Таблица данных для расчёта амортизационных отчислений
В ячейку, вычисляющую ежегодные амортизационные отчисления, введите формулу: =АПЛ(начальная стоимость;остаточная стоимость;срок эксплуатации). АПЛ – функция, возвращающая величину амортизации актива за один период, рассчитанную линейным методом.
1.2. Вызовите окно Диспетчера сценариев: на вкладке Данные, в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Диспетчер сценариев. В появившемся окне Диспетчера сценариев нажмите кнопку Добавить. В окне Добавление сценария в поле Название сценария впишите имя создаваемого сценария, например, СЦ1.
Внимание! Имена сценариев не должны повторяться.
1.3. В поле Изменяемые ячейки введите диапазон, в который будут подставляться варианты значений (ссылки на ячейки, содержащие значения). Нажмите ОК.
1.4. В следующем окне во всех полях измените значения, например: начальная стоимость – 210 000,00р., остаточная стоимость – 21 000,00р., срок эксплуатации – 9 лет.
1.5. Нажмите кнопку Добавить и создайте ещё два сценария с разными вариантами значений. Нажмите кнопку ОК.
1.6. Для того чтобы просмотреть каждый сценарий, выделите Сц1 и нажмите кнопку Вывести. В таблице должны появиться значения для первого сценария. Затем аналогично посмотрите второй и третий сценарии.
1.7. Общую картину удобнее всего увидеть в режиме отчёта. В окне Диспетчера сценариев нажмите кнопку Отчёт. Отчёт будет создан на новом листе книги Excel. В отчёте представляются все варианты сценариев. Анализ возможных сценариев поможет принять экономически обоснованное решение.
1.8. Для редактирования сценария в диалоговом окне Диспетчера сценариев щёлкните по кнопке Изменить и в открывшемся окне внесите нужные изменения.
Внимание! Обратите внимание, в отчётах заголовки строк обозначены адресами ячеек с данными и не совсем понятно, какие именно данные представлены. Чтобы отчёт был более наглядным, всем изменяемым ячейкам нужно присваивать имена.
2. Самостоятельная работа. Использование сценариев для прогноза объемов продаж
Используя средство Сценарии, спрогнозируйте объемы продаж безалкогольных напитков региональным оптовым дистрибьюторам для зимы и для лета. Рабочий лист, подготовленный для выполнения таких расчетов, показан на рис.2.
| А | В | С | D | |
| Предлагаемая розничная цена одной упаковки | ||||
| Оптовая скидка | 0,15 | |||
| Предполагаемое количество проданных упаковок | Стоимость розничных продаж | Чистая стоимость | ||
| регион | ||||
| Северный | ||||
| Восточный | ||||
| Южный | ||||
| Западный | ||||
| всего |
Рис. 2. Таблица данных для создания сценария
Всем изменяемым ячейкам (В1:В2; В5:В8; С5:С8) необходимо присвоить соответствующие имена. Например, ячейка С5 имеет имя Северный_розн. Для этого на вкладке Формулы в группе Определённые имена нажмите кнопку Присвоить имя. В поле Имя: задайте имя ячейки. В поле Область: выберите лист, на котором расположена таблица. Нажмите ОК. Таблица для расчета объемов продаж состоит из двух разделов.
1. В диапазоне А1:В2 записаны предполагаемая розничная цена одной упаковки напитков и оптовая скидка, вычисляемая как процент от розничной цены.
2. В диапазоне A5:D8 по каждому региону приведены предполагаемые объемы продаж упаковок напитков, их розничная и чистая (оптовая) стоимости.Стоимость розничных продаж вычисляется по формуле: =Предполагаемое количество проданных упаковок*Предполагаемая розничная цена*Оптовая скидка. Чистая стоимость вычисляется по формуле: =Стоимость розничных продаж*(1-Оптовая скидка/предполагаемая розничная цена).
1. Используя Диспетчер сценариев, спроектируйте возможное изменение розничной цены и оптовой скидки в зависимости от объема продаж напитков летом и зимой.
2. Создайте отчёт об изменении продаж в зависимости от сезона.
3. Защита сценариев от изменений
Для защиты сценариев от возможных изменений выполните следующие действия.
3.1. Откройте окно Диспетчер сценариев и в списке Сценарии щелкните на том сценарии, который хотите защитить от изменений, и затем щелкните на кнопке Изменить.
3.2. В диалоговом окне Изменение сценария в области Защита установите флажок запретить изменения и/или флажок скрыть (в этом случае данный сценарий не будет отображаться в списке Сценарии диалогового окна Диспетчер сценариев).
3.3. Щелкните на кнопке ОК. В открывшемся диалоговом окне Значения ячеек сценария щелкните на кнопке ОК и закройте окно Диспетчер сценариев.
3.4. Чтобы ввести в действие защиту сценария перейдите на вкладку Рецензирование в группе Изменения нажмите кнопку Защитить лист. В открывшемся окне задайте пароль и отметьте галочкой строку изменение сценариев.
3.5. Для защиты листа введите свой пароль, если вы хотите, чтобы отключение защиты сценария было возможно только после введения соответствующего пароля.
Внимание! Если вы решили применить пароль, обязательно запишите его и сохраните в надежном месте — Excel не сможет подсказать забытый вами пароль.
3.6. Для отключения защиты сценариев на вкладке Рецензирование в группе Изменения нажмите кнопку Снять защиту листа. Если вы использовали пароль, то появится диалоговое окно Снять защиту листа, в котором вы должны ввести пароль. После ввода правильного пароля щелкните в этом окне на кнопке ОК.
Внимание! Хотя изменение и удаление созданных ранее сценариев в защищенном рабочем листе невозможно, создавать новые сценарии на таком же листе можно. При этом разрешается изменять значения в защищенных ячейках, но нельзя их (ячейки) удалять.