От журнала регистрации к журналу операций

Для ведения учета в нужном объеме в журнал регистрации следует внести некоторые изменения. Переименуйте лист, на котором он расположен, в ЖурналОпе-раций. Для отражения производимых операций измените названия столбцов: D - Наименование, Е - Размер, F - Из чего.

Удалите имена, которые были ранее присвоены столбцам журнала. Для этого нужно, выполнив команду Вставка/Имя/Присвоить, вызвать диалоговое окно Присвоение имени (см. главу 7, рис. 7.2), выделить старое имя в списке Имя и нажать кнопку Удалить.

Выделив диапазон ячеек, в котором расположена таблица журнала операций, присвойте ячейкам новые имена, нажав комбинацию клавиш [Ctrl+Shift+F3].

Рис. 9.30. Фрагмент рабочего листа ЖурналОпвраций

Заполнение журнала операций

Информацию в журнал операций рекомендуем заносить следующим образом.
1. Данные о покупке материалов (денежные затраты):

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

- столбец Расход - сумма, уплаченная за материал.

2. Сведения об использовании материалов:

- столбец Что именно - наименование материала;

- столбцы Наименование, Размер и Из чего - сведения об изделии, на которое расходуется материал;

- столбец Дохой - стоимость использованного материала с отрицательным знаком.

3. Операции по продаже готовых изделий:

- столбцы Наименование, Размер и Из него - сведения об изделии;

- столбец Доход - сумму, полученную от продажи изделия.

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

Таблица 1: покупка и расход материалов

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

- Область определения временного интервала, за который производится расчет:

- ячейка В1 - с какого числа;

- ячейка В2 - по какое число,

Область расчета суммы расходов на приобретение материалов (область А4: В10):
- диапазон А6: А10 - название материала;

- диапазон В6: В10 - расчет сумм, потраченных на приобретение каждого материала;

- ячейка В4 - сумма, уплаченная за все материалы.

- Область расчета суммы израсходованных материалов (область А12: В18):

- диапазон А14: А18 - название материала;

- диапазон В14: В18 - расчета стоимости израсходовано материала;

- ячейка В12 - стоимость всех израсходованных материалов,

- Область расчета остатков материалов (область А20: В26):

- диапазон А22: А26 - название материала;

- диапазон В22: В26 - расчет стоимости остатков материала;

- ячейка В20 - полная стоимость остатков материалов.

Объемная разрезанная круговая диаграммы, демонстрирующая расход материала на все изделия.

Рис. 9.31. Лист Расходы1

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

{=СУММ(ЕСЛИ(Дата> =ПериодС; ЕСЛИ(Дата< " ПериодПо;

ЕСЛИ(Наименование=А6; Расход; 0); 0); 0))}

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

{=-СУММ(ЕСЛИ(Дата> =ПериодС; ЕСЛИ(Дата< =ПериодПо;

ЕСЛИ(Что_именно=А14; Доход; 0); 0); 0))}

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

=В6-В14

Таблица 2: расход материалов на изделия

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

Рис. 9.32. Лист Расходы2

Ячейки В4: Е4 и F4: T4 объединены и имеют адреса В4 и F4 соответственно. Ячейки В5: С5, D5: E5, F5: G5 и Н5: 15 также объединены и имеют адреса В5, D5, F5 и Н5 соответственно. Формула в ячейке В7 должна выглядеть следующим образом:

{=-СУММ(ЕСЛИ(Дата> =ПериодС;

ЕСЛИ(Дата< =ПериодПо; ЕСЛИ(Из_чего=$В$4;

ЕСЛИ(Наименование=$В$5; ЕСЛИ(Размер=B$6;

ЕСЛИ(Что_именно=$А7; Доход; 0); 0); 0); 0); 0); 0))}

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

После вставки этой же формулы в ячейку D7 необходимо скорректировать адрес ячейки, указывающей наименование изделия. Измените адрес на $D$5. Формула в ячейке D7 будет иметь вид:

{=-СУММ(ЕСЛИ(Дата> =ПериодС;

ЕСЛИ(Дата< =ПериодПо; ЕСЛИ(Из_чего=$В$4/ЕСЛИ(Наименование=$D$5;

ЕСЛИ(Размер=D$6; ЕСЛИ(Что_именно=$А7; Доход; 0); 0); 0); 0); 0); 0))}

При копировании формулы в ячейку F7 скорректируйте адреса ячеек, указывающих наименование изделия и материал, из которого они изготовлены. Измените адреса на SFS5 и SFS4 соответственно. Формула в ячейке F7 будет выглядеть следующим образом:

{=-СУММ(ЕСЛИ(Дата> =ПериодС;

ЕСЛИ(Дата< =ПериодПо; ЕСЛИ(Из_чего=$В54; ЕСЛИ(Наименование=$D$5;

ЕСЛИ(Размер=Е$6; ЕСЛИ(Что_именно=$А7; Доход; 0); 0); 0); 0); 0); 0))}

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

Таблица 3: распределение выручки по изделиям

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

{=СУММ(ЕСЛИ(Дата> =ПериодС; ЕСЛИ(Дата< =ПериодПо; ЕСЛИ(Наименование=В$5;

ЕСЛИ(Размер=$А6; ЕСЛИ(Что_именно=0; ЕСЛИ(Из_чего=$В$4; Доход; 0); 0); 0); 0); 0); 0))}

Формулы в столбцах С, D и Е строятся на ее основе по тем же принципам, что и в предыдущем примере. В столбце F вычисляются итоговые значения.

Рис. 9.33. Лист Выручка

Таблица 4: распределение прибыли по изделиям

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

{=СУММ(ЕСЛИ(Дата> =ПериодС;

ЕСЛИ(Дата< ПериодПо; ЕСЛИ(Наименование=В$5;

ЕСЛИ(Размер=$А6; ЕСЛИ(Что_именно=0;

ЕСЛИ(Из_чего=$В$4; Доход; 0); 0); 0); 0); 0); 0))+СУММ

(ЕСЛИ(Дата> =ПериодС; ЕСЛИ(Дата< ПериодПо;

ЕСЛИ(Наименование=В$5; ЕСЛИ(Размер=$А6;

ЕСЛИ(Что_именно> 0; ЕСЛИ(Из_чего=$В$4; Доход; 0); 0); 0); 0); 0); 0))}

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

- значения в столбце Что_именно равны нулю;

- значения в столбце Что_именно больше нуля.

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

Рис. 9.34. Лист Прибыль

Резюме

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

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

При работе с таблицами анализа желательно, чтобы журнал регистрации находился в отдельной книге. В противном случае при вводе данных в журнал сразу же выполняется перерасчет всех формул, что существенно замедляет работу. Можно воспользоваться и ручным режимом вычислений, для чего следует выполнить команду Сервис/Параметры и на вкладке Вычисления диалогового окна Параметры (см. главу 2, рис. 2.27) активизировать переключатель Вручную. В таком случае перерасчет формул будет производиться после нажатия клавиши [F9].