Методические указания и задания для практических занятий Москва 2005

Московский государственный университет

путей сообщения
(МИИТ)

Кафедра «Экономика и управление на транспорте»

А.В. Шобанов, И.А. Епишкин, Е.В. Струкова

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

В ЭКОНОМИКЕ

Методические указания и задания

для практических занятий

Москва – 2005

Московский государственный университет

путей сообщения (МИИТ)

Кафедра «Экономика и управление на транспорте»

А.В. Шобанов, И.А. Епишкин, Е.В. Струкова

Утверждено

редакционно-издательским

Советом университета

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ЭКОНОМИКЕ

Методические указания и задания для практических занятий

для студентов экономических специальностей

Москва – 2005

УДК 330

Ш - 78

Информационные технологии в экономике. Методические указания и задания для практических занятий для студентов экономических специальностей / Шобанов А.В., Епишкин И.А., Струкова Е.В. – М.: МИИТ, 2005. - 36 с.

Данное издание содержит методические рекомендации и задания для выполнения индивидуальной работы в семестре на практических занятиях студентами кафедры «Экономика и управление на транспорте».

Ó Московский государственный университет путей сообщения (МИИТ), 2005

СОДЕРЖАНИЕ

ВВЕДЕНИЕ..............................................................................................................4

1. ЗАДАНИЯ ДЛЯ ПРАКТИЧЕСКИХ ЗАНЯТИЙ..............................................5

1.1. Задание № 1………………...………………………………………….......5

1.2. Задание № 2……..........................................................................................6

1.3. Задание № 3..................................................................................................8

1.4. Задание № 4……………………..................................................................9

1.5. Задание № 5……………………….............................................................10

1.6. Задание № 6.................................................................................................12

1.7. Задание № 7.................................................................................................13

1.8. Задание № 8………………………………….……………………………15

1.9. Задание № 9…………………………….…………………………............16

1.10. Задание № 10……………………………..………………………...........19

1.11. Задание № 11………………………………….…………………………22

2. МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ

ЗАДАНИЙ В СРЕДЕ MS EXCEL………………………………………………27

ВВЕДЕНИЕ

Данные методические указания предназначены для студентов, обучающихся на кафедре «Экономика и управление на транспорте» института экономики и финансов (ИЭФ) МИИТа.

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

Предлагаемые задания ориентированы на использование средств электронных таблиц Microsoft Excel.

1. Задания для практических занЯтий

1.1. Задание № 1

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

Таблица 1

Структура привлечённых средств коммерческого банка

Привлеченные средства коммерческого банка	Сумма млн. руб.	Уд. вес, %
Депозиты государственных предприятий	2 000
Вклады населения	4 000
Депозиты СП	700
Депозиты внебюджетных фондов	1 000
Депозиты фермерских хозяйств	850
Депозиты АО и ООО	1 200
Депозиты ИЧП	900
Остатки на расчётных и текущих счетах клиентов	8 000
Депозиты юридических лиц в валюте	5 000
ИТОГО

2. Выполнить сортировку документа:

- по убыванию объёмов привлеченных средств коммерческого банка;

- по возрастанию наименований привлеченных средств.

3. Построить на отдельном рабочем листе круговую диаграмму, отражающую структуру сумм привлечённых средств в виде соответствующих секторов. Показать на графике процентное соотношение привлечённых средств, вывести легенду и название графика "Структура привлечённых средств коммерческого банка".

4. С помощью средства "Автофильтр" на отдельном листе выполнить фильтрацию сформированного документа, оставив в нём:

- только те привлеченные средства коммерческого банка, объём которых больше 1 млрд. руб. Вернуть документ в исходный вид.

- только депозиты коммерческого банка. Вернуть документ в исходный вид;

5. На основании исходного документа "Структура привлечённых средств КБ" рассчитать и сформировать следующий документ только для депозитных средств банка:

Таблица 2

Анализ привлеченных средств коммерческого банка

Расчётная величина	Значение
Средняя величина всех депозитных средств
Количество всех привлеченных средств банка
Максимальная величина депозитных средств
Минимальная величина всех привлеченных средств банка

1.2. Задание № 2

1. На ЛИСТЕ 1 сформировать и заполнить ведомость (табл. 3).

2. Скопировать данную ведомость на ЛИСТ2-ЛИСТ3 и в "групповом режиме" рассчитать:

ОС=БС - И

ВС=БС * k

где ОС – остаточная стоимость основных фондов, млн.руб.;

БС – балансовая стоимость основных фондов, млн.руб.;

И – износ основных фондов, млн.руб.;

ВС – восстановительная стоимость основных фондов, млн.руб.;

k – коэффициент переоценки стоимости основных фондов.

Если балансовая стоимость объекта превышает 500 млн. руб. (БС>500), то коэффициент переоценки равен трем (k = 3). Если БС<500, то k = 2.

3. Переименовать ЛИСТ 1 в ВЕДОМОСТЬ; ЛИСТ 2 в ИТОГИ; ЛИСТ 3 в РАСШ_ФИЛЬТР.

4. В групповом режиме добавить в таблицу (во все листы) новую графу (после графы "наименование объекта") "Вид объекта" и присвоить всем объектам Цех №1-Цех №4 вид основной, а всем остальным объектам – вспомогательный.

Таблица 3

Переоценка основных средств, млн. руб.

Наименование объекта	Балансовая стоимость	Износ	Остаточная стоимость	Восстановительная стоимость
Заводоуправление	1576,2	568,0
Диспетчерская	176,0	45,4
Цех № 1	710,2	120,3
Цех № 2	804,6	240,0
Цех № 3	933,0	150,2
Цех № 4	474,4	174,5
Склад № 1	570,5	221,2
Склад № 2	430,4	92,2
Склад № 3	564,9	118,0
Склад № 4	320,5	87,5
Итого

5. На ЛИСТЕ ИТОГИ рассчитать общую (суммарную) балансовую и остаточную стоимость всех основных и всех вспомогательных объектов с помощью команды "Итоги" меню "Данные" (сначала отсортировать таблицу по возрастанию видов объекта).

6.
На ЛИСТЕ РАСШ_ФИЛЬТР с помощью команды расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых более 500 млн. руб. Поместить в новый документ графы "Наименование объекта", "Балансовая стоимость", "Остаточная стоимость".

1.3. Задание № 3

1. Составить балльные оценки критериев, используемых при составлении рейтинга пяти транспортных компаний в соответствии со шкалой: меньше 400 - 4, от 400 до 600 - 6, от 600 до 800 - 8, больше 800 - 10. В результате решения задачи необходимо сформировать следующий выходной документ:

Таблица 4

Результаты рейтинга транспортных компаний

Наименование показателя	Рейтинговые оценки критериев					Балльные оценки критериев
	1	2	3	4	5	1	2	3	4	5
Перечень работ, услуг	900	700	700	800	650
Стоимость работ, услуг	856	479	454	520	300
Объемы работ, услуг	880	469	660	555	420
Квалификация	856	618	422	720	620
Техническая, технологическая и информационная оснащенность	635	638	479	440	643
Финансовое состояние	866	612	764	860	654
Репутация	800	91	164	700	753
Прочие показатели	816	220	291	668	247

2. Закрепить область шапки таблицы.

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

4. По критерию "Репутация" определить ранг каждой из компаний, заполнив табл. 5. Значение критерия скопировать из табл. 4, используя команду СПЕЦИАЛЬНАЯ ВСТАВКА меню ПРАВКА.

Таблица 5

Репутация транспортных компаний

Компания	Значение критерия	Ранг
1
2
3
4
5

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

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

1.4. Задание № 4

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

- ликвидные активы - LA(i);

- суммы остатков на расчётных, текущих счетах, вкладов и депозитов - C(i);

- общая сумма активов банка - A(i);

- обязательства банка по счетам до востребования - OB(i);

где i - количество рассматриваемых периодов.

Формулы для расчёта:

H5(i)=LA(i)/C(i); H6(i)=LA(i)/A(i); H7(i)=LA(i)/OB(i).

В результате решения задачи необходимо сформировать следующий документ:

Таблица 6

Показатели ликвидности баланса коммерческого банка, тыс. руб.

Показатель	1 января	1 февраля	1 марта	1 апреля	1 мая
LA(i)	1618170	3313380	976490	316790	3587380
C(i)	1597240	3174570	4275760	3754220	6988760
A(i)	3379790	14367540	14237870	9812660	13063760
OB(i)	669210	4412760	9023420	7856240	5876750
H5(i)
H6(i)
H7(i)

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

3. На отдельном листе выполнить фильтрацию таблицы, оставив в ней только показатели ликвидности и выходные показатели баланса банка на 1 мая.

4. На отдельном листе построить гистограмму ликвидных активов. На том же графике выполнить прогноз изменения активов на два периода вперед. Вывести уравнение кривой линии тренда.

1.5. Задание № 5

Используя финансовые функции для коммерческих расчётов решить следующие задачи:

1. Какая сумма окажется на счёте при внутригодовом учёте процентов, если 10 000 руб. были размещены в банке на 6 (n) лет под 10% (r) годовых. Результаты расчетов представить в форме табл. 7.

2. То же, но вкладчику выплачивают сумму в 800 руб.

3. Какая сумма должна быть выплачена, если шесть лет назад была выдана ссуда 1500 тыс. руб. под 15 % годовых с ежемесячным начислением процентов.

Таблица 7

Определение будущего значения вклада

Метод начисления процентов	Общее число периодов начисления процентов	Ставка процента за период начисления, %	Будущее значение вклада, руб.
ежегодный	=n	=r
полугодовой	=n*2	=r/2
квартальный	=n*4	=r/4
месячный	=n*12	=r/12
ежедневный	=n*365	=r/365

4. Есть два варианта инвестирования средств в течение 4 лет: в начале каждого года под 26% годовых или в конце каждого года под 38% годовых. Пусть ежегодно вносится 30 тыс. руб. Определите, сколько денег окажется на счёте в конце 4-го года для каждого варианта.

5. Фирме потребуется 5 млн. руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5 млн. руб. Определите необходимую сумму текущего вклада, если ставка процента по нему составляет 12% в год.

6. Предположим, рассматриваются два варианта покупки дома: заплатить сразу 9,9 млн. руб. или в рассрочку - по 94 тыс. руб. ежемесячно в течение 15 лет. Определить, какой вариант предпочтительнее, если ставка процента- 8 % годовых.

7. Инвестиции в проект к концу первого года его реализации составят 10000 руб. В последующие три года ожидаются годовые доходы по проекту 3000 руб., 4200 руб., 6800 руб. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.

1.6. Задание № 6

1. Выполните группировку трех новых листов. Введите и заполните табл. 8.

2. Добавьте новый рабочий лист и скопируйте в него таблицу с любого листа, очистите содержимое полей 2, 3, 4. Выполните консолидацию таблиц по полю 2, 3, 4 на основе данных трех предыдущих таблиц, используя функцию "Сумма" команды КОНСОЛИДАЦИЯ (меню ДАННЫЕ).

3. На основе консолидированной таблицы постройте сводную таблицу (меню ДАННЫЕ), выделив диапазон таблицы без «шапки», и разместите:

- графу 1 в строках СВОДНОЙ ТАБЛИЦЫ;

- графу 6 в столбцах СВОДНОЙ ТАБЛИЦЫ;

- графу 2, 3, 4 в данных СВОДНОЙ ТАБЛИЦЫ;

- графу 5 в странице СВОДНОЙ ТАБЛИЦЫ.

4. Выполните фильтрацию данных в СВОДНОЙ ТАБЛИЦЕ для разных значений графы 5.

Таблица 8

Ведомость выдачи зарплаты

Ф.И.О.	Всего начислено, руб.	Всего удержано, руб.	Сумма к выдаче, руб.	Отдел	Период
1	2	3	4	5	6
Иванов В.А.	1500	14% от	1	1 кв.
Петров А.А.	1800	всего	2	1 кв.
Сидоров П.Р.	1350	начислено	2	1 кв.
Морозов К.Н.	1650	1	1 кв.
Черкасов К.Г.	1900	1	1 кв.
Захаров Л.Д.	1770	1	1 кв.
Ветров А.Р.	1400	2	1 кв.
Иванов В.А.	1200	1	2 кв.
Петров А.А.	3000	2	2 кв.
Сидоров П.Р.	4700	2	2 кв.
Морозов К.Н.	6500	1	2 кв.
Черкасов К.Г.	2400	1	2 кв.
Захаров Л.Д.	7700	1	2 кв.
Ветров А.Р.	4440	2	2 кв.

5. Изменить имя поля данных в сводной таблице по исходной таблице (сумма по полю 2 изменить на «Всего начислено», сумма по полю 3 на « Всего удержано», сумма по полю 4 на «Сумма к выдаче»).

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

- максимум по полю "Всего начислено", установив денежный формат (р.);

- минимум по полю "Всего удержано";

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

1.7. Задание № 7

1. В табл. 9 приведены данные результатов сдачи экзаменационной сессии студентов. На новый рабочий лист скопируйте шаблон таблицы (названия строк и граф), содержащий фамилии первых десяти студентов. Используя функцию ПРОСМОТР категории ССЫЛКИ И МАССИВЫ, заполните таблицу результатами сдачи.

2. На основе данных табл. 9 подсчитайте количество студентов, сдавших сессию на «отлично», «хорошо» и «удовлетворительно», с помощью функции СЧЕТЕСЛИ категории СТАТИСТИЧЕСКИЕ. Сформировать документ в виде табл. 10.

3. Создайте с использованием УСЛОВНОГО ФОРМАТИРОВАНИЯ для всех значений табл. 10 следующие форматы:

- если значение меньше или равно 10, то шрифт полужирный курсив и отобразить на жёлтом фоне;

- если значение больше 10, то шрифт полужирный и отобразить на зелёном фоне.

Сводная ведомость результатов экзаменационной сессии
№ п/п	ФИО	Оценки
		Информатика	Экономика	Статистика	Менеджмент
