|
После разработки компьютерного варианта модели расчета схемы поставки товара на предприятии АО "Стеклодув" принято решение создания компьютерной модели прогнозирования движения денежных потоков, в которой:
- движение денежных средств можно было бы планировать в ежедневном режиме в иностранной и национальной валюте;
- проводить расчеты параметров привлекаемых кредитных ресурсов;
- производить расчет сумм налогов по данному проекту и планирование сроков их оплаты;
- модель должна строить различные графики, отражающие движение денежных потоков и привлекаемых заемных ресурсов;
- модель должна быть комбинирована для прогнозирования денежных потоков по различным проектам и пр.
С чего начать
Создание компьютерной модели поручили финансовому отделу и они приступили к реализации проекта интуитивно. С чего начать? Модель необходима для обоснования принимаемых решений. Если проанализировать человеческую логику, то можно прийти к выводу что принятие решений наиболее оправдано, если оно на чем-то базируется. В системе управления предприятием такой базой могут служить самые различные отчеты.
Отчет — это набор какой-либо информации, структурированной любым удобным для наиболее эффективного восприятия способом, в котором фиксируется состояние каких-либо параметров на определенный (или за определенный) момент времени.
Следовательно, выходом модели должен быть какой-то отчет (или комбинация отчетов), создающиеся моделью самостоятельно и по заложенному в нее алгоритму. Причем в данном отчете должна находиться только та информация, которая необходима для принятия решения. А решение в системе прогнозирования денежных потоков может быть только двоякое:
- при временном недостатке денежных средств — когда, сколько и на какой период, а также во что обойдется предприятию (или одной из ее структур) привлечение денежных средств;
- при временном избытке денежных средств — когда, сколько и на какой период времени, а также будет ли оправданно их временное размещение, например, на депозите.
Для создания любого отчета должны быть созданы какие-либо предпосылки. Если проанализировать бухгалтерскую компьютерную программу, то в ней формы отчета могут быть различны: баланс, отчет о финансовых результатах и пр. Эти отчеты базируются на данных промежуточных сводных форм, например, оборотно-сальдовых ведомостей. Следовательно, прежде чем подготовить отчет, необходимо сгруппировать исходные данные каким-то определенным образом. А исходными данными в бухгалтерской программе является журнал хозяйственных операций. Один из вариантов моделирования — применение бухгалтерской программы. Вводя операции в журнал хозяйственных операций, можно получить прогнозные отчеты. Но сама логика построения бухгалтерской программы не позволяет гибко изменять различные параметры влияющие на результаты. А таких параметров может быть довольно много: даты, курсы валют, процентные ставки и пр. Основой моделирования является получение самых различных сценариев развития событий при изменении различных базовых показателей. Следовательно, основой компьютерной модели может послужить принцип формирования бухгалтерской программой нужных для принятия решения отчетов, но с той степенью гибкости, которые свойственны электронным таблицам.
Специалистами финансового отдела принято решение, что исходные данные для последующих расчетов должны присутствовать в модели в виде таблицы журнала регистрации будущих (или прошлых) событий.
Таблица ввода исходных данных
Требования к таблице
Создайте таблицу, отражающую движение товарных и денежных потоков. Таблица должна отвечать всем требованиям Excel при работе со списками (табличными базами данных), или:
- должна быть наглядна и удобна в использовании;
- должна присутствовать возможность сортировки данных списка по любому критерию;
- должен быть встроена функция фильтрации списка по заданным критериям;
- должна присутствовать возможность использования диалогового окна Формы;
- в верхней части рабочего листа должны располагаться элементы управления проведения расчетов в таблице и т.д.
Принято решение: рабочему листу, с размещаемой на нем таблицей, присвоить имя ИсхДанные, а файлу компьютерной модели — ДенежныеПотоки. Шесть верхних строк рабочего листа отведите для расположения элементов управления, ввода сальдо денежных средств на начало периода и расчета итоговых сумм. На практике принято располагать ячейки расчета итоговых данных внизу таблиц, но в этом заключается довольно большой недостаток — если таблица большая, то для просмотра этих данных нужно перейти в конец таблицы, что не всегда удобно. При расположении же ячеек с итоговыми вычислениями в верхней части рабочего листа, будет возможность их постоянного отображения на экране монитора при применении команды закрепления областей.
Заголовок таблицы будет состоять из двух строк: основного и детализированного. Основная строка должна содержать:
- наименование операции и дату ее проведения;
- признак, по которому будет проводиться анализ операции: с позиции НДС, налога с оборота, налога на прибыль и движения денежных средств по счетам;
- эквивалент денежных средств, выраженный в национальной валюте по курсу: поступление и списание;
- наименование валюты и ее курс на дату проведения;
- денежные средства в той валюте, в которой будет проводиться операция: поступление и списание;
- область расчета налогов по проводимым операциям.
С позиции наглядности, таблица с двумя уровнями заголовка удобна. Недостаток заключается в том, что при сортировке списка, Excel автоматически принимает верхнюю строку за заголовок. Если не выделить область сортировки, то производится сортировка данных по заданному столбцу, при этом данные второй строки заголовка участвуют в процессе сортировки и могут быть перемещены внутрь таблицы.
Рис. 17.1. Первый этап создания таблицы с двухуровневым заголовком
Следующий недостаток двухуровневого заголовка. При размещении заголовка, как показано на рис. 17.1. Excel принимает всю таблицу с двумя строками заголовка за список и при попытке произвести сортировку списка, выделяет область сортировки (в том числе и строку 9, которая содержит объединенные ячейки), но произвольное перемещение содержимого объединенных ячеек при сортировке невозможно и поэтому появляется диалоговое окно Excel (рис.14.2.) с предупреждающим текстом.
Рис. 17.2. Диалоговое окно Excel, с предупреждением, что сортировка списка содержащего объединенные ячейки невозможна
Если же поменять местами строки, то результат проведения сортировки не изменится. В Excel не заложен принцип наличия двух строк заголовка. Выход довольно прост — выделите строку 8 и произведите вставку ячеек (рис. 17.3.), выполнив команду Вставка/Строки, а потом, выделив строку 8, скройте ее, выполнив команду Формат/Строка/Скрыть. Это простое решение позволит обмануть Excel, который будет воспринимать строку 9 за заголовок и список для Excel будет начинаться также со строки 9.
Рис. 17.3. Таблица с двухуровневым заголовком воспринимаемая Excel как список
Описание таблицы исходных данных
Создаваемая таблица расположена в столбцах С:Q. Заголовок находится в строках 7 и 9. Область с данными занимает диапазон ячеек С10:Q5000, то есть имеется возможность внести в таблицу 4991 записей. При необходимости размер таблицы можно увеличить или уменьшить, в зависимости от пожеланий разработчика.
В таблицу вводятся данные не только операций, проводимых непосредственно с денежными потоками, но и операций, влияющих на налогообложение, а, в частности, на налог на прибыль, НДС и налог с оборота.
Элементы таблицы
Столбец С предназначен для ввода наименования операции, а в столбец D вводится дата ее проведения.
В столбцы E, F, G и Н, в зависимости от проводимой операции, вводятся ее признаки или на какие расчеты эта операция влияет. Для обозначения признака используется значение 1, при вводе которого в соответствующие столбцы строки проводимой операции можно будет учитывать следующее:
- значение признака, введенное в столбец Н, будет обозначать, что введенная в этой строке операция связана с наличностью, и эта сумма будет отражена в движении денежных средств. На эту сумму будут уменьшены (или увеличены) денежные средства предприятия на дату ее проведения;
- столбец E — признак НДС в операции, при вводе которого производится расчет суммы НДС, участвующей при расчетах с бюджетом, как в сторону уменьшения, так и увеличения задолженности по этому налогу;
- при вводе значения признака в столбец G производится расчет налога с оборота по этой операции;
- если вводить значение признака в столбец Н, то сумма этой операции будет учитываться при расчете базы налогообложения по налогу на прибыль.
В столбец К вводится наименования валюты, с которой проводится операция, а в столбец L курс этой валюты по отношению к национальной валюте на дату проведения операции (этой области присвоено имя Курс). В столбцы M и N вводятся значения суммы операции в валюте ее проведения. Данные вводятся либо только в столбец M или в столбец N.
В столбцах I и J производится расчет денежных средств по курсу на день ее проведения. Формула, в ячейке I10 копируется на весь диапазон I10:J5000:
=ОКРУГЛ(M10*Курс;2)
В столбцах О:Q производится расчет сумм НДС, налога с оборота и дохода/затрат по проводимым операциям.
Автоматизация ввода курса валют
В создаваемой таблице в столбец L курс валют необходимо вводить с клавиатуры, что не совсем удобно. Желательно чтобы курс валюты, наименование которой указанно в столбце К, вводился самостоятельно и на указанную в столбце D дату. При изменении даты курс должен изменяться автоматически. Далее, предположим, что валютные операции предприятию не в диковинку, отслеживает изменение курсов валют и ведет таблицу, по которой зафиксировано их изменение по данным Центробанка, а также вводится будущая возможная их тенденция.
В рассматриваемом примере курсы валют введены в рабочий лист Курс, который находится в одноименном файле. Файл же находится на диске D в директории Самоучитель.
Рис. 17. 4. Рабочая книга Курс
При создании формулы расчета эквивалента валюты по курсу Центробанка в ячейке L10 файл Курс должен быть открыт. Формула определения курса по введенной в ячейку К10 наименованию валюты и дате, введенной в ячейку D10 следующая:
=ЕСЛИ(K10=0;0;ВПР(D10;[Курс.xls]Курс!$A:$D;ЕСЛИ(K10="USD";2;ЕСЛИ(K10="EUR";3;4));ЛОЖЬ))
Основной функцией поиска курса валюты, несомненно, является функция ВПР. Она ничем не отличается от рассмотренных ранее примеров, кроме того, что для определения номера столбца, по которому она производит поиск искомого значения, указанного в первом аргументе (дата проведения операции), функция использует переменное значение, в зависимости от наименования текста валюты, введенной в ячейку К10. В зависимости от текста наименования валюты, функцией ЕСЛИ задается номер столбца поиска данных. Кроме того, формула осуществляет поиск данных не в этой книге, а в другой под именем Курс. Поэтому в формуле присутствует ссылка не только на имя рабочего листа, но и на имя файла (Курс), которые формируются автоматически при выделении столбцов A:D в этом файле.
Если рабочую книгу закрыть, то формула примет вид, в которой указан путь нахождения файла:
=ЕСЛИ(K10=0;0;ВПР(D10;'D:\Самоучитель\[Курс.xls]Курс'!$A:$D;ЕСЛИ(K10="USD";2;ЕСЛИ(K10="EUR";3;4));ЛОЖЬ))
Если эта формула будет находиться в строке, в которой не указана дата, то для того чтобы функция ВПР не возвращала значение ошибки #Н/Д, она находится в третьем аргументе первой функции ЕСЛИ, вычисления по которой происходят только в том случае, если введена дата в ячейку К10. Если дата введена, а наименование валюты нет, то формула возвратит также значение равное нулю.
Расчеты, проводимые в таблице
Расчет суммы НДС
В ячейке О10 находится формула:
=ЕСЛИ($E10=1;ОКРУГЛ((I10-J10)*СтавкаНДС/(1+СтавкаНДС);2);0)
которая при введенном признаке НДС в столбце Е рассчитывает сумму НДС, которая входит в сумму операции. Результат вычислений может иметь положительное или отрицательное значение, в зависимости от того — в какой столбец (I или J) введена сумма проводимой операции. При отсутствии признака НДС, формула возвращает значение равное нулю.
Ставке налога на добавленную стоимость присвоено имя СтавкаНДС и она в нашем примере равна 20%.
Расчет суммы налога с оборота
Расчет суммы налога с оборота производится в столбце Р по формуле (ячейка Р10):
=ЕСЛИ(F10=1;ОКРУГЛ((I10-O10)*СтавкаНалогОборот;2);0)
которая при введении соответствующего признака вычитает из суммы операции сумму НДС (если введен признак его наличии в операции) и умножает полученный результат на ставку налога с оборота, которой присвоено имя СтавкаНалогОборот. В нашем примере эта ставка равна 1%.
Расчет получаемого дохода и затрат с целью налогообложения
С целью расчета базы налогообложения, вначале сравнивается сумма полученных доходов (I10) и понесенных издержек (J10). Формула определения дохода/издержек в ячейке Q10 при введенном признаке в столбце G вычитает из суммы операции (I10-J10) сумму НДС (О10) и сумму налога с оборота (Р10):
=ЕСЛИ(G10=1;I10-J10-O10-P10;0)
Если операция увеличивает статью затрат, то она будет отражена в столбце Q с отрицательным знаком (-J10-О10-Р10), если увеличивает доходную часть — то это величина положительная (I10-О10-Р10).
Суммирование данных
Формулы итогового суммирования введенных данных по столбцам введите в строках 5:6 (см. рис. 17.10.). Для суммирования данных по столбцам I, J, O, P и Q примените формулу обыкновенную суммирования. Например, в ячейке I6 формула, которая копируется в ячейки строки 6 для суммирования по указанным столбцам:
=СУММ(I10:I5000)
Для суммирования оборотов по каждой валюте примените функцию СУММЕСЛИ. Формула, вводимая в ячейку М5:
=СУММЕСЛИ($K$10:$K$5000;$L6;M$10:M$5000)
содержит абсолютные и смешанные ссылки, что позволяет скопировать содержимое ячейки М5 с содержащейся в ней формулой в диапазон ячеек М5:N6 без последующего редактирования. В качестве условия, по которому будет производиться суммирование, использовано наименование валют, введенные в ячейки L5 и L6.
Зеркальное отображение операций
Остановимся на двух деталях отображения вводимых операций.
Деталь первая. В строке 10 введена операция приобретения товара. Сумма операции проходит по статье Списано. Такое название операции дано с той позиции, что приобретение товара с одной стороны увеличивает активы баланса предприятия по статье Товары, а с другой стороны увеличивает пассив баланса по возникшей кредиторской задолженности. При погашении этой задолженности денежными средствами уменьшается актив баланса по статье денежные средства и уменьшения пассива баланса по задолженности. В связи с тем, что создаваемая модель связана с расчетом денежных средств, то при вводе операции приобретения товара, рассматривается будущая операция списания денежных средств. Кроме того, в нашем примере приобретаемый товар, при вводе соответствующих признаков, увеличивает сумму издержек (а значит должен быть отражен в столбце Q с отрицательным знаком) и уменьшает сумму обязательств перед бюджетом по НДС (должен быть отражен в столбце O с отрицательным знаком).
Аналогично рассматривается и введенная в строку 15операция по реализации товара.
Деталь вторая. В строках 18 и 19 введена операция Конвертация. Данная операция обозначает, что денежные средства были списаны с валютного счета и отправлены на продажу, а вместо них получены те же денежные средства, но уже в национальной валюте, за минусом оплаты услуг по конвертации (строка 17).
С позиции движения денежных потоков общая сумма денежных средств не изменилась, если не считать издержек по конвертации. А время, которое эти деньги отсутствовали, можно считать их временным отсутствием (деньги в пути) с целью изменения ими наименования валюты. Исходя из этого, можно рассуждать, что поступившие денежные средства в национальной валюте не как поступившие средства, а как возвращенные. Поэтому эта операция введена в зеркальном отображении.
Усовершенствование таблицы
Созданная таблица обладает недостатком, а именно тем, что является преимуществом электронных таблиц — наличием формул. Почему же недостаток? Формулы содержатся в диапазоне ячеек I10:J5000 и О10:Q5000, то есть их около 25 тысяч. Это не только "утяжеляет" файл, но и может доставить неприятности. Например, случайное и незамеченное удаление формулы может внести ошибки в расчеты.
В качестве примера опишем возможную ситуацию, которая неоднократно встречалась на практике. Например, при расчете суммы комиссионных при конвертации валюты, в ячейку N17 введено не числовое значение, а формула:
=M16*0,5%
которая производит умножение суммы валюты, отправляемой на продажу, на процент комиссионных за эту услугу. Суммы оборотов по поступлению и списанию денежных средств в ячейках I6 и J6 равны соответственно 2400000 и 2412000 рублей. Произведите сортировку списка, например, по убыванию, по наименованию операции. Полученный результат суммирования поменяет значение на 2400000 и 2406000 рублей соответственно. Это произошло потому что ячейка N17 в результате сортировки находится по адресу N16, и формула в ней изменилась на:
=M15*0,5%
а в ячейке М15 нет никакого значения.
Этого бы не произошло, если бы в формуле была задана абсолютная ссылка, но в процессе работы пользователь может этого не сделать. И если таблица заполнена многими сотнями или тысячами строк, содержащими данные, то поиск ошибки может не принести никаких результатов. А время потраченное на повторное выполнение работы по вводу данных может исчисляться часами и днями. Для избежания таких ошибок существует один способ — замена формул вычисленными значениями. Помогут в этом создаваемые макросы.
Макрос определения курса валют на указанную дату и расчета эквивалента денежных средств по курсу
Макросы ввода формул записываются уже после ввода вышеописанных формул в ячейки. Задача макроса Курс заключается в следующем:
- открыть файл Курс;
- вернуться в файл ДенежныеПотоки;
- ввести в столбце L формулу определения курса указанной валюты на указанную даты и заменить формулы на определенные с помощью формул значения;
- закрыть файл Курс;
- ввести в диапазон ячеек I10:J5000 формулу расчета эквивалента денежных средств по определенному курсу.
Для записи макроса Курс (рис. 17.6.) выполните следующее:
с помощью кнопки Открыть или выполнив команду Файл/Открыть откройте диалоговое окно Открытие документа;
перейдите на диск D и в директории Самоучитель откройте файл Курс дважды щелкнув по его имени правой кнопкой мыши;
выполнив команду Окно/ДенежныеПотоки вернитесь в книгу ДенежныеПотоки и выделите область ячеек L10:L5000;
выделив формулу определения курса валют в строке формул, нажмите комбинацию клавиш Ctrl+Enter для одновременного ввода формул во все ячейки выделенного диапазона;
не перемещая табличного курсора, скопируйте содержимого выделенного диапазона в буфер обмена и вызовите диалоговое окно Специальная вставка, в котором в области Вставить выделите переключатель Значения;
выполните команду Окно/Курс и закройте файл Курс, выполнив команду Файл/Закрыть или нажав кнопку закрытия файла;
введите формулы в диапазон ячеек I10:J5000. Заменять формулы на вычисленные ими значения временно не следует. В предыдущем случае это было необходимо с позиции повышения быстродействия выполнения подпрограммы;
перейдите в Редактор Visual Basic и отредактируйте текст кода VBA.
Рис. 17.5. Подпрограмма Курс
Макрос расчета сумм налогов
Запись макроса РасчетНалогов (рис. 17.6.) заключается в последовательном вводе формул в ячейки столбцов O, P и Q.
Рис. 17.6. Подпрограмма РасчетНалогов
Макрос расчета итоговых сумм
Макрос РасчетСумм производит ввод формул:
- суммирования оборотов по поступлению рассчитанных денежных средств по курсу в ячейку I6 и вставке ее в ячейки J6 и O6:Q6. Для одновременной вставки формул в диапазон ячеек J6,O6:Q6 после ввода формулы в ячейку I6 и копирования ее содержимого выделите ячейку I6, и нажав и удерживая клавишу Ctrl выделите мышью диапазон ячеек O6:Q6, произведите вставку скопированного;
- суммирования по принципу "если" по наименованию валюты в ячейке М5 и последующего копирования и вставки содержимого этой ячейки в диапазон M5:N6.
Далее для замены всех формул на вычисленные ими значения на рабочем листе ИсхДанные нажатием на кнопку Выделить весь лист произведите выделение всего рабочего листа (Cells) и через диалоговое окно Специальная вставка замените формулы на значения. Отредактируйте текст подпрограммы.
Немаловажная деталь при редактировании, на которую хотелось бы обратить внимание читателя. Обратите внимание на строки ввода формул, например, фрагмент: Range("I6,J6,O6:Q6").Formula. Во-первых, при вводе формул макрос не выделяет ячейки, в которые вводятся формулы, потому что при редактировании удален этот оператор (Select). Dо-вторых формулы одновременно вводятся в различные ячейки. Этим значительно увеличивается быстродействие выполнения макроса.
Рис. 17.7. Подпрограмма РасчетСумм
Соберем все макросы вместе
Выполнение всех записанных макросов можно производить отдельно, но будет очень удобно, если они будут выполняться в записанной последовательности. Для этого запишите подпрограмму ПолныйРасчетИсхДанные, которая последовательно перечисляет имена выполняемых процедур.
Рис. 17.8. Подпрограмма ПолныйРасчетИсхДанные
Макрос ввода текста наименования валют
Неудобство ввода наименования валют заключается во вводе текста наименования с клавиатуры. Это неудобно само по себе и возможен ввод ошибок. При расчете же эквивалентного денежного потока, формула расчета воспринимает введенное наименование валюты дословно и при вводе ошибочного текста он не будет понят. Макрос ввода наименования валюты должен вводить текст как в отдельно выделенную ячейку, так и в диапазон ячеек. Для записи макроса прежде чем начать запись, введите текст валюты в любую ячейку и выделите диапазон ячеек, начиная с этой ячейки. После начала записи макроса нажмите клавиши F2 и Enter. Записанная строка кода VBA:
Selection.FormulaR1C1 = "RUR"
показывает, что в выделенный диапазон записана формула ввода текста. Замените оператор формулы на оператор присвоения значения Value.
Рис. 17.9. Подпрограммы ввода текста наименования валют
Опасность макросов, производящих вставку значений в выделенный диапазон, заключается в том, что вставка может случайно произвестись не в ту область. Если подобная операция осуществляется непосредственно при работе с электронной таблицей в окне Excel, то всегда присутствует возможность отмены операции, используя команду Правка/Отменить. При выполнении же макроса возможность отмены операции отсутствует. Поэтому совет только один — почаще производите сохранение. А если все-таки подобное произошло, то желательно иметь в запасе резервную копию файла, чтобы заменить "испорченный" диапазон ячеек.
Назначение кнопкам макросов
После записи макросов, создайте элементы управления Кнопка и назначьте им макросы. Пример расположения кнопок на рабочем листе ИсхДанные приведен на рис. 17.10.
Рис. 17.10. Рабочий лист ИсхДанные с созданными элементами управления Кнопка
Дополнительно в левом верхнем углу выделите ячейки для ввода значений остатка денежных средств по каждой валюте на начало рассматриваемого периода. Для контроля правильности работы модели при ее создании, введите в них единички, что облегчит проверку правильности вычислений. Курс валют на рассматриваемый период на листе Курс файла Курс установлен с этой же целью равным 10.
Итоги
В этой главе приступили к созданию модели прогнозирования денежных потоков, основой которого служат правильно собранные исходные данные. С этой целью принято решение, что эти данные будут собираться в журнале предстоящих операций на отдельно выделенном для этого рабочем листе. Минимально необходимые расчеты, выполняющиеся на этом листе, производятся автоматически при помощи записанных подпрограмм (макросов), после выполнения которых на этом листе присутствуют только значения вычисленные формулами. То есть, на этом рабочем листе нет ни одной формулы.
|
