Лабораторный практикум по информатике и ИКТ

Предлагаемый лабораторный практикум предназначен для учащихся  изучающих дисциплину "Информатика и ИКТ" технического профиля обучения. Знания, полученные при изучении данного курса, имеют для учащихся большое значение в сфере практического применения и при их дальнейшем обучении. Содержание данного лабораторного практикума предполагает дальнейшее углубление и детализацию полученных учащимися знаний как с учетом развития  программного обеспечения, так и с учетом гораздо большей практической направленности, т. к. при решении профессионально-ориентированных задач учащиеся не только глубже усваивают основные понятия, которые являются ключевыми для учащихся, но и добиваются получения осознанных навыков работы с программой MS Exsel.

23

Задание № 1.

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

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

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

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

• Восстановительную полную стоимость объекта (ВП) и восстановительную остаточную стоимость объекта (ВО) по следующим формулам:
  ВП = БС*К;
  ВО = ОС*К,
  где К = 3,0 если БС 500 млн. руб., иначе К = 2,0, если БС

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задание № 2.

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

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

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

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

SS=SUM(S(I)),

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SS=SUM(S(I)),

где S(I) – сумма i-го привлеченного средства;

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

где U(I) – удельный вес i- го привлеченного средства;

I=[1,N], N – количество видов привлеченных средств банка.

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

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

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

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

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

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

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

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

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

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

1. Сопоставить доходность акций по уровню дивидендов за 2002 год по отдельным элементам.

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

^{Доходность акций по уровню дивидендов.}

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

DivR(i)=NA*Div(i);

DN(i)=Div(i);

DF(i)=DivR(i)/CP(i),

где i =[1,N], N – число рассматриваемых эмитентов.

1. В выходном документе отсортировать записи в порядке возрастания фактической доходности по дивидендам.

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

• Эмитент;

• Номинал акции;

• Цена продажи;

• Доходность по дивидендам практическая.

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

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

3. На основании исходного документа Доходность акций по уровню дивидендов рассчитать следующие значения:

• средняя цена продажи акций по всем эмитентам;

• максимальная цена продажи акций, наименование соответствующего эмитента;

• минимальная цена продажи акций, наименование соответствующего эмитента;

• максимальная фактическая доходность акций по уровню дивидендов, наименование соответствующего эмитента;

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

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

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

1. Определить начальную сумму долга по векселю.

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

^{Доходность акций по уровню дивидендов.}

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

RW(i)=SW(i)/(1+(K(i)*P(i)/100);

K(i)=DS(i)/DG(i);

DG=360;

SSW=SUM (SW(i));

SPW=SUM(PW(i)),

где i =[1,N], N – количество векселедержателей.

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

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

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

4. Выполнить фильтрацию документа, оставив в нем только кредиторов, суммы векселей которых на конец срока ссуды превышают 800 тыс. руб.

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

• Кредитор

• Сумма кредита (тыс. руб.)

• Первоначальная сумма ссуды (тыс. руб.)

Новый документ должен иметь следующий вид:

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

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

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

^{Анализ поступления средств на счета по вкладам коммерческого банка.}

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

US(i)=(SK(i)-SN(i))/P(i)*100.

1. Выполнить сортировку документа по убыванию уровня оседаемости средств коммерческого банка.

2. Отсортировать заново документ по возрастанию остатков вкладов на конец года.

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

4. На основании исходного документа Анализ поступления средств на счета по вкладам коммерческого банка сформировать следующий выходной документ по всем четырем банкам:

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

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

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

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

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

Ссуды под залог ценных бумаг;

Ссуды под залог товарно-материальных ценностей;

Ссуды под залог валютных ценностей;

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

Ссуды под залог долговых требований;

Гарантированные ссуды;

Застрахованные ссуды;

Ссуды без обеспечения.

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

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

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

SS = SUM (S (I)).

U (I)=S (I)/SS*100.

Где S(I) -сумма i - ой ссуды,

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

I = (1.N). N - количество видов предоставленных ссуд.

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

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

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

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

1. На основании исходного документа Структура кредитных вложений коммерческого банка сформировать следующий документ:

   Расчетная величина	Значение
   Максимальная сумма ссуд под залог (по всем видам ссуд под залог)
   Минимальная сумма ссуд под залог(по всем видам ссуд под залог)
   Количество видов ссуд под залог
   Максимальная сумма ссуд (по всем видам ссуд)
   Минимальная сумма ссуды (по всем видам ссуд)

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

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

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

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

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

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

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

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

^{Показатели ликвидности баланса коммерческого банка}

^{(тыс. руб.)}

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

H5(i)= LA(i)/ C(i)

H6(i)=LA(i) / A(i)

H7(i)=LA(i)/ OB(i)

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

H5 - соотношение суммы ликвидных активов банка и суммы р/сч., т/сч., вкладов и депозитов;

H6 - соотношение суммы ликвидных активов банка и общей суммы активов;

H7 - соотношение суммы ликвидных активов банка и суммы по счетам до востребования.

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

2. Выполнить фильтрацию таблицы, оставив на ней только показатели ликвидности и выходные показатели баланса банка на 01,05,97.

3. Вернуть таблицу в исходное состояние.

4. Подготовить к выводу на печать

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

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

^{Структура привлеченных ресурсов коммерческого банка.}

1. Выполнить сортировку таблицы по возрастанию значений остатков привлеченных ресурсов (в млн. руб.)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

^{Структура депозитной базы привлеченных ресурсов коммерческого банка.}

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

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

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

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

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

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

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

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

Объемы эффективных кредитных и свободных ресурсов банка.

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

EKR(i)=UF(i)+OSS(i)+ORS(i)+D(i)+PR(i)-NA(i)-0.1*(D(i)+ORS(i))-0.3*ORS(i);

OSR(i)=EKR(i)-FKV(i),

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

1. Выполнить фильтрацию документа, оставив в нем только информацию о кредитных ресурсах по первому кварталу 1997 года.

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

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

4. Вернуть документ в исходное состояние.

5. Определить по исходному документу объемы эффективных кредитных и свободных ресурсов банка, в каком месяце объем свободных ресурсов банка был максимальным, а в каком месяце – минимальным.

6. Рассчитать по исходному документу среднее значение объемов эффективных кредитных ресурсов и среднее значение свободных ресурсов за весь период – с 01.01.97 по 01.05.97 – работы банка.

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

1. Рассчитать значение эффективных кредитных ресурсов – EKR и объем свободных ресурсов – OSR на 01.06.01 по формулам приведенным в п.1

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

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