<<
>>

3.2. Автоматизация составления документов

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

3.2.1.

Автоматическое составление сметы и расчет себестоимости

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

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

А В Ж і D \ 1 смета обучения Прейскурант 7

3 f* Щ7ИШ ft»? Є- СіГЧкЛПХГГЬ

эооо Курс бухучета 5000 А <оі«ьютерная 6y»amefw» Курс налогообложения 7000 5

S Программам печатные мв»»|жапы ПОСІОЯМШЙ КЛМВ»1 1600 Углубленное изучение бухучета Компьютерная бухгалтерия 12000 9000 7 Бвэ 2СБ0 в Учебник. 17ti 9 ; Сумма 13?s

3.12.

Составление сметы

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

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

Вначале нужно выбрать, что за курс предполагается прослушать. В нашем примере (Рис. 3.12) эта информация вводится в ячейку^. Но вначале нужно перечислить все возможные варианты курсов и расценки на них. Эта информация вводится в ячейки с СЗ по D6. Далее нужно указать, что в ячейку А4 можно вводить информацию только из ячеек СЗ, С4, С5 и Сб. Любая другая информация в этой ячейке недопустима.

Сделайте текущей ячейку А4, щелкнув на ней мышью, и выберите команду меню Данные Проверка, чтобы открыть диалог настройки проверки вводимых значений. На вкладке Параметры этого диалога выберите в списке Тип данных вариант Список. Этим вы укажите, что в данную ячейку можно вводить значения только из определенного списка. Далее нужно указать, где расположен этот список. Щелкните мышью на поле Источник и выделите с помощью мыши ячейки с СЗ по Адреса ячеек появятся в поле. Вы также можете указать адрес списка вручную, введя в поле выражение =$С$3:$С$6 Установите флажок Список допустимых значений, и нажмите кнопку ОК. Диалог закроется, и теперь вы не сможете ввести в ячейку неправильное значение.

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

Аналогично нужно задать проверку для ячеек А6иА7. Единственное отличие в том, что для каждой ячейки задаются свои собственные списки. После

Рис. 3.13.

Выбор из списка

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

Введите в ячейку формулу Функция ВЛР

очень полезна и будет использоваться во многих примерах.

Она позволяет найти в таблице строку, содержащую нужное значение, и вернуть значение из другой ячейки этой же строки. В нашем случае мы ищем название курса, а хотим узнать его цену, записанную в той же строке. Диапазон ячеек C3:D6 указывает на область таблицы, в которой будет выполняться поиск. Поиск осуществляется по ячейкам первого столбца области, то есть по ячейкам столбца С. Значение, которое будет искаться, указано в ячейке А4, что и задается первым аргументом функции. Число 2 в качестве третьего аргумента указывает, что нужно взять содержимое второго столбца найденной строки, то есть столбца D. Слово ЛОЖЬ в формуле говорит, что список в указанном диапазоне ячеек может быть не отсортирован. Теперь, если вы введете в ячейку А4значение Курс бухучета, в ячейке В4 появится сумма из третьей строки столбца D, так как именно в этой строке в прейскуранте находится данное название. В нашем примере это будет число 5000. Аналогично, в ячейку В5 нужно ввести формулу

чтобы вставить в смету стоимость дополнительных

материалов.

Так как остальные вычисления предполагают увеличение или уменьшение базовой суммы, формулы получатся немного сложнее. В ячейку В6 введите формулу =(В4+В5)*(ВПР(А6;С15:О16;2;Л0ЖЬ)-1?у\\\\а В4+В5 вычисляет стоимость курса вместе с дополнительными материалами. Выражение ВПР(А6;С15:1)16;2;ЛОЖ возвращает процент, который должен уплатить клиент. Если вычесть из него единицу, то мы получим процент скидки, причем он будет отрицательный. Умножив первую часть формулы на вторую, мы получим размер предоставляемой скидки.

В ячейку В7 введите очень похожую формулу, вычисляющую наценку: =(В4+В5+В6)*(ВПР(А7;С18:В19;2;ЛОЖЬ)-Щ отличие от скидки, наценка получается неотрицательной. Все составные части сметы рассчитаны, осталось их только просуммировать. Введите в ячейку В9 формулу Коммен

тарии тут не требуются. Далее вы можете добавить дополнительные вычисления, например, рассчитать НДС и НСП. Потратив немного времени, вы составили таблицу, существенно ускоряющую составление смет.

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

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

Все исходные данные и результаты в примере расположены на одном листе (Рис. 3.14, слева), а вспомогательная информация, нужная для расчетов, - на А в С С А | е С Р t 1 Расчет себестоимости выпуск» жни*и

Т ! Переплел

г I . 5уНШ?» 3 :Ж*сткий 1.ь Офсетная 0.05 4 ; Формат книги 4 - Газетная 0.01 Газетная Тираж 17500 5 'Бумажный с паминатом 1,1 6

"У- і Коэффициент Бумажный 65600 fi-'i Подготовка 9600 7 : Типовой гонорар за страницу 80 Художнику 3000 8 і гонорара авторов 11.15 Тираж 56000 Т: Редактору за страницу 5 9 ;Тира* 7000 Гонорар авторам 29440 9 :Склад и трансп. расх. на книг "0.9 Корректору за страницу 4 10 J Подготовка к 1E+Q5 Щ 6.85 Верстка за 3 11 Художнику 3000 1,1 ; Прочие расходы на книгу Оборудование и материалы Редактору 35000 121 1] Корректору 28000 Й Счета ЕСН 35,6% Стоимость 30 14і Рассчитанная 45 Верстка ЇІ Стм,а НДС 20% Стоимость подготовки обложки 900 15! 16: Транспорт и склад 6300 f61 Фсрмас*nl«u Cm. Cm. Расход бумаги 17 Прочие расходы 5950 17: обл. 13 Налоги W741 "18Г;«0х9О/1& 2 0.01 .0.77 19 ЕСН 41453 19 170x100/17 2,5 0.015 1 20 НДС 23288 20 184x108/18 3 0,02 1.3 Рис. 3.14, Расчет себестоимости

другом (Рис. 3.14, справа). В качестве исходных данных используется объем книги в страницах, формат книги, то есть ее размеры, используемая бумага, тип переплета, то есть обложки книги, тираж и коэффициент гонорара авторам.

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

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

Перейдите на второй лист рабочей книги и введите варианты переплета книги. В нашем примере эта информация введена в ячейки с A3 до В5. Выделите эти ячейки и введите в поле имени, расположенное в левой части строки формул, имя Переплет. Далее вернитесь на первый лист и сделайте текущей ячейку В6. Выберите команду менк> Данные Проверка, и на вкладке Параметры появившегося диалога выберите в списке Тип данных вариант Список. В поле Источник введите формулу Если данные для списка расположены на другом

листе, для ссылки нужно использовать имя. Установите флажок Список допусти-мых значений и нажмите кнопку ОК. Диалог закроется, список значений будет задан для данной ячейки. Аналогично задайте проверку ячеек В4 и В5. Сразу можно ввести формулу для расчета окончательной суммы. Введите в ячейку В14 формулу =(D3+D6+D9+D10+D16+D17+D18)/B9. После сложения все расходы делятся на размер тиража книги.

Далее нужно ввести формулы для расчета, и начнем мы с самых простых. Введите в ячейку D3 формулу =D4+D5. Расходы на обложку состоят из двух частей. Первая часть — расходы на подготовку к печати, например, на изготовление диапозитивов, - не зависят от тиража. И для одной книги и для десяти тысяч книг нужно изготовить одни и те же диапозитивы. Вторая часть зависит от тиража (например, стоимость печати и бумаги). Аналогично, стоимость ос-новной части книги также состоит из двух частей, поэтому введите в ячейку D6 формулу =D7+D8, Так как некоторые другие составляющие также состоят из нескольких частей, введите в : формулу =СУММ(В11:В15_ а в ячейку D18 формулу =D19+D20.

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

Введите в ячейку D4 формулу =Jlucm2!E14 а в ячейку D11 формулу =Лист2!Е" Все простые формулы введены, и настала пора вводить основные формулы для расчетов. При расчетах часто используется функция ВНР описанная выше, так что мы не будем снова рассказывать об особенностях ее применения. Введите в ячейку D5 формулу

=В9*ВПР(В4;Лист2!А18:В20;2;ЛОЖЬ)*ВПР(В6;Лист2!АЗ:В5;2;ЛОЖЬ) Тираж книги умножается на стоимость печати обложки, различающейся для разных форматов. Полученное число умножается на коэффициент, учитывающий тип обложки, например, твердый переплет дороже мягкого.

В ячейку введите формулу так как постоянные расходы,

не зависящие от тиража, все же зависят от объема книги. Далее введите наиболее сложную формулу в ячейку

=В9*ВЗ*(ВПР(В4;Лист2!А18:С20;3;Л()ЖЬ)+ ВПР(В5;Лист2!ОЗ:Е4;2;ЛОЖЬ)*ВПР(В4;Лист2!А18:О20;4;ЛОЖЬ)) Тираж книги умножается на количество страниц, на стоимость страницы, которая в свою очередь состоит из двух частей - стоимости печати и стоимости бумаги. Стоимость печати берется из таблицы со второго листа, в зависимости от формата книги, то есть ее ширины и высоты. Стоимость бумаги определяется на основе формата и типа используемой бумаги.

В ячейку D9 введите формулу =ВЗ*Лист2. В7*В7. Гонорар авторам определяется как произведение объема книги на базовую ставку и на коэффициент. Примерно так же определяются затраты на других работников, только без учета коэффициента. Все оставшиеся ячейки, кроме налогов, рассчитываются как произведение соответствующего показателя со второго листа на объем книги, хранящийся в ячейке ВЗ. А о том, как считаются налоги, можно и не рассказывать.

После того, как введены все формулы и занесены справочные данные на втором листе, можно выполнять расчет. Вводя разные значения в ячейки, можно узнать себестоимость издания. Кстати, с помощью данной таблицы можно под-бирать нужные варианты. Выберите команду меню Сервис Подбор параметра, и откроется диалог настройки подбора. В качестве ячейки, в которой нужно установить заданное значение, выберите ячейку В14, в которой выводится рассчитанная себестоимость. Введите нужное вам значение, а для изменения укажите ячейку В9, то есть тираж книги. Нажмите кнопку OA', и Excel найдет тираж, при котором можно получить нужную себестоимость. Аналогично можно при неизменном тираже найти оптимальный объем книги.

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

3.2.2. Автоматизация создания платежных поручений

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

Вначале вам надо создать бланк платежного поручения или получить его из любого источника. На этой простой операции мы не будем останавливаться. Также вам понадобится программа печати прописью, описанная выше. Если вы не получили из Интернета нужный макрос или не создали лист с формулами, вам придется вручную вводить суммы прописью, что очень неудобно.

Для автоматизации создания платежных поручений вам понадобится рабо-чая книга Excel с тремя листами. На одном листе будет находиться бланк платежного поручения, а на другом — список платежек, а на третьем — реквизиты плательщика. Если вы используете лист с расчетами для вывода суммы прописью, этот лист будет четвертым. Вначале, как уже было сказано, создайте бланк платежного поручения. Он должен выглядеть примерно так (Рис. 3.15, слева). Обратите внимание, что ячейки, предназначенные для вывода больших текстов, объединены с соседними ячейками. ILflj N I f*3l«C в 1 Информация о плательщиц 2 \ 3 ИНН 200148790 4 Название ООО "Ноя ''.обстеелнзя фирма" 5 ; Расчетный счет 3574547604762 Л , StJUfc плательщика Банк "Банкиры pskc мендуїет" 7 \ Корреспондентский СЧвТ 13236747623712 8 і БИК 044585384 в | с ! о

і ЇМ.. Й .".і

Поступ, t б»*кпл»т

ПЛАТЕЖНОЕ ПОРУЧЕНИЕ *

Щ

Я

14 | банк получателя

.17:

¦^д.; Получатель

Рис. 3.15. Бланк платежного поручения и реквизиты плательщика Далее перейдите на другой лист и введите реквизиты своего предприятия (Рис. 3.15, справа). После этого снова вернитесь на лист с бланком и проставьте в нужные места ссылки на ячейки листа с реквизитами. В принципе, можно ввести эту информацию непосредственно в бланк, но при работе с несколькими предприятиями или с несколькими расчетными счетами можно будет добавить еще один лист с реквизитами и заменить только название листа, а не менять всю информацию в ячейках. Кроме того, удобнее, когда вся информация сосредоточена в одном столбце, а не разбросана по разным ячейкам. Однако, еще раз повторимся, что вполне допустимо ввести реквизиты плательщика непосредственно в бланк платежного поручения. На этом подготовительная работа закончена, и настала пора создавать базу платежных поручений.

Перейдите на чистый лист, на котором будет располагаться информация обо всех платежках. В первой строке, в ячейках с Ml введите заголовки полей платежного поручения. При этом информацию о плательщике и сумму прописью вводить не надо. Начиная со второй строки, будут располагаться строки с описанием разных платежных поручений. Заполните две—три строки, введя информацию о разных платежках. При этом вам потребуется изменить формат нескольких столбцов. Для суммы лучше использовать финансовый формат, для даты — формат даты, а для остальных полей лучше использовать текстовый формат. Если используется общий формат, то, например, в БИК не будут видны нули, расположенные слева. В результате вы должны получить примерно такую таблицу (Рис. 3.16).

Далее перейдите на страницу с бланком платежного поручения и завершите заполнение полей. Единственным полем, заполняемым вручную, будут номер платежного поручения. У нас он расположен в ячейке ЕЗ. Все остальные поля заполняются автоматически. Так как почти все используемые формулы однотипны, рассмотрим заполнение поля суммы платежа. В нашем бланке эта сумма находится в ячейке Н6. Сумма должна быть взята из столбца С базы платежек, расположенной у нас на листе Введите в ячейку Н6 формулу

=ВЛР(ЕЗ;Лист2!А2:МЮОО;3;ЛОЖ. Как вы помните, функция ВПР находит в диапазоне то есть в тысяче строк базы платежек, строку с

номером, совпадающим со значением ячейки ЕЗ, то есть находит нужное платежное поручение по его номеру. Результатом вычислений будет значение из третьего столбца найденной строки, то есть сумма платежа для платежки с указанным номером. Абсолютно такие же формулы нужно ввести во все оставшиеся поля бланка, кроме суммы прописью. Отличие будет только в том, что выбираться будут разные столбцы. Например, в ячейку Н12, где выводится БИК получа- А В С і О" е

{ Номар Дата Суша Назначение платеж» Пия

20 11 2002 45 685 468,5бр Оплата консультационных ЗАО "Все "4 услуг шглсмь договору N» ко*суп»т»ции" '23 22 11.2002 56 728.ООр Ома» тепвфоиных успуг ОАО "Телефонная

3 Г . G • И | I J : К ¦ і і М і

ИНН Расчётный счёт Банк Кор. счёт ПИК Видплат. Вов an. Он. плат

457543134 3570847604764 Б»кк 'Сотый 324468004560Э 044582318 «ымтронна 01 "б банк'

376543564 3453465488044 Кб Телефонии* 39294980568665 047685619 амктрбнмй 01 6 Рис. 3.16. База платежных поручений теля, нужно ввести формулу В десятом

столбце, то есть в столбце /базы платежек, как раз и расположен БИК.

После того, как похожие формулы добавлены везде, где надо, осталось ввести сумму прописью. В нашем бланке она отображается в ячейке С5. Если у вас установлен макрос, описанный выше, введите в ячейку формулу =СуммаПропи- сыо(Нб) Напоминаем, что в ячейке Н6у нас находится сумма числом. Если же вы используете дополнительный расчетный лист для получения суммы прописью, установите ссылку на нужную ячейку этого листа, не забыв сослаться с листа расчетов на ячейку Н6 листа бланка, для получения исходных данных преобразования.

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

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

В правой части каждой ячейки заголовка появится кнопка со стрелкой При нажатии этой кнопки открывается список со значениями, по которым можно отфильтровать список. Например, нажмите кнопку gig в столбце Имя, и будет открыт список получателей. Выберите нужную организацию, и в списке останутся только платежные поручения по платежам данной организации. Чтобы снова показать все строки, следует снова нажать кнопку чг в столбце Имя и выбрать элемент Все в открывающемся списке. Вы можете выбрать одновременную фильтрацию по нескольким значениям, например по получателю и назначению платежа. Аналогично можно отобрать операции с нужными датами или суммами. Можно задать более сложное условие для фильтрации, выбрав элемент открывающегося списка Условие. На экране появится диалог, в котором вы можете задать любое условие, например сумма оплаты должна быть больше 300 и меньше 40000 или дата больше июня текущего года.

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

<< | >>
Источник: Коцюбинский А.О., Грошев С.В.. Excel для бухгалтера в примерах.. 2003

Еще по теме 3.2. Автоматизация составления документов:

  1. 6.2. Общие правила составления таможенных документов
  2. Глава 10. ПРАВОВОЙ РЕЖИМ СОСТАВЛЕНИЯ ПРОЕКТОВ БЮДЖЕТОВ. Общие положения о составлении проектов бюджетов
  3. Глава 9. АВТОМАТИЗАЦИЯ УЧЕТА В СТРОИТЕЛЬСТВЕ
  4. СИСТЕМА «ТОЧНО ВОВРЕМЯ» И АВТОМАТИЗАЦИЯ
  5. автоматизация «с человеческим лицом
  6. 9.3. Порядок выполнения автоматизации
  7. ПРОСТАЯ АВТОМАТИЗАЦИЯ ВЕДЕТ К ТРАВМАТИЗМУ
  8. 29.2. АВТОМАТИЗАЦИЯ БУХГАЛТЕРСКОГО УЧЕТА
  9. 36. ОБЩИЕ ПОЛОЖЕНИЯ ОСМОТРА ДОКУМЕНТОВ. ПРАВИЛА ОБРАЩЕНИЯ С ДОКУМЕНТАМИ – ВЕЩЕСТВЕННЫМИ ДОКАЗАТЕЛЬСТВАМИ
  10. 7.2. Если ваш бухгалтер «ушел» со всеми документами (случай полного и частичного отсутствия документов)
  11. 9.1. Цели и роль автоматизации учета для строительнойкомпании
  12. 3.3. Автоматизация отдельных направлений деятельности
  13. Глава 6. АВТОМАТИЗАЦИЯ ФИНАНСОВО- ХОЗЯЙСТВЕННОЙ ДЕЯТЕЛЬНОСТИ ПРЕДПРИЯТИЙ НА БАЗЕ ЭИС
- Аудит - Бухгалтерский учет - Макро и микро экономика - Экономическая теория -
- Бизнес - Компьютеры и интернет - Психология - Философия - Финансы - Экономика - Юриспруденция и право - Lecture.Center