Проверка результатов с помощью сценариев

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

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

Упражнение. Спрогнозировать выручку магазина в феврале и марте, в зависимости от изменения количества продаж товаров. Для этого:

· Скопировать исходную таблицу (блок ячеек А1:Н11) с листа «Анализ данных» на новый рабочий лист в ячейку А1, назвать лист «Прогноз».

· Установить курсор в ячейку Е3, на вкладке ФормулывгруппеОпределенные именанажатькнопкуПрисвоить имя.

· В окне Создание имени в опции Имя ввести с клавиатуры «Видеомагнитофон» и нажать ОК. Аналогичным способом присвоить соответствующие имена ячейкам Е4:Е10(если наименование состоит из нескольких слов, между ними ставится знак подчеркивания «_»). Ячейке Н11 присвоить имя «Выручка».

· Установить курсор в любую ячейку рабочего листа и на вкладке Данныев группе Работа с данными нажать кнопку Анализ «что-если». В открывшемся окне выбрать команду Диспетчер сценариев.

· В окне Диспетчер сценариев нажать кнопку Добавить.

· В окне Добавление сценария в опции Название сценариянабрать с клавиатуры «Прогноз на февраль», в опции Изменяемые ячейки указать блок ячеек Е3:Е10 и нажать ОК.

· В диалоговом окне Значения ячеек сценария заменить текущие значения на:

1. Видеомагнитофон – 50

2. Видеоплеер - 15

3. Магнитола -60

4. Музыкальный_центр - 20

5. Система_караоке - 12

6. Аудиоплеер - 100

7. Видеокамера - 30

8. Телевизор – 20

· Нажать кнопку Добавить.

· В окне Добавление сценария в опции Название сценариянабрать с клавиатуры «Прогноз на март», в опции Изменяемые ячейки убедиться, что указан блок ячеек Е3:Е10, и нажать ОК.

· В окне Значения ячеек сценария заменить текущие значения на:

1. Видеомагнитофон – 40

2. Видеоплеер - 12

3. Магнитола - 5

4. Музыкальный_центр - 27

5. Система_караоке - 15

6. Аудиоплеер - 120

7. Видеокамера - 25

8. Телевизор – 35

· Нажать кнопку ОК.

· В окне Диспетчер сценариев нажать кнопку Отчет.

· В окне Отчет по сценарию включить параметр Структура в опции Тип отчета,установить курсор в окнопараметраЯчейка результата, нажать клавишу F3 и из списка имен выбрать «Выручка», нажать ОК. Просмотреть результат.

ГЛАВА 4

Индивидуальные задания
для выполнения лабораторных работ

Задание 1

Коэффициент отдачи собственных средств = Рентабельность +Затраты на сырье / Затраты на переработку * (Рентабельность - Процентная ставка в месяц).

1. Данные графы «Наименование банка» ввести с использованием команды Данные\Проверка.

2. Используя логические функции предусмотреть в формуле деление на 0.

3. В ячейке А12 рассчитать среднюю процентную кредитную ставку.

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

5. Отсортировать данные таблицы по наименованию банка и наименованию заемщика.

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

7. Оформить таблицу по образцу.

8. Изменить начертание шрифта заголовка.

9. Шапку таблицы оформить при помощи заливки.

10. Построить смешанный график (две оси У), отражающий затраты на сырье и затраты на переработку по заемщикам. Дать название графику и показать легенду.

Задание 2

Остаточная стоимость = Балансовая стоимость – Износ до переоценки.

Восстановительная стоимость полная = Балансовая стоимость * Коэффициент.

Восстановительная стоимость остаточная = Остаточная стоимость* Коэффициент,

где Коэффициент = 3, если балансовая стоимость больше 500, в противном случае Коэффициент = 2,8.

1. Данные графы «Код подразделения» ввести с использованием команды Данные\Проверка.

2. Выбрать наименования объектов с кодом подразделения 100, у которых балансовая стоимость до переоценки больше 1000. Результат поместить в ячейки рабочего листа вне таблицы.

3. Отсортировать данные таблицы по возрастанию кода подразделения и убыванию износа до переоценки.

4. Создать сводную таблицу для расчета среднего износа до переоценки для каждого кода подразделения.

5. Оформить таблицу по образцу.

6. Выровнять по центру данные таблицы, представить числовые данные с точностью до 2-х знаков после запятой.

7. Зафиксировать шапку таблицы.

8. Построить круговую диаграмму, отражающую остаточную стоимость объектов. Обеспечить вывод названия объекта около каждого сектора и соответствующий процент. Дать название диаграмме. Самый большой сектор вырезать.

Задание 3

Остаток на конец года = Остаток на начало года + Поступило – Выбыло.

Графу «Инвентаризация» рассчитать, используя логическую функцию: если в течение года происходило выбытие основных средств, то произвести инвентаризацию, в противном случае – не проводить.

1. Данные графы «Код группы основных средств» ввести с использованием команды Данные\Проверка.

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

3. Выбрать основные средства, остаток которых на начало года был меньше 1000 и которые не выбывали в текущем году. Результат поместить в отдельную область рабочего листа.

4. Отсортировать данные таблицы по возрастанию кода группы основных средств и убыванию остатка на конец года.

5. Создать сводную таблицу для расчета остатков на начало и конец года по каждому коду группы основных средств.

6. Оформить таблицу по образцу.

7. Изменить начертание шрифта заголовка.

8. Шапку таблицы оформить при помощи заливки.

9. Построить гистограмму, показывающую остатки на начало и конец года. Показать легенду и дать название графику.

Задание 4

Темп изменения = На конец года / На начало года * 100.