Практикум "Информационные объекты табличного документа и действия с ними" (1 курс)

ИНФОРМАЦИОННЫЕ ОБЪЕКТЫ ТАБЛИЧНОГО ДОКУМЕТА И ДЕЙСТВИЯ С НИМИ

§  Задание 1. Расчет срока реализации продукта

§  Задание 2. Расчет затрат на командировки

§  Задание 3. Расчет стоимости автомобиля, купленного на аукционе

§  Задание 4. Расчет квартплаты

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

_________________________________________________________________________________

Задание 1. Расчет срока реализации продукта                                             назад

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

Исходными данными являются наименования продуктов, поставщики продуктов и их местонахождение, дата изготовления, срок хранения продукта.

Технология выполнения задания

1.1.Автозаполнение. Вычисления по формулам

1.     Скопируйте в свою папку файл /Заготовки Excel. Откройте файл, лист Продукция фабрик. Цветом выделены ячейки, в которых нужно произвести расчеты.

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

3.     Заполните столбцы D и E, использовав автозаполнение и копирование данных.

4.     Заполните столбец G датами по дням, использовав автозаполнение.

5.     Для заполнения столбца G датами по месяцам удалите все даты, кроме первой.

6.     Выделите все ячейки столбца G вместе с начальной датой.

7.     Выполните команду Главная} Редактирование} Заполнить и выберите пункт Прогрессия.

8.     В диалоговом окне Прогрессия в разделе Тип установите переключатель даты, а в разделе Единицы – переключатель месяц. Нажмите ОК.

9.     В столбце H рассчитайте срок хранения в сутках. Для этого в ячейку H8 введите формулу =F8*30 (будем считать, что в месяце 30 дней) и распространите её на другие ячейки столбца, использовав автозаполнение.

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

10. В столбце I рассчитайте срок реализации продукта. Для этого в ячейку I8 введите формулу =G8+H8 и распространите её на другие ячейки столбца, использовав автозаполнение.

11. Заполните столбец B последовательными номерами любым известным вам способом (формулой либо с помощью команды Прогрессия и т.д.)

1.2.Форматирование таблицы

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

2.     Подберите высоту строки заголовка по образцу, перетаскивая нижнюю границу строки.

3.     Выделите заголовки столбцов и выполните команду Главная} Выравнивание} Перенос текста . Установите горизонтальное и вертикальное выравнивание заголовков – по центру.

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

5.     Сохраните выполненное задание.

Образец выполненной работы:

Задание 2. Расчет затрат на командировку                                                  назад

По данным, представленным в таблице, рассчитать:

o   сумму выплат на командировку, определяемую бухгалтерией;

o   фактические зарплаты на командировку;

o   сумму возмещения за командировочные расходы;

o   суммарные вычеты из прибыли предприятия за счет перерасхода средств.

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

Правила расчета

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

Сумма выплат на командировку =

Количество дней * (Расходы + Гостиничные) + Средняя стоимость проезда * 2.

ü Фактические расходы на командировку составляют затраты на проезд в оба конца (по предъявляемым билетам) плюс затраты на проживание (по предъявляемым квитанциям) плюс затраты на непредвиденные расходы:

Фактическая сумма затрат на командировку =

Общая стоимость проживания + Стоимость проживания + Стоимость проезда в оба конца + Затраты на непредвиденные расходы.

ü Сумма возмещения командировочных расходов составляет разницу между фактическими затратами на командировку и суммой денег, выданной бухгалтерией:

Сумма возмещения командировочных расходов = Фактическая сумма затрат на командировку – Сумма выплат за командировку.

ü Расчет вычетов из прибыли предприятия определяется перерасходами на проживание.

Технология выполнения задания

1.     Откройте файл /Заготовки Excel, лист Отчет за командировку. Цветом выделены ячейки, в которых нужно произвести расчеты.

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

Примечание. Для получения абсолютной ссылки щелкните в ячейке, ссылку на которую вы хотите получить, и нажмите клавишу F4.

Таблица: Формулы для расчета затрат на командировку

Ячейка	Формула	Пояснение
G17	=E17 – D17	Количество дней, которое сотрудник был в командировке
H17	=F17*2	Стоимость проезда в оба конца
I17	=G17*($C$11+$C$12)	Общая сумма суточных затрат
J17	=H17+I17	Сумма, выдаваемая бухгалтерией
N17	=M17*G17	Общая стоимость проживания
O17	=N17+K17+L17+$C$11*G17	Общая сумма фактических затрат на командировку
P17	=O17 – J17	Сумма возмещения командировочных расходов
Q17	=N17 – $C$12*G17	Вычеты из прибыли предприятия

3.     Щелкните в ячейке С25 и выполните команду Главная} Редактирование} Сумма . В ячейку вставится функция СУММ() и предложит в качестве аргумента диапазон ячеек для суммирования .

4.     Отредактируйте диапазон, выделив ячейки Q17:Q23. Нажмите OK.

5.     Отформатируйте заголовки «Планируемые расходы» и «Фактические расходы» по образцу.

6.     Сохраните выполненное задание.

Образец выполненной работы:

Задание 3. Расчет стоимости автомобиля, купленного на аукционе        назад

Рассчитать стоимость автомобиля после доставки его в Санкт-Петербург из Японии. Доставка осуществляется физическим лицом. Автомобиль покупается в 2010 году на одном из аукционов в Японии. Возраст автомобиля – от 3 до 5 лет. Расчеты выполняются в разных валютах. Исходными данными являются:

o   курсы валют (доллары США, евро, японская иена) по отношению к рублю;

o   справочные данные по Японии (налог с продаж в процентах, аукционный сбор в Японии, стоимость доставки с аукциона до порта);

o   фиксированные платежи в рублях (морской фрахт из Японии до Владивостока, дополнительные платежи во Владивостоке, контейнер до Санкт-Петербурга);

o   таблица стоимости автомобилей на аукционе, объем двигателя и единая таможенная ставка на литраж.

Правила расчета

ü Затраты на территории Японии складываются из цены автомобиля на аукционе, налога с продаж, аукционного сбора и доставки автомобиля до порта.

ü Затраты на территории России состоят из таможенных сборов, дополнительных платежей во Владивостоке, необходимых при оформлении документов, и оплаты контейнера до Санкт-Петербурга.

ü Таможенные сборы рассчитываются по единой таможенной ставке (ЕТС). Значения ЕТС берутся из официальной таблицы и зависят от года выпуска автомобиля и объема его двигателя.

Технология выполнения задания

1.     Откройте файл /Заготовки Excel, лист Таможня. Цветом выделены ячейки, в которых нужно произвести расчеты.

2.     Отформатируйте таблицу согласно образцу, представленному на листе.

3.     В ячейке C13 рассчитайте курс одной иены по формуле: =C12/B12.

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

Таблица: Формулы для расчета стоимости автомобиля

Ячейка	Формула	Пояснение
Расчет затрат на территории Японии
D17	=C17+$G$11	Стоимость автомобиля с аукционным сбором
E17	=$G$10*D17	Налог с продаж
F17	=D17+E17+$G$12	Общая цена автомобиля в Японии
G17	=F17*$C$13	Стоимость автомобиля в рублях
H17	=G17+$K$10	Стоимость автомобиля вместе с затратами на морскую перевозку
Расчет затрат на территории России
K17	=I17*J17	Размер таможенной пошлины (в евро)
L17	=K17*$C$11	Перевод размера таможенной пошлины в рубли
M17	=H17+L17+$K$11+$K$12	Окончательная цена автомобиля в рублях после доставки его в Санкт-Петербург
N17	=C17*$C$13	Стоимость автомобиля на аукционе в Японии (в рублях)
017	M17/N17	Соотношение конечной (в Санкт-Петербурге) и первоначальной (на аукционе в Японии) цены автомобиля

5.     Сохраните выполненное задание.

Образец выполненной работы:

 Задание 4. Расчет квартплаты                                                                    назад

Рассчитать ежемесячную квартирную оплату для всех квартир в доме ЖСК и найти общую сумму выплат.

Размер оплаты зависит от площади квартиры и количества жильцов, прописанных в квартире.

Исходными данными для выполнения задания является таблица стоимости услуг и характеристики квартир в доме.

Технология выполнения задания

1.     Откройте файл /Заготовки Excel, лист Расчет квартплаты. Цветом выделены ячейки, в которых нужно произвести расчеты.

2.     Отформатируйте заголовки столбцов (строка 25) согласно образцу, приведенному на листе.

3.     В ячейке I20 рассчитайте общую плату за 1 м² как сумму значений ячеек C17:O17.

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

5.     В ячейке I22 найдите сумму выплат за пользование радио и телевизионной антенной.

6.     В столбце F рассчитайте общую плату за всю площадь квартиры как произведение площади квартиры на общую плату за 1 м² (I20).

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

8.     В столбце H рассчитайте месячную квартплату за одну квартиру как сумму общей платы за всю площадь, общей платы за всех жильцов и платы за пользование радио и телевизионной антенны (I22).

9.     В столбце I рассчитайте месячную плату за все квартиры данного типа.

10. В ячейке D38 рассчитайте ежемесячную квартплату для всего многоквартирного дома.

11. Сохраните выполненное задание.

Образец выполненной работы: