Овчаренко_7

5.3. Задания для самостоятельный занятий и ответы к ним

Предлагаемые задания целиком основаны на изложенном материале. Задания используют тематику финансового и банковского менеджмента, однако для их выполнения не требуются предварительные знания по этим вопросам, необходимо только владение информационной технологией использования описанных средств EXCEL. При составлении заданий был использован сборник Фадейкиной Н.В. "Сто избранных задач по финансовой информатике", вып. СМВШБД, 1996 г.

Задание № 1

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

Значения балансовой, остаточной и восстановительной стоимостей

объектов, а также стоимость износа считать в млн. руб.

Переоценка основных средств производства

	Наименование	Балан-		Остаточная	Восстано-	Восстано-
		совая	Износ		вительная	вительная
	объекта	стои-		стоимость	полная	остаточная
		мость			стоимость	стоимость
	Заводо-	11576.2	568.0
	управление
	Диспетчерская	176.0	45.4
	ЦехN1	710.2	120.3
	ЦехN2	804.6	240.0
	ЦехN3	933.0	150.2
	ЦexN4	474.4	174.5
	Склад N1	570.5	221.2
	Склад N2	430.4	92.2
	Склад N3	564.9	118.0
	Склад N4	320.5	87.5
	Итого

Используя значения балансовой стоимости (БС) и износа объекта (ИО), рассчитать:

• Остаточную стоимость объекта (ОС) по следующей формуле: ОС = БС — ИО;

• Восстановительную полную стоимость объекта (ВП) и восстановительную остаточную стоимость объекта (ВО) по следующим формулам:

ВП = БС • К, ВО = ОС • К,

где К = 3.0, если БС > 500 млн. руб., иначе К = 2.0, если БС < 500 млн.

руб.

2.Добавить в ведомость новую графу Вид объекта и присвоить всем объектам Цех N1 — Цех N4 вид основной, а всем остальным объектам присвоить вид вспомогательный.

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

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

5.Рассчитать общую (суммарную) балансовую стоимость, износ и общую (суммарную) остаточную стоимость всех основных и вспомогательных видов объектов с помощью команды Итоги. После анализа результатов расчета вернуть таблицу в исходное состояние.

6.С помощью команды Расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых > 500 млн. руб. Включить в новую ведомость следующие графы:

•Наименование объекта;

•Балансовая стоимость;

•Остаточная стоимость;

•Восстановительная полная стоимость.

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

8.Построить на отдельном рабочем листе EXCEL смешанную диаграмму, в которой необходимо показать значения балансовой и остаточной стоимостей для всех вспомогательных объектов в виде гистограмм, а значения восстановительной (полной) стоимости всех

вспомогательных объектов представить в виде линейного графика на той же диаграмме. Вывести легенду и название графика Оценка основных

средств производства (вспомогательные объекты).

9.На основании исходной накопительной ведомости Переоценка основных средств производства с помощью аппарата Функции базы данных рассчитать и сформировать следующий документ:

Тип объекта	Основной	Вспомогательный
Средняя балансовая стоимость
Максимальный износ
Минимальный износ
Максимальная остаточная
стоимость
Средняя остаточная стоимость
Количество объектов

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

Ответы к заданию № 1

В результате выполнения первых трех пунктов задания № 1 должна быть получена следующая таблица:

Переоценка основных средств производства

Наименов	Вид	Балан-	Износ	Остаточ-	Восстано-	Восстано-
ание	объекта	совая		ная стои-	вительная	вительная
объекта		стоимость		мость	полная	остаточна
Диспетче	Вспомо-	176	45,4	130,6	352	261,2
рская	гательный
Заводо-	Вспомо-	11576,2	568	11008,2	34728,6	33024,6
управлени	гательный
Склад N1	Вспомо-	570,5	221,2	349,3	1711,5	1047,9
	гательный
Склад N2	Вспомо-	430,4	92,2	338,2	860,8	676,4
	гательный
Склад N3	Вспомо-	564,9	118	446,9	1694,7	1340,7
	гательный
Склад N4	Вспомо-	320,5	87,5	233	641	466
	гательный
Цех N1	Основной	710,2	120,3	589,9	2130,6	1769,7
Цех N2	Основной	804,6	240	564,6	2413,8	1693,8
Цех N3	Основной	933	150,2	782,8	2799	2348,4
Цex N4	Основной	474,4	174,5	299,9	948,8	599,8
Итого		16560,7	1817,	14743,4	48280,8	43228,5

Используя команду Автофильтр, можно выполнить фильтрацию ведомости, оставив в ней только вспомогательные объекты.

Команда Расширенный фильтр позволяет сформировать накопительную ведомость по тем объектам, балансовая стоимость которых > 500 млн. руб., как показано в следующей таблице:

Наименование	Балансовая	Остаточная	Восстановительная
объекта	стоимость	стоимость	полная стоимость
Заводоуправление	11576,2	11008,2	34728,6
Склад N 1	570,5	349,3	1711,5
Склад N3	564,9	446,9	1694,7
Цех N1	710,2	589,9	2130,6
Цех N2	804,6	564,6	2413,8
Цех N3	933	782,8	2799

Расчет общей (суммарной) балансовой стоимости, износа и общей

(суммарной) остаточной стоимости всех основных и всех вспомогательных видов объектов с помощью команды Итоги имеет следующий вид:

