Задание 1.

1. Создать документ Excel и разместить на листе таблицу по номеру варианта (последняя цифра номера зачетной книжки).

2. В позиции, помеченные вопросительным знаком, внести формулы в соответствии с требуемым алгоритмом вычисления. Добавить, если необходимо, новые строки и столбцы.

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

Варианты заданий:

Вариант 1. Рассчитать сумму оплаты за электроэнергию за 1-й квартал 2000 года. Определить средний расход электроэнергии за квартал.

Вариант 2. Составить таблицу начисления заработной платы работникам МП «Воронья слободка».

Примечание 1. Тарифная ставка определяется в зависимости от разряда: 1-й разряд – 1200 руб.; 2-й разряд – 1500 руб.; 3-й разряд – 2000 руб.

Примечание 2. Размер премиальных определяется в зависимости от выполнения плана:

 ниже 100% – премия не начисляется;

 100% – премия 20% от тарифной ставки;

 101…110% – премия 30%;

 111…115% – премия 40%.

Вариант 3. Рассчитать начисление стипендии студентам по итогам сессии.

Примечание. Размер стипендии составляет 2 МРОТ (минимальный размер оплаты труда, равный 108 руб. 54 коп.). Если все экзамены сданы на «пятерку», то надбавка составляет 50%. Если есть одна «четверка» (при остальных «пятерках»), то надбавка составляет 25%. Если есть хотя бы одна «двойка», то стипендия не начисляется.

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

.............................

Примечание 1. Надбавка за ученую степень рассчитывается, исходя из минимального размера оплаты труда (МРОТ), равного 108 руб. 54 коп.

Примечание 2. Начисление по окладу (графа 3) составляет 100% для штатных работников и 50% для совместителей.

Вариант 5. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства (в млн. руб.).

Примечание 1. Восстановительная полная стоимость (ВПС) объекта  и восстановительная остаточная стоимость (ВОС) объекта вычисляются по балансовой стоимости (БС) и износу объекта (ИО):

ВПС = БС*К;  ВОС = ОС*К,

где  К = 3,0, если БС > 500 млн. руб.;

К = 2,0, если БС £ 500 млн. руб.

Примечание 2. Для заполнения столбцов использовать функцию ЕСЛИ.

Вариант 6. Оформить в виде таблицы расчет подоходного налога за 1999 год с пяти физических лиц, чьи доходы лежат в каждой из пяти категорий, по следующему алгоритму:

 до 30000 руб. : 3% в федеральный бюджет, 9% в бюджет субъектов федерации;

 от 30001 до 60000 руб. : 3% в федеральный бюджет, 2700 руб. + 12% с суммы, превышающей 30000 руб., в бюджет субъектов федерации;

 от 60001 до 90000 руб. : 3% в федеральный бюджет, 6300 руб. + 17% с суммы, превышающей 60000 руб., в бюджет субъектов федерации;

 от 90001 до 150000 руб. : 3% в федеральный бюджет, 11400 руб. + 22% с суммы, превышающей 90000 руб., в бюджет субъектов федерации;

 от 150001 и выше : 3% в федеральный бюджет, 24600 руб. + 32% с суммы, превышающей 150000 руб., в бюджет субъектов федерации.

Подсчитать итоговые суммы к выплате.

Вариант 7. Рассчитать итоговое количество продукции, произведенное в России различными отраслями за 4 года, и относительные показатели каждого года.

Вариант 8. Рассчитать сумму, которая будет на счету в конце каждого месяца, если в начале года внесено 10000 руб., для различных годовых норм банковского процента  i.

Примечание. Формула для расчета сложных процентов имеет вид:

Р_m = Р₀ (1+i/m)^N*m,

где  P₀ – первоначально вложенная сумма;

P_m – сумма, которая будет получена через N лет;

m – количество начислений процентных платежей в течение годового периода;

i – годовая норма процента.

Вариант 9.  Рассчитать структуру розничной цены продукции (руб.), исходя из следующих данных:

Вариант 10. Рассчитать доход, полученный от вложения свободных средств в одно- , трех- и шестимесячные депозиты к концу 6-го месяца. Доход от одномесячного депозита – 1%, от трехмесячного депозита – 4%, от шестимесячного депозита – 9%. Результаты расчета предлагается оформить в виде следующей таблицы.

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

