Расчет KPI в excel скачать

Программно-методический комплекс KPI-Excelkulagin_oleg

К списку услугПрограммно-методический комплекс KPI-Excel 4.0Программно-методический комплекс (ПМК) KPI-Excel предназначен для разработки и поддержки системы ключевых показателей деятельности (KPI) и оплаты по результатам работы (PRP) на предприятиях малого и среднего бизнеса.СКАЧАТЬ ПРЕЗЕНТАЦИЮВ состав ПМК KPI-Excel входят шаблоны таблиц, позволяющих:

• Формулировать стратегические цели компании в формате сбалансированной системы показателей (BSC).
• Выбирать стратегические показатели и определять стратегические задачи предприятия.
• Каскадировать стратегические цели на нижние уровни управления организацией.
• Определять служебные функции руководителей и сотрудников.
• Разрабатывать библиотеки KPI предприятия, руководителей и сотрудников.
• Выбирать и согласовывать ключевые показатели для разных должностей в компании.
• Формировать соглашения о целях («матрицы KPI»), ставить оперативные задачи и оценивать результаты работы руководителей и сотрудников предприятия.
• Рассчитывать оплату по результатам работы на основе рейтинга эффективности и индексов KPI руководителей и сотрудников.
• Осуществлять промежуточный контроль ключевых показателей деятельности на уровне компании, структурных подразделений и рядовых сотрудников.
• Отслеживать динамику результатов предприятия в целом, руководителей подразделений и их подчиненных.

• Три базовых файла (книги в формате MS Excel): «Директор», «Руководитель», «Сотрудник», на основе которых Вы можете создавать иерархию рабочих файлов KPI-Excel для любых должностей в соответствии со структурой вашей компании.
• Цифровая книга Олега Кулагина «Управление по целям. Секреты технологии KPI».
• Библиотека целей и служебных функций, содержащая наиболее распространенные формулировки целей предприятия и функций, используемых для разработки ключевых показателей деятельности организации и сотрудников.
• Библиотека показателей деятельности «KPI-Library 2019», содержащая более 400 наиболее актуальных показателей деятельности с приведением их названия, общего описания и альтернативных расчетных формул (книга в формате MS Excel).
• Онлайн-поддержка («горячая линия») по использованию ПМК KPI-Excel на вашем предприятии.

Программно-методический комплекс KPI-Excel существенно упрощает проектирование и внедрение системы целевого управления компанией на основе KPI и избавляет от необходимости разрабатывать собственные или приобретать дорогостоящие программные продукты.

KPI-Excel – простой, удобный и надежный инструмент для проектирования и поддержки системы KPI вашего предприятия.

СКАЧАТЬ ПРЕЗЕНТАЦИЮ

К списку услуг

Here is a super slick Interactive Excel KPI Dashboard I prepared with some slicer magic. It will be a good practice of creative use of pivot table tools. Downloadable file is included.

Now let’s see how to build an Interactive Excel KPI Dashboard using pivot table tools:

I prepared a summary data for 4 departments; welding, cutting, assembly and packing. KPI’s for these 4 departments are selected as efficiency, productivity and scrap rate. Data set includes values for 12 months (4x3x12=144 rows). Here is how data set looks:

Select all the data and insert a pivot table. Arrange fields as shown below (Don’t look for Rest field at this point. We are going to add it in next step):

Click on the pivot table to activate it and add a calculated field from the menu shown below:

Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:

Set KPI filter as “Productivity” and make 2 copies of the pivot table (by copy and paste). Set KPI filter for those tables as “Efficiency” and “Scrap Rate”. Here is how it is supposed to look:

Insert a donut chart for each of these pivot tables. Cut them and paste them to another sheet. Format your charts to look like this:

Now make another copy of the first chart and arrange fields as shown below:

When finished, make 2 copies of this pivot chart too:

Now merge 3 cells and type “=B5” (without bracelets). B5 is the cell that contains result value of first pivot table (you can see from previous picture). Make sure to disable gridlines here. Then select your merged cell and copy it. Then paste it, as linked picture, to the sheet that has your donut charts. And drag it over related chart.

We are finished our donut charts at this point. It is time for our column charts. We need a 3 column data table for each of our column charts. Here is how I do it:

Place a 3 column grid just under the second set of pivot tables. First row will contain column titles. First column will contain month names. Remaining 2 columns will be filled by formulas. Here are the formulas:

Result Column Formula:

=SUMIFS(Sheet1!$E:$E;Sheet1!$A:$A;Sheet2!$A$14;Sheet1!$B:$B;Sheet2!$B$11;Sheet1!$D:$D;Sheet2!$A20)

This formula fetches result values based on department and KPI values selected on the pivot table above and month name in first column. You can see how to use this function from here SUMIFS FUNCTION.

Selection Column Formula:

=IF(A20=$A$15;B20;NA())

This formula checks selected month on the pivot table above and shows result value from 2nd column if month matches. If it doesn’t match, it shows #N/A.

Here are the tables I prepared:

Now select first table and insert a column chart. For formatting:

• Double-click on the orange column and set is as secondary axis.
• Set Series Overlap and Gap Width to 100%
• Delete both axes, chart title, legend and grid lines.
• Change column colors and set Shape Outline for the chart as “No Fill”

Here is how your chart should look like after all these make-up:

Do the same for other tables (other KPI’s), you can also copy chart formatting to do this faster. Then place these chart next to your donut charts:

Now comes the interactivity part. Click on your first donut chart and insert a slicer from INSERT ribbon. Select Month Name as field. Make sure settings are made as shown below:

Now customize you slicers as a 3×4 button grid with button height and width as 1,2cmx1,2cm. Change colors to match with your charts. You can see how to do this from Customize Slicers. Here is how it should look like:

Видео (кликните для воспроизведения).

Add a final slicer for departments. You can do this by clicking on any of the charts and inserting a slicer. This slicer must be connected to all pivot tables.

Place it on top of your charts, add KPI texts over slicers and our dashboard is done.

You can download this Interactive Excel KPI Dashboard from the link below:

Download Interactive Excel KPI Dashboard

Here is a super slick Interactive Excel KPI Dashboard I prepared with some slicer magic. It will be a good practice of creative use of pivot table tools. Downloadable file is included.

Now let’s see how to build an Interactive Excel KPI Dashboard using pivot table tools:

I prepared a summary data for 4 departments; welding, cutting, assembly and packing. KPI’s for these 4 departments are selected as efficiency, productivity and scrap rate. Data set includes values for 12 months (4x3x12=144 rows). Here is how data set looks:

Select all the data and insert a pivot table. Arrange fields as shown below (Don’t look for Rest field at this point. We are going to add it in next step):

Click on the pivot table to activate it and add a calculated field from the menu shown below:

Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:

Set KPI filter as “Productivity” and make 2 copies of the pivot table (by copy and paste). Set KPI filter for those tables as “Efficiency” and “Scrap Rate”. Here is how it is supposed to look:

Insert a donut chart for each of these pivot tables. Cut them and paste them to another sheet. Format your charts to look like this:

Now make another copy of the first chart and arrange fields as shown below:

When finished, make 2 copies of this pivot chart too:

Now merge 3 cells and type “=B5” (without bracelets). B5 is the cell that contains result value of first pivot table (you can see from previous picture). Make sure to disable gridlines here. Then select your merged cell and copy it. Then paste it, as linked picture, to the sheet that has your donut charts. And drag it over related chart.

We are finished our donut charts at this point. It is time for our column charts. We need a 3 column data table for each of our column charts. Here is how I do it:

Place a 3 column grid just under the second set of pivot tables. First row will contain column titles. First column will contain month names. Remaining 2 columns will be filled by formulas. Here are the formulas:

Result Column Formula:

=SUMIFS(Sheet1!$E:$E;Sheet1!$A:$A;Sheet2!$A$14;Sheet1!$B:$B;Sheet2!$B$11;Sheet1!$D:$D;Sheet2!$A20)

This formula fetches result values based on department and KPI values selected on the pivot table above and month name in first column. You can see how to use this function from here SUMIFS FUNCTION.

Selection Column Formula:

=IF(A20=$A$15;B20;NA())

This formula checks selected month on the pivot table above and shows result value from 2nd column if month matches. If it doesn’t match, it shows #N/A.

Here are the tables I prepared:

Now select first table and insert a column chart. For formatting:

• Double-click on the orange column and set is as secondary axis.
• Set Series Overlap and Gap Width to 100%
• Delete both axes, chart title, legend and grid lines.
• Change column colors and set Shape Outline for the chart as “No Fill”

Here is how your chart should look like after all these make-up:

Do the same for other tables (other KPI’s), you can also copy chart formatting to do this faster. Then place these chart next to your donut charts:

Now comes the interactivity part. Click on your first donut chart and insert a slicer from INSERT ribbon. Select Month Name as field. Make sure settings are made as shown below:

Now customize you slicers as a 3×4 button grid with button height and width as 1,2cmx1,2cm. Change colors to match with your charts. You can see how to do this from Customize Slicers. Here is how it should look like:

Add a final slicer for departments. You can do this by clicking on any of the charts and inserting a slicer. This slicer must be connected to all pivot tables.

Place it on top of your charts, add KPI texts over slicers and our dashboard is done.

You can download this Interactive Excel KPI Dashboard from the link below:

Download Interactive Excel KPI Dashboard

Видео (кликните для воспроизведения).

Татьяна Туманова

Приветствую вас! на нашем ресурсе. Я Татьяна Туманова. Я уже более 7 лет занимаюсь юрисконсультом. Я считаю, что являюсь специалистом в этом направлении, хочу научить всех посетителей сайта решать сложные и не очень задачи.
Все материалы для сайта собраны и тщательно переработаны с целью донести в доступном виде всю необходимую информацию. Перед применением описанного на сайте всегда необходима ОБЯЗАТЕЛЬНАЯ консультация с профессионалами.