Обработка данных с помощью средств MS Excel

Вариант 1

  Используя соответствующие финансовые функции, решите следующие задачи.

1. Определите, какая сумма окажется на счете, если вклад размером 900 руб. положен под 9 % годовых на 19 лет, а проценты начисляются ежеквартально.
2. Какая сумма должна быть выплачена, если 6 лет назад была выдана ссуда 1500 руб. под 15 % годовых с ежемесячным начислением процентов.
3. Взносы на сберегательный счет составляют 200 руб. в начале каждого года. Определите, сколько будет на счете через 7 лет при ставке 10 %.
4. Есть два варианта вложения средств в сумме 300 тыс. руб. в течении 4 лет: в начале каждого года под 26% и в конце каждого года под 38 % годовых. Определите, сколько денег окажется на счете для каждого варианта через 4 года.
5. Ссуда в 5000 руб. погашается ежемесячными платежами по 141,7 руб. Через сколько лет произойдет погашение ссуды, если годовая ставка процента 16 %.
6. Какую сумму необходимо положить на депозит под 16,5 %, чтобы через 3 года получить 44 тыс. руб. при полугодовом начислении процентов.
7. По сертификату, погашаемому через 3 года выплатой в 250 тыс. руб., начисляются проценты раз в полгода. Определите цену продажи, если номинальная ставка 38 %.

  8. Определите необходимую сумму  текущего вклада, чтобы через  12 лет он достиг 5000 руб., если процентная  ставка по нему составляет 12 %.

  9. Рассматриваются два варианта покупки дома: заплатить сразу 100000 руб. или платить в рассрочку в течении 15 лет по 940 руб. ежемесячно. Какой вариант выгодней, если ставка процента 8 % годовых.

10. Какую сумму нужно ежемесячно вносить на счет, чтобы через 3 года получить 10 млн. руб., если годовая ставка 18,6 %.
11. Определите ежемесячные выплаты по займу в 10 млн. руб., взятому на 7 месяцев под 9 % годовых.
12. Определите величину ежегодной амортизации оборудования начальной стоимостью 8000 тыс.руб., если срок эксплуатации его 10 лет, а остаточная стоимость 500 тыс. руб. Выполнить расчеты, используя функцию АПЛ.

 

  Вариант 2

  1. Создать таблицу, содержащую сведения  о сотрудниках предприятия. Названия  колонок: «Фамилия», «Должность»,  «Отдел», «Дата поступления на  работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая  база», «Налог», «Выплатить». Колонки  «Фамилия», «Должность», «Отдел»,  «Дата поступления на работу»,  «Зарплата» заполнить произвольными  данными, при этом в колонке  «Зарплата» использовать значения  от 500 до 3000 р., в колонке «Должность»  использовать 5-6 названий (например, техник, инженер, экономист, водитель  и т.д.), в колонке «Отдел» использовать 3-4 названия (например, бухгалтерия,  отдел кадров, транспортный отдел,  конструкторский отдел). Значения  в остальных колонках рассчитать  по формулам:

  Стаж  работы = (Текущая дата — Дата поступления  на работу)/365.

  Результат округлить до целого.

  

0, если стаж работы меньше 5 лет;

Надбавка = 5 % от зарплаты, если стаж работы от 5 до 10 лет;

10% от  зарплаты, если стаж работы больше 10 лет. 

  Премия = 20 % (Зарплата + Надбавка).

  Всего начислено = Зарплата + Надбавка + Премия.

  Пенсионный  фонд = 1 % от «Всего начислено».

  Налогооблагаемая  база = Всего начислено — Пенсионный фонд.

Налог =

12 % от Налогооблагаемой  базы, если Налогооблагаемая база  меньше 1000 руб.

20 % от Налогооблагаемой  базы, если Налогооблагаемая база  больше 1000 руб. 

  Выплатить = Всего начислено — Пенсионный фонд — Налог.

  В таблице  должно быть не менее 10 строк.

2. Организовать таблицу как базу данных: константы (проценты премии, налога, пенсионного фонда, текущую дату и т.д.) расположить выше шапки таблицы, т.е. имен полей так, чтобы между константами и шапкой оставалась хотя бы одна пустая строка, между именами полей и первой записью не должно быть ни одной пустой строки.
3. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.
4. Используя форму данных, добавить в список еще 10 записей.
5. Используя форму данных, выполнить поиск записей по следующим критериям:

• заданная должность;

• заданный отдел;
• стаж работы больше заданного;
• заданная должность и зарплата меньше заданной;
• заданный отдел и стаж работы больше заданного.

6. Выполнить сортировку данных по:

• отделам;
• фамилиям;
• отделам и фамилиям;
• отделам и зарплатам;
• отделам, должностям и фамилиям;
• отделам, должностям, зарплатам, фамилиям.

7. Используя Автофильтр, отобрать данные о сотрудниках:

