Работа предприятия в таблице excel

Привет! Мы «Нескучные финансы», помогаем бизнесу навести порядок в учете и управлять бизнесом на основе цифр. Для этого мы или берем финучет на аутсорс, или предоставляем удобные бесплатные шаблоны. В этой статье как раз второе.

Мы подготовили барсетку. Это 16 шаблонов финансовых таблиц, в которых вы можете вести управленческий учет в своем бизнесе. Для каждой таблицы мы записали видеоинструкцию, ссылка на которую лежит на последней вкладке таблицы.

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

Чтобы воспользоваться таблицей, нужно сделать ее копию на свой Гугл-диск. Можно скачать в Экселе, но не рекомендуем: какие-то формулы могут не работать.

ДДС (отчет о движении денежных средств)

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

Баланс

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

Баланс состоит из активов и пассивов. В активах показывается, что есть у компании, а в пассивах ― кому это принадлежит.

Отчет о прибылях и убытках

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

Учет основных средств

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

Управление запасами

Помогает навести порядок на складе и знать, сколько товара есть в наличии. Еще управлять запасами ― это держать ходовой товар в достаточном количестве. Чтобы определить спрос на товар, нужно анализировать фактические данные.ABC-анализ дает понять, какие товары занимали наибольшую долю в выручке компании, а XYZ-анализ помогает понять, как часто покупают конкретный товар. Можно анализировать не только конкретные продукты, но и категории товаров.

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

Учет логистики

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

Учет финансовой деятельности

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

Учет сделок

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

Финансовая модель

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

Платежный календарь

Один из инструментов планирования. Сначала планируют на месяц-два-три бюджет, а на его основе делают платежный календарь. В нем по датам видно, сколько и когда ожидается поступлений и затрат. Это помогает предвосхищать кассовые разрывы: в платежном календаре видны «дырки», которые можно залатать предоплатой от другого клиента, переносом сроков оплаты, заемными средствами и другими способами.

Зарплатная ведомость

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

Маркетинговый отчет

Сводный документ, в котором учитываются рекламные активности на разных онлайн площадках и привлеченный с них трафик. С помощью такого инструмента можно собрать данные для анализа эффективности рекламы из разных источников: в отчете наглядно видно, что эффективней для продвижения вашего продукта ― контекстная реклама в Яндекс.Директе или таркетинговая во Вконтакте. Также здесь можно вести учет рекламных расходов и выручки, которая получена от клиентов из онлайна.

Калькулятор рентабельности проектов

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

Калькулятор финансового рычага

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

Калькулятор влияния скидки на прибыль

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

Отчет отдела продаж

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

Пользуйтесь на здоровье! Если есть вопросы по шаблонам, задавайте их в комментариях.

Microsoft Excel дает пользователю целый инструментарий для анализа финансовой деятельности предприятия, проведения статистических расчетов и прогнозирования.

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

Пример финансового анализа предприятия в Excel

Задача – изучение результатов финансовой деятельности и состояния предприятия. Цели:

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

Основываясь на результатах финансовой деятельности, руководитель вырабатывают стратегию дальнейшего развития предприятия.

Анализ финансового состояния предприятия подразумевает

• анализ баланса и отчета о прибылях и убытках;
• анализ ликвидности баланса;
• анализ платежеспособности, финансовой стабильности предприятия;
• анализ деловой активности, состояния активов.

Рассмотрим приемы анализа балансового отчета в Excel.

Сначала составляем баланс (для примера – схематично, не используя все данные из формы 1).

Проанализируем структуру активов и пассивов, динамику изменений величины статей – построим сравнительный аналитический баланс.

1. Представим значения на начало и на конец года в виде относительных величин. Формула: =B4/$B$14 (отношение значения на начало года к величине баланса на начало года). По такому же принципу составляем формулы для «конца года» и «пассива». Копируем на весь столбец. В новых столбцах устанавливаем процентный формат.



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



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



4. Чтобы найти динамику в процентах к значению показателя начала года, считаем отношение абсолютного показателя к значению начала года. Формула: =F4/B4. Копируем на весь столбец.



5. По такому же принципу находим динамику в процентах для значений конца года.

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

Какие результаты дает аналитический баланс:

1. Валюта баланса в конце отчетного периода стала больше в сравнении с начальным периодом.
2. Внеоборотные активы приращиваются с более высокими темпами, чем оборотные.
3. Собственный капитал предприятия больше, чем заемный. Причем темпы роста собственного превышают динамику заемного.
4. Кредиторская и дебиторская задолженность приращиваются примерно в одинаковом темпе.



Статистический анализ данных в Excel

Для реализации статистических методов в программе Excel предусмотрен огромный набор средств. Часть из них – встроенные функции. Специализированные способы обработки данных доступны в надстройке «Пакет анализа».

Рассмотрим популярные статистические функции.

1. СРЗНАЧ – Среднее значение – рассчитывает выборочное или генеральное среднее. Аргумент функции – набор чисел, указанный в виде ссылки на диапазон ячеек.



2. ДИСП – для вычисления выборочной дисперсии (без учета текстовых и логических значений); ДИСПА – учитывает текстовые и логические значения. ДИСПР – для вычисления генеральной дисперсии (ДИСПРА – с учетом текстовых и логических параметров).



3. Для нахождения квадратного корня из дисперсии – СТАНДОТКЛОН (для выборочного стандартного отклонения) и СТАНДОТКЛОНП (для генерального стандартного отклонения).



4. Для нахождения моды совокупности данных применяется одноименная функция. Разделяет диапазон данных на две равные по числу элементов части МЕДИАНА.



5. Размах варьирования – это разность между наибольшим и наименьшим значением совокупности данных. В Excel можно найти следующим образом:



6. Проверить отклонение от нормального распределения позволяют функции СКОС (асимметрия) и ЭКСЦЕСС. Асимметрия отражает величину несимметричности распределения данных: большая часть значений больше или меньше среднего.

В примере большая часть данных выше среднего, т.к. асимметрия больше «0».

ЭКСЦЕСС сравнивает максимум экспериментального с максимумом нормального распределения.

В примере максимум распределения экспериментальных данных выше нормального распределения.

Рассмотрим, как для целей статистики применяется надстройка «Пакет анализа».

Задача: Сгенерировать 400 случайных чисел с нормальным распределением. Оформить полный перечень статистических характеристик и гистограмму.

1. Открываем меню инструмента «Анализ данных» на вкладка «Данные» (если данный инструмент недоступен, то нужно подключить настройку анализа ). Выбираем строку «Генерация случайных чисел».



2. Вносим в поля диалогового окна следующие данные:



3. После нажатия ОК:



4. Зададим интервалы решения. Предположим, что их длины одинаковые и равны 3. Ставим курсор в ячейку В2. Вводим начальное число для автоматического составления интервалов. К примеру, 65. Далее нужно сделать доступной команду «Заполнить». Открываем меню «Параметры Excel» (кнопка «Офис»). Выполняем действия, изображенные на рисунке:



5. На панели быстрого доступа появляется нужная кнопка. В выпадающем меню выбираем команду «Прогрессия». Заполняем диалоговое окно. В столбце В появятся интервалы разбиения.



6. Первый результат работы:



7. Снова открываем список инструмента «Анализ данных». Выбираем «Гистограмма». Заполняем диалоговое окно:



8. Второй результат работы:



9. Построить таблицу статистических характеристик поможет команда «Описательная статистика» (пакет «Анализ данных»). Диалоговое окно заполним следующим образом:

После нажатия ОК отображаются основные статистические параметры по данному ряду.

Скачать пример финансового анализа в Excel

Это третий окончательный результат работы в данном примере.

Планирование производства на предприятии в эксель

Планирование производства на предприятии в эксель

Представьте, что вы планируете закупки расходных материалов для производства. К вам стекаются 2 потока данных: производственные планы и прогноз по наличию производственных материалов на складах. У вас несколько заводов, много видов материалов. На выходе вы обязаны предоставлять информацию по тому, какие материалы в каких количествах и когда следует закупать и куда отправить.

Рассматривая этот пример, мы научимся нескольким довольно оригинальным и эффектным приёмам, которые вы потом сможете применять для своих задач.

Вы узнаете универсальный метод совмещения данных из двух (и более) таблиц, имеющих разные форматы

Вы узнаете, как использовать сводные таблицы для получения отчёта с нарастающим итогом

Мы будем использовать: умные таблицы, именованные диапазоны, формулы ИНДЕКС (INDEX), ЕСЛИ (IF), ПОИСКПОЗ (MATCH), СТОЛБЕЦ (COLUMN), СТРОКА (ROW), ЧСТРОК (ROWS) и сводные таблицы

Вы увидите отличную иллюстрацию синтеза вышеперечисленных инструментов Excel для достижения впечатляющих результатов

Данные на входе

Лист REQ содержит планы использования материалов (компоненты) для производства конечной продукции.

Например, компонент P49 потребуется на зводе L01 в количестве 58 235 штук к 26 мая 2015 года. Обратите внимания, что суммы отрицательные, в отличие от следующей таблицы. Это нам пригодится.

Лист STK отражает процесс поступления материалов на склады заводов.

Например, материал P97 в количестве 229 784 штук 7 апреля 2015 года поступит на склад завода L01, так как есть соответствующий контракт с производителем этого материала.

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

Итак, у нас с вами есть поток событий, которые уменьшают запасы материалов на складах (производство) и поток событий, которые увеличивают запасы (закупки). Всё что нам надо — это выстроить эти события на одной временной шкале и следить, чтобы уровень складских запасов не становился отрицательным. Отрицательный уровень запасов говорит о том, что для производства не хватает материалов. Многие крупные компании имеют штат людей, которые занимаются примерно такой работой, которую я сейчас описал. В данном случае моя задача, показать пути, как это можно делать в Excel с минимальным количеством усилий и с известной долей изящества.

Файл примера

Объединяем таблицы

Объединять таблицы будем. формулами. То есть в ячейках нашей объединенной таблицы будут такие формулы, которые сначала выведут все строки таблицы REQ, а затем все строки таблицы STK. И всё это будет сделано с учётом того, что у всех таблиц разная структура. На этом этапе мы совершенно не будем заботиться о сортировке строк — пусть идут, как идут.

Исходные таблицы оформляем в виде умных таблиц, присваивая им соответствующие идентификаторы: лист REQ — умная таблица tblREQ , лист STK — tblSTK .

Теперь перейдём на лист Combine . Наша объединенная таблица должна состоять из следующих столбцов: Компонент , Завод , Срок , Кол-во , где Срок — это либо дата производства, либо дата поступления материала на склад. Кроме этого добавляем 2 вспомогательных столбца: Таблица и Строка . Если ячейка столбца Таблица содержит 1, то данные извлекаются из таблицы tblREQ , если 2 — то tblSTK . Ячейки столбца Строка будут подсказывать, из какой строки соответствующей таблицы брать данные.

Формула для колонки Таблица выглядит так:

=ЕСЛИ( СТРОКА(1:1) 0″ ) + 1 )

Это стандартный подход, рассматренный тут.

Сводная таблица

Вот сейчас будет важно, очень многие этого не понимают:

