Financial Dashboard in Excel

hello everyone welcome to learningx today we are going to make a financial dashboard using microsoft excel this dashboard will be based on the income statement of an organization it's a must check dashboard for finance and accounting background professionals no vva and macro has been used very simple method used so that beginners can do this we have given the free file in the description box check that for practice so let's enjoy the video first of all look at the data here we can see it's an income statement of an organization the income statement showing data of 12 months respectively will make a whole dashboard using or analyzing this incoming statement we'll find out roi and pmroa quick ratio income versus expense income with target expense with budget and many other analysis for respective months like for january or may and it will continue like this so let's start first of all go to analysis and make a copy of this sheet then rename it with detailed report then create another sheet and rename that with dashboard now select the full sheet and change the color go to insert illustration and take this shape drag it then make it in correct position and with correct size decrease the cropness of the shape and fill it with white color and the shadow effect for outline give a light color now make a copy of this and resize this and place it here decrease the companies make another copy again resize it and place it in the right corner then go to format shape effect shadow and give a shadow now fill the shape with golden color do the same thing for this shape go to insert go to illustration shape and insert this shape draw out the happiness go to format shape effect give a shadow effect go to format shape gradient fill make it with the background color and give this pure white color the middle one now it will give a glossy effect for shape outline give a light outline go to illustration and bring this shape make it golden color no outline and for effect give a shadow effect now make it group make full copy of this as we are going to make four donut charts now make one more copy of this and resize it then place it in the right downside our dashboard designing part is done now we'll go to our analysis part for that we'll go to analyze and we'll drag it down i'll remove this and i'll fill it with all borders i'll give a color for better understanding i'll go to insert from controls i'll select this one and i'll place it here now i want to show the months in this drop-down list for that i'll type january and i'll drag it till december basically i'm trying to find out this now go to format control i'll go to input range and i'll select this for cell link i'll select this cell i'll give it 3d shading and ok and it's done now drop down menu i've got my months if i select march you can see it's in three that means it's in the third row august it's in the eighth position now i want to input a formula where it will show the income of two that means the income of february the cost of goods sold of february and everything or february when i will select march it will show all the information of that respective months so type in index formula select this area as array then lock the cell by pressing f4 or by normally input dollar sign then for row number type match formula select this as lookup below then select this area for lookup array and lock it then give exact match or type 0 for column number select this cell that has been connected with these buttons and lock it then press enter now if i click on app till you can see it's showing the income of april now i'll drag it down and it's done now i'll just change the percentage from it for few cells now i'll type another formula which is max formula i'll select this row and i'll multiply it 125 percent or 1.25 then i'll drag the formula till the last row now i'll change the format of the sales which is in percentage form and it's done now if i change the month the whole data will be changed accordingly now select roi go to insert donut chart the chart will be created remove all the administrators select the series copy it and paste it instantly now select the first trees for twice give it a golden color fill do this for second series give a black color fill and again first twist from the right side give no fill second swiss from the left side give no fill now select the chart give no feel and no outline select the format data series give the donut hole size to 55 percent and make a copy of it paste it in the dashboard fit it in the shape i should make it black color fill with black and this one with golden color feel now it's looking gorgeous i'll go to insert text box and in text box link with the sale which is the title roi return on investment now make a copy of it and connect it with diesel it's showing the percentage of the roi now select both of them change the font with arial black align it give no outline as well as give no fill to the text box increase the size of the percentage and our first chart is done now i'll go to analyze this time i want to create a chart for net profit margin for this we don't have to create a new chart we'll just change the link we'll select the series and we'll change the linking and that's it now we'll select the second series we'll make it with black color fill and then the first phrase with golden color fill and rest of the two i'll give no fill i'll copy it and i'll paste it i'll position it i'll copy this to text box and i'll paste it here now i'll link it with the percentage of npm i'll type and pm or i'll link it with net profit margin i'll format paint our second chart is done now we'll create a third chart we just change the linking from net profit margin to our roa return asset that's it we don't have to create a new chart i'll do the same thing as i did in the previous i'll make a copy and i'll paste it in my dashboard i'll also copy the text box i'll paste it here i'll link it with the percentage of roa and i'll also link the title name from the analysis sheet i'll format paint it with the previous format and we'll make our last donut chart which is quick ratio we'll do the same as like the previous we'll give a black color fill for the first trees and for the second series we'll give a golden color fill and rest of the two will be no fill i'll make a copy of it and i'll paste it in my dashboard i'll fit it i'll copy the text box i'll paste it here i'll link it with quick ratio then i'll link the title name i'll format it our donut chart is done now now i want to find out income versus expense for that i'll link income and i'll type income and i'll link total expense i'll insert and i'll select this one i'll remove all the unnecessary parts from the chart i'll go to form data level i'll give values for separator i'll give a new line i'll give golden color for this one i'll give black color font material black i'll fill it with black color and i'll fill this one expense with golden color again i'll go to data series in number i want to show it in million so that i'll type 0.00 double comma and in quotation i'll type million or click it and it will show in million now i'll copy this and i'll paste it in my dashboard i'll resize it to fit in the position now go to analyze sheet link income cost of goods sold total expense tax and net profit as we want to make a comparison between this now select all of this and drag it in the right make a total of this it's done now select income to total go to insert and select this waterfall chart remove all the unnecessary parts from the chart i'm going to form a data level i want to show it in million for the tile type 0.00 double comma 4 million and in quotation i'll type million or only m i'll give add and it's done now i'll remove this i'll link the chart title gridlines and i'll select status total i'll change the name of the total operating expense to total oe and cost of goods sold to cogs as it is taking too much space in chart i'll copy this and i'll paste it in my dashboard i'll fill it with black color and rest of the waterfall chart with golden color i'll select the chart again i'll bold it now select format for chart give no outline that's done our full chart is done now select income to net profit and select rada with fill remove all the unnecessary parts from the chart make a copy of it give no feel and give a bold and black color font fill it with black color fill and also give a shadow effect copy this and paste it to dashboard for outline select black color and forward increase the weight for effect give a shadow effect now resize it and fit it in the chat to make it look better go to insert text box place it here make a copy of it now go to format and say fill no fill now type income achieved versus target here type expense reached versus budget do the same thing bold it make it black color font and align it in center or directly format paint it for outline give no outline do the same thing for this text box too now go to insert illustration and give this arrow a straight make it black color and give a shadow effect now select this true text box copy and paste it now link this income achieved versus target with this one and expense list versus budget with this one give it a real black font and increase the size of the font format painting and that's done now i'll copy this i'll paste it here we have to link it once more as it's blank go to format control i'll go to input range and i'll see and in cell link we'll link this one so repeat those tasks that we've done earlier and it's done now our dashboard is dynamic now i'll go to insert i'll go to icons i'll select this one this one and this one and click on insert place all the clippers in the bar menu now insert a text box as we want to give title for this clipboard this is the cover piece i'll format it i'll copy it and i'll rename it to details report and again copy this one and paste it here change the name dashboard now select the clipboard and the text box and make it group do the same thing for rest of the clip pattern text now go to detailed report and color these selected cells with golden color and for income go to conditional format in data bar select this one yeah you can give anything as you like here i'm giving what i like you can give as lucky as you wish and you can also give your own rules in conditional formatting to make it better to make it more beautiful i'm giving here some icon sets in the conditional formatting i'm just trying to represent the data in attractive way insert a shape and remove the grid lines resize the shape with the same width of the table format it with the theme color and for outline give no outline for effect give shadow effect now create another paste name cover paste now go to dashboard link this first phrase with this cover pace detail report link it with detailed report and for dashboard link it with dashboard now copy this tree and paste it in detailed report place it place it in this menu resize it to fit in the menu now go to cover base in cover pace i'll insert this picture which already i have created in powerpoint you can create as like as you wish and i've also given the powerpoint file in the download link you can check it from here now in view in the formula bar and headings i'll fit it with my screen and i'll insert the shape in format i'll give this golden color feel and no outline in shape effect give shadow effect now copy these three and paste it here and resize to make it fit now it's fully dynamic if you click here if you click those clipart it will direct you on that respective page and all the charts are dynamic or that's good is done if you have enjoyed the video then don't forget to subscribe the channel and check our dashboard playlist for more attractive dashboard videos

Channel: LearningX

Views: 7,321

Rating: 4.9310346 out of 5

Keywords: how to create a financial dashboard in excel, how to make a financial dashboard in excel, financial dashboard in excel, financial dashboard, how to create financial dashboard in excel, how to make financial dashboard in excel, professional finance dashboard, finance dashboard in excel, finance dashboard, how to create finance dashboard in excel, simple financial dashboard, simple financial dashboard in excel, finance dashboards, financial dashboard excel, financial dashboards excel

Id: Dpto7i_3He0

Length: 26min 39sec (1599 seconds)

Published: Tue Feb 02 2021