Проектирование ЭТ

Технология работы с электронными таблицами

Хотя работа в каждой ЭТ имеет свои особенности, можно говорить о некоторой обобщенной (усредненной) технологии работы с ней. Изобразим технологию работы в виде схемы (рис. 5.4.1).

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

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

Математические модели помогают пользователю на основе имеющейся таблицы получить новую информацию решением таких задач компьютерного моделирования: «Что, ес

Рис. 5.4.1. Обобщенная технология работы с ЭТ

 

ли?», анализ чувствительности и др. Решение задач типа: «Что будет, если?» - дает возможность узнать, как изменятся выходные параметры при изменении одной или нескольких входных величин (условий). Расширением таких задач являются задачи анализа чувствительности, позволяющие определить, как изменится решение модели при изменении одной или нескольких входных величин с заданным шагом в определенном диапазоне значений. Обратной по отношению к задаче «Что будет, если?» - является задача «Как сделать, чтобы?». Она возникает в случае, если целью является достижении определенного значения модели, и ищутся значения входных параметров, обеспечивающих достижение этой цели. Различные виды анализа данных, содержащихся в исходной таблице можно проводить с использованием встроенных функций и процедур. Так, входящие в состав ЭТ статистические функции могут использоваться в статистическом анализе или для прогноза содержащихся в таблице данных. Использование финансовых функций позволяет произвести анализ эффективности планируемых капиталовложений, рассчитать стоимость ценных бумаг или размер амортизационных отчислений. Для решения оптимизационных задач используются специальные модели математического программирования.

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

Иногда при работе с большими ЭТ требуется найти ту или иную строку (столбец) или отсортировать строки (столбцы) в нужном порядке. Для этого в ЭТ предусмотрен ограниченный программный инструментарий СУБД, позволяющий манипулировать строками и столбцами как компонентами БД.

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

Этап 4 обеспечивает вывод результирующих данных на печать. При этом результаты могут быть распечатаны в табличном виде или в виде графических диаграмм.

Проектирование ЭТ рассмотрим на примере форсирования прогноза финансовой деятельности некоторой компании за 5 лет.

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

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

 

	A	B	C	D	E
	Объем продаж, шт.
	Цена	$2.00	$2.10	$2.21	$2.32
	Доход	$20000	$24780	$30702	$30702
	Расходы	$15000	$15750	$16537	$81364
	Прибыль	$5000	$9030	$14165	$20676
	Прогнозные допущения
	Рост объема продаж	18,00%
	Рост цен	5,00%

 

Эти данные расположены в нижнем левом углу таблицы.

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

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

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

Создание ЭТ начинается с ввода названий столбцов и строк.

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

 

	A	B	C	D	E
	Объем продаж, шт.		=(1+$B$10)*B3	…	…
	Цена	$2.00	=(1+$B$11)*B4
	Доход	=B3*B4	=C3*C4
	Расходы		=(1+$B$11)*B6
	Прибыль	=B5-B6	=C5-C6
	Прогнозные допущения
	Рост объема продаж	18,00%
	Рост цен	5,00%

 

Далее вводятся данные первого года (в нашем примере 2006), которые частично задаются как числовые данные (объем продаж, продажная цена), а частично – как формулы. В ячейку В5 записываем формулу для вычисления дохода = B3*B4, а в ячейку В7 – формулу для вычисления прибыли =B5-B6. И вводим числовые данные, выражающие прогнозные допущения.

Далее вводим формулы в столбец второго года (2007). Эти формулы учитывают результаты первого года и, кроме того, отражают прогнозные допущения. Так, объем продаж в 2007 году определяется как объем продаж в 2006 году, умноженный на процент роста, указанный в прогнозных допущениях.

Объем продаж (2007) = (1+%роста)* объем продаж (2006)

 

ячейка В3

относительный адрес

ячейка В10

абсолютный адрес

ячейка С3

формула:

=(1+$B$10)*B3

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

Пересчет остальных параметров из столбца В в столбец С выполняется аналогичным образом.

Остальные столбцы (D, E, F) заполняются простым копированием формул, содержащихся в столбце С. Команда копирования, при этом автоматически подстроит содержащиеся в них относительные адреса ячеек.

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