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

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

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

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

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

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

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

Рис.5.2. Выбор из cписка

Аналогично нужно задать «cписки» для ячеек A 5, A6 и A7. Единственное отличие в том, что для каждой ячейки задаются свои собственные списки. После

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

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

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

В ячейку В7 введите очень похожую формулу, вычисляющую наценку: =(В4+В5+В6)*(ВПР(А7;С18:D19;2;ЛОЖЬ)-1). В отличие от скидки, наценка получается неотрицательной. Все составные части сметы рассчитаны, осталось их только просуммировать. Введите в ячейку В9 формулу =СУММ(В4:В7). Далее вы можете добавить дополнительные вычисления, например, рассчитать НДС и НСП. Потратив немного времени, вы составили таблицу, существенно ускоряющую составление смет. Выбрав нужные значения из четырех списков, вы мгновенно получаете готовую смету. Если воспользоваться описанными выше приемами, можно автоматически вставить рассчитанные суммы в счет по уплате или в приходный кассовый ордер

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

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

Рис.5.3. Расчет себестоимости

В качестве исходных данных используется объем книги в страницах, формат книги, то есть ее размеры, используемая бумага, тип переплета, то есть обложки книги, тираж и коэффициент гонорара авторам. Условно считается, что авторы получают фиксированную плату за каждый лист, но для некоторых книг может использоваться повышающий или понижающий коэффициент. Введите заголовок таблицы и названия. Все исходные данные вводятся в ячейки столбца В. Если объем, тираж и коэффициент гонорара вводится обычным способом, то остальные три ячейки заполняются данными из списка. Однако если вы создадите список на другом листе, то не сможете просто указать его для проверки, как в предыдущем примере. Для этого нужно выполнить некоторые предварительные действия. Перейдите на второй лист рабочей книги и введите варианты переплета книги. В нашем примере эта информация введена в ячейки с A3 до А5. Выделите эти ячейки и введите в поле имени, расположенное в левой части строки формул, имя Переплет. Далее вернитесь на первый лист и сделайте текущей ячейку В6. Выберите команду меню Данные → Проверка, и на вкладке Параметры появившегося диалога выберите в списке Тип данных вариант Список. В поле Источник введите формулу =Переплет. Если данные для списка расположены на другом листе, для ссылки нужно использовать имя. Установите флажок Список допустимых значений и нажмите кнопку ОК. Диалог закроется, список значений будет задан для данной ячейки. Аналогично задайте «списки» в ячейках В4 и В5. Сразу можно ввести формулу для расчета окончательной суммы. Введите в ячейку В14 формулу =(D3+D6+D9+D10+D16+D17+D18)/B9. После сложения все расходы делятся на размер тиража книги. Далее нужно ввести формулы для расчета, и начнем мы с самых простых. Введите в ячейку D3 формулу =D4+D5. Расходы на обложку состоят из двух частей. Первая часть — расходы на подготовку к печати, например, на изготовление диапозитивов, - не зависят от тиража. И для одной книги и для десяти тысяч книг нужно изготовить одни и те же диапозитивы. Вторая часть зависит от тиража (например, стоимость печати и бумаги). Аналогично, стоимость основной части книги также состоит из двух частей, поэтому введите в ячейку D6 формулу =D7+D8, Так как некоторые другие составляющие также состоят из нескольких частей, введите в D10 формулу =CУMM(D11:D15) aв ячейку D18 формулу =D19+D20. Некоторые суммы в нашем примере фиксированы, и не зависят от вводимых данных. Их можно просто ввести в нужные ячейки, но для единообразия лучше вставить ссылки на ячейки второго листа. Введите в ячейку D4 формулу =Лист2!E14, ав ячейку D11 формулу = Лист2!Е7. Всепростые формулы введены, и настала пора вводить основные формулы для расчетов. При расчетах часто используется функция ВПР, описанная выше, так что мы не будем снова рассказывать об особенностях ее применения. Введите в ячейку D5 формулу =В9*ВПР(В4; Лист2!А18:В20;2;ЛОЖЬ)*ВПР(В6;Лист2!АЗ:В5;2;ЛОЖЬ) Тираж книги умножается на стоимость печати обложки, различающейся для разных форматов. Полученное число умножается на коэффициент, учитывающий тип обложки, например, твердый переплет дороже мягкого.В ячейку D7 введите формулу =ВЗ*Лист2!Е13, так как постоянные расходы, не зависящие от тиража, все же зависят от объема книги. Далее введите наиболее сложную формулу в ячейку D8: =В9*ВЗ*(ВПР(В4; Лист2!А18:С20;3;ЛОЖЬ)+ВПР(В5;Лист2!D3:Е4;2;ЛОЖЬ)*ВПР(В4;Лист2!А18:D20;4;ЛОЖЬ)) Тираж книги умножается на количество страниц, на стоимость страницы, которая в свою очередь состоит из двух частей - стоимости печати и стоимости бумаги. Стоимость печати берется из таблицы со второго листа, в зависимости от формата книги, то есть ее ширины и высоты. Стоимость бумаги определяется на основе формата и типа используемой бумаги. В ячейку D9 введите формулу =ВЗ*Лист2’B7*В7. Гонорар авторам определяется как произведение объема книги на базовую ставку и на коэффициент. Примерно так же определяются затраты на других работников, только без учета коэффициента. Все оставшиеся ячейки, кроме налогов, рассчитываются как произведение соответствующего показателя со второго листа на объем книги, хранящийся в ячейке В3. После того, как введены все формулы и занесены справочные данные на втором листе, можно выполнять расчет. Вводя разные значения в ячейки, можно узнать себестоимость издания. Кстати, с помощью данной таблицы можно подбирать нужные варианты. Выберите команду меню Сервис → Подбор параметра, и откроется диалог настройки подбора. В качестве ячейки, в которой нужно установить заданное значение, выберите ячейку В1, в которой выводится рассчитанная себестоимость. Введите нужное вам значение, а для изменения укажите ячейку В9, то есть тираж книги. Нажмите кнопку ОК, и Excel найдет тираж, при котором можно получить нужную себестоимость Аналогично можно при неизменном тираже найти оптимальный объем книги. Конечно, в нашем примере описана упрощенная схема расчета себестоимости, но, используя те же самые приемы, вы можете рассчитать себестоимость любого товара с учетом самых разных условий.

ПРАКТИЧЕСКАЯ РАБОТА №6