• фамилии которых начинаются на заданную букву;
• зарплата которых больше заданной;
• стаж работы которых находится в заданном диапазоне;
• с заданной должностью и зарплатой в заданном диапазоне.

8. Используя расширенный фильтр, отобрать данные о сотрудниках:

• с зарплатой от 1000 до 1500 руб.;
• со стажем работы меньше 7 лет или с зарплатой меньше 1000 руб.
• со стажем работы от 5 до 10 лет и с зарплатой от 700 руб. до 1500 руб.;
• зарплата которых выше средней (использовать функцию СРЗНАЧ);
• зарплата которых выше средней, а стаж работы от 5 до 15 лет;
• зарплата которых выше средней, а стаж работы — меньше среднего.
• обо всех техниках конструкторского отдела, у которых либо стаж работы больше 5 лет, либо зарплата больше 900 руб.;

9. Скопировать рабочий лист под именем «Итоги». Открыть таблицу на листе «Итоги».

10. Используя инструмент подведения итогов, разбить список на группы по отделам и подвести промежуточные и общие итоги по полям «Фамилия» (операция Количество значений), «Зарплата», «Надбавка», «Премия», «Пенсионный фонд», «Налог», «Выплатить» (операция СУММА).
11. Открыть таблицу на листе Сведения о сотрудниках.
12. Создать сводную таблицу суммарных выплат по отделам, внутри отделов — по фамилиям. Для этого в макете поместить поля «Отдел» и «Фамилия» в область «Строка», а поле «Выплатить» — в область «Данные».
13. Скопировать лист со сводной таблицей. Внести изменения в копию, чтобы данные по каждому отделу выводились на отдельной странице (для этого поле «Отдел» переместить в область «Страница»).
14. Создать еще одну копию первой сводной таблицы. Изменить копию так, чтобы подчитывалось количество сотрудников в отделах (для этого удалить из области «Данные» поле «Выплатить» и поместить туда поле «Фамилия»).

 

Вариант 3

  Составить таблицу для ведения учета  основных средств предприятия (рис. 1).

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

  В ячейку А1 (дата расчета) может быть введено любое число в формате  значений типа «дата» Ехсеl.

  Значения  в колонках А, В, С, D вводятся произвольно  вручную со следующими ограничениями:

1. значения в колонке В больше нуля;
2. значения в колонке С больше нуля и не больше 100;
3. в колонку D вводятся значения в формате даты Ехсе1.

  Число строк между заголовком таблицы  и строкой «Итого» может быть произвольным.

  Значения  в колонке G рассчитываются по следующим правилам.

  Если  дата ввода в эксплуатацию (D) не заполнена  или она больше, чем значение в  ячейке А1, то срок эксплуатации (G) равен 0. В противном случае, срок эксплуатации равен числу месяцев от даты ввода  в эксплуатацию (D) до даты расчета (А1). При составлении расчетной формулы  следует руководствоваться следующим  правилом расчета срока эксплуатации в месяцах:

СрокЭксплуатации=12*(ГодРасчета-ГодВводаВЭксплуатацию)+МесяцРасчета-МесяцВводаВЭксплуатацию

  При записи формулы рекомендуется использовать функции Ехсеl ГОД() и МЕСЯЦ(). 

Рис. 1. Структура и пример заполнения таблицы по учету основных средств

    Значения  в колонке Н рассчитываются в  соответствии с правилом:

  РасчетныйИзнос=Стоимость*НормаАмортизации*СрокЭксплуатации/1200

    Колонки G и Н чисто технологические  и используются для упрощения  записи формул расчета колонок Е  и F.

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

  ИзносЗаМесяц=Стоимость*НормаАмортизации/1200

    Если  расчетный износ (колонка Н) больше стоимости, то накопленный износ (колонка F) равен стоимости, в противном  случае накопленный износ равен  расчетному износу.

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

 

Вариант 4

  Составить таблицу (рис. 1). При построении таблицы руководствоваться следующими правилами.

  В ячейке D1 задаются накладные расходы  на производство всех видов продукции. В ячейке D2 указывается номер  способа распределения накладных  расходов по видам выпускаемой продукции, на основе которого производится калькуляция  полной себестоимости.

Рис. 1. Структура и пример заполнения таблицы калькуляции полной себестоимости продукции

  В строке 4 задаются формулы для расчета  итогов по соответствующим колонкам.

  Значения  таблицы в колонках А («Продукция»), В («Материалы»), С («Зарплата»), начиная  со строки 5, задаются вручную. В колонку А («Продукция») в произвольной текстовой форме вводятся данные о видах выпускаемой продукции. В колонке В («Материалы») вводятся данные о материалах, затраченных на производство данного вида продукции, а в колонку С («Зарплата») — сведения о зарплате, выплаченной за ее производство.

  В колонке D рассчитываются прямые затраты  на производство каждого вида продукции  как сумма затрат материалов и  выплаченной зарплаты.

  В колонке Е, в зависимости от номера правила распределения, производится вычисление доли накладных расходов, относимых на конкретный вид продукции.