Таблица 5 - Ведомость анализа реализованных товаров

Методика выполнения практической части работы

1 Алгоритм вычисления:

ОС=БС-ИО;

ВП=БС*К, где К=3 если БС>500 и К=2 если БС<=500

ВО=ОС*К, где К=3 если БС>500 и К=2 если БС<=500

3 Выполнить сортировку ведомости по возрастанию Видов объектов, а внутри каждого вида – по возрастанию Наименования объектов.

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

4 Рассчитать общую (суммарную) балансовую стоимость, износ и общую остаточную стоимость всех основных и вспомогательных видов объектов с помощью команды Данные - Итоги. После анализа результатов вернуть таблицу в исходное состояние.

5 С помощью команды Расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых больше 500 млн. тенге.

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

7 Построить на отдельном рабочем листе Excel смешанную диаграмму (график/гистограмму2), в которой необходимо показать значения балансовой и остаточной стоимостей для всех вспомогательных объектов в виде гистограммы, а значения восстановительной (полной) стоимости - в виде графика на той же диаграмме.

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

5-7 санитарок;

8-10 медсестер;

10-12 врачей;

1 заведующий аптекой;

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

1 главный врач;

1 завхоз;

1 заведующий больницей.

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

Оклад санитарки принимается за расчетную единицу А=1 В=0

Медсестра должна получать в 1,5 раза больше санитарки =1,5 В=0

Врач - в 3 раза больше санитарки А=3 В=0

Заведующий отделением - на $30 больше, чем врач А=З В=30

Заведующий аптекой - в 2 раза больше санитарки А=2 В=0

Завхоз - на $40 больше медсестры А=1,5 В=40

Главный врач - в 4 раза больше санитарки А=4 В=0

Заведующий больницей - на $20 больше главного врача А=4 В=20

Задав количество человек на каждой должности, можно составить уравнение:

N1*(A1*C+B1)+N2*(A2*C+B2)+ ...N8*(A8*C+B8)=10000, где:

N1- количество санитарок;

N2- количество медсестер и т.д.

А1...А8 и В1...В8- коэффициенты для каждой должности.

В этом уравнении нам известны А1...А8 и В1...В8, а не известны С и N1...N8.

Ясно, что для такого уравнения единственно верного решения нет. Поэтому необходимо, взяв первоначально какие-либо приемлемые значения неизвестных, подсчитать сумму. Если эта сумма не будет равна $10000, то необходимо либо изменять оклад санитарки, либо количество работников на какой-либо должности.

Методика выполнения практической части работы

Вначале следует заполнить таблицу.

В столбцы А, В, С, Е и Н этой таблицы следует ввести необходимые данные.

Далее ячейке НЗ желательно присвоить имя (Вставка→Имя→Присвоить), например «ЗС». (Это абсолютная адресация, удобно для копирования).

В ячейку D3 вносим формулу =A3*ЗС + B3, где ЗС - зарплата санитарки (Если ячейке НЗ не присваивать имя, то формула будет такой: =АЗ*$Н$3+ВЗ). Размножим формулу до конца данных в столбце D

Щелкнем по ячейке F3. Введем в нее формулу =D3*E3. Размножим эту формулу до ячейки F10.

В ячейку F12 введем формулу =СУММ(FЗ:F10). Попробуйте сделать это при помощи значка «Автосумма»

Данные в столбце «Количество сотрудников»отмечены данные, которые не могут быть изменены. Мы видим, что суммарный месячный фонд зарплаты превышает $10000.

А теперь заставим Excel проделать эту работу за нас:

Щелкните по ячейке F12. Выберите Сервис→ Подбор Параметра. В появившемся диалоговом окне необходимо установить в поле ввода «Установит ячейке» - значение $F$12; в поле ввода «Значение» - 10000; в поле ввода «Изменяя ячейку значение $Н$3 и нажать Ok. Мы увидим в Н3, какой должна быть зарплата санитарки.

Составьте и просчитайте на этом же листе еще вариант таблицы со следующим количеством сотрудников: Санитарок - 7, медсестер -10, врачей -12

Данные функции вызываются командой Вставка, Функции или нажатием кнопки Мастер функций. Далее устанавливается категория Финансовые и в списке выбирается нужная функция, как показано на рисунке 1.

Аргументы:

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

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

Рассмотрим пример использования функции БЗ. Необходимо рассчитать, какая сумма будет на счете, если 3 миллиона тенге размещены под 12 % годовых на пять лет, и проценты начисляются каждый месяц. Будущая стоимость вычисляется следующим способом:

=БС(12%/12;5*12;;-3000000).

Через пять лет на счете будет 5450090 тенге.
Задачи для решения по функции БС.

Финансовая функция ПЗ (ПС), отображенная на рисунке 3, предназначена для расчета текущей стоимости как единой суммы вклада (займа), так и будущих платежей (фиксированных и периодических). Этот расчет является обратным к определению будущей стоимости функцией БЗ.

Рисунок 3 – Диалоговое окно функции ПЗ (ПС)

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

Рассмотрим пример решения задачи с помощью функции ПЗ. Необходимо определить, какую сумму нужно положить на депозит в настоящее время, чтобы при ставке 11,5 % годовых и ежемесячных вкладах на данный депозит в размере 1500 тенге через пять лет получить 100 тысяч тенге.

Используя функцию ПЗ, получим:

=ПЗ(11,5%/12;5*12;-1500;100000).

Ответ: в настоящее время на депозит нужно положить 11780 тенге.

Задачи для решения по функции ПС.

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

Окно данной функции показано на рисунке 4.


Рисунок 4 – Диалоговое окно функции ЧПС (НПЗ)
Синтаксис: ЧПС (норма; значение1; значение2; …; значение29).

Аргументы:

• 
  норма – норма скидки за один период;
  
• 
  значение1, …, значение29 – от 1 до 29 аргументов, представляющих расходы и доходы в конце каждого периода.
  

Функция ПЗ аналогична функции ЧПС. Основное различие между ними заключается в том, что функция ПЗ допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода' Кроме того, в отличие от функции ЧПС, денежные взносы в функции ПЗ должны быть постоянными и равными на весь период инвестиции, тогда как функция ЧПС допускает неодинаковые значения.

Рассмотрим пример использования функции ЧПС (НПЗ). Затраты по проекту в начальный момент его реализации составляют 37 миллионов тенге, а ожидаемые доходы за первые пять лет – 8, 9,2, 10, 13,9 и 14,5 миллионов. На шестой год ожидается убыток в размере 6,5 миллионов тенге. Цена капитала – 8 % годовых. Необходимо рассчитать чистую текущую стоимость проекта.

Настоящее приведенное значение рассчитывается следующим способом:

=ЧПС(8%;8;9,2;10;13,9;14,5;-6,5).

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

Задачи для решения по функции ЧПС

Финансовая функция КПЕР, изображенная на рисунке 5, используется для расчета срока платежей. Эта функция вычисляет общее число периодов выплат как единой суммы вклада, так и для периодических постоянных выплат на основе постоянной процентной ставки.

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

Рассмотрим следующий пример: предприниматель берет кредит в 100000 тенге при годовой ставке 12 % и собирается выплачивать по 2000 тенге в месяц. Число выплат вычисляется следующим образом:

=КПЕР(12%/12;-2000;100000;0;1).

Срок выплаты кредита составит округленно 69 месяцев или 5,7 лет.

Для данной функции существенно, когда должна производиться выплата – в начале или в конце периода. Если в предыдущем примере в графе «тип» поставить 0, то срок составит округленно 70 месяцев или 5,8 лет.

Функция СТАВКА (НОРМА) вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Диалоговое окно данной функции представлено на рисунке 6.

Все значения аргументов данной функции совпадают со значениями предыдущих финансовых функций. Аргумент «предположение» («нач_прибл») - предполагаемая величина нормы. Если «предположение» опущено, то оно полагается равным 10%. Если функция СТАВКА не сходится, следует попытаться использовать различные значения «нач_прибл». Обычно функция СТАВКА сходится, если «нач_прибл» имеет значение между 0 и 1.

Следует отметить, что функция СТАВКА вычисляет процентную ставку методом итераций, поэтому решение может быть и не найдено. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки #ЧИСЛО!.

Рассмотрим пример использования функции СТАВКА. Чтобы определить процентную ставку для четырехлетнего займа размером в 8000 тенге с ежемесячной выплатой 200 тенге, можно использовать формулу:

=СТАВКА(4*12;-200;8000).

В результате получаем: месячная (так как период равен месяцу) процентная ставка равна 0,77 %.

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

Аргументы:

• 
  первичное - текущее значение вклада (инвестиций);
  
• 
  план – массив применяемых процентных ставок.
  

Рассмотрим пример использования финансовой функции БЗРАСПИС. По облигации номиналом 100 тысяч тенге, выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в 1 год – 10 %, 2, 3 - по 20 %, 4, 5, 6 – по 25 %. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Для решения данной задачи необходимо занести все процентные ставки на рабочий лист, как показано на рисунке 8. Далее в графу «первичное» заносится номинальная стоимость облигации, а в графу «план» - массив процентных ставок из диапазона B1:B6.

Рисунок 8 – Пример использования функции БЗРАСПИС

Функция ВНДОХ (ВСД), представленная на рисунке 9, возвращает внутреннюю скорость оборота для ряда потоков денежных средств. Объемы операций не обязаны быть регулярными, как в случае ренты. Внутренняя скорость оборота – это процентная ставка дохода, полученного от инвестиций, состоящих из выплат (отрицательные значения) и поступлений (положительные значения), которые происходят в регулярные периоды времени.

Аргументы:

• 
  значения - массив или ссылка на ячейки, содержащие числовые величины, для которых вычисляется внутренняя скорость оборота средств. Значения должны включать, по крайней мере, одно положительное значение и одно отрицательное значение, для того чтобы можно было вычислить внутреннюю скорость оборота. Функция ВНДОХ, использует порядок значений для интерпретации порядка денежных выплат или поступлений, поэтому нужно следить, чтобы значения выплат и поступлений вводились в правильном порядке;
  
• 
  предположение - величина, о которой предполагается, что она близка к результату ВНДОХ.
  

В большинстве случаев нет необходимости задавать предположение для вычислений с помощью функции ВНДОХ. Если предположение опущено, то оно полагается равным 0,1 (10%).

Если ВНДОХ выдает значение ошибки #ЧИСЛО! или результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз, но уже с другим предположением.

Рассмотрим пример использования функции ВНДОХ. Предположим, вы хотите открыть обменный пункт. Вы вносите первоначальную сумму для организации бизнеса в 50 тысяч долларов и ожидаете получить чистый доход в течение 5 лет в следующих размерах: 1-й год – 12; 2-й год – 13; 3-й год – 15; 4-й год – 17; 5-й год – 18 тысяч долларов. Вычислить внутреннюю скорость оборота инвестиций (процентная ставка дохода от вложенного капитала) после двух, трех, четырех и пяти лет. Решение данной задачи представлено на рисунке 10.

Задачи для решения.