1	Быкова А.А.	отлично	отлично	хорошо	отлично
2	Якунина Ю.Ф.	хорошо	удовл.	удовл.	хорошо
3	Тарасова А.М.	удовл.	удовл.	удовл.	хорошо
4	Скородумова В.С.	хорошо	удовл.	удовл.	хорошо
5	Каренина Е.О.	хорошо	отлично	хорошо	хорошо
6	Хлебников М.А.	удовл.	удовл.	удовл.	удовл.
7	Александров Н.К.	хорошо	хорошо	хорошо	удовл.
8	Михайлова Т.А.	отлично	отлично	отлично	отлично
9	Хитров Ю.А.	отлично	отлично	отлично	хорошо
10	Афонина А.А.	хорошо	отлично	отлично	хорошо
11	Бородина А.А.	удовл.	удовл.	удовл.	удовл.
12	Воробьева С.В.	хорошо	отлично	удовл.	хорошо
13	Кустовая М.Н.	хорошо	хорошо	удовл.	хорошо
14	Петрова И.М.	отлично	хорошо	отлично	отлично
15	Соколова Е.В.	отлично	отлично	хорошо	отлично
16	Гречкина А.А.	отлично	хорошо	отлично	отлично
17	Воронцова Е.А.	отлично	хорошо	отлично	отлично
18	Долина А.Л.	хорошо	отлично	отлично	отлично
19	Алексеева Р.А.	отлично	отлично	отлично	отлично
20	Аистов Д.В.	удовл.	удовл.	хорошо	хорошо
21	Шариков К.Н.	отлично	хорошо	хорошо	хорошо
22	Смирнов А.Н.	отлично	отлично	удовл.	хорошо
23	Власова О.А.	отлично	хорошо	хорошо	отлично
24	Сорокина Ю.В.	хорошо	отлично	удовл.	отлично

Таблица 9

Таблица 10

Ведомость результатов сдачи сессии

	Информатика	Экономика	Статистика	Менеджмент
Количество студентов, сдавших на "отлично"
Количество студентов, сдавших на "хорошо"
Количество студентов, сдавших на "удовлетворительно"

1.8. Задание № 8

Используя команду Подбор параметра меню СЕРВИС, выполните следующие задания:

1. На рынке телекоммуникационных услуг конкурируют две компании. Тарификация услуг компаний приведена в табл. 11. Определить, при каком количестве минут разговора в месяц стоимость услуг двух компаний одинакова. Для этого В табл. 11 добавить столбец «Количество минут» и объединяем две ячейки этого столбца (Компания 1 и Компания 2). В любой пустой ячейке этого листа вводим условие равноценности стоимости услуг компании:

,

где Х – количество минут разговора.

Таблица 11

Тарификация услуг

Компании	Абонентская плата за месяц (А), у.е.	Тариф по кредитной системе (Тк ), у.е./мин.	Тариф по авансовой системе (Та ), у.е./мин.
Компания 1	10	0,10	0,20
Компания 2	14	0,075	0,20

2. Для Компании 1 определите, при каком количестве используемых минут разговора в месяц стоимость кредитной и авансовой системы оплаты услуг одинакова. Условие равноценности систем оплаты услуг компании 1:

.

3. Подберите такое значение Х, при котором тождество имело бы смысл.

1.9. Задание № 9

1. Выполнить ABC-классификацию и XYZ-классификацию материально-технических ресурсов, закупленных железной дорогой в 2005 году. Для ABC-классификации необходимо заполнить табл. 12.

Таблица 12

№ п/п	Вид закупаемой продукции	Стоимость продукции, руб	Удельный вес, %	Нарастающий итог	Группа
1	ЗАПАСНЫЕ ЧАСТИ ВАГОНОВ	6209754
2	ЗАПАСНЫЕ ЧАСТИ ЛОКОМОТИВОВ	3401395
3	ЗАПАСНЫЕ ЧАСТИ ПАССАЖИРСКИХ ВАГОНОВ	800822
4	ЗАПАСНЫЕ ЧАСТИ ПУТЕВОЙ ТЕХНИКИ	1340768
5	ИЗДЕЛИЯ ЛЕГКОЙ ПРОМЫШЛЕННОСТИ	1142375
6	ЛЕСОПИЛОМАТЕРИАЛЫ	372634
7	ЛОКОМОТИВНЫЕ БАНДАЖИ	345450
8	МАСЛА И СМАЗКА	1713324
9	МАТЕРИАЛЫ ВЕРХНЕГО СТРОЕНИЯ ПУТИ	17522662
10	МЕТАЛЛОПРОКАТ	2712934
11	МОСТОКОНСТРУКЦИИ	652396
12	ПОДШИПНИКИ ВАГОННЫЕ	2267295
13	ПОДШИПНИКИ ЛОКОМОТИВНЫЕ	319539
14	ПОСТЕЛЬНЫЕ ПРИНАДЛЕЖНОСТИ	412278
15	ПРОДУКЦИЯ МАШИНОСТРОЕНИЯ	664035
16	ПРОЧИЕ МАТЕРИАЛЫ	952054
17	ТОПЛИВО	37718932
18	ХИМИЧЕСКАЯ ПРОДУКЦИЯ	808228
19	ЦЕЛЬНОКАТАННЫЕ КОЛЕСА	8411294
20	ЭЛЕКТРОТЕХНИЧЕСКАЯ ПРОДУКЦИЯ	2946249
	Общий итог

