Задания на зачет
по курсу «Финансовые расчеты в электронных таблицах».
Вариант 1.
Задание 1. Предприятие еженедельно производит три вида изделий в количествах x1, x2, x3 шт. На производство одного изделия каждого вида затрачивается соответственно 19, 23 и 35 чел.-ч. Недельные возможности предприятия по трудовым ресурсам оцениваются в 540 чел.-ч.
- Как учесть ограничение по трудовым ресурсам в задаче линейного программирования, где в качестве управляемых переменных выбраны объемы выпуска изделий x1, x2, x3? Запишите это ограничение в аналитическом виде.
- Если целью руководства является максимизация дохода предприятия за счет выбора оптимальной программы выпуска изделий, а доход от реализации каждого изделия известен и составляет 350, 480, 932 у.д.е. соответственно, то как можно записать целевую функцию для такой задачи оптимизации? Как будет выглядеть математическая модель оптимизации при учете ограничения по имеющимся трудовым ресурсам?
- Пусть первоочередной задачей предприятия является не максимизация дохода, а снижение производственных издержек, которые можно уменьшить за счет изменения объемов x1, x2, x3 выпуска изделий. Издержки производства известны, и составляют 114, 387 и 256 у.д.е. на единицу каждого выпускаемого изделия соответственно. Как в этом случае можно записать целевую функцию для новой задачи оптимизации?
Задание 2. Сделайте расчет 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате.
Задание 3. Вас просят дать в долг 10 000 руб. и обещают вернуть через год 2 000 руб., через 2 года – 4000 руб., через три года – 7 000 руб. При какой годовой процентной ставке эта сделка выгодна?
Задание 4. Допустим, что у вас просят в долг 10 000 руб. и обещают возвращать по 2 000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7 %?
Вами была рассмотрена задача с двумя результирующими функциями: числовой – чистым текущим объемом вклада и качественной, оценивающей, выгодна ли сделка. Эти функции зависят от нескольких параметров. Некоторыми из них вы можете управлять, например, сроком и суммой ежегодно возвращаемых денег. Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрите способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 6, 2000; 12, 1500 и 7, 1500.
Задание 5. Составление отчетной ведомости по расчету затрат на производство.
Фирма производит CD-диски. Упаковка диска обходится фирме в 10 рублей штука, стоимость материалов 40 рублей. Готовые диски фирма продает по цене 100 рублей за штуку. Технические возможности фирмы позволяют выпускать до 5 тысяч дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков. За первую тысячу дисков оплата труда рабочих составляет 3 рубля за штуку, за вторую тысячу дисков – 4 рубля за штуку, за третью тысячу дисков – 5 рублей за штуку, за четвертую тысячу дисков – 6 рублей за штуку и свыше 4000 дисков – 7 рублей за штуку.
Фирме поступил заказ на изготовления 4500 CD- дисков. Необходимо подсчитать суммарные издержки и прибыль от выполнения данного заказа.
Для решения задачи заполните следующую таблицу в Excel.
Заказ, шт. |
|
|
Диски, шт. |
Оплата, руб./шт. |
Оплата, руб. |
Продажная цена, руб. |
|
|
|
|
|
Стоимость упаковки, руб./шт. |
|
|
|
|
|
Стоимость материала, руб./шт. |
|
|
|
|
|
|
|
|
|
|
|
Стоимость упаковки |
|
|
|
|
|
Стоимость материала |
|
|
|
|
|
Зарплата |
|
|
|
|
|
Общие издержки |
|
|
|
|
|
Прибыль |
|
|
|
|
|
При расчете зарплаты рабочих, в зависимости от объема выпущенных дисков, используйте логическую функцию ЕСЛИ (общая расчетная формула вводится в расчетный диапазон). Внимание, вы работаете с массивом данных, это означает, что при вводе формулы нажимаем одновременно три клавиши Ctrl+Shift+Enter.
Модуль 1. "Финансовые расчеты в электронных таблицах".
Задание 1. Вычислить n-годичную ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой i% и начальным взносом А% Сделать расчет для ежемесячных и ежегодных выплат
Задание 2. Вас просят дать в долг Р руб. и обещают вернуть Р1 руб. через год, P2 руб. — через два года к т. Д. , наконец, Рn руб. — через n лет При какой годовой процентном ставке эта сделка имеет смысл ?
Вариант |
|
Р |
Р1 |
Р2 |
Р3 |
Р4 |
Р5 |
1 |
3 |
17000 |
5000 |
7000 |
8000 |
|
|
2 |
4 |
20000 |
6000 |
6000 |
9000 |
7000 |
|
3 |
5 |
22000 |
5000 |
8000 |
8000 |
7000 |
5000 |
4 |
3 |
30000 |
5000 |
10000 |
I8000 |
|
|
5 |
4 |
35000 |
5000 |
9000 |
10000 |
18000 |
|
6 |
5 |
21000 |
4000 |
5000 |
8000 |
10000 |
11000 |
7 |
3 |
25000 |
8000 |
9000 |
I0000 |
|
|
8 |
4 |
31000 |
9000 |
I0000 |
10000 |
15000 |
|
9 |
5 |
32000 |
8000 |
10000 |
I0000 |
10000 |
11000 |
10 |
3 |
36000 |
10000 |
I5000 |
21000 |
|
|
Задание 3. Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение n лет. При какой годовой процентной ставке эта сделка имеет смысл?
Вариант |
n |
Р |
А |
1 |
7 |
170000 |
30000 |
2 |
8 |
200000 |
31000 |
3 |
9 |
220000 |
33000 |
4 |
10 |
300000 |
34000 |
5 |
11 |
350000 |
41000 |
6 |
7 |
210000 |
32000 |
7 |
8 |
250000 |
37000 |
8 |
9 |
310000 |
40000 |
9 |
10 |
320000 |
35000 |
10 |
11 |
360000 |
41000 |
Задание 4.Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок n лет.
Вариант |
n |
Р |
1 |
1 |
7 |
170000 |
5 |
2 |
8 |
200000 |
6 |
3 |
9 |
220000 |
7 |
4 |
10 |
300000 |
8 |
5 |
11 |
350000 |
9 |
6 |
7 |
210000 |
10 |
7 |
8 |
250000 |
11 |
8 |
9 |
310000 |
12 |
9 |
10 |
320000 |
13 |
10 |
11 |
360000 |
14 |
Задание 5.
Вы берете в долг Р руб. под годовую ставку i% и собираетесь выплачивать по А руб. в год. Сколько лет займут эти выплаты?
Вариант |
Р |
А |
i |
1 |
170000 |
31000 |
3 |
2 |
200000 |
32000 |
4 |
3 |
220000 |
33000 |
5 |
4 |
300000 |
34000 |
6 |
5 |
370000 |
41000 |
7 |
6 |
210000 |
32000 |
8 |
7 |
260000 |
37000 |
9 |
0 |
310000 |
40000 |
10 |
9 |
320000 |
35000 |
4 |
10 |
360000 |
41000 |
5 |
Задание 6.Вас просят дать в долг Р руб в день D и обещают вернуть Р1 руб. в день D1, Р2 руб. — в день D2 т.д., наконец, Р1 руб. — в день Dn .Имеет ли смысл эта сделка при годовой ставке i%? (i, Р, n, Р1,..., Рn взять из задания 2)
Вариант |
D |
D1 |
D2 |
D3 |
D4 |
D5 |
1 |
12.02.11 |
22. 10.11 |
11 05.12 |
25.12 12 |
|
|
2 |
13.02.11 |
23. 10.11 |
12 05.12 |
26.12 12 |
I2.07 14 |
|
3 |
14.02.11 |
24. 10.11 |
13 05.12 |
27.12.12 |
13.07 14 |
11.05.15 |
4 |
15.02.11 |
25. 10.11 |
14.05.12 |
28.12.12 |
|
|
5 |
16.02 11 |
26.10 11 |
15.05 12 |
29.12.12 |
I2.07 14 |
|
6 |
17.02 11 |
27.10 11 |
16.05.12 |
30.12.12 |
13.07 14 |
11.05.15 |
7 |
18.02.11 |
28. 10 11 |
17 05.12 |
31.12.12 |
|
|
8 |
19.02.11 |
29 10.11 |
18.05.12 |
01.01.13 |
12 07 14 |
|
9 |
20.02.11 |
30.10.11 |
19.05.12 |
02.01.13 |
13.07.14 |
11.05.15 |
10 |
2I.02.11 |
31.10.11 |
20.05.12 |
03.01 13 |
|
|
Задание 7. Составить отчетную ведомость реализации товаров n магазинами с месяца А по месяц В, приведенную на рис 1.
Вариант |
A |
В |
n |
1 |
май |
декабрь |
3 |
2 |
июнь |
январь |
4 |
3 |
мюль |
октябрь |
5 |
4 |
август |
январь |
6 |
5 |
сентябрь |
декабрь |
7 |
6 |
октябрь |
март |
8 |
7 |
ноябрь |
март |
9 |
8 |
декабрь |
июль |
10 |
9 |
январь |
июль |
4 |
10 |
февраль |
август |
5 |
В качестве стоимостей товаров введите произвольные трехзначные числа, а в качестве объемов их реализации — произвольные двузначные числа.
Рисунок 1.
Отчетная ведомость о результатах работы сети магазинов должна иметь следующую структур
Выручка сети магазинов в млн.руб.
Магазин |
январь |
.. |
июль |
Суммарная выручка |
Место |
Средняя выручка |
Процент |
Диапазоны |
Количество |
1 |
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
… |
|
|
|
|
|
|
|
|
|
n |
|
|
|
|
|
|
|
|
|
Для нахождения места магазина по объему продаж используется функция РАНГ.С помощью функции ЧАСТОТА подсчитайте для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1 201 млн. руб. С этой целью в диапазон ячеек введите верхние границы этих интервалов 1000, 1100 и 1200, соответственно, а в диапазон ячеек ( в таблице это столбец «Количество») введите формулу с использованием функции ЧАСТОТА.
Задание 8.Вы берете в долг Р руб. под годовую ставку i% и собираетесь отдавать по А руб. в год Сколько лет займут выплаты?
Вариант |
1 |
2 |
3 |
1 |
5 |
G |
7 |
8 |
9 |
10 |
A |
200 |
190 |
170 |
161 |
116 |
213 |
320 |
123 |
52I |
711 |
P |
1000 |
1700 |
3100 |
5900 |
6190 |
6509 |
6860 |
7216 |
7670 |
8138 |
i |
2 |
3 |
4 |
2 |
2 |
3 |
1 |
5 |
6 |
7 |
Задание 9.Вы собираетесь вкладывать по А руб. в течение n лет при годовой ставке i% Сколько денег будет на счете через n лет7
Вариант |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
А |
200 |
190 |
I78 |
164 |
I46 |
243 |
320 |
423 |
52I |
711 |
n |
10 |
11 |
12 |
13 |
14 |
8 |
9 |
10 |
11 |
12 |
i |
2 |
3 |
4 |
2 |
2 |
3 |
4 |
5 |
6 |
7 |
Задание 10. Определить процентную ставку для n-летнего займа Р руб. с ежегодной выплатой в А руб.
Вариант |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
A |
200 |
190 |
178 |
164 |
146 |
243 |
320 |
423 |
521 |
711 |
Р |
1000 |
1700 |
3100 |
5900 |
6190 |
6509 |
6060 |
7246 |
7670 |
8130 |
n |
2 |
3 |
4 |
2 |
2 |
3 |
4 |
5 |
6 |
7 |
Модуль 2. "Теоретические основы оптимизации и методы решения прикладных оптимизационных задач с помощью Excel."
Задание 1.(а) Транспортная задача.
Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-го пункта производства в j-й центр распределения сij приведена в таблице, где под строкой понимается пункт производства, а под столбцом — пункт распределения. Кроме того, в этой таблице в i-й строке указан объем производства в i-м пункте производства, а вj-м столбце указан спрос в j-м центре распределения Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.
Задание 2.(b) Задача о назначениях.
Имеются n рабочих и m видов работ. Стоимость сij выполнения i-м рабочим j-й работы приведена в таблице, где рабочему соответствует строка, а работе — столбец. Необходимо составить план работ так, чтобы все работы были выполнены, каждый рабочий был занят только на одной работе, а суммарная стоимость выполнения всех работ была бы минимальной.
Задание 3.(с) Линейная оптимизационная задача.
Решить данную задачу с помощью команды Сервис, Поиск решения.
Задание 4.(d) Уравнение регрессии.
Построить линейную модель для двух наблюдаемых величин (например, объем реализованных фирмой подержанных автомобилей за указанное число недель)
Вариант 1
(а) Транспортная задача
|
Стоимость перевозки единицы продукции |
Объемы производства |
|||
|
1 |
3 |
4 |
5 |
20 |
|
5 |
2 |
10 |
3 |
30 |
|
3 |
2 |
1 |
4 |
50 |
|
6 |
4 |
2 |
6 |
20 |
Объемы потребления |
30 |
20 |
60 |
15 |
|
(b) Задача о назначениях
|
Стоимость выполнения работ |
|
|
||
рабочие |
3 |
6 |
2 |
5 |
11 |