На втором шаге нужно указать область рабочего листа, где находится список. По умолчанию программа предлагает список, который расположен на активном рабочем листе. Здесь можно указать только часть списка, если для построения опорной таблицы остальная часть не нужна. Щелкните по командной кнопке Next, чтобы перейти к следующему шагу.
На третьем шаге задается структура (Layout) опорной таблицы. В правом поле расположены названия полей списка, а в среднем - представление структуры опорной таблицы. В это поле включается поле названий строк, поле названий столбцов и поле данных. Чтобы задать структуру таблицы, следует отбуксировать мышью названия полей списка в соответствующие поля таблицы. При построении опорной таблицы придерживайтесь следующих правил. Названия полей, по значениям которых будет проводится анализ данных, отбуксируйте в поле данных Data. В поле названий строк Row и в поле названий столбцов Column переместите названия полей, по значениям которых классифицируются (разбиваются) данные. Затем щелкните по командной кнопке Next, чтобы перейти к следующему шагу.
На четвертом шаге можно установить некоторые опции для построения таблицы. В поле ввода PivotTable Starting Cell указывается адрес, с которого начинается построение таблицы (левый верхний угол). В поле ввода PivotTableName можно указать название таблицы. По умолчанию программа использует название PivotTable1 с последующим увеличением номера таблицы. Если включить контрольные индикаторы Grand Totals forColumns и Grand Totals forRows, то в каждой строке и в каждом столбце опорной таблицы будут вычисляться общие суммы. Контрольный индикатор SaveDataWithTableLayout служит для сохранения опорной таблицы с заданной структурой. Включение контрольного индикатора AutoFormatTable позволяет автоматически сформатировать опорную таблицу. После закрытия диалогового окна Конструктора командной кнопкой Finish программа Excel размещает опорную таблицу в указанной позиции.
Между опорной таблицей и исходным списком возникает статическая связь, т.е. связь, которая существует только в момент построения таблицы. При изменении данных в списке опорная таблица не будет изменяться. Чтобы привести в соответствие содержимое таблицы и содержимое списка, нужно вызвать директиву RefreshData из меню Data.
Редактирование опорных таблиц.
Названия полей, по которым классифицируются данные, можно перемещать с помощью мыши, чтобы изменить структуру таблицы. Если в опорную таблицу необходимо добавить новые поля, то вызовите директиву PivotTable из меню Data или щелкните по пиктограмме Конструктора таблиц на опорной (Pivot) панели. После этого появится третье окно Конструктора опорных таблиц, в котором можно изменить размещение значений полей в таблице. Следите за тем, чтобы при вызове этой директивы указатель ячеек находился внутри таблицы, в противном случае Конструктор начнет построение новой таблицы.
Для расчета себестоимости продукции следует учесть все составляющие производства, которые могут повлиять на себестоимость предлагаемого изделия. Прямые затраты на производство предлагаемого изделия составят:
· комплектующие материалы и полуфабрикаты;
· зарплата производственных рабочих;
· амортизация или аренда оборудования;
· топливо и налоги на технические нужды;
· отчисления в фонд социального страхования;
· отчисления в пенсионный фонд.
Также следует выделить косвенные затраты:
· цеховые расходы;
· производственные расходы;
· расходы при поставке бракованных комплектующих;
· прочие производственные расходы;
· непроизводственные расходы.
Сложив все эти показатели, получите плановую себестоимость предлагаемого изделия, затем следует учесть расходы на отчисления в инновационный фонд и на налог на дороги.
С учетом всех вышеперечисленных пунктов предлагается модель расчета себестоимости единицы продукции, состоящаю из следующих списков-таблиц:
· Комплектующие материалы;
· Зарплата;
· Амортизация;
· Расчет себестоимости единицы продукции;
Таблица 3.1.1. Таблица комплектующих материалов
| A | B | C | D | E | |
| 3 | № п/п | Наименование | Производи-тель | Характеристика | Цена, грн. |
| 4 | 1 | Материнская плата | MB ASUS P2B-F | Intel 440BX AGPset, 4xDIMM, 5xPCI, 2xISA | 432,00 |
| 5 | 2 | Процессор | Intel Pentium II | 350 | 648,00 |
| 6 | 3 | Память | Samsung | SDRAM 128Mb | 536,00 |
| 7 | 4 | Винчестер | Seagate ST38641A | 8.4Gb rpm 5400 Ultra-DMA | 576,00 |
| 8 | 5 | Видеокарта | ASUS AGP-V3200 | 3DFx Banshee, 16Mb SGRAM | 324,00 |
| 9 | 6 | Дисковод | Mitsumi | 1,44 mb | 44,00 |
| 10 | 7 | Корпус | ATX | 128,00 | |
| 11 | 8 | Клавиатура, коврик, мышь | 40,00 | ||
| 12 | 9 | CD-ROM | ASUS | 36x | 152,00 |
| 13 | 10 | Звуковая плата | Creative | 64 PCI (OEM) | 100,80 |
| 14 | 11 | Монитор | LG | 15" LG SW 57M Multimedia | 576,00 |
| 15 | 12 | Модем | USR Courier | 57600 int V.Everything X2.US Canada | 604,80 |
| 16 | Итого: | =СУММ(E4:E15) |
Как видно из этой таблицы ключевой ячейкой будет ячейка Е16, которая будет связана с таблицей расчета себестоимости продукции прямой связью.
В следующем списке-таблице мы рассмотрим расчет заработной платы сотрудников предприятия. Расчет стоимости работы в день берется из расчета 22 рабочих дня в месяц (Таб. 3.1.2).
| A | B | C | D | E | F | |
| 4 | № | Должность | Ставка, грн | Отработано, дней | Кол-во, чел. | Сумма, грн. |
| 5 | 1 | Сборочный рабочий | 300 | 1 | 2 | =C5/22*D5*E5 |
| 6 | 2 | Бухгалтер | 350 | 0,5 | 1 | =C6/22*D6*E6 |
| 7 | 3 | Менеджер по реализации | 300 | 1 | 1 | =C7/22*D7*E7 |
| 8 | 4 | Директор | 400 | 1 | 1 | =C8/22*D8*E8 |
| 9 | ||||||
| 10 | Итого: | =СУММ(F5:F8) | ||||
| 12 | Зарплата всех сотрудников в день составляет 63,64 грн. | |||||
| 13 | В день в среднем производиться 5 компьютеров. Отсюда затраты на 1 компьютер | |||||
| 14 | составят 63,64/5= | =F10/5 | ||||
Затраты на оплату труда при производстве одного компьютера вычислены в ячейке С14. Данная ячейка также напрямую связана с таблицей расчета себестоимости.
И последняя список-таблица – расчет амортизации оборудования (Таб. 3.1.3).
Таблица 3.1.3. Расчет амортизации оборудования
| A | B | C | D | E | F | G | H | I | |
| 4 | № | Наименование оборудования | Цена, грн | Кол-во | Срок службы, лет | Суммарная амортизация за год | Стоимость 1 часа работы (грн) | Отработанное время (лет) | Сумма амортизации на 1 компьютер |
| 5 | 1 | Тестовая сеть: | |||||||
| 6 | - Сервер Pentium II 450 | 6000 | 1 | 3 | =C6*6,25%*4 | =F6/(22*12*8) | 1 | =G6*3 | |
| 7 | - Терминал Pentium II 350 | 4161,6 | 4 | 3 | =C7*6,25%*4 | =F7/(22*12*8) | 1 | =G7*3 | |
| 8 | 2 | UPC | 1000 | 1 | 3 | =C8*6,25%*4 | =F8/(22*12*8) | 1 | =G8*3 |
| 9 | 3 | Набор инструментов | 100 | 2 | 3 | =C9*6,25%*4 | =F9/(22*12*8) | 1 | =G9*3 |
| 10 | Итого: | =СУММ(I5:I9) | |||||||
Из последней таблицы амортизации оборудования берем ключевую ячейку I10 и переходим к рассмотрению таблицы, в которой производим расчет себестоимости единицы продукции (Таб. 3.1.4).
Таблица 3.1.4 Расчет себестоимости единицы продукции.
| B | C | D | E | F | |
| 3 | № | Статьи затрат | Сумма(грн.) | Обоснование | |
| 4 | 1 | Сырье и основные материалы | 0 | ||
| 5 | 2 | Комплектующие и полуфабрикаты | =Комплектующие!E16 | Смотри таб. Комплектующие | |
| 6 | 3 | Топливо и энергия на технические нужды | 150 | по факту | |
| 7 | 4 | Зарплата производственных рабочих | =Зарплата!C14 | Смотри таб. Зарплата | |
| 8 | 5 | Отчисления на социальные нужды | =D7*5,5% | 4%+1,5% от суммы зарплаты | |
| 9 | 6 | Отчисления на пенсионное страхование | =D7*32% | 32% от суммы зарплаты | |
| 10 | 7 | Амортизация или аренда оборудования | =Амортизация!I10 | Смотри таб. Амортизация | |
| 11 | Получ. затраты | =СУММ(D4:D10) | |||
| 12 | 8 | Цеховые расходы | =D7*40% | 40% от п.4 | |
| 13 | 9 | Производственные расходы | =D7 | 100% от п.4 | |
| 14 | 10 | Потери брака | =D13*4,5% | 4.5% от сум. пр. расходов | |
| 15 | 11 | Прочие производственные расходы | =D13*10% | 10% от сум. пр. расходов | |
| 16 | 12 | Непроизводственные расходы | =D13*5% | 5% от сум. пр. расходов | |
| 17 | Итого косвенных расходов | =СУММ(D12:D16) | |||
| 18 | 13 | Плановая себестоимость | =D11+D17 | сумма пп 1-12 | |
| 19 | 14 | Налог на дороги | =D18*1,2% | 1,2% от объема | |
| 20 | 15 | Отчисления в инновационный фонд | =D18*1% | 1% от дохода | |
| 21 | 16 | Полная себестоимость | =СУММ(D18:D20) | сумма пп 13-15 | |
| 22 | |||||
| 23 | Прибыль= | 20% | |||
| 24 | Стоимость нашего изделия без НДС будет составлять: себестоимость * % на прибыль = | =D21*D23+D21 | |||
| 25 | С НДС: | =F24*1,2 | |||
В последней таблице представлена схема расчета себестоимости единицы продукции. Здесь же можно установить величину ставки на прибыль в ячейке D23 (в данном случае это 20%) и модель рассчитает цену товара с учетом прибыли без НДС и с НДС, т.е. сразу можно получить результат цены на товар. При изменении любых исходных данных, изменении действующего законодательства можно отредактировать списки-таблицы, расчетные функции и модель будет готова к работе – это и делает ее гибким инструментом в руках пользователя.