2. Рассчитать нарастающий итог значения критерия классификации (стоимости закупок) по номенклатурным позициям. Для этого необходимо отсортировать таблицу по убыванию стоимости продукции. Затем следует определить удельный вес значения критерия классификации. Нарастающий итог рассчитывается следующим образом. Значения удельного веса для первой позиции номенклатуры переписывается в столбец нарастающего итога. Для последующих номенклатурных позиций производится суммирование значения нарастающего итога предыдущей позиции со значением удельного веса текущей позиции.

3. Выделить группы классификации. В классическом АВС-методе классификация проводится на основе закона Парето, утверждающего, что 80% значений качественного критерия определяется 20% количества выбранной совокупности объектов.

Группа А – объекты, сумма долей с накопительным итогом которых, составляет первые 80 % от общей суммы параметров.

Группа В – следующие за группой А объекты, сумма долей с накопительным итогом которых, составляет от 80 % до 95 % от общей суммы параметров.

Группа С – оставшиеся объекты, сумма долей с накопительным итогом которых, составляет от 95 % до 100 % от общей суммы параметров.

4. На основе полученных результатов построить кумулятивную кривую (линию нарастающего удельного веса). Она строится на базе таблицы АВС-классификации в виде графика кривой взаимосвязи качественных и количественных значений. Показать на графике группы классификации.

5. Сформировать результаты анализа в виде табл. 13.

Таблица 13

Группа	Объем закупок, руб.	Доля,%	Количество видов закупаемой продукции	Доля,%
А
В
С
Итого

6. Выполнит

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

Таблица 14

№ п/п	Вид закупаемой продукции	1 квартал	2 квартал	3 квартал	4 квартал	Коэффициент относительной вариации	XYZ-группа
1	МАСЛА И СМАЗКА	409980	404259	442632	456453
2	ЛОКОМОТИВНЫЕ БАНДАЖИ	87 418	76 094	95 042	86 896
3	ПОДШИПНИКИ ВАГОННЫЕ	470 149	603 033	597 086	597 027
4	ЗАПАСНЫЕ ЧАСТИ ПУТЕВОЙ ТЕХНИКИ	246 773	347 973	360 452	385 571
5	ЗАПАСНЫЕ ЧАСТИ ПАССАЖИРСКИХ ВАГОНОВ	180 536	205 385	249 473	165 427
6	ЦЕЛЬНОКАТАННЫЕ КОЛЕСА	1768403	1724509	2404502	2513879
7	ПОДШИПНИКИ ЛОКОМОТИВНЫЕ	55 635	76 948	97 078	89 878
8	ПРОДУКЦИЯ МАШИНОСТРОЕНИЯ	118 154	196 999	208 134	140 747
9	МАТЕРИАЛЫ ВЕРХНЕГО СТРОЕНИЯ ПУТИ	3236792	5268273	5581613	3435984
10	ТОПЛИВО	7945976	7703236	8743383	13326337
11	МОСТОКОНСТРУКЦИИ	223604	153514	162729	112550
12	ЗАПАСНЫЕ ЧАСТИ ЛОКОМОТИВОВ	550615	730386	1067203	1053190
13	ЛЕСОПИЛОМАТЕРИАЛЫ	109 492	120 881	90 562	51 699
14	ИЗДЕЛИЯ ЛЕГКОЙ ПРОМЫШЛЕННОСТИ	247 313	371 446	357 505	166 112
15	ХИМИЧЕСКАЯ ПРОДУКЦИЯ	162 405	272 025	247 647	126 151
16	ЗАПАСНЫЕ ЧАСТИ ВАГОНОВ	1170788	1830429	2197208	1011329
17	ЭЛЕКТРОТЕХНИЧЕСКАЯ ПРОДУКЦИЯ	626 619	908 694	1 034 359	376 578
18	МЕТАЛЛОПРОКАТ	463 337	899 139	953 430	397 028
19	ПРОЧИЕ МАТЕРИАЛЫ	87 384	307 973	350 532	206 165
20	ПОСТЕЛЬНЫЕ ПРИНАДЛЕЖНОСТИ	83 342	182 801	107 373	38 762

7. Определить коэффициент вариации для каждого объекта анализа – вида закупаемой продукции. Формула для расчета коэффициента вариации:

где хi
—
значение параметра по оцениваемому объекту за i-
тый период,

— среднее значение параметра по оцениваемому объекту анализа,

п —
число периодов.

8. Определить группы X, Y и Z. Для этого сначала необходимо отсортировать объекты анализа по возрастанию значения коэффициента вариации. Рекомендуемое (классическое) распределение:

Группа
X
– объекты, коэффициент вариации по которым не превышает 10%.

Группа
Y
– объекты, коэффициент вариации по которым составляет 10% - 25%.

Группа
Z
– объекты, коэффициент вариации по которым превышает 25%.

1.10. Задание № 10

