Моделирование и технологические приемы анализа хозяйственной деятельности средствами Microsoft Excel

 

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

Для ее создания средствами Microsoft Excel вызывается программа – табличный процессор командой Пуск – Программы – Microsoft Excel. На экран загружается главное окно программы с заголовком «Microsoft Excel» и рабочее окно в виде таблицы с заголовком «Книга1».

Книга в Microsoft Excel представляет собой файл, используемый для хранения и обработки данных. По умолчанию он имеет название «Книга1». Это имя, как правило, изменяют при сохранении данных. Для сохранения пустого файла, например под именем «Книга1_Информационная база», используется команда Файл – Сохранить как.

При открытии для продолжения работы сохраненного файла с именем «Книга1_Информационная база» выполняется команда Файл – Открыть. Имя файла будет отражено в заголовке рабочего окна программы.

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

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

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

В табл. 2.2 представлен один из вариантов компьютерной информационной базы.

Таблица 2.2. Состав информационной базы анализа хозяйственной деятельности организации в среде Microsoft Excel

Книга1 «Информационная база АХД»
Лист1	Форма1 «Бухгалтерский баланс»
Лист2	Форма2 «Отчет о прибылях и убытках»
Лист3	Форма3 «Отчет о движении источников собственных средств»
Лист4	Форма4 «Отчет о движении денежных средств»
Лист5	Форма5 «Приложение к бухгалтерскому балансу»
Лист6	Форма5-з «Отчет о затратах на производство и реализации продукции»
Лист7	Главная книга
Лист8	Сметы затрат
Лист9	Калькуляции единицы продукции
Лист10	Ведомость12
Лист11	Ведомость15
Лист12	Журнал-ордер10
Лист13	Форма 1П «Отчет о производстве продукции»
Лист14	Ведомость16 «Движение готовых изделий их отгрузки и реализации»
Лист15	Контракты на реализацию
Лист16	Данные маркетинговых исследований
Лист17	Данные оперативного учета
Лист18	Форма11 «Отчет о наличии и движении основных.средств и других внеоборотных активов»
Лист19	Ведомость переоценки основных средств
Лист20	Производственный календарь
Лист21	Бизнес-план
Лист22	Форма 1-Т «Отчет по труду»
Лист23	Расшифровка формы 2 о выплаченных дивидендах
Лист24	Приложение к форме П-2 «Сведения об инвестиционной деятельности»
Лист25	Лизинговые контракты

 

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

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

Рис 2.1 Схема связи программно-аналитических модулей с модулем информационной базы анализа в среде Microsoft Excel.

 

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

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

§ Лист1 «Анализ средств предприятия»;

§ Лист2 «Анализ состава и структуры внеоборотных активов»;

§ Лист3 «Анализ состава и структуры оборотных активов»;

§ Лист4 «Показатели производства продукции и остатков сырья, материалов и топлива»;

§ Лист5 «Анализ структуры и динамики амортизируемого имущества»;

§ Лист6 «Анализ формирования величины чистых активов организации»;

§ Лист7 «Анализ состава структуры и темпов роста дебиторской задолженности».

На основании методики анализа и концепций структурной организации и хранения информации в Microsoft Excel разрабатываются информационные модели для разделов анализа и общая информационная модель системы комплексного анализа. На рис. 2.2 показан пример информационной модели для раздела «Анализ источников средств предприятия». Стрелки на рис.2.2 указывают, какие листы аналитических модулей связаны с помощью ссылок с листами информационной базы.

Фрагмент общей информационной модели системы комплексного анализа в среде Microsoft Excel представлен на рис.2.3.

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

Рис. 2.2. Информационная модель для раздела «Анализ источников средств предприятия» в среде Microsoft Excel.

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

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

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

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

Рис. 2.3. Фрагмент информационной модели АХД в среде Microsoft Excel

 

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

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

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

Арифметический оператор	Значение
+ (знак плюс)	Сложение
- (знак минус)	Вычитание
/ (наклонная черта, слэш)	Деление
* (Звездочка)	Умножение
% (знак процента)	Процент
^ (крышка)	Возведение в степень

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

 

 

Таблица 2.3. Реализация приемов сравнения в Microsoft Excel

Виды сравнительного анализа	Расчетный показатель	Адрес ячейки размещения расчетного показателя	Запись расчетной формулы в ячейке расчетного показателя	Содержание вычислительной операции
Горизонтальный – вычисление абсолютных и относительных отклонений	Абсолютное отклонение фактического уровня показателя от базового	Е5	=D5 –B5	Число, расположенное в ячейке В5 (базовый) вычитается из числа, расположенного в ячейкеD5 (фактический)
Относительное отклонение фактического уровня от базового	F5	=(D5 –B5)/D5	Разность чисел, расположенных в ячейках D5 и В5 делится на число, расположенное в ячейке D5
Вертикальный – вычисление удельного веса частей в общем целом	Удельный вес показателя	С5	=В5/В10*100	Число из ячейки В5 делится на число в ячейке В10 (итог по столбцу В) и умножается на 100
Динамичекий – вычисление темпов роста и прироста показателей за ряд лет	Темп роста	G5	=D5/B5*100	Число из ячейки D5 делится на число, расположенное в ячейке B5 и умножается на 100
	Темп прироста	H5	=D5/B5*100-100	Число из ячейки D5 делится на число, расположенное в ячейке B5 и умножается на 100 и вычитается 100

 

Операторы логического сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

 

Оператор логического сравнения	Значение
=	Равно
>	Больше
<	Меньше
>=	Больше или равно
<=	Меньше или равно
<>	Не равно

Текстовый оператор используется для обозначения операции объединения последовательностей символов в единую последовательность символов. Текстовый оператор обозначается знаком & (амперсанд).

Другим элементом формул являются ссылки: относительные и абсолютные.

Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

При обработке аналитических таблиц используются некоторые стандартные функции Microsoft Excel:

СУММ - позволяет вычислить сумму нескольких чисел. Например, для получения в ячейке В16 итога чисел, расположенных в столбце В в строчках 1,2,3, …, 15, в ячейку В16 вводится формула =СУММ(В1:В15). Выражение (В1:В16) называют диапазоном данных;

ОКРУГЛ – округляет число в ячейке, например, =ОКРУГЛ;

МАКС – выбирает максимальное значение показателя в диапазоне данных, например, =МАКС(В1:В15);

МИН – выбирает минимальное значение показателя в диапазоне данных, например, =МИН(В1:В15);

СРЗНАЧ – вычисляет среднеарифметическое значение в диапазоне данных, например, =СРЗНАЧ(В1:В15).

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

Защита выполняется в два этапа. Сначала освобождается от защиты область исходных данных. Для этого выделяется область исходных данных и выполняется команда Формат - Ячейки - Защита. В строке «Защищаемая ячейка»убирается флажок. После этого выполняется команда Сервис - Защита - Защитить лист.

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

Заполнение информационной базы может осуществляться следующими способами:

1. ручной ввод данных;

2. импорт данных из других систем;

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

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

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

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

На рис. 2.4. представлена структура спроектированной аналитической таблицы«Анализ средств предприятия» (Модуль «Анализ формирования средств и чистых активов», Книга2 Лист1). Ниже дано описание техники создания этой таблицы.

Шаг1. Формирование заголовка таблицы

Заголовок обычно вводится в ячейку А1. При первоначальной загрузке Microsoft Excelячейка А1 активна (выделена, обрамлена утолщенной линией). В дальнейшем для активизации любой ячейки необходимо ее щелкнуть мышью. На клавиатуре набирается название таблицы. Набираемый текст отражается в строке формул (строка расположена над рабочей областью окна). Если строка формул отсутствует необходимо выполнить команду Сервис - Параметры - Вид и в области Отображать щелкнуть мышью Строку формул и нажать ОК. После набора текста нажимается клавиша Enter.

Шаг2. Формирование шапки таблицы

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

В ячейки от А3 до H3 вводятся названия столбцов. Для корректировки возможных ошибок при вводе служит клавиша F2.

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

 

Рис 2.4. Фрагмент электронной таблицы в режиме формул

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

Шаг 3. Ввод формул и функций

Формулы и функции вводятся в те ячейки, где должны размещаться расчетные показатели. В данной таблице - это диапазон ячеек В7:H37.

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

='F:\[Книга1_ИБ.xls]Лист1Ф.1"Бухг. баланс"'!C8

Формула представляет собой ссылку на статью баланса «Основные средства» на начало года, расположенную в ячейке С8 в Книге1 «Информационная база»наЛисте1"Бухгалтерский баланс" (Рис. 2.5). Аналогичная формула с ссылкой на статью баланса «Основные средства» на конец года (ячейка D8, Книга1) вводится в ячейку D7:

='F:\[Книга1_ИБ.xls]Лист1Ф.1"Бухг. баланс"'!D8

В ячейки С7 и Е7 записываются формулы для расчета удельного веса данной статьи в итоге баланса на начало и соответственно конец отчетного года:

=B7/$B$37*100

=D7/$D$37*100

Знак $ указывает на абсолютный адрес ячейки.

Далее в ячейках F7 и G7 рассчитываются изменения за отчетный год суммы и удельного веса по статье «Основные средства»:

=D7-B7

=E7-C7

Последняя графа таблицы отражает темп роста статей актива баланса. В ячейку H7 вводится формула:

D7/B7*100

 

Рис. 2.5. Лист1 «Информационной базы АХД»

Для ввода остальных формул выполняется копирование каждой введенной в строку 7 (семь) формулы вниз по столбцам методом "перетаскивания" (Указатель мыши устанавливается в правый нижний угол ячейки с формулой. Указатель при этом принимает форму тонкого крестика. Нажав левую клавишу мыши, ее передвигают в нужном направлении).

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

Шаг4. Обрамление таблицы

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

Шаг5. Защита таблицы

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

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

Средством наглядного представления данных являются диаграммы. Они облегчают выполнение сравнений, выявление закономерностей и тенденций данных. Рассмотрим графическое представление структуры первого раздела актива баланса «Внеоборотные активы». Данные для построения диаграммы содержатся в Книге1 «Информационная база»,Лист1"Бухгалтерский баланс" (рис.2.6).

Рис. 2.6. Исходные данные для построения диаграммы

Построение диаграммы выполняется с помощью Мастера диаграмм в интерактивном режиме.

В таблице выделяется область с исходными данными для построения диаграммы. Выделение можно выполнять мышью или клавишной командой Shift + клавиши-стрелки передвижения курсора. Выделение несмежных областей выполняется при нажатой клавише Ctrl.

Выделяемая область должна охватывать ряды анализируемых данных, а также названия столбцов и строк. В нашем примере выделяются диапазоны несмежных ячеек А8:А12иС8:С12.

При нажатии кнопки Мастера диаграмм на экране появится окно Мастер диаграмм (Шаг1 из 4): тип диаграммы. Выбирается тип диаграммы, например, круговая. Такая диаграмма отображает вклад каждого значения в общую сумму. Для перехода от одного шага к другому здесь и в дальнейшем используется кнопка Далее.

На шаге 2 проверяется диапазон ячеек, для которого строится диаграмма и указываются параметры ориентации – Ряды в столбцах.

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

Шаг 4. Определяется место размещения диаграммы - на имеющемся листе или отдельном. Кнопка Готово завершает построение диаграммы (см. рис. 2.7).

Рис. 2.7. Структура внеоборотных активов