Задание 2.

1. Создать документ Excel и разместить на листе таблицу по номеру варианта (последняя цифра номера зачетной книжки).

2. Предположим, что математическая модель некоторой оптимизационной задачи уже построена. Найти оптимальное значение целевой функции R_(x), при заданных ограничениях с помощью функции ПОИСК РЕШЕНИЯ.

Варианты заданий:

1. R(x)= 626x₁+ 656x₂ ® mах  при ограничениях

5x₁ + 8x₂ £ 81; 6x₁ + 4x₂ £ 70; 3x₁ + x₂ £ 26; x₁ + x₂ £ 12;

x₁ £ 8; x₁, x₂ ³ 0.

2. R(x)=–5x₁ + 4x₂–x₃–3x₄–5x₅  ® min  при ограничениях

3x₁–x₂ + 2x₄ + x₅ = 5; 2x₁–3x₂ + x₃  + 2x₄ + x₅ = 6;

3x₁–x₂ +x₃ +3x₄ + 2x₅ = 9; x_i ³ 0, i=1...5.

3. R(x)=–2x₁ +x₂ + 4x₃–x₄–x₅ ® min  при ограничениях

x₂ + 2x₄–x₅ =1; x₁–x₄–x₅ =1;

2x₂+x₃ + 2x₅ = 4; x_i ³ 0, i=1...5.

4. R(x)= 2x₁ + x₂ + x₃ + 7x₄–2x₅ ® min  при ограничениях

x₁ +x₂–x₃ + x₄  = 1; 2x₁ + x₂ + x₃ –x₅ = 7;

x₁ + 2x₂ + x₃ –7x₄ + x₅ = 6; x_i ³ 0, i=1...5.

5. R(x)=–x₁ + x₂ + x₃ + x₄ + 3x₅ ® min  при ограничениях

2x₁+ 2x₂+ x₄ + x₅ =3; 3x₁ –x₂ + 2x₃ – 2x₅ =1;

–3x₁ + 2x₃ – x₄ + 2x₅ = 1; x_i ³ 0, i=1...5.

6. R(x)= –4x₁ +2x₂ –x₃ +x₄ ® min  при ограничениях

3x₁ + 2x₂ – x₃ + 4x₄  = 3; x₁ – x₂ + 4x₃  – 2x₄ = 2;

x_i ³ 0, i=1...4.

7. R(x)= x₁ + 2x₂ + x₃ –x₄ ® min  при ограничениях

10x₂ + x₃ + 2x₄ + 3x₅ = 25; –x₁ + 5x₂ + x₃  + x₄ + x₅ = 10;

2x₁ – x₂ + x₃ – 3x₄  = 6; x_i ³ 0, i=1...5.

8. R(x)= 4x₁ –3x₂ – x₄ + x₅ ® min  при ограничениях

–x₁ + 3x₂ + x₄  = 13;  4x₁ + x₂ + x₅ = 2;

–2x₁ + x₂ + x₃  = 1;  x₁ – 3x₂ + x₆ = 0; x_i ³ 0, i=1...6.

9. R(x)= x₁ – x₂  ® mах  при ограничениях

2x₁ – 4x₂ – x₃ + x₄ = –3; 4x₁ – 3x₂ – x₃  + x₄ + x₅ = 6;

x₁ + 4x₂ + x₃ + x₅ = 15; x_i ³ 0, i=1...5.

10. R(x)= x₁ + 9x₂ + 5x₃ + 3x₄ + 4x₅ + 14x₆ ® min  при ограничениях

x₁ + x₄  = 20; x₂ + x₅ = 50; x₃ + x₆ =30;

x₄ + x₅ +x₆ = 60; x_i ³ 0, i=1...6.

III.  Финансовые функции.

Финансовые расчеты, проводимые с помощью встроенных финансовых функций Excel, можно разделить на четыре группы:

-наращение и дисконтирование доходов и затрат (БС, ПС, КПЕР, СТАВКА, ПЛТ и др.);

-анализ эффективности капитальных вложений (НПЗ, ВНДОХ и др.);

