Лабораторная, Информационные технологии Решить 8 лабораторных небольшого объема.
Лабораторная работа №1.
Решение прямой задачи отчетности
средствами электронной таблицы Microsoft Excel.
Данная лабораторная работа является первой и позволяет студенту восстановить навыки работы с электронной таблицей “Microsoft Excel” и операционной средой “Windows 95/98”, полученные в ходе изучения дисциплин «Информатика» и «Курс компьютерной подготовки».
Внимание! Результаты лабораторной работы №1 будут необходимы для выполнения лабораторной работы №2. Поэтому необходимо сохранить работу по персональному адресу, содержащему ФИО, № группы и № курса студента (см. ниже).
1. Войдите в электронную таблицу “Microsoft Excel” (щелчок левой кнопкой мыши по соответствующей пиктограмме “Excel” на рабочем столе “Windows”, либо последовательное нажатие левой кнопкой мыши кнопок “Пуск” – “Программы” – “Microsoft Excel”).
2. В ячейку А1 шрифтом “Arial Cyr” № 10 (обычно устанавливается по умолчанию) впишите заглавие: «Отчет о доходах и расходах предприятия». Для этого – активизируйте ячейку А1 одинарным щелчком левой кнопки мыши, наберите текст, введите текст в память двойным щелчком левой кнопки мыши вне ячейки А1. Для исправления текста – активизируйте ячейку А1 двойным щелчком левой кнопки мыши (появляется мигающий курсор “|”) и установите курсор редактирования в нужную позицию текста. Для стирания букв слева от курсора пользуйтесь клавишей ¬, для стирания букв справа от курсора – клавишей Delete, для стирания группы букв и слов – зачерните их, нажав левую кнопку мыши и проведя по ним курсором, затем нажмите клавишу Delete.
3. С помощью меню «Файл – Сохранить как» сохраните информацию по адресу:
«Мои документы / № курса_№ группы_ФИО / Лаб_№.xls»(например, Мои документы / 4_1_ИСН/Лаб_1.xls).
После “Сохранить как” появляется таблица “Сохранение документа”. Необходимо в строке “Папка:” с помощью кнопки “Ñ” установить “Мои документы”, с помощью третьей кнопки “Создать папку” (желтый портфель со звездочкой в правом верхнем углу) создать новую папку и присвоить ей имя “№ курса_№ группы_ФИО”, войти в эту папку двойным щелчком левой кнопки мыши (имя папки появляется в строке “Папка:”) и в строке таблицы “Имя файла:” набрать “Лаб_№”. При необходимости исправить имя папки – щелкнуть по нему правой кнопкой мыши и в появившемся “висящем” меню выбрать опцию “Переименовать”. При исправлении имени файла – щелкнуть по нему левой кнопкой мыши (устанавливается курсор редактирования текста). Для завершения сохранения информации – щелкнуть левой кнопкой мыши по кнопке “Сохранить” в правом углу таблицы “Сохранение документа”. После этого в верхней строке “Microsoft Excel” появится имя фала.
Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных – не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”).
4. В ячейку А3 введите «Период отчетности».
5. В ячейки B3 ¸ G3 посредством функции автозаполнения (перетаскивание ячейки за правый нижний угол (маркер принимает вид +) при нажатой левой кнопке мыши) введите месяца – январь, февраль, март, апрель, май, июнь.
6. В ячейку А5 введите название дохода предприятия – «Валовая выручка».
7. В ячейки B5 ¸ G5 посредством меню «Правка – Заполнить – Прогрессия» введите денежный поток, начиная с 150000 р, с шагом в 20000 р. Для этого – введите в ячейку В5 начальное число прогрессии 150000 (двойной щелчок по ячейке левой кнопкой мыши, набор числа, одинарный щелчок левой кнопкой мыши вне ячейки В5), после его ввода активизируйте ячейку В5, щелкните по ячейке правой кнопкой мыши и в появившемся «висящем» меню щелкните левой кнопкой мыши по опции «Формат ячеек…» (“рука с листом”). В появившейся таблице «Формат ячеек» выберите опцию «Число» (первая кнопка в верхней строке таблицы) и в строке «Числовые форматы» выберите денежный или финансовый формат. В строке «Число десятичных знаков» с помощью символов “D” (вверх), “Ñ” (вниз) установите два знака после запятой, а в строке «Обозначение» выберите денежную единицу рубль – р. Завершите форматирование ячейки, щелкнув левой кнопкой мыши по кнопке «ОК» таблицы «Формат ячеек».
Внимание! Если в ячейке появляются символы «ххххх» это означает, что ее диапазоны необходимо расширить (отформатированное число не умещается в ячейке). Для этого достаточно навести курсор “Excel” (белый крест) на правую границу имени (в данном случае В) ячейки (курсор примет вид черного креста с обоюдной горизонтальной стрелкой) и нажав левую кнопку мыши увеличить ширину столбца.
Для ввода прогрессии – активизируйте отформатированную ячейку с начальным числом 150000р. Посредством меню «Правка – Заполнить – Прогрессия» откройте таблицу “Прогрессия”, в строку “Шаг:” введите число 20000, а в строку “Предельное значение:” введите приблизительное последнее число прогрессии (150000+5*20000). Завершите ввод прогрессии, нажав кнопку «ОК» таблицы “Прогрессия”. Удалите лишние числа, начиная с ячейки Н (если неправильно назначено предельное значение прогрессии).
8. В ячейку А6 введите название – «Налог на добавленную стоимость, 20%».
9. В ячейку В6 введите формулу « =B5*0,2 », используя английский фонт. После завершения ввода выражения щелкните левой кнопкой мыши по зеленой “птичке” (n) в строке формул “Excel”. При правильных действиях в ячейке В6 появится численный результат.
10. С помощью функции копирования формул (нажать на правый нижний угол ячейки В6 левой кнопкой мыши и, не отпуская кнопку, протащить курсор по соответствующим ячейкам) скопируйте формулу в ячейки C6 ¸ G6.
11. В ячейку А7 введите название – «Выручка от реализации».
12. В ячейку В7 введите формулу « =В5-В6 ».
13. Скопируйте формулу в ячейки C7 ¸ G7.
14. В ячейку А9 введите название группы затрат – «Затраты на производство продукции».
15. В ячейку А10 введите название «В том числе: зарплата».
16. В ячейку В10 введите значение зарплаты – 600 и придайте ей денежный формат с двумя десятичными знаками после запятой.
17. С помощью функции размножения назначьте введенную зарплату всем ячейкам C10 ¸ G10.
18. В ячейку А11 введите название “арендная плата”.
19. Введите одинаковые арендные платы 2500 р для всех месяцев (ячейки В11 ¸ G11).
Также воспользуйтесь функцией размножения, введя 2500 в ячейку В11 и придав ей денежный формат.
20. В ячейку А12 введите название “аренда оборудования”.
21. Введите одинаковые расходы в 1500 р на аренду оборудования для всех месяцев.
22. В ячейку А13 введите название “прочие расходы”.
23. Для всех месяцев (ячейки В13 ¸ G13) с помощью прогрессии введите прочие расходы, начиная с 80000 р (шаг - 10000 р).
24. Посчитайте затраты на производство продукции (ячейка В9) с помощью функции “Автосумма” (значок “å”) в панели инструментов. Для этого – активизируйте ячейку В9 и щелкните левой кнопкой мыши по значку “å”. Обведите затем пунктиром (с помощью нажатия на леву кнопку мыши) ячейки В10 ¸ В13. В ячейке В9 при этом появятся правильные адреса слагаемых. Завершите суммирование, щелкнув левой кнопкой мыши по зеленой “птичке” (n) в строке формул “Excel”.
25. С помощью функции копирования формул получите затраты на производство продукции для всех месяцев.
26. В ячейку А15 введите название «Валовая прибыль».
27. Посчитайте валовую прибыль для всех месяцев, воспользовавшись функцией копирования формул и введя первоначально формулу « = В7 – В9» в ячейку В15.
28. В ячейку А16 введите название «Облагаемый доход».
29. Посчитайте облагаемый доход для всех месяцев, воспользовавшись формулой « = В10 + В15», введенной в ячейку В16.
30. В ячейку А17 введите название «Налог на доход предприятия, 22%».
31. Посчитайте налог на доход предприятия для всех месяцев, воспользовавшись формулой « = В16*0,22 », введенной в ячейку В17.
31. В ячейку А18 введите название «Чистый доход предприятия».
32. Посчитайте чистый доход предприятия для всех месяцев, воспользовавшись формулой « = В15 - В17», введенной в ячейку В18.
33. Отформатируйте документ, вызвав (левой кнопкой мыши) «Формат» и активизируя в нем режим «Автоформат». В появившейся таблице «Автоформат» выберите один из финансовых форматов в «Списке форматов:». После выбора нужного формата (левая кнопка мыши) зафиксируйте его, нажав кнопку «ОК».
34. Дооформите документ с помощью средств панели инструментов (подчеркивание, выделение жирным шрифтом, цвета шрифта, цвета заливки, внешние границы) по своему усмотрению.
35. Сдайте выполненную лабораторную работу преподавателю, получив подпись в рабочей тетради!
36. Закройте документ, предварительно сохранив его по заданному адресу – щелчок по значку “x” в панели задач “Microsoft Excel” (правый верхний угол), при необходимости подтвердите сохранение документа и выйдете из программы “Microsoft Excel” (снова щелчок по значку “x” в правом верхнем углу экрана).
Таблица к лабораторной работе №1
| A | B | C | D | E | F | G |
1 | Отчет о доходах и расходах предприятия |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
3 | Период отчетности: | январь | февраль | март | апрель | май | июнь |
4 |
|
|
|
|
|
|
|
5 | Валовая выручка | 150 000,00р. | 170 000,00р. | 190 000,00р. | 210 000,00р. | 230 000,00р. | 250 000,00р. |
6 | Налог на добавленную стоимость, 20% | 30 000,00р. | 34 000,00р. | 38 000,00р. | 42 000,00р. | 46 000,00р. | 50 000,00р. |
7 | Выручка от реализации, (B5-B6) | 120 000,00р. | 136 000,00р. | 152 000,00р. | 168 000,00р. | 184 000,00р. | 200 000,00р. |
8 |
|
|
|
|
|
|
|
9 | Затраты на производство продукции, | 84 600,00р. | 94 600,00р. | 104 600,00р. | 114 600,00р. | 124 600,00р. | 134 600,00р. |
10 | в том числе: заработная плата | 600,00р. | 600,00р. | 600,00р. | 600,00р. | 600,00р. | 600,00р. |
11 | арендная плата | 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. |
12 | аренда оборудования | 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. |
13 | прочие расходы | 80 000,00р. | 90 000,00р. | 100 000,00р. | 110 000,00р. | 120 000,00р. | 130 000,00р. |
14 |
|
|
|
|
|
|
|
15 | Валовая прибыль, (B7-B9) | 35 400,00р. | 41 400,00р. | 47 400,00р. | 53 400,00р. | 59 400,00р. | 65 400,00р. |
16 | Облагаемый доход, ( B10+B15) | 36 000,00р. | 42 000,00р. | 48 000,00р. | 54 000,00р. | 60 000,00р. | 66 000,00р. |
17 | Налог на доход предприятия, 22% | 7 920,00р. | 9 240,00р. | 10 560,00р. | 11 880,00р. | 13 200,00р. | 14 520,00р. |
18 | Чистый доход предприятия, (B15-B17) | 27 480,00р. | 32 160,00р. | 36 840,00р. | 41 520,00р. | 46 200,00р. | 50 880,00р. |
Лабораторная работа №2.
Графическое отображение данных о доходах и расходах предприятия
в электронной таблице Microsoft Excel.
1. Войдите в электронную таблицу “Microsoft Excel” и по адресу «Мои документы / № курса_№ группы_ФИО / Лаб_1» откройте данные лабораторной работы №1.
2. В ячейку А1 впишите заглавие: «Графическое представление отчета о доходах и расходах предприятия». Так как заглавие целиком не умещается в ячейке А1, то возможно увеличить пространство, объединив несколько ячеек (А1 ¸ E1). Для этого необходимо активизировать объединяемые ячейки, проведя по ним курсором при нажатой левой кнопке мыши. После активизации ячеек (ячейки, не содержащие введенное заглавие, зачернены) необходимо щелкнуть левой кнопкой мыши по пиктограмме «Объединить и поместить в центре» («¬a®») панели инструментов.
3. Сохраните информацию по адресу: Мои документы / № курса_№ группы_ФИО / Лаб_2.
Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных – не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”) панели инструментов.
4. Подготовка данных для графического отображения зависимостей.
Активизируйте ячейки В3 ¸ В18, проведя по ним курсором при нажатой левой кнопке мыши. Затем войдите в меню «Вставка» панели задач и щелкнуть левой кнопкой мыши по режиму «Столбцы». После этого столбец В освобождается от данных, которые начинаются со столбца С.
5. Построение графика зависимости «Чистого дохода предприятия» от «Периода отчетности».
Для этого сначала введите в память данные, откладываемые по оси «абсцисс» («Период отчетности»), затем – оси «ординат» («Чистый доход предприятия») графика.
Для ввода данных оси «абсцисс» активизируйте ячейки А3 ¸ H3, проведя по ним курсором при нажатой левой кнопке мыши. Для ввода данных оси «ординат» нажмите левую кнопку “Ctrl” консоли (клавиатуры) и, не отпуская ее, активизируйте ячейки А18 ¸ H18, проведя по ним курсором при нажатой левой кнопке мыши. Обратите внимание на то, что в таблице одновременно активизированы две соответствующих строки (их числовые данные затемнены)! В противном случае (активизирована лишь последняя по очередности ввода строка) необходимо повторить ввод данных.
После ввода необходимых данных щелкните левой кнопкой мыши по пиктограмме «Мастер диаграмм» («изображение цветной гистограммы») на панели инструментов. В открывшемся меню «Мастер диаграмм (шаг 1 из 4:)» выберите режим «Стандартные» (автоматически устанавливается по умолчанию) и щелкните левой кнопкой мыши по диаграмме «График» опции «Тип:». Справа в опции «Вид:» темной заливкой будет отмечен вид графика, а внизу будет дано его краткое описание. После этого щелкните левой кнопкой мыши по кнопке «Готово» (нижний правый угол меню «Мастер диаграмм»). Сразу же появляется уменьшенное окно с отображаемой диаграммой. Наличие черных квадратиков по сторонам и углам рамки окна (границ диаграммы) говорит о том, что диаграмма активизирована! Поместите курсор в область диаграммы на чистое поле, и он примет вид стрелки. При этом под стрелкой появится подпись «Область диаграммы». Последовательно наведите стрелку на различные части области диаграммы и ознакомьтесь с появляющимися подписями: темное поле диаграммы («Область построения диаграммы», цифры у оси «ординат» («Ось значений»), подписи под осью «абсцисс» («Ось категорий»), прямоугольник справа от области построения диаграммы («Легенда»), подпись над областью построения диаграммы («Заголовок диаграммы»).
Для активизации основного документа электронной таблицы установите курсор вне области диаграммы и щелкните левой кнопкой мыши.
Внимание! Запишите в тетрадь последовательность действий пункта 5, зарисуйте диаграмму и зафиксируйте все обозначения на ее области.
6. Буксировка диаграммы в нужную позицию основного документа.
Активизируйте диаграмму, наведя курсор на свободное поле внутри ее рамки и нажав левую кнопку мыши (появляются черные квадратики по сторонам и углам рамки диаграммы). Снова наведите курсор на свободную область диаграммы и нажмите на левую кнопку мыши (курсор примет вид четырех стрелок, направленных в разные стороны). Не отпуская левую кнопку мыши, переместите диаграмму под таблицу с данными. После этого, наведя курсор на квадратик правой границы рамки диаграммы (курсор примет вид обоюдонаправленной горизонтальной стрелки) и нажав на левую кнопку мыши, увеличьте размер диаграммы до приемлемого (названия месяцев под осью абсцисс должны занять горизонтальное положение).
7. Создание надписей на осях диаграммы.
Щелкните правой кнопкой мыши по свободному полю области диаграммы. В появившемся «висящем» меню выберите опцию «Параметры диаграммы», щелкнув по ней левой кнопкой мыши. В открывшейся таблице «Параметры диаграммы» в режиме «Заголовки» (устанавливается по умолчанию) поместите курсор (щелчок левой кнопкой мыши) в окно под подписью «Ось X (категорий):» и напишите «Период отчетности». Аналогично под подписью «Ось Y (ось значений):» напишите «Чистый доход предприятия», а под подписью «Название диаграммы» перепишите «Динамика чистого дохода». Щелкните левой кнопкой мыши по кнопке “ОК” (правый нижний угол) таблицы «Параметры диаграммы».
8. Редактирование надписей диаграммы.
Активизируйте надпись «Динамика чистого дохода» диаграммы, наведя на нее курсор и щелкнув левой кнопкой мыши (появляется серая рамка). Щелкнув один раз левой кнопкой мыши по надписи, установите курсор (вертикальная мигающая черточка «ê») в ее конец и добавьте слово «предприятия» в заглавие диаграммы. Для перемещения курсора в процессе редактирования заглавия возможно также использовать стрелки (¬ - влево, ® - вправо) консоли.
Активизируйте надпись «Период отчетности», наведите курсор на границу серой рамки и, нажав левую кнопку мыши, “отбуксируйте” надпись к правому концу оси X.
Наведите курсор на надпись «Чистый доход предприятия» и щелкните правой кнопкой мыши. В появившемся «висящем» меню щелкните левой кнопкой мыши по опции «Формат оси …». В открывшейся таблице «Формат оси» активизируйте режим «Выравнивание» и в его опции «Ориентация» (рисунок полуокружности с угловыми делениями) наведите курсор на отметку, соответствующую “0 градусов” и щелкните левой кнопкой мыши. После этого щелкните левой кнопкой мыши по кнопке “ОК” таблицы «Формат оси». “Отбуксируйте” название «Чистый доход предприятия» к верхнему концу оси Y, активизируя надпись, наведя курсор на границу ее рамки и нажав левую кнопку мыши. Добавьте размерность оси Y, вставив “р.” после запятой в название «Чистый доход предприятия».
Активизируйте надпись, соответствующую легенде диаграммы (справа от области ее построения). С помощью курсора и мыши уменьшите область «Легенда» и “отбуксируйте” ее в “приемлемую позицию”.
Активизируйте область построения диаграммы, наведя курсор на темное поле с графиком и щелкнув левой кнопкой мыши. Установите необходимый размер области построения диаграммы и переместите ее в приемлемое положение внутри области диаграммы.
Внимание! Как “приемлемые положения”, так и размеры областей и надписей диаграммы выбираются из соображений удобства ее представления.
Для установления размера подписей оси категорий (X) и оси значений (Y) наведите курсор на соответствующую подпись, активизируйте (щелкнув правой кнопкой мыши) таблицу «Формат оси» и в ней выберите режим «Шрифт», в котором с помощью опции «Размер» установите приемлемый размер шрифта. Аналогично установите размер заголовка диаграммы.
9. Изменение типа диаграммы.
Расположите курсор в свободном месте области диаграммы и щелкните правой кнопкой мыши. В появившемся «висящем» окне выберите опцию «Тип диаграммы …», а в открывшейся таблице «Тип диаграммы» в режиме «Стандартные» выберите тип: «Гистограмма». Сравните ранее построенный графический и новый гистограммный типы диаграмм. Оставьте наиболее тот, который с Вашей точки зрения наиболее эффектно представляет данные отчета.
10. Размещение нескольких зависимостей на одной диаграмме.
По аналогии с п. 5 последовательно (используя кнопку “Ctrl” консоли) введите данные, относящиеся к статьям: «Период отчетности», «Затраты на производство продукции», «Чистый доход предприятия». С помощью мастера диаграмм постройте соответствующие графики, расположите их по аналогии с пп. 6-8 под последней диаграммой, создайте и отредактируйте все надписи. Выберите тип диаграммы, наиболее эффектно представляющий несколько зависимостей.
11. Построение зависимости «Чистого дохода предприятия» от «Затрат на производство продукции».
Переместите надпись «Чистый доход предприятия» из ячейки А18 в ячейку В18 (активизируйте ячейку А18 – наведите курсор на любую границу ячейки – переместите ячейку при нажатой левой кнопке мыши). Активизируйте ячейки В9 ¸ H9, а затем В18 ¸ H18 (не забывайте использовать кнопку “Ctrl” консоли). С помощью мастера диаграмм постройте соответствующую зависимость, расположите ее по аналогии с пп. 6-8 под последней диаграммой, создайте и отредактируйте все надписи. Выберите тип диаграммы, наиболее эффектно представляющий исследуемую зависимость. Отобразите на диаграмме значения всех ее точек (щелчок правой кнопкой мыши по графику - активизация левой кнопкой мыши опции «Формат рядов данных» - вызов режима «Подписи данных» - активизация кнопки «Значения»).
12. Сдайте выполненную лабораторную работу преподавателю, получив подпись в рабочей тетради!
13. Закройте документ, предварительно сохранив его по заданному адресу.
Таблица к лабораторной работе №2
Графическое представление отчета о доходах и расходах предприятия |
|
|
|
|
| ||
|
|
|
|
|
|
|
|
Период отчетности: |
| январь | февраль | март | апрель | май | июнь |
|
|
|
|
|
|
|
|
Валовая выручка |
| 150 000,00р. | 170 000,00р. | 190 000,00р. | 210 000,00р. | 230 000,00р. | 250 000,00р. |
Налог на добавленную стоимость, 20% |
| 30 000,00р. | 34 000,00р. | 38 000,00р. | 42 000,00р. | 46 000,00р. | 50 000,00р. |
Выручка от реализации, (B5-B6) |
| 120 000,00р. | 136 000,00р. | 152 000,00р. | 168 000,00р. | 184 000,00р. | 200 000,00р. |
|
|
|
|
|
|
|
|
Затраты на производство продукции, |
| 84 600,00р. | 94 600,00р. | 104 600,00р. | 114 600,00р. | 124 600,00р. | 134 600,00р. |
в том числе: заработная плата |
| 600,00р. | 600,00р. | 600,00р. | 600,00р. | 600,00р. | 600,00р. |
арендная плата |
| 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. | 2 500,00р. |
аренда оборудования |
| 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. | 1 500,00р. |
прочие расходы |
| 80 000,00р. | 90 000,00р. | 100 000,00р. | 110 000,00р. | 120 000,00р. | 130 000,00р. |
|
|
|
|
|
|
|
|
Валовая прибыль, (B7-B9) |
| 35 400,00р. | 41 400,00р. | 47 400,00р. | 53 400,00р. | 59 400,00р. | 65 400,00р. |
Облагаемый доход, ( B10+B15) |
| 36 000,00р. | 42 000,00р. | 48 000,00р. | 54 000,00р. | 60 000,00р. | 66 000,00р. |
Налог на доход предприятия, 22% |
| 7 920,00р. | 9 240,00р. | 10 560,00р. | 11 880,00р. | 13 200,00р. | 14 520,00р. |
Чистый доход предприятия, (B15-B17) | Чистый доход предприятия, (B15-B17) | 27 480,00р. | 32 160,00р. | 36 840,00р. | 41 520,00р. | 46 200,00р. | 50 880,00р. |
Рисунки к лабораторной работе №2
Лабораторная работа №3.
Решение обратной задачи отчетности
средствами электронной таблицы Microsoft Excel.
1. Войдите в электронную таблицу “Microsoft Excel” и по адресу «Мои документы / № курса_№ группы_ФИО / Лаб_1» откройте данные лабораторной работы №1.
2. В ячейку А1 впишите заглавие: «Подбор параметров при отчете о доходах и расходах предприятия». Поместите заглавие в нескольких ячейках, активизируя их левой кнопкой мыши и объединяя с помощью пиктограммы «Объединить и поместить в центре» («¬a®») панели инструментов.
3. Сохраните информацию по адресу: Мои документы / № курса_№ группы_ФИО / Лаб_3.
Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных – не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”) панели инструментов.
4. Постройте на одной диаграмме графики зависимостей «Прочие расходы» и «Чистый доход предприятия» от «Периода отчетности». Расположите диаграмму под таблицей данных, создайте и отредактируйте все надписи (название диаграммы – «Динамика “Прочих расходов” и “Чистого дохода” предприятия», название оси X – «Период отчетности», Y – «Значения») в соответствии с пп. 6,7,8 лабораторной работы №2.
5. Нахождение «Прочих расходов» данного месяца для достижения требуемого «Чистого дохода» этого же месяца.
Допустим, что нам необходимо получить в мае месяце чистый доход в размере 70000 р. Как при этом пересчитать затраты на производство продукции, непосредственно изменив статью «Прочие расходы»?
Активизируйте ячейку F18 и войдите в режим «Подбор параметра…» меню «Сервис» панели задач (щелчок левой кнопкой мыши). В открывшемся окне «Подбор параметра» в строке «Значение:» наберите цифру 70000 (предварительно установив курсор редактирования текста «мигающий символ “ï”»), затем установите курсор редактирования текста в строку «Изменяя значение ячейки:», наведите курсор “Excel” (большой белый крест) на ячейку F13 и щелкните левой кнопкой мыши. Нажмите кнопку ”ОК” и запустите цикл итерационных вычислений. Если решение найдено (см. появившееся сообщение), снова нажмите кнопку ”ОК”.
Внимание! Если окно «Подбор параметра» закрывает нужные ячейки таблицы данных, то наведите курсор “Excel” (большой белый крест) на верхнюю синюю полоску окна и, удерживая левую кнопку мыши в нажатом положении, «отбуксируйте» окно в удобную позицию. Другой способ – навести курсор на красную кнопку соответствующей строки окна «Подбор параметра» и щелкнуть левой кнопкой мыши, временно уменьшая окно. Для придания окну прежнего размера необходимо снова воспользоваться красной кнопкой.
Обратите внимание на изменение формы графиков диаграммы.
6. Нахождение статей расходов и дохода предшествующих месяцев для достижения требуемого «Чистого дохода» данного месяца.
Измените правило образования статьи «Прочие расходы», предварительно удалив все значения ячеек C13 ¸ G13 и введя затем в ячейку C13 формулу «В13+10000». Скопируйте формулу во все ячейки D13 ¸ G13. Теперь значение прочих расходов каждого месяца зависят от расхода начального месяца (январь).
Проделайте все операции п. 5, установив значение чистого дохода в мае месяце в 100 000 р., изменяя прочие расходы в январе месяце.
Обратите внимание - как изменились статьи расходов и дохода всех месяцев.
7. Отображение другой совокупности графиков на диаграмме.
Откройте “висящее” меню «Формат области диаграммы», наведя курсор на свободное поле области диаграммы и щелкнув правой кнопкой мыши. Выберите в меню режим «Исходные данные», щелкнув по нему левой кнопкой мыши, и нажмите красную кнопку в строке «Диапазон:» появившегося окна «Исходные данные». «Поднявшись с помощью вертикальной полосы прокрутки (правая сторона экрана) вверх сделайте доступным обзору все данные. Используя клавишу консоли “Ctrl”, введите последовательно данные, соответствующие: «Периоду отчетности», «Налогу на добавленную стоимость», «Прочим расходам», «Чистому доходу предприятия». Снова нажмите красную кнопку окна «Исходные данные - Диапазон:», которое размещается в правом верхнем углу экрана. Нажмите кнопку “ОК” раскрывшегося окна «Исходные данные».
Обратите внимание на иную совокупность графиков диаграммы.
8. Для закрепления навыка решения обратных задач решите самостоятельно следующую задачу.
На какие сроки (в годах) нужно положить вклады в размерах: 1000 р., 2000 р., 3000 р., 4000 р., 5000 р., в банк под годовые процентные ставки 5%, 10%, 15%, 20%, 25% чтобы получить суммы вкладов в 2000 р., 4000 р., 6000 р., 8000 р., 10000 р. соответственно? Сроки вычислить с точностью до одного года. Построить диаграмму зависимостей срока вклада от процентной ставки.
Для решения составьте шаблон, заказав соответствующий формат ячеек.
| А | B | C | D | E | F |
1 | Размер вклада | 1 000,00р. | 2 000,00р. | 3 000,00р. | 4 000,00р. | 5 000,00р. |
2 | Процентная ставка | 5% | 10% | 15% | 20% | 25% |
3 | Срок вклада | 5 | 5 | 5 | 5 | 5 |
4 | Сумма вклада | =B1*(1+B2)^B3 |
|
|
|
|
Обратите внимание на то, что первоначально все сроки вкладов установлены равными, например, 5 годам. Сумма вкладов вычисляется по формуле сложного процента. Функциональная зависимость ячеек необходима для решения обратной задачи с помощью режима «Подбор параметра …» меню «Сервис» панели задач “Excel”.
Внимание! Поскольку Вы создаете шаблон с новой строки “Excel”, продолжая лабораторную работу, то реальные номера строк шаблона будут другими.
Размножьте формулу ячейки B4 в ячейки С4 ¸ F4. Далее следуйте логике п.5.
9. Сдайте выполненную лабораторную работу преподавателю, получив подпись в рабочей тетради!
10. Закройте документ, предварительно сохранив его по заданному адресу.
Диаграмма к лабораторной работе №3
Таблица к обратной задаче лабораторной работы №3
Размер вклада |
| 1 000,00р. | 2 000,00р. | 3 000,00р. | 4 000,00р. | 5 000,00р. |
Процентная ставка |
| 5% | 10% | 15% | 20% | 25% |
| Срок вклада | 14 | 7 | 5 | 4 | 3 |
Сумма вклада |
| 2 000,00р. | 4 000,00р. | 6 000,00р. | 8 000,00р. | 10 000,00р. |
Лабораторная работа №4.
Оценка потоков платежей
средствами электронной таблицы Microsoft Excel.
1. Введите в ячейку А1 название: «Лабораторная работа №4», а в ячейку А2 – название первого раздела лабораторной работы: «Анализ операций с элементарными потоками платежей».
2. Сохраните «лаб_4» по заданному адресу.
3. Создайте шаблон для решения типовых задач по анализу элементарных потоков платежей. Для того, чтобы проводить запись в одной ячейке в несколько строк, пользуйтесь комбинацией клавиш Alt + Enter (при нажатой клавише Alt нажмите клавишу Enter). В шаблоне заполните лишь ячейки А, как показано ниже !
Таблица 1
| A | B | C |
1 | Лабораторная работа №4 |
|
|
2 | Анализ операций с элементарными потоками платежей |
| |
3 |
|
|
|
4 | Исходные данные: |
|
|
5 |
|
|
|
6 | Годовая процентная ставка, r = | 10.00% |
|
7 | Количество начислений в году, m = | 4 |
|
8 | Срок проведения операции (лет), n = | 5 |
|
9 | Начальное значение, PV = | 1 000,00 р. |
|
10 | Будущее значение, FV = | 3 000,00 р. |
|
11 |
|
|
|
12 | Результаты вычислений: |
|
|
13 |
|
|
|
14 | Будущее значение, FV = | =БС(B6/B7;B8*B7;0;-B9;[0]) | 1 638,62р. |
15 | Периодическая процентная ставка, r = | =СТАВКА(B8*B7;0;-В9;B10; ;[0]) | 5,65% |
16 | Годовая процентная ставка, r' = | =B15*B7 | 22,59% |
17 | 44,49 | ||
18 | Современная величина, PV = | =ПС(B6/B7;B8*B7;0;B10;[0]) | -1 830,81р |
Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”) панели инструментов.
4. Введите в ячейки B6 ¸ B10 соответствующие числовые данные, показанные в таблице 1 (не забывайте задавать соответствующий формат ячеек с помощью правой кнопки мыши).
5. Стандартные функции, приведенные в ячейках B14 ¸ B18 позволяют автоматизировать анализ операций и вызываются с помощью пиктограммы “fx” (“Вставка функции”) панели инструментов Microsoft Excel. Для выполнения какой либо операции необходимо активизировать ячейку, где необходимо разместить результат операции, например, B14, вызвать функцию, в открывшемся окне “Мастер функций – шаг 1 из 2” в разделе “Категория” выбрать “Финансовые”. С помощью “Мастер функций – шаг 1 из 2”, проведите автоматизированные расчеты в ячейках B14 ¸ B18, используя функции и аргументы, показанные в таблице 1. В таблице 1 в квадратных скобках (“[]”) стоят аргументы, ввод которых не обязателен. Знак минус «-» у аргумента соответствует оттоку денежных средств. Выбор соответствующей функции проводите в разделе: “Функция” вызванного окна “Мастер функций – шаг 1 из 2”. Выбрав функцию (щелчок левой кнопкой мыши), обратите внимание на ее формат (смысл аргументов) и действие, которые приводятся в нижней части окна. Для активизации функции нажмите на кнопку “ОК”. Введите в соответствующие строки открывшегося окна адреса ячеек, в которых расположены значения необходимых аргументов. Для этого поместите курсор (щелчок левой кнопкой мыши) в соответствующую строку напротив названия аргумента и активизируйте с помощью курсора ячейку таблицы Microsoft Excel, в которой находятся числовые данные. Аргумент «выплаты» принимается равным «0», что соответствует отсутствию дополнительного пополнения вкладов. Под аргументом «тип» понимается логический аргумент, принимающий значения «0» (начало периода при начислении процентов), либо «1» (конец периода). Не забывайте вводить знак «минус» для аргумента «нз». При необходимости корректируйте вводимые адреса ячеек с помощью операций “/”, “*”, как показано в таблице 1.
6. Избавьтесь от возможных ошибок ввода данных, используя логические функции ЕСЛИ, ИЛИ и И в разделе “Категория” окна “Мастер функций – шаг 1 из 2”. Введите в ячейки С14 ¸ С18 формулы, приведенные в таблице 2.
Таблица 2.
Ячейка | Формула |
С14 | =ЕСЛИ (B6*B7*B8*B9=0; 0; БС(B6/B7;B8*B7;0;-B9;[0])) |
С15 | =ЕСЛИ(ИЛИ(B7*B8*B9*B10<>0;B9*B10<>0);СТАВКА (B8*B7;0;-B9;B10;[0]);0) |
С16 | =ЕСЛИ (И (В7<>0;B15<>0;B7*B15;0) |
С17 | =ЕСЛИ (B6*B7*B9*B10<>0;);КПЕР(B6/B7;0;-B9;B10;[0]);0) |
С18 | =ПС(B6/B7;B8*B7;0;B10;[0]);0) *И(B6*B7*B8*B10<>0) |
Логические функции имеют следующие форматы:
=ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь),
=ИЛИ (логическое 1; логическое 2; … ; логическое N), =И (логическое 1; логическое 2; …),
Функция ЕСЛИ возвращает значение второго или третьего ее аргумента, если ее первый аргумент принимает значение «истина» или «ложь». Функция ИЛИ возвращает 1 («истина»), если выполняется хотя бы одно из N перечисленных логических условий, и 0 («ложь») в противном случае. Функция И возвращает «истина» если все ее аргументы имеют значение «истина».
7. Приведенные в таблице 2 функции являются вложенными, т.к. одни (логические) функции используют в качестве аргумента другие (финансовые) функции. Для автоматизации их набора используйте панель формул, щелкнув по кнопке со знаком равенства (“=”) в строке формул. Откройте список 10-ти используемых последний раз формул, щелкнув по кнопке “Ñ” (слева от красного знака “Х”). Просмотрите список и, если в нем нет требуемых функций, нажмите кнопку “Отмена” панели формул. Для ввода формул ЕСЛИ, ИЛИ, И в панель формул вызовите их в какие либо ячейки, используя пиктограмму “fx” (“Вставка функции”) панели инструментов Microsoft Excel. После вызова каждой формулы не забывайте подтверждать ее ввод – кнопка “ОК”. При проявлении списка аргументов данной формулы нажмите кнопку “Отмена”.
8. Активизируйте ячейку С14 и откройте панель формул. Выберите логическую функцию ЕСЛИ. Введите согласно таблице 2 ее первые два аргумента в соответствующие строки. В качестве третьего аргумента выберите ячейку B14.
9. Используя панель формул, введите в ячейку С15 функцию ЕСЛИ. Активизируя первый аргумент данной функции, снова используйте панель формул и в ее списке выберите формулу ИЛИ. Введите все аргументы данной функции согласно таблице 2. Щелкните в строке формул по внешней функции ЕСЛИ и продолжите ввод остальных ее аргументов. Заполните все остальные ячейки С16 ¸ С18.
10. Сдайте выполненную лабораторную работу преподавателю.
|
| |||||||||
| A | B | C | D | E | F | G | H |
| |
1 | Определить сумму выплат для разных процентных ставок, |
|
|
| ||||||
2 | при условии что сумма вклада известна. |
|
|
|
|
| ||||
3 |
|
|
|
|
|
|
|
|
| |
4 |
| Размер вклада | 188 444,74р. |
| Процент | Коэф. | Сумма выплат |
|
| |
5 |
|
|
|
| 3,00% | 1,8 | 340 352,16р. |
|
| |
6 |
| Срок вклада | 20 |
| 3,25% | 1,9 | 357 260,69р. |
|
| |
7 |
|
|
|
| 3,50% | 2,0 | 374 965,25р. |
|
| |
8 |
| Процентная ставка | 3% |
| 3,75% | 2,1 | 393 501,26р. |
|
| |
9 |
|
|
|
| 4,00% | 2,2 | 412 905,63р. |
|
| |
10 |
| Коэффициент наращивания | 1,8 | =(1+C8)^C6 | 4,25% | 2,3 | 433 216,81р. |
|
| |
11 |
|
|
|
| 4,50% | 2,4 | 454 474,83р. |
|
| |
12 |
|
|
|
| 4,75% | 2,5 | 476 721,41р. |
|
| |
13 |
| Сумма выплат | 340 352,16р. | =C10*C4 | 5,00% | 2,7 | 500 000,00р. |
|
| |
14 |
|
|
|
| 5,25% | 2,8 | 524 355,84р. |
|
| |
15 | 1. | Составить список процентных ставок (E5:E19) | 5,50% | 2,9 | 549 836,06р. |
|
| |||
16 | 2. | F5=C10 |
|
| 5,75% | 3,1 | 576 489,69р. |
|
| |
17 |
| G5=C13 |
|
| 6,00% | 3,2 | 604 367,81р. |
|
| |
18 | 3. | Выделить даипазон ячеек (E5 : G19) | 6,25% | 3,4 | 633 523,60р. |
|
| |||
19 | 4. |
|
|
| 6,50% | 3,5 | 664 012,38р. |
|
| |
20 | 5. | Установить курсор в поле Подставлять значения по строкам |
|
|
|
| ||||
21 |
| и выделить ячейку C8 |
|
|
|
|
|
| ||
22 | 6. | OK |
|
|
|
|
|
|
| |
23 |
|
|
|
|
|
|
|
|
| |
24 | Определить сумму выплат для разных процентных ставок и сроков вклада |
|
| |||||||
25 | при условии что сумма вклада известна. |
|
|
|
|
| ||||
26 |
| Размер вклада | 188 444,74р. |
| 340352,16 | 5 | 10 | 15 |
| |
27 |
|
|
|
| 3,00% | 218 459,10 | 253 253,97 | 293 590,8р. |
| |
28 |
| Срок вклада | 20 |
| 3,25% | 221 123,21 | 259 468,49 | 304 463,28 |
| |
29 |
|
|
|
| 3,50% | 223 813,24 | 265 819,92 | 315 710,68 |
| |
30 |
| Процентная ставка | 3% |
| 3,75% | 226 529,39 | 272 310,93 | 327 344,92 |
| |
31 |
|
|
|
| 4,00% | 229 271,84 | 278 944,25 | 339 378,33 |
| |
32 |
| Коэффициент | 1,806 | =(1+C32)^C28 | 4,25% | 232 040,79 | 285 722,64 | 351 823,61 |
| |
33 |
|
|
|
| 4,50% | 234 836,43 | 292 648,92 | 364 693,80 |
| |
34 |
|
|
|
| 4,75% | 237 658,95 | 299 725,95 | 378 002,35 |
| |
35 |
| Сумма выплат | 340 352,16р. | =C32*C26 | 5,00% | 240 508,55 | 306 956,63 | 391 763,08 |
| |
36 |
|
|
|
| 5,25% | 243 385,41 | 314 343,92 | 405 990,24 |
| |
37 | 1. | Составить список процентных ставок (E27:E41) | 5,50% | 246 289,74 | 321 890,84 | 420 698,46 |
| |||
38 | 2. | E26=C35 |
|
| 5,75% | 249 221,73 | 329 600,44 | 435 902,80 |
| |
39 |
| Составить список сроков вкладов (F26:H26) | 6,00% | 252 181,57 | 337 475,83 | 451 618,79 |
| |||
40 | 3. | Выделить даипазон ячеек (E26: H41) | 6,25% | 255 169,47 | 345 520,17 | 467 862,36 |
| |||
41 | 4. |
|
|
| 6,50% | 258 185,63 | 353 736,68 | 484 649,91 |
| |
42 | 5. | Установить курсор в поле Подставлять значения по строкам |
|
|
|
| ||||
43 |
| и выделить ячейку C30 |
|
|
|
|
|
| ||
44 | 6. | Установить курсор в поле Подставлять значения по столбцам |
|
|
| |||||
45 |
| и выделить ячейку C28 |
|
|
|
|
|
| ||
46 | 7. | OK |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
| |
Лабораторная работа №6
Исчисление характеристик аннуитетов
средствами электронной таблицы Microsoft Excel.
1. Войдите в электронную таблицу “Microsoft Excel” и задайте альбомный формат
(Файл ® Параметры страницы ® Ориентация ® альбомная).
2. В ячейку А1 впишите заглавие: «Анализ аннуитетов».
3. Сохраните работу (“Лаб_6”) по соответствующему адресу.
Решите следующие задачи, используя финансовые функции ПЛТ (норма;кпер;нз; [бс];[тип]), БС (норма;число_периодов;выплата;нз;[тип]).
Задача 1.
4. Необходимо определить размер периодического платежа (CF) для создания фонда в размере FV = 50 000 р. в течении n = 5 лет для двух типов начисления процентов банком - в начале (тип = 1) и в конце каждого периода (тип = 0). При этом годовая процентная ставка по депозиту составляет r = 10%, а начальной суммы на депозите (PV) не было. Соответствующая таблица должна выглядеть следующим образом:
| А | B | C |
1 | Анализ аннуитетов |
|
|
2 |
| Задача 1. | Задача 2. |
3 | Исходные данные: |
|
|
4 |
|
|
|
5 | Годовая процентная ставка, r = | 10,00% |
|
6 | Количество начислений в году, m = | 1 |
|
7 | Срок проведения операции, n = | 5 |
|
8 | Начальное значение, PV = |
|
|
9 | Будущее значение, FV = | 50 000,00 р |
|
10 | Периодический платеж, CF = | . |
|
11 |
| . |
|
12 |
|
|
|
13 | Результаты вычислений: |
|
|
14 |
|
|
|
15 | Периодический платеж, СF(1) = | = ПЛТ(B5;B6*B7;; B9;1) |
|
16 | Периодический платеж, СF(0) = | = ПЛТ(B5;B6*B7;; B9;0) |
|
17 | Будущее значение, FV(1) = |
| = БС(…;1) |
18 | Будущее значение, FV(0) = |
| = БС(…;0) |
!!!Для копирования формул используйте абсолютную адресацию (с помощью символа “$”) в финансовых функциях.
Сравните результаты для разных типов начислений.
Задача 2.
5. Финансовая компания создает фонд для погашения обязательств путем помещения в банк суммы в 50 000 р. с последующим ежегодным пополнением суммами по 10 000 р. Ставка по депозиту равна 10% годовых. Какова будет величина фонда к концу 4-го года для разных типов начислений ?
Введите исходные данные в ячейки столбца С и воспользуйтесь функцией БС, разместив результаты вычислений в ячейках C17 и C18.
Задача 3.
6. Решите обратную задачу – с помощью функции ПЛТ вычислите величины периодических платежей CF(1) и CF(0), чтобы к концу 4-го года в банке накопились суммы FV(1) и FV(0), определенные в предыдущей задаче.
Результаты поместите в соответствующих ячейках столбца D и E.
Задача 4.
7. Корпорация планирует ежегодно в течение 10 лет делать отчисления по 5 000 р. для создания фонда выкупа своих облигаций. Средства помещаются в банк под 12% годовых. Какие суммы будут накоплены к концу срока операции для различных типов начислений ?
Решите задачу самостоятельно, поместив исходные данные и результаты в соответствующих ячейках столбца F.
Задача 5.
8. Составьте самостоятельно шаблон (в ячейках столбцов G¸ K) для решения задачи определения величины накопления (FV5) при произвольных потоках платежей: CF1 = 10 000 р., CF2 = 20 000 р., CF3= 30 000 р., CF4 = 40 000 р., CF5 = 50 000 р., поступающих в течение 5 лет по процентной ставке r = 10%. Для расчета воспользуйтесь формулой
,
которая для конкретных исходных данных задачи примет вид )*
.
9. Определите современную стоимость заданного потока платежей, воспользовавшись формулой
,
которая для конкретных исходных данных задачи примет вид )*
.
10. Вычислите эквивалентный платеж по аннуитету, воспользовавшись приведением заданного произвольного потока платежей к величине современной стоимости
или для исходных данных задачи )*.
)* Примечание: в пп. 8 ¸ 10 используйте математическую функцию СУММПРОИЗВ, подготовив предварительно массивы наращивания (1+r)5-t и дисконтирования (1+r)-t для t = 1,2,3,4,5.
11. Воспользовавшись функцией БС(r;5;-CF) и вычисленным в п.10 значением эквивалентного CF, проверьте значение величины накопления FV5, полученного в п. 8.
Сдайте выполненную лабораторную работу преподавателю.
Лабораторная работа №7.
Разработка плана погашения кредита
средствами электронной таблицы Microsoft Excel.
Разработайте план погашения кредита равными долями (амортизация долга) при условии, что кредит выдан в 10 000 р. на 5 лет под 12% годовых. При этом потоки платежей (аннуитеты) должны формироваться раз в конце каждого года.
1. Создайте сначала шаблон (рис. 1), первая часть которого предназначена для ввода условий (исходные данные), на основании которых получен кредит (PV, r, n). Предусмотрите также возможность выплат процентов m раз в году, а также различные типы начисления процентов: 1 – в начале (prenumerando), 0 - конце (postnumerando) каждого периода.
| A | B | C | D | E | F |
1 |
| План | погашения | кредита |
|
|
2 |
|
|
|
|
|
|
3 |
|
| Исходные | данные |
|
|
4 |
|
|
|
|
|
|
5 | Сумма кредита ( PV ) | Срок погашения ( n ) | Число выплат в году ( m ) | Процентная ставка ( r ) | Тип начисления ( 0 или 1 ) |
|
6 | 10 000,00 | 5 | 1 | 12% | 0 |
|
7 |
|
|
|
|
|
|
8 |
| Результаты | вычислений |
|
| |
9 |
|
|
|
|
|
|
10 | Величина | платежа (CF) = |
| Общее число | выплат (mn) = |
|
11 |
|
|
|
|
|
|
12 | Номер периода | Баланс на конец года | Основной Долг | Проценты | Накопленный долг | Накопленный процент |
13 | 1 |
|
|
|
|
|
14 | 2 |
|
|
|
|
|
15 | 3 |
|
|
|
|
|
16 | 4 |
|
|
|
|
|
17 | 5 |
|
|
|
|
|
Рис. 1. Схема шаблона.
Сохраните документ по заданному адресу !
2. Перед вводом формул во вторую часть шаблона (результаты вычислений) определите собственные имена соответствующих ячеек согласно таблице 1.
Ячейка | Имя |
A6 | Сумма |
B6 | Срок |
C6 | Выплат |
D6 | Ставка |
E6 | Тип |
Таблица 1. Имена ячеек шаблона.
Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных – не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”).
Собственные имена заменяют буквенно-цифровые обозначения ячеек и делают расчеты более наглядными. Данные символические имена могут быть использованы везде, где можно применить обычные адресные ссылки. Кроме того, присвоенные ячейка имена дают ссылку на них в формулах в виде абсолютной адресации, что важно при копировании формул.
При определении имен следует руководствоваться правилами:
- имя должно начинаться с буквы или символа “_”;
- использование пробелов в именах недопустимо, в качестве разделителей слов следует применять символ “_” (например, Число_выплат);
- длина имени не должна превышать 255 символов.
Существует несколько способов определения имен. Наиболее простой - использование окна имен, которое расположено в левой части строки ввода Excel. Для того, чтобы определить имя для ячейки, необходимо:
1) сделать ячейку активной (установить в нее указатель курсора – “ú” );
2) щелкнуть мышью по окну имен, установив в него указатель курсора;
3) ввести с клавиатуры требуемое имя и нажать клавишу Enter.
После выполнения указанных действий при активизации данной ячейки в окне имен всегда будет показано определенное для нее имя.
Задание имен можно также осуществлять в виде диалога, воспользовавшись пунктом Имя темы Вставка главного меню Excel.
3. Вторую часть шаблона, содержащую результаты вычислений, создайте с помощью формул, приведенных в таблице 2.
Ячейка | Формула |
С10 | = -ПЛТ ( Ставка/Выплат; Срок * Выплат; Сумма; Тип) |
F10 | = Срок * Выплат |
B13 | = Сумма –E13 |
C13 | = - ОСПЛТ (Ставка/Выплат; A13; Срок * Выплат; Сумма; ; Тип) |
D13 | = -ПРПЛТ (Ставка/Выплат; A13; Срок * Выплат; Сумма; ; Тип) |
E13 | = - ОБЩДОХОД (Ставка/Выплат; Срок * Выплат; Сумма; 1; A13; Тип) |
F13 | = - ОБЩПЛАТ (Ставка/Выплат; Срок * Выплат; Сумма; 1; A13; Тип) |
Таблица 2. Формулы шаблона.
Обратите внимание на то, что все функции заданы с отрицательным знаком. Это обеспечивает возможность ввода исходных данных и получения результатов вычислений в виде положительных величин. В противном случае необходимо изменять знаки у аргументов функций.
4. Скопируйте введенные формулы блока B13.F13 для получения результатов для всех номеров периода погашения кредита (путем нажатия левой клавишей мыши правого нижнего угла – курсор принимает вид “+” – активизированной копируемой ячейки или копируемого блока). Обратите внимание на то, что в копируемых формулах автоматически изменяются лишь адреса, заданные буквенно-числовыми обозначениями. Адреса, заданные именами (содержащие исходные данные), не изменяются. В противном случае было бы нужно использовать абсолютные ссылки на данные адреса с помощью знака “$”.
Сдайте выполненную лабораторную работу преподавателю.
Лабораторная работа №8.
Использование финансовых функций Excel
для расчета амортизационных расходов
Амортизация – процесс равномерного распределения стоимости производственных мощностей «списание» в течении предполагаемого срока их эксплуатации на отчетные периоды.
Для определения размеров амортизации за год необходимо знать следующее:
- срок эксплуатации;
- первоначальную стоимость - нетто-цена приобретения плюс все необходимые расходы по доставке, установке и подготовке объекта к работе;
- ликвидационную стоимость - стоимость скрапа, лома и других отходов, возникших при ликвидации и после предполагаемой их продажи;
- амортизируемая стоимость - разность между первоначальной и ликвидационной стоимостью.
Метод равномерного (прямолинейного) списания.
Амортизируемая стоимость объекта равномерно списывается (распределяется) в течение срока его службы. Схема начисления амортизации: (первоначальная стоимость объекта – ликвидационная стоимость) / число отчетных периодов эксплуатации объекта. Норма амортизации является постоянной. Функция Excel АПЛ (Вставка; Функция; Финансовые) возвращает сумму амортизации для каждого года эксплуатации.
Задача 1.
Предприятие приобретает оборудование за 58000 $. Срок службы – 8 лет. Составьте таблицу амортизационных отчислений методом равномерного списания, если ликвидационная стоимость оборудования равна 4000 $.
Решение.
Откройте Excel, озаглавьте лист 1 – пример 1. Введите исходные данные в ячейки А1:В4 как показано на рисунке 1. Присвойте имена ячейкам В1 – Стоимость; В2 – Период; В3 – Остаток; диапазону А7:А14 – Год.
В В7 введите формулу = АПЛ (Стоимость; Остаток; Период). Скопируйте формулу в диапазон В7:В14.
В С7 введите формулу = СУММ($В$7 В7:).
Формула использует комбинацию абсолютной – $В$7 и относительной – В7 ссылок. Это стандартный метод создания текущего итога. При копировании формулы абсолютная ссылка остается неизменной, а относительная изменяется.
Скопируйте формулу в С8:С14.
Постройте диаграмму амортизационных расходов, рассчитанных по методу равномерного начисления. Переименуйте лист как Пример 3.
Сохраните файл как Амортизация активов.
Рис.1. Начисление амортизационных расходов рассчитанных по методу равномерного начисления.
Методы ускоренного начисления.
В первые годы начисляется большая сумма, в последующие – оставшаяся часть их стоимости.
Метод учета полных лет службы (метод суммы цифр).
Сумма амортизационных расходов начисляется в соответствии с текущим значением активов. Если срок амортизации равен n лет, вычисляем сумму номеров лет Kn:
Kn = 1 + 2 + 3 + …= n*(n+1) / 2.
Величина амортизационных отчислений в i – м году вычисляется по формуле:
Ai = (S-Sост)*(n - i + 1)/ Kn, где S - первоначальная стоимость имущества, Sост – ликвидационная стоимость имущества.
Данный метод реализуется функцией Excel – АМГД.
Задача 2.
Решите задачу 1, используя метод учета полных лет службы, который реализуется функцией АМГД. Скопируйте на новый рабочий лист Пример1, файл Амортизация активов. Внесите необходимые коррективы и составьте таблицу амортизации. Переименуйте лист как Пример 2. Постройте диаграмму (см. рис. 2).
Амортизация по остаточной стоимости (метод уменьшающегося баланса).
Метод уменьшающегося остатка (declining-balance method) основан на том же принципе, что и метод суммы чисел. Хотя при этом методе может применяться любая твердая ставка, но чаще всего число процентов r, на которое следует снижать стоимость каждый год определяется как:
r = 1-(S / Sост)1/n, где Sост – ликвидационная стоимость имущества. Данный метод реализуется функцией Excel – ФУО.
Рис.2. Начисление амортизационных расходов с использованием функции АМГД
Задача 3 (а).
Решите задачу 1, используя метод уменьшающегося баланса, который реализуется функцией ФУО. Скопируйте на новый рабочий лист Пример2, файл Амортизация активов. Внесите необходимые коррективы и составьте таблицу амортизации. Переименуйте лист как Пример 3.
Обратите внимание на незначительную неточность, допущенную при использовании функции ФУО: в качестве текущего итога указано 53993,80$, а не 54000$. Это произошло вследствие того, что функция вычисляет постоянный коэффициент, который используется при начислении амортизационных расходов, который округляется до трех цифр.
Метод уменьшающегося баланса (неполный год).
В функцию ФУО могут быть внесены некоторые изменения: использован необязательный аргумент Месяц.
Задача 3 (б).
Решите задачу 1, если покупка оборудования сделана в июне (тогда начисление амортизационных расходов начнется с июля). Для начисления амортизационных расходов будет использована формула = ФУО(Стоимость; Остаток; Период; Год; 6). Здесь 6 – количество месяцев, в течение которых будет начисляться амортизация (с июля по декабрь). Обратите внимание, что период, в течение которого начисляется амортизация, начинается в середине первого года и заканчивается в середине шестого года. Результаты решения задач 3 (а) и 3 (б) представлены на рис. 3.
Амортизация по методу двойного уменьшающегося остатка (метод двойного списания).
Этот процесс обычно называется методом уменьшающегося остатка при удвоенной норме амортизации (double-declining-balance method). Данный метод позволяет значительно ускорить процесс начисления амортизационных расходов по сравнению с методом уменьшающегося баланса. Метод реализуется функцией Excel – ДДОБ.
Рис.3. Начисление амортизационных расходов с использованием функции ФУО
Задача 4.
Решите задачу 1, используя метод уменьшающегося баланса, который реализуется функцией ДДОБ, которая для вычисления данных, показанных на рис. 4, имеет следующий вид:
= ДДОБ (Стоимость; Остаток; Период; Год; 2).
Функция ДДОБ позволяет в качестве пятого аргумента использовать значение, отличное от 2. Например, 1,5.
Неточность, которая возникла из-за ошибки округления функции ДДОБ, более существенна, чем в задаче 3. Рассмотрим способ исправления ошибок округления.
Выделите ячейку С14, выберите команду: Сервис, Подбор параметра. Установите параметры окна, как показано на рис 5, нажмите кнопку Ок. Средство Подбор параметра откорректирует значение, на которое увеличивается ставка, что амортизационные отчисления будут равны разности стоимости активов и ликвидационной стоимости.
Рис.4. Начисление амортизационных расходов с использованием функции ДДОБ
Начисление амортизационных расходов по методу двойного процента со снижающегося остатка.
Функция ПУО для начисления амортизационных расходов является самой
гибкой из предлагаемых Excel. Формула для вычисления амортизации с использованием данной функции выглядит следующим образом:
= ПУО(Стоимость; Остаток; Период; Начальный_период;
Конечный _период; Коэффициент; Без_переключения)
Аргумент Коэффициент работает так же как и для функции ДДОБ. Аргументы Начальный_период и Конечный_период указывают период времени, в течение которого активы находятся в эксплуатации.
Например, чтобы получить размер амортизации активов в течение первого года пятилетнего периода эксплуатации, используют следующую формулу:
= ПУО(Стоимость; Остаток; 5; 0; 1; Коэффициент; Без_переключения).
Присвоение аргументу Без_переключения значения ЛОЖЬ, приводит к тому что функция переключается на метод равномерного начисления тогда, когда начисления по этому методу проходят быстрее чем по методу двойного процента со снижающегося остатка. Если присвоить аргументу Без_переключения значение ИСТИНА, то функция не будет переключаться на метод равномерного начисления.
Задача 5.
Решите задачу 1, используя функцию ПУО. Рассмотрите присвоение аргументу Без_переключения значений ИСТИНА и ЛОЖЬ. Функция ПУО для заполнения таблицы амортизации, имеет следующий вид:
= ПУО(Стоимость; Остаток; Период; Год-1; Год; Коэффициент; ЛОЖЬ) и = ПУО(Стоимость; Остаток; Период; Год-1; Год; Коэффициент; ИСТИНА).
Операции в Excel
Присвоение имен ячейкам; использование функций Excel;запись, копирование формул; создание диаграмм; копирование листов и присвоение им имен ;сохранение файлов; использование средства Подбор параметра Вопросы и задания
- Заполните в тетради следующую таблицу функций Excel по начислению амортизации:
Функция | Аргументы | Способ амортизации |
|
|
|
- Создайте в Excel с помощью формул функции АПЛ, ФУО, ДДОБ, ПУО и решите задачи 1–4 (для любого периода функцию ПУО можно представить в виде: = МИН((Стоимость-Остаток-ИтогПрошл), Буд*Коэффициент/Период), где ИтогПрошл – общие амортизационные расходы, начисленные за предыдущие периоды, Буд – значение Стоимость – ИтогПрошл)
- 200 руб.
- Лабораторная работа
- VBA
- Нет
- Excel
- Нет
На нашем сайте есть работы, которые включают в себя несколько задач. Если Вам необходима только одна или несколько задач из всей работы, то вам нет необходимости покупать работу целиком. Мы можем продать задачи по отдельности. Для этого обратитесь к нам удобным для Вас способом.
Также если вдруг какая-то работа будет не соответствовать описанию или вы найдете ошибку, то мы всегда готовы исправить проблему в обговорённые с Вами сроки.