9) Тема: Создание приложения на vba для расширенного анализа данных

Цель:

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

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

Методические указания:

Расширенный анализ данных (Data Envelopment Analysis — DEA) — это метод определения эффективности работы подразделения (например, банка, больницы или школы). Эффективность рассчитывается для произвольных затрат. Например, с помощью DEA можно проанализировать работу нескольких банковских отделений, |ж в качестве затрат рассматриваются рабочее время, площадь помещений и используемые ресурсы. Результатом анализа работы таких подразделений может быть количество операций кредитования, создания депозитных счетов и обработанных шов за определенный период времени. Всегда можно использовать эти данные в ■«скольких моделях линейного программирования, по одной для каждого отделения. Модели используются для проверки эффективности отделения (при этом сравнивается стоимость затрат и стоимость получаемого результата). По определению отделение считается эффективным, если общая стоимость результата равна общей стоимости затрат. Если общая стоимость получаемого результата меньше общей стоимости затрат, то отделение является неэффективным. Описываемое приложение получает данные из текстового файла (. txt), накраивает модель Поиск решения, запускает модель для каждого подразделения и обобщает полученные результаты. Кроме всего прочего, приложение демонстрирует, как можно импортировать данные из текстового файла в приложение Excel.

Функциональные возможности приложения:

Исходные данные для приложения приведены в файле DEA.txt. Это простой текстовый файл, который можно создать с помощью редактора Блокнот. В файле перечислены затраты и получаемые результаты, название подразделений, такая установлено соответствие между затратами и конечными результатами. Приложении импортирует эти данные в файл приложения DEA.xls, где они используются в качестве параметров модели линейного программирования. Такая модель создается для каждого подразделения с целью выяснить его эффективность. Результаты выводятся на лист Отчет.

В текущем виде приложение требует, чтобы файлы DEA. txt и DEA. xls находились в одной папке. Текущая версия файла DEA. txt содержит данные о четыре подразделениях (факультетах университета). Каждое подразделение имеет три входных параметра и два результата. Эти данные можно заменить на любые другие В файле можно указать любое количество подразделений, входных параметров и результатов. При этом приложение будет работать корректно. Формат данных фай DEA. txt рассматривается далее.

10) Тема: Создание приложения на vba для расчета бюджета

Цели:

• Показать, как простой эвристический анализ реализуется в VBA с помощью циклов и массивов.

• Показать, как случайные входные параметры модели могут генерироваться с помощью VBA посредством формул рабочего листа. Более того, эти значения необходимо сделать постоянным с помощью методов Сору и Paste Special объекта Range.

Функциональные возможности приложения

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

1. Сначала у пользователя запрашивается общее количество проектов, указать любое количество проектов, но не более 30. После этого случайным образом генерируются входные параметры модели для указанного количества проектов — начальные стоимости, чистая приведенная стоимость и размер бюджета. Размер бюджета выбирается таким образом, чтобы его хватило на | выполнение большинства, но не всех проектов.

2. После получения входных параметров на (скрытом) листе Модель создается модель бюджета. Сгенерированная таким образом модель оптимизируется с помощью надстройки Поиск решения. Также для поиска оптимального решения используется алгоритм эвристического анализа. Результаты, возвращаемые в обоих случаях, отображаются на листе Отчет, включая процентное отношение значения чистой приведенной стоимости, полученной с помощью эвристического анализа, к значению чистой приведенной стоимости, рассчи-1 тайной с помощью надстройки Поиск решения.

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

4. Для изучения влияния параметра Tolerance надстройки Поиск решения (который при каждом ее запуске установлен в значение 0) на получаемые результаты мы решили в файл включить лист Interesting. На листе описывается проблема, в которой надстройка Поиск решения не может найти оптимальное решение, если параметр Tolerance установлен в принятое по умолчанию значение 5. (Этот лист не является частью приложения, но он иллюстрирует интересный поведенческий аспект надстройки Поиск решения.)

Методические указания:

В этом приложении должно быть продемонстрировано, как с помощью VBA-кода можно сравнить оптимальное решение с правильным, но не оптимальным эвристическим решением. Для этого необходимо разработать приложение расчета бюджета, в котором необходимо принять решение о том, какие проекты компании будут приняты в производство. Каждый проект характеризуется начальной стоимостью и набором будущих денежных потоков, которые описываются чистой приведенной стоимостью (ЧПС) или net present value (NPV). Проект необходимо выбирать по принципу "все или ничего". Частичное выполнение проекта недопустимо. Кроме того, начальная стоимость проекта не должна превышать размер бюджета. Целью оптимизации является поиск подмножества проектов, которое позволит максимизировать чистую приведенную стоимость и не превысить выделенный бюджет.

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