Welcome to Other Level's today we present this "Powerful Financial Statistics System" with multiple dashboards using Microsoft Excel which contains many of innovative graphs and data visualization without using VBA codes the first dashboard contains a dynamic web chart analyzes in detail all sources of income and the percentage of each source and highlights the source with the highest value in addition a creative Doughnut Chart showing the achieved percentage from the target dashboard title and description total income amount and the target a line chart income by months total counts and percentage of income sources items average of monthly income a bar chart showing the monthly operating profits and the total amount analyzing for two types of marketing strategies all these values are controlled and changed based on the year selection of the slicer second dashboard analyzes the total the slicer creative design for a dynamic map chart analyze the profit value from each country for the most profitable country details of various types of taxes and here is the percentage of profits achieved using a donut chart with circular edges this dashboard fully controlled by yearly slicer and third dashboard is for sale process status here is a diagram that explains the stages that all sales processes go through so you can see how many customers go through each stage and highlights the most frequently used stages and also a doughnut chart showing total sales the percentage of sales achieved from the target and sales details by points of sale in addition total refunded orders percentage bar chart for orders by points of sales finally in the fourth dashboard we add a project's workflow report with the several main kpis in a beautiful design you can get this template by visiting our online store www.other-levels.com it is also available with a light theme these are the color codes used in the first dashboard and the font type is "Avenir" let's start by inserting the first sheet and name it "income source" for the first dashboard we don't need the Gridlines and Headings Set the background in "Black" insert a rectangle to place the title and the buttons that will move us between the dashboards easily keep your eyes always on the shapes dimensions and color codes your company logo is preferable to add as usual all texts and numbers will be added to the dashboard through text boxes not inside the cells this is the "Browser" button will link it to access your official website no need the second circle in the browse icon don't forget to add the name of the destination name in the "Screen tip" box because without it, a long and not good name will appear when the cursor is on the icon to indicate the chosen dashboard we'll mark it with this blue tick now add new sheet taps to the other three dashboards in order to match the header to them all in quickly and easily way now copy the entire group and add it to the other sheets as you can see we can now be moving between the dashboards without any differences in dimensions let's begin it's good to contain a title and a summary explanation of its contents this first dashboard will analyze sources of income so the main important value is the "total of the achieved amounts" always we prefer to create all formulas and pivot tabs in a separate sheet and name it pivot tables to insert a pivot table select one of the table cells then go to insert menu choose a pivot table locate its place we want sources of income and income amounts for each source add it once more again to get the percentage for each source let's first create the web chart graph we will create it using a multiple shapes and charts for purchasers of this dashboard if you change the source's names you have to re-copy the updated names from the pivot table and paste the data table here mainly i will use the scatter chart the idea is simple each circle in the scatter chart is determined by "X" and "Y" measures this database that we use contains 6 income sources we already set up 6 circles location which is apparent in this table follow the next simple steps i will explain it to you without complications or information that does not interest you to know first select an empty cell then insert scatter chart from the chart design menu choose "select data" add new data series it is recommended to add a name for the data source then choose data range for "X" values then data range for "Y" values and click "Ok" as you can see all circles are placed based on the X and Y values for more clarification: check X and Y values for "usage fees" you will find X = 1 and Y = 3 and you will find the circle "located" based on these values now we will change the chart design to "scatter bubbles" which has given us Bigger circles Next, use vlookup formula to get the amounts from select data menu re-insert column X data then add the amounts in "Sizes" field next step is to fix the highest and lowest "bounds" for both X and Y axis to not let them increase and decrease when change data values make the highest bound "10" and the lowest bound "0" and also set the "scale bubble size" to 70 we don't want any of the other chart elements next we will add a highlight to the top income source amount to be in a different color and big circle size so add another column and use MAX formula to find the top income amount only the formula explanation is: if the value in this cell is the highest value For this range ? if YES added here, but if NOT keep the cell empty equal = IF , select the cell type MAX formula then select the range IF True = add the value here, IF False = "" marks, and "two double quotation" marks means keep cell Empty we will add it to the chart, go to the chart design menu choose "Select data" click on + "Add new data source" and set a name for it then add X and Y values now form the "Size" field select the max range then "Ok" now insert a "Year Slicer" to test the formula and scatter chart as you can see the formula and chart work correctly insert the "Data label" to the top value no need to show the Y value or the legend line also want to add the other amounts we will use the same formula used before but switch between IF True and IF False Excellent, choose one of the circles and add the data label we finish the chart data table modify font size and color higher value for sure will be in a larger font Beautiful colors, now let's add a shadow the first part of this web chart is ready let's move it to the dashboard we modified this slicer before and to know how to modify the slicers please watch the video above we don't need the slicer header check on Hide items with no data Beautiful start as i said this first dashboard will analyze the income sources so the main value is a total of the achieved amounts let's create a data table that we'll use in many times always try not to add too many pivot tables to keep the dashboard speed and size and use formulas instead the percentage equation is = Achieved amount / Target amount find the remaining percentage to reach 100% Simple, 100% minus this percentage Great! now we will add the Total amount of the Actual amount, its percentage, and the amount of the Target as for the percentage table, we will use it later Next, total income per month we will add a line chart that analyzes the total income according to the months of the year that will be chosen from the slicer to create it in unique design, we'll need Two charts but first, add the incomes once more the first chart as a "line chart" then choose one of the lines then change the design to "Area chart" this will give us a distinctive line chart instead of the default one we will add the quantity of items and the percentage for each use the first pivot table to extract the quantities one to extract the quantities and the second to extract the percentage use the vlookup formula that we have explained at the beginning now it's ready to be added on the dashboard need to add bullets, but not using circle shapes better to use circular symbols go to insert menu choose symbols let's move to the right side on the dashboard it is important to know the average monthly income in the following analysis we will design a distinctive background using a rounded rectangular colored slightly lighter than the main background to get the monthly average we will use an average Formula type "Average" then choose the search range let's include the Average symbol from Excel symbols list Next, the Operating Expenses we add the total expenses amounts and also a bar chart showing operating expenses by months insert the months in "Row" field then the amount in "Value" field select the pivot table then insert a bar chart it will be very simple you can find the total by summing the amounts with (SUM) equation or you can type = and select the "Grand total" cell next analysis the percentage of profits for "Marketing strategies" and here we have two types of strategies the B2B and B2C now it's time to add a donghut chart but with a distinctive design that is Gorgeous and easy to implement let's start together… these are the percentages that were extracted previously first select one of empty cells then insert a donghut chart then go to chart design menu and "Select data" in the Y values field add number (1) and comma (,) sign up to "58 Times" Press "Ok" remove all chart elements we will color it in a gradient of Pink and Violet the border is important and it will take the same dashboard black background color and 11.5" widths we finished the 1st part of the graph in the next part we start by inserting percentages to the chart and let's name it "Percentages" then in Y value add the percentages from the table then change the chart type to "Combo Chart" and then change the "Secondary Axis" the blue part is the "Actual income" percentage so let's make the transparency to 100% and the orange part is for the remaining is for the remaining percentage to achieve the target so change the color to black but the transparency by 28% we're done here, Great design and idea isn't it ? from now on we will start completing the "Web chart" and adding all the breakdowns of the income sources in a beautiful view First, design the core center of the web chart it has three circles in the center in different colors to give a beautiful appear now choose the three circles and the percentage, then use the "Align" feature to set them exactly in the center Great work! now connect the core center to the main circles the following analysis shows the percentage of each income source then we add the analysis of breakdown for each income source let's finish the designs first before moving on to adding data one pivot table is enough for us to find all the required values insert income sources then income breakdown in below we will certainly need to show the amounts as well as the percentages for each income breakdown now copy all names and paste them on the side of the table use vlookup formula to find amounts and percentages for purchasers of this dashboard if you change the source's names you have to re-copy the updated names from the pivot table and paste the data table here it's time to add the source names group and then duplicate it for easy change you have to create the circles and lines in different lengths and sizes as to add beautiful appearance let's now distribute the sources locations and start with the advertisements which contain five sources just link the source name amount and percentage of each add the money icon in the center of the web chart we finish design the first dashboard which is creative and contains many financial analyses of income sources all now remaining is to protect the dashboard and the slicer select the slicer then choose "not to move or change the size" and unlock as for the rest of the elements make sure that they all "Lock" Okay, go to the review menu then choose sheet protection, the click validity will only be for items that have been "unlocked" we're done with today's video we will be completing the dashboard in two more videos and they will be ready by next week i hope i have show you something useful for you have a good day