-расчеты по ценным бумагам (ДОХОД, ЦЕНА и др.);

-расчет амортизационных отчислений (АМР, АМГД и др.).

Всего в Excel встроено более 50 финансовых функций. Рассмотрим применение некоторых из них.

Функция БЗ.

=БЗ(норма; число_периодов; выплата; нз; тип) в исходной русификации

=БС(ставка; кпер; плт; пс; тип) в новейшей русификации.

 Возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки (наращение из настоящего в будущее). Для расчета функции БЗ используется метод сложных процентов.

Ее вид:

БЗ (Норма; Кпер; Выплата; Нз; Тип)

Аргументы:

Норма – процентная ставка за период (задавать в процентном формате или в долях);

Кпер – общее число платежных периодов, по истечении которых вы хотите определить объем имеющихся средств;

Выплата – величина постоянных периодических платежей;

Нз – начальное значение (текущая стоимость) вклада;

Тип – параметр, определяющий, когда вносятся платежи: в начале (=0) или в конце периода (=1). По умолчанию Тип=0.

Функция БЗРАСПИС. Возвращает будущее значение единовременного вложения при переменной процентной ставке.

Ее вид:

БЗРАСПИС (Первичное; План)

Аргументы:

Первичное – числовое значение, представляющее собой исходную сумму средств;

План – массив процентных ставок, используемых за рассматриваемый период.

Пример 1. В банк помещен депозит в размере А = 5000 руб. По этому депозиту в первом году будет начислено р₁ = 10% , во втором – р₂ = 12%,  в третьем – р₃ = 15%, в четвертом и пятом – р_4,5 = 16% годовых. Сколько будет на счету в конце пятого года? Сколько надо было бы поместить на счет при постоянной процентной ставке i = 13%, чтобы обеспечить ту же сумму.

Решить аналогичную задачу, взяв данные из таблицы 3.1.

Указание. Использовать сервисную функцию Excel Подбор параметра.

Таблица 3.1

Функция ПЗ.

=ПЗ в исходной русификации

=ПС(ставка; кпер; плт; бс; тип) в новейшей русификации.

Возвращает текущий объем вклада на основе постоянных периодических платежей (или, иными словами, сумму всех будущих платежей).

Ее вид:

ПЗ (Норма, Кпер, Выплата, Бс, Тип)

Аргументы:

Норма – процентная ставка за период (задавать в процентном формате или в долях);

Кпер – общее число периодов выплат;

Выплата – величина постоянных периодических платежей;

Бс – баланс наличности, который нужно достичь после последней выплаты (если опущен, то 0);

Тип – 0 или 1. Если 0 – оплата производится в конце периода, если 1, то в начале.

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

Сколько  вы  переплатите  за  холодильник  стоимостью 10 тыс. руб. при покупке его в рассрочку на 3 года под процентную ставку 5% при ежеквартальной выплате 1500 руб.

Ответ: почти 16619 – 10000 =6619 руб.

Примечание. При использовании финансовых функций необходимо соблюдать следующее правило: то, что вы платите, должно учитываться со знаком «–», а то, что вы получаете, – со знаком «+».

Пример 2. У вас просят в долг P=10000 руб. и обещают возвращать по A=2000 руб. в течение N=6 лет. У вас есть другой способ использования этих денег: положить их в банк под 7% годовых и каждый год снимать по 2000 руб. Какая финансовая операция будет более выгодна для вас? С помощью На вкладке ДАННЫЕ – АНАЛИЗ «ЧТО-ЕСЛИ» - Диспетчера сценариев проанализировать ситуацию для нескольких возможных вариантов изменения параметров А и N, взяв их из таблицы 3.2.

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

Таблица 3.2

После нажатия кнопки ОК в диалоговом окне Значения ячеек сценария вводятся значения параметров для первого сценария, и с помощью диалогового окна Диспетчер сценариев добавляется необходимое число сценариев.

С помощью кнопки Отчет открывается диалоговое окно Отчет по сценарию, где определяется тип отчета (Структура или Сводная таблица) и задаются ячейки, где вычисляется результат, т.е. значение функции ПЗ.

