ЗАДАЧА 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога

 

1. Составить таблицу (на листе Задача 3) следующей формы
(рис. 4.)

Рис. 4.

Функция ЕСЛИ

2. Введите в таблицу заглавие, шапку, цифровые и текстовые данные.

В ячейку Е4 введите формулу =ЕСЛИ(И(В4=10;С4>18);D4*0,1;0). Формула означает, что если код города равен 10 и возраст старше 18 лет, то сумма налога определяется умножением дохода на величину налога. В

3. противном случае сумма налога равна 0. Скопируйте формулу из ячейки Е4 в Е5:Е6.

 

Задание 4. Ввод и обработка данных в формате ДАТА - ВРЕМЯ

 

1. В ячейки с А4 по F4 введите следующие данные («шапку» новой таблицы)

 

Порядко- вый номер

Фамилия

Дата рождения

Возраст

Юбилей

Премия

Рис. 5.

 

2. Столбец Порядковый номер заполните числами с1 по 14, используя автозаполнение.

3. Столбец Фамилия заполните фамилиями из пользовательского списка (табл. 1.)

4. Столбец Дата рождения также заполните данными из следующей таблицы.

5. В ячейках D5: D18 создайте формулу для начисления возраста студентов, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ, то есть в ячейке D5 нужно создать формулу: =ЦЕЛОЕ((СЕГОДНЯ()-С5)/365)

6. В ячейках столбца Юбилей должен содержаться либо текст Юбилей, либо символ «-» в зависимости от того, какое число содержится в соседней ячейке слева. Если число кратное 5, будем считать возраст юбилейным. Формула имеет вид: =ЕСЛИ(ОСТАТ(D5;5)=0;«Юбилей»;«-»)

7. С помощью условного форматирования (меню Формат) ячейки с текстом Юбилей оформите шрифтом курсив полужирный, синего цвета.

8. Заполните ячейки F5: F18 в соответствии со следующим правилом: премия начисляется только тем студентам, у которых юбилей. Сумма премии равна 50$. Примените любой из финансовых долларовых форматов для оформления ячеек F5: F18.

9. Используя функцию СЧЕТЕСЛИ, вычислите, сколько студентов-юбиляров, студентов моложе 24 лет, студентов старше 25 лет.

10. Отформатируйте таблицу: добавьте заголовок, внешние и внутренние границы к ячейкам, заливку.

11. Отсортируйте таблицу по фамилиям.

 

Таблица 1.

 

Порядковый номер	Фамилия	Дата рождения	Возраст	Юбилей	Премия
	Соловьев	01.01.1980		-	-
	Игнатов	31.03.1981		-	-
	Петров	04.04.1983		-	-
	Новоселов	30.12.1982		-	-
	Шустов	07.07.1983		-	-
	Потапова	06.10.1980		Юбилей	$50
	Сидоров	13.06.1983		-	-
	Ситников	12.04.1982		-	-
	Зорина	23.06.1983		-	-
	Колосова	24.06.1983		-	-
	Амосова	25.07.1983		-	-
	Горин	25.08.1984		-	-
	Иванова	13.06.1983		-	-
	Волков	15.07.1982		-	-
	Количество юбиляров
	Количество студентов моложе 24 лет
	Количество студентов старше 25 лет

 

Задание 5. Подбор параметра

 

На основе представленных данных требуется рассчитать рентабельность кафе. Исходные данные (на рисунке выделены обычным шрифтом (не полужирным)):

· Таблицу стоимости продуктов в расчете на 1 кг;

· Раскладку продуктов по блюдам в расчете на 1 порцию;

· Цену одного обеда;

· Количество людей;

· Количество рабочих дней в месяце.

Формулировка задания

1. Подготовить лист по образцу (рис. 6).

 

 

Рис. 6.

 

2.Добавить формулы для расчета промежуточных данных и требуемых результатов (ячейки с формулами выделены полужирным.). При вводе формул считать, что:

· Стоимость продуктов в таблицах “1 блюдо”, “2 блюдо”,
“3 блюдо” можно определить как произведение количества продукта в блюде на стоимость этого продукта в таблице “Стоимость продуктов”. Пример: для “1 блюда” формула выглядит так: =G3*C3, ее можно копировать во все ячейки столбца данной таблицы.

· Итоговая стоимость каждого блюда определяется как сумма чисел в соответствующем столбце.

· Стоимость 1-го обеда складывается из итоговых стоимостей
1-го, 2-го и 3-го блюд.

· Доход (за месяц) получается произведением цены 1-го обеда на количество людей и количество дней работы.

· Себестоимость продуктов (за месяц) получается произведением стоимости 1-го обеда на количество людей и количество дней работы.

· Итого расходы рассчитываются путем сложения себестоимости продуктов, зарплаты персонала, аренды помещения и прочих расходов.

· Прибыль определяется как разность между доходом и итоговыми расходами.

· Рентабельность продукции находится как отношение прибыли к итоговым расходам.

3. Руководствуясь полученными данными, решить с помощью Мастера подбора параметра следующие задачи анализа рентабельности (ответы сформировать в отдельной таблице произвольной формы):

· Какова должна быть назначена цена обеда, чтобы рентабельность составила 20%?

· Какое количество людей должно питаться в кафе, чтобы прибыль увеличилась на 5000 рублей?

· Какова должна быть цена на отдельные виды продуктов, чтобы рентабельность составила 15%, 20%?

Задание 6. Поиск решения. Уменьшение затрат на перевозку грузов

 

ПОСТАНОВКА ЗАДАЧИ

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

 

ПОРЯДОК ВЫПОЛНЕНИЯ:

Создайте на листе Транспортные расходы таблицу.

С этой целью:

 

Рис.7.

 

Ø В ячейку А1 введите текст «Оптимизация транспортных потоков».

Ø В ячейку В2 введите текст «Потребители->».

Ø В ячейки C2:F2 введите названия мясоперерабатывающих заводов.

Ø В ячейку А3 введите текст «Поставщики».

Ø В ячейки А4:А8 названия складов.

Ø Установите курсор в ячейку В4 и нажмите кнопку Автосумма, после чего выделите ячейки с С4 по F4. В строке формул появится формула =СУММ(С4:F4). Нажмите кнопку , расположенную слева в строке формул, и формула будет введена.

Ø Скопируйте содержимое ячейки В4 в ячейки В5:В8.

Ø Выделите ячейки с С4 до F8. Ведите цифру 1 и нажмите кнопку. Нажмите комбинацию клавиш CTRL+D (автозаполнение столбцов в выделенной области), а затем нажмите CTRL+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области Числовой.

Ø В ячейку В9 введите текст «Факт->».

Ø В ячейку С9 введите формулу =СУММ(С4:С8). Скопируйте формулу в ячейки D9:F9.

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

Введите требуемые объемы поставок и цены поставок. С этой целью:

Введите в ячейку В10 текст «Запросы->». В десятой строке вводятся

Ø значения потребляемого каждым из заводов мяса в тоннах.

Ø В следующие ячейки введите соответственно:

 

В11			С10
В12		D10
В13		E10
В14		F10
В15

 

Ø Выделите ячейки с А4 по А8. Нажмите клавишу CTRL и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с А11 по А15, затем отпустите клавишу мыши и клавишу CTRL. Названия складов будут скопированы.

Ø В ячейки второго столбца занесите объемы месячных запасов на различных складах в тоннах соответственно.

Ø В ячейки с С11 по F15 занесите стоимость перевозки тонны мяса с конкретного склада на конкретный завод. Для этого введите в ячейки с С11 по F15 следующие данные:

 

· 47000	· 41500	· 45000	· 32650
· 39000	· 32300	· 38000	· 41000
· 23650	· 27300	· 21000	· 18000
· 19500	· 19400	· 9000	· 24000
· 39000	· 36000	· 27500	· 44000

 

Ø В ячейку А16 введите текст «Всего».

Ø В ячейку С16 введите формулу =С4*С11+С5*С12+С6*С13+
С7*С14+С8*С15.

В ячейке С4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке С11 – цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок по данному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.

Ø Скопируйте формулу из ячейки С16 в ячейки D16:F16.

Ø В ячейку В16 введите формулу =СУММ(С16: F16). В данной ячейке будет вычисляться общая стоимость перевозки мяса.

Ø В ячейку А18 введите текст “Всего на перевозки требуется”, а в ячейку Е18 – “млн.руб.”.

Ø Для вычисления суммы в миллионах в ячейку D18 введите формулу =В16/1000000.

Выполните форматирование таблицы в соответствии с рис. 7.

Скопируйте лист Транспортные расходы (для возможного

восстановления начального вида таблицы) на лист2 и переименуйте скопированный лист, дав ему название Поиск решения.

Выполните поиск решения (Сервис – Поиск решения) с целью определения минимальных затрат на перевозки при соблюдении следующих условий (рис. 8).

Ø Объем поставок с конкретного склада должен быть меньше или равен запасам на складе.

Ø Объем перевозок не должен быть отрицательным.

Ø Запросы заводов должны быть выполнены полностью. Перевыполнение поставок допустимо, а недовыполнение – нет.

Сохраните результаты поиска решения. Проверьте правильность полученных результатов (рис. 9).

 

Рис. 8.

Рис. 9. Результат поиска решения.

 

Задание 7. Консолидация данных

 

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

Рис. 10.

 

В качестве исходных данных для консолидации используем следующую таблицу (рис. 10). Данные по колонкам В и С вводятся с клавиатуры, а по колонке D считаются по формуле.

Порядок выполнения консолидации для таблицы.

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

2. Выберите меню Данные – Консолидация.

3. В окне Консолидация в списке Функция выберите функцию Сумма.

4. Установите курсор в поле Ссылка. Выделите первую область в исходной таблице (A4:D8). Нажмите кнопку Добавить.

5. Повторить пункт 4 для диапазонов A10:D14, A16:D19.

6. Установите флажок в поле В левом столбце и нажмите кнопку ОК.

В результате должна получиться следующая таблица (рис.11).

 

Рис. 11.

 

Основной недостаток таблицы: не производится автоматический пересчет в итоговой таблице при изменении данных в исходной таблице.

 

Задание 8. Сводные таблицы

 

Мастер сводных таблиц позволяет использовать еще один способ обобщения табличных данных. Одна из особенностей этого мастера заключается

в том, что он удовлетворительно работает только с однородными табличными данными, поэтому исходную таблицу нужно преобразовать к виду (см. табл. 12). Для этого скопируйте исходную таблицу из предыдущего задания на лист 2 и проведите преобразования.

 

Рис. 12.

 

Для построения сводной таблицы необходимо выполнить следующие действия.

1. Вызовите Мастера сводных таблиц (меню Данные – Сводная таблица). Откроется окно – Первый шаг. Поставьте переключатель, указывающий источник данных на В списке. Щелкнуть по кнопке Далее >.

2. Вторым шагом указать диапазон, в котором содержатся исходные данные, то есть выделить ячейки с А1 по Е12, кн. Далее >.

 

Рис. 13.

 

3. Третьим шагом нужно определить, как будет выглядеть новая сводная таблица. Переместите мышью кнопку Точка в область Страница, кнопку Наименование в область Строка, кнопку Цена в область Столбец, а кнопку Сумма в область Данные. Щелкнуть Далее> (рис. 13).

4. Четвертым шагом в поле Поместить таблицу в … поставить переключатель на строку Существующий лист, щелкнуть по ярлыку листа 3 и по ячейке А1. Щелкнуть по кнопке Готово.

Открывая кнопку, находящуюся в клетке В1 (рис.14), можно выводить на экран информацию как по отдельному объекту, так и сводную по всем объектам.

 

Рис. 14. Сводная таблица

 

Задание 9. Списки

 

В MS Excel в качестве базы данных можно использовать список. При выполнении обычных операций с данными, например при поиске, сортировке

или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных:

Ø Столбцы списков становятся полями базы данных.

Ø Заголовки столбцов становятся именами полей базы данных

Ø Каждая строка списка преобразуется в запись данных.

1. Создайте таблицу в соответствии с заданным образцом
(табл. 2).

Таблица 2

Дата	Расход	Сумма	Получатель
01.06.12	Накладные расходы	$600	ЗАО БИН
02.06.12	Накладные расходы	$321	ТОО Надежда
04.06.12	Материалы	$16000	АО Престиж
05.06.12	Зарплата	$2000	Васильева М.Ф.
05.06.12	Зарплата	$2540	Казаков С.С.
05.06.12	Зарплата	$1890	Иванов И.И.
30.06.12	Накладные расходы	$1000	АО ИНВЕСТ
04.07.12	Накладные расходы	$600	ЗАО БИН
04.07.12	Накладные расходы	$440	ТОО Надежда
04.07.12	Материалы	$13200	АО Оргсинтез
05.07.12	Зарплата	$2000	Васильева М.Ф.
05.07.12	Зарплата	$2540	Казаков С.С.
05.07.12	Зарплата	$1890	Иванов И.И.
31.07.12	Накладные расходы	$1000	АО ИНВЕСТ
04.08.12	Накладные расходы	$600	ЗАО БИН
05.08.12	Зарплата	$2000	Васильева М.Ф.
05.08.12	Зарплата	$2540	Казаков С.С.
05.08.12	Зарплата	$1890	Иванов И.И.
04.09.12	Накладные расходы	$311	ТОО Надежда
05.09.12	Зарплата	$2000	Васильева М.Ф.
05.09.12	Зарплата	$2540	Казаков С.С.
05.09.12	Зарплата	$1890	Иванов И.И.

 

2. Используя Форму данных, добавьте в список данные об АО Престиж: «30.06.12, Материалы, $800, АО Престиж». Для вывода формы на экран щелкните на любой из ячеек заглавной строки и выберите команду Данные Ø Форма (рис.15.)

3. Используя Форму данных и кнопку Критерии, просмотрите информацию о Казакове и измените сумму зарплаты за 05.09.12 на $2800.

4. Используя Форму данных, просмотрите все данные списка о расходах на материалы, превышающих $12000.

Рис. 15.

 

5. Отобразите все данные списка по АО ИНВЕСТ, используя Данные Ø Фильтр Ø Автофильтр.

Используя Автофильтр, отобразите все данные списка по накладным расходам, а затем только за июнь (рис. 16).

 

Рис. 16.

 

6. По каждому расходу подведите итог по полю Сумма, воспользовавшись командой Данные Ø Итоги, предварительно отменив Автофильтр и отсортировав данные списка по полю Расход.

7. Присвойте имя (например, имя Список) диапазону ячеек, содержащему все данные списка (включая заглавную строку).

8. Вставьте перед диапазоном со списком 11 пустых строк.

9. В ячейки A1:D1 скопируйте шапку таблицы.

10. Используя Расширенный фильтр отобразите все данные списка по зарплате, используя для области критериев ячейки A1:D2. Изменив область критериев, отобразите все данные списка по накладным расходам.

11. Внесите изменения в область критериев, добавив в ее шапку еще одну ячейку с названием Сумма, так, чтобы отобразились накладные расходы только от $500 до $900 (рис. 17).

 

 

Рис. 17.

 

12. Используя Расширенный фильтр, поместите в любую пустую область рабочего листа все данные списка о накладных расходах и зарплате за июль, предварительно изменив область критериев, либо создав новую.

13. Используя Расширенный фильтр и новую область критериев, поместите данные списка только по зарплате за июнь и июль, указав дату, сумму и получателя. Для этого предварительно создайте шапку новой таблицы (3 ячейки).

14. Измените область критериев, оставив в качестве критерия только вид расхода – зарплату.

15. Для заданного критерия отбора вычислите общую сумму:

· В ячейку В6 введите формулу расчета суммы, используя Мастер функции БДСУММ. Для задания диапазона базы данных укажите имя соответствующего диапазона (Список), для задания поля укажите с помощью мыши ячейку с названием поля Сумма, для задания области критериев также воспользуйтесь мышью;

· В ячейку В5 введите текст «Сумма по заданному критерию».

16. Используя функцию БСЧЕТ, подсчитайте в ячейке С6 количество выданных зарплат. Имя поля, указываемого в окне Мастера функций, оставьте прежним. В ячейку С5 введите текст «Количество».

 

Самостоятельные задания по теме
«Табличный процессор EXCEL»

 

 

Задание 1. Анализ дебиторской задолженности

1. Построить таблицы 1 и 2 по приведенным ниже формам
(рис. 18, рис. 19). Названия месяцев в таблицах 1 и 2 ввести, используя формат даты. Разместить каждую таблицу на отдельном листе рабочей книги, используя одновременное выделение двух листов.

2. Присвоить каждому листу короткое имя, соответствующее названию таблицы в сокращенном виде.

3. В таблице 1 рассчитать значение строки «Итого».

4. В таблице 2 рассчитать значения граф 3 – 8. Графы 3 – 7 рассчитать по данным таблицы 1, причем формулу расчета процента оплаты ввести один раз, а далее скопировать ее по строкам и столбцам.

5. Выполнить обрамление всей таблицы, убрать сетку.

Рис. 18.

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

Рис. 19.

7. Убрать рамки у легенды и диаграммы.

8. Ввести в нижний колонтитул индекс группы, свою фамилию, имя, дату и имя файла.

9. Документ сохранить, организовать просмотр перед печатью.

10. В приложении Word создать документ с заголовком диаграммы, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.

Задание 2. Расчет возвратной суммы кредита

1. Построить таблицу по приведенной ниже форме (рис. 20).

 

Рис. 20. Расчет возвратной суммы кредита

 

2. Рассчитать сумму возврата кредита (гр. 6) при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 40% от суммы кредита (гр. 3), в противном случае сумма возврата увеличивается на 40% плюс 1% за каждый просроченный день. В результате графа 6 будет иметь вид (рис. 21):

3. Ввести название таблицы, а в строку между названием таблицы и ее шапкой ввести текущее значение даты и времени.

4. Выполнить обрамление всей таблицы, убрать сетку.

5. Используя Мастер рисования выделить тенью графу 2.

6. По данным граф 1, 3 и 6 таблицы построить гистограмму с легендой, заголовком, названием осей.

7. Убрать рамки у легенды и диаграммы.

Рис. 21.

8. Ввести в нижний колонтитул номер группы, свою фамилию, имя, дату и имя файла.

9. Документ сохранить, организовать просмотр перед печатью.

10. В приложении Word создать документ, поместить в него построенную диаграмму. Документ сохранить и организовать просмотр перед печатью.

Задание 3. Консолидация по видам выпуска ГКО

 

1. Создать рабочую книгу из трех листов. Присвоить первому листу имя «Январь», второму – «Февраль», третьему – «Итого».

2. Выделить все листы (удерживая CTRL, щелкнуть мышкой по ярлычкам листов) и ввести шапку таблицы.

3. Снять выделение листов и ввести названия каждой таблицы.

 

№ выпуска ГКО	Эмиссия (млрд. руб.)	Выручка (млрд. руб.)	Погашено (млрд. руб.)	Доходы бюджета (млрд.руб.)	Средняя взвешен ная цена
21000RMFS	979,69	662,95	433,90
21000RMFS	1998,00	1276,40	1250,70
22000RMFS	2440,89	1409,89	1296,50
23000RMFS	278,53	197,45
22000RMFS	162,50	118,23	54,64
ИТОГО:

Рис. 22.

 

4. Заполнить данными таблицу 1 (рис. 22) на листе «Январь» и таблицу 2 (рис. 23) – на листе «Февраль».

 

№ выпуска ГКО	Эмиссия (млрд. руб.)	Выручка (млрд. руб.)	Погашено (млрд. руб.)	Доходы бюджета (млрд.руб.)	Средняя взвешен ная цена
21000RMFS	1385,83	911,78	903,10
21000RMFS	50,00	36,64
22000RMFS	320,00	224,61	98,75
23000RMFS	143,07	56,71
22000RMFS	38,76	33,14	18,43
23000RMFS	66,65	52,17	32,17
21000RMFS	150,00	60,82
23000RMFS	54,53	47,18
21000RMFS	45,18	35,72	14,22
22000RMFS	73,77	30,10
ИТОГО:

Рис. 23.

 

5. Выполнить все необходимые расчеты следующим образом:

· Графа «Доходы бюджета» = графа «Выручка» – графа «Погашено»;

· Графа «Средняя взвешенная цена» = графа «Выручка»/графа «Эмиссия» * 100.

6. Рассчитать суммы итогов за январь и февраль методом автосуммирования.

7. Перейти на лист «Итого», ввести заголовок «Итоговая таблица».

Для получения итогов по каждой ценной бумаге выполнить

1. консолидацию по категориям (кроме столбца F), в качестве имен используются значения левого столбца (рис. 25).

2. Рассчитать итоговую среднюю цену по каждому виду ценных бумаг путем консолидации данных и выбрав функцию Среднее (рис. 24).

3. Рассчитать итоги по соответствующим графам листа «Итого» (рис. 26).

 

Рис. 24.

 

Рис. 25.

 

Рис. 26.

 

Задание 4. Оптимизируемые модели

Задача определения структуры продукции в условиях уменьшения прибыли.

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

Компания производит телевизоры, стерео и акустические системы, используя общий склад источников питания, громкоговорителей и т.д. Количество деталей ограничено. Нужно определить наиболее выгодное соотношение производимых продуктов. При этом необходимо учитывать, что прибыль в расчете на единицу продукции уменьшается при росте объема производства, так как необходимы дополнительные затраты для обеспечения сбыта. В задаче учтен фактор уменьшения прибыли при росте объема.

 

1. Создать исходную таблицу в точном соответствии с заданными адресами (рис. 27).

 

Рис. 27.

 

2. В выделенные ячейки ввести и скопировать следующие формулы:

в яч. С4 ввести: =$D$2*D4+$E$2*E4+$F$2*F4
в яч. D10 ввести: =75*МАКС(D2;0)^$H$8
в яч. Е10 ввести: =50*МАКС(Е2;0)^$H$8
в яч. F10 ввести: =35*МАКС(F2;0)^$H$8
в яч. D11 ввести: =СУММ(D10:F10),

где:
- числа 75, 50, 35 – единичная прибыль на разные виды продукции;
- в ячейке Н8 стоит коэффициент, учитывающий фактор уменьшения прибыли при росте объема производства (что делает задачу нелинейной).

Для решения задачи в меню Сервис выбрать Поиск решения. В окне Поиск решения в поле Установить целевую ячейку задать $D$11. Выберите

1. переключатель Максимальное значение. В поле Изменяя ячейки выберите $D$2:$F$2.

2. Далее следует задать ограничения по задаче.
Щелкните по кнопке Добавить и наберите первое ограничение:
$C$4:$C$8<=$B$4:$B$8 (то есть количество используемых деталей не должно превышать наличное).

3. Щелкните по кнопке Добавить и наберите второе ограничение:
$D$2:$F$2>=0

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

5. Скопируйте исходную таблицу на лист 2 и лист 3. В таблице на листе 2 снова запустите Поиск решения и в результирующем окне поставьте переключатель на Сохранить найденное значение. В таблице на листе 3 в окне Поиск решения задайте найти в целевой ячейке не максимальное значение, а, например, 12000. Сохраните найденное значение.

 

Задание 5. Расчет доходности учтенных векселей

1. Построить две таблицы по приведенным ниже формам (рис. 28).

2. В выделенных областях второй таблицы произвести расчеты по формулам.
Ячейки из графы Дней до погашения рассчитываются по формуле:
= ДНЕЙ360 («Дата обращения в банк»;«Срок до погашения»)

3. Ячейки из графы Цена векселя рассчитываются по формуле:
=«Номинал векселя»*(1-«Дней до погашения»*«Учетный процент» /360)

4. Ячейки из графы Дисконт рассчитываются по формуле:
=«Номинал векселя»-«Цена векселя»

5. Ячейки из графы Доходность рассчитываются по формуле:
=«Дисконт»*360/«Дней до погашения»/«Цена векселя»

6. Графу Цена векселя пересчитать по логической формуле и скопировать вниз по столбцу.

7. Задать соответствующим графам Денежный формат, Процентный формат, формат Даты. Согласно образцу расчертить таблицу.

8. Построить диаграмму, наиболее наглядно отражающую полученные результаты.

 

Таблица учетных ставок
Дней до погашения	Учетный процент
До 90 дн.	36%
От 90 до 120 дн.	40%
От 120 до 150 дн.	44%
От 150 до 180 дн.	68%
Реестр учтенных векселей
Номинал векселя	Срок до погашения		Дата обращения в банк	Дней до погаше-ния	Цена векселя	Дисконт	Доходность в % годовых
1 500,00р.	По предъявлению	01.01.12	21.12.11		1 485,00р.	15,00р.	36,36%
2 000,00р.	По предъявлению	10.02.12	19.10.11		1 753,33р.	246,67р.	45,63%
4 500,00р.	По предъявлению	20.03.12	14.12.11		3 807,00р.	693,00р.	52,01%
6 000,00р.	По предъявлению	10.04.12	19.11.11		4 966,00р.	1 034,00р.	53,16%
9 000,00р.	По предъявлению	15.05.12	04.12.11		6 263,00р.	2 737,00р.	97,72%
Итого					18 274,33р.	4 725,67р.

Рис. 28.

Задание 6. Конвертация валюты

Руководство фирмы поручило вам через Центральный банк России перевести деньги в банки нескольких стран и конвертировать их в иностранную валюту, которую могли бы оперативно использовать представители вашей фирмы. Для каждого из банков выделена сумма в 1 000 000 рублей. Необходимо создать отчет о проведенных операциях, указав в нем итоговые суммы в иностранной валюте для каждой страны.

Для упрощения расчетов размер комиссионных принимается одинаковым для всех валют и равным 1%.

 

Порядок выполнения работы.

1. Войдите в Internet и наберите следующий адрес
Web-страницы: http://www.rbc.ru

2. После завершения поиска появится Web-страница со сведениями, взятыми из международной информационной системы и подготовленными РИА «РосБизнесКонсалтинг».
Войдите в раздел <Курсы валют ЦБ России>, найдите и выпишите (или скопируйте) данные о курсах валют для своего варианта.

3. Создайте отчет в EXCEL, заполнив графы необходимыми данными и формулами и отформатировав надлежащим образом таблицу.

 

Варианты заданий (соответствие вида валюты номеру строки в таблице 3.

 

1 вариант - строки 9,6,10

2 вариант - строки 3,4,5

3 вариант - строки 1,2,7

4 вариант - строки 8,1,2

5 вариант - строки 4,5,6

6 вариант - строки 3,7,8

7 вариант - строки 9,10,1

Таблица 3

№ строки	Ведущие валюты мира	Условные обозначения валют
	Австралийский доллар	AUD
	Канадский доллар	CAD
	Французский франк	FRF
	Немецкая марка	DEM
	Итальянская лира	ITL
	Японская иена	JPY
	Нидерландский гульден	NLG
	Швейцарский франк	CHF
	Британский фунт стерлингов	GBP
	Американский доллар	USD

 

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