Лабораторная работа №6. Анализ затрат на амортизационные отчисления
Цель работы: Проанализировать затраты на амортизационное отчисление.
Постановка задачи:
Первоначальная стоимость оборудования магазина составляет 121000 руб. При этом рассматриваемое предприятие применяет для начисления амортизации метод уменьшаемого остатка.
Известно, что срок полезного использования основных средств магазина «Домовой» составляет 16 лет. Таким образом, при учете, его остаточная стоимость составляет 1030 руб., можно рассчитать размер ежегодных амортизационных отчислений, используя такую функцию, как ДДОБ.
Ход выполнения работы:
Функция ДДОБ Возвращает значение амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанный метод.
Синтаксис
ДДОБ(нач_стоимость;ост_стоимость;время_эксплуатации;период;коэффициент)
Нач_стоимость - начальная стоимость актива.
Ост_стоимость - стоимость в конце периода амортизации (иногда называемая остаточной стоимостью имущества); может быть равна нулю.
Время_эксплуатации - количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).
Период - период, для которого требуется вычислить амортизацию. Период должен быть измерен в тех же единицах, что и время_эксплуатации.
Коэффициент - процентная ставка снижающегося остатка. Если коэффициент опущен, то он полагается равным 2 (метод удвоенного процента со снижающегося остатка).
Произведем заполнение таблицы – см. таблицу 6.1, содержащую формулы для вычислений. В таблице 6.2 приведены результаты вычислений амортизационных отчислений.
Таблица 6.1 – Формулы для вычислений
|
Год |
Расчет амортизации |
|
1 |
=ДДОБ($E$1;$E$2;$E$3;A2) |
|
2 |
=ДДОБ($E$1;$E$2;$E$3;A3) |
|
3 |
=ДДОБ($E$1;$E$2;$E$3;A4) |
|
4 |
=ДДОБ($E$1;$E$2;$E$3;A5) |
|
5 |
=ДДОБ($E$1;$E$2;$E$3;A6) |
|
6 |
=ДДОБ($E$1;$E$2;$E$3;A7) |
|
7 |
=ДДОБ($E$1;$E$2;$E$3;A8) |
|
8 |
=ДДОБ($E$1;$E$2;$E$3;A9) |
|
9 |
=ДДОБ($E$1;$E$2;$E$3;A10) |
|
10 |
=ДДОБ($E$1;$E$2;$E$3;A11) |
|
11 |
=ДДОБ($E$1;$E$2;$E$3;A12) |
|
12 |
=ДДОБ($E$1;$E$2;$E$3;A13) |
|
13 |
=ДДОБ($E$1;$E$2;$E$3;A14) |
|
14 |
=ДДОБ($E$1;$E$2;$E$3;A15) |
|
15 |
=ДДОБ($E$1;$E$2;$E$3;A16) |
|
16 |
=ДДОБ($E$1;$E$2;$E$3;A17) |
|
Итого: |
=СУММ(B2:B17) |
Таблица 6.2 – Результаты вычислений амортизационных отчислений
|
Год |
Расчет амортизации |
|
1 |
15 125,00 |
|
2 |
13 234,38 |
|
3 |
11 580,08 |
|
4 |
10 132,57 |
|
5 |
8 866,00 |
|
6 |
7 757,75 |
|
7 |
6 788,03 |
|
8 |
5 939,53 |
|
9 |
5 197,08 |
|
10 |
4 547,45 |
|
11 |
3 979,02 |
|
12 |
3 481,64 |
|
13 |
3 046,44 |
|
14 |
2 665,63 |
|
15 |
2 332,43 |
|
16 |
2 040,87 |
|
Итого: |
106 713,88 |
Выводы: изучена встроенная функция ДДОБ, с помощью которой можно вычислить величину амортизационных отчислений.
Лабораторная работа №7. Информационные технологии решения задач бизнес-анализа
Цель работы: Научиться находить оптимальное решение в условиях дефицита ресурсов с использованием компьютерной технологии.
Постановка задачи:
Фирма выпускает продукцию 4 видов: продукт 1, продукт 2, продукт 3 и продукт 4. Для выпуска использует 3 вида ресурсов: трудовые, сырьевые, финансовые.
Известны: нормы расходов (количество ресурса каждого вида), необходимые для выпуска единицы продукции данного типа, сколько ресурса имеется в наличии, а также прибыль, получаемая от реализации единицы каждого типа продукции – см. таблицу 7.1.
Определить, в каком количестве нужно выпускать продукцию каждого вида, чтобы получить от ее производства максимальную прибыль.
Таблица 7.1 – Исходные данные
|
Ресурс |
Продукт1 |
Продукт2 |
Продукт3 |
Продукт4 |
Знак ограничения |
Наличие |
|
Прибыль |
60 |
70 |
120 |
130 |
Max |
|
|
Трудовые |
1 |
1 |
1 |
1 |
<= |
16 |
|
Сырьевые |
6 |
5 |
4 |
3 |
<= |
110 |
|
Финансовые |
4 |
6 |
10 |
13 |
<= |
100 |
Ход выполнения работы:
Введем обозначения, пусть xj – количество выпускаемой продукции j-го вида, j = 1, 2, 3, 4; Bi – количество имеющегося в наличии ресурса i-го вида, i = 1, 2, 3; Aij – норма расхода ресурса i-го вида для производства единицы продукции j-го вида; Cj – прибыль, получаемая от реализации единицы продукции j-го вида.
Тогда требуется решить следующую задачу оптимизации:
F = 60x1 + 70x2 + 120x3 + 130x4 -> max
x1 + x2 + x3 + x4 ≤ 16
6x1 + 5x2 + 4x3 + 3x4 ≤ 110
4x1 + 6x2 + 10x3 + 13x4 ≤100.
На рабочем листе Excel подготовим форму для ввода условий задачи. Для этого в ячейку D1 введем слово Переменные , а в ячейку D7 – Ограничения.
Далее в диапазон B2:E3 ввести соответственно Продукция1, …, Продукция 4. В ячейку A2 – Имя, А3 – Значение, А4 – Нижняя граница, А5 – Верхняя граница, А6 – Коэффициенты целевой функции и т.д. Все это является комментариями и на решение задачи никак не повлияет. Далее производим заполнение таблицы исходными данными согласно данным таблицы 7.1. Заполненная форма представлена на рис. ниже.
Введем зависимости для целевой функции и левой части ограничений. Так в ячейку F6 (значение целевой функции) введем следующую формулу =СУММПРОИЗВ(B3:E3;B6:E6). А в ячейку F9 введем формулу =СУММПРОИЗВ($B$3:$E$3;B9:E9). Протянем эту функцию на ячейки F10:F11. В итоге мы заполнили таблицу исходными данными и вычислимыми полями. Можно переходить к нахождению оптимального решения – см. рис. ниже.
Воспользуемся функционалом Поиск решения. Установим следующие параметры.
Нам будет выдано следующее диалоговое окно:
Решение найдено, после нажатия кнопки Ok, мы увидим его в нашей таблице. Так нам следует производить продукт 1 в количестве 10 штук, продукт 3 в количестве 6 штук и не производить продукты 2 и 4 вовсе. При этом прибыль составит 1320 ден. ед. – см. ниже.
При этом количество использованных трудовых ресурсов составит 16, сырьевых 84, а финансовых – 100. То есть финансы и трудовые ресурсы были использованы в полном объеме, а сырье – нет (остаток составляет 16 ед.).
Дополнительно посмотрим результаты нахождения решения. Повторно вызовем диалоговое окно Поиск решения, не изменяя параметров поиска выберем пункт Результаты, в результате получим следующую таблицу.
|
Microsoft Excel 12.0 Отчет по результатам |
|
|
| |||||||
|
Рабочий лист: [лабораторные в Excel.xlsx]лр7 |
|
|
| |||||||
|
Отчет создан: 11.05.2013 14:00:20 |
|
|
|
| ||||||
|
|
|
|
|
|
|
| ||||
|
|
|
|
|
|
|
| ||||
|
Целевая ячейка (Максимум) |
|
|
|
| ||||||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
| ||||
|
|
$F$6 |
коэфф. Цф |
1320 |
1320 |
|
| ||||
|
|
|
|
|
|
|
| ||||
|
|
|
|
|
|
|
| ||||
|
Изменяемые ячейки |
|
|
|
| ||||||
|
|
Ячейка |
Имя |
Исходное значение |
Результат |
|
| ||||
|
|
$B$3 |
значение Прод1 |
10 |
10 |
|
| ||||
|
|
$C$3 |
значение Прод2 |
0 |
0 |
|
| ||||
|
|
$D$3 |
значение Прод3 |
6 |
6 |
|
| ||||
|
|
$E$3 |
значение Прод4 |
0 |
0 |
|
| ||||
|
|
|
|
|
|
|
| ||||
|
|
|
|
|
|
|
| ||||
|
Ограничения |
|
|
|
| ||||||
|
|
Ячейка |
Имя |
Значение |
Формула |
Статус |
Разница | ||||
|
|
$F$9 |
трудовые левая часть |
16 |
$F$9<=$H$9 |
связанное |
0 | ||||
|
|
$F$10 |
сырье левая часть |
84 |
$F$10<=$H$10 |
не связан. |
26 | ||||
|
|
$F$11 |
финансы левая часть |
100 |
$F$11<=$H$11 |
связанное |
0 | ||||
|
|
$B$3 |
значение Прод1 |
10 |
$B$3>=0 |
не связан. |
10 | ||||
|
|
$C$3 |
значение Прод2 |
0 |
$C$3>=0 |
связанное |
0 | ||||
|
|
$D$3 |
значение Прод3 |
6 |
$D$3>=0 |
не связан. |
6 | ||||
|
|
$E$3 |
значение Прод4 |
0 |
$E$3>=0 |
связанное |
0 | ||||
Данный отчет показывает информацию о целевой функции, приводит значения искомых переменных, полученные в результате решения, а также показывает информацию об ограничениях. Так видно, что все ограничения на ресурсы, за исключением ограничения на сырьевые ресурсы, являются связанными (то есть ресурс использован в полном объеме). Сырье не было использовано в полном объеме (как мы уже говорили ранее), поэтому это ограничение является не связанным.
Вызовем также другой тип отчета – отчет об устойчивости и отчет по пределам.
|
Microsoft Excel 12.0 Отчет по устойчивости |
| |||||
|
Рабочий лист: [лабораторные в Excel.xlsx]лр7 | ||||||
|
Отчет создан: 11.05.2013 14:03:39 |
|
| ||||
|
|
|
|
|
| ||
|
|
|
|
|
| ||
|
Изменяемые ячейки |
|
| ||||
|
|
|
|
Результ. |
Нормир. | ||
|
|
Ячейка |
Имя |
значение |
градиент | ||
|
|
$B$3 |
значение Прод1 |
10 |
0 | ||
|
|
$C$3 |
значение Прод2 |
0 |
-10 | ||
|
|
$D$3 |
значение Прод3 |
6 |
0 | ||
|
|
$E$3 |
значение Прод4 |
0 |
-20 | ||
|
|
|
|
|
| ||
|
Ограничения |
|
| ||||
|
|
|
|
Результ. |
Лагранжа | ||
|
|
Ячейка |
Имя |
значение |
Множитель | ||
|
|
$F$9 |
трудовые левая часть |
16 |
20 | ||
|
|
$F$10 |
сырье левая часть |
84 |
0 | ||
|
|
$F$11 |
финансы левая часть |
100 |
10 | ||
|
Microsoft Excel 12.0 Отчет по пределам |
|
|
|
|
| ||||||||||||||
|
Рабочий лист: [лабораторные в Excel.xlsx]Отчет по пределам 1 |
| ||||||||||||||||||
|
Отчет создан: 11.05.2013 14:06:08 |
|
|
|
|
|
| |||||||||||||
|
|
|
|
|
|
|
|
|
|
| ||||||||||
|
|
|
|
|
|
|
|
|
|
| ||||||||||
|
|
|
Целевое |
|
|
|
|
|
|
| ||||||||||
|
|
Ячейка |
Имя |
Значение |
|
|
|
|
|
| ||||||||||
|
|
$F$6 |
коэфф. Цф |
1320 |
|
|
|
|
|
| ||||||||||
|
|
|
|
|
|
|
|
|
|
| ||||||||||
|
|
|
|
|
|
|
|
|
|
| ||||||||||
|
|
|
Изменяемое |
|
|
Нижний |
Целевой |
|
Верхний |
Целевой | ||||||||||
|
|
Ячейка |
Имя |
Значение |
|
предел |
результат |
|
предел |
результат | ||||||||||
|
|
$B$3 |
значение Прод1 |
10 |
|
0 |
720 |
|
10 |
1320 | ||||||||||
|
|
$C$3 |
значение Прод2 |
0 |
|
0 |
1320 |
|
0 |
1320 | ||||||||||
|
|
$D$3 |
значение Прод3 |
6 |
|
0 |
600 |
|
6 |
1320 | ||||||||||
|
|
$E$3 |
значение Прод4 |
0 |
|
0 |
1320 |
|
0 |
1320 | ||||||||||
Таким образом, для получения максимальной прибыли нам следует производить продукт 1 в количестве 10 штук, продукт 3 в количестве 6 штук и не производить продукты 2 и 4 вовсе. При этом прибыль составит 1320 ден. ед. Любое другое сочетание объемов производимой продукции приведет к снижению получаемой прибыли.
При этом количество использованных трудовых ресурсов составит 16, сырьевых 84, а финансовых – 100. То есть финансы и трудовые ресурсы были использованы в полном объеме, а сырье – нет (остаток составляет 16 ед.).
Заметим, что недоиспользование трудовых или финансовых ресурсов будет приводить к уменьшению прибыли. Так уменьшение использования на единицу трудовых ресурсов приведет к снижению прибыли на 20 ден. ед. (см. отчет по устойчивости, теневая цена, множитель Лагранжа), а финансовых на 10 ден. ед.
Показатель нормируемая стоимость (отчет по устойчивости) показывает, что включение в производственный план продукции 2-го и 4-го видов нецелесообразно. Так как производство продукции в объеме одна единица приведет к снижению прибыли на 10 и 20 ден. ед. соответственно.
Нулевые двойственные оценки переменных показывают, что производство продукции 1-го и 3-го вида экономически выгодно.
Так как трудовые и финансовые ресурсы использованы полностью, то ограничения являются жесткими (связанными). Их смягчение привело бы к увеличению прибыли. В данном случае – увеличение трудовых и финансовых ресурсов (это привлечение будет эффективным, пока сырьевые ресурсы не будут исчерпаны).
В столбце целевой результат указаны значения целевой функции при выпуске данного типа продукции на нижнем приделе. Так, при значении, 720 видно, что целевая функция составит 60*0 + 120*6 = 720.
Вывод: если есть возможность привлечь дополнительные трудовые и финансовые ресурсы, то нужно расширять производство.