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

Полное раскрытие темы с комментариями профессионалов в статье: "Расчет KPI в excel скачать". Если в процессе прочтения возникнут вопросы, вы можете задать их дежурному юристу.

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

К списку услугПрограммно-методический комплекс 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 вашего предприятия.

СКАЧАТЬ ПРЕЗЕНТАЦИЮ
Расчет KPI в excel скачать 117

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

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.

Slicers is a very useful but underutilized tool in Excel. You can dynamically filter your pivot tables and pivot charts with animated buttons, aka slicers. For more information on slicers, you can check this short article.

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:Расчет KPI в excel скачать 68

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):Расчет KPI в excel скачать 119

Click on the pivot table to activate it and add a calculated field from the menu shown below:Расчет KPI в excel скачать 55

Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:Расчет KPI в excel скачать 200

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:Расчет KPI в excel скачать 110

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:Расчет KPI в excel скачать 2

To do this, right-click on any gray button on the chart and select “Hide All Field Buttons on the Chart”. Delete chart title and legend. Then click on the donut and set shape outline to “No Fill”. Set shape color separately by clicking on them and selecting color.

Now make another copy of the first chart and arrange fields as shown below:Расчет KPI в excel скачать 12

When finished, make 2 copies of this pivot chart too:Расчет KPI в excel скачать 125

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.Расчет KPI в excel скачать 180

Читайте так же:  Декретный отпуск выплаты

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:Расчет KPI в excel скачать 153

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:Расчет KPI в excel скачать 174

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:Расчет KPI в excel скачать 192

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:Расчет KPI в excel скачать 199

You should check whether your slicer works correctly, by clicking on its buttons. If it doesn’t change charts, it is most likely because of report connections. You can correct this issue from there. Insert slicer for other KPI’s (chart pairs) too.

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:Расчет KPI в excel скачать 116

Читайте так же:  Гибкий рабочий график
Видео удалено.
Видео (кликните для воспроизведения).

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.

Slicers is a very useful but underutilized tool in Excel. You can dynamically filter your pivot tables and pivot charts with animated buttons, aka slicers. For more information on slicers, you can check this short article.

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:Расчет KPI в excel скачать 68

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):Расчет KPI в excel скачать 119

Click on the pivot table to activate it and add a calculated field from the menu shown below:Расчет KPI в excel скачать 55

Set calculated field parameters as shown below and we will have an additional field in our pivot table to use for our charting needs:Расчет KPI в excel скачать 200

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:Расчет KPI в excel скачать 110

Читайте так же:  Как узнать номер своего СНИЛС через интернет?

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:Расчет KPI в excel скачать 2

To do this, right-click on any gray button on the chart and select “Hide All Field Buttons on the Chart”. Delete chart title and legend. Then click on the donut and set shape outline to “No Fill”. Set shape color separately by clicking on them and selecting color.

Now make another copy of the first chart and arrange fields as shown below:Расчет KPI в excel скачать 12

When finished, make 2 copies of this pivot chart too:Расчет KPI в excel скачать 125

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.Расчет KPI в excel скачать 180

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:Расчет KPI в excel скачать 153

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:Расчет KPI в excel скачать 174

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:Расчет KPI в excel скачать 192

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:Расчет KPI в excel скачать 199

You should check whether your slicer works correctly, by clicking on its buttons. If it doesn’t change charts, it is most likely because of report connections. You can correct this issue from there. Insert slicer for other KPI’s (chart pairs) too.

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:Расчет KPI в excel скачать 116

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

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

Расчет KPI в excel скачать
Оценка 5 проголосовавших: 1

ОСТАВЬТЕ ОТВЕТ

Please enter your comment!
Please enter your name here