Функция НПЗ. Используется в Excel для расчета эффективности планируемых капиталовложений. Она возвращает чистую текущую величину вклада (инвестиции), вычисленного на основе ряда последовательных (неравномерных) поступлений денежных средств.

Вид функции:

НПЗ (Ставка; Значение1; Значение 2;...Значение N)

Аргументы:

Ставка – процентная ставка за период;

Значения – до 29 аргументов (могут быть массивы), представляющих поступления (доходы со знаком «+», расходы со знаком «–»).

Рассмотрим применение функции НПЗ для решения следующей задачи.

Пусть в начале первого года вы вкладываете в инвестиционный проект 30000 рублей и предполагаете годовые доходы 8000 руб., 9000 руб., 10000 руб., 12000 руб. в последующие четыре года (начиная со второго). Предположим, что годовая учетная ставка составляет 8 процентов, в таком случае чистый текущий объем инвестиции составит:

–30000+НПЗ(8%; 8000; 9000; 10000; 12000) =1882,14 руб.

Если платежи происходят в конце рассматриваемых периодов, то формула расчета чистого текущего объема инвестиции примет несколько другой вид:

НПЗ(8%; –30000; 8000; 9000; 10000; 12000) =1742,72 руб.,

т.е. первоначальные затраты 30000 руб. были включены в формулу одним из значений.

Функция ППЛАТ.

=ППЛАТ в исходной русификации

=ПЛТ(ставка; кпер; пс; бс; тип) в новейшей русификации.

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

Вид функции:

ППЛАТ (Норма, Кпер, Пз, Бс, Тип)

Аргументы:

Пз –- общая сумма всех будущих платежей с настоящего момента;

остальные аргументы те же, что и для функции ПЗ.

Функция НОРМА.

=НОРМА в исходной русификации

=СТАВКА(кпер; плт; пс; бс; тип; предложение) в новейшей русификации.

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

Ее вид:

НОРМА (Кпер, Выплата, Пз, Бс, Тип, Предположение)

Аргументы:

первые пять аргументов соответствуют аргументам функций ПЗ и НОРМА;

Предположение - предполагаемая величина нормы (поиск величины нормы организован итерационным способом и это значение есть начальное приближение); если опущено, то берется значение 10% .

Пример 5. Определить процентную ставку для N - летнего займа в А рублей с ежегодной выплатой в Р рублей. Данные взять из таблицы 3.5.

Таблица 3.5

Задание 4.

1. Допустим, что у вас есть небольшое предприятие (20 человек) и вам необходимо разработать базу учета кадров и зарплаты. Создайте документ Microsoft Access.

Создайте таблицу КАДРЫ, а также удобную форму для ее просмотра.

Обязательные поля: Фамилия, имя, год рождения, адрес, должность, оклад, срок приема на работу.

Дополнительные поля: по собственному усмотрению.

2. Создайте таблицу ЗАРПЛАТА, а также удобную форму для ее просмотра.

Обязательные поля: Фамилия, должность, оклад, начисления, налоги, на руки, зарплата за предыдущие месяцы.

Дополнительные поля: по собственному усмотрению.

3. Свяжите таблицы между собой. Постройте отчеты:

-Зарплата за месяц.

-Зарплата за год.

4. Постройте запрос в соответствии с вариантом.

Варианты заданий:

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

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

3. Постройте запрос на выбор из базы данных информации о тех работниках, оклад которых составляет более 7 тыс. руб. (фамилия, оклад, налоги).

4. Постройте запрос с параметром на выбор из базы данных информации о тех работниках, срок приема на работу которых будет указываться при запуске запроса (фамилия, срок приема на работу, зарплата за предыдущие месяцы).

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

6. Постройте запрос на выбор из базы данных информации о тех работниках, в фамилии которых встречается буква К (фамилия, адрес).

7. Постройте запрос на выбор из базы данных информации о тех работниках, налоги которых превышают 1,5 тыс. руб. (фамилия, оклад, налоги).

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

9. Постройте запрос на выбор из базы данных информации о тех работниках, зарплата которых менее 7 тыс.руб. (фамилия, оклад).

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

Задание 5.

Создать презентацию по предыдущему заданию. Описать постановку задачи, вставить таблицы, графики, рисунки, результаты запросов и отчетов (не менее 10 слайдов).