Платежи за кредит

	VISA	STB CARD	MASTER CARD	ВСЕГО
Январь	660	900	350
Февраль	670	920	360
Март	675	930	375
Апрель	677	925	395
Май	670	875	415

Лабораторная работа № 4.

Тема: Связывание данных в таблицах

Задание. Создайте таблицу, вид которой приведен ниже, для контроля личного бюджета по месяцам и за квартал.

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

1. Откройте первый рабочий лист и присвойте ему имя “Январь”. Присвойте таблице название “Личный бюджет”.

2. Создайте таблицу (см. табл. 4). В ячейку С3 введите имя “ЗНАЧЕНИЕ” и задайте для этого столбца денежный формат.

3. Введите формулы для вычисления значений в строках “ДОХОДЫ ВСЕГО”, “РАСХОДЫ ВСЕГО” и “СБЕРЕЖЕНИЯ”.

4. Оформите таблицу, выделив заголовки и строки с итоговыми значениями.

5. Сделайте копию таблицы и вставьте ее на 2 последующих листах. Присвойте листам скопиями названия Февраль и Март.

6. Вставьте копию таблицы на лист 4 и присвойте ему имя “За Квартал1”. Вычислите итоговые значения по каждой статье доходов и расходов, используя трехмерный вид связи между листами таблицы.

7. Вставьте копию таблицы на лист 5 и присвойте ему имя “За квартал2”. Вычислите итоговые значения по каждой статье доходов и расходов, используя для связи между листамитаблицыконсолидациюданных. Установите флажокСоздавать связи с исходнымиданнымив окне диалога Консолидация.

8. Вставьте копию таблицы на лист 6 и присвойте ему имя “За квартал3”. Вычислите итоговые значения по каждой статье доходов и расходов, используя для связи между листами таблицы имена строк.

9. Подсчитайте итоговые значения в строках “ДОХОДЫ ВСЕГО”, “РАСХОДЫ ВСЕГО” и “СБЕРЕЖЕНИЯ” на листах 4 , 5 и 6.

10. Введите значения и убедитесь, что вычисления выполняются верно.

Таблица 4

Личный бюджет

Статьи доходов и расходов	Значения
Доходы
Оклад
Премия
Дополнительный заработок
Доходы всего
Расходы
Жилье
Питание
Развлечения
Прочие расходы
Расходы всего
Сбережения

Лабораторная работа № 5

Тема: Финансовые функции. Логические функции. Абсолютные ссылки.

Условие задачи. Пусть предприятие приобрело оборудование стоимостью 120000 руб. Срок амортизации оборудования 15 лет, после чего его ликвидная стоимость будет составлять 1000 руб. Отчисления на амортизацию составляют 20% балансовой стоимости. Вычислите амортизационные отчисления, используя метод линейного списания и геометрически -дигрессивный метод. Определите оптимальный план начисления износа.

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

1. Откройте Лист 1 и присвойте ему имя ДАННЫЕ.

2. Введите в ячейку А1 заголовок Начисление амортизации.

3. Введите в ячейки А3 - А6 заголовки строк: Начальная стоимость, Срок амортизации, Максимальная норма списания, Остаточная стоимость после

4. Уменьшите ширину столбца В до 2,00, после чего задайте в ячейке В6 формулу =$D$4. В ячейку С6 введите слово лет. Это позволит автоматически вставить значение количества лет после указания цифры в ячейке D4.

5. В столбец D введите данные из условия задачи. Установите в ячейке D5 процентный формат. Задайте в ячейке D5 значение 0.2. Оформите таблицу.

6. Откройте Лист 2 и присвойте ему имя Расчет.

7. В ячейки B5, D5, F5, H5, J5 введите заголовки (табл. 5).

8. Уменьшите ширину столбцов A, C, E, G, I до 1,43.

9. В столбце В укажите годы начисления амортизации 1 - 15, используя операцию Автозаполнение.

10. Введите в ячейку D6 первоначальную стоимость оборудования, сделав ссылку на ячейку D3 листа ДАННЫЕ. При ссылках на лист ДАННЫЕ используйте абсолютные адреса.

11. В ячейке F6 укажите формулу для расчета величины амортизации методом линейного списания, используя функцию АМР.

Примечание: Время амортизации вычисляется как Срок амортизации – текущий год + 1. Стоимость определяется величиной Балансовой стоимости для соответствующего года. Ликвидная стоимость - это остаточная стоимость на листе ДАННЫЕ.

12.В ячейку H6 укажите формулу для расчета амортизации с помощью геометрически - дигрессивного метода, используя функцию ДОБ. При этом необходимо проверять, чтобы полученное значение не превышало 20% балансовой стоимости.

Для этого используйте функцию ЕСЛИ:

ЕСЛИ результат вычисления по функции ДОБ меньше 20% балансовой стоимости, то в ячейку H6 записываем результат вычисления по функции ДОБ. В противном случае нужно указать максимально возможное значение, которое равно произведению балансовой стоимости на максимальную норму списания.

