Практическое занятие 6. Практическая работа Экономические расчеты в ms excel задание
 Скачать 2.54 Mb.
|
|
=ВПР(С4;$B$22:$C$27;2)*D4 (вкладка Формулы категория Ссылкиимассивы). Здесь С4 – искомое значение (стаж сотрудника), которое необходимо найти в крайнем левом столбце таблицы, определяемой диапазоном $B$22:$C$27, индекс 2 (Но- мер_столбца) определяет номер столбца таблицы, из которого будет использовано зна- чение (процент надбавки за стаж). С помощью маркера автозаполнения скопируйте формулу в оставшиеся ячейки диапа- зона. Для вычисления значений последнего столбца формулу задайте самостоятельно.  На листе Лист2, который переименуйте в Цены, сформируйте основную и вспомога- тельные таблицы по образцу:Для вычислений в основной таблице задайте формулы (вкладка Формулы категория Ссылкиимассивы): в ячейке D4 = ГПР(C4;$C$15:$G$20;3); в ячейке F4 = D4*ВПР(E4;$B$23:$C$25;2). При этом воспользуйтесь данными вспомогательных таблиц: для столбца Цена (в евро) используйте данные первой вспомогательной таблицы, для столбца Цена (в руб.) ис- пользуйте данные второй вспомогательной таблицы. Примечание. Для заполнения ячеек столбца D можно скопировать указанную формулу. Но для разных товаров эту формулу необходимо «подкорректировать». Для СВЧ во вспомогательной таблице нужно выбрать строку 3, для элек- трического миксера надо выбрать строку 5, для электрического чайника, кофеварки и утюга надо выбрать строки 6, 2 и 4 соответственно. Сохраните выполненные изменения. На листе Лист3, который назовите Повременная зарплата, создайте таблицу по образ- цу, приведенному на рисунке.  Оформите заголовки основной и вспомогательной таблиц, используя технологию объ- единения нескольких ячеек в одну. Для этого выделите диапазон ячеек, которые нужно объединить (вкладка Главная команда  ).Оформите внешний вид таблицы, используя возможности команды  на вкладке Главная.Содержимое ячеек В3:F4 представьте в денежном формате (вкладка Главная коман- да  Денежный).Ячейки, соответствующие строкам Разряд,Тариф,Премия,выделите разными цветами. Сдельная заработная зарплата зависит от длительности рабочего времени, от разряда рабочего и от размера премии. Все вычисления для работника Иванова В.А. определя- ются следующим образом: Зарплата в ячейке D9: = ГПР(C9;B$2:F$4;2)*B9; Премия в ячейке Е9: = ГПР(C9;B$2:F$4;3); Начислено задайте самостоятельно. Скопируйте данные формулы в оставшиеся ячейки столбцов с помощью маркера авто- заполнения. Содержимое ячеек D9:F13 представьте в денежном формате. Просуммируйте данные ячеек по столбцам. По данным последнего столбца таблицы создайте круговую диаграмму.  На листе Лист4 рассчитайте заработную плату работников с учетом увеличивающегося подоходного налога и наличия детей, если налог удерживается не со всей суммы зара- ботка, а только с той части, которая уменьшается на 1 400 руб. (налоговый вычет) на каждого ребенка. Создайте основную и вспомогательную таблицы по образцу, приве- денному на рисунке.  Сначала вычислите сумму, облагаемую налогом, и налог на нее. Затем найдите сумму на руки как разность дохода и налога на доход. При маленьком доходе сумма, облагае- мая налогом, может оказаться меньше нуля. Чтобы не допускать отрицательных значе- ний, используется функция вычисления максимального из двух значений – нуля и об- лагаемой суммы. Таким образом, если результат получается отрицательным, то сумма обложения полагается равной нулю. Налог вычисляется с помощью функции поиска, определяющей во вспомогательной таблице ближайшее меньшее значение к облагае- мой сумме. Вычисления размера суммы обложения налогом и размера самого налога в ячейках D10 и Е10 для работника Иванова В.А. определяются следующим образом: Сумма обложения = МАКС(0;C10 – B10*$B$5); Налог = ГПР(D10;$B$3:$F$4;2)*D10; Сумма на руки задайте самостоятельно. Скопируйте данные формулы в оставшиеся ячейки столбцов с помощью маркера авто- заполнения. Просуммируйте данные ячеек по столбцам. Содержимое ячеек D10:F15 представьте в денежном формате. По данным трех последних столбцов создайте линейчатую диаграмму.  Покажите выполненное задание преподавателю. ЗА Д А Н И Е 10. ДИСПЕТЧЕР СЦЕНАРИЕВ На диске D: в своей папке создайте папку Задание_13, в ней создайте файл MS ExcelИнвестиции. На листе Лист1, который назовите Скорость оборота, решите задачу: затраты по не- которому проекту составляют 700 000 000 руб. Ожидаемые доходы в течение последу- ющих 5 лет составят соответственно 70 000 000 руб., 90 000 000 руб., 300 000 000 руб., 250 000 000 руб., 300 000 000 руб. Оцените экономическую целесообразность проекта по скорости оборота инвестиции, если средняя рыночная норма дохода по аналогичным проектам составляет 12,7 %. Если внутренняя скорость оборота инвестиций окажется больше рыночной нормы доходности, то проект следует считать экономически целесо- образным, в противном случае проект должен быть отвергнут. Рассмотрите также сле- дующие варианты (затраты на проект представлены отрицательным числом): – 600 000 000; 50 000 000; 100 000 000; 200 000 000; 200 000 000; 300 000 000; – 650 000 000; 90 000 000; 120 000 000; 200 000 000; 250 000 000; 250 000 000; – 500 000 000; 100 000 000; 100 000 000; 200 000 000; 250 000 000; 250 000 000. Для этого создайте таблицу по образцу:  Для расчета будущей стоимости облигации по сложной процентной ставке использует- ся функция ВСД (вкладка Формулы категория Финансовые). В этой функции для данной задачи используется только аргумент Значения, один из которых обязательно является отрицательным. Если внутренняя скорость оборота инвестиций будет больше рыночной нормы доходности, то проект считается экономически целесообразным. В противном случае проект должен быть отвергнут. Функция задается следующим об- разом: =ВСД(В4:В9) (вкладка Формулы категория Финансовые). В ячейке С12 с помощью логической функции ЕСЛИ (вкладка Формулы категория Логические) введите условие, согласно которому в этой ячейке будет выводиться ком- ментарий о целесообразности проекта: если В12 > В10, то «Проект экономически целе- сообразен», в противном случае «Проект необходимо отвергнуть». Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сцена- рия выполните следующие действия: вкладка Данные сервис Анализ «что-если» Диспетчер сценариев Добавить. Введите необходимые данные, как это показано на рисунке.   После нажатия на кнопку Добавить… появляется окно, в котором можно внести новые значения для изменяемых ячеек.Аналогичным образом внесите данные всех вариантов инвестиций, которые назовите соответствующим образом (см. на рисунке).   После внесения всех вариантов инвестиций составьте итоговый отчет. Для этого нажмите кнопку Отчет… и в появившемся окне Отчет по сценарию выберите тип от- чета Структураи дайте ссылки на ячейки, в которых вычисляются результаты.После нажатия на кнопку ОК на соответствующем листе рабочей книги будет выведен отчет по сценариям.  Аналогичным образом составьте итоговый отчет в виде сводной таблицы.  На новом листе рабочей книги должен получиться отчет следующего вида:  Покажите выполненное задание преподавателю. ЗА Д А Н И Е 11. ИТОГОВОЕ ЗАДАНИЕ На диске D: в своей папке создайте папку ИТОГ. В ней создайте файл MSExcelЗара-ботнаяплата. На листе Листе_1, который назовите Зарплата, создайте таблицу по образцу:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||