Создание расчетных таблиц. (ввод в ячейки таблицы текста, чисел, формул. Форматирование ячеек. Относительные и абсолютные адреса ячеек. Имена ячеек. )

Практические занятия

Практическое занятие 1.

Создание расчетных таблиц. (Ввод в ячейки таблицы текста, чисел, формул. Форматирование ячеек. Относительные и абсолютные адреса ячеек. Имена ячеек.)

Задание 1.

1. Ввести таблицу расчета реализации продукции (рис. 112).

 

 

Рис. 112

 

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

Задание 2.

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

 

 

Рис. 113

Задание 3.

Ввести таблицу расчета (рис. 114) и выполнить указания, приведенные в задании. Для значений НДС и НСП введите отдельные ячейки и присвойте им имена. Эти ячейки с именами затем используйте в расчетных формулах.

 

 

Рис. 114

 

Задание 4.

 

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

Основная формула теории процентов определяет будущую стоимость денег:

,

где P - настоящее значение вложенной суммы денег,

F - будущее значение стоимости денег,

n - количество периодов времени, на которое производится вложение,

r - норма доходности (прибыльности) от вложения.

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

Пусть Р=100 руб, r=0,1 (10 %), n=5 лет. Составить расчетную таблицу и рассчитать значения F1, F2, F3, F4, F5 и определить, какой процент от начальной суммы в 100 руб составляет доход за пять лет.

 

Практическое занятие 2. Создание диаграмм и графиков.

Задание 1.

1.Ввести исходные данные об объёме строительных работ (рис. 115).

 

Рис. 115

 

2.Создать круговую и точечную диаграммы.

3.Создать график, добавить линию тренда и уравнение.

 

Задание 2.

 

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

Прибыль определяется по формуле

 

(1)

где - доход от продаж (валовый доход),

- суммарные затраты.

Доход от продаж рассчитывается по формуле

 

, (2)

 

где - цена изделия,

- количество проданных изделий (объем продаж).

Суммарные затраты рассчитываются по формуле

 

, (3)

 

где - постоянные затраты (не зависят от объема продаж), =200000р.

- переменные затраты (пропорциональны объему продаж).

Переменные затраты рассчитываются по формуле

 

, (4)

 

где - затраты на покупку материалов,

- затраты на сдельную оплату труда.

Считаем, что затраты на материалы рассчитываются по формуле

 

, (5)

 

где - коэффициент, учитывающий долю затрат на материалы в цене изделия,

Считаем, что затраты на сдельную зарплату рассчитываются по формуле

 

, (6)

 

где - коэффициент, учитывающий долю затрат на зарплату в цене изделия.

 

Составить таблицу расчета (рис. 115а), в ячейки В9-В14 ввести расчетные формулы.

 

Рис. 115а

 

Выделить диапазон ячеек В5-В14 и скопировать их в диапазон ячеек С5-Н14. Выполнить расчеты для заданной серии значений объема продаж. Результаты расчетов приведены на рис. 115б.

 

 

Рис. 115б

 

3. Выделить диапазон ячеек А4-Н14 и для него, используя мастер диаграмм и выбирая точечную диаграмму, построить графики.

4. Удалить лишние графики, оставив только следующие графики: постоянные затраты, переменные затраты, суммарные затраты, валовый доход как функции объема продаж.

Диаграмма приведена на рис. 115в

 

Рис. 115в

 

5. Определить по графикам объем продаж, при котором валовый доход равен суммарным затратам (точка безубыточности).

Данный лист с именем Лист1 переименовать в Анализ безубыточности.

 

Задание 3.

После построения расчетной модели и проведения расчетов часто возникает необходимость в анализе влияния исходных данных на выходные результаты. Такие расчеты введены в качестве самостоятельных этапов в программах для разработки бизнес-планов (например, ProjectExpert) и программах анализа финансового состояния предприятия (напрмер, AuditExpert). Эти этапы называют обычно анализом чуствительности.

Изменяя поочередно в некоторых пределах, например, от -15% до +15%, исходные данные относительно их базового значения, рассчитывают соответствующие отклонения выходных величин от их базового значения. Для сравнительного анализа влияния исходных данных на выходную величину необходимо расположить все графики на одном рисунке, чтобы можно было выделить величины слабо или сильно влияющие на выходную величину.

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

 

 

 

 

Лист Анализ безубыточности (2) переименуем в лист Анализ чувствительности и изменим заголовок таблицы. В качестве опорной (базовой) точки выберем точку с объемом продаж, равном 3000 единиц. Скопируем содержимое ячейки F4 (q=3000) в ячейки B4:H4 и выделим желтым цветом столбец Е (точнее, диапазон E4:E15)(см. рис. 115г)

 

 

Рис. 115г

 

Будем изменять сначала объем продаж q в пределах от -15% до +15% от базового значения в 3000 единиц и посмотрим, в каких пределах будет изменяться прибыль. Расчеты приведены на рис. 115д.

 

 

Рис. 115д

 

В ячейках D4, C4, B4, F4, G4, H4 введены соответственно формулы E4*0,95, E4*0,9, E4*0,85, E4*1,05, E4*1,1, E4*1,15. Изменения прибыли в таблице приведены в абсолютных единицах, а нам необходимо получить результаты в относительных единицах, в % от базового значения прибыли. Для этого присвоим ячейке Е14 имя - базовое_значение - и в ячейку D15 введем формулу (D14-базовое_значение)/базовое_значение*100. Скопируем эту формулу в ячейки B15, C15, F15, G15, H15. Результаты приведены на рис. 115е.

 

 

Рис. 115е

 

То есть, изменение входной величины q в пределах от -15% до +15% приводит к изменению выходной величины – прибыли - в пределах от -30% до +30 %.

Такой же анализ необходимо провести для входных величин p, Км, Кзп. При изменении одного параметра все остальные входные параметры остаются неизменными и равными базовым значениям (базовые значения приведены в желтом столбике).

Для того, чтобы сохранить результаты расчетов, соответствующие изменению величины q, необходимо выделить диапазон ячеек B15:H15 с результатами расчетов, выбрать команду Копировать, затем выделить ячейки В17:Н17, выбрать команду Специальная вставка и вставить Значения. (см. Рис. 115ж).

 

 

Рис. 115ж

 

Поскольку эти результаты соответствуют изменению объема продаж q скопируем содержимое ячейки А4 в ячейку А17 (рис. 115з)

 

 

Рис. 115з

 

Для дальнейших действий скопируем диапазон базовых значений исходных данных в ячейки J4:J14.

 

 

Затем выполним следующие действия:

Выделим и скопируем содержимое ячеек с формулами B4:H4 в ячейки B5:H5.

В ячейку Е5 введем базовое значение р =500 (из ячейки J5).

Восстановим базовые значения объема продаж q = 3000 в ячейках В4:Н4.

Скопируем результаты расчетов в ячейки В18:Н18 ( только значения ).

Скопируем содержимое ячейки А5 в ячейку А18.

Эти действия необходимо повторить для величин Км и Кзп. Результаты этих действий показаны на рис. 115и.

 

 

 

Рис. 115и

 

В ячейки В16:Н16 вставлены значения отклонений.

По результатам расчетов, представленных в ячейках А16:Н20, построим графики (рис. 115к)

 

Рис. 115к

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

 

Задание 1.

 

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

При продаже товаров на сумму до 150 тыс. руб. менеджер получает комиссионные в размере 5% от объема продаж, а при продаже на сумму более 150 т.р. – 6% от объема продаж. При выполнении задания присвоить имена ячейкам В2, В3, В4 и ввести их в соответствующие формулы.

 

 

Рис. 116

Задание 2.

Ввести расчетную таблицу (рис. 117) и определить оценку при решении теста при условии, что более 25 правильных ответов оценивается в 5 баллов, более 20 – 4 балла, менее 20 – 3 балла.

 

 

Рис. 117

 

Скорректировать расчетную формулу, дополнив ее условием для оценки в 2 балла, если количество правильных ответов меньше или равно15.

 

Задание 3.

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

Рис. 118

 

Добавить справа столбец с именем Количество и столбец с именем Стоимость. В столбец Количество ввести произвольные значения как больше 5, так и менее 5 шт. Для расчета стоимости как произведения цены на количество использовать функцию ЕСЛИ.

 

Задание 4.

 

Создать в Excelисходную таблицу (рис. 119) и таблицу результатов ее обработки (рис. 120).

 

Рис. 119.

 

 

Рис. 120.

 

В ячейку G4 ввести формулу для расчета максимального количества осадков (функция МАКС) за три года. В ячейку G5 ввести формулу для расчета минимального количества осадков за три года (функция МИН). Функции МАКС и МИН содержатся вразделе статистических функций. В ячейку G6 ввести формулу для расчета суммарного количества осадков за три года (функция СУММ). Функция СУММ содержится в разделе математических функций. В ячейку G7 ввести формулу для расчета среднего количества осадков за три года (суммарное количество осадков разделить на общее число месяцев).

В ячейку G8 ввести формулу для расчета количества засушливых месяцев за три года (в засушливом месяце осадков меньше 10 мм). Для расчета количества засушливых месяцев за три года использовать формулу СЧЕТЕСЛИ (интервал, критерий). Критерием является условие: < 10.

Функция СЧЕТЕСЛИ содержится в разделе математических функций.

Для расчета количества месяцев в пределах >20 и <80 и вне нормы <10 и >100 нужно создать две вспомогательные таблицы.

 

Рис. 121.

 

Первая таблица показана на рис. 121. В ячейку таблицы В18 ввести логическую функцию ЕСЛИ (И(B6>20;B6<80);1;0).

Функция имеет следующий смысл: Логическое условие И объединяет два неравенства ( B6>20 и B6<80), которые должны выполняться одновременно. Если они оба выполняются, то в ячейку заносится значение 1. В противном случае - 0.

Скопировать содержимое ячейки В18 в остальные ячейки области В18:D29. В ячейку G9 ввести формулу СЧЁТЕСЛИ(B18:D29;"=1"). Будет подсчитываться количество ячеек, в которых значение равно 1 (т.е. количество месяцев, когда осадки в пределах >20 и <80).

Самостоятельно создать вторую вспомогательную таблицу, ввести в нее необходимую логическую функцию для определения месяцев вне нормы (условие <10 и >100). Эти условия должны объединяться логическим условием или. ВячейкуG10 ввести формулу аналогично ячейке G9 с ссылкой на вторую вспомогательную таблицу.

 

Задание 1.

произвести сортировку данных по алфавиту в первом столбце таблицы;

произвести сортировку данных в таблице по возрастанию диаметра.

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

Задание 2.

сиспользованием автофильтра осуществить поиск планет, начинающихся на букву «С» или «Ю» с массой менее 600 * 10²⁴ кг;

сиспользованием автофильтра осуществить поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 4*10²⁴ кг;

сиспользованием автофильтра осуществить поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3 * 10²⁴ кг до 500 * 10²⁴ кг, а также не более 2 спутников;

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

Задание 3.

сиспользованием усиленного фильтра осуществить поиск планет, имеющих период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км;

сиспользованием усиленного фильтра осуществить поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих один спутник.

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

Задание 4.

с использованием формы удалить и вставить информацию по планете Нептун;

с использованием формы осуществить поиск планеты, имеющей период обращения 11,86 земных лет;

вставить форму поиска указанной в предыдущем пункте планеты в документ Word.

Задание 1.

На основе приведенных данных за январь - март (рис. 123)

 

Январь

 

Февраль

 

 

Март

 

 

Рис. 123

 

выполнить консолидацию и получить приведенный ниже результат (рис. 124).

 

 

Рис. 124

Задание 2.

На основании исходных данных, приведенных ниже в таблице (рис. 125), создать сводную таблицу:

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

 

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

 

 

Рис. 125

Задание 1. Расчет ипотечной ссуды.

1.Создать таблицу для расчета ипотечной ссуды (рис. 126) и расчитать сумму первого взноса, размер ссуды, месячную выплату по ссуде, общую сумму выплат и сумму комиссионых. При выполнении задания присвоить имена ячейкам С6, С7, С9, С10, С11, С12, С13 и ввести их в соответствующие формулы.

 

 

 

Рис. 126

 

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

 

,

 

а на оставшуюся сумму берет ссуду в банке под некоторую годовую процентную ставку на некоторый срок (срок погашения ссуды), т.е.

 

 

Ежемесячно покупатель выплачивает банку определенную постоянную сумму, которая определяется с помощью функции ПЛТ(ставка; кпер; пс; бс; тип), т.е

 

,

 

Эта месячная плата включает в себе основные платежи и платежи по процентам. Аргументы функции ПЛТ имеют следующий смысл:

Ставка — годовая процентная ставка. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

Кпер — общее число выплат по ссуде (срок погашения ссуды). Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48.

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

 

 

Бс — требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), что соответствует нашему случаю.

Тип — число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

 

Тип	Когда нужно платить
0 или опущен	В конце периода
	В начале периода

 

В нашем случае выплата производится в конце периода (в конце каждого месяца). В итоге обращение к функции ПЛТ(…) будет иметь вид .

Общая сумма выплат, включающая основные платежи и платежи по процентам, равна

 

 

Сумма комиссионных, которую получит банк, равна

 

 

После проведения расчетов создать следующие сценарии (рис. 127):

 

 

	Сценарии
Срок погашения ссуды, мес.
Годовая процентная ставка

 

 

Рис. 127

 

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

4. Подобрать параметры срока погашения ссуды при месячной плате 1500.

Задание 2. Расчет прибыли.

1.Создать таблицу для расчета прибыли (рис. 128). При выполнении задания присвоить имена ячейкам В3, В4, В5, В17 и ввести их в соответствующие формулы.

 

 

Рис. 128

 

