Использование надстройки MS Excel «Подбор
Лабораторная работа № 2. Достижение требуемых
Экономических показателей средствами MS Excel
Цель работы: изучение надстройки «Подбор параметра» в среде MS Ехсеl.
При решении экономических задач достаточно часто возникает проблема подбора параметра. Так, в экономических расчётах применяются стандартные алгоритмы расчёта стоимости товара, расчёта фонда заработной платы, прибыли от деятельности предприятия, которые, в свою очередь, зависят от множества факторов, которые условно можно разделить на изменяемые и неизменяемые. К первым относятся оклад работника, отпускная или закупочная цена товара, доля расходов на рекламу, страхование и т.д., ко вторым – ставки налогов и сборов. Для получения требуемых экономических показателей деятельности предприятия должны быть либо подобраны, либо рассчитаны некоторые изменяемые параметры. Так как стандартные расчётные алгоритмы весьма сложные, преобразование алгоритма относительно изменяемого параметра является трудоёмкой математической задачей, часто с неоднозначным результатом, а подбор параметром вручную требует длительного времени (а в нелинейных расчётных соотношениях – ещё и удачи). Поэтому оптимальным во многих случаях представляется автоматический подбор численного значения параметра, выполняемый с помощью программного обеспечения ЭВМ, например табличного процессора MS Excel.
|
|
|
Постановка задачи. Деятельность торговой организации зачастую включает в себя взятие кредита на покупку партии товара. Требуется обосновать рациональность использования кредитных средств под 20 % годовых для закупки сахарного песка по цене 15000 руб. за тонну с целью его дальнейшей реализации. Для упрощения работы не учитывается налогообложение кредита.
Основные расчётные соотношения следующие:
 ;
| (2.2.1) |
 ;
| (2.2.2) |
 ;
| (2.2.3) |
| (2.2.4) |
| (2.2.5) |
 .
| (2.2.6) |
Порядок решения задачи
Подготовка шаблона для решения задачи
Фрагмент рабочего листа MS Excel с введёнными исходными данными и формулами изображён на рис. 2.2.1.
| А | B | C | ||
| 1 | Технико-экономическое обоснование торговой операции | ||||
| 2 | |||||
| 3 | Закупка | ||||
| 4 | |||||
| 5 | тип товара | сахар |
| ||
| 6 | Ед. измерения | тонны |
| ||
| 7 | Цена за единицу, руб. | 15000 |
| ||
| 8 | Размер партии | 1000 |
| ||
| 9 | Цена всей партии, руб. | =B7*B8 |
| ||
| 10 |
|
|
| ||
| 11
| Кредит | ||||
| 12 |
|
|
| ||
| 13 | % по кредиту в год | 0.2 |
| ||
| 14 | Срок кредита, мес. | 3 |
| ||
| 15 | Страховка кредита | 0.1 | =B16*(1+B13/12*B14)*B15 | ||
| 16 | Необходимая сумма кредита, руб. | =(B9+B21+B22+C24+C25+C26+C27)/(1-(B15+B23)-B13/12*B14*(B15+B23)) | |||
| 17 | % по кредиту в месяц, руб. | =B16*B13/12 |
| ||
| 18 |
|
|
| ||
| 19 | Накладные расходы | ||||
| 20 |
|
|
| ||
| 21 | Транспортные расходы | 2000000 |
| ||
| 22 | Непредвиденные расходы | 100000 |
| ||
| 23 | Страхование в пути | 0.02 | =B23*B9 | ||
| 24 | Акциз | 0.1 | =B24*B9 | ||
| 25 | НДС | 0.1 | =B25*(B9+C23+C24+C26+C27) | ||
| 26 | Таможенный сбор | 0.01 | =B26*B9 | ||
| 27 | Таможенный налог | 0.15 | =B27*B9 | ||
| 28 |
|
|
| ||
| 29 | Реализация товара и возврат кредита | ||||
| 30 |
|
|
| ||
| 31 | Цена реализации за ед., руб. | 25000 |
| ||
| 32 | Цена реализации за партию, руб. | =B31*B8 |
| ||
| 33 | Возврат кредита с %, руб. | =B16+B17*B14 |
| ||
| 34 | Прибыль до вычёта налогов, руб. | =B32-B33 |
| ||
Рис. 2.2.1. Шаблон MS Excel для решения задачи
Использование надстройки MS Excel «Подбор
Параметра»
2.1. С заданными исходными данными значение прибыли до вычета налогов, рассчитанное в ячейке B34, является отрицательным. Если нужно обеспечить безубыточность операции, т.е. нулевое значение прибыли, меняют цену реализации (цену реализации в ячейке В31 измените на 27000 и убедитесь, что значение прибыли (убытка) в В34 изменилось). Можно изменять значение в ячейке В31 вручную, значение прибыли в ячейке В34 будет меняться, но такой вариант требует значительных затрат времени, не точен, а следовательно, неэффективен. Значение цены реализации подбирают автоматически. Для этого вызовите надстройку Подбор параметра командой Сервис ® Подбор параметра и заполните в открывшемся окне поля так, как изображено на рис. 2.2.2. После нажатия на кнопку ОК убедитесь (по сообщению в открывшемся окне), что решение найдено.
|
|
|
Рис. 2.2.2. Пример заполнения окна Подбор параметра
2.2. Решите по аналогии с п. 2.1 следующую задачу: получите прибыль X руб. за счёт изменения параметра Y. Номер варианта задания выберите в соответствии с распределением. Варианты заданий приведены в табл. 2.2.1.
Таблица 2.2.1
Варианты заданий для самостоятельного решения
к лабораторной работе № 2
| Номер варианта | Значение прибыли X, руб. | Название варьируемого параметра Y |
| 1 | 10 000 000 | Закупочная цена (B7) |
| 2 | 3 000 000 | % по кредиту (В13) |
| 3 | 500 000 | Страхование в пути (В23) |
| 4 | 2 000 000 | Акциз (В24) |
| 5 | 2 500 000 | НДС (В25) |
| 6 | 300 000 | Таможенный налог (В27) |
| 7 | 8 000 000 | Срок кредита (В14) |
| 8 | 800 000 | Страхование кредита (В15) |
| 9 | 2 500 000 | Транспортные расходы (В21) |
| 10 | 200 000 | Непредвиденные расходы (В22) |
| 11 | 1 750 000 | Таможенный сбор (В26) |
Покажите полученную рабочую книгу MS Excel преподавателю.
|
|
|
Дата добавления: 2018-10-27; просмотров: 117; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!