Автоматизация учета дебиторской задолженности

- № участника;

- Наименование  предприятия;

- Юридический  адрес;

- Отдел;

- Ответственное  лицо;

- Дата  заключения договора и номер  договора;

- Дата  и номер счета;

- Дата  отгрузки.

Заполняем данные столбцы, соответствующими данными. Полученные результаты продемонстрированы на рис. 4,5,6.

                          Рисунок 4 – Информация о клиентах

                 Рисунок 5 – Информация об участниках

                Рисунок 6 – Информация об участниках

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

Далее проведем фильтрацию. Выделим столбцы: №, Наименование предприятия, Юридический адрес, Отдел. Ответственное лицо, Дата заключения договора и номер договора, Дата и номер счета. Дата отгрузки; к ним будет применяться фильтрация. Затем осуществляется команда Данные – Фильтр – Автофильтр.

          Заполняем Лист 2 – Размер и срок ДЗ. Осуществим ввод наименования столбцов:

         - №;

-Наименование;

-Номер  счета;

-Размер  ДЗ;

-Срок  ДЗ;

-Дата  оплаты.

Заполняем столбцы соответствующими данными. В столбце - Дата оплаты, производим расчет даты, путем сложения данных столбца « Срок ДЗ в днях» (Е2;16) и данных столбца «Дата отгрузки» (H14:18) из листа «Информация о клиентах» Меняем диапазон методом протягивания указателя (F2:F16). Полученные данные продемонстрированы на рис. 6.

                       Рисунок 6 – Размер и срок ДЗ

Также закрепляем области. Для закрепления помещаем табличный курсор в ячейку С2 и выполняем команду Окно – Закрепить области.

Аналогично проведем фильтрацию. Выделим столбцы: №, Наименование предприятия, Размер ДЗ, Срок ДЗ в днях и Дата оплаты; к ним будет применяться фильтрация. Затем осуществляется команда Данные – Фильтр – Автофильтр.

Заполняем лист 3 – Критический срок оплаты. Осуществим ввод наименования столбцов:

-№;

-Наименование  предприятия;

-Размер  ДЗ;

-Количество  дней критического срока оплаты;

-Дата  критического срока оплаты.

          В столбце – «Количество дней критического срока оплаты» введем логическую функцию «Если». Если 5>Размер ДЗ>0, следовательно, количество дней составит 10; Если 10>Размер ДЗ>5, следовательно, количество дней составит 14;Если 40>Размер ДЗ>10, следовательно, количество дней составит 21 день. Такая функция целесообразна, прежде всего, экономической обоснованностью, так как при возрастающей дебиторской задолженности, количество дней соответственно тоже должно увеличиваться. Полученные данные представлены на рис.7.

                Рисунок 7 – Количество дней критического  срока оплаты        

        В столбце - Дата критического срока оплаты, определим расчет даты, путем сложения данных из столбца «Дата оплаты»(F2:F16) из листа «Размер и срок ДЗ» и данных из столбца D «Количество дней критического срока оплаты» Полученные данные представлены на рис.8.

                     Рисунок 8 – Расчет даты критического срока оплаты.

 Как и в предыдущих примерах закрепим области. Для этого помещаем табличный курсор в ячейку С2 и выполняем команду Окно – Закрепить области.

 Проведем  фильтрацию. Выделим столбцы: №, Наименование  предприятия, Размер ДЗ, Количество дней критического срока оплаты и Дату критического срока оплаты. Затем осуществляется команда Данные – Фильтр – Автофильтр.

 Далее заполняем лист 4 – Сведения об оплаченной ДЗ. Здесь отражены данные о предприятиях оплативших дебиторскую задолженность, что включает в себя Наименование, Дату оплаты, Юридический адрес, Отдел, Ответственное лицо, Дата заключения договора и номер договора, Дата и номер счета, Дата отгрузки товара, Размер ДЗ, Срок ДЗ в днях, Дата оплаты, Дата критического срока оплаты. Все данные столбцов перенесем из листа «Информация о клиентах».

                            Рисунок 9 – Сведения об оплаченной ДЗ

                                    Рисунок 10 – Сведения об оплаченной ДЗ

 

