Материал: Автоматизация финансовых расчетов

 УЗ = Пmax´Дmax,

 УЗmin =УЗ – П_ср´Д_ср,

 УЗmax = УЗ+ОПЗ– Пmin´Дmin,

где          УЗ – уровень запаса, при котором делается заказ, ед.;

                Пmax – максимальная дневная потребность в сырье, ед.;

                Дmax – максимальное число дней выполнения заказа;

                УЗmin – минимальный уровень запаса, ед.;

                П_ср –  средняя дневная потребность в сырье, ед.;

                Д_ср – средняя продолжительность выполнения заказа (от момента размещения до момента получения сырья), дн.;

                УЗmax – максимальный уровень запаса, ед.;

                Пmin – минимальная дневная потребность в сырье, ед.;

                Дmin – минимальное число дней выполнения заказа.

1. Определяем уровень запаса (УЗ), при котором делается заказ:

 УЗ = 120´20 / 5 = 480 ед.

2. Определяем минимальный уровень запаса (УЗmin):

 УЗmin = 480 – 60´(14+20) / 2 / 5 = 276 ед.

3. Определяем максимальный уровень запаса (УЗmax):

 Узmax = 480+3000–35´14 / 5=3382 ед.

Домашнее задание №2 Разработка бюджета движения денежных средств с использованием имитационного моделирования Монте-Карло и Microsoft Exel

Датой рождения метода Монте-Карло принято считать 1949 г., когда появилась статья под названием «The Monte Carlo method». Создателями этого метода считают американских математиков Дж. Неймана и С. Улама. В СССР первые статьи о методе Монте-Карло были опубликованы в 1955-1956гг.

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

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

1. Установить взаимосвязи между исходными и выходными показателями в виде математического уравнения или неравенства.

2. Задать законы распределения вероятностей для ключевых параметров модели.

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

4. Рассчитать основные характеристики распределений исходных и выходных показателей.

5. Провести анализ полученных результатов.»

Алгоритм выполнения работы

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

2 этап. Диапазоны возможных изменений варьируемых показателей оформите в виде таблицы 1 (на основе экспертных оценок).

Уточните показатели таблицы в зависимости от сроков поступления денежных средств (в примере табл.1 срок погашения задолженности составляет два месяца).

3 этап. Для имитации значений требуемой переменной воспользуйтесь математической функцией СЛУЧМЕЖДУ() или СЛЧИС(). Функция СЛЧИС() возвращает равномерно распределенное случайное число Е, большее либо равное 0 и меньшее 1, т.е.: О < E< 1. С ее помощью можно получить любое случайное вещественное число. Например, чтобы получить случайное число между а и b, достаточно задать в любой ячейке электронной таблицы следующую формулу:

Задав любую из этих формул для переменных - выручки и переменных затрат, а также формулу для вычисления чистого денежного потока и скопировав их требуемое число раз (сделайте 100 раз), получаем генеральную совокупность, содержащую различные значения исходных показателей и полученных результатов за один месяц. Заполняем таблицу 2 для каждого месяца бюджета ДДС. Следует отметить, что применение встроенных функций MS Excel целесообразно лишь в том случае, когда вероятности реализации всех значений случайной величины считаются одинаковыми.

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

Настройка режима управления вычислениями производится установкой соответствующего флажка в подпункте «Вычисления» пункта «Параметры» темы «Сервис» главного меню.

Таблица 1

Исходные данные для имитационного моделирования бюджета денежных средств

Таблица 2

Генеральная совокупность значений исходных показателей и полученных результатов за один месяц

Постоянные расходы из месяца в месяц одни и те же.

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

Таблица 3

Статистический анализ результатов имитационного эксперимента

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

Среднее значение рассчитывается на основе данных столбцов таблицы 2 и функции MS Excel =СРЗНАЧ(Диапазон).

Стандартное отклонение значение рассчитывается на основе данных столбцов таблицы 2 и функции MS Excel =СТАНДОТКЛОНП(Диапазон).

Коэффициент вариации определяется путем деления стандартного отклонения на среднее значение показателя.

Минимум и максимум определяется с помощью функций МИН() и МАКС() вычислили минимальное и максимальное значение для массива данных из блока ячеек(таблица 2), указанного в качестве их аргумента

Функция СЧЕТЕСЛИ() осуществляет подсчет количества ячеек в указанном блоке(графа «Чистый денежный поток» таблица 2), значения которых удовлетворяют заданному условию (<0). Функция имеет следующий формат: СЧЕТЕСЛИ(Диапазон; "условие").

В рассматриваемом примере мы исходим из предположения о равномерном распределении ключевых переменных. Однако какое распределение при этом будет иметь результатная величина – показатель ЧДП, заранее определить нельзя.

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

В ППП EXCEL подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().

Функция НОРМАЛИЗАЦИЯ(x; среднее; станд_откл). Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность - p. Функция требует задания трех аргументов:

х – нормализуемое значение;

среднее – математическое ожидание случайной величины;

станд_откл – стандартное отклонение.

Полученное значение Z является аргументом для следующей функции – НОРМСТРАСП(). Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент – Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Справочно: Для проверки гипотезы о нормальном распределении случайной величины применяются специальные статистические критерии: Колмогорова-Смирнова и др. В целом ППП EXCEL позволяет быстро и эффективно осуществить расчет требуемого критерия и провести статистическую оценку гипотез.

Однако в простейшем случае для этих целей можно использовать такие характеристики распределения, как асимметрия (скос) и эксцесс. Для нормального распределения эти характеристики должны быть равны 0. На практике близкими к нулевым значениями можно пренебречь. Для вычисления коэффициента асимметрии и эксцесса в ППП EXCEL реализованы специальные статистические функции - СКОС() и ЭКСЦЕСС().

5 этап. Постройте графики и сделайте вывод о значениях ЧДП по месяцам и вероятностях их получения.

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

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

Результаты анализа

Величина ожидаемой NPV составила 3361,96 руб.. Величина стандартного отклонения равна 2271,31 и не превышает значения NPV. Коэффициент вариации (0,68) меньше 1, таким образом, риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 7%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 42% от среднего значения. Таким образом, с вероятностью более 90% можно утверждать, что поступления от проекта будут положительными величинами.

Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

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