Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1с-бухгалтерия» для дисциплин «Организация и методика аудита», «1с-бухгалтерия» (для (стр. 5 из 6)

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

Щелкаем правой кнопкой мыши в области сводной таблицы и выбираем из контекстного меню команду «Формулы4Вычисляемое поле»[10]. В открывшемся окне вводим имя нового поля «Сумма с НДС» и формулу для его вычисления =Сумма*1,2. Чтобы избежать опечаток, имена полей удобно выбирать из списка в окне «Поля» и переносить их в формулу с помощью кнопки «Добавить поле» (рис.21).

Перестроим отчет сводной таблицы с помощью Мастера (рис. 22). Поле «Покупатель» перенесем в область страниц, поле «Ключ» — в область строк. В область данных поместим вычисляемое поле «Сумма с НДС» (способ расчета – суммирование) и поле «Сум.прод» (способ расчета — минимальное значение). Сумма продажи повторяется в каждой строке таблицы-источника.

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

Поле «Покупатель» в макете сводной таблицы было вынесено в область страниц. Щелкнув мышью по треугольнику в ячейке B2 (обведено на рисунке) , можно выбрать из выпадающего списка конкретного покупателя и получить по нему таблицу и диаграмму. На рис. 24 показаны таблица и диаграмма, построенные по всем покупателям. Для ее построения из выпадающего списка был выбран пункт «(Все)».

Рис. 21 — Добавление вычисляемого поля в сводную таблицу

Рис. 22 — Макет сводного отчета с вычисляемым полем

Рис. 23 — Гистограмма продаж по отчету с вычисляемыми полями

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

Перейдем к листу с таблицей-источником и введем в столбец L новый заголовок «Сум.прод2». Наша таблица-источник упорядочена по полю «Ключ» (если нет —отсортируйте ее по этому полю). Для исключения повторяющихся значений суммы продажи в пределах накладной, запишите в ячейку L2 формулу: =ЕСЛИ(И(A1=A2;I1=I2);0;I2).

Эта формула означает: ЕСЛИ ключи предыдущей (A1) и текущей (A2) строк совпадают И совпадают значения столбцов «Сум.прод» текущей и предыдущей строк (I1=I2), в текущую ячейку записываем ноль. Иначе – копируем в ячейку значение из столбца «Сум.прод» текущей строки. Размножив эту формулу во все остальные строки столбца «Сум.прод2», получим новое поле. В нем для каждого значения ключа есть только одно значение суммы продажи (рис. 24).

Построим на основании измененной таблицы-источника сводную таблицу. В область страниц перетащим поле «Покупатель», в область строк – поля «Дата» и «Документ». В область данных поместим поле «Сум.прод2», способ расчета – суммирование.

Добавим в таблицу два вычисляемых поля: «Наценка грн.» и «Наценка %». В поле «Наценка грн.» рассчитаем наценку в гривнях по формуле =Сум.прод2/1,2–Сумма (то есть сумма продаж без НДС минус себестоимость проданных товаров). В поле «Наценка %» используем формулу =ЕСЛИ(Сумма< >0;'Наценка грн'/Сумма;0). Если значение поля «Сумма» не равно нулю (на ноль делить нельзя!), вычислим отношение полей «Наценка грн.» и «Сумма», иначе запишем ноль.

Рис. 24 — Результат генерации ключа для определения наценки

В таблице на рис. 25 назначим полю «Сум.прод2» денежный формат, полю «Наценка %» — процентный. Поле «Наценка грн.» удалим, чтобы таблица лучше читалась. Щелкнув правой кнопкой мыши в любой ячейке столбца «Данные» с текстом «Сумма по полю Наценка грн.» выделим все ячейки, относящиеся к этому полю, и выполним команду «Удалить» из выпадающего меню. Значения полей «Наценка %», формула для которых ссылается на удаляемое поле «Наценка грн.», при этом не изменятся. Мы удалили поле из отчета сводной таблицы, а не из самого объекта «сводная таблица». Формулы в других вычисляемых полях могут ссылаться и на невидимое поле. Чтобы удалить вычисляемое поле из объекта «сводная таблица», нужно пользоваться кнопкой «Удалить» окна вставки вычисляемого поля (см. рис. 21).

Рис. 25 — Сводная таблица для определения наценки

Список литературы

1. Горнаев А. ЕХСЕL, VBA, ІNTERNET в зкономике и финансах.— СПб, 2001, — 816 с.

2. Воробьев В.В. Microsoft Excel 2000: Пособие для начинающих.— К., 2000, — 36 с.

3. Майер П. Microsoft Office 97. Торгово-издательское бюро. BHV, — Киев, 1998, — 467 с.

4. Карпенко М.Ю. Методичні вказівки до виконання економічних розрахунків засобами Мisrosoft Excel з курсу "Прикладні задачі менеджменту на ПК" ( розділ "Фінансові функції та оптимізація"), — Харків: ХДАМГ, 2000, — 16 с.

5. Использование Microsoft Office (Word; Excel; MS Access). — К., 1996, — 478 с.

6. Борковский А.В. Англо-русский словарь по программированию и информатики (с толкованиями), — М.: Рус.яз., 1989.—335 с.

Содержание

1. Цель работы....................................................................................................... 3

2. Содержание работы.................................................................................... 4

3. Методика выполнения работы........................................................ 4

3.1. Подготовка исходных данных............................................................. 4

3.2. Преобразование исходных данных.................................................... 6

3.3. Синтез ключа............................................................................................... 11

3.4. Объединение таблиц по общему ключу.......................................... 14

3.5. Другие функции просмотра.................................................................. 18

3.6. Анализ структуры продаж по сводным отчетам....................... 19

3.7. Использование деловой графики для анализа структуры продаж 21

3.8. Организация вычислений в сводном отчете................................ 27

3.8.1. Изменение структуры сводного отчета....................................... 27

3.8.2. Внедрение вычисляемых полей в сводный отчет........................ 29

Список литературы...................................................................................... 35

Учебное издание

Методические указания к выполнению лабораторной работы «Формирование отчета о структуре продаж по данным программы «1С-Бухгалтерия» для дисциплин «Организация и методика аудита», «1С-Бухгалтерия» для студентов спец. 7.050106 «Учет и аудит», иностранных студентов и системы дистанционного образования.

Составители: Николай Юрьевич КАРПЕНКО,

Наталия Ивановна ГОРДИЕНКО,

Игорь Николаевич РЯБЧЕНКО.

Редактор: Н.З. Алябьев

Корректор: З.И. Зайцева

План 2005, поз. 156

Подп. в печать ______ Формат 60х84 1/16 Бумага офисная

Печать на ризографе. Усл.-печ. л. 1,5 Уч.-изд. л. 36

Зак. № _____ Тираж 50 экз.

Сектор оперативной полиграфии ИВЦ ХНАГХ, 61002, Харьков, ХНАГХ, ул. Революции, 12

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

[2] Справедливо, если в свойствах документа «Расходная накладная» установлен параметр автоматической нумерации, обычно такой параметр установлен.

[3] Приближенное соответствие – ближайший меньший элемент из первого столбца массива к искомому значению. Например, если в столбце поиска - цифры 1, 2 и 3, а искомое значение 2.999, то будет выбрано 2, а не ближайшее значение 3. Если искомое значение равно 0, т.е. меньше наименьшего значения столбца, функция выдаст сообщение #Н/Д.