Использование электронных таблиц MS EXCEL для решения экономических задач. Финансовый анализ в Excel (стр. 1 из 3)

Лист-задание

Часть І. Использование электронных таблиц MS EXCEL для решения экономических задач

Задание №1. Создание таблиц, расчет по формулам, построение диаграмм

Порядок выполнения:

1. Согласно варианту создать на рабочем листе таблицу

2. Ввести требуемые в задании данные для расчета

3. Отформатировать таблицу (выделить полужирным шрифтом заголовок таблицы, итоговые показатели и т.д.)

4. Провести расчеты:

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

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

- при помощи Мастера функций рассчитать требуемые статистические показатели представленной таблицы (минимум, максимум, среднее значение)

5. Построить диаграммы:

- по результатам расчетов построить гистограмму с указанием ее названия, наименования строк и столбцов

- круговую по данным одного столбца (или одной строки)

В пояснительной записке к Заданию №1 контрольной работы указать:

1. Таблицу с исходными данными (вариант задания)

2. Формулы excel со ссылками на ячейки, по которым производится расчет

3. Таблицу с результатами расчетов

4. Этапы построения диаграмм

5. Построенные диаграммы

Задание № 2. Подведение динамических итогов с использованием сводных таблиц

Порядок выполнения

1. Согласно варианту создать на рабочем листе исходную таблицу

2. Построить сводную таблицу (выделить исходную таблицу и выбрать пункт меню «данные-сводная таблица»)

3. Изменить структуру сводной таблицы (выбрав пункт «макет» мастера создания сводных таблиц)

В пояснительной записке к Заданию №2 контрольной работы указать:

1. Исходную таблицу (вариант задания)

2. Этапы построения сводной таблицы (в обязательном порядке макет сводной таблицы)

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

4. Порядок изменения структуры сводной таблицы

5. Измененную сводную таблицу

Задание № 3. Использование процедуры «Поиск решения»

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

Порядок выполнения:

1. На рабочем листе создать таблицу с данными о товарах, согласно варианту, и провести расчет по приведенным формулам

2. Запустить на компьютере процедуру «Поиск решения» (меню сервис- Поиск решения)

3. В открывшемся диалоговом окне указать необходимые ссылки:

- целевая ячейка - суммарная прибыль (максимальное значение). Эта ячейка должна содержать формулу

- изменяя ячейки – кол-во товаров каждого вида; нужно указать диапазон ячеек, в которых после выполнения процедуры «Поиск решения » будет получено значения оптимальных выпусков.

- ПРИМЕЧАНИЕ: этот диапазон используется для получения формул в целевой ячейке и ячейках ограничений;

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

4. Выполнить процедуру «Поиск решения»

В пояснительной записке к Заданию №3 контрольной работы указать:

· таблицу с данными о товарах

· провести расчет по формулам

· указать параметры процедуры «Поиск решения» с адресами ячеек (диалоговое окно процедуры Поиск решения)

таблицу с результатами выполненной процедуры «Поиск решения»

Задание № 4. Регрессионный анализ данных

Найти уравнение эмпирической зависимости в виде: Y = a X + b и построить ее график по данным таблицы

Порядок выполнения:

1. На рабочем листе создать таблицу согласно варианту

2. Построить точечную диаграмму

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

В пояснительной записке к Заданию №4 контрольной работы указать:

1. Исходную таблицу (вариант задания)

2. Диаграмму и этапы ее построения

3. Добавление линии тренда

4. Уравнение линии тренда

Часть ІІ. Финансовый анализ в Excel

Задание №1

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

Задание № 2

Используя функцию “Подбор параметра” создать систему для начисления сложных процентов при заданной сумме выплаты.

Задание №3. Таблицы подстановки с одной переменной

Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки.

Задание № 4. Таблицы данных с двумя переменными

Используя функцию “Таблицы подстановки” создать систему для прогнозирования суммы выплаты при вариации процентной ставки и суммы вклада.

Пояснительная записка

