Основные сведения об использовании сценариев,

Подборе параметра и поиске решения

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

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

Для проведения такого анализа «что-если» наоборот EXCEL имеет два средства: подбор параметра и поиск решения.

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

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

Содержание лабораторной работы

Выполнение данной лабораторной работы состоит из двух самостоятельных, не связанных между собой разделов:

· исследование информации, представленной в табл. 1 «Калькуляция» на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев;

· использование средства Поиск решения для решения двух задач линейного программирования.

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

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

Примечание. Средство подбора параметров поддерживает только одно входное значение переменной.

 

1. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1.

Таблица 1

Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции

(в таблице эти значения показаны на сером фоне жирным шрифтом). Остальные данные должны быть представлены в виде расчетных формул:

 

Доход = Цена продукции ^x Количество экземпляров;

Себестоимость реализованной продукции = Себестоимость продукции ^x Количество экземпляров;

Валовая прибыль = Доход – Себестоимость реализованной продукции;

Накладные расходы = Доход ^x Проценты накладных расходов;

Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции = Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

 

2. Переименуйте Лист1 в Калькуляция и скопируйте отлаженную таблицу с формулами в Лист2. Исследуйте информацию, представленную на таблице листа Калькуляция. Увеличение прибыли может быть достигнуто за счет изменения количества экземпляров, себестоимости, валовых издержек и уровня накладных расходов.

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

   Для этого:

· на вкладке Данные в группе Работа с данными выберите команду Анализ “что-если”, а затем выберите в списке пункт Подбор параметра;

· в диалоговом окне Подбор параметра в поле Установить в ячейке с помощью мыши укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1 500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);

· нажмите кнопку ОК.

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и щелкните кнопку OK для изменения значений ячеек таблицы в соответствии с найденным решением.

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

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

 

 

Построение сценариев

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

7. По данным рабочего листа Лист2 постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

8. Для построения каждого сценария необходимо:

· на вкладке Данные в группе Работа с данными выбрать команду Анализ “что-если”, а затем выбрать в списке пункт Диспетчер сценариев;

· в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;

· в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены 1»);

· в поле Изменяемые ячейки ввести абсолютную ссылку на ячейку, содержащую значение изменяемого параметра (например, цены);

· нажать кнопку OK;

· в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены ввести 175);

· нажать кнопку OK.

9. Повторите указанные в пункте 8 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);

10. Для просмотра сценариев в окне Диспетчер сценариев поочередно выбирайте сценарии из списка и щелкайте по кнопке Вывести. Excel заменит содержимое ячеек листа значениями из сценария и отобразит результаты на листе.

11. Для создания отчета по сценарию в диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.

12. В окне Отчет по сценарию выберите тип отчета Сводная таблица, установите абсолютную ссылку на ячейку со значением результата (Прибыль за продукцию) и нажмите ОК.

13. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2009 год и выполните прогнозирование бюджета на 2010, 2011 и 2012 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет. 

Бюджет предприятия на 2009 г. приведен в таблице:

 

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

 

 

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

· присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку диапазона В13-В17, на вкладке Формулы в группе Определенные имена выбирайте команду Присвоить имя и в окне Создание имени нажимайте ОК.

· присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2010», «Прибыль_2011», «Прибыль_2012»;

· введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

Общая прибыль= Объем продаж * Размер прибыли в %

Расход=Аренда + Услуги + Выплаты

Чистая прибыль=Общая прибыль-Расход

Показатели в столбцах C,D,E вычисляются по схеме:

Объем продаж 2010 г = Объем продаж 2009 г *(1+% роста объема продаж)

Размер прибыли 2010 г = Размер прибыли 2009 г *(1+% роста размера прибыли)

и т.д;

· определите первый сценарий, выполнив команду Данные/ Работа с данными/Анализ “что-если”/Диспетчер сценариев;

· аналогично создайте еще три сценария («Изменение показателей 1» и т. п.), щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне значения процентов роста показателей в ячейках B13:B17;

· создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на диапазон ячеек C11:E11, содержащие значения чистой прибыли;

· создайте отчет по сценарию, выбрав тип отчета – сводная таблица;

· проанализируйте полученные результаты решения задачи.

   Сохраните результаты лабораторной работы в файле с именем lab6.xls m.

 

 

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

 

1. Сурядный А. С.  Microsoft Office 2010. Лучший самоучитель. – М.: АСТ, Астрель, ВКТ, 2011. – 512 с.

 

2. Васильев А. А., Стоцкий Ю. А., Телина И. С. Microsoft Office 2010. Самоучитель. – Спб.: Питер, 2011. – 432 с.

 

3. Курбатова Е. Microsoft Office Excel 2010. Самоучитель. – М.: Диалектика/Вильямс, 2010. – 416 с.

 

4. Уокенбах Д. Microsoft Excel 2010. Библия пользователя. – М.: Диалектика/Вильямс, 2011. – 912 с.

 

5. Карлберг К. Бизнес-анализ с использованием Excel. – М.: Вильямс, 2012. – 576 с.

 

6.  Уокенбах Д. Формулы в Microsoft Excel 2010. – М.: Диалектика, 2011.—704 с.

 

7. Сингаевская Г. Функции в Microsoft Office Excel 2010. – М.: Диалектика/Вильямс, 2011. – 1094 с.

 

8. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2010. – М.: Диалектика/Вильямс, 2011. – 464 с.

 

[1] Макрос – действие или набор действий, используемые для автоматизации выполнения задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA).