1 00:00:00,580 --> 00:00:01,910 This is the case today. 2 00:00:02,050 --> 00:00:11,200 We are going to solve for our dashboard exercise, so suppose this is the PNL statement for our company 3 00:00:11,320 --> 00:00:12,580 of 20-20. 4 00:00:14,750 --> 00:00:22,070 And we wanted to analyze how our company is performing and how we can improve the bottom line of our 5 00:00:22,070 --> 00:00:22,670 business. 6 00:00:25,420 --> 00:00:34,330 Now, to analyze this further, we have also listed down the net profit and net profit margin of our 7 00:00:34,330 --> 00:00:36,940 business for last six years. 8 00:00:38,880 --> 00:00:48,150 Now, when we analyze this data, we realize that our net profit is increasing year on year, but our 9 00:00:48,150 --> 00:00:55,200 net profit margin is first increasing and is decreasing in recent years. 10 00:00:57,500 --> 00:01:03,170 No profit and profit margin is dependent on two main factors. 11 00:01:03,800 --> 00:01:05,540 First is the revenue. 12 00:01:05,660 --> 00:01:07,850 And second is the expense. 13 00:01:08,690 --> 00:01:11,810 Profit is generally revenue minus expense. 14 00:01:12,560 --> 00:01:22,250 And the reason behind low profit or low profit margin can either be a decrease in revenue or an increase 15 00:01:22,250 --> 00:01:23,990 in the net expense. 16 00:01:25,160 --> 00:01:31,310 So that's why we first looked at the revenue figures for the last five years. 17 00:01:32,400 --> 00:01:37,890 And we also added the projections of revenue figures for the year 2020. 18 00:01:39,520 --> 00:01:43,870 You can use forecasting method to predict future revenues. 19 00:01:44,750 --> 00:01:50,600 And if you are interested in forecasting we have a separate course on forecasting, using Python and 20 00:01:50,600 --> 00:01:58,100 forecasting using Excel so you can check it out if you are interested in how to calculate revenue projections 21 00:01:58,100 --> 00:01:59,210 using forecasting. 22 00:02:01,070 --> 00:02:11,210 But here we analyze that our revenue is increasing year on year and there is no decrease in profit at 23 00:02:11,210 --> 00:02:11,660 such. 24 00:02:13,120 --> 00:02:18,520 Now, since revenue figures are fine, next, we move on to expense figures. 25 00:02:21,630 --> 00:02:30,780 Now, looking at this expense breakup, we realized that the advertising and payroll expenses are the 26 00:02:30,780 --> 00:02:35,190 major contributor and have increased in recent years. 27 00:02:36,900 --> 00:02:42,990 After looking at net profit revenue report cost and expense breakup. 28 00:02:44,420 --> 00:02:52,730 We made the conclusion that advertisement and payroll expenses are increasing and there is a need to 29 00:02:52,730 --> 00:02:54,380 control these expenses. 30 00:02:55,760 --> 00:03:03,110 So for the next year, we set up a limit on advertising and payroll expense. 31 00:03:03,590 --> 00:03:11,360 We set a limit of three hundred thousand on advertising and two hundred and seventy thousand on payroll 32 00:03:11,360 --> 00:03:11,950 expense. 33 00:03:12,380 --> 00:03:21,530 And we are constantly monitoring how much we are spending in these two categories by tracking year to 34 00:03:21,770 --> 00:03:24,320 date values of these two expense. 35 00:03:26,350 --> 00:03:29,590 Not till now, we only looked at the raw numbers. 36 00:03:30,900 --> 00:03:37,980 But to share our findings with our colleague, we have also created a dashboard conveying the same story. 37 00:03:40,580 --> 00:03:47,840 So you can see that here first, we have created a dashboard from this data where we have net profit 38 00:03:47,840 --> 00:03:49,260 and net profit margin. 39 00:03:50,150 --> 00:03:51,980 And here you can clearly see that. 40 00:03:52,860 --> 00:03:59,010 Our profit is somewhat increasing, but our net profit is decreasing over time. 41 00:03:59,370 --> 00:04:03,180 This is the same conclusion which we drew from this data. 42 00:04:06,320 --> 00:04:08,960 Now, next, we looked at the revenue figures. 43 00:04:11,410 --> 00:04:15,520 And from the raw numbers, we concluded that the revenue figures are fine. 44 00:04:16,490 --> 00:04:22,310 From this graph as well, we can see that the revenue is fine and revenue is increasing year on year. 45 00:04:24,650 --> 00:04:34,880 So next, we move on to analyze our expenses, so we draw a pie chart of the expenses of the year 2020 46 00:04:35,420 --> 00:04:42,070 and you can see that a majority of the portion is because of these two buys. 47 00:04:42,200 --> 00:04:51,440 First one is the advertisement by which accounts for 390000 of expense, and the second is the payroll 48 00:04:51,440 --> 00:04:56,630 PPI, which accounts for three hundred and twenty three thousand of expense. 49 00:04:58,480 --> 00:05:07,440 Now, next, we set a limit for our advertisement and payroll expense, and we have used column chart 50 00:05:07,480 --> 00:05:13,750 to convey the same information, these are our limits and the orange part is the amount that we have 51 00:05:13,750 --> 00:05:16,270 already spent and on the top. 52 00:05:16,300 --> 00:05:22,990 Also, you can see the number of how much of our target limit that we have achieved. 53 00:05:26,320 --> 00:05:27,400 So overall. 54 00:05:28,910 --> 00:05:36,980 We have converted this raw data into these kind of charts, and it will be easier for us to convey the 55 00:05:36,980 --> 00:05:43,970 message and convey our thinking of our analysis using the charts over this raw data. 56 00:05:46,500 --> 00:05:51,930 So in this tutorial, we will see how to create these types. 57 00:05:52,900 --> 00:05:58,090 And how to format these tapes to create a dashboard?