Проектирование электронной таблицы.

ЗАДАНИЕ 1

Рассмотрим проектирование электронных таблиц. С этой целью проведем формирование прогноза финансовой деятельности некоторой компании за 5 лет (табл. 3). Условно доход определяется как произведение объема продаж в натуральном выражении на цену реализации. При этом размер прибыли определяется как разность между доходом и суммой расходов данного года.

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

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

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

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

После запуска программы MS Excel на экране открывается чистый рабочий лист, название которого указано внизу рабочего поля (Лист 1).

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

Таблица 3. - Финансовый прогноз деятельности компании

Далее вводятся данные первого года (в нашем примере 2004), которые частично зада­ются как числовые данные (объем продаж, продажная цена), а частично - как формулы. Так, например, в ячейку В5 записывается формула для вычисления дохода =В3*В4, а в ячейку В7 - формула для вычисления прибыли =В5-В6. Одновременно вводятся числовые данные, выражающие прогнозные допущения. Алгоритм расчёта представлен в табл. 4.

Наиболее сложный момент проектирования нашей таблицы - это ввод формул в столбец второго года (2005). Эти формулы учитывают результаты первого года и, кроме того, отражают прогнозные допущения. Так, например, объем продаж в 2005 г. определяет­ся как объем продаж 2004 г., умноженный на процент роста, указанный в прогнозных допу­щениях.

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

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

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

ЗАДАНИЕ 2

Таблица 3. - Расход кормов на привес свиней старше двух месяцев (кормовых единиц)

Алгоритм расчёта представлен в виде формул в самой таблице.

Ход работы:

1. Составление и заполнение таблицы:

· объединили ячейки А1 и А2; Н1 и Н2 объединили (формат – ячейки - выравнивание- объединение ячеек, переносить по словам), вводим данные. выравниваем (формат – ячейки - выравнивание- по центру);

· выделили ячейки В1, С1 и D1, объединили, вводим данные, выравниваем.;

· выделили ячейки Е1, F1 и G1, объединили, вводим данные, выравниваем;

· выделили блок ячеек А1…Н8, создаём границы (формат ячейки – граница - внешние и внутренние);

· вводим данные в таблицу, выравниваем по центру.

2. Ввод формул:

· в ячейку D3 вводим формулу =B3/C3*1000*100 (копируем формулу в ячейки D4 и D5);

· в ячейку G3 вводим формулу =E3/F3*1000*100 (копируем формулу в ячейки G4 и G5);

· в ячейку B6 вводим формулу =(B3+B4+B5)/3 (копируем формулу в ячейки C6, D6 ,E6, F6, G6);

· в ячейку B8 вводим формулу = В6/В7 *100 (копируем формулу в ячейки C8, D8, Е8, F8, G8, Н8);

· в ячейку Н3 вводим формулу =Е3/В3*100 (копируем формулу в ячейки Н4, Н5, Н6, Н7);

· устанавливаем числовой формат ячеек в соответствии с общепринятым в столбце или в строке ( от 0 до 2 десятичных знаков после запятой).

3. Построение графика.

· щелкнуть кнопку Мастер диаграмм на панели инструментов;

· в диалоговом окне Мастер диаграмм- тип диаграммы выбрать объемную гистограмму, щелкнуть кнопку Далее;

· в диалоговом окне Мастер диаграмм - источник данных диаграммы – Далее;

· мастер диаграмм- параметры диаграмм в соответствующем поле ввести с клавиатуры название диаграммы, на вкладке Подписи данных активизировать кнопку Категория и доля, снять флажок Линии выноски и щелкнуть кнопку Далее;

· мастер диаграмм- размещение диаграмм выбрать На отдельном, щелкнуть кнопку Готово. Программа вставит в вашу книгу лист с диаграммой.

Результат выполнения

Таблица 3. - Расход кормов на привес свиней старше двух месяцев (кормовых единиц)

Рисунок 3. - Диаграмма расхода кормов на привес свиней старше двух месяцев.

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

1. Гладкий А.А., Чиртик А.А. Excel. Трюки и эффекты. - СПб.: Питер, 2006.

2. Колупаев И.В. Вопросы и ответы по VBA (Excel): Справочник. - СПб.: Питер, 2004.

3. Макарова Н.В., Матвеев Л.А., Бройдо В.Л. и др. Информатика: учебник - М.: Финансы и статистика, 2004.

4. Мураховский В.И., Евсеев Г.А., Симонович С.В. Информатика; Серия: учебник для ВУЗов. - СПб.: Питер, 2005.

5. Острейковский В.А. Информатика: Серия: учебник для ВУЗов. - М.: Высшая школа, 2005.

6. Попов В.Б. Основы компьютерных технологий. – М.: Финансы и статистика, 2002 - 704 с.