Всё, что может быть сделано при помощи сводных таблиц, должно быть сделано при помощи сводных таблиц.

Это вопрос ваших трудозатрат, эффективности вашей работы. Сводные таблицы — ключевой инструмент Excel. Инструмент чрезвычайно мощный и простой ОДНОВРЕМЕННО . Понимаете, одновременно!

Итак, сводную таблицу строим на основе ИД rngCombined . Настройки все стандартные:

Поле Кол-во я переименовал в Запасы. Операция по этому полю само-собой суммирование плюс вот такая настройка:

Этим мы получаем нарастающий итог по запасам материала в разрезе Компонент — Завод . И всё, что нам остаётся делать — это отслеживать и не допускать появления отрицательных запасов. Например, смотрим отрицательное значение в строке 34 сводной таблицы. Оно означает, что на заводе L02 2 июня 2015 года запланировано производство с участием материала P97 и, учитывая объём запланированного производства, нам не хватит 22 584 штук материала P97. Смотрим в таблицу REQ и убеждаемся, что действительно 2 июня завод L02 хочет производить что-то с использованием 57 646 штук P97, а на складах у нас на этот день такого количества не будет. В финансах это называется «кассовый разрыв». Вещь очень печальная 🙂

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

Планирование производства — путь к успешному бизнесу

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

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

Процесс планирования целесообразно разделить на три этапа:

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

2. Определение основных действий, которые нужно осуществлять для достижения целей, принимая во внимание воздействие внешних и внутренних факторов.

3. Разработка гибкой системы планирования, обеспечивающей достижение поставленных целей.

ПРИНЦИПЫ И ВИДЫ ПЛАНИРОВАНИЯ

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

1. Принцип непрерывности подразумевает, что процесс планирования осуществляется постоянно в течение всего периода деятельности предприятия.
2. Принцип необходимости означает обязательное применение планов при выполнении любого вида трудовой деятельности.
3. Принцип единства констатирует, что планирование на предприятии должно быть системным. Понятие системы подразумевает взаимосвязь между ее элементами, наличие единого направления развития этих элементов, ориентированных на общие цели. В данном случае предполагается, что единый сводный план предприятия согласуется с отдельными планами его служб и подразделений.
4. Принцип экономичности. Планы должны предусматривать такой путь достижения цели, который связан с максимумом получаемого эффекта. Затраты на составление плана не должны превышать предполагаемых доходов (внедряемый план должен окупаться).
5. Принцип гибкости предоставляет системе планирования возможность менять свою направленность в связи с изменениями внутреннего или внешнего характера (колебание спроса, изменение цен, тарифов).
6. Принцип точности. План должен быть составлен с такой степенью точности, которая приемлема для решения возникающих проблем.
7. Принцип участия. Каждое подразделение предприятия становится участником процесса планирования независимо от выполняемой функции.
8. Принцип нацеленности на конечный результат. Все звенья предприятия имеют единую конечную цель, реализация которой является приоритетной.

В зависимости от содержания поставленных целей и задач планирование можно разделить на следующие виды (табл. 1).

Таблица 1. Виды планирования

Производственный учет

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

Важнейшими факторами управления производством на предприятии являются:

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

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

ПОСТАНОВКА ВОПРОСА

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

Без конкретной информации цех может начать делать что-то неактуальное на текущий момент, в надежде что угадали, и продукция сгодится после уточнения задания. Уточнения как правило не совпадают с желанием и возможностями. Поскольку оборудование занято производством других изделий и технология производства не позволяет освободить имеющееся оборудование занятое процессом производства для изготовления новой партии изделий, то получить требуемую продукцию вовремя не получается и образуется дефицит изделий. Начинаются крики, неразбериха, простои цехов и объектов составляющих цепочку зависимостей от требуемой продукции. Руководители верхнего уровня задают вопросы – зачем делали не то что нужно, зачем затратили сырье и материалы, зачем переполнили склады? Где-то так и выглядит реальный процесс производства.
Для того чтобы получалось именно то что требуется применяются графики производства. В графиках есть сроки изготовления изделий с учетом технологии применяемой в производстве. При этом график дублируется в нескольких экземплярах до уровня последнего старшего по каждому производственному участку. Выполнение графиков производства влечет за собой учет того что сделано. К составлению графиков мы еже вернемся в последующих обзорах. Остановимся на индивидуальном учете того что сделано конкретным работником. Обычно первичный учет изготовленной продукции возлагается на мастеров цеха. Для этих целей в цехах применяются журналы, которые в процессе рабочих смен заполняется промежуточными итогами. Записи в журнал заносятся от руки и иногда случаются ошибки в наименованиях изготовленных изделий или в их количестве. В масштабах завода для учета изготовленной продукции применяются базы данных (БД) типа 1С в которые данные заносятся из журнала мастеров и накладных на сданную (переданную) цехами продукцию. При этом нередко случаются разночтения межу БД и журналом, что приводит к ошибкам в учете и как следствие приводит к внеплановым инвентаризациям. Составление графиков будем рассматривать позже.
Для ускорения ввода данных в БД, а также копий этих данных для других отделов на предприятии где я работал применяются электронные журналы. Журнал представляет из себя таблицу Excel с выпадающими списками фамилий рабочих и марок изделий из небольшой базы данных. Списки содержат проверенные данные и ошибки исключаются. База данных располагается в отдельной книге Excel которая редактируется при необходимости. Мастер выбирает нужное, проставляет количество и получает алфавитный список фамилий и марок изделий с объемом выполненных работ.

За основу электронного журнала применена разработка автора под ником “nerv”. Разработка опубликована на сайте http://excelvba.ru/code/DropDownList под названием “Надстройка: выпадающий список с поиском (комбо)”. Кого заинтересовала эта информация могут посмотреть описание на указанном сайте и там же скачать эту надстройку.

Однако, эту надстройку поместить как обыкновенную надстройку в папку «Addins» созданную при установке Microsoft Office иногда не получается, так как сетевые администраторы часто блокируют установку программ и запрещают в локальной политике своих серверов вмешательство в систему. Предлагаемый вариант организации размещения папок электронного журнала на дисках рабочего компьютера позволяет выполнить установку надстройки в другую папку.

Всего создается 5 папок: 2 папки создаются в корне диска «С». Это папки «ДАННЫЕ» и «НАДСТРОЙКИ». В папку «ДАННЫЕ» помещается файл DDLSettings.xlsx, который будет производственной базой данных. Вид листа Excel с базой данных см. на рисунке ниже.

Папки и их содержимое на диске «С» рабочей станции

В папку «НАДСТРОЙКИ» помещается надстройка «выпадающий список с поиском (комбо)» автора «nerv» — nerv_DropDownList_1.6.xla.

Как установить надстройку Excel 2013/2016

Надстройка может храниться в компьютере в любой папке. В нашем обзоре это папка «НАДСТРОЙКИ». После этого запускается любой файл Excel и из строки меню надо пройти путь: Файл → Параметры → Надстройки → Управление → Надстройки Excel → Перейти… → Доступные надстройки → кнопка Обзор → найти в проводнике Windows папку «НАДСТРОЙКИ» → выделить надстройку “nerv_DropDownList_1.6.xla”? нажать кнопку открыть и поставить в чек боксе (напротив надстройки) ”drop-down list with search” галочку. Все, надстройка подключена и будет делать выпадающие списки.

Следующие 3 папки размещаются в паке компьютера «рабочий стол»: 1 – «Шаблон», 2 – «Журнал учета работ», 3 – «Архив & HELP». В папке «Шаблон» лежит незаполненный бланк учета работ с названием 00.00.0000.xlsm. Вместо этих нулей можно написать любой заголовок. Вообще-то эти нули подразумевают дату работ. Например, 22.11.2017г. Эта дата будет перенесена на лист учета работ в соответствующую ячейку.

Папки и их содержимое на «Рабочем столе» компьютера

После размещения папок по указанным местам открываем папку «ДАННЫЕ» на диске «С» и открываем книгу DDLSettings.xlsx с базой данных. Заполняем, редактируем, исправляем и сохраняем. Алфавит соблюдать не надо. Переходим на «Рабочий стол» и копируем на него книгу «00.00.0000.xlsm» из папки «Шаблон журнала». Даем книге нужное название и запускаем книгу.

При запуске книги данные из БД (с диска «С») переносятся на лист DDLSettings которые надо подтвердить. Далее переходим на лист ввода данных (в нашей книге это лист Смена1). С целью сохранения формата листа и формул разрешено вводить данные только в столбцы «ФИО работающих», «марка» и «к-во», а также ФИО мастеров. Ячейки с формулами заблокированы, форматирование на листе тоже запрещено. (Пароль для снятия защиты: treb). Данные можно вводить непосредственно в ячейку с клавиатуры, но это чревато ошибками. Поэтому выделяется ячейка ввода и нажимается комбинация клавиш Ctrl + Enter. Появляется окно ввода с выпадающим списком. Стоит набрать 1-2 буквы и слова, начинающиеся с этих букв, и нужная запись подтянется в видимую область. Курсором мышки надо выбрать нужное слово, и оно переместится в строку выбора. Если все правильно, надо нажать клавишу Enter и выбранное слово переместится в ячейку и выпадающий список скроется. Подправить можно и в строке выбора и в самой ячейке, но это делать не стоит.

Когда все данные внесены можно распечатать страницы и сгруппировать внесенные данные на один лист. Лист называется «Результат». Лист заполняется при нажатии кнопки «START» расположенной на листе «Смена1» в его нижней части.

Данные на листе «Результат»

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

Шаблон универсального бизнес-плана в формате Excel

Представляю Вашему вниманию универсальный бизнес-план. В данной версии бизнес-плана Вы можете сравнить два или три варианта бизнеса между собой, чтобы выбрать самый оптимальный, т.е., Вы можете посмотреть, какие показатели будут достигнуты в случае приобретения разного типа оборудования (разная цена, производительность, эксплуатационные расходы и т.д.). Можно составить бизнес-план только на один вариант развития событий. Для этого варианты 2 и 3 заполнять не нужно.

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

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

При возникновении вопросов, пишите через форму обратной связи.

Скачать модель бизнес-плана в формате Excel (версия 2.02)

ВНИМАНИЕ! В универсальный шаблон бизнес-плана внесены дополнения! Просмотреть видео по данным дополнениям можно в конце страницы.

Содержание бизнес-плана

1. Резюме проекта

1.1. Основные характеристики проекта

1.2. Наши преимущества

1.3. Необходимость в финансировании

1.4. Основные показатели проекта

2. Общий прогноз

3. Описание продукции

3.1. Описание продуктов

3.2. Позиционирование продуктов на рынке

4. Обзор рынка

4.1. Общее состояние рынка

4.2. Тенденции в развитии рынка

4.3. Сегменты рынка

4.5. Характеристика потенциальных потребителей

5. Конкуренция

5.1. Основные участники рынка

5.2. Основные методы конкуренции в отрасли

5.3. Изменения на рынке

5.4. Описание ведущих конкурентов

5.5. Основные конкурентные преимущества и недостатки

5.6. Сравнительный анализ нашей продукции с конкурентами

6. План маркетинга

6.3. Продвижение продукции на рынке

7. План производства

7.1. Описание производственного процесса

7.2. Производственное оборудование

8. Управление персоналом

8.1. Основной персонал

8.2. Организационная структура

8.3. Поиск и подбор сотрудников

8.4. Обслуживание клиентов

9. Финансовый план

10. Риски

Приложения:

1. Формирование цены на продукцию

2. График реализации проекта

Диаграммы:

1. Уровень цены единицы продукции

Таблицы:

1. Сравнительный анализ продукции с конкурентами

2. Производственное оборудование

3. Основной персонал компании

4. Расчет показателей проекта без учета индекса инфляции

5. Расчет показателей проекта с учетом индекса инфляции

6. Основные виды возможных рисков для компании

Видеоурок по дополнениям, которые были выполнены в шаблоне бизнес-плана

Скачать модель бизнес-плана в формате Excel (версия 2.02)

Если материал поста был для Вас полезен, поделитесь ссылкой на него в своей соцсети:

Другие материалы по теме «Разработка бизнес-плана»

Вам также может быть интересно:

Как запланировать производство продукции на предприятии

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

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

Система планирования работ как часть бизнес-плана

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

Порядок планирования на производстве

На действующем промышленном предприятии составляется производственная программа на основании заключённых договоров с заказчиками продукции, согласно плану госзакупок или по среднегодовым показателям выпуска продукции. А также учитываются данные анализа потребностей рынка и спроса на товар. В разработке планирования объема производства продукции участвуют следующие отделы:

• Производственная служба и отдел сбыта определяют номенклатуру, количество и сроки реализации. Выполняют планирование производства и реализации продукции.
• Задача бюджетного отдела — определить стоимость потребных материалов, трудовых затрат, энергоресурсов, топлива, а также затрат по накладным расходам и общеадминистративных расходах. Установить цену на новое изделие.
• Отделу кадров следует рассчитать количество станко-часов для выполнения всех операций и проанализировать соответствие трудовых ресурсов рассчитываемому объему выпуска продукции.
• Технический отдел анализирует соответствие основных средств, систем и устройств предприятия предполагаемому выполнению всех операций по изготовлению изделий, работ, услуг, устанавливает нормы затрат.
• Служба логистики подтверждает обеспечение и закупку товаров и материалов, запчастей и озвучивает цену на них.

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

Основные правила и виды планирования

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

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

Виды планирования классифицируют в зависимости от сроков и целей программы.

ЭКОНОМИЧЕСКИЕ
РАСЧЕТЫ И АНАЛИЗ ФИНАНСОВОГО СОСТОЯНИЯ

ПРЕДПРИЯТИЯ

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

Тема: ОРГАНИЗАЦИЯ РАСЧЕТОВ В ТАБЛИЧНОМ ПРОЦЕССОРЕ MS EXCEL

Цель. Изучение информационной технологии
исполь­зования встроенных вычислительных функций Excel для финан­сового анализа.

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

Исходные данные представлены
на рис. 1, результаты рабо­ты — на рис. 4.

Порядок работы

1. Запустите редактор
электронных таблиц Microsoft Excel и
создайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Все программы/Microsoft Excel).

Рис.1.1.
Исходная таблица

Введите
заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.

Для
оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления.
Для этого выпол­ните команду Формат/Стиль, в открывшемся окне Стиль набе­рите
имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте
Переносить по словам и выберите горизонтальное и
вертикальное выравнива­ние — по центру (рис. 1.2), на вкладке Число укажите
формат — Текстовой. После этого нажмите кнопку Добавить.

На третьей строке введите названия колонок таблицы —
«Дни недели»,
«Доход», «Расход», «Финансовый результат», далее за­полните таблицу исходными
данными согласно рисунка 1.1.

Краткая
справка. Для
ввода дней недели наберите «Поне­дельник» и произведите автокопирование до
«Воскресенья» (по­нятие левой кнопкой мыши за маркер автозаполнения в правом
нижнем углу ячейки).

5. Произведите расчеты в графе
«Финансовый результат» по
следующей формуле:

Финансовый результат = Доход — Расход.

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

Рис. 1.2. Форматирование ячеек — задание переноса по словам

6. Для ячеек с результатом расчетов
задайте формат «Денеж­ный» с выделением отрицательных чисел красным цветом (рис.1.3)
(Формат/Ячейки/вкладка Число/формат Денежный/ отрицательные числа
— красные. Число десятичных знаков задайте равное двум).

Обратите внимание,
как изменился цвет отрицательных значе­ний финансового результата на красный.

7. Рассчитайте средние значения
Дохода и Расхода, пользуясь
мастером функций (кнопка fx). Функция СРЗНАЧ находится в
разделе «Статистические». Для расчета функции среднего значения
дохода установите курсор в соответствующей ячейке для расчета
среднего значения (В11), запустите мастер функций и выберите
функцию СРЗНАЧ (Вставка/Функция/ категория — Статисти­ческие/
СРЗНАЧ). В качестве первого числа выделите группу ячеек с данными для
расчета среднего значения — В4:В10.

Аналогично рассчитайте среднее значение
расхода.

В
ячейке D13 выполните расчет общего финансового
резуль­тата (сумма по столбцу «Финансовый результат»). Для выполне­ния
автосуммы удобно пользоваться кнопкой Автосуммирования (∑) на панели
инструментов или функцией СУММ. В качестве первого числа выделите группу ячеек
с данными для расчета сум­мы — D4:D10.

Проведите
форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели
инструментов Объединить и поместить в центре или ко­мандой меню Формат/Ячейки/вкладка — Выравнивание/отображение
– Объединение ячеек.

Рис. 1.3. Задание формата отрицательных чисел красным цветом

Рис. 4. Таблица расчета финансового результата (Задание 1)

    Задайте
начертание шрифта — полужир­ное, цвет — по вашему усмотрению.

Конечный вид таблицы приведен на рис.1. 4.

10.        Постройте диаграмму
(линейчатого типа) изменения финансовых результатов по дням недели с помощью
мастера диаг­рамм.

Для этого выделите
интервал ячеек с данными финансового результата D4:D10 и выберите команду Вставка/Диаграмма. На первом
шаге работы с мастером диаграмм выберите тип диаг­раммы — линейчатая; на втором
шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек
с днями недели — А4:А10 (рис. 1.5).

Далее введите
название диаграммы и подписи осей. Дальней­шие шаги построения диаграммы
осуществляются по подсказкам мастера Диаграмм.

11.        Произведите фильтрацию
значений дохода, превышающих
4000 р.

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

Для установления
режима фильтра установите курсор внутри созданной таблицы и воспользуйтесь
командой Данные/Фильтр/ Автофильтр. В заголовках полей появятся стрелки
выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет
наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся
значений этого поля. Выберите команду для фильтрации — Условие.

В
открывшемся окне Пользовательский автофильтр задайте ус­ловие «Больше 4000» (рис. 1.6).

Рис. 1.5.
Задание Подписи оси Х при построении диаграммы

Рис.1.6. Пользовательский автофильтр

Произойдет отбор данных по заданному
условию.

Проследите, как
изменились вид таблицы и построенная диаг­рамма.

12. Сохраните созданную электронную книгу
в своей папке.

Тема: СВЯЗИ МЕЖДУ ФАЙЛАМИ И КОНСОЛИДАЦИЯ ДАННЫХ В MS EXCEL

Цель. Изучение технологии связей между
файлами и консолидации данных в MS Excel.

Задание 2. Задание связей между файлами.

Порядок работы

Запустите
редактор электронных таблиц Microsoft Excel и
создайте новую электронную книгу.

Создайте
таблицу «Отчет о продажах 1 квартал» по образцу (рис. 2.1). Введите исходные
данные (Доходы и Расходы):

Доходы =
234,58 р.;

Расходы =
75,33 р.

и проведите расчет
Прибыли: Прибыль = Доходы — Расходы. Со­храните файл под именем «1 квартал».

3. Создайте таблицу «Отчет о
продажах 2 квартал» по образцу (см. рис. 2.1) в виде нового файла. Для этого
создайте новый до­кумент (Файл/Создать) и скопируйте таблицу отчета о
продаже за первый квартал, после чего исправьте заголовок таблицы и измените
исходные данные:

Доходы =
452,6 р.; Расходы = 125,8 р.

Обратите внимание,
как изменился расчет прибыли. Сохрани­те этот файл под именем «2 квартал».

4. Создайте таблицу
«Отчет о продажах за полугодие» по образ­цу (см. рис. 2.1) в виде нового файла.
Для этого создайте новый документ (Файл/Создать) и скопируйте таблицу
отчета о прода­же за первый квартал, после чего подправьте заголовок таблицы и
в колонке «В» удалите все значения исходных данных и резуль­таты расчетов.
Сохраните файл под именем «Полугодие».

Рис. 2.1. Задание связей между файлами

5. Для расчета
полугодовых итогов свяжите формулами файлы «1 квартал» и «2 квартал».

Краткая
справка. Для
связи формулами файлов Excel вы­полните следующие действия:
откройте все три файла; начните ввод формулы в файле-клиенте (в файле
«Полугодие» введите формулу для расчета «Доход за полугодие»).

Формула для расчета:

Доход за полугодие = Доход за 1 квартал +
Доход за 2 квартал.

Чтобы вставить в
формулу адрес ячейки или диапазона ячеек из другого файла (файла-источника),
щелкните мышью по этим ячейкам, при этом расположите окна файлов на экране так,
что­бы они не перекрывали друг друга.

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

В
ячейке ВЗ файла «Полугодие» формула для расчета полугодо­вого дохода имеет вид:

= ‘[1 квартал.хls]Лист1′!$В$3 + ‘[2 квартал.хls]Лист1′!$В$3.

Аналогично
рассчитайте полугодовые значения Расходов и Прибыли, используя данные файлов «1
квартал» и «2 квартал». Результаты работы представлены на рис. 2.1. Сохраните
текущие результаты расчетов.

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

Задание 2.1. Обновление связей между файлами.

Порядок работы

Закройте файл
«Полугодие» предыдущего задания.

Измените
значение «Доходы» в файлах первого и второго квартала, увеличив значения на 100
р.:

Доходы 1
квартала = 334,58 р.;

Доходы 2
квартала = 552,6 р.

Сохраните изменения и закройте файлы.

Откройте
файл «Полугодие». Одновременно с открытием
файла появится окно с предложением обновить связи. Для обновления связей
нажмите кнопку Да. Проследите, как изменились данные файла «Полугодие»
(величина «Доходы» должна увели­читься на 200 р. и принять значение 887,18 р.).

Рис. 2.2.
Ручное обновление связей между файлами

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

4. Изучим процесс ручного обновления
связи. Сохраните файл «Полугодие» и закройте его.

5.  Вновь
откройте файлы первого и второго кварталов и измените исходные данные «Доходы», увеличив еще раз
значения на 100 р.:

Доходы 1
квартала = 434,58 р.;

Доходы 2
квартала = 652,6 р.

Сохраните изменения и закройте файлы.

6. Откройте файл «Полугодие».
Одновременно с открытием файла появится окно с предложением обновить связи,
нажмите кнопку Нет. Для ручного обновления связи в меню Правка выбе­рите
команду Связи, появится окно (рис. 2.2), в котором перечислены все
файлы, данные из которых используются в активном
файле «Полугодие».

Расположите его так,
чтобы были видны данные файла «Полу­годие», выберите файл «1 квартал», нажмите
кнопку Обновить и проследите, как изменились данные файла «Полугодие».
Анало­гично выберите файл «2 квартал» и нажмите кнопку Обновить. Проследите,
как вновь изменились данные файла «Полугодие».

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

Задание 2.2. Консолидация данных для подведения
итогов по таблицам данных сходной структуры.

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

Рис. 2.3. Консолидация данных

Порядок работы

1. Откройте все три файла Задания 2 и
в файле «Полугодие» в колонке «В» удалите все численные значения данных. Установи­те
курсор в ячейку ВЗ.

2.  Выполните
команду Данные/’Консолидация (рис. 2.3). В появив­шемся окне Консолидация выберите
функцию — «Сумма».

В строке «Ссылка»
сначала выделите в файле «1 квартал» диа­пазон ячеек ВЗ:В5 и нажмите кнопку Добавить,
затем выделите в файле «2 квартал» диапазон ячеек ВЗ:В5 и опять нажмите
кнопку Добавить (см. рис. 2.3). В списке диапазонов будут
находиться две области данных за первый и второй кварталы для
консолидации. Далее нажмите кнопку ОК, произойдет консолидированное сум­мирование
данных за первой и второй кварталы.

Вид таблиц после консолидации данных
приведен на рис. 2.4.

Рис. 2.4. Таблица «Полугодие» после консолидированного суммирования

Задание 2.3. Консолидация данных для подведения итогов
по таблицам неоднородной структуры.

Порядок работы

1.   
Запустите
редактор электронных таблиц Microsoft Excel и
создайте новую электронную книгу. Наберите отчет по отделам за третий квартал
по образцу (рис. 2.5). Произведите расчеты и со­храните файл с именем «3
квартал».

2.  
Создайте новую
электронную книгу. Наберите отчет по отде­лам за четвертый квартал по образцу
(рис. 2.6). Произведите рас­четы и сохраните файл с именем «4 квартал».

3.  
Создайте новую
электронную книгу. Наберите название таб­лицы «Полугодовой отчет о продажах по
отделам». Установите курсор в ячейку A3 и проведите консолидацию за третий и
чет­вертый кварталы по заголовкам таблиц. Для этого выполните ко­манду Данные/Консолидация. В появившемся
окне Консолидация данных сделайте ссылки на диапазон ячеек
АЗ:Е6 файла «3 квар­тал» и A3:D6
файла «4 квартал» (рис. 2.7). Обратите внимание, что интервал ячеек включает в
себя имена столбцов и строк таб­лицы.

Рис. 2.5. Исходные данные для третьего квартала Задания 2.2

Рис. 2.6. Исходные данные для четвертого квартала Задания 2.2

Рис. 2.7. Консолидация неоднородных таблиц

В окне Консолидация
активизируйте опции (поставьте галочку): подписи верхней строки; значения
левого столбца; создавать связи с исходными данными (результаты будут не
константами, а формулами).

Рис. 2.8. Результаты
консолидации неоднородных таблиц

После нажатия кнопки ОК
произойдет консолидация данных (рис. 2.8). Сохраните все файлы в папке
вашей группы.

Обратите внимание,
что все данные корректно сгруппированы по их заголовкам (по отделам). В левой
части экрана появятся так называемые кнопки управления контуром (иерархической
струк­турой). С их помощью можно скрывать или показывать исходные данные.

КОНТРОЛЬНЫЕ
ВОПРОСЫ

1.  Что такое автозаполнение?

2.  Какие способы объединения нескольких
исходных таблиц в одну вам известны?

3.  Что такое консолидация данных?

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

Тема:
ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ В ТАБЛИЧНОМ
ПРОЦЕССОРЕ MS EXCEL

Цель. Изучение информационной технологии
приме­нения относительной и абсолютной адресации для финансовых расчетов.

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

Исходные данные
представлены на рис. 1.1, результаты рабо­ты — на рис. 1.2 и 1.3.

Порядок работы

1. Запустите редактор электронных
таблиц Microsoft Excel и
создайте новую электронную книгу.

2.   
Создайте
таблицу расчета заработной платы по образцу (см. рис. 2.1).

Введите исходные
данные — Табельный номер, ФИО и Ок­лад, % Премии = 27 %, % Удержания = 13 %.

Выделите отдельные
ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. 1.1. Исходные данные для Задания 1

3. Произведите расчеты во всех
столбцах таблицы.

При расчете Премии
используется формула Премия = Оклад * % Премии. В ячейке D5 наберите формулу =$D$4xC5 (ячейка D4 используется в виде абсолютной
адресации). Скопируйте на­бранную формулу вниз по столбцу автозаполнением.

Краткая справка. Для удобства работы и
формирования навыков работы с абсолютным видом адресации рекомендуется при
оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной
таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с
константой будет вам напоминани­ем, что следует установить абсолютную адресацию
(набором сим­вола $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего
начислено = Оклад + Премия.

При расчете Удержания используется
формула:

Удержания = Всего начислено х % Удержаний.

Для этого в ячейке F5 наберите формулу: =$F$4xE5. Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено — Удержания.

4.  
Рассчитайте
итоги по столбцам, а также максимальный, ми­нимальный и средний доходы по
данным колонки «К выдаче» (Вставка/Функция/категория — Статистические
функции).

5.  
Переименуйте
ярлычок Листа 1, присвоив ему имя «Зарпла­та октябрь». Для этого дважды
щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переиме­новать
контекстного меню ярлычка, вызываемого правой кноп­кой мыши. Результаты
работы представлены на рис. 2.2.

Краткая справка. Каждая рабочая книга Excel может со­держать до 255 рабочих листов.
Это позволяет, используя несколь­ко листов, создавать понятные и четко структурированные
доку­менты, вместо того чтобы хранить большие последовательные наборы данных на
одном листе.

6. Скопируйте содержимое листа
«Зарплата октябрь» на новый
лист (Правка/Переместить/Скопировать
лист). Можно воспользоваться командой Переместить/Скопировать контекстного
меню ярлычка. Не забудьте для копирования поставить галочку в окне Создавать
копию.

Краткая
справка.
Перемещать и копировать листы мож­но, перетаскивая их корешки (для копирования
удерживайте на­жатой клавишу [Ctrl]).

Рис. 1.2.
Итоговый вид таблицы расчета заработной платы за октябрь

7.  
Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в
названии таблицы. Измените значе­ние Премии на 32%. Убедитесь, что программа
произвела, пере­счет формул.

8.  
Между
колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/
Столбец) и рассчитайте зна­чение доплаты по формуле:

Доплата =
Оклад х % Доплаты.

Значение доплаты примите равным 5 %.

9. Измените формулу для расчета
значений колонки «Всего на­
числено»:

Всего начислено = Оклад + Премия +
Доплата.

10.Проведите условное форматирование значений колон­ки «К вьщаче». Установите формат вывода значений
между 7000 и 10000 — зеленым цветом шрифта, меньше 7000 — красным, боль­ше
или равно 10 000 — синим цветом шрифта (Формат/Условное форматирование) (рис.
2.3).

11. Проведите сортировку по фамилиям в
алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5 по 18 строки
без итогов — выберите меню Данные/Сортировка, сортировать по — Столбец
В).

12. Поставьте к ячейке D3 комментарии «Премия пропорцио­нальна окладу» (Вставка/Примечание);
при этом в правом верх­нем углу ячейки появится красная точка, которая
свидетельствует о наличии примечания. Конечный вид таблицы расчета заработ­ной
платы за ноябрь приведен на рис. 2.4.

Рис. 1.3. Условное форматирование данных

13.        Защитите лист «Зарплата
ноябрь» от изменений (Сервис/
Защита/Защитить лист). Задайте пароль на лист, сделайте под­тверждение
пароля.

Убедитесь, что лист
защищен и удаление данных невозможно. Снимите защиту листа (Сервис/Защита/Снять
защиту листа).

14.        Сохраните созданную
электронную книгу под именем «Зар­плата» в своей папке.

Рис. 1.4. Конечный вид таблицы расчета зарплаты за ноябрь

Дополнительные задания

Задание 1.2. Сделать примечания к двум-трем
ячейкам.

Задание 1.3. Выполнить условное форматирование оклада
и премии за ноябрь месяц: до 2000 — желтым цветом заливки; от 2000 до 10 000 —
зеленым цветом шрифта; свыше 10 000 — малиновым цветом заливки, белым цветом
шрифта.

Задание 1.4. Защитить лист зарплаты за октябрь
от изменений.

Проверьте защиту. Убедитесь в
неизменяемости данных. Сни­мите защиту со всех листов электронной книги
«Зарплата».

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

Тема: СВЯЗАННЫЕ
ТАБЛИЦЫ, РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL

Цель. Связывание листов электронной
книги. Расчет промежуточных итогов. Структурированные таблицы.

Задание 2. Рассчитать зарплату за декабрь и
построить диаг­рамму. Создать итоговую таблицу ведомости квартального начис­ления
заработной платы, провести расчет промежуточных итогов по подразделениям.

Порядок работы

1.  
Запустите
редактор электронных таблиц Microsoft Excel и
откройте созданный в практической работе 2 файл «Зарплата».

2.  Скопируйте содержимое листа
«Зарплата ноябрь» на новый лист электронной
книги (Правка/Переместить/Скопировать лист).

3.  Присвойте
скопированному листу название «Зарплата декабрь». Исправьте название месяца в названии
таблицы.

4.  Измените значения Премии на 46 %,
Доплаты — на 8 %. Убе­дитесь, что программа произвела пересчет формул (рис. 2.1).

5. По данным таблицы «Зарплата
декабрь» постройте гисто­грамму дохода сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите форматирование диаграммы.
Конечный вид гистограммы приведен на рис. 2.2.

Рис. 2.1. Ведомость зарплаты за декабрь

6.  
Перед расчетом
итоговых данных за квартал проведите сор­тировку по фамилиям в алфавитном порядке
(по возрастанию) в таблице расчета зарплаты за октябрь.

7.  
Скопируйте
содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/
Скопировать лист).

Рис. 2.2. Гистограмма зарплаты за декабрь

8.  
Присвойте
скопированному листу название «Итоги за квар­тал». Измените название таблицы на
«Ведомость начисления зара­ботной платы за четвертый квартал».

9.  
Отредактируйте
лист «Итоги за квартал» согласно образцу на рис. 2.3. Для этого удалите в
основной таблице колонки «Оклад» и «Премия», а также строку 4 с численными
значениями: % Пре­мии и % Удержания и строку 19 «Всего». Удалите также строки с
расчетом максимального, минимального и среднего доходов под основной таблицей.
Вставьте пустую строку 3.

10. Вставьте новый столбец
«Подразделение» {Вставка/Стол­бец) между столбцами «Фамилия» и «Всего
начислено». Заполни­те столбец «Подразделение» данными по образцу (рис. 2.3).

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

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

В
ячейке D5 для
расчета квартальных начислений «Всего начис­лено» формула имеет вид:

= Зарплата декабрь!Р5 + Зарплата ноябрь!Р5
+ + Зарплата октябрь! Е5.

Аналогично
произведите квартальный расчет столбца «Удер­жания» и «К выдаче».

Рис. 2.3. Таблица для
расчета итоговой квартальной заработной платы

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

12. В силу
однородности расчетных таблиц зарплаты по меся­цам для расчета квартальных
значений столбцов «Удержания» и «К выдаче»
достаточно скопировать формулу из ячейки D5 в ячейки Е5 и F5.

Рис. 2.4. Расчет
квартального начисления заработной платы связыванием листов электронной книги

Рис. 2.5. Вид таблицы
начисления квартальной заработной платы после сортировки по подразделениям

Для расчета
квартального начис­ления заработной платы для всех сотрудников скопируйте
формулы вниз по столбцам D, Е и F. Ваша электронная таблица примет вид, как на рис. 2.4.

Рис. 2.6. Окно задания параметров расчета промежуточных итогов

13.   
Для расчета
промежуточных итогов проведите сортировку
по подразделениям, а внутри подразделе­ний — по фамилиям. Таблица при­мет
вид, как на рис. 2.5.

14.   
Рассчитайте
промежуточные итоги по подразделениям, используя формулу суммирования. Для
этого выделите всю таблицу и выполните команду Данные/Итоги (рис. 2.6).
Задайте параметры подсчета проме­жуточных итогов:

при каждом изменении — в Подразделение;
операция — Сумма;

добавить итоги: Всего начислено,
Удержания, К выдаче. Отметьте галочкой операции «Заменить текущие итоги» и «Ито­ги
под данными».

Примерный вид итоговой таблицы представлен
на рис. 2.7.

Рис. 2.7. Итоговый вид таблицы расчета квартальных итогов по зарплате

15.        Изучите полученную
структуру и формулы подведения промежуточных итогов, устанавливая курсор на
разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до
разных уровней (кнопками «+» и «-»).

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

16.        Сохраните файл «Зарплата» с произведенными изменениями.

Тема: ПОДБОР
ПАРАМЕТРА, ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА

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

Задание 3. Используя режим подбора параметра,
определите штатное расписания фирмы.

Исходные данные приведены на рис. 3.1.

Краткая  справка. Известно, что в штате фирмы
состоят:

6 курьеров;

8 младших менеджеров;

10 менеджеров;

3 заведующих отделами;

1 главный бухгалтер;

1 программист;

1 системный аналитик;

1 генеральный директор фирмы.

Рис. 3.1.
Исходные данные для Задания 3

Общий месячный фонд
зарплаты составляет 100 000 р. Необ­ходимо определить, какими должны быть
оклады сотрудников фирмы.

Каждый оклад является
линейной функцией от оклада курь­ера, а именно:

Зарплата = А*х + В„

где х — оклад
курьера; А-, и Д- — коэффициенты, показывающие: А-, — во сколько
раз превышается значение х; Д — на сколько превышается значение х.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel.

2.  
Создайте
таблицу штатного расписания фирмы по приведен­ному образцу (см. рис. 3.1).
Введите исходные данные в рабочий лист электронной книги.

