Практическая часть Вариант 9
Фирма ООО «Титаник» предоставляет услуги по перевозке грузов. Для определения затрат на приобретение материалов ежемесячно ведется учет количества приобретаемого топлива. Данные о ценах и количестве приобретенного топлива в течение месяца приведены на рис. 9.1.
1. Построить таблицы по приведенным ниже данным.
2. Выполнить расчет средней цены 1 л топлива по каждому виду, данные расчета занести в таблицы (рис. 9.1). Средняя цена определяется как отношение общей суммы затрат на приобретение данного вида топлива в течение месяца к общему количеству приобретенного топлива за месяц.
3. Организовать межтабличные связи для автоматического формирования ведомости затрат на приобретение топлива за квартал.
4. Сформировать и заполнить сводную ведомость затрат на приобретение топлива за квартал, определить среднюю цену 1 л топлива за квартал (рис. 9.2).
5. Результаты расчета средней цены 1 л топлива по каждому месяцу и по каждому виду топлива представить в графическом виде.
Ведомость затрат на приобретение ГСМ за январь 2006 г.
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1 л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,20 |
250 |
14,05 |
200 |
14,25 |
310 |
|
Бензин АИ-92 |
15,40 |
310 |
15,15 |
275 |
15,50 |
355 |
|
Бензин АИ-95 |
16,25 |
145 |
16,20 |
120 |
16,35 |
170 |
|
Средняя цена 1 л горючего за месяц: |
|
||||||
Ведомость затрат на приобретение ГСМ за февраль 2006 г.
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1 л |
|||
Цена, руб. |
кол-во, л |
цена, руб. |
кол-во, Л |
цена, руб. , |
кол-во, л |
||
Дизельное топливо |
14,30 |
240 |
14,35 |
250 |
14,25 |
270 |
|
Бензин АИ-92 |
15,45 |
320 |
15,50 |
320 |
15,55 |
300 |
|
БензинАИ-95 |
16,30 |
160 |
16,35 |
180 |
16,40 |
150 |
|
Средняя цена 1 л горючего за месяц: |
|
||||||
Ведомость затрат на приобретение ГСМ за март 2006 г
Наименование материала
|
1 партия |
2 партия |
3 партия |
Средняя цена за 1 л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,50 |
220 |
14,45 |
250 |
14,55 |
200 |
|
Бензин АИ-92 |
15,65 |
290 |
16,60 |
320 |
15,75 |
280 |
|
Бензин АИ-95 |
16,45 |
155 |
16,40 |
195 |
16,50 |
120 |
|
Средняя цена 1 л горючего за месяц: |
|
||||||
Рис. 9.1. Данные о затратах на приобретение ГСМ по месяцам
Расчетный период |
|
с |
по |
__.__.20__ |
__.__.20__ |
СВОДНАЯ ВЕДОМОСТЬ ЗАТРАТ НА ПРИОБРЕТЕНИЕ ГСМ
за 1 квартал 2006 г.
Наименование материала |
январь |
февраль |
март |
Средняя цена за 1 л |
|||
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
||
Дизельное топливо |
|
|
|
|
|
|
|
Бензин АИ-92 |
|
|
|
|
|
|
|
Бензин АИ-95 |
|
|
|
|
|
|
|
Средняя цена. 1 л горючего за квартал: |
|
||||||
Бухгалтер_____________________________
Рис. 9.2. Ведомость затрат на приобретение ГСМ за квартал
Описание алгоритма решения задачи
1. Запустить табличный процессор МS Ехсеl.
2. Создать книгу с именем «Титаник».
3. Лист 1 переименовать в лист с названием Январь.
4. На рабочем листе Товары МS Excel создать таблицу Ведомости затрат на приобретение ГСМ за январь 2006 г.
5. Заполнить таблицу Ведомость затрат на приобретение ГСМ за январь 2006 г. исходными данными (рис. 1).
Рис. 1. Расположение таблицы «Ведомость затрат на приобретение ГСМ за январь 2006 г.» на рабочем листе январь МS Excel
6. Лист 2 переименовать в лист с названием Февраль
7. На рабочем листе Февраль МS Excel создать таблицу - Ведомость затрат на приобретение ГСМ за февраль 2006 г.
8. Заполнить таблицу со списком затрат на приобретение ГСМ за февраль 2006г исходными данными (рис. 2).
Рис.2. Расположение таблицы Ведомость затрат на приобретение ГСМ за февраль 2006 г. на рабочем листе Февраль МS Excel
6. Лист 3 переименовать в лист с названием Март
7. На рабочем листе Март МS Excel создать таблицу - Ведомость затрат на приобретение ГСМ за март 2006 г.
8. Заполнить таблицу со списком затрат на приобретение ГСМ за март 2006г исходными данными (рис. 3).
Рис. 3. Расположение таблицы Ведомость затрат на приобретение ГСМ за март 2006 г. на рабочем листе Март МS Excel
Рассчитаем среднюю цену за 1л в течение каждого месяца
Выходные таблицы с расчетными формулами приведены ниже
Ведомость затрат на приобретение ГСМ за январь 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,2 |
250 |
14,05 |
200 |
14,25 |
310 |
=(B4*C4+D4*E4+F4*G4)/(C4+E4+G4) |
Бензин АИ-92 |
15,4 |
310 |
15,15 |
275 |
15,5 |
355 |
=(B5*C5+D5*E5+F5*G5)/(C5+E5+G5) |
Бензин АИ-95 |
16,25 |
145 |
16,2 |
120 |
16,35 |
170 |
=(B6*C6+D6*E6+F6*G6)/(C6+E6+G6) |
Средняя цена 1 л горючего за месяц: |
=(H4+H5+H6)/3 |
||||||
Ведомость затрат на приобретение ГСМ за январь 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,2 |
250 |
14,05 |
200 |
14,25 |
310 |
14,18 |
Бензин АИ-92 |
15,4 |
310 |
15,15 |
275 |
15,5 |
355 |
15,36 |
Бензин АИ-95 |
16,25 |
145 |
16,2 |
120 |
16,35 |
170 |
16,28 |
Средняя цена 1 л горючего за месяц: |
15,27 |
||||||
Таким образом, средняя цена 1л горючего за месяц в январе составила 15,27 руб.
Ведомость затрат на приобретение ГСМ за февраль 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,3 |
240 |
14,35 |
250 |
14,25 |
270 |
=(B4*C4+D4*E4+F4*G4)/(C4+E4+G4) |
Бензин АИ-92 |
15,45 |
320 |
15,5 |
320 |
15,55 |
300 |
=(B5*C5+D5*E5+F5*G5)/(C5+E5+G5) |
Бензин АИ-95 |
16,3 |
160 |
16,35 |
180 |
16,4 |
150 |
=(B6*C6+D6*E6+F6*G6)/(C6+E6+G6) |
Средняя цена 1 л горючего за месяц: |
=(H4+H5+H6)/3 |
||||||
Ведомость затрат на приобретение ГСМ за февраль 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,3 |
240 |
14,35 |
250 |
14,25 |
270 |
14,30 |
Бензин АИ-92 |
15,45 |
320 |
15,5 |
320 |
15,55 |
300 |
15,50 |
Бензин АИ-95 |
16,3 |
160 |
16,35 |
180 |
16,4 |
150 |
16,35 |
Средняя цена 1 л горючего за месяц: |
15,38 |
||||||
Ведомость затрат на приобретение ГСМ за март 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,5 |
220 |
14,45 |
250 |
14,55 |
200 |
=(B4*C4+D4*E4+F4*G4)/(C4+E4+G4) |
Бензин АИ-92 |
15,65 |
290 |
16,6 |
320 |
15,75 |
280 |
=(B5*C5+D5*E5+F5*G5)/(C5+E5+G5) |
Бензин АИ-95 |
16,45 |
155 |
16,4 |
195 |
16,5 |
120 |
=(B6*C6+D6*E6+F6*G6)/(C6+E6+G6) |
Средняя цена 1 л горючего за месяц: |
=(H4+H5+H6)/3 |
||||||
Ведомость затрат на приобретение ГСМ за март 2006 г. |
|||||||
Наименование материала |
1 партия |
2 партия |
3 партия |
Средняя цена за 1л |
|||
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
цена, руб. |
кол-во, л |
||
Дизельное топливо |
14,5 |
220 |
14,45 |
250 |
14,55 |
200 |
14,50 |
Бензин АИ-92 |
15,65 |
290 |
16,6 |
320 |
15,75 |
280 |
16,02 |
Бензин АИ-95 |
16,45 |
155 |
16,4 |
195 |
16,5 |
120 |
16,44 |
Средняя цена 1 л горючего за месяц: |
15,65 |
||||||
Лист 4 переименовать в лист с названием Затраты на приобретение ГСМ
На рабочем листе Затраты на приобретение ГСМ MS Excel создать таблицу, в которой будет содержаться цена и количество ГСМ за месяц.
Заполнить таблицу «Затраты на приобретение ГСМ» исходными данными (рис.4).
Наименование материала |
январь |
февраль |
март |
Средняя цена за 1 л |
|||
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
||
Дизельное топливо |
=январь!H4 |
=январь!C4+январь!E4+январь!G4 |
=февраль!H4 |
=февраль!C4+февраль!E4+февраль!G4 |
=март!H4 |
=март!C4+март!E4+март!G4 |
=(B7*C7+D7*E7+F7*G7)/(C7+E7+G7) |
Бензин АИ-92 |
=январь!H5 |
=январь!C5+январь!E5+январь!G5 |
=февраль!H5 |
=февраль!C5+февраль!E5+февраль!G5 |
=март!H5 |
=март!C5+март!E5+март!G5 |
=(B8*C8+D8*E8+F8*G8)/(C8+E8+G8) |
Бензин АИ-95 |
=январь!H6 |
=январь!C6+январь!E6+январь!G6 |
=февраль!H6 |
=февраль!C6+февраль!E6+февраль!G6 |
=март!H6 |
=март!C6+март!E6+март!G6 |
=(B9*C9+D9*E9+F9*G9)/(C9+E9+G9) |
Средняя цена. 1 л горючего за квартал: |
=(H7+H8+H9)/3 |
||||||
Рисунок 4 - «Затраты на приобретение ГСМ »
Наименование материала |
январь |
февраль |
март |
Средняя цена за 1 л |
|||
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
||
Дизельное топливо |
14,18 |
760 |
14,30 |
760 |
14,50 |
670 |
14,32 |
Бензин АИ-92 |
15,36 |
940 |
15,50 |
940 |
16,02 |
890 |
15,62 |
Бензин АИ-95 |
16,28 |
435 |
16,35 |
490 |
16,44 |
470 |
16,36 |
Средняя цена. 1 л горючего за квартал: |
15,43 |
||||||
Рисунок 5 – Выходная таблица Затраты на приобретение ГСМ
Путем создания межтабличных связей заполним созданную форму полученными данными таблицы
ООО «Титаник»
Расчетный период |
|
с |
по |
01.01.2006г |
31.03.2006г |
СВОДНАЯ ВЕДОМОСТЬ ЗАТРАТ НА ПРИОБРЕТЕНИЕ ГСМ
за 1 квартал 2006 г.
Наименование материала |
январь |
февраль |
март |
Средняя цена за 1 л |
|||
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
средняя цена, руб. |
количество, л |
||
Дизельное топливо |
14,18 |
760 |
14,30 |
760 |
14,50 |
670 |
14,32 |
Бензин АИ-92 |
15,36 |
940 |
15,50 |
940 |
16,02 |
890 |
15,62 |
Бензин АИ-95 |
16,28 |
435 |
16,35 |
490 |
16,44 |
470 |
16,36 |
Средняя цена. 1 л горючего за квартал: |
15,43 |
||||||
Бухгалтер_____________________________
Рис. 6. Ведомость затрат на приобретение ГСМ за квартал
Рисунок 7 - Средние цены на ГСМ по месяцам