W7 (Финансовый контроль и планирование с помощью Excel), страница 2

До сих пор прогнозирование происходило вполне удовлетворительно, однако, изучив отчеты о доходах и расходах компании за 1995 – 1997 годы, мы видим тревожную тенденцию. Основную часть дохода "Светлана" получает от тиражирования, а это зачастую требует наличия специального запаса бумаги. За три года "Светлана" закупила бумаги в объемах, намного превосходящих потребности конкретного заказа. Это было сделано с целью снижения переменных издержек, поскольку более крупные закупки означают меньшие затраты на единицу продукции. Кроме того, компания рассчитывала, что клиент сделает еще заказ, на выполнение которого и будет использован остаток данного товарного запаса.

К сожалению, этого не произошло. Товарно-материальные запасы бумажной продукции "Светланы" на протяжении всех трех лет неуклонно возрастали и уже превысили рост объема продаж. На рис.3 показано, что товарно-материальные запасы к концу года возросли с 92 903тыс.руб. до 129 190 тыс.руб. (другими словами, почти на 40%), тогда как объем нетго-продаж увеличился с 349 595 тыс.руб. до 389 864 тыс.руб.(т.е. всего на 11%). На рис.4 показано, что, если не будут приняты меры, товарно-материальные запасы на конец 1998 года составят 136 425 тыс.руб.

Какое же влияние на предварительный баланс на 1998 год и планирование доходов и расходов "Светланы" будет иметь изменение процедуры закупок?

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

Компания не может точно знать, когда ее клиенты намерены сделать следующие заказы (позволяющие "Светлане" использовать уже приобретены товарно-материальные запасы), но предполагает, что в определенный момент это случится. А тем временем остается лишь один выход: сократить объемы новых закупок товарно-материальных запасов до тех пор, пока заказы постоянных клиентов не позволят использовать уже имеющиеся запасы материалов. Такой шаг может привести к замещению роста уровня товарно-материальных запасов.

Компании "Светлана" следует предпринять меры по расширению клиентуры. Однако, если сократятся товарно-материальных запасы, это ограничит способность компании работать по специальным заказам новых клиентов. Таким образом, процесс сокращения имеющихся запасов компании до более управляемого уровня непременно приведет к понижению уровня объема продаж. Разумно ли такое решение?

Проведя исследования, компания "Светлана" узнает, к какому результату приведет сокращение в 1998 году закупок запасов на 50%, по предварительным данным – с 97951 тыс.руб. до 98 976 тыс.руб.. Специалисты прогнозируют, что объем нетго-продаж снизится с 411 696 тыс.руб. до 247 018 тыс.руб.. Большинство издержек, за исключением расходов на арендную плату помещения и амортизацию оборудования (они обычно не изменяются в зависимости от показателя нетто-продаж), сократятся пропорционально этому показателю. Планируемый результат показан на рис. 5 (предварительный отчет о доходах и расходах) и рис.6 (предварительный баланс компании).

При сокращении закупок запасов вдвое объем нетто-продаж снизится на 40%, а чистый доход уменьшится на 92%, т.е. на 69 658 тыс.руб. Это весьма значительные изменения финансового состояния предприятия. Для того чтобы снизить расходы на заработную плату в соответствии с сокращением нетто-продаж, придется уволить одного служащего – очень серьезный шаг в области кадровой политики для такой небольшой компании, как "Светлана".

В общем и целом издержки будут превышать доходы, получаемые от исправления положения дел в товарно-материальных запасах компании. Именно такого итога можно ожидать, если текущее решение оказывает такое сильное влияние на методы, применяемые компанией в работе с клиентами.

Делать анализ с помощью Ехсеl достаточно просто, намного проще, чем принимать отдельное бизнес-решение. Здесь задача заключается в создании рабочего листа, содержащего традиционные данные для отчета о доходах и расходах компании и баланса. После этого получить процентные показатели не составит большого труда. Например, чтобы получить значение – 100%, указанное в ячейке G4 (см. рис. 3), надо ввести следующую формулу:

= D4/$D$4

Затем следует скопировать эту формулу и вставить ее в ячейки G6:G11 и G13:G23. Абсолютная ссылка ($D$4) указывает, что знаменателем данной формулы всегда будет значение ячейки В4, а числитель будет изменяться в зависимости от того, в какую ячейку вставляется эта формула. Так, например, вставив ее в ячейку G6, вы получите следующую формулу: = D4/$D$4.

Чтобы получить значение, указанное в столбце F(см. рис. 3), введите в ячейку F4 следующую формулу:

=(В4+С4+D4)/($В$4+$С$4+$D$4)

' После чего скопируйте и вставьте эту формулу в ячейки F13:F23. Существует еще одна версия данной формулы:

= СРЗНАЧ(В6/$В$4;С6/$С$4;D6/$D$4)

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

После ввода этой формулы для проверки влияния изменений объемов нетто-продаж на издержки, чистый доход компании и сумму капитала, потребуется лишь изменить значения объема нетто-продаж. Поскольку все остальные категории отчета о доходах и расходах компании и ее баланса напрямую зависят от этого показателя, Ехсеl будет изменять их соответственно.

Проведение анализа чувствительности

После создания в Ехсеl предварительного отчета о доходах и расходах компании на следующий год, вы можете воспользоваться им для проведения анализа различных финансовых сценариев. Оценка возможных изменений в предварительных финансовых отчетах может помочь определить возможность и степень будущего риска. Рассматриваемый нами пример с компанией "Светлана" продемонстрировал, что может случиться, если объем продаж снизится на 40%. Выводы в данном случае строятся на основе предположения о том, что большинство сумм расходов непременно изменятся в результате изменений в объемах нетто-продаж.

Однако представляется целесообразным внимательно рассмотреть также и само это предположение. Возможно, вам захочется спрогнозировать, какое влияние может оказать снижение в будущем году суммы выручки на 10% на чистый доход компании. Или вы, например, захотите узнать, сможете ли удержаться в своей рыночной нише, если один из ваших поставщиков повысит цены на 20%.

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

Например, можно поменять значение ячейки С6 (см. рис.4):

= ОКРУГЛ(В6*$В$1;0)

которое равняется 197 951 тыс.руб., на:

= ОКРУГЛ(В6*$В*1,2;0)

которое указывает, что при повышении поставщиком цены на 20% запланированная сумма расходов на закупку товарно-материальных запасов составит 237 541 тыс.руб. Результатом этого будет снижение объема чистого дохода с 75 861 тыс.руб до 36 271. тыс.руб

Это действие может дать вам намного больше информации, чем данные о том, что ваш чистый доход может сократиться почти на 40 000 тыс.руб. Существует и другой способ прогнозирования изменений в сумме чистого дохода: принять во внимание, что повышение затрат на закупки товарно-материальных запасов приведет к снижению этого показателя на 52% (1х36 271 тыс.руб /75 861 тыс.руб). Все это доказывает, что чистая прибыль компании очень сильно зависит от цены поставок, – ваш доход практически полностью во власти цен ваших поставщиков.

Переходим от планов финансовой деятельности к составлению бюджета компании

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

Предположим, что компания "Светлана" хочет составить поквартальный текущий бюджет на 1998 год, основанный на предварительном отчете о доходах и расходах на этот год. Такой поквартальный бюджет показан на рис.7.

Квартальный бюджет, подобно плану финансовой деятельности, основывается на предположении о том, что основным фактором, влияющим на расходы предприятия, является объем продаж. Так получилось, что доходы обсуждаемой нами фирмы имеют несколько сезонный характер: традиционно 20% продаж приходится на первый квартал, 40% – на второй, 25% – на третий и 15% – на четвертый. Чтобы соответствующим образом распределить объем плановых годовых продаж по четырем кварталам, необходимо ввести следующие данные:

в ячейку С3:

= 0,2*В3

в ячейку D3:

= 0,4*В3

в ячейку Е3:

= 0,25*В3

в ячейку F3:

= 0,15*В3

Эти формулы распределяют общую сумму запланированного дохода по кварталам с учетом процентных показателей по каждому кварталу за предшествующие годы.

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

Закупки материалов в течение каждого квартала представляют собой функцию общей суммы плановых закупок на год и объема нетто-продаж по кварталам. Формула в ячейке С6 (закупки материалов в первом квартале) такова:

= $В6*С$3/$В$3

Данная формула представляет объем прогнозируемых закупок материалов на год (ячейка В6), распределенный пропорционально ежеквартальному процентному показателю нетто-продаж (ячейка С3) в объеме годовых нетто-продаж (ячейка В3). Используя в формуле смешанные ссылки, копирование и вставку из ячейки С6 в ячейки D6:F6, можно преобразовать соответствующим образом ссылку на квартальные нетто-продажи ячейки С3 к ячейкам D3:F3.

Товары, готовые к продаже, – это товарно-материальные запасы на начало квартала плюс закупки, осуществленные в течение данного квартала.

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

= $В9+С$3/$В$3

Следующим планируется объем товарно-материальных запасов на конец квартала, который вычисляется по следующей формуле:

= С7-С9

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

= СЗ-С9

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

= $В12*С$3/$В$3

И опять-таки, при использовании смешанных ссылок эта формула может быть скопирована и вставлена в остаток диапазона С15:С19, после чего значения, содержащиеся в ячейках, будут откорректированы. Предполагается, что строки 13 и 14, содержащие показатели расходов на аренду помещения и износ оборудования, при изменении объема нетто-продаж остаются неизменными; они предварительно рассчитываются на основе самых последних данных (доступных на конец предшествующего периода).

Общие издержки получаются путем суммирования всех расходных статей каждого квартала, а сумма налогов предварительно оценивается как 25% от текущего дохода (валовая прибыль минус расходы). Чистый доход рассчитывается путем вычитания из суммы валовой прибыли суммы общих издержек и суммы налогов.

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