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

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

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

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

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

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

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

Аналогично нужно задать «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