Тема: Подбор параметров, создание сценариев
Задание 1. Подбор параметров. 1
Порядок выполнения Задания 1. 2
Контрольное задание. 3
Задание 2. Создание сценария. 3
Порядок выполнения Задания 2. 3
Контрольное задание. 6
Оформите таблицу как показано ниже, вставив необходимые формулы.
Образец исходной таблицы (воспользуйтесь таблицей, построенной в занятии2 - «СсылкиДиаграммы»).
Образец таблицы с результатами.
Назовите этот рабочий лист «Исходные данные» и сделайте 5 копий на других рабочих листах в пределах данной рабочей книги.
Задание 1. Подбор параметров
Подбор параметров - это поиск определенного результата для ячейки с помощью подбора значения другой ячейки.
Откройте одну из копий рабочего листа «Исходные данные» и, используя инструмент «Подбор параметра», определите цену Изделия 3, если требуется получить долю выручки 50% от общей суммы реализации.
Порядок выполнения Задания 1.
1. Выберите команду Данные à Работа с данными à Анализ «что если» à Подбор параметра…
2. В появившемся окне Подбор параметра установить следующие параметры:
· В поле «Установить в ячейке» введите ссылку на ячейку, содержащую долю выручки от реализации Изделия 3 (в данном случае - это ячейка Е5)
· В поле «Значение» введите искомый результат (в данном случае 50)
· В поле «Изменяя значение ячейки» введите ссылку на ячейку, значение которой нужно подобрать (в данном случае - это ячейка С5. Щелкните «ОК». Задача решена.
Контрольное задание.
1. Откройте одну из копий рабочего листа «Исходные данные» и, используя инструмент «Подбор параметра», определите цену Изделия 1, если требуется получить итоговую сумму реализации 152000 руб.
2. Откройте одну из копий рабочего листа «Исходные данные» и, используя инструмент «Подбор параметра», определите объем Изделия 2, если требуется получить долю выручки этого изделия 70%.
Задание 2. Создание сценария
Используя инструмент «Сценарии», выполнить расчет доли выручки от реализации 3-его изделия при различных объемах продаж всех изделий.
Порядок выполнения Задания 2.
1. Откройте одну из копий рабочего листа «Исходные данные»
2. Данные à Анализ «что если» à Диспетчер сценариев…. Нажмите кнопку «Добавить»
3. Введите любое имя в поле «Название сценария», например «Вариант1»
4. В поле «Изменяемые ячейки» введите ссылки на ячейки, которые необходимо изменить (в данном случае – это ячейки от В3 до В6).
5. Установите флажок в поле «Запретить изменения» и нажмите кнопку OK.
6. В диалоговом окне «Значения ячеек сценария» измените значения ячеек В3, В4, В5, В6, т.е. объемы продаж.
7. Для создания второго сценария, нажмите кнопку «Добавить» и повторите шаги с 3 по 6.
8. Создав несколько сценариев, нажмите кнопку «OK», а затем в диалоговом окне «Диспетчер сценариев»- кнопку «Отчет».
9. 
В диалоговом окне «Отчет по сценарию» выберите тип отчета «Структура», укажите ячейки (результат), изменение которых Вы хотите видеть, и щелкните «ОК».
Сценарий будет создан на новом листе.
Контрольное задание
Используя инструмент «Сценарии», выполнить расчет суммы выручки при различных ценах на изделия, не менее 3 сценариев.
Задание 3. Расчет ипотечной ссуды.
1.Создать таблицу для расчета ипотечной ссуды (рис. 126) и расчитать сумму первого взноса, размер ссуды, месячную выплату по ссуде, общую сумму выплат и сумму комиссионых. При выполнении задания присвоить имена ячейкам С6, С7, С9, С10, С11, С12, С13 и ввести их в соответствующие формулы.
Рис. 126
Постановка задачи. При покупке дома покупатель оплачивает первый взнос, равный некоторому проценту от стоимости дома, т.е.
,
а на оставшуюся сумму берет ссуду в банке под некоторую годовую процентную ставку на некоторый срок (срок погашения ссуды), т.е.
Ежемесячно покупатель выплачивает банку определенную постоянную сумму, которая определяется с помощью функции ПЛТ(ставка; кпер; пс; бс; тип), т.е
,
Эта месячная плата включает в себе основные платежи и платежи по процентам. Аргументы функции ПЛТ имеют следующий смысл:
Ставка — годовая процентная ставка. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.
Кпер — общее число выплат по ссуде (срок погашения ссуды). Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48.
Пс — приведенная к текущему моменту стоимость, которая на текущий момент равноценна ряду будущих платежей. В нашем случае Пс равна размеру ссуды, т.е.
Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), что соответствует нашему случаю.
Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.
| Тип | Когда нужно платить |
| 0 или опущен | В конце периода |
| В начале периода |
В нашем случае выплата производится в конце периода (в конце каждого месяца). В итоге обращение к функции ПЛТ(…) будет иметь вид 
.
Общая сумма выплат, включающая основные платежи и платежи по процентам, равна
Сумма комиссионных, которую получит банк, равна
После проведения расчетов создать следующие сценарии (рис. 127):
| Сценарии | |||
| Срок погашения ссуды, мес. | |||
| Годовая процентная ставка |
Рис. 127
3. Получить отчет по сценарию в виде структуры с отражением результатов: месячная плата, общая сумма выплат и суммы комиссионных.
4. Подобрать параметры срока погашения ссуды при месячной плате 1500.
Задание 4. Расчет прибыли.
1.Создать таблицу для расчета прибыли (рис. 128). При выполнении задания присвоить имена ячейкам В3, В4, В5, В17 и ввести их в соответствующие формулы.
Рис. 128
Постановка задачи. Предприятие изготавливает и продает три вида изделий: изделие1, изделие 2, изделие 3. Затраты предприятия на изготовление изделия складываются из затрат на оплату работ и затрат на оплату материалов. Эти затраты предприятия определяют себестоимость изделия. Количество нормо-часов и единиц материалов на каждое изделие известно. Известны также стоимость нормо-часа работы и стоимость единицы материала. Чтобы предприятие получило прибыль от продажи изделий вводится торговая надбавка, равная некоторой доли от себестоимости. Отпускная цена на изделие определяется как себестоимость плюс торговая надбавка.
Расчетные формулы имеют следующий вид:
Себестоимость 
го изделия определяется по формуле:
где 
себестоимость го изделия,
стоимость единицы материала для го изделия,
количество единиц материала для го изделия.
стоимость нормо-часа работы по му изделию,
количество нормо-часов работы по му изделию.
Торговая надбавка для го изделия определяется по формуле
где 
абсолютное значение торговой надбавки для го изделия,
относительное значение торговой надбавки для го изделия, в % от себестоимости.
Отпускная цена на ое изделие опреляется по формуле
.
Прибыль по му (одному) изделию рассчитывается по формуле
.
Прибыль на изготовленные ые изделия рассчитывается по формуле
,
где 
количество изготовленных изделий го типа.
Суммарная прибыль по изготовленным изделиям всех типов определится по формуле
.
Необходимо создать таблицу для расчета прибыли (рис. 128). Ввести в ячейки необходимые формулы и провести расчет себестоимости, отпускной цены, прибыли на одно изделие и суммарной прибыли по каждому изделию и по всем трем изделиям. Присвоить имена ячейкам В3, В4, В5, в которых размещены исходные данные, и ячейке В17, в которой размещена выходная величина – сумма прибыли по всем изделиям.
2. Создать следующие сценарии (рис. 129):
| Сценарии | |||
| Стоимость нормо-часа работы | |||
| Стоимость единицы материалов |
Рис. 129
3. Получить отчет по сценарию в виде структуры с отражением результатов: сумма прибыли (ячейка В5).
4. Подобрать параметры стоимости нормо-часа работы при сумме прибыли по всем изделиям, равной 10000 руб.
