Создание связанных таблиц

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

Цель: приобрести навыки связывания данных.

Задание: Рассчитать прибыль предприятия за месяц.

Технология выполнения:

1. На листе 1 книги Excel составить таблицу «Поступление товаров» на склад предприятия:

- таблица должна содержать следующие столбцы: N п/п, Наименование товара, Количество, Дата поступления, Цена за единицу товара, Итого;

- заполнить таблицу поступления товаров на склад фирмы в течение одного месяца. Наименований товаров – 5, записей в таблице не менее 20;

- для столбца Дата поступления задать формат ячеек Дата, для столбца Цена за единицу товара и Итого – денежный;

- значение в столбце «Итого» рассчитать;

- листу рабочей книги присвоить имя «Данные поступления».

2. На листе 2 создать итоговую таблицу «Закупочная цена товара»:

- колонкам таблицы присвоить имена, соответствующие наименованиям товаров (Заменить «товар 1» – наименованием товара из таблицы Поступление товаров и т.д.). Строкам таблицы присвоить имена: Наименование товара, Количество поступившего товара за месяц, Закупочная цена товара за месяц (см рис.1):

Рис.1 Образец таблицы Закупочная цена товара

- рассчитать таблицу, используя функцию СУММЕСЛИ и ссылки на таблицу «Закупочная цена товара»;

- листу 2 рабочей книги присвоить имя Закупочная цена товара;

- на листе 3 отобразить формулы расчета таблицы «Закупочная цена товара» (см. рис. 2).

Рис.2. Фрагмент таблицы формул

4. На листе 4 создать таблицы Виды затрат на единицу товара и Расчет отпускной цены товара (см рис.3).

Рис.3. Таблицы для расчета отпускной цены

Формулы для расчета:

Для расчета использовать абсолютные и относительные ссылки на этот же лист и на другие листы созданной книги Excel.

- Отчисления на НДС: закупочная цена * процент НДС;

- Отчисления на рекламу: =закупочная цена*отчисления на рекламу;

- Накладные расходы: =закупочная цена*процент накладных расходов;

- Складские расходы: =закупочная цена*процент складских расходов;

- Отчисления на аренду помещения: = аренда помещения/ количество поступившего товара за месяц;

- Коммунальные платежи: = коммунальные услуги / количество поступившего товара за месяц;

- Прочие расходы: = 500 руб. / количество товара за месяц;

- Расходы на упаковку: = закупочная цена*отчисления на упаковку;

- Торговая наценка: = закупочная цена*процент торговой наценки;

- Отпускная цена товара: = закупочная цена + Отчисления на НДС + Отчисления на рекламу + Накладные расходы + Складские расходы + Отчисления на аренду помещения + Коммунальные платежи + Прочие расходы + Торговая наценка + Расходы на упаковку + Торговая наценка.

- Листу 4 рабочей книги присвоить имя Отпускная цена товара.

5. На листе 5 отобразить формулы расчета отпускной цены товара.

6. На листе 6 создать таблицу Прибыль предприятия за месяц (см рис.4).

Рис.4. Образец таблицы Прибыль предприятия за месяц

1) Столбы Количество поступившего товара, закупочная цена и отпускная цена заполнить имеющимися данными из соответствующих таблиц. Для этого:

a) Поместите курсор в необходимую пустую ячейку, например количество поступившего товара1;

b) Поставьте знак «=»; перейдите на лист Поступление товара, поместите курсор в ячейку с нужным значением и нажмите Enter;

c) Аналогично перенесите другие значения.

2) Столбец Количество проданного товара заполнить самостоятельно.

3) Столбец Прибыль рассчитать по формуле:

Прибыль = (отпускная цена – закупочная цена)/количество поступившего товара*количество проданного товара.

4) На листе 7 показать формулы расчета таблицы Прибыль предприятия за месяц.