Часть І. Использование электронных таблиц MS EXCEL для решения экономических задач

Задание №1. Создание таблиц, расчет по формулам, построение диаграмм

1. Вариант 8. Учет затрат на вспомогательные материалы

Наименование материала	Цена (Ц)	Количество			Сумма на конец месяца (С)	Доля в общем объеме (Д)
		На начало месяца (НМ)	На конец месяца (КМ)	Израсходовано (И)
Краска	12,50	90	50
Лак	28,30	60	10
Бензин	2,00	140	60
Керосин	1,80	90	30
Эмаль	16,30	75	80
Ацетон	11,80	60	15
Итого	Х	Х	Х	Х
Средняя сумма расхода						Х
Минимальная доля в общем объеме

И=КМ-НМ С=Ц*КМ Д=С/(Итого С) * 100

2. Расчеты в таблице производились по следующим формулам Еxcel со ссылками на ячейки:

- Израсходовано (И): Е6 =D6-C6;

- Сумма на конец месяца (С): F6 =B6*D6;

- Доля в общем объеме (Д): G6 =(B6/F6)*100;

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

- выберем пункт меню Вставка – Функция, откроется окно «Мастер функций» (рис.1).

Рисунок 1. – Мастер функций

- из списка функций выберем СРЗНАЧ и нажмем кнопку «Ок». В поле «Число 1» укажем диапазон ячеек, среди значений которых нужно найти среднее. Формула будет иметь вид: G13=СРЗНАЧ(F6:F11)

Аналогично рассчитаем минимальную долю в общем объеме, воспользуясь функцией МИН. Формула будет иметь вид: G14= =МИН(G6:G11)

3. Таблица с результатами расчетов:

Наименование материала	Цена (Ц)	Количество			Сумма на конец месяца (С)	Доля в общем объеме (Д)
		На начало месяца (НМ)	На конец месяца (КМ)	Израсходовано (И)
Краска	12,5	90	50	-40	625	2,00
Лак	28,3	60	10	-50	283	10,00
Бензин	2	140	60	-80	120	1,67
Керосин	1,8	90	30	-60	54	3,33
Эмаль	16,3	75	80	5	1304	1,25
Ацетон	11,8	60	15	-45	177	6,67
Итого	Х	Х	Х	Х	2563
Средняя сумма расхода						427,17
Минимальная доля в общем объеме						1,25

4. Этапы построения диаграмм

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

- укажем диапазон ячеек (F5:G10), по которым нужно построить диаграмму, выберем пункт Вставка – Диаграмма Откроется окно «Мастер диаграмм» (рис.2);

Рисунок 2. - Мастер диаграмм

- выберем тип Гистограмма и нажмем кнопку Далее. В закладке Ряд в поле Ряд напишем названия рядов 1 и 2 и нажмем кнопку Готово;

- нажмем правую кнопку мыши, удерживая курсор мыши наведенным на простроенную диаграмму;

- из пунктов меню выберем Параметры диаграммы, откроется окно (рис.3);

- выберем закладку Заголовки и укажем название, наименование строк и столбцов диаграммы.

Рисунок 3. - Параметры диаграммы

5. Построенная гистограмма будет иметь вид:

Рисунок 4. - Гистограмма

Для построения круговой гистограммы выберем пункт меню Вставка – Диаграмма и тип Круговая, после чего нажмем кнопку Готово. Аналогичным образом укажем название круговой диаграммы (рис.5).

Рисунок 5. – Круговая диаграмма

Задание №2. Подведение динамических итогов с использованием сводных таблиц

1. Вариант 8.

Поставляемое изделие	Тип транспорта	Расстояние	Стоимость перевозки
Нефть	Морской	1000-5000	14000
Зерно	ж/д	до 1000	7000
Агрегат	воздух	свыше 5000	3100
Нефть	ж/д	до 1000	10000
Зерно	морской	1000-5000	5400
агрегат	воздух	свыше 5000	15600

2. Этапы построения сводной таблицы: