Создаваемая таблица расположена в столбцах С: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).
С позиции движения денежных потоков общая сумма денежных средств не изменилась, если не считать издержек по конвертации. А время, которое эти деньги отсутствовали, можно считать их временным отсутствием (деньги в пути) с целью изменения ими наименования валюты. Исходя из этого, можно рассуждать, что поступившие денежные средства в национальной валюте не как поступившие средства, а как возвращенные. Поэтому эта операция введена в зеркальном отображении.