Salaries and wages total expenses by month expenses for the drivers and buddies the total distance for the trips total of the round trip and the one-way trip the total monthly trips shipments by cargo types analysis for each trip's classified driver and buddy income per trip classify total trips in the hired transportation the second dashboard showing the trip schedule in details and the general notes.
Both dashboards values and charts can change depend on your selection from drivers names and month you can get our templates by visiting our online store other dash levels dot com these are the color codes used in the design and the font type as a body all our dashboards template features are working in all versions of excel mac and windows operating systems and for training and practicing you can now download the data set for this dashboard for free from our website.
Let's start with changing the background color insert round same side corners rectangle shape to create the second background rotate the shape to 90 degrees left change shape color go to the shape format tab to remove the shape outline and resize it.
Increase the rounded part by dragging down yellow button copy and paste the shape to create the third background we need it in white color select both shapes and align them to right and middle group both shapes now create two rectangle shapes we will use one as a header for this dashboard.
And the other will complete the second background with it duplicate the shape and resize the first rectangle drag the shape besides the second background ungroup the shapes to easy control zoom in for better view remove the outline and change the rectangle color to be same second background color.
Adjust the gradient's color and position copy and paste the rectangle shape now create the background for the middle of the dashboard resize it and format shape with gradient color click on the top rectangle shape press control plus shift then drag it to the bottom and align it with the corners.
Rounded rectangle shape to bottom send the rectangle shape to the back align both shapes to bottom press control plus shift then drag to the right side rotate and flip them horizontally adjust the rounded part to be the same as the one on the left side.
Finally group all shapes together now the dashboard background is ready let's start insert your company logo duplicate it to create the schedule dashboard button remove outline and fill color for both.
Now we will insert rectangle rounded corner shape to be as a highlighter bring the dashboard text box to front then drag the rounded rectangle behind it remove the outline and change the shape color insert top corners rounded rectangle shape and rotate the shape by 90 degrees right.
It will be the indicator increase rounded corners to the maximum align them to the middle next the dashboard main title fleet management transportation and logistics dashboard select 2022 and reduce the font size and.
Increase the font size to the rest duplicate the text box and add the subtitle for this dashboard
Search and insert the required map picture from online pictures but in png or svg format add circular shape to show location of the city.Now add the city and country name its remains to add the country flag the header now is completed and next is to add the final background for each analysis insert rounded rectangle decrease rounded corners a little fill it with gradient color.
And change outline color copy and paste the shape two times and align it to distribute horizontally crop the map till the end of header right edge below insert four rounded rectangles.
remove the outline and change color to white i think we have to decrease the font size of the dashboard and schedule a little rename this sheet to dashboard i also think that i need to change transparency here the background now is totally ready so we will start analyzing the data.
Insert new sheet for the database and one for the pivot tables the database has been added format the data table and add the table name first we need to find the number of the total trips insert pivot table from database drag the numbering column to values right-click and change summarize by to count now this number 24 as the total.
Trips adjust the pivot table design if you want that we don't need the grid lines and headings and show the formula bar type a title for each pivot table for any future adjustments duplicate this pivot table instead of re-inserted again and now we will find total hired transportation yes means this is in hired transportation.
And beside we'll type yes and i will use the vlookup function to find the total add if error function to avoid showing the error results let's move back to the dashboard sheet insert rounded corners rectangle then resize and format shape.
Add text box and rename to total trips copy and paste text box and give reference to the count value resize the font size now add the hired transportation value .
now find a svg truck picture add shadow to the truck image using oval shape set the distance blurriness and shadow resize.
Bring the truck image forward then group them
next part is to add slicers one for the drivers and the other one for months select any pivot table and insert two slicers one for the drivers and one for the months .Let's move them to the dashboard click on driver slicer and go to slicer settings select the hide items with no data and uncheck the display header we will follow the same process for the month slicer select the disable resizing and moving then deselect lock option.
Finally select don't move or size with cells option to hide the cell selection border that appear on cell a1 insert rectangle shape and fill it with the same background color then send it to back to create creative slicer we will insert pictures of the drivers.
Go to picture format to remove the image's background now change the image color to light gray follow the same process for second driver image we need to add background for both images using circle shape.
place the driver's image on the circle shape then crop the driver image using a circular shape we are done here next is analysis for trip classify let's insert the background for it.
Let's go back to the pivot table sheet clear all rows and values add trip classify in rows field and values field now we will insert 2d pie chart copy and paste the row labels use the vlookup and if error functions as shown before.
copy and paste the text box two times and align all to distribute vertically copy the grouped text box and give reference of count from the data table.
change the font color and format next is to find the driver and buddy income for trip classify remove trip classify from values and add driver wage by trip and buddy wage by trip i will use a 2d column chart.
we don't want the y-axis legends select horizontal axis and remove the outline also remove the chart outline now show the chart data labels outside the end select one of the columns and click control plus one then change the gap width and series.
Overlap adjust font color and size finally add the chart title reduce the font size of per trip classify insert a thick line to be the legend do.
Next analysis total trips by type of shipment add goods to both rows and values i prefer to use a new chart type for this analysis 2d bar chart will be great i don't want to show the title and legend .
Remove outline and color now change the bar color and then change the gap width let's move to the main kpi's values in this dashboard total expenses in its breakdowns which are salaries and wages again repeat the process of copying the pivot table.
Column rename the heading to total expenses to find the total expenses we need to use the calculated field add name for the equation then the formula will be the sum for driver wage buddy wage driver salary and buddy salary.
Click okay we will use the calculated field again to get the total salaries add name for the equation then the formula will be the driver salary plus buddy salary.
And here we will add the total wages use calculated field to calculate total wages in table by adding driver wage and body wage use format painter to copy the format to the other text box we need to show a donut chart with the.
Percentage for both type salaries then wages and get the total count for each directly from the pivot table value but here type wages first then salaries and get the count values from the table .
Select the data table as shown and insert donut chart repeat the previous steps for total wages chart now remove the outline chart title legend and remove the chart background color change the plot area color as shown.
Format the plot area and increase the donut hole size resize the chart to fit in box as shown and align it to middle copy and paste the text box two times move to pivot tables and calculate the percentage of salaries and wages to total expenses .
Align text to middle change font format color and size and move the text box inside donut chart so far we did better good next video we will complete the rest and it will be ready by this week i hope you learned something useful today thank you and see you in the next video.