Эксперименты с моделью

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

· Мария слишком поздно получает деньги за свою продукцию, а материалы закупает заранее с предоплатой;

· первые три месяца бизнес убыточен из-за недостаточных объемов выпуска;

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

Увидев в результате расчетов, что денег будет не хватать, Мария должна изменить что-то в организации своего бизнеса. Она может, например:

a) вложить в дело не 40, а 180 тыс. руб.

b) взять побольше кредит;

c) найти более дешевый материал;

d) работать только с салонами новобрачных, пусть и в других городах, чтобы продавать им свои изделия не по 250, а по 300 руб.

e) повысить долю закупок салонами новобрачных среди своих клиентов, так чтобы средняя цена реализации возросла до 270 руб.

f) продавать свои изделия самостоятельно, без посредников.

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

Задание 42. Проверьте помесячно остаток денежных средств в случае «а». Можно ли обойтись меньшей суммой?

Задание 43. Попробуем закрыть дыру в бюджете кредитом. Посмотрите, что будет, если взять кредит не 30 тыс., а на 140 тыс. больше, т.е. 170 тыс. руб.

Задание 44 Посмотрите, как изменятся прибыль и остатки денежных средств, если ткань покупать не по 20, а по 15 руб. за метр (измените значение в ячейке В5).

Задание 45 Что нужно сделать, чтобы проверить финансовые последствия варианта «d»? Как Вы оцениваете эти последствия?

Вариант «е» более реален с точки зрения маркетинга, чем вариант «d», однако с точки зрения финансов, если уж цена 300 руб. нас не устраивает, то 270 руб. тем более. Вариант «f» практически нереален для начинающих с организационной точки зрения. Тем не менее, если бы мы захотели просчитать возможность организации собственной торговли, нам нужно было бы вставить в модель строки, соответствующие зарплате продавцов и затратам на содержание магазина.

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

Задание 46 Какие варианты лучше всего комбинировать?

Задание 47 Что получится, если использовать одновременно варианты «с» и «е»?

Продолжим эксперименты. Результаты приведены в табл. 26

Таблица 26. Результаты различного комбинирования цен

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

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

Из табл. 26 видно также, что сумма кредита, о которой предстоит договариваться с банком, и даже его срок (5 месяцев или 7) зависят от того, по какой цене удастся купить ткань у поставщиков. Это уже достаточно сложная зависимость, проследить которую «в уме», без использования финансовой модели, мы бы не сумели.

Запомним также некоторые правила, которые нужно соблюдать, проводя вычислительные эксперименты на модели:

· скопируйте полностью ту модель, с которой собираетесь экспериментировать, на чистый лист или в новый файл. Не трогайте исходную модель, она Вам может еще пригодиться;

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

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

· чтобы удобнее было следить за результатами изменений, расположите сразу после исходных данных строку с интересующими Вас результатами, например, прибыль и остаток денежных средств. Для этого сделайте ссылки на соответствующие места в расчетной части модели, расположенной ниже;

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

· результаты расчетов записывайте (вручную или на компьютере, используя меню/Правка/Специальная вставка/Вставить значения) в отдельную таблицу, в которой будут содержаться значения изменяемых параметров и полученные при этом результирующие показатели (пример – табл. 26);

· закончив эксперимент, верните исходные данные в первоначальное состояние.

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

А теперь займемся расчетами по использованию кредита. В табл.26 самый лучший вариант – последний. Но планировать рекомендуется с расчетом на худшее, по правилу «плохой погоды». Остановимся на втором варианте, выделенном в табл.26 серым цветом. Он не самый худший, но из числа пессимистических. Выбрать именно его, а не первый вариант, меня побуждает то соображение, что кредит на 7 месяцев может оказаться дороже, чем на 6. Впрочем, пока в модели ставка процентов по кредиту не различается по срокам. Этот случай, хотя и больше соответствует действительности, несколько сложнее для моделирования, поэтому мы рассмотрим его позже.

Сейчас мы будем менять цифры в строке 23 «получение кредита». Когда увидим, что остаток денежных средств достаточно большой, начнем заполнять следующую строку 24 «возврат кредита». Начнем с того, что кредит в январе лучше взять не 30000 «на покупку оборудования» (как мы отчитаемся по нему, если оборудование стоит всего 20000?), а 33000 «на покупку оборудования и мебели». В феврале, марте и апреле берм соответственно 34, 27 и 7 тысяч рублей – суммы, достаточные, чтобы закрыть временную нехватку денег из-за запаздывания поступлений выручки. Эти кредиты имеют другое назначение – на пополнение оборотных средств. Гарантией по этим кредитам для банка отчасти могут служить запасы материалов и будущие поступления выручки. Начиная с июня можно приступить к погашению кредитов (см.табл.27), которое займет 5 месяцев: 15, 26, 26, 27 и 7 тысяч рублей. График погашения подбираем так, чтобы остаток денежных средств в строке 30 все время оставался положительным. Последнее погашение (в октябре) делаем, ориентируясь на непогашенную сумму займа в строке 25.

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

Задание 48. У Вас должна была сохраниться основная модель, изображающая формирование прибыли (табл.18) и движения денежных средств (табл. 24) для швейной фирмы Марии. Используя эту модель, повторите вышеизложенные выкладки с подбором цен и величины кредитов.

Задание 49 Можно ли обойтись меньшей суммой кредита?

Таблица 27. План финансирования бизнеса при цене материалов 15 руб\м и средней цене продукции 280 руб\шт.

Продолжение табл. 27

Баланс

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

Чаще всего баланс сравнивают с моментальной фотографией, на которой состояние Вашей фирмы запечатлено в неподвижном, зафиксированном виде. Баланс показывает, что у Вашей фирмы есть на данный момент. Он относится к определенной дате. В этом его отличие от предыдущих форм, которые относятся не к дате, а к периоду между началом и отчетной датой.

Сам термин «баланс» возник из представления о равновесии, уравновешенности частей. В России принято использовать горизонтальную форму баланса в виде двусторонней таблицы, где слева перечислено имущество, сгруппированное по признаку его физической формы и длительности обращения (АКТИВ), а справа – то же самое имущество, но сгруппированное по признаку происхождения, источника формирования, собственности (ПАССИВ). Поскольку обе классификации, и справа, и слева, относятся к одному и тому же имуществу, то итоговые суммы обязательно должны совпадать, т.е., если все записано и подсчитано правильно, то АКТИВЫ = ПАССИВАМ.

Таблица 28. Общий вид баланса.

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

Оборотные активы включают запасы сырья и материалов, незавершенное производство, запасы готовой продукции, дебиторскую задолженность и денежные средства на расчетном счете и в кассе. Эта часть имущества подвержена быстрым изменениям, постоянно пополняется и расходуется, по ходу производственного процесса перетекает друг в друга, откуда их название «текущие активы». Материалы превращаются в готовую продукцию, отдавая ее магазину, мы получаем взамен его обязательство отдать деньги (дебиторская задолженность, слово «дебит» означает «он должен»). Когда это обязательство будет исполнено, дебиторская задолженность исчезнет, но появятся деньги на ту же сумму. А затем круг замыкается, потому что на эти деньги мы снова покупаем материалы, производим продукцию и т.д. Можно сказать, что эти средства совершают хозяйственный кругооборот, подобно круговороту воды в природе, отсюда их второе название – оборотные активы.

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

Заемные средства состоят из кредитов банка и кредиторской задолженности. Кредиторская задолженность – это те суммы, которые мы должны в ближайшее время заплатить своим кредиторам, поэтому их называют также «текущие пассивы». К ним относятся выставленные к оплате, но пока не оплаченные счета поставщиков за товары и услуги, начисленная, но не выплаченная зарплата, задолженность перед бюджетом по налогам и т.д.

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

Общий вид баланса в горизонтальной форме показан в табл. 29

.

Таблица 29. Структура горизонтальной формы баланса.

Сейчас мы приступим к построению модели баланса. Пробные прогоны модели будем совершать при исходных данных, показанных в табл. А2 раздела «Ответы». Модель баланса будет учитывать результаты, полученные при расчете предыдущих форм - отчета о прибыли (формулы в табл. 18) и отчета (на самом деле – прогноза) о движении денежных средств (формулы в табл. 24).

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

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

Задание 50. Отступите от последней строки отчета о движении денежных средств вниз несколько строк (резерв на дополнение модели) и начните, например, со строки 80, заполнение столбца А названиями строк баланса. Используйте только те, которые есть чем заполнить по фирме Марии. Нулевые строки, например, здания, можно пока пропустить.Долгосрочные и краткосрочные кредиты банка можно показать одной строкой, а вот кредиторскую задолженность, наоборот, лучше расписать подробнее – по зарплате, в бюджет по налогу на прибыль, в пенсионный и другие фонды по единому социальному налогу. Вернитесь к описанию ситуации в разделе 2.1, чтобы понять, по каким платежам у Марии платежи идут с опозданием и образуют кредиторскую задолженность. Дебиторскую задолженность тоже лучше сделать в две строчки – отдельно долги магазинов за поставленную им продукцию, отдельно наши авансовые платежи по аренде. Последние тоже относятся к дебиторской задолженности, потому что представляют собой оплаченное нами обязательство со стороны арендодателей предоставить нам помещение на 3 месяца вперед.

Заполните вместе со мной формулами столбец В для января. Здесь у нас будет показано состояние имущества на начало года. К первому января у Марии был только начальный (уставной) капитал, существующий в виде денежных средств. Таким образом, и в пассиве, в разделе «собственные средства», в строке «уставной капитал» и в активе, в разделе «текущие активы», в строке «денежные средства» должна стоять на начало года одна и та же сумма, равная указанной в исходных данных в ячейке В21 величине начального капитала. И все. Больше пока ничего нет. Весь баланс из двух строк.

Тем не менее уже в столбце В пропишем формулы суммирования и проверки балансового равенства:

Задание 51. Сделайте это самостоятельно и сравните результаты. Чтобы посмотреть сразу все формулы, на верхней панели EXCEL щелкните левой клавишей мышки «сервис», затем в выпавшем меню предпоследний пункт «параметры», затем в разделе «вид» и «параметры окна» поставьте галочку (просто щелкнув мышкой) в окошко «формулы». Чтобы снова увидеть не формулы, а числа, подсчитанные по этим формулам, повторите все сначала: «сервис - параметры» и уберите галочку из окошка «формулы», щелкнув по нему еще раз.

Таблица 29. Формулы модели баланса на начало года

Будем составлять баланс поквартально. Итоги на конец первого квартала разместим в столбце Е. Будьте внимательны. Разбираем порядок построения каждой строчки, каждой ячейки. В ячейке Е80 наберите дату, к которой относятся цифры – 01.04. В ней появится надпись «1 апреля». Ниже в столбце Е напишем сначала формулы для расшифровывающих строк, а затем итоговые, суммирующие строки. По ходу этой работы я постараюсь пояснить, почему нужно делать именно так, а не иначе.

Машины и оборудование (ячейка Е83). Здесь должна содержаться стоимость оборудования за вычетом износа. К началу апреля оно отработает уже 3 месяца, и должно стать дешевле первоначальной стоимости на сумму амортизации за 3 месяца (B46+C46+D46=E46 из отчета о прибыли). Первоначальная стоимость оборудования равна затратам на его приобретение, которые показаны в отчете о движении денежных средств (Е71).

Внимание! Возникла необходимость в модификации модели.

В ячейке Е71 отчета о движении денежных средств показаны затраты на приобретение мебели и оборудования вместе, в отчете о прибыли амортизация оборудования и мебели идет по разным нормам и показана в разных строках - 45 и 46. Лучше записывать все, что можно, раздельно, чтобы видеть все подробности. Может ведь случиться так, что мы будем покупать оборудование и мебель в разные месяцы и даже кварталы. Поэтому сейчас мы немного изменим нашу модель. Чтобы ничего случайно не испортить, скопируем весь лист в новый файл.

Задание 52. Потренируйтесь в копировании моделей.

· Выделите мышкой всю область исходных данных и расчетную зону, захватив все три составленные формы, от А1 до Q 101.

· Щелкните на верхней панели по значку «копировать» (можно пользоваться таким же значком, который есть в маленьком меню, появляющемся при нажатии правой клавиши мыши).

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

· Раздвиньте в ней пошире столбец А, чтобы поместились наши заголовки строк.

· Станьте в ячейку А1 и нажмите (в верхнем меню или по правой клавише мыши) кнопку «вставить».

· Проверьте, не появились ли в каких-либо ячейках знаки «****». Это может быть, если число в ячейке слишком большое для такого узенького столбца. Раздвиньте его, звездочки исчезнут.

· Теперь нужно проверить, является ли модель в новом файле полностью автономной, не содержат ли ее формулы ссылок на предыдущий файл. Включите режим просмотра формул, как в задании 51, и посмотрите, нет ли где-нибудь надписей, гораздо более длинных, чем обычные формулы, примерно таких: ='[Моногр фин мод 2 Мария.xls]Лист1'! $D$12. Это и есть ссылка на другой файл. Надо или сейчас поставить в эти ячейки нужные числа, или потом всюду носить за собой оба файла. Обычно достаточно заменить ссылки на числа только в первом столбце В, дальше все будет сделано автоматически.

· Перейдите от просмотра формул к просмотру чисел (снимите флажок в окошке «формулы», меню «сервис - параметры»), проверьте, стоят ли там именно такие значения, как Вам надо.Если нет, сделайте переадресацию на предыдущие столбцы. Мне пришлось, например, заменить формулы на числа в ячейках с В7 по В10.

· Сверьте результаты расчетов, полученные в новом и в прежнем файлах. Они могут немного отличаться. Возможно, это связано с ошибками округления. Проверьте те ячейки, в которых были произведены замены. Для этого в исходной модели с помощью верхнего меню «формат – ячейки – числовой» увеличьте число знаков после запятой. Так и есть! В старой модели в ячейке В10 стоит почасовая оплата труда 11, 3636 руб,, а в новой я поставила 11, 3600. Исправив это различие, можно вернуть формат ячейки к прежнему – 2 знака после запятой. Теперь результаты в исходной модели и в копии полностью совпадают.

· Сохраните файл под новым именем.

.

В модель, размещенную в новом файле, будем вносить изменения и усовершенствования. Они состоят в том, что в баланс и в отчет о движении денежных средств мы добавим новые строки, чтобы показать отдельно стоимость швейных машинок и отдельно – стоимость мебели.

Задание 53. Доработка модели. движения денежных средств.

· В отчете о движении денежных средств вставьте чистую строку после строки 71 «покупка оборудования и мебели». В ячейке А71 оставьте только слова «покупка оборудования», а в ячейке А72 напишите «покупка мебели».

· В столбце В измените формулы: В71=В15, В72=В18. Скопируйте эти формулы на следующие месяцы.

· В ячейке Е72 поместите формулу суммирования расходов, скопировав ее с ячейки Е71.Повторите эту операцию для других кварталов, в столбцах I, M, Q.

· В ячейке В65 исправьте формулу суммирования по строке «итого расходы». Она должна содержать суммы расположенных ниже строк не до 71-й, а по 72-ю включительно: В65=СУММ(B66: B72).

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

· Проверьте правильность работы модели. Например, пусть в марте будет куплена еще одна швейная машинка, а в ноябре – мебель на сумму 5000 руб.

В модели формирования прибыли мебель и оборудование уже написаны раздельно, так что на первый взгляд ничего в ней менять не надо. Однако при выполнении последнего пункта задания 53 Вы могли заметить, что при увеличении стоимости оборудования и мебели прибыль не изменилась. Этого не может быть, потому что затраты должны были увеличиться за счет роста амортизационных отчислений. Посмотрите на суммы амортизации, показанные в строках 46 и 47. Там из месяца в месяц идет 250 и 271 руб., изменений, соответствующих дополнительным покупкам марта и ноября, нет. Значит, что-то не в порядке с расчетными формулами. Загляните в модель или в табл. 18. В столбцах C, D и т.д. в С46 стоит «=В46», D46=С46, т.е. сумма амортизации не подсчитывается, а переносится неизменной из предыдущего месяца.

Задание 54. Подумайте, как нужно изменить формулы, чтобы модель работала правильно.

Если Вы не сделали это упражнение самостоятельно, обязательно загляните в ответы и внесите нужные изменения в свою модель. Это задание пропускать нельзя.

Продолжаем составление баланса. Остаточная стоимость оборудования на конец первого квартала равна его стоимости на начало года плюс покупки за квартал минус амортизация за квартал:

Е84=B84+E71-E46.

То же самое для мебели:

Е85=B85+E72-E47.

Аналогично для конца 2 квартала, по прошествии 6 месяцев:

I84=B84+I71-I46,

I85=B85+I72-I47.

Формулы для подсчета остаточной стоимости оборудования и мебели на конец 3 квартала и на конец года выглядят аналогично. Обратите внимание, что все они начинаются со ссылки на их стоимость на начало года, поэтому их нельзя просто копировать, а нужно набрать заново. Можно было, правда, в формуле для 2-го квартала обозначить В84 и В85 как фиксированные ячейки, т.е. $В$84 и $В$85, тогда эти формулы можно было бы копировать обычным способом в 3 и 4 квартал.

Строка 83 «Постоянные активы» должна содержать суммы строк 84 (оборудование) и 85 (мебель). В этой формуле нет боковых ссылок на другие столбцы, поэтому, набрав для первого квартала Е83=E84+E85, эту формулу можно смело копировать до конца, получив в результате формулы:

I83=I84+I85,

M83=M84+M85,

Q83=Q84+Q85.

Строка 87 «Запасы и затраты» будет содержать сведения о запасах материалов на соответствующую дату. Мы уже подсчитывали эту величину в строках 31-32, и теперь воспользуемся этими результатами. На начало года строка 87 в нашем балансе должна быть равна наличию материалов на начало января (В87=В31), на конец первого квартала – остатку на конец марта или на начало апреля (это одно и то же, но первое записано в D32, а второе в F31):

Е87=D31.

Эту формулу можно без опасений копировать для других кварталов:

I87=H32,

M87=L32,

Q8743320.

Если бы мы написали Е87=F87, то при копировании в формуле для ячейки Q87 (декабрь) получили бы ссылку на столбец R (январь следующего года), который пока не заполнен.

Дебиторская задолженность – это долги магазинов за поставленную им продукцию. По смыслу это разница между стоимостью проданной им продукции (строка «выручка» в отчете о прибыли) и той суммой денег, которые мы от них уже получили (строка «выручка» в разделе «приход» прогноза движения денежных средств). Для каждого следующего квартала берется остаток задолженности на конец предыдущего квартала, к ней прибавляется возникновение новой задолженности, равное продажам текущего квартала, и вычитается погашение задолженности, равное поступлению денег за текущий квартал. Поскольку у нас в квартальных столбцах стоят суммы нарастающим итогом за 3, 6, 9 и 12 месяцев, можно поступить и проще: задолженность рвана объему продаж (строка 39) минус поступление денег за проданную продукцию (Строка 63). Отсюда формулы:

Е89 =E39-E63,

I89 =I39-I63,

M89 =M39-M63,

Q89=Q39-Q63.

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

Дебиторская задолженность клиентов на момент начала деятельности предприятия, естественно, отсутствует (на начало года ставим 0).Однако на начало следующего года она равна неоплаченному остатку на конец предыдущего. Это нужно будет учесть при копировании формул модели за год вперед еще на два-три года.

Арендная плата, вносимая вперед, тоже образует дебиторскую задолженность, а если платить ее вослед прошедшим месяцам, то кредиторскую. У нас по сценарию до начала каждого квартала Мария должна платить вперед за три месяца. Посмотрим в прогноз движения денежных строк. Оказывается, там вообще нет строки «аренда». Я просто забыла ее включить. С Вами при разработке реальной модели это тоже вполне может случиться. К счастью, средства ECXEL позволяют безболезненно выходить из таких затруднений. Достаточно вставить в нужное место чистую строку, например, строку 69. Все формулы при сами сдвинутся и перепишутся, как надо. Даже суммарную строку «итого расход» не придется изменять, потому что там была написана формула В65=СУММ(B66: B72), а теперь появилась В65=СУММ(B66: B73).

Итак, в январе платим за январь, февраль и март, в марте – за апрель, май и июнь, в июне – за июль, август, сентябрь и т.д. Отобразим эти условия платежа в формулах:

В69=B48+C48+D48 (январь),

С69=0 (февраль),

D69=F48+G48+H48 (март)

Е69=СУММ(B69: D69) (итого 1-й квартал),

F69=0 (апрель),

G69=0 (май),

H69 =J48+K48+L48 (июнь).

Последние 3 строки копируем для остальных кварталов.

Задание 55. Почему в столбце Q для декабря получилось, что арендная плата равна нулю? Правильно ли это? Что можно сделать?

Квартальные суммы скопируем из верхних или нижних строк того же столбца:

Е69 =СУММ(B69: D69),

I69 =СУММ(F69: H69)+E69 и т.д.

Задание 56. Чему равен остаток денежных средств на конец января и на конец года?

Дебиторская задолженность арендодателя в строке 91 равна разнице между проплаченной суммой и той арендной платой, которая прошла по отчету о прибыли за соответствующие месяцы. Если проплачено больше, то мы имеем право и дальше занимать это помещение (или получить обратно деньги), поэтому эта разница отражается в активе баланса:

Е91 =E69-E48.

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

Для других кварталов копируем формулу из ячейки Е91. Затем в строке 89 просуммируем дебиторскую задолженность клиентов и арендодателя (строки 90 и 91). Задолженность клиентов раньше значилась в строке 89, но после того, как была вставлена дополнительная строка в отчет о движении денежных средств, все нижележащие строки сместились на одну строку вниз.

На начало года денежные средства берем из ячейки В61. Остаток денежных средств на конец каждого периода можно скопировать из соответствующей ячейки отчета о движении денежных средств. Но тут есть одна тонкость. Если остаток денежных средств положителен, то он должен размещаться в активе. Отрицательный остаток означает задолженность банку (овердрафт, превышение счета), и его нужно показывать в пассиве, в составе кредиторской задолженности. Значит, нам необходимо снова использовать уже знакомую Вам функцию «ЕСЛИ». Но сначала вставим в пассив баланса строку 100 «задолженность поставщикам» и строку 101 «задолженность банку».

Е72 =ЕСЛИ(E75> =0; E75; 0),

Е101 =ЕСЛИ(E75< 0; -E75; 0).

Скопируйте эти ячейки для других кварталов. Если все сделано правильно, то в 72-й строке должны появиться цифры 0, 0, 0, 33130, а в строке 101 17333, 25608, 34270 и 0., т.е. первые три квартала остаток денежных средств пока отрицательный, и он появляется в пассиве, а на коней года – положительный, и его место в активе.

Теперь просуммируем все строки, относящиеся к текущим активам – запасы, дебиторскую задолженность и денежные средства:

Е87 =E89+E90+E93.

Итого актив равен сумме постоянных и текущих активов:

Е83=E84+E87.

Эти формулы тоже копируем на другие кварталы. Актив баланса готов.

Переходим к составлению формул для автоматического подсчета статей пассива баланса.

Начальный капитал не будет изменяться. Делаем ссылку на исходные данные и затем на каждый предыдущий квартал. Нераспределенную прибыль (непокрытый убыток) мы можем найти в отчете о прибылях и убытках (сейчас это строка 55). Вносим в ячейку Е96 формулу «=Е55». Затем посчитаем, сколько у нас всего собственных средств:

Е94 =СУММ(E95: E96).

Заемные средства равны сумме кредитов и кредиторской задолженности:

Е97 =E98+E99

Кредиты берем из предыдущего столбца строки 25 «непогашенная сумма кредита» в зоне исходных данных, где мы уже выполнили вспомогательный расчет:

Е98=D25.

Кредиторская задолженность – это сумма соответствующих расшифровывающих строк внизу:

Е99 =СУММ(E100: E104)

Эти 4 ячейки (с 94 по 99) можно скопировать на другие кварталы все вместе. Следующая строка – задолженность перед поставщиками за закупаемые материалы. Пока Мария расплачивается в момент покупки, задолженность не возникает. Но ведь она может получить отсрочку платежа или задержать его. Поэтому для универсальности модели сделаем и тут формулы. Задолженность равна стоимости израсходованных материалов (см. отчет о прибыли) плюс прирост стоимости запасов (см. вспомогательный расчет в строках 31-32) минус оплата закупок (см. движение денежных средств):

Е100 =E43+(D32-B31)-E66.

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

Е100 =E43+(D32-$B$31)-E66.

Тогда в других кварталах формулы будут иметь вид:

I 100 =I43+(H32-$B$31)-I66

M100 =M43+(L32-$B$31)-M66

Q100 =Q43+(P32-$B$31)-Q66

Задолженность банку (Е101) мы уже записали раньше, задолженность по зарплате равна разности между зарплатой начисленной (в отчете о прибыли и убытках) и выплаченной (в отчете о движении денежных средств). Здесь просуммирована зарплата основных рабочих и административного персонала:

Е102 =E41+E49-E67.

Так же хотелось бы поступить и с единым социальным налогом, и с налогом на прибыль. Да вот беда! При составлении формы «Движение денежных средств» мы показали уплату обоих этих налогов одной строкой. Придется снова менять эту форму, разделять платежи, иначе неудобно составлять баланс. Вывод:

Все, что можно, пишите раздельно,

особенно при разных сроках платежей.

НЕ ЭКОНОМЬТЕ СТРОКИ!

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

Опустим утомительные подробности. Окончательно формулы модели движения денежных средств после всех дополнений Вы можете посмотреть в разделе «Ответы» в табл. А4. После разделения платежей формулы для задолженности по единому социальному налогу в балансе выглядит аналогично предыдущей формуле по зарплате, а для налога на прибыль составляется по общему правилу «НАЧИСЛЕННОЕ МИНУС ВЫПЛАЧЕННОЕ»:

Е104 =E42+E50-E68

Е195 =E54-E69.

Затем нужно заполнить строку «итого кредиторская задолженность», найти сумму заемных средств и общую сумму пассива как сумму собственных и заемных средств.

Для контроля правильности составления баланса советую использовать специальную строку «невязка», равную разнице между активом и пассивом. Если невязка нулевая, значит, равновесие баланса соблюдено и все правильно.

Формулы баланса на конец первого квартала приведены в табл.30. Для других кварталов эти формулы аналогичны. По ходу составления модели баланса выяснились некоторые недостатки предыдущих моделей – прибыли и денежных средств, было вставлено несколько дополнительных строк, в связи с чем формулы немного сдвинулись относительно представленных в табл.18 и 24. Поэтому теперь, разбирая формулы табл.30, лучше всего смотреть строки, на которые ссылаются эти формулы, прямо с дисплея или в табл.А4.

Таблица 30. Модель баланса.