Лабораторная работа
Электронные таблицы Excel 2010
(Способы прогнозирования значений с помощью анализа «что-если»)
(метод сценариев)
Сценарием в Microsoft Excel называется набор значений подстановки, используемый для прогнозирования поведения модели. Существует возможность создания и сохранения в листе различных сценариев и переключения на любой из этих сценариев для просмотра результатов. Так, если требуется сформировать бюджет, но годовой доход точно не известен, то для дохода определяются различные значения, а затем для каждого сценария выполняется анализ «что-если».
В Приложении N1 представлена математическая модель для расчета деятельности торговой фирмы, имеющей сеть торговых ларьков и магазинов. В текущей экономической ситуации важно принять правильное решение по выбору стратегии торговли, распределения финансовых и людских ресурсов. Что выгоднее, увеличить оборот товара при заниженной его стоимости, или держать высокую стоимость сохраняя низкий оборот? Что выгоднее, держать сеть ларьков, или организовать продажу через магазины? Это и многое другое можно расчитать посредством метода сценариев.
Порядок выполнения работы
1. Загрузите Excel.
3. Наберите на Листе 1 таблицу с формулами и числовыми данными по заданному примеру Приложения N1, строго придерживаясь адресов ячеек (т. е. должны быть только столбцы B E F ). Такой вид представления в сценариях называется математическая модель. Числовые данные представляют собой изменяемые параметры, выделите их в ячейках серым цветом (как в приложении), для чего щелкните по подкрашиваемой ячейке и затем в меню выберите ярлык Главная а затем щелкните по пиктограмме 
с названием ЦВЕТ ЗАЛИВКИ; в появившейся палитре щелкните по серому цвету.
4. После набора модели проверьте правильность вычислений, сверив их с Приложением N2.
5. Ячейкам, где вписаны изменяемые параметры, и ячейкам, где стоят формулы для расчета ¢¢ДОХОД ОТ ПРОДАЖ ¢¢ ¢¢Итого¢¢ ¢¢ЧИСТЫЙ ДОХОД в МЕСЯЦ¢¢ , присвойте соответствующие выполняемой операции имена. Имя присваивается через меню ФОРМУЛЫ, опции ПРИСВОИТЬ ИМЯ. В появившемся диалоговом окне Создание имени в поле Имя введите сокращенное имя ячейки (Например, имя ячейки е4 запишем кол_маг. Пробелы в имени недопустимы.)
Д |
альнейшая работа выполняется диспетчером сценариев и роль пользователя заключается в выборе значений подстановок данных из имеющегося набора изменяемых данных модели. Этот выбор зависит от того, какой конечный результат предполагается получить.
Рассмотрим пример (Приложение N3). Задача состояла в том, чтобы оценить динамику чистого_дохода при торговле одним киоском. В качестве переменного параметра в сценариях было выбрано количество покупок_на_один_киоск при фиксированных параметрах средняя_сумма_покупки, количество_киосков, расходы_на_киоск, количество_магазинов. Последние параметры относятся к изменяемым и их значения были подставлены в сценарии со значениями отличными от текущих (см. структуру сценария). Первый расчет (Структура сценария 1) по набору из четырех сценариев при стоимости разовой покупки 50 рублей дал динамику прибыли (чистый_доход), изображенную на диаграмме Рис.1. Второй расчет (Структура сценария 2) по набору из четырех сценариев при стоимости разовой покупки 80 рублей дал динамику прибыли (чистый_доход), изображенную на диаграмме Рис.2.
Проанализируем результат. Для заданной экономической модели и выбранных подстановках при стоимости покупки 50 рублей прибыль будет, если в день в киоске происходит более 50 покупок (см. Рис.1). Если цену за покупку увеличим в ~1,5 раза, прибыль будет, если в день в киоске происходит более 30 покупок (см. Рис.2).
I. Задание. Выполните расчеты по структуре сценария 1
а) Вызовите диспетчер сценариев, для чего щелкните по меню Данные, поле Работа с данными, опция Анализ «что если», функция Диспетчер сценариев. В появившемся диалоговом окне щелкните по кнопке ДОБАВИТЬ. В новом появившемся диалоговом окне задайте НАЗВАНИЕ СЦЕНАРИЯ (введите название СЦ1), затем клавишей Tab установите курсор в поле ИЗМЕНЯЕМЫЕ ЯЧЕЙКИ Щелкните мышкой по ячейке Е10 вашей модели-таблицы. Ее адрес должен появиться в поле Изменяемые ячейки. Нажмите клавишу Ctrl и, удерживая ее, щелкните мышкой по ячейке Е12, затем по ячейке Е7, затем по ячейке F24, затем по ячейке Е4. (При ручном вводе адресов ячеек необходимо разделять адреса символом « ; ») Нажмите ОК. В открывшемся новом диалоговом окне введите значения каждой изменяемой ячейки по данным Структуры сценария 1 (Приложение N3), нажмите ОК. Вы снова попадете в диспетчер сценариев. Повторите набор нового сценария, щелкнув по кнопке ДОБАВИТЬ и затем введя название СЦ2, изменяемые ячейки оставьте такими же, нажмите ОК. В появившемся окне Значения ячеек сценария введите значения согласно Приложения N3.
б) Вышеуказанным способом введите четыре сценария
в) После набора четырех сценариев щелкните на кнопке ОТЧЕТ. В появившемся диалоговом окне Отчет по сценарию в поле Ячейки результата должен стоять адрес F32. Если стоит иной адрес, удалите его и щелкните мышкой по ячейке с адресом F32 вашей модели-таблицы. Нажмите ОК. Появится новая страница с названием ярлыка СТРУКТУРА СЦЕНАРИЯ , в которой отобразится результат расчетов в виде Структуры сценария.
II. Оформите данные в виде диаграммы
a) В структуре сценария выделите мышкой ячейки Результата для СЦ1, СЦ2, СЦ3, СЦ4. Выберите в меню ВСТАВКА, затем опцию ГИСТОГРАММА. В появившемся выпадающем меню выберите первый вид Гистограммы. В появившемся меню Конструктор выберите макет диаграммы похожий на макет Рис. 1 Приложения №3
b) Сместите Мастер диаграмм в сторону, чтобы открыть Структуру сценария .
c) Щелкните по меню Данные, опция Выбрать данные. В появившемся окне Выбор источника данных используя поле Элементы легенды (ряды) измените названия Ряд1, Ряд2, Ряд3, Ряд4 на значение параметров соответственно 30, 40, 50, 100 и затем нажмите кнопку ОК. Сделайте надписи на диаграмме в соответствии с Рис. 1 Приложения №3.
d) Щелкните по первому столбику диаграммы правой кнопкой мышки и выберите опцию Добавить подписи данных. Аналогично сделайте подписи над другими столбиками
III. Задание. Выполните расчеты по структуре сценария 2
IV. Создайте новую Структуру сценария для решения следующей задачи:
- получить чистый доход руб (количество магазинов – 0, покупок на 1 киоск – 50, средняя сумма покупки – 70 руб, расходы на содержание киоска – 6000 руб); переменную для сценариев выбираете самостоятельно.
В структуре сценария должно быть отображено не менее 3-х сценариев при нахождении заданных требований!!!
