Interactive Balance Sheet Pivot Table

With a pivot table and slices we can create a balance sheet that is interactive. Now I've created one here and what I've got in there are four different pivot tables. I've got two graphs that are connected and also some metrics up here. And then with the slicer once I make the change the metrics change the graph changes and so do the pivot tables so I can see my different status as that every month so I can see you can do some pretty powerful reports. And it's not that hard. I will show you in the next couple of minutes how to do this. So the first thing you need to do is when you're creating a dashboard or a interactive slice out with chart you've got to make sure that you set out your canvas. And then separating different areas. So on the top here within a putting there the slices. And then second. I'm going to put the metrics at the bottom when I have the graphs and. And then here When have our pivot tables colors go onto our data and we have our data here which has the month and we have the Year for 2014 only and we'll have our balance sheet items into current assets current liabilities and non-current assets and non-current liabilities and the type here we have the different types of assets and liabilities as defined in a normal accounting business structure and press. Okay. And we have the actual amounts there. So from in here we can create a pivot table insert pivot table an existing worksheet and let's put it in here and press okay. So we're gonna drop in our balance sheet into the Row Labels and type into the right labels so you can see it's like this. Now we'll make some space here so we can fit it in now from in here. Make sure that and the options and options the order column is switched off and the design subtitles do not show subtitles. And then Phil hit it we can get rid of them and also the no buttons. Now one thing we're going to drop in the actuals into the values area now from in here. We can actually get rid of that and then just press a spacing so we recognize that as a character and it's a workaround to have a blank header in the grand total. We're going to change that to total current assets and press enter. And that's fine. Let's go back to our values and we can put in there the dollar signs into the number format. The steady currency and choose dollar signs with a negative read and zero decimal places and press. Okay. Now we can filter these just for the assets. So when we go into the balance sheet we can just select the current assets and press okay. So it just gives us the current assets and in the design we can choose this one in there. Now we can click and go to options and select entire pivot table and press control copy. And in their price control V We've pasted the similar format in here so I have to go and redo all the for many again. So anything now is instead of the balance type being assets we can just choose current liabilities and that changes they notably the same for the non-current assets. So again click there select and type pivot table control copy and then here because control V and we can do the same thing there. Okay. So this clip in the non-current assets list change that to select the on current assets. And in here with select to include the non-current liabilities now let's delete this space here and then in here we can just highlight it and put in I like right. And the total assets left to the sum which is current assets plus the total current assets. Now we get up and get pivot data so let's escape of that this click in a pivot table called Options and from the dropdown option let's get rid of get pivot because what I want. Once again it's clicking a and then we'll do the same thing for the liabilities so we have our pivot tables for the assets and the liabilities we can actually highlight all of this and choose a different font if you like. Okay. So the next up now he's going to pull the ratios there. So the current ratio is current assets divided by current liabilities. The quick ratio is the current assets minus the inventory divided by the current liabilities. We'll get that minus the inventory and then divide it by the current liabilities. Now the debt equity ratio equals the total liabilities divided by the owner's equity. So let's go to the total liabilities there and divide by the owner's equity and the owner's equity is simply total assets minus total liabilities. So total assets minus liabilities. So we have our numbers there and in here we can just adjust that if you like. Now one thing I noticed here that we didn't change the names for the grand totals here. So here it should be total on current assets here should be total current liabilities. And in here total non-current liabilities. The next thing is to put in here the chart that relate to the total liabilities. So let's highlight total liabilities and the amount that we've got to insert and bar chart and we can clear that in Max Ellis just stood in here for the moment. We can get rid of the titles at and then the great lines. Let's make this a little bit bigger. And then highlight that and get rid of it. So this clip is now Archer a control one and then from in here we can get a feel and head and feel and then choose this format. And then we can choose a red color. And then let's click outside of the border there. And then the border color have an eye line as well. Now from the x axis click on that press control one maximum you can live as automatic. But we can put it in to maximum of one million. And the major unit will be 200000 display units. When I put that in hundreds and then the minor tick mark will have the cross will show display units on label. That's fine. And press. Okay. Finally let's make this in great color. And this as well. So we've created the chart. We can just make it a little bit smaller or bigger just depending on the size there. So really the same thing for the other chart. So instead of going through the same process we're going to save this chart. So could a design save template as now when you do that. It goes to the Microsoft template and charts and when a color in the interactive balance sheet let's create the other chart. This click on the total assets and go to insert and bar and bar we have that there. So let's go to the change chart type from the template. Let's hover over here and go on to our interactive balance sheet and press okay. Now when it changes to a green color and also it's going to go from right to left as well this click in the chart press control 1 and then the field is going to be a green color let's click in here and the values are going to be in reverse order and press okay. Once you acknowledge that we have hundreds and let's click in here and press control 1 and we change it to thousands and the same thing for that just clicking the x axis and change that to thousands. Okay. So now we can put the charts in our dashboard and we can reduce it like this just to make it fit and we can change that later on. Okay. And the same thing for this just putting their okay. Now one thing is that background should be greater. Click there. And then putting the light gray background click on the graph press it for to repeat the same thing in their press effort to repeat. And we have our chatting man. The funny thing we need to do is putting that slice up so we can control the months so click. Anyway in the paper table options and instead of slicer and Cheese Month and press okay. And from in here we can put it into six columns. We can drag it across like that. Right click slide to settings get rid of the display here. Now this too is the custom slicer which I created earlier called Johns with a slicer. And we can reduce like this or we can just make the balance a little bit bigger keep feeding them this fine in name now. One thing we need to do is connect the slicer to the four different pivot tables so click on the slice of that right click and pivot table connections and just check all the boxes. So we're connecting all the pivot tables to the slicer and press okay. Snap press January the pivot tables change the totals update and so do our metrics. So we have our live an interactive dashboard. You can see it at any time. How your business is doing which is a pretty powerful tool for years but it is pretty easy to create this once you know had a year's pivot tables slices and a couple of charts. And by going through this course you're going to find out how to do all this stuff here. And it's not that hard. It looks pretty fascinating. Usually pivotal principles and some common sense. Then you can create a dashboard just like this.