Boost Your Sales Funnel Analytics for Finance with airSlate SignNow

How to create outlook signature

hello and welcome to lean excel solutions! In this tutorial, we will learn to create,   this fully interactive sales dashboard  in Microsoft Excel from scratch.  Also, we will see, how to change  the complete theme of the dashboard   by just selecting the standard  theme color or the customized one. We are going to do it in the following steps- First, we will have an overview of this dashboard.  Second, an overview of the data sheets  including how to update the data.  Third, prepare the database. Fourth, data analysis and prepare the visuals.  Fifth, design the background of the dashboard. Sixth, prepare the dashboard using   background and visuals. And the last one, formatting it. So, let's start with the first step. Basically, it is designed for the B2C   type of business. Like Dmart, Walmart,  Amazon, Shops & supermarkets, etc.  These are the slices to drill down  the data. like yearly, monthly,   by sales type, and by mode of payment. Here is, the total sales, total profit,   and profit percentage. These all are combined into a monthly format here.  We can hide or unhide it to view  it as individually or comparative. This visual indicates product-wise  sales for the selected period.  Only 10 products are visualized at a  glance, and you can scroll up & down to   view other products in the list. It shows day-wise sales.  It shows sales percentage contribution based  on the type of selling and mode of payment.  This is for the top-selling  product and product category.  The final one is the  category-wise sales contribution. Now, let's have an overview of the data  sheets and where to update input data.  This is the master data sheet  or you can call it a catalog.  It is added in the table form. The first column is the product ID.   The list of items in this column must be unique. Then we have the product column.  Instead of these two columns, we  can manage with only one also.  But I kept it separate because  sometimes product names can be the same,   but some parameters will be  different, like price, supplier, etc.  If it is not applicable in your case, you just  need to maintain the same data in both columns. This is the category column, which is  the product category. like cosmetics,   foods, drinks, electronics, etc.  If you have predefined categories, you can create  a drop-down list using data validation, like this. The next column is the unit of measure (UOM). You can update it also,   based on the products you have.  And the last two columns are  buying price and selling price,   which means unit purchasing  price and unit selling price. Please note, after some time, if  there is any change in any product   like buying or selling price, don't change it  in the existing one. Otherwise, it will apply   the latest changes to previous data too. So,  create a new product ID for it, and add the   required details to the table, like this. The next sheet is the input sheet,   in which we have to add product  sales data on the regular basis.  In the input data table, these are the  columns we have to fill on product selling. The first column is the date of Selling. Second, product ID.  Third, quantity. Fourth, sales types,   like direct selling, are purchased  by a wholesaler or ordered online.  You can change or update this  list using data validation.  Next is a mode of payment,  which is online or in cash.  You can update these two as per requirements.  The last one is a discount percentage. if you  want to offer any discount, you can add it here. On this sheet, we are going  to design the dashboard.  These are the few icons, we are  going to use in the dashboard.  And are inserted from this inbuild library only. And the final sheet is Analysis, where  we will do all backend calculations.  So, let's start with the data preparations. First, we will extract some columns to the Input   Data table from the master data. Copy these  headings and paste them into the input data table. Let's use the vlookup function with  reference to the product ID column,   to extract these data from the master data table.  Add columns for total buying and  total selling value and calculate it. Add day, month, and year columns. Since we have formatted it as a table, we  just need to update these columns only,   the remaining will be updated  automatically, like this.  Alright, now let's go to the analysis sheet,   insert the pivot tables for the input  data table and rename them ingly. Let's remove the grant  total wherever not required.  Now the next step is to create visuals. First, let's add the slicers.   Click inside of any pivot table, go  to insert, and click on the slicer.  Select the fields which we want to  include as a slicer in the dashboard.  Since we inserted these slices  by clicking on this pivot table.  The slicers are linked with this pivot only.  To link these slices with the remaining pivot go  to the slicer tab, and select report connections.  It will open a new tab and select there with  which pivot table we want to link this slicer.  Let's do the same with other slices.  Let's add an area chart for the day-wise sales. Calculate the total sales, total  profit, and profit percentage. Next is monthly sales. Let's prepare one table using this pivot table.  Insert a column chart and format it. We will add a profit percentage as data labels.  Add three checkboxes, so that we can show or  hide the sales, profit or profit percentage.  Now, based on the selection here, the  cell values will change to TRUE or FALSE.  Let's link the TRUE or FALSE conditions  with the table values using the if function. We need to fix the O1 cell.  Let's add profit percentage as a data label. Click on Values From Cells and select the range. Alright, let's get rid of these NA's by  changing it as a blank in the formula.  This pivot table we will  use to create these visuals.  Let's start with this one first.  We want here the top product. We can get  it by using a RANK function, like this. But, this range is not dynamic, if the number  of line items changes after selecting the slicer   or adding a new product, either it will show an  error or there will be a blank at the bottoms.  So first, let's make this range  dynamic using the OFFSET function.  I have used the OFFSET function several times  with detailed explanations in earlier videos.   If you find it difficult to understand  here, go and watch those videos.   The link is in the description below.  Let's use the rank function now. Alright, it's working fine. Here we will use the vlookup function to  extract the first rank product details   from the below table, later we will link  these cells to the dashboard with text boxes.  To create this visual, let's copy the same  formula and change the height of the range to 10. Insert the bar chart and format it a bit. Insert the scroll bar and link it to the cell. now we will use the output of the scrolling  bar as a row in the OFFSET function. To get rid of the header in range, let's  change the Scroll Bar minimum value to 1.  Now, to remove these zeros from the range,  we will add one another conditional formula.  Now replace the earlier rows reference  with this cell in the OFFSET function. Alright, now it's restricted  up to this range. but still,   there are zeros at the bottom which we can avoid  by subtracting nine from the COUNT function here. This pivot table we will  use to create these visuals.  For this let's repeat the same process which  we did earlier for extracting the top product. Now, to create this visual, we can't insert  a treemap chart directly using pivot data.   We can use this table for it. But again, if the number of line items changes  in the range after selecting the slicer   or adding a new category, it  will not reflect in this chart.  So let's do it using name  manager and this OFFSET function.  Let's create these visuals  using these pivot tables. So all visuals are created.   Now we will design the dashboard  background, which we will do in PowerPoint.  There are several advantages of  doing it in PowerPoint, like-  We can get desired aspect  ratio easily in PowerPoint.  Like currently, we are using a 16:9 ratio. We can use rulers, guidelines, and guides   to match the shapes correctly. We can align the shapes easily.  And if required, we can create  the shapes using merged shapes. So,   let's copy this slide for another layout If you observe, all colors we have  selected from these theme colors only.  The reason is, that we can change complete  color formatting by changing the theme here. We can customize the desired color also.  I have already created these theme  colors and I used standard colors only.   Now select all, copy, and go back to  the dashboard sheet, and paste it. We can see, that the color change to the  default format after pasting it in Excel.  Let's group them first and send them back. In Excel, we can change the theme colors here.  Let's increase the size of the  background shapes for better clarity.  Change the color of the icons.  Remember, you must select the  colors from the theme colors only   if you want to change the complete  color combination after theme selection.  If you select any standard color or  customized color, even if the same   color combination is available in theme color,  it won't change with theme selection, like this. Let's insert the required number of  text boxes, rename it, and then format   it. Now, let's link the text boxes with the analysis  sheet, which will update with slicer selection. now, let's go to the analysis sheet, select all  visuals, cut it, and paste in the dashboard sheet.  Let's arrange them ingly. Let's check whether all visuals are linked or not. So this visual is not updated with the slicers.  Let's check the data linkage again and correct it. we cannot change the default slicer format.   So let's duplicate one in the existing,  one and format it as per our requirement.  Let's remove the borders and select  the colors, that match the background.  Same for the header.  Let's complete quickly the formatting  of borders, background, and text. Use gradient fill color here.  It's already been selected and this is  the composition I used for gradient fill.  Alright, the overall formatting is completed.  Here, by selecting theme colors, we can  completely change the look of the dashboard.  Let's use a plain background also,  which we created in PowerPoint.  Select all, copy and paste  into the dashboard sheet.  In the selection pane, these last  two groups are the background.  We can hide unhide the required one  and select the theme ingly.  In this way, our sales dashboard is  ready. I have designed this in office 365. If you like the video give it a  thumbs up and subscribe to the channel   if you have not yet subscribed! Thank you for watching...!