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

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

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

Задание 8. Ввести таблицу с упрощенным бюджетом предприятия на 2000 год и выполнить прогнозирование бюджета на 2001, 2002 и 2003 годы, манипулируя темпами роста различных показателей. Подготовить 4 сценария с различными прогнозами роста и создать итоговый сравнительный отчет.

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

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

Технология решения задачи:

1. Присвоить имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливать курсор на каждую ячейку и выполнять команду ВСТАВКА/Имя / Присвоить, щелкая по кнопке «Ok» в окне «Присвоение имени».

2. Присвоить имена ячейкам результата С11, D11, E11 – «Прибыль_2001», «Прибыль_2002», «Прибыль_2003»

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

Справка

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

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

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

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

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

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

и т.д.

4. Определить первый сценарий, выполнив команду СЕРВИС/Сценарии;

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

- в окне “ Добавить сценарий ” ввести в поле “ Имя сценария ” имя (например, “ Сценарий 1 ”);

- в поле “ Изменяемые ячейки ” ввести абсолютную ссылку на ячейки, содержащие значения изменяемых параметров (B13: B17);

5. Щелкнув по кнопке «Добавит ь» создать аналогично «Сценарий 2», изменив непосредственно в окне значения процентов роста показателей в ячейках В13: В17, например, 3%, 2%, 6%, 4%, 4%.

6. Аналогично предыдущему пункту, изменяя значения отдельных показателей, создать еще «Сценарий 3» и «Сценарий 4».

7. Щелкнув по кнопке «Отчет» в окне “ Диспетчер сценариев ”, перейти к построению отчета.

- в окне «Отчет по сценарию» выбрать тип «структура» и ввести в поле «Ячейки результата» ссылки на ячейки С11, D11, E11, содержащие значения чистой прибыли.

Замечание. Ссылки должны разделятся символом «;» – «точка с запятой».

Щелкнуть по кнопке «Ok». На экране появится рабочий лист «Структура сценария» с таблицей примерно следующего вида:

8. Создать «Сводную таблицу по сценарию». Для этого перейти на исходный рабочий лист и выполнить команду СЕРВИС/Сценарии.

- в окне “ Диспетчер сценариев ”, перейти к построению отчета.

- в окне «Отчет по сценарию» выбрать тип «сводная таблица» и щелкнуть по кнопке «Ok».

- Появится рабочий лист с таблицей примерно следующего вида: