Студентам ИТ / 3 ЛП_ИТ / ИТ_автоматизир / ИТ_автоматизир_Excel / 3_обр_зад_отч

Лабораторная работа №3

Решение обратной задачи отчетности средствами электронной таблицы Microsoft Excel

1.Войдите в электронную таблицу “Microsoft Excel” и по адресу «Мои документы / № курса_№ группы_ФИО / Лаб_1» откройте данные лабораторной работы №1.

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

3.Сохраните информацию по адресу: Мои документы / № курса_№ группы_ФИО / Лаб_3.

Внимание! В ходе дальнейшего создания документа, во избежание потери введенных данных – не забывайте сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме «Сохранить» (“рисунок дискетки”) панели инструментов.

4.Постройте на одной диаграмме графики зависимостей «Прочие расходы» и «Чистый доход предприятия» от «Периода отчетности». Расположите диаграмму под таблицей данных, создайте и отредактируйте все надписи (название диаграммы – «Динамика “Прочих расходов” и “Чистого дохода” предприятия», название оси X – «Период отчетности», Y – «Значения») в соответствии с пп. 6,7,8 лабораторной работы №2.

5.Нахождение «Прочих расходов» данного месяца для достижения требуемого «Чистого дохода» этого же месяца.

Допустим, что нам необходимо получить в мае месяце чистый доход в размере 70000 р. Как при этом пересчитать затраты на производство продукции, непосредственно изменив статью «Прочие расходы»?

Активизируйте ячейку F18 и войдите в режим «Подбор параметра…» меню «Сервис» панели задач (щелчок левой кнопкой мыши). В открывшемся окне «Подбор параметра» в строке «Значение:» наберите цифру 70000 (предварительно установив курсор

редактирования текста «мигающий символ “ ”»), затем установите курсор редактирования текста в строку «Изменяя значение ячейки:», наведите курсор “Excel” (большой белый крест) на ячейку F13 и щелкните левой кнопкой мыши. Нажмите кнопку ”ОК” и запустите цикл итерационных вычислений. Если решение найдено (см. появившееся сообщение), снова нажмите кнопку ”ОК”.

Внимание! Если окно «Подбор параметра» закрывает нужные ячейки таблицы данных, то наведите курсор “Excel” (большой белый крест) на верхнюю синюю полоску окна и, удерживая левую кнопку мыши в нажатом положении, «отбуксируйте» окно в удобную позицию. Другой способ – навести курсор на красную кнопку соответствующей строки окна «Подбор параметра» и щелкнуть левой кнопкой мыши, временно уменьшая окно. Для придания окну прежнего размера необходимо снова воспользоваться красной кнопкой.

Обратите внимание на изменение формы графиков диаграммы.

6. Нахождение статей расходов и дохода предшествующих месяцев для достижения требуемого «Чистого дохода» данного месяца.

Измените правило образования статьи «Прочие расходы», предварительно удалив все значения ячеек C13 G13 и введя затем в ячейку C13 формулу «В13+10000». Скопируйте формулу во все ячейки D13 G13. Теперь значение прочих расходов каждого месяца зависят от расхода начального месяца (январь).

Проделайте все операции п. 5, установив значение чистого дохода в мае месяце в 100 000 р., изменяя прочие расходы в январе месяце.

Обратите внимание - как изменились статьи расходов и дохода всех месяцев.

7. Отображение другой совокупности графиков на диаграмме.

Откройте “висящее” меню «Формат области диаграммы», наведя курсор на свободное поле области диаграммы и щелкнув правой кнопкой мыши. Выберите в меню режим «Исходные данные», щелкнув по нему левой кнопкой мыши, и нажмите красную кнопку в строке «Диапазон:» появившегося окна «Исходные данные». «Поднявшись с помощью вертикальной полосы прокрутки (правая сторона экрана) вверх сделайте доступным обзору все данные. Используя клавишу консоли “Ctrl”, введите последовательно данные, соответствующие: «Периоду отчетности», «Налогу на добавленную стоимость», «Прочим расходам», «Чистому доходу предприятия». Снова нажмите красную кнопку окна «Исходные данные - Диапазон:», которое размещается в правом верхнем углу экрана. Нажмите кнопку “ОК” раскрывшегося окна «Исходные данные».

Обратите внимание на иную совокупность графиков диаграммы.

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

На какие сроки (в годах) нужно положить вклады в размерах: 1000 р., 2000 р., 3000 р., 4000 р., 5000 р., в банк под годовые процентные ставки 5%, 10%, 15%, 20%, 25% чтобы получить суммы вкладов в 2000 р., 4000 р., 6000 р., 8000 р., 10000 р. соответственно? Сроки вычислить с точностью до одного года. Построить диаграмму зависимостей срока вклада от процентной ставки.

Для решения составьте шаблон, заказав соответствующий формат ячеек.

	А	B	C	D	E	F
1	Размер вклада	1 000,00р.	2 000,00р.	3 000,00р.	4 000,00р.	5 000,00р.
2	Процентная ставка	5%	10%	15%	20%	25%
3	Срок вклада	5	5	5	5	5
4	Сумма вклада	=B1*(1+B3)^B2

Обратите внимание на то, что первоначально все сроки вкладов установлены равными, например, 5 годам. Сумма вкладов вычисляется по формуле сложного процента. Функциональная зависимость ячеек необходима для решения обратной задачи с помощью режима «Подбор параметра …» меню «Сервис» панели задач “Excel”.

Внимание! Поскольку Вы создаете шаблон с новой строки “Excel”, продолжая лабораторную работу, то реальные номера строк шаблона будут другими.

Размножьте формулу ячейки B4 в ячейки С4 F4. Далее следуйте логике п.5.

9.Сдайте выполненную лабораторную работу преподавателю, получив подпись в рабочей тетради!

10.Закройте документ, предварительно сохранив его по заданному адресу.

2

	Диаграмма к лабораторной работе №3
Динамика "Прочих расходов" и "Чистого дохода" предприятия			Налог на
			добавленну
			ю стоимость,
Значения		20%

120 000,00р.						прочие
100 000,00р.
						расходы
80 000,00р.
60 000,00р.						Чистый
40 000,00р.
						доход
20 000,00р.						предприятия,
0,00р.
январь	февраль	март	апрель	май	июнь	Период отчетности

3

Таблица к обратной задаче лабораторной работы №3

Размер вклада		1 000,00р.	2 000,00р.	3 000,00р.	4 000,00р.	5 000,00р.
Процентная ставка		5%	10%	15%	20%	25%
	Срок вклада	14	7	5	4	3
Сумма вклада		2 000,00р.	4 000,00р.	6 000,00р.	8 000,00р.	10 000,00р.

	Зависимость срока вклада от процентной ставки банка
Срок вклада
15
10
5
0
5%	10%	15%	20%	25%

Срок вклада

Процентная ставка банка

4