Наиме-	Вид объекта	Балансовая	Износ	Остаточная	Восстано-	Восстано-
нование		стоимость		стоимость	вительная	вительная
объекта					полная	остаточная
					стоимость	стоимость
Диспет-	Вспомога-	176	45,4	130,6	352	261,2
черская	тельный
Заводоупра	Вспомога-	11576,2	568	11008,2	34728,6	33024,6
вление	тельный
Склад N1	Вспомога-	570,5	221,2	349,3	1711,5	1047,9
	тельный
Склад N2	Вспомога-	430,4	92,2	338,2	860,8	676,4
	тельный
Склад N3	Вспомога-	564,9	118	446,9	1694,7	1340,7
	тельный
Склад N4	Вспомога-	320,5	87,5	233	641	466
	тельный
	Вспомога-	13638,5	1132,3	12506,2
	тельный
	Всего
Цех N1	Основной	710,2	120,3	589,9	2130,6	1769,7
Цех N2	Основной	804,6	240	564,6	2413,8	1693,8
Цех N3	Основной	933	150,2	782,8	2799	2348,4
Цex N4	Основной	474,4	174,5	299,9	948,8	599,8
	Основной	2922,2	685	2237,2
	Всего
	Общий итог	16560,7	1817,3	14743,4

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

производства.

Переоценка основных средств производства

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

вспомогательных объектов — в виде линейного графика, имеет заголовок Оценка основных средств производства (вспомогательные объекты) и

приведен ниже.

Оценка основных средств производства (вспомогательные объекты)

Результаты расчетов с помощью функций EXCEL Работа с базой данных представлены в следующей таблице:

Вид объекта	Основной	Вспомогательный
Средняя балансовая	730,55	2273,08
Максимальный износ	240,00	568,00
Минимальный износ	120,30	45,40
Максимальная	782,80	11008,20
остаточная стоимость
Средняя остаточная	559,30	2084,37
стоимость
Количество объектов	4	6

Задание №2

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

Для решения задачи используется следующая входная информация (в млн. руб.):

•объем ссуд, предоставленных государственным предприятиям,

•объем ссуд, предоставленных кооперативам;

•объем ссуд, предоставленных совместным предприятиям;

•объем ссуд, предоставленных предпринимателям;

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

•объем ссуд, предоставленных инофирмам;

•объем ссуд, предоставленных сельскохозяйственным предприятиям;

•объем ссуд, предоставленных предприятиям, организованным в форме АО и ТОО;

•объем ссуд, предоставленных ИЧП;

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

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

Вложения коммерческого банка	Сумма	Уд. вес (%)
Объем ссуд государственным	1000	U(1)
предприятиям
Объем ссуд кооперативам	400	U(2)
Объем ссуд совместным предприятиям	2000	U(3)
Объем ссуд предпринимателям	350	U(4)
Объем ссуд физическим лицам	650	U(5)
Объем ссуд инофирмам	1000	U(6)
Объем ссуд с/х предприятиям	300	U(7)

Объем ссуд предприятиям в форме АО и	1200	U(8)
ТОО
Объем ссуд ИЧП	500	U(9)
Объем межбанковских кредитов	3000	U(10)
ИТОГО	SS	100%

Формулы для расчета выходных показателей имеют следующий вид:

SS = SUM (S(I)),

где S(I) — сумма i-й ссуды (млн. руб.);

U(I) = S(I) / SS • 100,

где U(I) — удельный вес i-й ссуды,

I = [1,N], N — количество видов предоставляемых ссуд.

2.Выполнить сортировку документа по возрастанию объемов вложений коммерческого банка.

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

также легенду и название графика Структура кредитных вложений банка.

4.Построить на новом листе EXCEL смешанную диаграмму, в которой суммы объемов каждого вида ссуды коммерческого банка были бы представлены в виде гистограмм, а их удельные веса в виде линейного графика на той же диаграмме. Вывести легенду и название графика Анализ

кредитных вложений коммерческого банка.

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

Вложения коммерческого банка	Сумма

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

Ответы к заданию №2

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

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

Вложения коммерческого банка	Сумма	Уд. вес (%)
Объем ссуд с/х предприятиям	300	3%
Объем ссуд предпринимателям	350	3%
Объем ссуд кооперативам	400	4%
Объем ссуд ИЧП	500	5%
Объем ссуд физическим лицам	650	6%
Объем ссуд государственным предприятиям	1000	10%
Объем ссуд инофирмам	1000	10%
Объем ссуд предприятиям в форме АО и ТОО	1200	12%
Объем ссуд совместным предприятиям	2000	19%
Объем межбанковских кредитов	3000	29%
ИТОГО	10400	100%

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

Вложения коммерческого банка	Сумма
Объем ссуд предприятиям в форме АО и ТОО	1200
Объем ссуд совместным предприятиям	2000
Объем межбанковских кредитов	3000

Ниже приведена круговая диаграмма, отражающая структуру сумм каждого вида ссуды коммерческого банка в виде соответствующего сектора, из которой видно, что наибольший объем ссуды имеют межбанковские кредиты:

Структура кредитных вложений банка

Смешанная диаграмма, в которой суммы вложений каждого вида ссуды представлены в виде гистограмм, а их удельные веса в виде линейного графика, приведена ниже: