Задача 3. (5 баллов)Расчет стипендии учебной группы
Провести аналогичный расчет стипендии для группы минимум из 10 студентов и при условии, что стипендиальный фонд на группу равен 650000 руб., минимальная стипендия составляет 42500 руб., студенческий коэффициент равен 1,52.
Задача 4. (10 баллов) Планирование прибыльности фирмы
Требуется провести планирование прибыльности фирмы в заданном году, построив для этого электронную таблицу.
Каждый квартал фирма приобретает и продает некоторое количество товара - Объем сбыта. В начале каждого квартала фирма имеет некоторый капитал (Начальная сумма). Эта сумма вкладывается (см. Расход: ) в приобретение товара (Себестоимость реализованного товара), в расходы на заработную плату работников, в рекламу товара. Остальные расходы отнесены к Накладным расходам, которые считаются прямо пропорциональными Реализации. Объем сбыта зависит от Затрат на рекламу по эмпирической формуле.
Раздел Приход: складывается из Начальной суммы и Реализации. Остаток средств в конце квартала (разница между Приходом и Расходом) является Начальной суммой следующего квартала.
Валовый доход появляется за счет разницы цен покупки (Себестоимость товара) и продажи (Цена товара). Прибыль определяется разницей между Остаткомсредств на конец текущего квартала и Начальной суммой.
Уровень прибыльностиотражает эффективность продажи. В столбце Всего находятся суммы соответствующих данных за четыре квартала.
1. Создайте таблицу (Рис. 10): в ячейки, выделенные серым цветом, заносятся исходные данные, в остальные ячейки – формулы и соответствующий текст. Оформить таблицу, форматируя шрифт, рамку, закраску. (Подсказка: текст, оформленный жирным шрифтом находится в столбце A.)
2. Постройте гистограмму доходов (валовый доход, прибыль).
3. Дайте название листу книги "План".
4. Скопируйте содержимое листа на новый лист.
5. Вставьте строку коэффициента покупательского спроса Кпсперед строкой объема сбыта:
| Коэффициент покупательского спроса | 1,1 | 0,9 | 0,8 | 1,2 |
6. Отредактируйте формулы для объема сбыта
 |
7. Проверьте как изменились данные в таблице и гистограмма.
8. Добавьте в гистограмму затраты на рекламу.
| Формулы, используемые в таблице: |
| Себестоимость реализованного товара = Объем сбыта * Себестоимость товара |
| Реализация = Объем сбыта * Цена товара |
| Накладные расходы = Кнакл * Реализация, где Кнакл = 0,15 (15%) |
| Начальная сумма = Остаток с прошлого квартала |
| Валовый доход= Реализация – Себестоимость реализованного товара |
| Прибыль = Остаток – Начальная сумма |
| Уровень прибыльности = Прибыль / Реализация |
| A | B | C | D | E | F | G | |
| ПЛАН | |||||||
| финансовых потоков фирмы в 2013 году | |||||||
| Квартал | I | II | III | IV | Всего | ||
| Объем сбыта ( шт.) | 3 991 | 3 991 | 3 991 | 3 991 | 15 962 | ||
| Приход: | Н ачальная сумма | 155 000 | 172 916 | 190 831 | 207 747 | ||
| Реализация | 159 625 | 159 625 | 159 625 | 159 625 | 638 498 | ||
| Всего: | 314 625 | 332 540 | 350 456 | 367 371 | 638 498 | ||
| Расход: | Себестоимость реализованного товара | 99 765 | 99 765 | 99 765 | 99 765 | 399 061 | |
| Затраты на зарплату | 8 000 | 8 000 | 9 000 | 9 000 | 34 000 | ||
| Затраты на рекламу | 10 000 | 10 000 | 10 000 | 10 000 | 40 000 | ||
| Накладные расходы | 23 944 | 23 944 | 23 944 | 23 944 | 95 775 | ||
| Всего: | 141 709 | 141 709 | 142 709 | 142 709 | 568 836 | ||
| Остаток | 172 916 | 190 831 | 207 747 | 224 662 | |||
| Валовый доход | 59 859 | 59 859 | 59 859 | 59 859 | 239 437 | ||
| Прибыль | 17 916р. | 17 916р. | 16 916р. | 16 916р. | 69 662р. | ||
| Уровень прибыльности | 11% | 11% | 11% | 11% | 11% | ||
| Цена товара: | 40р. | ||||||
| Себестоимость товара: | 25р. |
Рис. 10 Так должен выглядеть результат
Задача 5. (10 баллов) "Оптимизация распределения средств, вложенных в различные виды рекламы"
1. Внимательно прочитайте постановку задачи в верхней части рис.11. Создайте таблицу, показанную на рисунке:
1.1.Сделайте новый лист, дайте ему имя Виды реклам.
1.2.Введите шапку таблицы, отформатировав заголовки столбцов по центру клеток.
1.3.Вставьте над шапкой пустую строку и введите туда заголовок таблицы - Решение с помощью таблицы полужирным шрифтом. Выровняйте заголовок по центру всей таблицы.
1.4.Заполните левый столбец таблицы. Для этого введите числа 0 и 500 в первые две клетки, затем выделите их и скопируйте вниз, используя автозаполнение.
1.5.Введите в первую клетку 2-го столбца формулу, выражающую значение y из уравнения (1) раздела Уравнений (рис.11.). Чтобы указать значение средств, вложенных в рекламу
в I квартале из листа План, сначала введите знак = , затем перейдите на лист План и щелкните на соответствующей клетке таблицы План. Посмотрите, как отобразился адрес этой клетки в строке формул. Щелкните в строке формул после адреса этой клетки, перейдите на
лист Виды реклам, еще раз щелкните там же и продолжайте ввод формулы.
1.6.Заполните формулами 
и 
первые клетки 3-го и 4-го столбцов, а в 5-м столбце укажите их сумму 
.
1.7.Скопируйте получившуюся строку из четырех клеток вниз до конца таблицы.
2. Постройте справа от таблицы диаграмму, показанную на рис.12, с помощью Мастера диаграмм.
3. Из графика Общей эффективности видно, что есть значение x, при котором достигается ее максимум. Форматирование диаграммы сделайте позже, теперь же из таблицы найдите решение задачи:
3.1.Ниже таблицы сделайте табличку Решение из 2 строк и 6 столбцов (рис.11).
3.2.В клетку Макс (для максимального значения Общей эффективности) с помощью Мастера функций введите функцию МАКС() (Категория функций Статистические). В качестве аргумента функции укажите столбец Общей эффективности, в котором надо найти максимум.
3.3.В клетку № в столбце введите функцию ПОИСКПОЗ() (Категория функций Просмотр и ссылки), позволяющую найти номер той строки в столбце, в которой находится заданное значение. В качестве искомого значения укажите клетку с максимумом, искомого массива - столбец Общей эффективности, типа сопоставления - число 0.
3.4.В клетку для искомого x (в первом столбце таблички) внесите функцию ИНДЕКС() (Категория функций Просмотр и ссылки), позволяющую найти в столбце клетку, порядковый номер которой находится в заданной клетке. В качестве вида списка аргументов выберите первый из двух, предлагаемых Мастером функций. В качестве массива укажите столбец значений x, номера строки - клетку с № в столбце.
3.5.Заполните аналогичной формулой три соседние справа клетки, чтобы там появились соответствующие значения y и эффективностей. Проверьте правильность адресов клеток в скопированных формулах.
4. Найдите решение этой же задачи с помощью команды MS Excel Сервис-Поиск решения... , для чего создайте аналогичную предыдущей табличку (рис. 11):
4.1.Перед табличкой вставьте заголовок Решение Excel .
4.2.В клетки искомых x и y введите любые начальные значения, например, 6000 и 4000. Клетки 3-6 столбцов заполните формулами, вычисляющими общую эффективность для этих x и y.
4.3.Оптимизируйте значения x и y так, чтобы Общая эффективность стала максимальной ( 
). В качестве ограничений можно установить значения x и y больше 0, а также их сумму равной значению средств, вложенных в рекламу в I квартале из листа План. Сохраните полученные результаты и сделайте отчет (рис. 13). Сравните результаты, полученные из таблицы и с помощью команды Поиск решения... .
4.4.Попробуйте задать другие начальные данные для x и y, убедитесь, что результат не всегда может быть получен. Попробуйте в таблице План задать другое значение средств на рекламу в I квартале. Посмотрите, как изменились графики и оптимальные значения x и y.
5. Придайте таблице законченный вид:
5.1.Сделайте выравнивание столбцов 1 и 2 таблицы по центру, формат вывода чисел установите 0. Сделайте обрамление таблицы.
5.2.Вставьте пустую строку перед первой строкой и введите туда заголовок всего листа Оптимизация вклада средств в различные виды рекламы.
6. Придайте диаграмме законченный вид, войдя в режим ее форматирования:
Рис. 11
Рис. 13