Первые три аргумента функции ДОБ те же, что и в функции АМР. Списание производится один раз в год.

13. В столбце ИЗНОС записываем большую величину амортизации, используя функцию нахождения максимального значения.

14. В ячейку D7 записываем величину балансовой стоимости в последующие годы, которая равна первоначальной стоимости за вычетом износа.

15. Копируем формулы из ячеек D7, F6, H6, J6 в последующие ячейки.

16. В ячейке J21 подсчитайте сумму начисленного износа. В ячейке D21 укажите остаточную стоимость оборудования.

17. Задайте в столбцах D, F, H, J денежный формат. Оформите таблицу.

18. Проверьте вычисления. Просуммируйте результаты в столбце Износ. Определите величину остаточной стоимости оборудования. Она должна быть равна 1000р.

19. Представьте процесс начисления Износа графически. Выделите столбцы Линейное списание, Дигрессивный метод и Износ для построения графика. Выберите команду Вставка/Диаграмма/На новом листе. Выберите тип диаграммы График. Присвойте диаграмме название “Расчет амортизации”, оси Х – “Год”, оси Y – “Величина амортизации”.

Таблица 5

Год	Балансовая стоимость	Линейное списание	Дигрессивный метод	Износ
1	120 000,00р.	7 9333.33р.	24 000,00р.	24 0000,00р.
2	96 000,00р.	6 785,71р.	19 200,00р.	19 200,00р.
3	76 800,00р.	5 830,77р.	15 360,00р.	15 360,00р.
4	61 440,00р.	5 036,67р.	12 288,00р.	12 288,00р.
5	49 152,00р.	4 377,45р.	9 830,40р.	9 830,40р.
6	39 321,60р.	3 832,16р.	7 864,32р.	7 864,32р.
7	31 457,28р.	3 384,14р.	5 291,46р.	5 291,46р.
8	25 165,82р.	3020,73р.	5 033,16р.	5 033,16р.
9	20 132,66р.	2733,24р.	4 026,53р.	4 026,53р.
10	16 106,13р.	2517,69р.	3 221,23р.	3 221,23р.
11	12 884,90р.	2376,98р.	2 576,98р.	2 576,98р.
12	10 307,92р.	2326,98р.	2 061,58р.	2 326,98р.
13	7 980,94р.	2326,98р.	1 596,19р.	2 326,98р.
14	5 653,96р.	2326,98р.	1 130,79р.	2 326,98р.
15	3 326,98р.	2326,98р.	665,79р.	2 326,98р.
	1000			119000,00р.

Лабораторная работа №6.

Тема: Финансовые функции. Логические функции. Абсолютные ссылки.

Условие задачи. Пусть вы решили взять кредит в банке размером 200 000 руб. сроком на 5 лет, погашать который (основной долг и проценты по долгу) собираетесь равномерными платежами в конце календарного года. Запросы на финансирование Вы направили в три банка, из которых пришли ответы с соответствующими условиями. Вам предстоит сравнить условия, определив эффективную процентную ставку, а также составить план погашения кредита по годам для банка, в котором Вы берете кредит.

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

1. Откройте Лист 1 и присвойте ему имя КРЕДИТ. Укажите в ячейке А1 заголовок Финансирование, а в ячейке А3 подзаголовок Выбор кредита.

2. Сравните предложения разных банков, данные по которым будут представлены по строкам. Для этого укажите в ячейках В7, В9, В11 соответственно БАНК1, БАНК2, БАНК3. В ячейки C5, D5, E5, F5, G5 введите заголовки столбцов таблицы с исходными данными (см. табл. 6), а в ячейки I5, J5, K5, L5 введите заголовки таблицы, в которой будут производиться расчеты (см. табл. 7).

3. Введите исходные данные согласно табл. 6. Задайте в столбцах D F процентный формат, а в столбцах С и Е - денежный.

Таблица 6.

	Объем кредита	Выдача (%)	Плата за оформление	Ставка (%)	Срок (лет)
Банк 1	200000р.	0,95	300р.	0.12	5
Банк 2	200000р.	0,96	250р.	0,135	5
Банк 3	200000р.	0,965	350	0,142	5

4. Произведем расчеты во второй части таблицы. В ячейке J7 определим величину платы за риск, которую банки берут с клиентов (дизажио) в случае невозврата кредита. Она равна произведению Объема кредита на (1 - Выдача). В ячейке I7 подсчитайте сумму, которую банк Вам выплатит. Для этого из Объема кредита нужно вычесть Дизажио и Плату за оформление. В ячейке L7 подсчитайте плату, которую Вы должны заплатить банку за кредит, для этого используйте финансовую функцию ППЛАТ (вводите только обязательные аргументы функции). В ячейке L7 определите отношение годовой выплаты банку к сумме полученного от банка кредита.

5. Скопируйте формулы из I7, J7, K7 , L7 для остальных банков. Задайте в столбцах I, J, K денежный формат, а в столбце L – процентный. Оформите таблицу. Сравните полученные результаты с результатами, приведенными в табл. 7.

