Технология работы. 1. Наберите математическую модель по заданному примеру (рис

1. Наберите математическую модель по заданному примеру
(рис. 2.18). Изменяемые параметры выделите в ячейках серым цветом. Сверьте полученные значения с результатом, представленным справа на том же рис. 2.18.

Формулы	Результат

Рис. 2.18

2. Ячейкам с изменяющимися данными присвойте соответствующие имена («Количество магазинов» и т.д.). Имя присваивается через меню Формулы, группа Определенные имена, команда Присвоить имя. В диалоговом окне Создание имени задайте имя либо согласитесь с предложенным.

Внимание! В имени нельзя использовать пробелы! Вместо пробела ставьте нижний подчерк «_».

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

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

Вызовите диспетчер сценариев (меню Данные, группа Работа с данными, Анализ «что–если», Диспетчер сценариев) В появившемся диалоговом окне щелкните по кнопке Добавить. Во вновь появившемся диалоговом окне задайте Название сценария (введите название СЦ1), затем установите курсор в поле «Изменяемые ячейки». Установите курсор на первой изменяемой ячейке вашей модели-таблицы и щелкните левой клавишей. Ее адрес должен появиться в поле «Изменяемые ячейки». Нажмите клавишу Ctrl и, удерживая ее, щелкните мышкой на второй изменяемой ячейке, затем отпустите клавишу Ctrl. Подобным образом отметьте все изменяемые ячейки. При ручном вводе адресов ячеек необходимо разделять адреса символом «;» (рис. 2.19). Нажмите ОК.

Рис. 2.19

В открывшемся новом диалоговом окне введите значения каждой изменяемой ячейки по данным первого сценария (магазинов – 0, киоск – 1, покупок на один день в киоске – 10, стоимость товара – 50, расходы на киоск – 5000), нажмите ОК. Вы снова попадете в Диспетчер сценариев. Повторите набор нового сценария СЦ2 и т.д. (в соответствии с рис. 2.20).

Рис. 2.20

3. Вышеуказанным способом введите четыре сценария (СЦ1, СЦ2, СЦ3, СЦ4), меняя параметр Покупок на один киоск в день: 10, 20, 30, 50.

4. После набора четырех сценариев щелкните на кнопке Отчет. В появившемся диалоговом окне установите курсор в поле «Ячейки результата» и введите адрес ячейки c формулой расчета Чистого дохода в месяц (C32). Нажмите ОК. Появится новая страница с названием ярлыка Структура сценария, в которой отобразится результат расчетов в виде Структуры сценария.

5. Оформите данные в виде диаграммы:

а) выделите ячейки с E12 по H12. Меню Вставка, группа Диаграмма, команда Гистограмма. Выберите Гистограмму с группировкой;

б) меню Конструктор, группа Данные, щелкните Строка/Столбец (меняется ориентация строк и столбцов);

в) меню Конструктор, группа Данные, Выбрать данные. В группе Элементы легенды (ряды) выберите Ряд1 и нажмите кнопку Изменить. В окне Изменение ряда введите Имя ряда – 10. Нажмите ОК. Аналогично назовите Ряд2 – 20, Ряд3 – 30, Ряд4 – 50;

г) меню Макет, группа Подписи, Название диаграммы – над диаграммой. Введите – Чистый доход (при стоимости товара 50 р.);

д) меню Макет, группа Оси, Основная горизонтальная ось – нет;

е) меню Макет, группа Подписи, Названия осей – название основной горизонтальной оси – название под осью. Введите – Количество покупок;

ж) меню Макет, группа Подписи, Подписи данных – У вершины снаружи. Получится диаграмма, изображенная на рис. 2.21.

Рис. 2.21

6. Скопируйте таблицу с математической моделью на другой лист. Аналогично постройте новые сценарии для стоимости товара 80 р.:
0 магазинов, 1 киоск, покупок на один день – 10, 20, 30, 50 (рис. 2.22).

Рис. 2.22

7. Аналогично постройте диаграмму.

Первый расчет (Структура сценария 1) по набору из четырех сценариев при стоимости разовой покупки 50 р. дал динамику прибыли (чистый доход), изображенную на диаграмме рис. 2.21. Второй расчет (Структура сценария 2) по набору из четырех сценариев при стоимости разовой покупки 80 р. дал динамику прибыли (чистый доход), изображенную на диаграмме рис. 2.23.

Проанализируем результат. Для заданной экономической модели и выбранных подстановках при стоимости покупки 50 р. прибыль будет, если в день в киоске происходит более 30 покупок (см. рис. 2.21). Если стоимость покупки увеличим в 1,5 раза, прибыль будет, если в день в киоске происходит более 20 покупок (рис. 2.23).

Рис. 2.23

Подбор параметра

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

Предположим, у вас есть 150 000 р. Цена одной акции – 15 000 р., процентная ставка 5 %, срок вклада – 1 месяц. Чтобы узнать, какую сумму вы получите через месяц, необходимо рассчитать коэффициент наращивания.

Коэффициент наращивания рассчитывается с помощью формулы сложных процентов:

К_н = (1 + C) ⁿ,

где С – процентная ставка, n – количество дней.

Для расчета суммы выплат умножим коэффициент наращивания на цену одной акции и количество акций.

Составив такую таблицу, мы уже видим, что, купив 10 акций, через 30 дней мы получим 648 291 р. (рис. 2.24)

Рис. 2.24

А сколько нужно дней, чтобы получить 1 млн р.?

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

В том случае, если нужно определить значение исходных данных исходя из заданной суммы выплат, задача значительно усложняется. Решить такую задачу можно только подбором, изменяя исходные величины, пока не будет получен желаемый результат. Для решения такой задачи можно использовать средство Excel – Подбор параметра.

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

Технология работы

1. Выберите из меню Данные, группа Работа с данными, Анализ «что–если» инструмент Подбор параметра. На экране появится окно диалога Подбор параметра ( рис. 2.25).

Рис. 2.25

2. В поле «Установить в ячейке» укажите целевую ячейку – ту, в которой мы хотим получить желаемое значение. В нашем случае это ячейка Сумма выплат.

3. В поле «Значение»введите числовое значение, которое должно быть получено в целевой ячейке (1000000). В это поле должно быть введено только число, если это не так, при запуске появится соответствующее сообщение.

4. В поле «Изменяя значение ячейки» укажите адрес ячейки, которая должна меняться для получения желаемого результата (срок вклада).

5. Нажмите кнопку ОК, чтобы начать процесс поиска нужного значения. На экране появится окно диалога Результат подбора параметра с сообщением о результате поиска.

6. Нажмите кнопку ОК, если полученные результаты удовлетворительны, или кнопку Отмена, если от полученных результатов придется отказаться.

Если решение найти не удалось, сообщение об этом выводится в окне диалога Результат подбора параметра.

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

Лабораторная работа № 6