3.  
Выделите
отдельную ячейку D3 для зарплаты курьера (пере­менная
«х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

4.  
В столбце D введите формулу для расчета заработной платы по каждой
должности. Например, для ячейки D6
формула расчета имеет вид: = B6*$D$3
+ С6 (ячейка D3 задана виде абсолютной
адресации). Далее скопируйте формулу из ячейки D6
вниз по стол­бцу азтокопированием в интервале ячеек D6:D13.

В столбце F задайте формулу расчета заработной платы всех работающих в
данной должности. Например, для ячейки F6 фор­мула
расчета имеет вид: = D6*E6.
Далее скопируйте формулу из ячейки F6 вниз
по столбцу автокопированием в интервале ячеек F6:F13.

В ячейке F14 вычислите суммарный фонд заработной платы фирмы.

5. Произведите подбор зарплат
сотрудников фирмы для сум­
марной заработной платы в сумме 100 000 р. Для этого в меню
Сервис активизируйте команду Подбор параметра.

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

В поле Значение наберите искомый
результат 100 000.

В
поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и
щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников
по заданному условию при фонде зарплаты, равном 100 000 р.

6. Сохраните
созданную электронную книгу под именем «Штат­
ное расписание» в своей папке.

Задание 4. Используя режим подбора параметра
и таблицу расчета штатного расписания (см. Задание 3), определите вели­чину
заработной платы сотрудников фирмы для ряда заданных значений фонда заработной
платы.

Порядок работы

1. Выберите коэффициенты уравнений
для расчета согласно табл. 3.1 (один из пяти вариантов расчетов).

2.    
Методом
подбора параметра последовательно определите зарплаты сотрудников фирмы для
различных значений фонда за­работной платы:
100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р.
Результаты подбора значений зарплат скопируйте в табл. 3.2 в виде специальной
вставки.

Краткая
справка. Для
копирования результатов расчетов в виде значений необходимо выделить копируемые
данные, про­извести запись в буфер памяти (Правка/Копировать), установить
курсор в первую ячейку таблицы ответов соответствующего стол­бца, задать режим
специальной вставки (Правка/Специальная вставка), отметив в качестве
объекта вставки — значения (Прав­ка/Специальная вставка/вставитъ — Значения)
(рис. 3.2).

Специальная вставка
данных в виде значений позволяет копи­ровать данные, полученные в результате
расчетов, без дальней­шей зависимости их от пересчета формул.

Таблица 3.1

Выбор исходных данных

Таблица 3.2

Результаты
подбора значений заработной платы

Рис. 3.2.
Специальная вставка значений данных

КОНТРОЛЬНЫЕ
ВОПРОСЫ

1. 
Что называется абсолютной
адресаций?

2. 
Что называется
относительной адресацией?

3. 
Рассчитайте заработную
плату сотрудников при фонде зарплаты 600000?

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

Тема:
ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL

Цель. Изучение технологии экономических
расчетов и определение
окупаемости средствами электронных таблиц.

Задание 1.1. Оценка рентабельности рекламной
компании-фирмы.

Порядок работы

Запустите
редактор электронных таблиц Microsoft Excel
создайте новую электронную книгу.

Создайте
таблицу оценки рекламной компании по образцу (рис. 1.1). Введите исходные данные:
Месяц, Расходы на рекламу А(0), р., Сумма покрытия В(0) р., Рыночная процентная
ставка 0 = 13,7%.

Выделите для рыночной
процентной ставки, являющейся кон­стантой, отдельную ячейку — СЗ, и дайте этой
ячейке имя «Став­ка».

Краткая
справка. Для
присваивания имени ячейке или группе ячеек выполните следующие действия:

• выделите ячейку
(группу ячеек или несмежный диапазон которой необходимо присвоить имя,

щелкните поле Имя, которое
расположено слева в строке формул;

введите имя ячейки.

• нажмите клавишу [Enter].
Помните, что по умолчанию имена являются абсолютными ссылками.

3. Произведите расчеты во всех столбцах
таблицы.

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

Рис. 1.1. Исходные данные для Задания 1.1

Формула для расчета:

A(n)
= А (0) *(1 +j/12)^(1-n),

в ячейке С6 наберите :

= В6 * (1+
ставка/12) ^ (1 — $А6).

Примечание. Ячейка А6 в формуле имеет
комбинирован­ную адресацию: абсолютную адресацию
по столбцу и относитель­ную по строке и имеет вид — $A6.

При расчете расходов на
рекламу нарастающим итогом надо учесть, что первый платеж равен
значению текущей стоимости расходов на рекламу, значит, в ячейку D6 введем значение = С6, но в ячейке D7 формула примет вид: = D6 + С7.
Далее формулу ячейки D7 скопируйте в ячейки D8: D17.

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

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

Для расчета текущей
стоимости покрытия скопируйте формулу из ячейки С6 в ячейку F6. В ячейке F6
должна быть формула

= Е6 * (1
+ ставка/12) ^ (1 — $А6).

Далее с помощью
маркера автозаполнения скопируйте формулу в ячейки F7:F17.

Сумма покрытия
нарастающим итогом рассчитывается аналогично расходам на  рекламу нарастающим
итогом, поэтому в ячейку G6 поместим содержимое ячейки F6 (= F6), а в G7 вве­дем формулу:

= G6 + F7

Рис. 1.2. Рассчитанная таблица оценки рекламной компании

Далее формулу из
ячейки G7 скопируем в ячейки G8:G17. В последних трех ячейках столбца будет представлено одно и то же значение,
ведь результаты рекламной компании за последние три месяца на сбыте продукции
уже не сказывались.

Сравнив значения в
столбцах D и G,
уже можно сделать вывод о рентабельности рекламной компании, однако расчет
денежных потоков в течение года (колонка Н), вычисляемый как разница колонок G и D, показывает, в каком месяце была
пройдена то­чка окупаемости инвестиций. В ячейке Н6 введите формулу:

= G6 — D6,

и скопируйте ее вниз на весь столбец.

Проведите условное
форматирование результатов расчета ко­лонки Н: отрицательных чисел — синим
курсивом, положитель­ных чисел — красным цветом шрифта. По результатам
условного форматирования видно, что точка
окупаемости приходится на июль месяц.

4. В ячейке Е19
произведите расчет количества месяцев, в кото­рых
имеется сумма покрытия (используйте функцию «Счет» (Встав­ка/Функция/Статистические),
указав в качестве диапазона «Зна­чение 1» интервал ячеек Е7: Е14). После
расчета формула в ячейке Е19 будет иметь вид = СЧЕТ(Е7: Е14).

Рис. 1.3. Расчет функции СЧЕТЕСЛИ

5. В
ячейке Е20 произведите расчет количества месяцев, в кото­рых сумма покрытия больше 100 000 р. (используйте
функцию СЧЕТЕСЛИ, указав в качестве
диапазона «Значение» интервал ячеек Е7:Е14,
а в качестве условия > 100 000) (рис. 1.3). После расчета формула в ячейке
Е20 будет иметь вид = СЧЕТЕСЛИ(Е7 :Е14).

6. Постройте
графики по результатам расчетов (рис. 1.4):
«Сальдо дисконтированных денежных потоков нарастающим итогом» — по результатом расчетов колонки Н;

«Реклама:
доходы и расходы» — по данным колонок D и G (диапазоны D5: D17 и G5: G17 выделяйте,
удерживая нажатой кла­вишу
[Ctrl]).

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

7. Сохраните
файл.

Рис 1.4. Графики для определения точки окупаемости инвестиций

Тема: НАКОПЛЕНИЕ СРЕДСТВ И ИНВЕСТИРОВАНИЕ ПРОЕКТОВ В MS EXCEL

Цель. Изучение технологии экономических расчетов в  табличном процессоре.

Задание 2.1. Фирма поместила в коммерческий
банк 45000 р. на шесть лет под 10,5 % годовых. Какая сумма окажется на счете, если
проценты начисляются ежегодно? Рассчитайте, какую сумму надо поместить в банк
на тех же условиях, чтобы через шесть лет накопить 250 000 р.

Порядок работы

Запустите
редактор электронных таблиц Microsoft Excel и
создайте новую электронную книгу.

Создайте
таблицу констант и таблицу для расчета наращен­ной суммы вклада по образцу
(рис. 2.1).

Произведите расчеты А(n) двумя способами:

с помощью формулы А(n) = А(0) * (1 + j)ⁿ (в ячейку D10
ввести формулу = $В$3 * (1 + $В$4) ^ А9 или использовать фун­кцию СТЕПЕНЬ);

с помощью функции БС (рис. 2.2).

Краткая справка. Функция БС возвращает будущее
значе­ние вклада на основе периодических постоянных платежей и по­стоянной
процентной ставки.

Рис. 2.1. Исходные данные для Задания 2.1

Рис. 2.2. Задание параметров функции БЗ

Синтаксис функции БС:
БС (Ставка;Кпер;Плт;Пс;Тип), где став­ка — это процентная ставка за
период; кпер — это общее число периодов выплат годовой ренты; плата —
это выплата, произво­димая в каждый период (это значение не может меняться
в тече­ние всего периода выплат). Обычно плата состоит из основного платежа и
платежа по процентам, но не включает в себя других налогов и сборов. Если
аргумент пропущен, должно быть указано значение аргумента Пс. Пс — это
текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.
Если аргу­мент Пс опущен, то он полагается равным 0. В этом случае
должно быть указано значение аргумента плата.

Тип — это число 0 или 1, обозначающее,
когда должна произ­водиться выплата. Если аргумент тип опущен, то он
полагается равным 0 (0 — платеж в конце периода, 1 — платеж в начале
периода).

Рис. 2.3. Результаты расчета накопления финансовых средств фирмы

Все аргументы,
означающие деньги, которые платятся (напри­мер, депозитные вклады),
представляются отрицательными чис­лами. Деньги, которые получены (например,
дивиденды), пред­ставляются положительными числами.

Для ячейки СЮ задание
параметров расчета функции БЗ имеет вид, как на рис. 2.2. Конечный вид
расчетной таблицы приведен на рис. 2.3.

4.
Используя режим Подбор параметра (Сервис/Подбор пара­метра), рассчитайте, какую сумму надо поместить в
банк на тех же условиях, чтобы через шесть лет накопить 250 000 р. В резуль­тате
подбора выясняется, что для накопления суммы в 250 000 р. первоначальная сумма
для накопления должна быть равной 137 330,29 р.

Задание 2.2. Сравнить доходность размещения
средств пред­приятия, положенных в банк на один год, если проценты начис­ляются
m раз в год исходя из процентной ставки j = 9,5 % годовых (рис. 2.4); по результатам расчета
построить график изменения доходности инвестиционной операции от количества раз
начис­ления процентов в году (капитализации).

Рис. 2.4. Исходные данные для Задания 2.2

Выясните, при каком
значении j доходность (при капитализа­ции m = 12) составит 15 %.

Краткая справка. Формула для расчета доходности:

Доходность
= (1 + j/m)^m— 1.

Примечание. Установите формат значений
доходности — процентный.

Для проверки
правильности ваших расчетов сравните получен­ный результат с правильным
ответом: для m = 12 доходность = = 9,92%.

Для выяснения, при
каком значении j доходность (при капита­лизации m = 12) составит 15 %, произведите обратный расчет,
используя режим Подбор параметра.

Правильный ответ: доходность составит 15 %
при j = 14,08 %.

Тема: ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ
ТАБЛИЦ ДЛЯ ФИНАНСОВЫХ И ЭКОНОМИЧЕСКИХ РАСЧЕТОВ

Цель. Закрепление и проверка навыков экономических и финансовых расчетов в
электронных таблицах.

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

Задание 3.1. Создать таблицу расчета прибыли
фирмы, произ­вести расчеты суммарных доходов, расходов (прямых и прочих) и
прибыли; произведите пересчет прибыли в условные единицы по курсу (рис. 3.1).

Выясните, при каком
значении зарплаты прибыль будет равна 500000 р. (используйте режим   Подбор
параметра).

Рис. 3.1.
Исходные данные для Задания 3.1

Краткая справка. Формулы для расчета:

Расходы: всего =
Прямые расходы + Прочие расходы;

Прибыль = Доходы:
всего — Расходы: всего;

Прибыль (у.е.) =
Прибыль/Курс 1у.е..

Задание 3.2. Фирма хочет накопить деньги для
реализации нового проекта. С этой целью в течение пяти лет она кладет на счет
ежегодно по 1250 $ в конце каждого года под 8 % годовых (рис. 3.2). Определить
сколько будет на счете фирмы к концу пя­того года (в MS Excel)? Построить диаграмму по
результатам расчетов. Выясните, какую сумму надо ежегодно класть на счет, что­бы
к концу пятого года накопить 10000 $.

Рис. 3.2. Исходные данные для Задания 3.2

Краткая
справка.
Формула для расчета:

Сумма на счете = D* ((1 +j) ^n—
l)/j.
Сравните полученный результат с правильным ответом: для n = 5 сумма на счете = 7 333,25 $.

Для расчета суммы
ежегодного вклада для накопления к концу пятого года 10000 $ используйте режим Подбор
параметра.

Задание 3.3. Фирма собирается инвестировать
проект в тече­ние трех лет. Имеются два варианта инвестирования:

1-й вариант: под 12 % годовых в начале
каждого года; 2-й вариант: под 14 % годовых в конце каждого года.
Предполагается ежегодно вносить по 500 000 р. Определить, в какую сумму
обойдется проект (рис. 5.4).

Рис. 3.3. Исходные данные для Задания 3.3

Порядок работы

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

2.   Сравните полученный результат с
правильным ответом: для n = 3 сумма проекта по 1-му
варианту — 1 889 664,00 р.; по 2-му варианту —  1 719 800,00 р.

Краткая справка. Формулы для расчета:

1-й вариант: Сумма проекта = D * ((1 + j) ^ n
— 1) * (1 + j)/j;

2-й вариант: Сумма проекта = D * ((1 +
j) ^ n — l)/j.

КОНТРОЛЬНЫЕ
ВОПРОСЫ

Проанализируйте
графики к заданию 1.1.

Какие
условия капитализации используются в задании 2.2?

Какой
вариант инвестирования лучше и почему (задание 3.3)?

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

Тема: РАСЧЕТ АКТИВОВ И ПАСИВОВ БАЛАНСА В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Цель. Изучение технологии расчета активов и пассивов баланса в электронных таблицах.

Задание 1.1.
Создать таблицу активов аналитического баланса.

Краткая
справка. В
структуре активов баланса выделяются две группы: оборотные активы (запасы,
сосредоточенные в сы­рье, незавершенном производстве, готовой продукции;
дебитор­ская задолженность и денежные средства предприятия) и внеобо­ротные
активы (основные средства; нематериальные активы; не­завершенное строительство
и долгосрочные инвестиции).

Порядок работы

Запустите
редактор электронных таблиц Microsoft Excel и
создайте новую электронную книгу.

На
Листе 1 создайте таблицу активов баланса по образцу (рис. 1.1).

Произведите расчеты в
таблице активов баланса в столбце В.

Краткая справка. Формулы для расчета в столбце В:

Внеоборотные активы —
(В3) = СУММ(В4:В7);

Запасы
и прочие оборотные активы — (В9) = СУММ(В10: В14);

Расчеты и денежные
средства — (В16) = СУММ(В17:В19);

Оборотные активы —
(В8) = В9 + В15 + В16.

Рис. 1.1. Таблица расчета активов баланса

Скопируйте набранные
формулы в столбец С.

Переименуйте
лист электронной книги, присвоив ему имя «Активы».

Сохраните
созданную электронную книгу с именем «Анализ баланса».

Задание 1.2.
Создать таблицу пассивов аналитического баланса.

Краткая справка. В структуре пассивов баланса
выделяют­ся группы: собственный капитал, долгосрочные обязательства и краткосрочные
обязательства.

Порядок работы

На
Листе 2 файла «Анализ баланса» создайте таблицу пасси­вов баланса по образцу
(рис. 1.2).

Произведите
расчеты в таблице пассивов баланса в столбце В.

Краткая справка. Формулы для расчета в столбце В:

Собственный капитал — (ВЗ) = СУММ(В4:В8);

Долгосрочные обязательства — (В9) =
СУММ(В10:В11);

Краткосрочная кредиторская задолженность —
(В 14) = = СУММ(В15:В20);

Краткосрочные
обязательства — (В12) = В13 + В14 + В21 + В22.

Скопируйте набранные
формулы в столбец С.

Переименуйте
Лист 2 электронной книги, присвоив ему имя «Пассивы».

Рис. 1.2. Таблица расчета пассивов баланса

5. Сохраните созданную электронную книгу.

Задание 1.3.
Создать таблицу агрегированного аналитического баланса.

Данные с листов
«Активы» и «Пассивы» позволяют рассчитать агрегированный аналитический баланс.

Порядок работы

На Листе 3 создайте
таблицу агрегированного аналитическо­го баланса по образцу (рис. 1.3).

Произведите расчеты в
таблице агрегированного аналитиче­ского баланса.

Формулы для расчета в столбце В:

Внеоборотные активы — (В4) = ‘активы’!В3;

Оборотные активы — (В6) = ‘активы ‘!В8;

Баланс — (В8) = В4 + В6;

Внеоборотные активы, % к итогу, — (В5) =
В4/В8;

Оборотные активы, % к итогу баланса — (В7)
= В6/В8;

Собственный капитал — (В 10) = ‘пассивы’!В3;

Долгосрочные обязательства — (В 12) =
‘пассивы’!В9;

Краткосрочные обязательства — (В 14) =
‘пассивы’!В12;

Баланс — (В16) = В10 + В12 + В14;

Собственный капитал, % к итогу баланса — (В11)
= В10/В16;

Рис. 1.3. Таблица расчета агрегированного аналитического баланса

Долгосрочные
обязательства, % к итогу баланса — (В 13) = В12/ /В16;

Краткосрочные
обязательства, % к итогу баланса — (В 15) = В14/ /В16.

Скопируйте
набранные формулы в столбец С. Ваша элект­ронная таблица примет вид, как на
рис. 3.4.

Переименуйте
Лист 3 электронной книги, присвоив ему имя «Агрегированный баланс».

Сохраните созданную
электронную книгу.

Рис. 1.4. Агрегированный аналитический баланс

Тема: АНАЛИЗ ФИНАНСОВОГО СОСТОЯНИЯ ПРЕДПРИЯТИЯ НА
ОСНОВАНИИ ДАННЫХ БАЛАНСА В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Цель. Изучение технологии анализа
финансового со­стояния в электронных таблицах

Задание 2.1. Создать таблицу расчета
реформированного ана­литического баланса 1.

Краткая
справка.
Реформированный аналитический ба­ланс 1 предназначен для анализа эффективности
деятельности предприятия. В нем активы предприятия собраны в две группы:
производственные и непроизводственные активы.

Обязательства
предприятия также разделены на две группы: финансовые и коммерческие
обязательства. При этом коммерче­ские обязательства переносятся из пассивов в
активы со знаком «–». Таким образом, в
реформированном аналитическом балансе 1 рассчитываются чистые активы,
которые очищены от коммерче­ской и кредиторской задолженностей и используются
при анализе эффективности деятельности предприятия. Внеоборотные, ак­тивы
предприятия рассчитываются как сумма производственных внеоборотных активов и
прочих внеоборотных активов.

Рис. 2.1. Таблица
реформированного аналитического баланса 1

Порядок работы

Откройте созданную
электронную книгу «Анализ баланса».

На
очередном свободном листе создайте таблицу реформи­рованного аналитического
баланса 1 по образцу (рис. 2.1).

Произведите расчеты в таблице реформированного аналитиче­ского
баланса 1. Используем данные листов «Активы» и «Пассивы».

Формулы для расчета в столбце В:

Производственные
внеоборотные активы (В5) = ‘активы’!В5 + + ‘активы’!В6 + ‘активы’!В15;

Прочие внеоборотные активы (В6) =
‘активы’!В4 + ‘активы’!В7;

Внеоборотные активы (В4) = В5 + В6;

Запасы и прочие оборотные активы (В8) =
‘активы’!В9;

Краткосрочная дебиторская задолженность
(В9) = ‘активы’!В17;

Денежные средства и
краткосрочные вложения (В 10) = ‘активы’!В18 + ‘активы’!В19;

Кредиторская
задолженность (В 11) = — (‘пассивы’!В14 + ‘пассивы’!В21);

Чистый оборотный капитал (В7) = SUM(B8:B11);

ИТОГО ЧИСТЫЕ АКТИВЫ (В 12) = В4 + В7.

Уставный капитал оплаченный (В 16) =
‘пассивы’!В4;

Добавочный капитал (В 17) = ‘пассивы’!В5;

Резервы, прибыль,
фонды (фактические), целевое финансиро­вание (В18) = ‘пассивы’!В6 +
‘пассивы’!В7;

Собственный капитал (фактический) (В15) = SUM(B16:B18);

Долгосрочные финансовые обязательства
(В20) = ‘пассивы’!В9;

Краткосрочные кредиты и займы (В21) =
‘пассивы’!В12;

Финансовые обязательства (В 19) = SUM(B20 : B21);

ИТОГО ВЛОЖЕННЫЙ КАПИТАЛ (В22) = В15 + В19.

Скопируйте
набранные формулы в столбец С. Ваша элект­ронная таблица примет вид, как на
рис. 2.2.

Переименуйте
лист электронной книги, присвоив ему имя «Реформированный баланс 1».

Сохраните созданную
электронную книгу.

Рис. 2.2. Реформированный аналитический баланс 1

Задание 2.2. Создать таблицу расчета реформированного
ана­литического баланса 2.

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

Внеоборотные активы
предприятия, как и в реформирован­ном аналитическом балансе 1, рассчитываются
как сумма про­изводственных внеоборотных и прочих внеоборотных активов.

Порядок работы

1. На очередном свободном листе
электронной книги «Анализ баланса» создайте таблицу реформированного
аналитического ба­ланса 2 по образцу (рис. 2.3).

Рис. 2.3. Таблица реформированного аналитического баланса 2

2. Произведите
расчеты в таблице реформированного аналити­ческого баланса 2.

Краткая справка. Используем данные листов «Активы»,
«Пассивы» и «Реформированный баланс 1».

Формулы для расчета в столбце В:

Внеоборотные активы (В4) =
‘Реформир_баланс1’!В4;

Запасы и прочие
оборотные активы (В6) = ‘Реформир_ба-ланс1’!В8;

Краткосрочная
дебиторская задолженность (В7) = ‘Реформир_ баланс 1’!В9;

Краткосрочные финансовые вложения (В8) =
‘активы’!В18;

Денежные средства (В9) = ‘активы’!В19;

Оборотные активы (В5) = SUM(B6:B9);

АКТИВЫ ВСЕГО (В10) = В4 + В5.

Собственный капитал
(фактический) (В 12) = ‘Реформир_баланс1’!В15;

Долгосрочные
финансовые обязательства (В 13) = ‘Реформир_ баланс1’!В20;

Краткосрочные
финансовые обязательства (В14) = ‘пассивы’!В12;

ПАССИВЫ ВСЕГО (В15) = SUM(B12:B14).

ЧИСТЫЙ ОБОРОТНЫЙ КАПИТАЛ (В17) = В5 — В14.

Скопируйте набранные
формулы в столбец С.

Ваша электронная
таблица примет вид, как на рис. 2.4.

Переименуйте лист
электронной книги, присвоив ему имя «Реформированный баланс2».

Сохраните созданную
электронную книгу.

Рис. 2.4.
Реформированный аналитический баланс 2

Задание 2.3. Рассчитать показатели финансовой
устойчивости предприятия на основе данных таблицы «Реформированный ба­ланс 2».

Результаты расчетов оформить в виде
таблицы.

Краткая справка. Формулы для расчета:

=

=

=

Задание 2.4. Рассчитать параметры ликвидности
предприятия на основе данных таблицы «Реформированный баланс 2».

Результаты расчетов оформить в виде
таблицы.

Краткая справка. Формулы для расчета:

=

.

КОНТРОЛЬНЫЕ
ВОПРОСЫ

1.Дайте понятие
активов и пассивов.

2.Что показывает
реформированный баланс 1. Сделайте вывод по полученным данным.

3.Что
показывает реформированный баланс 2. Сделайте вывод по полученным данным.

4.Что такое
ликвидность и абсолютная ликвидность.

Время на прочтение
5 мин

Количество просмотров 172K

Я работаю обычным аналитиком и, так получилось, что летом 2014 года, участвуя в одном e-commerce проекте, на коленке за 3 недели сделал управленческий учет в MS Excel. Давно планировал и наконец-то решил выложить на Хабр. Думаю, будет полезно малым предпринимателям, понимающим важность управления финансовыми потоками, но не желающим тратить значительное количество времени и средств на ведение управленческого учета. Не претендую на истину в последней инстанции и буду рад иным решениям, предложенным участниками сообщества.

Бизнес, к которому я летом имел отношение, был обычным интернет-магазином одежды премиум и выше сегмента с оборотом около 1 млн рублей в месяц. Бизнес работал, не сказать, чтобы очень успешно, но работал и продолжает работать. Собственник понимал необходимость ведения управленческого учета и, с этим пониманием, взял меня в качестве финансового директора (аналитика/менеджера …), так как предыдущий ушел из бизнеса за 3 месяца до моего прихода. Собственно, дыра такой же продолжительности была и в ведении управленческого учета. Забегая вперед скажу, что дыру не устранил (решили не ворошить прошлое), но создал систему, которая успешно работает при минимальных трудозатратах и по сей день.

Мой предшественник вёл управленку в Финграде, который оказался весьма мощным инструментом. Например, он позволял автоматически грузить информацию из 1С и выписок разных банк-клиентов, создавая проводки по заранее сформулированным правилам. Вещь, безусловно полезная, однако, при соблюдении системы двойной записи увеличивала время работы в разы. Чтобы избежать увеличения работы этот инструмент позволял генерировать «зависимые проводки». В создании этих дополнительных проводок и была зарыта собака. И тут выяснилось, что за всей мощью Финграда крылась уникальность, обусловившая полное отсутствие экспертизы в свободном доступе. Обычным пользователям (платившим, кстати, 3000 рублей в месяц за доступ к системе) были доступны лишь «Руководство пользователя» на официальном сайте, да 6 видео-уроков там же. Youtube, дававший доступ к ещё паре десятков видеоуроков, также не сильно помогал. Форумов с информацией «how to…» не было в принципе. Поддержка, на конкретные вопросы о правилах создания «зависимых проводок» и просьбах помочь именно в моем случае — морозилась фразами «у нас с вами не заключен договор на поддержку, поэтому на такие специфические вопросы мы не готовы отвечать». Хотя казалось бы — чего специфического в таких просьбах, да ещё и со скриншотами с моей стороны? Понятно, что все можно бить руками, но спрашивается, а зачем тогда вообще платить за инструмент, который сильно увеличивает время, необходимое на ведение управленки и не дает никаких преимуществ для малого бизнеса?

Убедив собственника в нецелесообразности использования «Финграда» при таких объемах бизнеса и выгрузив всю информацию из системы, я поставил на нем БОЛЬШОЙ и жирный крест. При этом решение уйти именно в MS Excel было не спонтанным. Хорошенько загуглив на тему ведения управленческого учета находил монстров, похожих на «Финград», либо ссылки на веб-приложения для ведения личных финансов, в то время как основными требованиями к системе были:

— возможность ведения БДДС и БДР на основе изменяемого плана счетов;
— простота в дальнейшем ведении управленческого учета (в том числе силами «финансово-неграмотных» пользователей);
— гибкость (возможность на ходу расширять/убирать функционал);
— отсутствие перегруженности инструмента/интерфейса.

Для начала проясним термины: будучи не финансистом, под БДДС понимаю «Баланс Движения Денежных Средств», БДР — «Бюджет Доходов и Расходов». БДДС считаем кассовым методом (днем совершения операции — колонка «Дата операции») и используем для операционного day-to-day планирования, а БДР методом начисления (колонка «Период начисления») для стратегического, в рамках года и более.

Итак, как все устроено и как оно работает (в идеале):

1. Управленческий учет собирается на основе информации вводимой конечными пользователями при помощи формы в Google Docs. Красным помечены названия полей и кодировки вариантов в конечном файле управленческого учета — своего рода мапинг полей.

2. В итоге выглядит оно так (зеленым залито то, что перенесено в итоговый файл управленки).

3. Управленческий учет построен на базе .xls выгрузки из Финграда (отсюда странные для сторонних пользователей названия и, в целом избыточное количество колонок). Убедительная просьба не воспринимать всерьез значения колонок «Приход», «Расход» — многое рандомно изменено.

Механизм заполнения прост: аккуратно переносим во вкладку «Общая книга» из формы Google Docs и банковских выписок. Красным выделены строки, используемые для формирования БДР, зеленым — БДДС., которые представляют собой сводные таблицы и строятся на основе промежуточных вкладок с говорящими названиями. Единственные колонки, информация в которых не связана с иными источниками: «Исходный ID» (уникальные значения строк) и «Дата создания» (=ТДАТА(), а затем копируем и вставляем как значение)

4. Статьи ДДС (движения денежных средств) располагаются на отдельной вкладке «ПС_служебный» и вполне могут регулярно пересматриваться в зависимости от конкретных потребностей (не забываем обновлять формулы на листах «Данные_БДДС», «Данные_БДР»).

5. На картинке образец БДДС, в формате по умолчанию, свернутый до понедельной «актуальности».

6. Образец БДС (помесячный). Обратите внимание на уже упоминавшийся выше тезис об использовании строк из «Общей книги»: Бюджет и Факт для БДР, План и Факт — для БДДС.

7. Работа с БДДС подразумевает поддержание строк «План» в максимально актуальном состоянии. Я достаточно педантичен в работе с первичной информацией и комментарии сделанные мной сохраняли всю историю изменений. Как будет у Вас — вопрос к Вам. Мой подход позволил мне отлавливать примерно 1 существенную ошибку в неделю, грозившую расхождениями на десятки-сотни тысяч рублей. Время, кстати, съедалось немного.

8. Собственно сам файл управленческого учета.

PS: Долго думал над тем, как автоматизировать процесс «перелива» информации из формы Google Docs, пока не пришел к мысли о необходимом ручном контроле вводимой разнородной информации (много людей заполняет формы + наличие минимум одного банк-клиента + 1С). Тем более не знаю VBA… Отдаю на суд хабрасообщества как есть, надеюсь, кому-нибудь поможет или просто будет интересно.

UPD от 30.04.2020: ввиду текущей экономической ситуации решил вспомнить Excel и бесплатно помочь малому бизнесу созданием подобных инструментов. Все интеллектуальные права останутся за мной и будут опубликованы в виде статей (без данных, содержащих коммерческую тайну).

Критерии попадания в выборку кому хочу помочь:

Обязательный:
Вы не МММ, продавцы алкоголя возле школ, микрофинансовые организации

и прочие лохотронщики

Опциональные(достаточно соответствовать одному из):
Ваше дело имеет очевидную ценность для общества (благотворительные фонды, волонтерские движения и т.д.)
или
Вы платите белые зарплаты и приемлемый для оборота объем налогов (придется доказать выписками перечислений в фонды и на счета ФНС)

Функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

Средства Excel для визуализации данных бизнес-анализа

Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.

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

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

Именно поэтому для экономического (т. е. постфактного) анализа работы компании широко применяются специализированные программы, а вот для целей бизнес-анализа на практике предпочитают пользоваться всем известным табличным редактором Excel. И для этого есть веские основания, которые мы и рассмотрим.

Основные функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

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

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

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

Создать сводную таблицу может любой пользователь. Для этого в меню функций Вставка следует выбрать параметр Сводная таблица. Но для успешной работы со сводной таблицей как инструментом бизнес-анализа потребуются определенные навыки.

Работают со сводными таблицами из вкладки меню функции «Анализ» (рис. 1). На этой вкладке также настраиваются параметры сводной таблицы и источники данных (откуда берется информация).

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

Обратите внимание!

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

Функция ВПР табличного редактора Excel помогает консолидировать данные для бизнес-анализа тем пользователям, которые недостаточно хорошо знают функционал сводных таблиц.

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

К сведению

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

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

Для наглядности приведем еще пример локального применения функции ВПР при решении задачи построения оперативного бизнес-отчета из собственной практики.

Функция ЕСЛИ, предусмотренная функционалом Excel, также популярна у бизнес-аналитиков, но она применяется чаще всего не при анализе информации, а при построении различного рода прогнозов и сценариев результатов деятельности компании. Суть функции в том, что в заданной ячейке выводится один результат при выполнении определенного условия и другой — при невыполнении этого условия.

Учет производства продукции

Основная задача любой производственной деятельности — учет производства продукции. Эффективный учет необходим для отслеживания соблюдения плана производства, а также позволяет осуществить расчет сдельной заработной платы в зависимости от объема произведенной продукции работником предприятия. В дополнении к этому реализована возможность учета основных и дополнительных работ производственного цикла. Табель учета производства разработан в среде Microsoft Excel, в виде стандартной книги с макросами формата .xlsm, что избавляет от необходимости установки дополнительного программного обеспечения. Для полноценной работы вам потребуется скачать архив по ссылке ниже, разархивировать с помощью программы WinRar или 7zip, открыть файл и разрешить работу макросов (при наличии предупреждения Excel).

Ссылки для скачивания:

— Скачать макрос учет производства продукции и расчета сдельной заработной платы

Как начать работу с учетом производства продукции в excel:

1. Разархивируйте скачанный архив с файлом с помощью программы 7zip или WinRar.

2. При появлении сообщения о доверенном источнике: закройте программу, кликните правой кнопкой мыши на файле — «Свойства», далее установите галочку напротив «Разблокировать».

3. Если в Вашем Excel запуск макросов по умолчанию отключен, в данном окне необходимо нажать «Включить содержимое».

4. Для полноценной работы с файлом необходимо нажать «Разрешить редактирование» при появлении данного сообщения.

Учет производства продукции — настройки построения

Для удобства работы с программой все настройки построения табеля учета производства продукции вынесены на отдельный лист «Настройки». Лист разделен на 2 блока: перечень сотрудников, справочники продукции и работ организации. Перечень сотрудников представляет собой простую таблицу работников организации — табельный номер, фио, должность. Остановимся подробнее на блоке справочников производственного процесса (рисунок ниже).

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

После того как все данные заведены (для ознакомления с программой можно воспользоваться введенными тестовыми данными) следует приступить непосредственно к построению листа месячного учета производства продукции и расчета сдельной заработной платы. Для этого нажмите кнопку «Создать табель на месяц».

Учет производства продукции — ведение табеля

На рисунке ниже представлен месячный табель учета производства продукции:

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

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

Формирование расчетных листов сдельной заработной платы

На рисунке ниже изображен стандартный месячный расчетный лист по сотруднику, который можно сформировать за выбранный период при нажатии на кнопку «Расчетные листы» (лист «Настройки»):

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

Сводная таблица «Учет производства продукции»

Еще одной полезной фичей табеля учета производства продукции является построение сводной таблицы по всему перечню произведенных наименований за выбранный период (рисунок ниже). Для расчета сводной необходимо использовать кнопку «Сводная таблица» листа настроек.

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

Графики производительности по наименованиям

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

Заказать разработку программы или доработку существующей

Спасибо за прочтение данной статьи!

Если Вам понравился мой продукт, но Вы хотели бы донастроить его под Ваше производство или заказать любую другую расчетную программу, проконсультироваться по возможности автоматизации своих бизнес процессов, для связи со мной можно использовать WatsApp +79507094770, сайт excellab.ru или написать мне на почту: goryaninov@bk.ru, профиль вк.

Всегда на связи, рад помочь Вашему бизнесу!

Ссылки для скачивания:

— Скачать макрос учет производства продукции и расчета сдельной заработной платы

Здесь можно ознакомиться с другими программами автоматизации, которые представлены на моем сайте:

— Дневной табель учета рабочего времени в excel
— Почасовой табель учета рабочего времени в excel
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен
— Учет доходов и расходов
— Автоматизированное формирование документов

Вы можете найти этот шаблон, выполнив поиск в Excel.

Шаблон плана проекта Excel

План проекта — это документ. которые могут требовать диаграмм Excel, но в остальном составлены в Microsoft Word. Однако для базовых проектов вам может потребоваться только документ Microsoft Excel.

4. Простая диаграмма Ганта

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

Этот шаблон включен в Microsoft Excel.

5. Шаблон планировщика событий

План проекта действительно не то, что вы обычно составляете в Excel. Но если ваш проект достаточно прост, например, планирование вечеринки, вам нужен твердый одностраничный шаблон, в котором перечислены основные задачи и вы можете определить график и бюджет. Этот шаблон из Office Templates Online — отличное начало.

Шаблон отслеживания проектов Excel

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

6. Отслеживание затрат по видам деятельности

Этот шаблон отслеживания может помочь вам получить представление о прямых, косвенных, а также общих и административных затратах на продукт.

7. Шаблон отслеживания проекта

Этот шаблон Vertex42 необходим, если вы работаете с несколькими различными клиентами, проектами и / или результатами. Он объединяет детали проекта, расходы, статусы задач и сроки выполнения.

Шаблоны бизнес планов

В Microsoft Office есть собственная категория для бизнес-планов. Воспользуйтесь предложенным бизнес-поиском и выберите категорию « Бизнес-планы » справа.

Вы найдете следующие шаблоны Microsoft Excel:

• Затраты на запуск
• Контрольный список бизнес-плана
• Контрольный список бизнес-плана с SWOT-анализом

Дополнительные шаблоны бизнес-планов шаблоны бизнес-планов , посмотрите нашу специальную статью.

Поиск онлайн-шаблонов

Не можете найти нужный шаблон управления проектом в Excel? Обратитесь к стороннему онлайн-ресурсу для более широкого выбора шаблонов таблиц Excel. Мы рекомендуем следующие сайты.

Vertex42

На этом сайте есть несколько отличных шаблонов управления проектами для Microsoft Office 2003 и выше. Сайт отмечает, что его шаблоны в основном связаны с планированием проекта. Для более сложных задач может потребоваться Microsoft Project или другое программное обеспечение для управления проектами.

На странице, посвященной управлению проектами , вы найдете список полезных материалов, включая, помимо прочего, следующее:

• График
• Проектирование бюджета
• Метод критического пути

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

TidyForm

TidyForm имеет респектабельный выбор шаблонов управления проектами Microsoft Excel. Самые популярные категории перечислены на главной странице. Если вы не можете сразу найти то, что вам нужно, переключитесь в раздел « Бизнес » или попробуйте функцию поиска.

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

Мы рекомендуем следующие страницы:

• Диаграмма Ганта
• Проектное предложение
• Структура разбивки работ

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

Управление шаблонами Microsoft Excel

Сначала посмотрим, какие шаблоны вы уже установили в Microsoft Excel. Для этой демонстрации мы использовали Excel 2016, но процедура аналогична в Microsoft Office 2013 и Office 2019.

Значения по умолчанию

Когда вы запускаете Microsoft Excel, первое окно, которое вы видите, будет содержать поле поиска для онлайн-шаблонов. Когда вы начинаете с существующей книги, перейдите в Файл> Создать, чтобы перейти к тому же представлению.

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

Искать в Интернете больше шаблонов проектов

Самый быстрый способ найти нужный вам шаблон — это поиск. Как только вы начнете поиск, например, для термина « проект» , вы также увидите категории шаблонов, перечисленные рядом с шаблонами, которые соответствуют вашему поиску.

Сузить поиск

Отличная особенность заключается в том, что вы можете сузить область поиска, выбрав несколько категорий. Это поможет вам исключить шаблоны, которые могут соответствовать вашему ключевому слову, но не нужной категории. С другой стороны, вы можете обнаружить, что идеальный шаблон не доступен в Microsoft Excel.

Предварительный просмотр и создание шаблона

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

Чтобы загрузить и использовать шаблон, нажмите кнопку « Создать» , чтобы открыть новую книгу Microsoft Excel с предварительно заполненным шаблоном.

Шаблон готов, установлен, иди

Пока вы заняты этим, просмотрите наш список полезных офисных шаблонов. и запаситесь шаблонами деловых писем. Шаблоны

Мы рассмотрели много советов по управлению проектами и уловок прошлого. Когда вы разберетесь с шаблонами, вы можете рассмотреть дополнительные инструменты и решения. Например, знаете ли вы, что Outlook отлично подходит для управления проектами ? Аналогично, вы можете использовать OneNote для управления проектами. И вы можете интегрировать OneNote с Outlook для управления проектами. ? Возможности безграничны.

Если вы все еще ищете надежный инструмент управления проектами, Руководство для , попробовать Асану.