6. По отношению в столбце L определите банк, в котором Вам выгоднее взять кредит и постройте для него план погашения кредита.

Таблица 7

Получено	Дизажио	Выплата (год)	Выплата/Получено
189 700,00р.	10 00,00р.	55 481,95р.	29,2%
191 750,00р.	8 000,00р.	57 558,22р.	30,0%
192 650,00р.	7 000,00р.	58 536,99р.	30,4%

4. По отношению в столбце L определите банк, в котором Вам выгоднее взять кредит и постройте для него план погашения кредита.

5. Перейдите на второй рабочий лист и присвойте ему имя Погашение кредита. В ячейке А2 введите заголовок таблицы План погашения кредита. В ячейках B4, C4, D4, E4 введите заголовки столбцов: Год, Погашение долга, Выплата по процентам, Остаток.

6. Используя операцию Автозаполнение введите в столбец В годы от 1 до 5. Определите в ячейке D5 размер выплачиваемого в первый год долга по процентам как произведение Объема кредита на Ставку процента из листа КРЕДИТ. Напоминаю, что при ссылке на лист КРЕДИТ, надо использовать абсолютные адреса. В ячейке С5 запишите часть годового платежа, которая уйдет в первый год на погашение основного долга по формуле Выплата ( год) из листа КРЕДИТ минус Выплата по процентам. Остаток долга к концу первого года подсчитаем в ячейке Е5 по формуле: Объем кредита минус Погашение долга за первый год.

7. Выплачиваемые по долгу проценты за второй год определяются, исходя из остатка к концу первого года. Запишите результат в ячейку D6. Остаток долга к концу второго года определяется как разница между остатком к концу первого года и суммой, выплаченной на погашение долга за второй год. Запишите формулу в ячейку Е6. Скопируйте формулы из ячеек C5, D6, E6. При правильных вычислениях остаток долга к концу пятого года должен быть равен нулю.

8. Оформите таблицу. Укажите во всей таблице денежный формат.

Лабораторная работа № 7.

Тема: Работа с данными.

Задание: Выполните операции над данными. Предварительно создайте справочник, приведенный в табл.8.

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

1. Использование формы. Выделите область таблицы и перейдите в режим формы. Введите данные. Выполните листание записей. Перейдите к 6-й записи и замените адрес Лесная,19,12 на адрес Лесопарковая,12, 33.Добавьте еще одну произвольную запись в имеющийся список.

Таблица 8

№	Фамилия, Имя	Должность	Дата рождения	Адрес	Телефон
1	Сидоров Андрей	Кассир	06.12.48	Ленина, 38,4	65-10-02
2	Попов Петр	Продавец	23.04.56	Южная,6,41	34-82-19
3	Шмидт Анна	Продавец	01.08.60	Лесная,19,12	24-13-08
4	Шмидт Николай	Президент	16.05.55	Горная,212,44	35-03-31
5	Жуков Петр	Продавец	23.01.66	Солнечная,1,2	12-65-90
6	Попова Марина	Координатор	02.03.59	Лесная, 12,45	12-18-92
7	Орлов Сергей	Менеджер	05.08.64	Мопра, 12,22	26-66-76
8	Исаев Петр	Разведчик	23.02.64	Зеленая, 64,44	14-18-78

Выполните поиск записей по фамилии “Попов” (критерием поиска является фамилия), а затем по имени “Петр” (критерием поиска является шаблон * Петр). Выполните поиск сотрудников старше 30 лет (имеющих дату рождения после 01.01.68).

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

3. Группировка. Примените группировку данных по должностям.

4. Автофильтр. Установите автофильтр и выберите всех сотрудников, имеющих должность Продавец. Затем выберите данные обо всех продавцах, живущих на Северо-западе или ЧМЗ (телефон начинается с цифр 12 или 41). Результат применения фильтра скопируйте на другое место рабочего листа и дайте таблицам названия.

5. Расширенный фильтр. Перед использованием расширенного фильтра определите область критериев (3 строки правее таблицы) и область результатов поиска (10 -12 строк ниже таблицы). Выполните копирование строки заголовка исходной таблицы в указанные области.

Выберите из таблицы:

• сотрудников, чьи фамилии начинаются на букву П;

• сотрудников, чьи фамилии начинаются на букву П, с датой рождения после 01.01.54;

• сотрудников, профессия которых Президент или Координатор;

• другие варианты выборок по желанию.

Результаты выборок скопируйте на другие листы рабочей книги и дайте им соответствующие имена.

Лабораторная работа № 8

Тема: Работа со списками. Функции поиска в списках.

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

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

1. Создайте список клиентов. Откройте лист 1 и присвойте ему имя КЛИЕНТЫ. Введите заголовки столбцов в ячейки A1 – H1 (см. табл. 9) и введите в нее данные, используя команду Данные/Форма. Задайте в столбце H процентный формат. Отсортируйте данные по возрастанию по полю Код. Для связывания таблиц присвойте столбцам следующие имена: столбцу А – имя Фирма, столбцу В – Код, столбцу H – Скидка. Оформите таблицу.

Таблица 9