Лабораторная работа №6
Бизнес-планирование средствами Microsoft Excel
Цель. Изучить финансовые функции Microsoft Excel, связанные с принятием инвестиционных решений, а также провести анализ чувствительности бизнес-плана с помощью сценариев.
Задание
Сформировать основные документы бизнес-плана – Отчет о прибылях и убытках и Отчет о движении денежных средств.
Рассчитать показатели эффективности бизнес-плана.
Выполнить анализ чувствительности бизнес-плана с помощью сценариев.
Основные сведения
Электронные таблицы MS Excel предоставляют экономисту обширный инструментарий, позволяющий выполнять достаточно сложные вычисления и анализировать финансово-экономическую информацию различного рода. В данной работе демонстрируются возможности MS Excel, используемые при разработке бизнес-планов.
Технология работы 1. Общие сведения и начало работы
Скопируйте файл Бизнес-план.xls (уточните у преподавателя местонахождение файла) в свою папку. Откройте этот файл.
На рабочих листах Общие данные, Инв. план, Осн. фонды, Сбыт и Издержки введены основные сведения об инвестиционном проекте Производство фильтроэлементов для автомобилей. Горизонт планирования составляет 5 лет. В сентябре 2006 заканчивается инвестиционный этап проекта и с октября начинается выпуск продукции. На листе Сбыт приведены прогноз цены и объемов продаж.
В ходе данной работы необходимо заполнить листы Отчет о приб., Отчет о движ. и Эффект, получив в результате документы Отчет о прибылях и убытках и Отчет о движении денежных средств, а также показатели эффективности данного инвестиционного проекта.
Для удобства работы с MS Excel рекомендуется присваивать имена ячейкам и диапазонам ячеек, так как это повышает наглядность и облегчает понимание используемых формул. На листе Имена приведены все используемые в данной рабочей книге имена.
2. Создание Отчета о прибылях и убытках
Откройте лист Отчет о приб. На рис. 6.1 приведен результирующий вид Отчета о прибылях и убытках.
Рис. 6.1. Результирующий вид Отчета о прибылях и убытках
В ячейку С2 введите формулу =ГОД(ДатаНачала). В ячейку D2 введите формулу =C2+1 и размножьте ее далее по строке.
Для ввода имени ДатаНачала можно щелкнуть по соответствующей ячейке ('Общие данные'!С2) или воспользоваться клавишей F3.
Рассмотрим 1-й способ расчета валового объема продаж – в ячейку С3 введите формулу =Сбыт!B6*Цена/(1+НДС) и размножьте формулу по строке.
2-й способ. Выделите диапазон C4:G4 и введите формулу =ОбъемСбыта*Цена/(1+НДС) Щелкните левой кнопкой мыши в строке формул и одновременно нажмите клавиши Ctrl, Shift и Enter, чтобы ввести формулу массива. Данный стиль ввода формул считается более предпочтительным.
В ячейку С5 введите формулу =C4*Потери и размножьте ее по строке.
В ячейку С6 введите формулу =C3-C5 и размножьте ее по строке.
В диапазон C7:G7 введите формулу массива
{=ОбъемСбыта*(Материалы+Электроэнергия)/(1+НДС)}
В диапазон C8:G8 самостоятельно введите формулу массива.
В диапазон C9:G9 введите формулу массива
{=ОбъемСбыта*СдельнаяЗарплата}
В ячейку С10 введите формулу =C9*СУММ('Общие данные'!$D$8:$D$10) и размножьте ее по строке.
Чтобы ввести в ссылке на ячейку знаки $ удобно пользоваться клавишей F4. Вспомните, для чего служат знаки $ в ссылке на ячейку.
В ячейку С11 введите формулу =СУММ(C7:C10), в ячейку С12 введите формулу =C6-C11, а в ячейку С13 введите формулу
=СУММПРОИЗВ(СтоимостьОПФ;НормыНаРемонт)
Размножьте эти формулы по соответствующим строкам.
В диапазон C14:G14 введите формулу массива {=Топливо/(1+НДС)}
В ячейку С15 введите формулу =C13+C14 и размножьте ее по строке.
В диапазон C16:G16 введите формулу массива
{=Менеджмент*(1+СУММ('Общие данные'!D8:D10))+ПрочиеИздержки}
В ячейку С17 введите формулу =C15+C16 и размножьте ее по строке.
В дальнейшем нам потребуется использовать некоторые финансовые функции Excel. Перед началом работы с финансовыми функциями рекомендуется установить надстройку Пакет анализа. Для этого выполните команду меню СервисНадстройки… В диалоговом окне Надстройки установите флажок напротив строки Пакет анализа и нажмите кнопку OK/ В результате вам будут доступны все финансовые функции Excel, которые ориентированы на решение задач, связанных с расчетами различных аннуитетов, амортизации, цены, доходности и других параметров ценных бумаг (облигаций, акций и т.п.), а также задач оценки эффективности инвестиционных проектов.
В диапазоне C18:G18 для расчета величины амортизационных отчислений за год воспользуемся финансовой функцией АПЛ().
Синтаксис функции АПЛ(Стоимость;Остаток;Период).
Стоимость – это начальная стоимость имущества.
Остаток – это стоимость имущества в конце периода амортизации.
Период – это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).
В нашем случае Остаток равен 0. Зная годовую норму амортизации, можно рассчитать аргумент функции Период=1/Годовая норма амортизации. Тогда в ячейку D18 нужно ввести формулу
=АПЛ(ЗданияСооружения;0;1/'Осн. фонды'!$D$3)+АПЛ(Оборудование;0;1/'Осн. фонды'!$D$4)
. Для ввода функции АПЛ() выделите ячейку D18, нажмите кнопку Вставка функции  , выберите категорию Финансовые, функция АПЛ.
Так как в 2006 году основные фонды будут эксплуатироваться только три месяца, то в ячейку С18 введите формулу =3/12*D18
В диапазон C19:G19 введите значение 0.
Чтобы ввести в один или несколько диапазонов ячеек одинаковое значение, можно выделить эти диапазоны ячеек, затем ввести нужное значение и нажать одновременно клавиши Ctrl и Enter.
В ячейку С20 введите формулу =СУММ(C18:C19), в ячейку С21 введите =C12-C17-C20, в С22 введите =ЕСЛИ(C21<0;0;C21*НалогНаПрибыль), в С23 введите =C21-C22. Размножьте эти формулы по соответствующим строкам.
3. Создание Отчета о движении денежных средств
Откройте лист Отчет о движ. На рис 6.2 представлен результирующий вид Отчета о движении денежных средств. В таблице 6.1 приведены формулы, которые необходимо ввести на данный рабочий лист.
Рис. 6.2. Результирующий вид Отчета о движении денежных средств
Таблица 6.1
№
|
Наименование статьи
|
=ГОД(ДатаНачала)
|
=C2+1
|
1
|
Чистая прибыль
|
{=ЧистПрибыль}
|
{=ЧистПрибыль}
|
2
|
Амортизация
|
{=Амортизация}
|
{=Амортизация}
|
3
|
КЭШ-ФЛО ОТ ОПЕРАЦИОННОЙ ДЕЯТЕЛЬНОСТИ
|
=C3+C4
|
=D3+D4
|
4
|
Затраты на приобретение активов
|
=СУММ('Инв. план'!F4:F6)
|
0
|
5
|
Другие издержки подготовительного периода
|
='Инв. план'!F3
|
0
|
6
|
КЭШ-ФЛО ОТ ИНВЕСТИЦИОННОЙ ДЕЯТЕЛЬНОСТИ
|
=–(C6+C7)
|
=–(D6+D7)
|
7
|
Кредиты
|
0
|
0
|
8
|
Выплаты в погашение кредитов
|
0
|
0
|
9
|
КЭШ-ФЛО ОТ ФИНАНСОВОЙ ДЕЯТЕЛЬНОСТИ
|
0
|
0
|
10
|
САЛЬДО НАЛИЧНОСТИ НА НАЧАЛО ПЕРИОДА
|
=СтартКапитал+C11
|
=C13+D11
|
11
|
САЛЬДО НАЛИЧНОСТИ НА КОНЕЦ ПЕРИОДА
|
=C12+C5+C8
|
=D12+D5+D8
| 4. Расчет показателей эффективности проекта
Откройте лист Эффект. На рис 6.3 представлен результирующий вид этого листа.
Рис. 6.3. Результирующий вид рабочего листа Эффект
В ячейку В4 введите формулу ='Отчет о движ.'!C5+'Отчет о движ.'!C8, в В5 =СУММ($B4:B4), а в В6 =ЕСЛИ(B5<0;1;0). «Растяните» формулы по строкам.
Чтобы рассчитать срок окупаемости (Payback Period, PP), нужно рассчитать момент времени, когда кумулятивный чистый поток денежных средств изменит знак с минуса на плюс. В нашем случае это произойдет между вторым и третьим годом. В ячейке А6 получена грубая оценка срока окупаемости с помощью формулы =СУММ(B6:F6). Для уточнения срока окупаемости в ячейку В7 введите
=A6-0,5-ИНДЕКС(КумЧистПотокДенСредств;;A6)/
(ИНДЕКС(КумЧистПотокДенСредств;;A6+1)-ИНДЕКС(КумЧистПотокДенСредств;;A6))
В данной работе для простоты предполагается, что все доходы и расходы распределены равномерно в течение года (на практике это зачастую не так). В этом случае рекомендуется считать, что все элементы потока денежных средств относятся к середине соответствующего года. Поэтому, когда кумулятивный поток изменил знак с «–» (–959921) на «+» (+719562), мы считаем, что срок окупаемости лежит между серединой второго года и серединой третьего года. Последняя формула выведена с учетом этого предположения.
Чтобы ввести формулу в диапазон B10:F10, выделите этот диапазон и введите формулу =ЧистПотокДенСредств/(1+СтавкаДисконта)^(Год-0,5), а затем одновременно нажмите клавиши Ctrl и Enter.
Нажав одновременно клавиши Ctrl и Enter, мы определяем, что каждое значение в диапазоне ЧистПотокДенСредств будет разделено на соответствующее ему значение массива (1+СтавкаДисконта)^(Год-0,5). Здесь используется показатель степени Год-0,5, так как мы условились относить все доходы и расходы к середине года.
В остальные ячейки диапазона А11:F13 необходимые формулы введите самостоятельно.
Чтобы ввести формулу в диапазон B15:F15, выделите этот диапазон и введите формулу
=ЧистПотокДенСредств/((1+Инфляция)*(1+СтавкаДисконта))^(Год-0,5)
Затем нажмите клавиши Ctrl и Enter. В диапазоне А16:F18 остальные формулы введите самостоятельно.
Чтобы убедиться, что все сроки окупаемости рассчитаны правильно, построим графики всех трех кумулятивных потоков денежных средств. Выделите диапазоны А5:F5, А11:F11 и А16:F16. Нажмите кнопку Мастер диаграмм  и выберите тип диаграммы График. Нажмите кнопку Готово.
Чтобы выделить несмежные диапазоны ячеек, нужно выделить первый диапазон ячеек обычным способом, а затем, нажав и удерживая клавишу Ctrl, выделить следующие диапазоны ячеек.
Для расчета чистого приведенного дохода NPV существует две финансовые функции – ЧПС() и ЧИСТНЗ() (см. Приложение). Функция ЧПС() используется, если платежи (поступления) происходят через равные промежутки времени. С учетом того, что все платежи (поступления) мы относили к середине года, в ячейку В20 введите формулу
=ЧПС(СтавкаДисконта;ЧистПотокДенСредств)*(1+СтавкаДисконта)^0,5
Обратите внимание, что полученное значение 2068348 совпадает со значением в ячейке F11.
Для расчета чистого приведенного дохода NPV с учетом инфляции нужной функции не существует, поэтому в ячейку В21 введите формулу =F16.
В ячейку В22 введите формулу
=–ЧПС(СтавкаДисконта;'Отчет о движ.'!C8:G8) *(1+СтавкаДисконта)^0,5
Эта формула предполагает, что все инвестиции осуществлялись в середине 2006 года. Однако данные инвестиционного плана (лист Инв. план) позволяют уточнить эту оценку. Перейдите на лист Инв. план и добавьте в таблицу этап №0 со стоимостью – р., а также добавьте столбец Момент инвестирования согласно рис. 6.4. Попытайтесь самостоятельно ввести формулы в диапазон Н3:Н7.
Теперь на рабочем листе Эффект в ячейку В23 введите формулу
=ЧИСТНЗ(СтавкаДисконта;'Инв. план'!F3:F7;'Инв. план'!H3:H7)
Функция ЧИСТНЗ() используется, если платежи (поступления) происходят через неравные промежутки времени (как в нашем случае). Очевидно, что уточненная оценка не существенно отличается от упрощенной оценки.
В ячейку В24 введите формулу =1+B20/B23, а в В25 формулу =1+B21/B23.
Рис. 6.4. Результирующий вид рабочего листа Инв. план
Для расчета внутренней нормы рентабельности (доходности) проекта IRR (Internal Rate of Return) служат финансовые функции ВСД() и ЧИСТВНДОХ(). Функция ВСД() используется, если платежи (поступления) происходят через равные промежутки времени. В ячейку В26 введите формулу
=ВСД(ЧистПотокДенСредств)
Функции для расчета IRR с учетом инфляции не существует. Поэтому в ячейку В27 необходимо ввести формулу
=(ВСД(ЧистПотокДенСредств)-Инфляция)/(1+Инфляция)
Рассчитанные показатели эффективности инвестиционного проекта свидетельствуют о его эффективности при заданной инвестором ставке дисконтирования e=15% , так как выполняются условия NPV>0, IRR>e и PI>1.
5. Анализ чувствительности инвестиционного проекта
Для исследования чувствительности проекта к изменениям различных условий можно использовать такое средство MS Excel как сценарии. Будем исследовать влияние изменения цены, объема сбыта и уровня инфляции на эффективность проекта (в частности, на NPV, IRR и срок окупаемости с учетом инфляции).
К сожалению, на листе Сбыт не представлены значения прогноза инфляции, NPV, IRR и срока окупаемости, а все изменяемые ячейки сценария должны находиться на активном листе (т.е. на листе Сбыт). Чтобы значение прогноза инфляции присутствовало на листе, перейдите на лист Общие данные, вырежьте диапазон А11:С11 (кнопка Вырезать  ) и вставьте в любом месте на листе Сбыт (теперь ячейка с именем Инфляция располагается на листе Сбыт). Чтобы не перемещать ячейки результатов NPV, IRR и срок окупаемости РР с листа Эффект на лист Сбыт, создадим копии этих ячеек на листе Сбыт. В ячейку В10 введите формулу =Эффект!B21, в В11 введите =Эффект!B27, в В12 введите =Эффект!B18. Ячейке В10 присвойте имя NPV, В11 – IRR, В12 – PP.
Перейдите на лист Сбыт и выполните команду СервисСценарии… В окне Диспетчер сценариев нажмите кнопку Добавить… В окне Добавление сценария задайте Название сценария Базовый сценарий, Изменяемые ячейки C3;B6:F6;Инфляция и нажмите кнопку ОК. В окне Значения ячеек сценария указаны текущие значения изменяемых ячеек. Нажмите кнопку ОК.
Добавьте сценарий Рост цен на 10% (Изменяемые ячейки С3, Цена =130*1,1 или 143). Самостоятельно создайте сценарий Падение цен на 10%.
Добавьте сценарий Рост продаж на 10% (Изменяемые ячейки B6:F6, $B$6 =50000*1,1 или 55000, $С$6 =300000*1,1 или 330000 и т.д.). Самостоятельно создайте сценарии Падение продаж на 10%, Инфляция 15% и Инфляция 20%.
Для создания отчета по сценариям нажмите кнопку Отчет… в окне Диспетчер сценариев. В окне Отчет по сценарию укажите Тип отчета структура, Ячейки результата =$B$10:$B$12. Нажмите кнопку ОК. Проанализируйте созданный рабочий лист Структура сценария. Убедитесь, что наибольшее влияние на эффективность проекта имеет цена.
Самостоятельно создайте отчет по сценариям с использованием сводной таблицы.
Чтобы получить удовлетворительный отчет с использованием сводной таблицы, необходимо предварительно удалить Базовый сценарий. После создания листа Сводная таблица по сценарию отредактируйте сводную таблицу. Для полей IRR и РР измените параметры поля – щелкните правой кнопкой мыши в нужном поле, выберите команду Параметры поля… и в окне Вычисление поля сводной таблицы задайте Операция Сумма, после чего нажмите кнопку ОК. Отформатируйте сводную таблицу так, чтобы она приняла следующий вид.
Рис. 6.5. Результирующий вид листа Сводная таблица по сценарию
Обратите внимание, что в сводной таблице результаты рассчитаны при совокупном действии сценариев в отличие от листа Структура сценария.
Чтобы определить минимальную цену, при которой проект будет оставаться рентабельным, перейдите на лист Сбыт и выполните команду СервисПодбор параметра… В окне Подбор параметра задайте значения согласно рис. 6.6. Нажмите кнопку ОК.
Убедитесь, что при цене 127,53 руб. NPV=0 руб., IRR=15% (т.е. IRR равен ставке дисконта), а РР больше 5 лет.
|
Рис. 6.6. Окно Подбор параметра
| |