Как и в предыдущих примерах закрепим области. Для этого помещаем табличный курсор в ячейку С3 и выполняем команду Окно – Закрепить области.

 Проведем  фильтрацию. Выделим столбцы: №, Наименование  предприятия, Юридический адрес, Дата  оплаты, Отдел, Ответственное лицо, Дата заключения договора и  номер заключения, Дата и номер  счета, Дата отгрузки товара, Размер  ДЗ, срок ДЗ в днях, Дата оплаты, Дата критического срока оплаты. Затем осуществляется команда  Данные – Фильтр – Автофильтр.

         Заполняем лист 6 -  Сведения о ДЗ <12 мес. Наименования столбцов:

 - №;

 -Наименования столбцов;

 -Юридический адрес;

 -Отдел;

 -Ответственное лицо;

 -Дата заключения договора и номер договора;

 -Дата и номер счета;

 -Размер ДЗ;

 -Срок ДЗ;

   -Дата оплаты;

   -Количество дней просрочки;

   -Размер неустойки (руб.);

   -Количество дней критического срока оплаты;

   -Дата критического срока оплаты.

          Данные по столбцам перенесены из листа «Информация о клиентах» и показаны на рисунках 11,12,13.

                                Рисунок 11-  Сведения о ДЗ<12 мес.

                                Рисунок 12 – Сведения о ДЗ < 12мес.   

         Данные из столбца L «Размер неустойки» вычислим умножением данных из столбца «Размера ДЗ» на 0,1%. Данные представлены на рисунке 13.

              Рисунок 13- Вычисление размера неустойки

 Как и в предыдущих примерах закрепим области. Для этого помещаем табличный курсор в ячейку С3 и выполняем команду Окно – Закрепить области.

 Проведем  фильтрацию. Выделим столбцы: №, Наименование  предприятия, Юридический адрес, Дата  оплаты, Отдел, Ответственное лицо, Дата заключения договора и  номер заключения, Дата и номер  счета, Дата отгрузки товара, Размер  ДЗ, Срок ДЗ в днях, Дата оплаты, Количество дней просрочки, Размер  неустойки, Количество дней критического  срока оплаты, Дата критического  срока оплаты. Затем осуществляется  команда Данные – Фильтр –  Автофильтр.

            Заполняем лист 8 – Сведения о просроченной ДЗ.

            -Наименование столбцов:

            -№;

  -Наименование предприятия;

     -Юридический адрес;

     -Отдел;

     -Ответственное лицо;

     -Дата заключения договора и номер договора;

     -Дата и номер счета;

     -Дата отгрузки товара;

      -Размер ДЗ;

     -Срок ДЗ в днях;

      -Дата оплаты;

      -Срок просроченной задолженности;

      -Количество дней критического  срока оплаты;

      -Дата критического срока оплаты;

      -Неустойка.

      Данные перенесены из листа «Информация о клиентах».

      Данные в столбце «Размер неустойки» также рассчитываем, умножив столбец I «Размер ДЗ» на 0,1%

              Полученные данные представлены на рисунках 14,15,16.

                            Рисунок 14 – Сведения о просроченной ДЗ

 

                            Рисунок 15 – Сведения о просроченной ДЗ

                           Рисунок 16 – Сведения о просроченной ДЗ

 

Как и в предыдущих примерах закрепим области. Для этого помещаем табличный курсор в ячейку С3 и выполняем команду Окно – Закрепить области.

 Проведем  фильтрацию. Выделим столбцы: №, Наименование  предприятия, Юридический адрес, Отдел, Отдел, Ответственное лицо, Дата  заключения договора и номер  заключения, Дата и номер счета, Дата отгрузки товара, Размер  ДЗ, Срок ДЗ в днях, Дата оплаты, Количество дней просрочки, Размер неустойки, Количество дней критического срока оплаты, Дата критического срока оплаты. Затем осуществляется команда Данные – Фильтр – Автофильтр.

 Далее  заполняем лист 10 – Сведения о  ДЗ > 12 мес.

 Наименования  столбцов:

   - №;

   -Наименования столбцов;

    -Юридический адрес;

    -Отдел;

    -Ответственное лицо;

    -Дата заключения договора и номер договора;

    -Дата и номер счета;

   -Дата отгрузки;

   -Размер ДЗ;

   -Срок ДЗ;

   -Дата оплаты;

   -Количество дней просрочки;

   -Размер неустойки (руб.).

   Необходимые данные перенесены из листа «Информация о клиентах». Данные в столбце Q «Размер неустойки» рассчитываем, умножив столбец  M «Размер ДЗ»  на 0,1%.

