Как найти премию в excel от оклада

Как рассчитать премию в экселе?

Рассчитать премию в программе эксель довольно просто, нужно только понимать какой алгоритм начисления. Давайте рассмотрим один из многочисленных вариантов.

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

Так как для премии действуют разные условия и чтобы сделать быстрый расчет, воспользуемся функцией «Если», она будет состоять из двух частей, сначала напишем первую часть в ячейке «D2»: =ЕСЛИ(B2<5;10%*C2;), данная ветка будет работать только для людей со стажем меньше пяти лет.

Теперь напишем второе условие для промежутка пять и десять лет и выше, формула преобразуется в следующий вид: =ЕСЛИ(B2<5;10%*C2;ЕСЛИ(5

Остается только скопировать формулу из ячейки «D2» на диапазон ячеек «D3» по «D6» и мы сделали несложную формулу расчета премии, в зависимости от отработанного стажа.

Как рассчитать премию в exel?? при трех разных условиях

На предприятии премия выплачивается в зависимости от стажа.
1 Если стаж меньше 5 лет, то премия 10%,
2 если больше или равен 5,но меньше 10 лет, то 20%,
3 если больше или равен 10, то 50%.

yadi.sk /i/ i1YgkuEDgzzU4

https:// yadi.sk /i/i1YgkuEDgzzU4

Можно избавиться от G4 а вместо 50%, 20%, 10% в столбце G сразу писать формулу D5 * 0,5 в столбце H. Что я кстати для проверки и сделал.

У меня получилось вот так.

Расчет премии сотрудника – Формулы и пример расчета. Excel-калькулятор

Калькулятор KPI представляет собой настраиваемый EXCEL документ, для расчета премии сотрудников.

Для кого подойдет наш калькулятор:

• Для собственников бизнеса, которые нацелены на сосредоточение и фокусирование свой команды на результат.
• Для HR отдела, которым нужно сократить время расчета премии.
• Для сотрудников финансового департамента, которые могут с легкостью контролировать финансовые показатели.

Преимущества:

• Простой в использовании инструмент
• Настраиваемый под Ваш бизнес
• Инструмент позволяет использовать для расчета разную валюту

Наш калькулятор расчета премии содержит:

1. Подробную инструкцию по применению
2. Формулы расчета премии
3. Линии отсечения (нормы)
4. Расчет квартальных и годовых премий
   маркетолога

Чем наш калькулятор KPI лучше других онлайн-калькуляторов?

• Можно настроить под условия Вашего бизнеса
• Учитывает ключевые показатели эффективности
• Разработан практикующим экспертом в области финансовых расчетов

Работа с калькулятором происходит следующем образом:

Вначале заполняется лист «настройка». Вносятся данные сотрудника (ФИО, Должность, Отдел).

Затем заполняется список ежемесячных KPI актуальных для должности сотрудника.

Можно также ввести названия квартальных KPI.

• Прямой KPI (например, Выручка) – чем ВЫШЕ показатель, тем БОЛЬШЕ % KPI.
• Обратный KPI (например, Административные расходы) – чем НИЖЕ показатель, тем БОЛЬШЕ % KPI.

• Чистыми на руки (без учета налогов);
• С учетом налогов, выплачиваемых сотрудником (с учетом подоходного налога и ФСЗН уплачиваемого из ЗП);
• С учетом всех затрат на ФОТ (с учетом всех затрат нанимателя на выплату ЗП)

• «Задается цифрой» — Значение задается вручную;
• «Процент от оклада» — Премия формируется как процент от оклада;
• «Процент от показателя» — Премия формируется как процент от показателя, название показателя формируется на листе Настройка.

Если это предусмотрено в мотивации компании настраивается комиссия для должности. Комиссии, как правило, назначаются для сотрудников отдела маркетинга или продаж. Если у сотрудника нет комиссий это поле нужно оставить пустым. В колонке «Список комиссии для должности» вносится название показателя из которого берется комиссия, а в колонке % — процент от комиссии.

Также можно изменять налоговые ставки. По умолчанию, в калькуляторе установлены стандартные ставки налогов, применяемые в РБ. Будьте осторожны, если Ваша организация работает по другим ставкам налогов или сотрудник имеет льготное налогообложение, нужно внести изменения в данные параметр.

• Валюта внесения данных — валюта в которой будет рассчитываться ЗП.
• Валюты выплаты — валюта в которой будет отражена конечная сумма выплат.

На листах месяцев, вносятся фактические параметры для расчета ЗП за каждый месяц. Существует 3 типа листов:

1. Листы только с ежемесячными KPI (Январь, Февраль, Апрель, Май, Июль, Август, Октябрь, Ноябрь)
2. Листы с ежемесячными и квартальными KPI (Март, Июнь, Сентябрь)
3. Лист с ежемесячными и квартальными KPI и годовым бонусом (Декабрь)

Формируется базы для расчета KPI. В зависимости от листа «настройки», нужно заполнить следующие параметры — фонд KPI, % от оклада / % от заданного параметры, размер заданного параметра.

• Удельный Вес — данная колонка отражает как будет распределятся сформированный фонд KPI между показателями. Сумма удельных весов должна быть равна 100%, веса месячных и квартальных KPI суммируются. (Если сумма весов не равна 100%, индикатор загорается красным)

• Нормативное значение — плановое значение KPI

• Фактическое значение — значение показателя в конкретном месяце.

• Нижняя линия отсечения — такой процент выполнения KPI, ниже которого, премия не выплачивается.

• Верхняя линия отсечения — такой процент выполнения KPI, выше которого, размер премии не растет».

Итоговая таблицы «Премия за KPI», выглядит следующим образом.

Далее, если необходимо, заполняется таблица «комиссионные» для расчета комиссии.

Если необходимо, вносятся значения прочих доплат. В колонке наименование доплаты вносится название доплаты, в колонке размер доплаты вносится денежное вознаграждение.

Квартальные KPI можно рассчитать на вкладках «Март», «Июнь», «Сентябрь», «Декабрь». Данные вносятся аналогично ежемесячным KPI. Но в эти месяцы необходимо подвести значения месячных KPI, чтобы сумма весов равнялась 100%.

Расчет годового бонуса происходят на вкладке «Декабрь». Вносится по аналогии с «Внесение значений для формирования % выполнения KPI»

После заполнения всех месяцев, можно перейти к работе с вкладкой «дашборд».

• Чистыми на руки (отображается ЗП очищенная от налогов);
• С учетом налогов, выплачиваемых сотрудником (отображается ЗП с учетом подоходного налога и взноса в ФСЗН);
• С учетом всех затрат на ФОТ.

На графиках отображается распределение годового дохода сотрудника по компонентам. В графике не учитывается годовой бонус. Месяц считается внесённым, если внесена сумма премии за KPI или база / процент для ее расчета.

Система мотивации в каждой организации разная. И не всегда она считается А+В=С.

Возьмём выдуманный случай. Предположим, что начальство вам платит только за проданное количество товара больше 10 шт, за каждую штуку 100 руб, если же продали меньше 10, то просто 100 руб и всё.

Ну то есть продали 5 штук. получили 100 руб, а продали 11 штук, получили 1100 руб.

Посчитать оплату нам поможет функция ЕСЛИ, или в англ Excel – IF

В ячейку D2 пишем «=ЕСЛИ»

Кому-то удобно писать как на рисунке, я же привык нажимать в этом случае Shift+F3 и вот что получается:

В это окошко мне писать формулы удобней

Вернёмся к нашим вычислениям. Первый аргумент в данном случае, это логическое выражение, а именно — если продано более или равно 10 штук товара. Выглядит это так:

И если это условие верно, то во второе окошко «Значение_если_истина» пишем следующее: В2*С2, то есть проданное кол-во умножить на стоимость

Ну и третий аргумент, «Значение_если_ложь», мы пишем, то что будет отображаться, если условия не соблюдены, а именно – 100 рублей.

Жмём ОК и протягиваем формулу до конца столбика

И что мы видим?

Где условия выполнены, подчеркнул зелёным, для наглядности, где нет — коричневым.

А на сегодня всё.

Если статья была полезной — отблагодарите лайком, или переводом на кофеёк. Если вам интересна данная тема, не забудьте подписаться на канал здесь и в телеграм . Спасибо

Практическая
работа № 1

СОЗДАНИЕ ЭЛЕКТРОННОЙ КНИГИ.

ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИИ В MS
EXCEL

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

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

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и создайте новую электронную книгу.

2.           
Создайте таблицу расчета заработной платы по
образцу (см. рис. 1.1). Введите исходные данные – Табельный номер, ФИО и Оклад,
% Премии = 27%, % Удержания = 13 %.

Рис. 1.1 – Ведомость начисления заработной платы

При расчете Премии используется формула
Премия = Оклад * % Премии, в ячейке D5 наберите формулу = $D$4 *
С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте
автозаполнением.

Рекомендации.
Для удобства работы и формирования навыков работы с абсолютным видом адресации
рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от
цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка
(т.е. ячейка с константой) будет вам напоминанием, что следует установить
абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши
[F4]).

Формула для расчета
«Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула
Удержание = Всего начислено * % Удержания, для этого в ячейке F5 наберите
формулу = $F$4 * Е5.

Формула для расчета
столбца «К выдаче»:

К выдаче = Всего
начислено – Удержания.

3.           
Рассчитайте итоги по столбцам, а также
максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/
категория – Статистические функции).

