Then use vlookup to get the values foreign we will add an if error formula to avoid errors appearing on the dashboard so now the empty values or errors will replaced with the dash sign copy the same formula for the other reports adjust the format of the amounts numbers and percentages as desired.
Foreign foreign this data is for four years therefore they appear in large numbers so we will insert slicers so that we can analyze and create the charts correctly choose year and month foreign slicer settings hide items with no data and the header.
Let's create a new sheet tab for the dashboard and then move the slicers let's add the dashboard title and background select all cells then change the fill color to this color code then the light blue background using a rounded rectangular shape remove the outline color.
Adjust the shape size then send it to back next insert a proper image of your project let's first change the slicer design watch our videos to know how to modify the slicers we will adjust the position for height and width foreign.
I think to highlight the difference between them should put a line to separate them choose the second background color from format menu make sure to unlock slicer and check on do not move or size with cells then finally disable to resizing and moving we are done here let's back to the dashboard title.
First add the company's website we'll name it project management dashboard decrease the dashboard work font finally insert the logo let's move on and add the first report the financial performance copy the background to place all values on it.
Align both shapes to the top first analyze Capital expenditures foreign just type the equal sign and then choose the capital expenditures budget we will attempt to highlight the actual Capital expenditures using blue rectangle.
Foreign the actual amount foreign next the capex percentage but we will need an equation to find the percentage it is dividing the actual amount by the budget.
Make some improvements to the data of the four reports foreign ly in this part we will add a line chart for the actual and budget capex during the months copy the pivot table and remove the report's name then add the category to columns field filter to capex actual and the budget.
- Scale it with hosted RPA bots
- Learn How To Create Your Own Drag & Drop Kanban Board In Excel Masterclass + Free Download
- 1000+ Professional Business Card Design Templates In PSD Files Sheri Sk Business Card Templates
Then add years and months to row field - ID Card Make
We need one more table for the Opex line chart change the pivot table name next we need to disconnect the monthly slicer from these line charts and Link them only with the yearly slicer remove the grand total.Both pivot tables are ready so let's insert a 2d line chart change the budget line chart with area chart instead foreign move them to the dashboard to continue adjustments for the vertical axis we prefer to format numbers in million using the following format code.
Thank you remove the grid lines and chart background color foreign the actual line will be in dark orange and for the budget area chart make it in light orange foreign ly resize the fonts and the chart.
Place it within the capex part foreign group them all and duplicate for Opex part just relink the amounts and percentages we are done here there are five more criteria for financial performance which are the.
Percentage of Opex as planned maintenance maintenance costs as a percentage of Revenue total operating costs per ton outstanding warranty claims total Tire management spend foreign foreign put the amounts into a gray rectangle thank you.
Then for sure we need a title for this part we will use the same font format for all titles we want to add a gradient color to the right side Excel can't give us a sharp edge for each color so we'll do it another way foreign.
Ly we will add icons indicating the content of this part for example we can use a dollar sign and also search for the plus icon foreign foreign next analysis for the project safety performance the data has been ready.
From the beginning and it contains the following first aid treatments doctor treatments shortness of breath Burns interactions incidents Hazard observation and housekeeping related we will just create it in a simple design group them and then duplicate for more seven times we will use the selection pane to be able to select them all.
Select them all and distribute them vertically then group all text boxes link them one by one foreign to symbolize the topic of this part our last analysis in today's video is the control system and this is the fourth report it contains the following operator-induced failures new purchase orders purchase.
Orders raised for more than three months work orders raised work orders raised for more than
One month completed planned maintenance standard operating procedures stock outs we will just create it in a simple design foreign foreign.Foreign foreign foreign that's all for today's video hope you learned something useful for you on the other hand you have an easy way to directly purchase this template from our website other Dash levels.com.