Определить финансово-экономическое состояние клиентов железнодорожного транспорта на основе анализа бухгалтерского баланса. Основные строки бухгалтерского баланса приведены в табл. 15.

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

Коэффициент текущей ликвидности Kтл:

где AII
- итоговая строка 2 – го раздела актива бухгалтерского баланса «Оборотные активы»,

ПV
- итоговая строка 5 – го раздела пассива бухгалтерского баланса «Краткосрочные обязательства».

Таблица 15

Основные строки бухгалтерского баланса, руб.

Наименование строки			Код строки		Значение, тыс.руб
				Клиент 1		Клиент 2		Клиент 3
АКТИВ 1.Внеоборотные активы			190		520024		1518280		104373
2.Оборотные активы
Запасы			210		368882		1432099		3555
Дебиторская задолженность			230, 240		1526894		1005818		19970
Краткосрочные финансовые вложения			250		0		0		672
Денежные средства			260		1762283		19433		2034
Прочие оборотные активы			270		0		0		0
Итог по разделу 2			290		3658059		2457350		26231
Баланс (сумма строк 190+290)			300		4178083		3975630		130604
ПАССИВ 3.Капитал и резервы
Итог по разделу 3			490		778173		3291282		117075
4. Долгосрочные обязательства
Итог по разделу 4			590		0		0		1949
5.Краткосрочные обязательства
Займы и кредиты			610		72256		370000		1100
Кредиторская задолженность			620		3327654		285648		10224
Задолженность участникам (учредителям) по выплате доходов			630		0		28700		256
Итог по разделу 5			690		3399910		684348		11580
Баланс (сумма строк 490+590+690)			700		4178083		3975630		130604

Коэффициент обеспеченности собственными средствами Ko:

где AI
- итоговая строка 1 – го раздела актива бухгалтерского баланса «Внеоборотные активы»,

ПIII
- итоговая строка 3 – го раздела пассива бухгалтерского баланса «Капитал и резервы».

Коэффициент абсолютной ликвидности Kал:

Коэффициент критической (срочной) ликвидности Kкл:

Коэффициент автономии Kа:

Каждый рассчитанный коэффициент имеет нормативное значение (табл. 16).

Рисунок 3.

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

Таблица 16

Нормативные значения коэффициентов платежеспособности

№ п/п	Коэффициент	Нормативное значение (интервал)
1	Коэффициент текущей ликвидности	1-2
2	Коэффициент обеспеченности собственными средствами	0,1-0,5
3	Коэффициент абсолютной ликвидности	0,2-0,5
4	Коэффициент критической (срочной) ликвидности	0,5-1
5	Коэффициент автономии	0,1-0,5

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

Порядок решения поставленной задачи с помощью средств Excel по данным табл. 14 (сначала по клиенту 1):

1. Рассчитать коэффициенты платежеспособности с использованием стандартных средств программы.

2. Определить платежеспособность клиента по каждому коэффициенту с помощью функции «ЕСЛИ».

3. Определить платежеспособность клиента в целом с помощью функции «ЕСЛИ».

Результаты решения необходимо представить в форме таблицы 16.

Таблица 17

Оценка финансового состояния клиента железнодорожного транспорта

№ п/п	Коэффициент	Нормативное значение	Расчетное значение	Финансовое состояние клиента по каждому коэффициенту	Финансовое состояние клиента в целом
1	Коэффициент текущей ликвидности	1-2
2	Коэффициент обеспеченности собственными средствами	0,1-0,5
3	Коэффициент абсолютной ликвидности	0,2-0,5
4	Коэффициент критической (срочной) ликвидности	0,5-1
5	Коэффициент автономии	0,1-0,5

Примечание: если расчетное значение коэффициента попадает в интервал нормативных значений, то в 5-том столбце ставиться 1, если не попадает, то ставиться 0. Если значения 4-х или 5-ти из рассчитанных коэффициентов попадают в установленные для них интервалы, то в 6-том столбце пишется «Устойчивое», в противном случае пишется «Неустойчивое». Для оценки финансового состояния остальных клиентов необходимо поменять ссылки в столбце "Расчетное значение" табл.17 с помощью команды "Найти и заменить".

1.11. Задание № 11

Определить наиболее «популярные» марки иностранных легковых автомобилей среди жителей Московского региона.

По специально разработанной анкете был проведен опрос 10 экспертов. В анкете опроса экспертам предлагалось проранжировать различные марки иностранных легковых автомобилей по уровню их популярности среди жителей Московского региона. Ранжирование - это процедура установления относительной популярности марок автомобиля на основе их упорядочения.

Таблица 18

Шкала ранжирования популярности марок авто

Уровень популярности марки авто	Оценка (ранг)
Максимальный	6
Самый высокий	5
Высокий	4
Средний	3
Малый	2
Минимальный	1

В результате экспертного опроса каждой марке легкового автомобиля каждым экспертом был присвоен определенный ранг. Результаты опроса приведены в табл. 19.

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

Обработка информации экспертного опроса проводится с использованием формул математической статистики.

Таблица 19

Экспертная оценка популярности иностранных легковых автомобилей среди жителей Московского региона

Эксперты	Марки автомобилей
	Хонда	Порше	БМВ	Митсубиси	Мерседес	Тойтота	Лексус	Ауди
1	3	4	4	4	5	5	5	6
2	5	4	3	3	5	5	5	5
3	4	4	2	4	5	6	6	6
4	2	4	3	4	6	5	5	5
5	3	3	2	4	4	5	5	6
6	4	4	3	4	5	5	5	6
7	4	4	2	4	5	5	6	5
8	4	4	3	3	6	5	5	6
9	3	3	3	4	6	5	5	5
10	5	3	3	3	6	4	5	5

(1)

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

где bi
– суммарная оценка для i-ой марки;

bij
– оценка, присваиваемая i-ой марке автомобиля j-м экспертом.

n – количество экспертов.

Марка, у которой наибольшее значение bi
, является наиболее популярной среди жителей Московского региона.

2. Находится среднее арифметическое значение популярности каждой марки автомобиля:

(2)

где Хср
i
– среднее арифметическое значение i-ой марки автомобиля,

3. Определяется дисперсия (D), которая характеризует меру отклонения фактической оценки данной марки автомобиля от среднего значения:

4. Более точно степень отклонения популярности определенной марки от среднего значения показывает среднеквадратичное отклонение (s), которое представляет собой квадратный корень из дисперсии:

(4)

5. Показателем колеблемости, оценивающим типичность средних величин является коэффициент вариации (g):

(5)

Значение коэффициента вариации более 40% свидетельствует о большой колеблемости популярности определенной марки автомобиля, то есть мнения экспертов относительно этой марки автомобиля значительно расходятся.

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

(6)

где: s2
f
– фактическая дисперсия суммарных (упорядоченных) оценок, данных экспертами;

s2
max
– дисперсия суммарных (упорядоченных) оценок в случае полной согласованной мнений экспертов;

m – количество оцениваемых марок автомобилей;

n – количество экспертов.

Значение коэффициента конкордации изменяется в диапазоне от 0 до 1. Если коэффициент конкордации равен нулю, то согласованность отсутствует. При коэффициенте конкордации равном единице связь между оценками экспертов полная. Для использования полученных от экспертов оценок необходимо, чтобы коэффициент конкордации был больше 0,5, то есть при W=0,5 считается, что мнения экспертов согласованы.

Порядок решения поставленной задачи с помощью средств Excel по данным табл. 19:

1. Суммарный ранг популярности марки автомобиля определяется с помощью функции «СУММ».

2. Среднее арифметическое значение популярности каждой марки автомобиля определяется с помощью функции «СРЗНАЧ».

3. Дисперсия определяется с помощью функции «ДИСП».

4. Среднеквадратичное отклонение определяется с помощью функции «СТАНДОТКЛОН».

5. Ввод формул для определения коэффициентов вариации и конкордации производится с использованием стандартных средств.

6. Место в рейтинге популярности определяется по значению суммарного ранга с помощью команды "РАНГ".

Результаты обработки экспертного опроса необходимо представить в форме табл. 20.

Таблица 20

Результаты обработки экспертного опроса

Показатели	Марки автомобилей
	Хонда	Порше	БМВ	Митсубиси	Мерседес	Тойота	Лексус	Ауди
									Суммарный ранг
Средний ранг
Дисперсия
Среднеквадратическое отклонение
Коэффициент вариации
Коэффициент конкордации
Место в рейтинге популярности

2. Методические указания к выполнению заданий в среде
ms Excel

Использование абсолютных и относительных ссылок

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

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

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

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

Таким образом, используются следующие виды ссылок.

$A$1 (абсолютный столбец и абсолютная строка)	$A$1
A$1 (относительный столбец и абсолютная строка)	C$1
$A1 (абсолютный столбец и относительная строка)	$A3
A1 (относительный столбец и относительная строка)	C3

Для преобразования типа ссылки может быть использована клавиша F4.

Работа с листами в групповом режиме

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

Основные функции используемы для выполнения заданий

Функция ЕСЛИ

Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

Значение_если_ложь — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Функция СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

СЧЁТЕСЛИ(диапазон;критерий)

Диапазон — диапазон, в котором нужно подсчитать ячейки.

Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Функция СУММЕСЛИ

Функция СУММЕСЛИ суммирует ячейки, заданные критерием.

СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

Диапазон — диапазон вычисляемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования — фактические ячейки для суммирования.

Функция СРЗНАЧ

СРЗНАЧ(число1; число2; ...)

Возвращает среднее (арифметическое) своих аргументов.

Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.

Финансовые функции

Финансовая функция БЗ

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

БЗ (норма, число_периодов, выплата, нз, тип)

Существуют различные варианты использования этой функции при решении конкретных задач.

Если необходимо рассчитать будущую стоимость единой суммы вклада, по которой начисляются сложные проценты определённое число периодов, то эту величину можно рассчитать по формуле

,

где - будущая стоимость вклада или займа; - текущая стоимость вклада (займа); n
–
общее число периодов начисления процентов; r
–
процентная ставка по вкладу (займу).

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, нз)

Бывают ситуации, когда платежи производятся систематически, а не один раз, как в предыдущем варианте. Эти платежи могут осуществляться в начале каждого расчётного периода (так называемые «обязательные платежи» или пренумерандо) или в конце («обычные платежи» или постнумерандо) в течение n
периодов. В каждом периоде вносится одинаковая сумма. Необходимо найти их будущую стоимость в конце n
-
го периода для обоих случаев. Отличие в расчёте при этом заключается в том, что во втором случае не происходит начисления процентов на последний вклад, т.е. все вклады пренумерандо увеличиваются на сложные проценты на один расчётный период больше, чем вклады постнумерандо.

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

,

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

В этом случае на рабочем листе формула примет вид:

БЗ (норма, число_периодов, выплата, ,1)

Здесь тип=1, т.к. он определяет время начисления процентов: в конце/начале (0/1) периода. Если тип опущен, он считается равным 0.

Для расчёта будущей стоимости серии фиксированных периодических платежей, если выплаты происходят в конце периода, то формула модифицируется:

В этом случае на рабочем листе Excel формула примет вид:

БЗ (норма, число_периодов, выплата, ,0) или

БЗ (норма, число_периодов, выплата)

При решении конкретной задачи вместо названий аргументов следует записать соответствующие числа. Результаты должны совпадать с расчётом формул.

Функция ПЗ

Данная функция предназначена для расчёта текущей (настоящей) стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей. Этот расчёт является обратным к определению будущей стоимости при помощи функции БЗ.

Данная функция используется в следующих расчётах:

Если известно будущее значение вклада (займа) и требуется определить текущее значение этого вклада, т.е. сумму, которую необходимо положить на счёт сегодня, чтобы в конце n-го периода она достигла заданного значения. Это значение можно получить из формулы:

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, , бс)

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

В этом случае на рабочем листе формула примет вид:

ПЗ(норма, кпер, выплата, , 1)

Для расчёта текущей стоимости постоянных периодических выплат, если они происходят в конце периода (обычные платежи) формула модифицируется

Соответствующая этому расчёту формула примет вид:

ПЗ(норма, кпер, выплата)

Функция НПЗ

Функция вычисляет чистую текущую стоимость (
NPV
)
периодических платежей переменной величины как сумму ожидаемых доходов и расходов, дисконтированных нормой процента r:

,

где valuei
–
значения выплат и поступлений.

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

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

Функция ПРПЛТ

Функция ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки. Для получения более полного описания смысла аргументов функции ПРПЛТ и более подробной информации о функциях, связанных с ежегодными выплатами, см. справку по функции ПС.

ПРПЛТ(ставка;период;кпер;пс;бс;тип)

Ставка — процентная ставка за период.

Период — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «кпер».

Кпер — общее число периодов выплат годовой ренты.

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

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

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

Функция ПЛТ

Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

ПЛТ(ставка;кпер;пс;бс;тип)

Функция ОБЩПЛАТ

Функция ОБЩПЛАТ возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат.

ОБЩПЛАТ(ставка;кол_пер;нз;нач_период;кон_период;тип)

Кол_пер — это общее количество периодов выплат.

Нз — это стоимость инвестиции на текущий момент.

Нач_период — это номер первого периода, включаемого в вычисления. Периоды выплат нумеруются, начиная с 1.

Кон_период — это номер последнего периода, включаемого в вычисления.

Тип — это выбор времени платежа.

Функция ОСПЛТ

Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки. Более подробное описание аргументов функции ОСПЛТ см. в описании функции ПС.

ОСПЛТ(ставка;период;кпер;пс;бс;тип)

Функция ОБЩДОХОД

Функция ОБЩДОХОД возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами.

ОБЩДОХОД(ставка;кол_пер;нз;нач_период;кон_период;тип)

Статистические функции

Функция ДИСП

Функция ДИСП оценивает дисперсию по выборке.

ДИСП(число1;число2; ...)

Число1, число2, ... — это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.

Функция СТАНДОТКЛОН

Функция СТАНДОТКЛОН оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

СТАНДОТКЛОН(число1; число2; ...)

Число1, число2, ... — от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно также использовать массив или ссылку на массив.

Учебно-методическое издание

Шобанов Андрей Витальевич

Епишкин Илья Анатольевич

Струкова Елена Викторовна

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ЭКОНОМИКЕ

Методические указания и задания для практических занятий

для студентов экономических специальностей

Подписано в печать Тираж экз.

Усл. печ. л. Заказ Изд. № Формат

127994, Москва, ул. Образцова, 15. Типография МИИТа