4.           
Переименуйте ярлычок Листа 1, присвоив ему имя
«Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое
имя. Можно воспользоваться командой Переименовать контекстного меню
ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на
рис. 1.2.

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это
позволяет, используя несколько листов, создавать понятные и четко
структурированные документы, вместо того, чтобы хранить большие
последовательные наборы данных на одном листе.

5.           
Скопируйте содержимое листа «Зарплата октябрь» на
новый лист (Правка/Переместить/ Скопировать лист). Можно воспользоваться
командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте
для копирования поставить галочку в окошке Создавать копию

(рис. 1.3).

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для
копирования удерживайте нажатой клавишу [Ctrl]).

6.           
Присвойте скопированному листу название «Зарплата
ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии
на 32%. Убедитесь, что программа произвела пересчет формул.

7.           
Между колонками «Премия» и «Всего начислено»
вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте
значение доплаты по формуле Доплата = Оклад * % Доплаты. Значение доплаты
примите равным 5 %.

8.           
Измените формулу для расчета значений колонки
«Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

Рис. 1.2 – Результаты начисления заработной платы

Рис. 1.3 – Копирование листа электронной книги.

9.           
Проведите условное форматирование значений колонки
«К выдаче». Установите формат вывода значений между 7000 и 10 000 – зеленым
цветом шрифта; меньше 7000 – красным; больше или равно 10 000 – синим цветом
шрифта (Формат/Условное форматирование) (рис. 1.4).

Рис. 1.4 – Условное форматирование данных

10.        
Проведите сортировку по фамилиям в алфавитном
порядке по возрастанию (выделите фрагмент с 5 по 18 строки)

11.        
Таблицы без итогов, выберите меню Данные/Сортировка,
сортировать по – Столбец В) (рис. 1.5).

Рис. 1.5 – Сортировка данных

12.        
Поставьте к ячейке D3 комментарии «Премия
пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем
углу ячейки появится красная точка, которая свидетельствует о наличии
примечания.

13.        
Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/
Защитить лист). Задайте пароль на лист (рис. 1.6), сделайте подтверждение
пароля

Убедитесь, что лист защищен и невозможно
удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

Рис. 1.6 – Защита листа электронной книги

14.        
Сохраните созданную электронную книгу под именем
«Зарплата» в своей папке.

Задание 1.2. Сделать примечания к двум-трем ячейкам.

Задание 1.3.
Выполнить условное форматирование оклада и премии за ноябрь месяц:

до 2000 р. – желтым цветом заливки; от 2000 до
10 000 р. – зеленым цветом шрифта; свыше 10 000 р. – малиновым цветом заливки,
белым цветом шрифта.

Задание 1.4. Защитить лист зарплаты за октябрь от изменений.

Проверьте защиту. Убедитесь в
неизменяемости данных. Снимите защиту со всех листов электронной книги
«Зарплата».

Задание 1.5. Построить
круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц.

Дополнительное задание

Задание 1. Рассчитать таблицу.

Введите произвольно случайные числа от 0 до 10
в диапазон А2:А20.

для чего в ячейку А2
введите формулу = ЦЕЛОЕ(СЛЧИС()*10).

Практическая
работа № 2

СВЯЗАННЫЕ ТАБЛИЦЫ. 

РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ 

В ТАБЛИЦАХ MS EXCEL

Цель занятия. Связывание листов электронной книги. Расчет промежуточных итогов.
Структурирование таблицы.

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

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и откройте созданный в Практической работе 1 файл «Зарплата».

2.           
Скопируйте содержимое листа «Зарплата ноябрь» на
новый лист электронной книги (Правка/Переместить/Скопировать лист).Не
забудьте для копирования поставить галочку в окошке Создавать копию.

3.           
Присвойте скопированному листу название «Зарплата
декабрь». Исправьте название месяца в ведомости на декабрь.

Рис. 2.1 – Ведомость зарплаты за декабрь

4.           
Измените значение Премии на 46 %, Доплаты – на 8 %.
Убедитесь, что программа произвела пересчет формул.

5.           
По данным таблицы «Зарплата декабрь» постройте
гистограмму доходов сотрудников. В качестве подписей оси Х выберите фамилии
сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы
приведен на рис. 2.2.

Рис. 2.2 – Гистограмма зарплаты за декабрь

6.           
Перед расчетом итоговых данных за квартал проведите
сортировку по фамилиям в алфавитном порядке (по возрастанию) в
ведомостях начисления зарплаты за октябрь–декабрь.

7.           
Скопируйте содержимое листа «Зарплата октябрь» на
новый лист (Правка/Переместить/ Скопировать лист). Не забудьте
для копирования поставить галочку в окошке Создавать копию.

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

9.           
Отредактируйте лист «Итоги за квартал» согласно
образцу на рис. 2.3. Для этого удалите в основной таблице (см. рис. 2.1)колонки
Оклада и Премии, а также строку 4 с численными значениями %Премии и % Удержания
и строку 19 «Всего». Удалите также строки с расчетом максимального,
минимального и среднего доходов под основной таблицей. Вставьте пустую третью
строку.

10.        
Вставьте новый столбец «Подразделение» (Вставка/Столбец)
между столбцами «Фамилия» и «Всего начислено». Заполните столбец
«Подразделение» данными по образцу (см. рис. 2.3).

Рис. 2.3 – Таблица для
расчета итоговой квартальной заработной платы

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

Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа,
следует во время ввода формулы щелкнуть по закладке этого листа и выделить на
нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.

В ячейке D5 для расчета квартальных
начислений «Всего начислено» формула имеет вид

= ‘Зарплата декабрь’!F5 + ‘Зарплата
ноябрь’!F5 + ‘Зарплата октябрь’!Е5.

Аналогично произведите квартальный расчет
«Удержания» и «К выдаче».

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

12.        
Для расчета промежуточных итогов проведите
сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица
примет вид, как на рис. 2.4.

13.        
Подведите промежуточные итоги по подразделениям,
используя формулу суммирования. Для этого выделите всю таблицу и выполните
команду Данные/Итоги (рис. 2.5). Задайте параметры подсчета
промежуточных итогов:

при каждом изменении в – Подразделение;
операция – Сумма; добавить итоги по: Всего начислено, Удержания, К выдаче.

Рис. 2.4 – Вид таблицы после сортировки

Рис. 2.5 – Окно задания параметров расчета промежуточных итогов

Отметьте галочкой операции «Заменить
текущие итоги» и «Итоги под данными».

Примерный вид итоговой
таблицы представлен на рис. 2.6.

14.        
Изучите полученную структуру и формулы подведения
промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь
сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

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

Рис. 2.6 – Итоговый вид
таблицы расчета квартальных итогов по зарплате

15.        
Сохраните файл «Зарплата» с произведенными
изменениями(Файл/Сохранить).

Дополнительные задания

Задание 1. Исследовать графическое отображение зависимостей ячеек друг от друга.

Порядок работы

Скопируйте содержимое листа «Зарплата
октябрь» на новый лист. Копии присвойте имя «Зависимости». Откройте панель
«Зависимости» (Сервис/Зависимости/Панель зависимостей) (рис. 2.7).
Изучите назначение инструментов панели, задерживая на них указатель мыши.

Рис. 2.7 – Панель зависимостей

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

Рис. 2.8 – Зависимости в таблице расчета зарплаты

Практическая
работа № 3

ПОДБОР ПАРАМЕТРА. ОРГАНИЗАЦИЯ ОБРАТНОГО
РАСЧЕТА

Цель занятия.
Изучение технологии подбора параметра при обратных расчетах.

Задание 3.1.
Используя режим подбора параметра, определить, при каком значении % Премии
общая сумма заработной платы за октябрь будет равна 250 000 р. (на основании
файла «Зарплата», созданного в Практических работах 1…2).

Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии,
одинакового для всех сотрудников. Результатом вычислений являются ячейки,
содержащие формулы, при этом изменение исходных данных приводит к изменению
результатов расчетов. Использование операции «Подбор параметра» в MS Excel
позволяет производить обратный расчет, когда задается конкретное значение
рассчитанного параметра, и по этому значению подбирается некоторое
удовлетворяющее заданным условиям, значение исходного параметра расчета.

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и откройте созданный в Практических работ 1…2 файл «Зарплата».

2.           
Скопируйте содержимое листа «Зарплата октябрь» на
новый лист электронной книги (Правка/Переместить/Скопировать лист). Не
забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте
скопированному листу имя «Подбор параметра».

3.           
Осуществите подбор параметра командой Сервис/Подбор
параметра (рис. 3.1).

Рис. 3.1 – Задание параметров подбора параметра

В диалоговом окне Подбор параметра на
первой строке в качестве подбираемого параметра укажите адрес общей итоговой
суммы зарплаты (ячейка G19), на второй строке наберите заданное значение
200000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка
D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте
подтверждение подобранному параметру нажатием кнопки OK (рис. 3.2).

Рис. 3.2 – Подтверждение результатов подбора параметра

Произойдет обратный пересчет % Премии.
Результаты подбора (рис. 3.3): если сумма к выдаче равна 200000 р., то % Премии
должен быть 158 %.

Рис. 3.3 – Подбор значения % Премии для заданной общей суммы заработной
платы, равной 250000 р.

Задание 3.2. Используя режим подбора параметра, определить штатное расписания фирмы.
Исходные данные приведены на рис. 3.4.

Краткая справка. Известно, что в штате фирмы состоит:

•    6 курьеров;

•    8 младших менеджеров;

•    10 менеджеров;

•    3 заведующих отделами;

•    1 главный бухгалтер;

•    1 программист;

•    1 системный аналитик;

•    1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет
100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией
от оклада курьера, а именно:

зарплата
= А_i * х
+ B_i,

где х
– оклад курьера;

  А_i  и B_i – коэффициенты,
показывающие:

А_i – во сколько раз превышается значение х; B_i –
на сколько превышается значение х.

Рис. 3.4 – Исходные данные для Задания 3.2

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel

2.           
Создайте таблицу штатного расписания фирмы по
приведенному образцу (см. рис. 3.4). Введите исходные данные в рабочий лист
электронной книги.

3.           
Выделите отдельную ячейку D3 для зарплаты курьера
(переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно
введите произвольное число.

4.           
В столбце D введите формулу для расчета заработной
платы по каждой должности. Например, для ячейки D6 формула расчета имеет
следующий вид: = В6 * $D$3 + С6 (ячейка D3 задана в виде абсолютной
адресации).

Далее скопируйте формулу из ячейки D6
вниз по столбцу автокопированием.

В столбце F задайте формулу расчета
заработной платы всех работающих в данной должности. Например, для ячейки F6
формула расчета имеет вид

= D6 * Е6.

Далее скопируйте формулу из ячейки F6
вниз по столбцу автокопированием.

В ячейке F14 автосуммированием вычислите
суммарный фонд заработной платы фирмы.

5.           
Произведите подбор зарплат сотрудников фирмы для
суммарной заработной платы, равной 100 000 р. Для этого в меню Сервис активизируйте
команду Подбор параметра.

В поле Установить в ячейке появившегося
окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной
платы; в поле Значение наберите искомый результат 100000;

в поле Изменяя значение ячейки введите
ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера,
и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников
по заданному условию при фонде зарплаты, равном 100 000 р.

6.           
Присвойте рабочему листу имя «Штатное расписание
1». Сохраните созданную электронную книгу под именем «Штатное расписание» в
своей папке.

Анализ задач показывает, что с помощью MS
Excel можно ре шать линейные уравнения. Задания 3.1 и 3.2 показывают,
что поиск значения параметра формулы – это не что иное, как численное решение
уравнений. Другими словами, используя возможности программы MS Excel, можно
решать любые уравнения с одной переменной.

Задание 3.3. Используя режим подбора параметра и таблицу расчета штатного расписания
(см. задание 3.2), определить заработные платы сотрудников фирмы для
ряда заданных значений фонда заработной платы.

Порядок работы

1.           
Скопируйте содержимое листа «Штатное расписание 1»
на новый лист и присвойте копии листа имя «Штатное расписание 2».Выберите
коэффициенты уравнений для расчета согласно табл. 3.1(один из пяти вариантов
расчетов).

2.           
Методом подбора параметра последовательно
определите зарплаты сотрудников фирмы для различных значений фонда заработной
платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 руб. Результаты
подбора значений зарплат скопируйте в табл. 3.2 в виде специальной вставки.

Таблица 3.1

Таблица 3.2

Краткая справка. Для копирования результатов расчетов в виде значений необходимо
выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать),
установить курсор в соответствующую ячейку таблицы ответов, задать режим
специальной вставки (Правка/Специальная вставка), отметив в качестве
объекта вставки -– значения (Правка/ Специальная вставка/вставить – значения)
(рис. 3.5).

Рис. 3.5 – Специальная вставка значений данных

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

Практическая работа № 4

ЗАДАЧИ ОПТИМИЗАЦИИ

 (ПОИСК РЕШЕНИЯ)

Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации,
максимизации).

Задание 4.1. Минимизация фонда заработной платы фирмы.

Пусть известно, что для нормальной работы
фирмы требуется 5…7 курьеров, 8…10 младших менеджеров, 10 менеджеров, 3
заведующих отделами, главный бухгалтер, программист, системный аналитик,
генеральный директор фирмы.

Общий месячный фонд зарплаты должен быть
минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы,
при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи возьмем
линейную модель.

Тогда условие задачи
имеет вид

N₁ · А₁ * х + N₂ * (А₂ * х + В₂) + . . . + N₈ * (А₈ * х + В₈) = Минимум, где N_i – количество работников
данной специальности;

  х
– зарплата курьера;

  А_i и В_i – коэффициенты заработной
платы сотрудников фирмы.

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и откройте созданный в Практической работе 3 файл «Штатное расписание».

Скопируйте содержимое листа «Штатное
расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

2.           
В меню Сервис активизируйте команду Поиск
решения (рис. 4.1).

Рис. 4.1 – Задание
условий для минимизации фонда заработной платы

3.           
В окне Установить целевую ячейку укажите
ячейку F14, содержащую модель – суммарный фонд заработной платы.

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

В окне Изменяя ячейки укажите
адреса ячеек, в которых будет отражено количество курьеров и младших
менеджеров, а также зарплата курьера – $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7
и D3 держите нажатой клавишу

[Ctrl]).

Рис. 4.2 – Добавление ограничений для минимизации  фонда заработной
платы

Используя кнопку Добавить в окнах Поиск
решения и Добавление ограничений, опишите все ограничения задачи:
количество курьеров изменя- ется от 5 до 7, младших менеджеров от 8 до 10, а
зарплата курьера > 1400 (рис. 4.2). Ограничения наберите в виде

$D$3 > – 1400

$Е$6> = 5 $Е$6 < = 7 $Е$7 > = 8 $Е$7
< = 10.

Активизировав кнопку Параметры, введите
параметры поиска, как показано на рис. 4.3.

Рис. 4.3 – Задание параметров поиска решения по минимизации  фонда
заработной платы

Окончательный вид окна Поиск
решения приведен на рис. 4.1.

Запустите процесс поиска решения нажатием
кнопки Выполнить. В открывшемся диалоговом окне Результаты поиска
решения задайте опцию Сохранить найденное решение (рис. 4.4).

Рис. 4.4 – Сохранение найденного при поиске решения

Решение задачи приведено на рис. 4.5. Оно
тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный
фонд заработной платы.

Рис. 4.5 – Минимизация фонда заработной платы

Задание 4.2. Составление плана выгодного производства.

Фирма производит несколько видов
продукции из одного и того же сырья – А, В и С. Реализация продукции А дает
прибыль 10 р., В – 15 р. и С – 20 р. на единицу изделия.

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

Нормы расхода сырья на производство
продукции каждого вида приведены в табл. 4.1.

Таблица 4.1

Сырье	Н	ормы расхода сы	рья	Запас сырья
Сырье 1	18	15	12	350
Сырье 2	6	4	8	200
Сырье 3	5	3	3	100
Прибыль	10	15	20

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и создайте новую электронную книгу.

2.           
Создайте расчетную таблицу как на рис. 4.6.

Рис. 4.6 – Исходные данные для задания 4.2

Введите исходные данные и формулы в
электронную таблицу. Расчетные формулы имеют такой вид:

Расход сырья 1 = (количество сырья 1) *
(норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) +
(количество сырья 1) * (норма расхода сырья С).

Значит, в ячейку F5 нужно ввести формулу
= В5 * $В$9 + С5 * $С$9 + D5 * $D$9.

Обратите внимание, что значения
количества сырья каждого вида пока не известны и будут подобраны в процессе
решения задания (ячейки B9:D9 пока пустые).

(Общая прибыль по А) = (прибыль на ед.
изделий А) * (количество А), следовательно, в ячейку В10 следует ввести формулу
= В8 * В9.

Итоговая общая прибыль = (Общая прибыль
по А) + (Общая прибыль по В) + (Общая прибыль по С), значит, в ячейку ЕЮ
следует ввести формулу = CУMM(B10:D10).

3. В меню Сервис активизируйте
команду Поиск решения и введите параметры поиска, как указано на рис.
4.7.

Рис. 4.7 – Задание условий и ограничений для поиска решений

В качестве целевой ячейки укажите ячейку
«Итоговая общая прибыль» (ЕЮ), в качестве изменяемых ячеек – ячейки количества
сырья – (B9:D9).

Не забудьте задать максимальное значение
суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 <
= 200; расход сырья 3 < = 100,

а также положительные
значения количества сырья А, В, С> = 0.

Установите параметры поиска решения (рис.
4.8). Для этого кнопкой Параметры откройте диалоговое окно Параметры
поиска решения, установите параметры по образцу, задайте линейную модель
расчета (Линейность модели).

Рис. 4.8 – Задание параметров поиска решения

Рис. 4.9 – Найденное решение максимизации прибыли  при заданных
ограничениях

4.           
Кнопкой Выполнить запустите Поиск
решения. Если вы сделали все верно, то решение будет как на рис. 4.9.

5.           
Сохраните созданный документ под именем «План
производства».

Выводы. Из решения видно, что
оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и
22,22 кг продукции С. Продукцию А производить не стоит. Полученная прибыль при
этом составит 527,78 р.

Практическая
работа № 5

ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL

Цель занятия. Изучение технологии экономических расчетов в табличном процессоре.

Задание 5.1. Оценка рентабельности рекламной
кампании фирмы.

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и создайте новую электронную книгу.

2.           
Создайте таблицу оценки рекламной кампании по
образцу рис. 5.1. Введите исходные данные: Месяц, Расходы на рекламу А(0) (р.),
Сумма покрытия В(0) (р.)> Рыночная процентная ставка G) = 13,7%-

Выделите для рыночной процентной ставки,
являющейся константой, отдельную ячейку – СЗ, и дайте этой ячейке имя
«Ставка».

Рис. 5.1 – Исходные данные для Задания 5.1

Краткая справка. Присваивание имени ячейке или группе ячеек.

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

•              
Щелкните на поле Имя, которое расположено
слева в строке формул.

•              
Введите имя ячеек.

•              
Нажмите клавишу [Enter].

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

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

Формулы для расчета: А(n)
= А(0) * (1 + j/12)^(l-n),  ячейке С6 наберите формулу
= В6 * (1 + ставка/12)^(1 – $А6).

Примечание. Ячейка
А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и
относительную по строке, и записывается в виде $А6.

При расчете расходов на рекламу
нарастающим итогом надо учесть, что первый платеж равен значению текущей
стоимости расходов на рекламу, значит, в ячейку D6 введем значение = С6, но в
ячейке D7 формула примет вид = D6 + С7. Далее формулу скопируйте в ячейки
D8:D17.

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

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

Рис. 5.2 – Рассчитанная таблица оценки рекламной кампании

Для расчета текущей стоимости покрытия
скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6 должна быть формула =
Е6 * (1 + ставка/12)^(1 – $A6).

Далее с помощью маркера автозаполнения скопируйте
формулу в ячейки F7:F17.

Сумма покрытия нарастающим итогом
рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в
ячейку G6 поместим содержимое ячейки F6 (= F6), а в G7 введем формулу: =G6+F7

Далее формулу из ячейки G7 скопируем в
ячейки G8:G17. В последних трех ячейках столбца будет представлено одно и то же
значение, ведь результаты рекламной кампании за последние три месяца на сбыте
продукции уже не сказывались.

Сравнив значения в столбцах D и G, уже
можно сделать вывод о рентабельности рекламной кампании, однако расчет денежных
потоков в течение года (колонка Н), вычисляемый как разница колонок G и D,
показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке
Н6 введите формулу = G6 – D6, и скопируйте ее на всю колонку.

Проведите условное форматирование
результатов расчета колонки Н: отрицательных чисел – синим курсивом,
положительных чисел – красным цветом шрифта. По результатам условного
форматирования видно, что точка окупаемости приходится на июль месяц.

4.           
В ячейке Е19 произведите расчет количества месяцев,
в которых сумма покрытия имеется (используйте функцию «Счет»{Вставка/Функция/
Статистические), указав в качестве диапазона интервал ячеек Е6:Е17).

После расчета формула в
ячейке Е19 будет иметь вид – СЧЕТ(Е6:Е17).

5.           
В ячейке Е20 произведите расчет количества месяцев,
в которых сумма покрытия больше 100 000 р. (используйте функцию СЧЕТЕСЛИ,
указав в качестве диапазона интервал ячеек Е7:Е14, а в качестве условия >
100 000). После расчета формула в ячейке Е20 будет иметь вид = СЧЕТЕСЛИ(Е7:Е14)
(рис. 5.3).

Рис. 5.3 – Расчет функции СЧЕТЕСЛИ

6.           
Постройте графики по результатам расчетов (рис.
5.4): «Сальдо дисконтированных денежных потоков нарастающим итогом» по
результатам расчетов колонки Н;

«Реклама: расходы и доходы» по данным
колонок D и G (диапазоны D5:D17 и G5:G17 выделяйте, удерживая нажатой клавишу
[Ctrl]).

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

7.           
Сохраните файл в папке вашей группы.

Задание 5.2. Фирма поместила в коммерческий банк 45000 р. на 6 лет под 10,5 %
годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно?
Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6
лет накопить 250 000 р.?

Порядок работы

1.           
Запустите редактор электронных таблиц Microsoft
Excel и создайте новую электронную книгу или перейдите на новый лист книги,
созданной в Задании 5.1.

2.           
Создайте таблицу констант и таблицу для расчета
наращенной суммы вклада по образцу (рис. 5.5).

3.           
Произведите расчеты А(n) двумя способами:

с помощью формулы A(n) = A(0)*(l +j)ⁿ
(в ячейку D10 ввести формулу: = $В$3 * (1 + $В$4)^А10 или использовать функцию
СТЕПЕНЬ); с помощью функции БЗ (см. рис. 5.5).

Краткая справка. Функция БЗ возвращает будущее значение вклада на основе периодических
постоянных платежей и постоянной процентной ставки.

Рис. 5.5 – Исходные данные для Задания 5.2

Синтаксис функции БЗ: БЗ (ставка;
кпер; плата; нз; тип), где ставка ~ это процентная ставка за период;
кпер – это общее число периодов выплат годовой ренты; плата – это
выплата, производимая в каждый период, вводится со знаком «-», это значение не
может меняться в течение всего периода выплат. Обычно плата состоит из
основного платежа и платежа по процентам, но не включает других налогов и
сборов; нз –- это текущая стоимость, или общая сумма всех будущих
платежей с настоящего момента. Если аргумент нз опущен, то он полагается
равным 0. В этом случае должно быть указано значение аргумента плата; тип – это
число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент тип
опущен, то он полагается равным 0 (0 – платеж в конце периода; 1 – платеж в
начале периода).

Рис. 5.6 – Задание параметров функции БЗ

Рис. 5.7 – Результаты расчета накопления
финансовых средств фирмы

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

Для ячейки С15 задание параметров расчета
функции БЗ имеет вид, как на рис. 5.6.

Конечный вид расчетной
таблицы приведен на рис. 5.7.

4. Используя режим Подбор параметра
(Сервис/Подбор параметра) рассчитайте, какую сумму надо поместить в банк на
тех же условиях, чтобы через 6 лет накопить 250 000 р. Задание параметров
подбора значения суммы вклада для накопления 250 000 р. приведено на рис. 5.8.
В результате подбора выясняется, что первоначальная сумма для накопления в 137
330,29 р.

позволит накопить
заданную сумму в 250000 р.

Рис. 5.8 – Подбор значения суммы вклада для накопления 250000 р.

Задание 5.3. Сравнить
доходность размещения средств организации, положенных в банк на один год, если
проценты начисляются m раз в год, исходя из процентной ставки j = 9,5% годовых
(рис. 5.9); по результатам расчета построить график изменения доходности
инвестиционной операции от количества раз начисления процентов в году
(капитализации).

Рис. 5.9 –Исходные данные для Задания 5.3

Рис. 5.10 – Обратный расчет при подборе параметра

Выясните, при каком значении j доходность
(при капитализации m =12) составит 15 %.

Краткая справка. Формула для расчета доходности Доходность = (1 +j/m)^m–
1.

Примечание. Установите
формат значений доходности – «Процентный».

Для проверки правильности ваших расчетов
сравните полученный результат с правильным ответом:

для m = 12 доходность =
9,92 %.

Произведите обратный расчет (используйте
режим Подбор параметра) для выяснения, при каком значении j доходность
(при капитализации m = 12) составит 15 % (рис. 5.10).

Правильный ответ:
доходность составит 15 % при j = 14,08 %,

Практическая работа № 6

СВОДНЫЕ ТАБЛИЦЫ

Цель занятия: Подвести итоги на основании данных списков, внешних баз данных.
Сводная таблица обеспечивает различные способы агрегирования информации.

Порядок работы

1.           
Создайте таблицу, предложенную на рис.6.1.

2.           
Для создания сводной таблицы, выполните команду Данные/Сводная
таблица.

3.           
Выполнить работу с Мастером самостоятельно.

4.           
Перетащить значки с названиями столбцов таблицы.
Для этого, схватить мышью значок и перетащить в область Строка и Данные (рис.
6.2.)

Рис. 6.1 – Учет доходов и расходов в быту и бизнесе.

Рис. 6.2 – Сводная таблица для ведения домашнего бюджета.

5.           
Если выполнить двойной щелчок на перенесенных
значках, можно редактировать их назначение.

6.           
Выполнить автоформатирование полученной сводной
таблицы командой Формат/Автоформат.

7.           
Внесите изменения в исходные данные и выполните
команду Данные/Обновить данные.

Практическая работа № 7

КОМПЛЕКСНОЕ ИСПОЛЬЗОВАНИЕ ПРИЛОЖЕНИЙ
MICROSOFT OFFICE ДЛЯ СОЗДАНИЯ ДОКУМЕНТОВ

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

Порядок работы

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

Задание 7.1. Создать
таблицу расчета прибыли фирмы, произвести расчеты суммарных доходов, расходов
(прямых и прочих) и прибыли; произвести пересчет прибыли в условные единицы по
курсу (рис. 7.1).

Выясните, при каком значении зарплаты прибыль
будет равна 500000

р. {используйте режим Подбор
параметра).

Краткая справка. Формулы для расчета:

Расходы: всего = Прямые расходы + Прочие
расходы; Прибыль – Доходы: всего – Расходы: всего; Прибыль (у. е.) = Прибыль *
Курс 1 у. е.

Рис. 7.1 – Исходные данные для Задания 7.1.

Задание 7.2. Фирма
хочет накопить деньги для реализации нового проекта. С этой целью в течение
пяти лет она кладет на счет ежегодно по 1250$ в конце каждого года под 8 %
годовых (рис. 7.2). Определить сколько будет на счете фирмы к концу пятого года
(в MS Excel). Построить диаграмму по результатам расчетов. Выясните, какую
сумму надо ежегодно класть на счет, чтобы к концу пятого года накопить 10 000
$. Краткая справка. Формула для расчета Сумма на счете = D * ((1 + j)^n
– l)/j.

Сравните полученный
результат с правильным ответом: для n = 5 сумма на счете = 7333,25$.

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

Вид экрана для расчета с использованием
функции БЗ приведен на рис. 7.3.

Рис. 7.2 – Исходные данные для Задания 7.3

Рис. 7.3 – Использование функции БЗ для расчета

Задание 7.3. Создать «Акт о порче товарно-материальных ценностей».

Текстовую часть документа создайте в
текстовом редакторе MS Word, таблицу расчета стоимости товарно-материальных
ценностей (ТМЦ) для списания создайте в MS Excel, проведите расчеты и
скопируйте в текстовый документ.

Наименование
организации____________                    «Утверждаю»

Отдел_______________________________ 
  Руководитель организации

                                                                            «___»
____________ 20__г.

АКТ

О
ПОРЧЕ ТОВАРНО-МАТЕРИАЛЬНЫХ ЦЕННОСТЕЙ

от
«___» ____________20___г.

Комиссия в составе: председатель
_____________________, члены комиссии _________________________________________________
на основании приказа от _______ № _________ составила настоящий акт в том, что
указанные ниже ценности пришли в негодность и подлежат списанию.

№ п/п	Наименование	Ед. изм.	Колво	Стоимость	Причины и характер порчи и дата заключения
розничная	оптовая
цена	стоимость	цена	стоимость
1	Стол	Шт.	15			250	?	№ 7 от 15.03.04
2	Стулья	Шт.	28			45	?	№ 2 от 15.02.04
3	Скатерти	Шт.	45	20	?			№ 1 от 15.01.04
4	Шторы	Шт.	10	75	?			№ 5 от 1.03.04
5	Двери	Шт.	12	120	?			№ 8 от 5.03.04
6	Компьютер	Шт.	1	5200	?			№ 9 от 15.04.04
7	Калькулятор	Шт.	5	100	?			№ 11 от 15.04.04
8	Телевизор	Шт.	1	4300	?			№ 12 от 15.04.04
	Итого:				?

 Итого по акту ____________
наименование на сумму ___________ руб. ____к.

_________________________________________________________________

(прописью по розничным ценам и по
ценам приобретения) Председатель комиссии ______________________

Задание 7.4. Фирма собирается инвестировать
проект в течение трех лет.

Имеется два варианта инвестирования: 1-й
вариант: под 12 % годовых в начале каждого года; 2-й вариант: под 14 % годовых
в конце каждого года. Предполагается ежегодно вносить по 500 000 р. Определить,
в какую сумму обойдется проект (рис. 7.4).

Порядок работы

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

Сравните полученный результат с правильным
ответом: для n = 3 сумма проекта по 1-му варианту – 1 889 664,00 р.; по 2-му
варианту – 1 719 800,00 р. Краткая справка. Формулы для расчета:

1-й вариант: Сумма
проекта = D * ((1 + j)^n – 1) * (1 +j)/j; 2-й вариант: Сумма проекта = D
* ((1 +j)^n – l)/j.

Рис. 7.4 – Исходные данные для Задания 7.4.

Задание 7.5. Создать
по образцу бланк счета для аренды автомобиля в MS Excel.

Автомобиль использовался
с 12.10.04 00:00 до 14.10.04 16:40. Тарифная ставка « 120 р./ч.

Рис. 7.5 – Исходные данные для Задания 7.5.

Краткая справка. Для ввода даты используйте функцию «Сегодня». При вводе периода аренды
автомобиля используйте формат «Дата», в котором присутствуют дата и время.

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

Порядок работы

Для подсчета количества часов аренды
автомобиля установите в ячейке «Итого» числовой формат, рассчитайте разницу дат
пользования (Дата по: – Дата с:). Вы получите количество дней пользования
автомобилем. Для перевода количества дней пользования автомобилем в часы
произведите умножение на 24.

Расчет суммы счета
сделайте по следующей формуле: Всего = «Тариф за час» * Итого.

Практическая работа № 8

МЕТОДЫ ОБРАБОТКИ И АНАЛИЗА ИНФОРМАЦИИ В EXCEL

Цель занятия. Изучить приёмы работы со списками, формами, фильтрами и функциями базы
данных.

Порядок работы

Задание 8.1. Запустить приложение MS Excel.

Таблица 8.1

1.           
Переименовать «Лист 1» в «Базу Данных», «Лист 2» в
«Отчет».

2.           
На основе списка ваших товарищей создать свою базу
данных, аналогичную представленной в таблице 8.1. Таблицу разместить, начиная с
6-й строки листа. Первые 5 строк оставить пустыми (для ввода критериев). При
заполнении имён полей использовать в меню Формат команду Формат
ячеек; на вкладке Выравнивание установить флаг «переносить по
словам».

3.           
Отформатировать таблицу, применив команду Автоподбор
ширины столбца в меню Формат к ячейкам А6:I14.

4.           
Используя команду Форма меню Данные и
кнопку Добавить, ввести новые записи ещё о трёх своих товарищах в пустые
ячейки (обязательно мужского и женского пола).

5.           
Оставить ячейки поля Телефон двух-трех своих
товарищей не заполненными (телефоны отсутствуют). Закончив ввод данных,
щёлкнуть на кнопке Закрыть.

6.           
Дополнить таблицу столбцом J – средний
вступительный бал. Занести в ячейки столбца J случайные числа в диапазоне от
3,5 до 5, используя функцию СЛЧИС()*(5-3,5)+3,5.

7.           
Полученные данные столбца J (J7:J17) скопировать в
эти же ячейки, используя при копировании команду Специальная вставка и
флаг значения.

8.           
По завершению формирования базы данных ячейки
А6:J17 будут заполнены данными ваших товарищей.

9.           
Отформатировать таблицу с помощью команды Автоформат
меню Формат.

Задание 8.2. Используя
команду Сортировка меню Данные, отсортировать базу данных:

•    по фамилии в алфавитном порядке,

•    внутри полученного списка по возрасту,

•    затем по году обучения в порядке возрастания.

1.  Результаты сортировки скопировать на Лист «Отчет».

2.  Отсортировать БД:

•    по имени в порядке возрастания,

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

3. Полученные результаты скопировать на
Лист «Отчет» в свободные ячейки.

 Задание 8.3. Используя команду Форма
меню Данные и кнопку Критерии, отыскать студентов мужского пола,
возрастом старше 18 лет (>18)

Просмотреть все записи в прямом и
обратном порядке, используя кнопки Далее и Назад в окне Форма.

 Задание 8.4. Используя команду Фильтр/Автофильтр
в меню Данные, отобразить на экране (с помощью списков у соответствующих
полей) тех студентов, которые учатся 1 год и имеют возраст 18 лет. Скопировать
полученные результаты на Лист «Отчет» на свободные ячейки.

1.           
Восстановить исходное состояние БД (команда Все
в списках соответствующих полей).

2.           
Используя в списке полей Условие, отобразить
в таблице записи только студентов, имеющих самый младший и самый старший
«возраст» (18 ИЛИ 21). Скопировать полученные данные на Лист «Отчет».

3.           
Аналогично выбрать из исходного списка студентов,
имеющих средний состав семьи (>2 И <5). Результат скопировать на Лист
«Отчет».

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

5.           
Отобразить все записи и закрыть фильтр.

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

1. Сохранить файл под именем DBase.xls в
папке Мои документы, в своей личной папке.

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

•   количество
студентов, имеющих телефоны, в ячейке Е2.

•   максимальный
вступительный балл в ячейке H2,

•   минимальный
вступительный балл в ячейке I2,• среднее значение вступительного балла в ячейке
J2 .

Задание 8.7. В
соответствии с последней цифрой M номера студента по журналу, и с помощью
соответствующей функции работы с базой данных определить и вывести в свободную
ячейку Листа “Отчет” искомый параметр, указанный в таблице 8.2.:

Таблица 8.2

M	Искомый параметр
0	суммарный средний балл студентов, имеющих возраст 18 лет
1	максимальный средний балл студента, обучающегося на 1 курсе
2	минимальный средний бал студента мужского пола
3	количество первокурсников, имеющих телефон
4	средний состав семьи студентов в возрасте старше 19 лет
5	телефон студента, имеющего минимальный средний балл
6	год обучения (курс) студента, с максимальным средним баллом
7	фамилию студента, имеющего максимальный состав семьи
8	адрес студента, обучающегося на старшем курсе
9	имя студента, имеющего минимальный состав семьи

Для одновременного отображения формул на
Листе необходимо: в меню Сервис выбрать команду Параметры; в окне
диалога на вкладке Вид в списке Параметры окна поставить флаг Формулы.
Сохранить результаты работы в своей папке.

ЛИТЕРАТУРА

1.           
Информационные системы и технологии в экономике и
управлении: учебник / ред. В.В. Трофимов. – 3-е изд., перераб. и доп. – М.:
Юрайт, 2009. – 521 с.

2.           
Информационные технологии управления: учебник / Б.В.
Черников. – Москва: ИД «Форум»; Инфра-М, 2008. – 352 с: ил. – (Высшее
образование). – Гриф УМО.

3.           
Коряковцева, Н. А. Хрестоматия по информационной
культуре. 100 выпусков. № 59: брошюра / Н. А. Коряковцева. – М.:
Либереябибинформ, 2007. – 144 с.

4.           
Рагулина, М.И. Информационные технологии в
математике: учебное пособие / под ред. М.П. Лапчика. – М. : Изд-кий центр
«Академия», 2008. – 304 с.

5.           
Брыкова Н.В. Автоматизация бухгалтерского учета:
лабораторный практикум: учеб. пособие для учреждений нач. проф. образования /
Н.В. Быкова. – 6-е изд., стер. – М.: Издательский центр «Академия», 2007.

6.                      
Информатика. Базовый курс / Симонович С.В. и др. –
СПб.: Издательство «Питер», 2007.

Посчитать премию от оклада: варианты начисления

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

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

Отражают такие правила в особых документах, создание которых преследует 2 цели:

• включение премий в систему оплаты за труд;
• установление применяемого порядка премирования.

Как правильно оформить премии сотрудникам и отразить их в учете, узнайте в «КонсультантПлюс». Получите пробный доступ к системе и бесплатно переходите в Типовую ситуацию.

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

Как правило, документом, содержащим информацию о процедуре начисления премий, становится либо положение об оплате труда, либо положение о премировании. С 2017 года эти документы, представляющие собой внутренние нормативные акты, в ТК РФ (ст. 309.2) указаны как необязательные для создания в микропредприятиях. Однако отсутствие такого документа требует прописывать правила премирования, действующие у работодателя, в трудовом соглашении с каждым сотрудником. По существу, такой же порядок, допускающий варианты отражения условий премирования в разных внутренних документах (в т. ч. и в трудовом договоре), действовал и до нововведений 2017 года. Причем разработка отдельного документа о правилах премирования, касающихся всех сотрудников одновременно, всегда считалась более предпочтительной, поскольку позволяла сократить объемы информации, вносимые в трудовые соглашения.

О том, какие моменты должны найти отражение в нормативном акте о премировании в отношении премий за квартал, читайте в статье «Расчет квартальной премии за фактически отработанное время».

Какие коэффициенты применяются при расчете премии

Правила начисления премии могут не ставить процедуру ее расчета в зависимость от каких-либо иных условий, кроме величины оклада и размера доли, установленной для исчисления премиальных. В этом случае премия фактически становится фиксированной. Размер ее не будет зависеть от времени фактической работы или правил лишения премии.

Измениться величина премиальных в такой ситуации может только при изменении базы начисления, т. е. оклада. В зависимости от алгоритма, внесенного в основной документ о премировании, изменение оклада, произошедшее в периоде начисления премии, может быть учтено по-разному:

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

Последний вариант потребует расчета коэффициентов, учитывающих соответствующие соотношения количества дней.

Эксперты «КонсультантПлюс» рассказали о переходе с окладной системы оплаты труда на окладно-премиальную. Получите пробный доступ к материалу на данную тему бесплатно.

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

Если в регионе, где работает работодатель, действует районный коэффициент к зарплате, то сумма премии, рассчитанная от оклада, должна быть умножена на этот коэффициент.

О налогообложении начисленных премиальных читайте в материале «Какими налогами и взносами облагается премия сотрудникам?».

Пример расчета премии в файле Excel (он же — калькулятор)

Рассмотрим  пример расчета премии сотрудникам на цифрах. Предположим, что в ООО «Импульс» установлена начисляемая поквартально премия, если ООО за соответствующий квартал достигнуты определенные финансовые результаты по итогам работы. Размер премии составляет 30% от оклада.

В положении о премировании, утвержденном в ООО, зафиксировано, что начисление не производится за не полностью отработанные работником месяцы. Для квартальной премии это означает, что сумма, рассчитанная как 30%-ная доля оклада, должна быть умножена на коэффициент, учитывающий количество полностью отработанных в квартале месяцев. Соответственно, этот коэффициент может составлять величину 2/3 либо 1/3.

За совершение дисциплинарного проступка в периоде премирования предусмотрено лишение сотрудника премии за этот период полностью.

При изменении оклада в любом из месяцев периода премирования расчет премии за квартал делается от новой величины оклада.

В регионе работы ООО действует районный коэффициент к зарплате, равный 1,4.

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

Скачать пример расчета

Учет премии при расчете среднего заработка

Премии, предусмотренные системой оплаты труда и имеющие установленные правила их начисления, учитывают в расчете среднего заработка (ст. 139 ТК РФ). Однако они по-разному входят в этот расчет в зависимости от ряда обстоятельств (положение об особенностях порядка исчисления среднего заработка, утвержденное постановлением Правительства РФ от 24.12.2007 № 922):

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

Различные сочетания этих обстоятельств приводят к тому, что премию оказывается возможным взять в расчет:

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

Читайте подробнее о расчете среднего заработка в тематической публикации, подготовленной экспертами «КонсультантПлюс». Получите пробный доступ к документу бесплатно.

Для премий, определяемых от оклада, возможны практически все варианты принятия в расчет, поскольку они могут начисляться:

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

Обо всех возникающих вариантах учета премий в расчете среднего заработка читайте в материале «Учитывается ли премия при расчете отпускных?».

Итоги

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