Закрепим области. Для этого помещаем табличный курсор в ячейку B4 и выполняем команду Окно – Закрепить области.

Проведем фильтрацию. Выделим столбцы: №, Наименование предприятия, Юридический адрес, Отдел, Отдел, Ответственное лицо, Дата заключения договора и номер заключения, Дата и номер счета, Дата отгрузки товара, Размер ДЗ, Срок ДЗ в днях, Дата оплаты, Количество дней просрочки, Размер неустойки. Затем осуществляется команда Данные – Фильтр – Автофильтр.

Также, для того, чтобы определить порядковый номер записи во всех рабочих листах в соответствующих ячейках столбцов необходимо ввести формулы или воспользоваться командой Функции (Вставка – Функция). В случае, если воспользоваться командой Функции, то необходимо выбрать категорию логические – функцию ЕСЛИ, указать условие ИСТИНА для того, чтобы значение в данной ячейке было одно и равно это значение единице (рис. 17). Или с клавиатуры вводим в первую ячейку столбца формулу: =ЕСЛИ(ИСТИНА;1). Эта функция показывает, что в данной ячейке должно быть значение равное единице. В ячейку ниже вводим другую формулу, например: =МАКС($A$4:A4)+1. Формула определяет максимальное значение порядкового номера в диапазоне ячеек, расположенных выше активной ячейки в столбце А и добавляет к нему значение единица. Меняем диапазон методом протягивания указателя от одной угловой ячейки до следующей по вертикали.

 

 
 Рисунок 17 – Формула для порядкового  номера записи

 

 

 

 

 

 

 

 

 

 

2.2 Процесс заполнения извещения дебиторской задолженности

 

После заполнения рабочих листов, преступим к заполнению выходных листов дебиторской задолженности:

-Извещение об оплаченной ДЗ;

-Извещение ДЗ <12 мес.;

-Извещение о просроченной ДЗ;

-Извещение ДЗ >12 мес.

             Рисунок  18 – Извещение об оплаченной  ДЗ

                      Рисунок 19 – Извещение ДЗ < 12 мес.

             Рисунок 20- Извещение о просроченной  ДЗ

               Рисунок 21 – Извещение ДЗ > 12 мес.

На данных рабочих листах объединяем ячейки и переносим текст в ячейках с формулами, форматируем ячейки. Шрифту текста, формируемому в ячейках с формулами, зададим, например, голубой цвет.

Для всех формул одинаковы следующие показатели:

- Искомое значение (значение, которое  должно быть найдено в первом  столбце массива). Им является  ячейка L1. В этой ячейке указан порядковый номер, по которому определяется заполнение формы дебиторской задолженности;

- Диапазон просмотра (логическое значение, определяющее, точно (ИСТИНА) или приближенно (ЛОЖЬ) должно производится сопоставление. В нашем случае выбрано приближенное сопоставление, т.е. ЛОЖЬ;

- Номер_индекса_столбца  показывает номер столбца в  Табл_массив, в котором должно  быть найдено соответствующее  значение. И это значение у  каждой формулы разное.

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

-Извещение  об оплаченной ДЗ – B1:C11;

-Извещение  ДЗ < 12 мес. - B1:C17;

-Извещение  о просроченной ДЗ - B1:C20;

-Извещение  ДЗ > 12 мес. - B1:C22;

Рассмотрим более подробно процесс заполнения все выходных листов. При заполнении использовалась функция ВПР, которая ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из строки, содержащей найденное в первом аргументе значение, но находящегося в столбце, который задан в третьем аргументе функции. Четвертый аргумент — логическое выражение ЛОЖЬ, задается для поиска точно соответствующей информации, введенной в первом аргументе.

Извещение об оплаченной ДЗ:

- формула  поиска Наименования объекта. Воспользуемся командой Функции (Вставка—Функция). Выбираем категорию ссылки и массивы, функцию ВПР или вводим в ячейку B формулу: =ВПР(L1;'Сведения об оплаченной ДЗ'!A3:C5;2;ЛОЖЬ)