Постановка задачи. Предприятие изготавливает и продает три вида изделий: изделие1, изделие 2, изделие 3. Затраты предприятия на изготовление изделия складываются из затрат на оплату работ и затрат на оплату материалов. Эти затраты предприятия определяют себестоимость изделия. Количество нормо-часов и единиц материалов на каждое изделие известно. Известны также стоимость нормо-часа работы и стоимость единицы материала. Чтобы предприятие получило прибыль от продажи изделий вводится торговая надбавка, равная некоторой доли от себестоимости. Отпускная цена на изделие определяется как себестоимость плюс торговая надбавка.

Расчетные формулы имеют следующий вид:

Себестоимость го изделия определяется по формуле:

 

 

где себестоимость го изделия,

стоимость единицы материала для го изделия,

количество единиц материала для го изделия.

стоимость нормо-часа работы по му изделию,

количество нормо-часов работы по му изделию.

Торговая надбавка для го изделия определяется по формуле

 

 

где абсолютное значение торговой надбавки для го изделия,

относительное значение торговой надбавки для го изделия, в % от себестоимости.

Отпускная цена на ое изделие опреляется по формуле

 

.

 

Прибыль по му (одному) изделию рассчитывается по формуле

 

.

 

Прибыль на изготовленные ые изделия рассчитывается по формуле

 

,

 

где количество изготовленных изделий го типа.

Суммарная прибыль по изготовленным изделиям всех типов определится по формуле

 

.

 

Необходимо создать таблицу для расчета прибыли (рис. 128). Ввести в ячейки необходимые формулы и провести расчет себестоимости, отпускной цены, прибыли на одно изделие и суммарной прибыли по каждому изделию и по всем трем изделиям. Присвоить имена ячейкам В3, В4, В5, в которых размещены исходные данные, и ячейке В17, в которой размещена выходная величина – сумма прибыли по всем изделиям.

 

2. Создать следующие сценарии (рис. 129):

 

	Сценарии
Стоимость нормо-часа работы
Стоимость единицы материалов

 

Рис. 129

 

3. Получить отчет по сценарию в виде структуры с отражением результатов: сумма прибыли (ячейка В5).

4. Подобрать параметры стоимости нормо-часа работы при сумме прибыли по всем изделиям, равной 10000 руб.

Задание 3.

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

 

 

 

Поиск решения.

Задание 4. Расчет оптимальной реализации продукции.

1. Создать таблицу расчета реализации (рис. 130). Рассчитать Столбец Сумма как произведение Количества на Цену. В ячейке В8 подсчитать сумму по столбцу Количество.

 

 

Рис. 130

 

2.Установить целевую ячейку, изменяемые ячейки, ограничения (рис. 131).

 

 

Рис. 131

3. Выполнить поиск решения и получить отчет.

Результаты оптимизации (поиска решения) приведены на рис. 131а

 

 

Рис. 131а

 

 

Задание 5. Расчет оптимальной цены реализации продукции.

В примере из задания 2 занятия 1, ввести зависимость между объемом продаж и ценой изделия (такая зависимость называется функцией спроса). Она имеет вид

q=b-a*p, где a=20, b=30000 (см. рис. 131б)

 

 

 

 

Рис. 131 б.

 

Если задать диапазон изменения цены в пределах от 300 до 1300, то получим оптимальную цену изделия, при которой прибыль будет максимальной. Графически эта зависимость представлена на рис. 131в.

 

 

Рис. 131 в.

 

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

 

Практические занятия

Практическое занятие 1.

Создание расчетных таблиц. (Ввод в ячейки таблицы текста, чисел, формул. Форматирование ячеек. Относительные и абсолютные адреса ячеек. Имена ячеек.)

Задание 1.

1. Ввести таблицу расчета реализации продукции (рис. 112).

 

 

Рис. 112

 

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

Задание 2.

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

 

 

Рис. 113

Задание 3.

Ввести таблицу расчета (рис. 114) и выполнить указания, приведенные в задании. Для значений НДС и НСП введите отдельные ячейки и присвойте им имена. Эти ячейки с именами затем используйте в расчетных формулах.

 

 

Рис. 114

 

Задание 4.

 

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

Основная формула теории процентов определяет будущую стоимость денег:

,

где P - настоящее значение вложенной суммы денег,

F - будущее значение стоимости денег,

n - количество периодов времени, на которое производится вложение,

r - норма доходности (прибыльности) от вложения.

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

Пусть Р=100 руб, r=0,1 (10 %), n=5 лет. Составить расчетную таблицу и рассчитать значения F1, F2, F3, F4, F5 и определить, какой процент от начальной суммы в 100 руб составляет доход за пять лет.