Добавлен: 28.11.2018
Просмотров: 845
Скачиваний: 89
Анализ данных в Microsoft Excel «Подбор параметров. Разработка сценариев»
Выполнить следующее задание в среде Microsoft Excel.
Оформить отчет.
1. Введите следующую таблицу (таблица 1):
Таблица 1
Стоимость тура типа А($)
500
Курс
59,75
Количество заявок на тур А
27,85
Доход
= стоимость * курс * количество
Себестоимость тура типа А($)
300
Зарплата сотрудникам
10 000,00р.
Начисления на зарплату
= зарплата * % начислений
% начислений на зарплату
36,50%
Себестоимость
= себестоимость тура * курс * количество +
зарплата + начисления
Затраты на рекламу
10 000,00р.
НДС
= доход * % НДС
Издержки
= себестоимость + затраты на рекламу + НДС
Прибыль
= доход - издержки
2. Воспользовавшись опцией меню Сервис – Подбор параметров, подберите значение ячейки
Стоимость так, чтобы в строке Прибыль было 60 000р., затем 70 000р. Запишите значение
цены.
3. Подберите такое значение ячейки Себестоимость тура типа А, чтобы получить в строке
Прибыль значение 60 000р., затем 70 000р. Запишите значение цены.
4. Попробуйте добиться такого же результата, изменяя ячейки Зарплата. Проанализируйте
полученный ответ (письменно).
5. Вызовите опцию меню Сервис – Сценарии. Создайте сценарий По цене, в котором
изменяется ячейка Прибыль в зависимости от ячейки Себестоимость тура типа А. Дважды
измените значение ячейки на записанные значения цены в п.2 и каждый раз выведите отчет на
отдельном листе.
6. Создайте сценарий, в котором Прибыль изменяется в зависимости от Себестоимость тура
типа А. Назовите сценарий По себестоимости. Измените значение себестоимости на
записанные в п.3 данные. Снова выведите отчет.
7. На новом рабочем листе создайте таблицу Расчет по двум турам (таблица 2).
Таблица 2
Тур А
Тур В
Стоисость тура ($)
500
800
Курс
27,85
27.85
Количество заявок на тур
20
15
Доход
Себестоимость тура ($)
300
550
Зарплата сотрудникам
10 000,00р.
10 000,00р.
Начисления на зарплату
% начислений на зарплату
36,50%
36.50%
Себестоимость
Затраты на рекламу
10 000,00р.
10 000,00р.
НДС
% НДС
20%
20%
Издержки
Прибыль
Общее число заявок
= заявок А + заявок В
Общая прибыль
= прибыль А + прибыль В
8. Воспользовавшись алгоритмом поиска решений, найдите максимальную прибыль. В качестве
ограничений задайте:
- зарплата не менее 5000 (для каждого тура);
- общее число заявок не более 50;
- себестоимость тура типа А не менее 300;
- себестоимость тура типа В не менее 550;
- стоимость тура типа А не более 600;
- стоимость тура типа В не более 1100;
- число заявок – целое и больше нуля.
Рассчитав прибыль, получите отчет.
