Финансовое моделирование, ч. 4

Часть 1

Часть 2

Часть 3


Итак, продолжаем и переходим к заполнению оставшихся листов модели формулами. Сегодня посчитаем доходную и расходную части.


Начнем с доходной.

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

В первом разделе, ссылаясь на соответствующие ячейки, переносим для удобства значения из листа «Горный календарь».

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Далее считаем вес золота и серебра, которое получаем на собственной обогатительной фабрике:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост
Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Также для удобства указываем цену золота и серебра в рублях, ссылаясь на лист «Исходные данные».

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Перемножив вес полученного драгметалла и его цену, получаем выручку:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Собственно, с доходной частью всё. Переходим к расходам.

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Сначала снова ссылаемся не некоторые данные из уже заполненных листов — для удобства. Если это менее удобно — можно этот раздел пропускать:)

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Надеюсь, что это все знают, но тем не менее, обращу внимание на специфику написания формул в excel. Ссылаясь на ячейку (будь то ячейка на текущем листе или на другом) и «протягивая» затем формулу, например, вбок, то ссылки также смещаются. Скажем если мы сослались на ячейку А1, потом протянули формулу вправо на одну ячейку, то эта соседняя ячейка уже будет ссылаться на ячейку В1 (сдвинутую на одну ячейку вправо от А1). Обычно это и нужно, но иногда такого эффекта надо избежать. Для фиксации ячейки, на которую мы ссылаемся, используется знак доллара «$». Т.к. «протягивать» формулу можно как влево-вправо, так и вниз-вверх, то и фиксировать, соответственно, мы можем либо всю ячейку целиком ($A$1), либо столбец, в котором она расположена ($A1) — тогда при протягивании вверх-вниз ссылка на ячейку будет сдвигаться, а влево-вправо — нет, либо строку (A$1).


Это можно использовать, например, в формуле суммы — зафиксировав начальную ячейку из суммируемого диапазона и не фиксируя конечную — при протягивании формулы начальная ячейка будет одной и той же, таким образом расширяя диапазон суммирования:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Таким образом, получим в каждой ячейке за каждый период первоначальную стоимость основных средств, введенных в эксплуатацию с начала деятельности организации до настоящего момента:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Переходим к расчету прямых затрат на производство. Перемножаем объем перерабатываемой в периоде руды с ценой материала и расходом материала на тонну переработанной руды. Не забываем контролировать единицы измерения — тонны, тысячи тонн и т.д.

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Затраты на конвейерную ленту считаются аналогично.


Общепроизводственные расходы не зависят от объема перерабатываемой на фабрике руды, поэтому просто перемножаем расход и цену ресурса:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Ремонт и обслуживание основных средств зависит от введенных в эксплуатацию ОС. Перемножаем норму расхода и первоначальную стоимость введенных в эксплуатацию основных средств.

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Административно-хозяйственные расходы даны в исходных данных уже в абсолютном значении за каждый период:Расходы на доставку продукции до аффинажного завода в исходных данных даны исходя из полной мощности переработки. Поэтому для расчета умножаем эту цифру на переработку в текущем периоде и делим на максимальный объем переработки за все периоды:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Расходы на доставку продукции до аффинажного завода в исходных данных даны исходя из полной мощности переработки. Поэтому для расчета умножаем эту цифру на переработку в текущем периоде и делим на максимальный объем переработки за все периоды:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Аналогично считаются расходы на доставку вахты.


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

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Расходы на оплату труда указаны в исходных данных аналогично общепроизводственным: расходы на АУП даны в абсолютном размере, и они не зависят от от объемов производства. Расходы на производственный и вспомогательный персонал даны из расчета полной мощности предприятия.

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост
Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Далее считаем сумму затрат и налоги, зависящие от них (в исходных данных зарплата дана без НДФЛ и социальных отчислений, цены на ресурсы и материалы — без НДС, поэтому все эти налоги и отчисления лягут на предприятие сверху обозначенных цифр):

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост
Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост
Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

Далее считаем норму оборотных средств. Поясню на всякий случай: не секрет, что предприятию после осуществления капзатрат сначала нужно вложить дополнительно некоторую сумму денег в т.н. «оборотку» — в ресурсы и материалы, нужно заплатить работникам и т.д., так как всегда сначала нужно все это закупить и проплатить, а только потом пойдет выручка. Таким образом, получается, что часть денег у нас всегда «заморожена» в оборотке — чтобы продолжать деятельность, предприятию нужно постоянно иметь какой-то запас топлива, материалов и прочего для работы. Соответственно, чем больше объем деятельности у организации, тем больший объем средств будет заморожен в обороте. Нет деятельности — нет оборотных средств. В то же время, частично предприятие может управлять этим объемом средств — сокращать, где возможно, сроки оплаты у своих клиентов и, наоборот, подольше не платить поставщикам, сокращать сроки поставки продукции, платить работникам позже и т.д.


В нашем случае норма оборотных средств задана на листе исходных данных в процентах от общего объема затрат в периоде. Необходимо посчитать оборотку в абсолютных цифрах:

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

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

Финансовое моделирование, ч. 4 Финансовое моделирование, Microsoft Excel, Финансы, Длиннопост

На сегодня все. В следующий раз посчитаем налоги, финансирование проекта и сведем все рассчитанные потоки в листе Cash Flow.