Shipments Cargo Analytics Dashboard using Microsoft Excel
Shipments Cargo Analytics Dashboard using Microsoft Excel
Download our Templates / Get Free Datasets
Visit our online store https://www.other-levels.com
Contact us for business:
Comment down below
Customer services https://www.other-levels.com
Have a question about this dashboard?
Comment down below after joining our channel Membership,
/ @otherlevels
All our videos / otherlevel’s
0:00, Dashboard Overview \u0026 Features
0:58, Color codes \u0026 Fonts
1:13, Web store www.other-levels.com
1:21, Database contents
1:59, Background Design
8:05, Title \u0026 Logo
9:00, Total Tonnage per month
13:49, Total shipments per month
15:37, Analysis of cargo materials
22:54, Total cargos per year
24:50, Top five charterers
29:44, Top five importing countries
39:30, Status of the navigational voyages
47:25, Total vessels \u0026 average of waiting hours
50:45, Tons of all materials
54:40, Insert and Modify the Slicers
56:18, Protect Excel Workbook
Subtitles in this video are available in 14 Languages #Spanish #Hindi #Arabic #Japanese #Filipino #Chinese #English #Indonesia #French #Korian #Russian #Urdo #Bengali #Portuguese
Playlists
Multiple Dynamic Excel Dashboards • How to build Dynamic \u0026 Interactive Da…
Single Dynamic Excel Dashboard • Excel Real Estate and Property Manage…
Modern Excel Reports • Modern Excel Reports
PowerPoint Slides Playlist • How to Design a Modern Flow Process C…
Copyright © 2023 Other Level’s. All rights reserved.
“Any illegal reproduction of this content in any form will result in immediate action against the person concerned.”
#Cargo #Shipments #Dashboard
Content
4.48 -> Welcome to Other Level's
6.318 -> Today we will create together this cargo analytics dashboard with a beautiful blue and white theme
12.799 -> this dashboard contains many analyses of two types of cargo Oils and "Grains"
19.039 -> Total tonnage and analysis for each month
22.08 -> total shipments and the monthly shipments Analysis of cargo materials and percentage of each
28.72 -> total cargo per year the total number of Charterers and the top five customers plus the percentage of each
36.16 -> Top five importing countries for each type of material the status of the navigational voyages of three ships
43.371 -> but it's not connected to the "slicers"
46.239 -> the total number of vessel and its Average number of hours waiting at the ports
51.36 -> Finally, an analysis of the total tons of all materials whether for Oils or Grains
57.92 -> These are the "color codes" used in the design and the font type is "Arial"
72.72 -> You can get our templates and make full modifications to them by visiting our online store www.other-levels.com
80.64 -> let's start by explaining the contents of the Database the years and months
85.759 -> port's name & charterer's name shipment destinations arrival and sailing dates
92.159 -> the number of waiting days in the ports names of vessels and type of cargo
97.759 -> 10 types of Grains & 8 types of Oils Finally, the sum of oils and grains tonnages
103.28 -> using SUM formula a table containing the data
108.159 -> For the status of the navigational voyages for three ships
111.774 -> but they are not linked to the slicers as i said and they will be fixed, and update manually
117.734 -> we'll add a sheet tab to create the dashboard
123.92 -> remove the Gridlines and Headings the design has Two backgrounds
129.52 -> we'll start with the first background we will design it with gradient colors
134.347 -> so we cannot only change the colors of the cells and we will insert a "Rectangle" instead,
138.879 -> for ease of creation
190.289 -> make sure that the "Rectangle" is the exact size of the background
199.44 -> now we insert a "Round same side corner rectangle" shape to design the second background
256.083 -> The colors and the general design should match the topic of the dashboard
260.32 -> which is "marine trade" so we will add a design of sea waves
263.521 -> in the lower left of the background using a "Double wave" shape
327.36 -> adding Transparency to the colors will give a beautiful mixing of colors
372.88 -> we will hide the bottom part with a "White Rectangle"
418.4 -> the design of the waves will be outstanding when adding a dark blue color in the slicers part
424.56 -> it will be appearing the "waves" and the "first background" as a one part
429.039 -> and the second background will put it in the front to separate from them
433.115 -> finally giving us a great design
445.12 -> duplicate the "wave" to the right side as if the wave is in the entire background
486 -> with this we have finished designing the background and we will start adding the main title to the dashboard
536.399 -> we will insert a white "rounded rectangle" as the background for the first Line Chart
553.12 -> in this part we want to show the Total Tonnages Shipped
561.36 -> we will insert a pivot table
572.399 -> as you can see in the database table that the total tons of Oils
576 -> are separate from the total tons of Grains to calculate their sum in the pivot table
579.275 -> we'll use the "calculated field"
587.68 -> we have to add a title to the "calculated field"
596.32 -> then choose total oils and click on "insert field" add the ( + ) sign
601.279 -> then choose total grains, then "Ok" thus, you get a pivot table field
607.12 -> that contains the required equation
611.6 -> add some adjustments to the pivot table
625.6 -> we will make another pivot table For total tons during months of the year
636.079 -> we don't need the grand total and now we're going to insert a "line chart"
644.959 -> Cut the chart, and Paste it into the dashboard
648 -> delete the Title, the Legend, and the Vertical Aux
656.48 -> watch carefully to the line chart formats
694.079 -> the numbers are big, so the best position for the "data label"
697.389 -> at the top of the line
726.48 -> Copy the previous "text box" instead of inserting another one again and re-formatting it
753.12 -> to reflect the total tonnages from the pivot table to this text box
758.324 -> write ( = ) sign in the formula bar, then go to the pivot table sheet
762.375 -> and select the value from the pivot table as you can see we cannot connect it directly from the pivot table
770.72 -> so, we will write in the formula bar the "cell number" manually
774.72 -> the fastest way is choosing the next cell down below the targeted value
779.6 -> then change the cell number from the formula bar
824.32 -> Great! now we will move to the next part which is the analytics for total shipments
830.079 -> it's similar to the previous one
842.8 -> to arrange the pivot tables we will create them next to each other
847.12 -> and we'll separate them by add a "vertical line" in between
868.32 -> we will use the same formats used in the previous chart
872.079 -> so, instead of wasting time repeating the same steps! we will save this format as a "Template"
881.519 -> let's Name it "Line"
890.16 -> then go to "Design" menu and change the chart type
893.585 -> Go to the "templates" list choose the template you saved
934.56 -> Next, the analysis of cargo materials and percentage of each
947.68 -> let's change the sheet tabs color
971.199 -> the first pivot table will show the total shipments of oil and grains
981.759 -> regarding the percentage if we add it as part of the pivot table
986.085 -> the "Doughnut chart" will be inconsistent so, i will extract the percentages
994.881 -> using a formula outside the pivot table the formula is to divide the "total of each type" by the "total"
1008.079 -> Every part should be different and special we will use the "Doughnut chart"
1011.031 -> and remove the "title" and the "legend" the chart is on the left side
1015.169 -> and the total will be right
1070.32 -> and the percentage in down below the total in smaller font
1108.72 -> insert "Circle" to use it as a legend
1150.88 -> copy everything pull them to the top Now we want total tons for both oils and grains
1156.559 -> similar as what was done before
1239.84 -> save the chart as a "Template" to save your time
1345.039 -> adjust and move all shapes and make sure that the overall appearance of the dashboard
1348.88 -> is in its Best form
1366.72 -> the last part at the top right of the dashboard will be an analysis of the "annual cargo tonnage"
1419.52 -> the pivot chart contains the "Years" and the "Total tons"
1427.2 -> i will use a "Radar chart" as the place is small and the radar is enough
1431.266 -> to show the most years of export of materials
1485.679 -> Next, top 5 charterers
1534.64 -> the database contains a long list of charterers therefore, we will arrange the list
1539.6 -> from largest to smallest then we keep the first five charterers
1544.559 -> next to the pivot table by typing ( = ) then selecting the "Cell" then "Enter"
1550.553 -> thus, the list will maintain the order from largest to smallest even with the slicer changes
1557.919 -> percentage of the charterers getting the materials by dividing the "total tonnage of the charterers" by the "total of all tonnages"
1576.32 -> choose the charterer "cells" and the "value cells" then insert a "bar chart"
1583.52 -> delete what you don't want
1626.4 -> the chart sorting appears from smallest to largest but it is better to appear reverse
1632.48 -> so, go to the "Format Aux" menu then choose "categorize in reverse order"
1653.2 -> we will put the percentages above each charterer name
1695.679 -> use the "Distribute vertically" option to maintain the correct distances between the "Text Boxes"
1729.2 -> So Great! now we have to show the number of "total charterers"
1746.32 -> to find the total number of charterers use the "COUNTA" formula and then select the whole list Except for the "Final total"
1780.399 -> now we reach to the analysis that shows the top five countries importing oils
1785.039 -> and the top five countries importing grains through this shipping company
1819.76 -> the first pivot table will contain the names of the destinations with total tons of grains
1825.84 -> using the same steps as above to find the top five as we said by arranging the tons from largest to smallest
1834.159 -> then put it next to the pivot table
1842.96 -> and then find the percentage compared to the rest of the countries
1865.44 -> as for the second pivot tabl e it will contain the names of the destinations
1869.84 -> with a total of tons of oils while repeating the same previous steps
1884.64 -> i prefer to show it without a charts and i will show the Map only in this analysis
1998.64 -> Copy them all, they are the same as the Graint part
2050.56 -> Next, add the Map in this Map i want to show the total of the two materials together
2055.119 -> i will make the Highest countries importing grains
2058.159 -> below the highest countries importing oils in one table
2083.119 -> then insert the Map as you can see that the map is small
2087.919 -> and it will be useless unless it is enlarged so, we're going to do a "Trick" to show it perfectly
2092 -> as you can see that all shipments are destined for the continent of Africa
2096.24 -> Europe, Asia, and Russia so we will highlight this part of the World Map
2101.52 -> and hide the rest in the following way:
2104.4 -> insert a "Rounded rectangle" shape and i'll use it as a "Frame for the Map"
2120.079 -> don't fill the shape with colors make the line width to "30 pt"
2130 -> then choose the same "background color" for it
2133.28 -> it is preferable to set the colors of the map before hiding the useless parts
2138.4 -> i will make the background gradient in blue colors and countries in dark blue and cherry red
2198.64 -> now we can insert "Four rectangles" containing the same background color
2203.04 -> on all sides that need to be hidden
2234.64 -> Group them all, and send them back
2246.56 -> Excellent! now we will add to the map the total number of countries
2250.72 -> which shipments were sent to by the company
2288.56 -> we will need four Text boxes three of them will contain unchanging texts
2294.16 -> and the fourth text box will change dynamically with the slicers
2361.68 -> as you can see the map has become more clear and useful in analysis Maps always add beauty to the dashboards
2369.76 -> the next part is simple as it contains text boxes linked to the cells in this small table
2376.24 -> but with a distinctive and beautiful design for all status of the navigational voyages
2404.64 -> let's start designing the status " ON WAY" First, i will show the name of the "shipping vessel"
2415.68 -> then the "type of cargo" shipped
2430.8 -> Third, the position in navigation
2441.839 -> then add the shipping country and destination plus sailing and arrival Dates
2528.803 -> now we want to add an "Arrow"
2534.384 -> where Green is the appropriate color with ON WAY status
2653.04 -> Copy them all and paste twice it's the same "ON WAY" status
2847.76 -> Next, the total number of vessels
2915.2 -> list the vessel's names then use the "COUNTA" formula
2917.524 -> to find the total number
2939.68 -> the Average of ships waiting time in ports
2943.034 -> First, the total waiting days we find it by minus "arrival date" from "sailing date"
2949.073 -> now we'll insert all the waiting days and then use the AVERAGE formula
3042.8 -> Finally, an analysis of the total tons of all materials whether for oils or grains
3081.52 -> each type of grains and oils in the database is in a separate column not in rows
3087.599 -> thus, when inserting a column chart will be unordered
3093.52 -> therefore, we will "switch the rows and columns" from the "Design" menu
3150.8 -> but as you see the materials names contain a "sum of" sentence !
3155.68 -> you will need to delete this sentence and replace it with a "space"
3226 -> then add the total tons of oils and grains in top right
3279.44 -> Now insert list the slicers and to know how to modify the slicers
3284.48 -> please watch the video above
3335.52 -> from "slicer settings" choose "Hide items with no data" and hide the "Headers"
3355.119 -> add the header for each slicers
3375.44 -> the most important part is the protecting dashboard choose all shapes and elements
3380 -> using "CTRL + A" then "CTRL + 1" to open the "format menu"
3385.839 -> then select "Lock" then select the slicer,
3390.319 -> then go to the "format menu" and "uncheck" the Lock option now protect the sheet,
3395.599 -> and you will find that you can use only the slicer and all the data and shapes of the dashboard are protected
3412.213 -> I hope you learned something useful today Thank You and see you in the next video
Source: https://www.youtube.com/watch?v=ZJX3ZsGFO2M