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

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

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

Снимок

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

Снимок1

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

Снимок2

Снимок3

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

Снимок4

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

Снимок5

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

Снимок6

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

Снимок7

 

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

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

 

Снимок8

 

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

Снимок9

 

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

Снимок10

 

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

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

Снимок11

 

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

Снимок12

 

Административно-хозяйственные расходы даны в исходных данных уже в абсолютном значении за каждый период:

Снимок13

 

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

Снимок14

 

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

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

Снимок15

 

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

Снимок16

Снимок17

 

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

Снимок18

Снимок19

Снимок20

 

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

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

 

Снимок21

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

 

Снимок22

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>