Create a results dashboard - Microsoft Excel Tutorial

- [Instructor] After you write your simulation output to an Excel table, you can summarize those results using a dashboard. A dashboard worksheet provides a high level overview for your data, giving you a summary that you can use to guide operations and further analysis. I'll demonstrate how to do that. And I'll work with the 02_04 dashboard workbook. You can find that in the exercise files collection. So here I have a fairly straightforward dashboard. And over on the left, you see that I have demand for each of my three cities and I'm calculating the average, the maximum and the minimum. If I go back to the demand worksheet, you can see that I have demand for Portland, demand for Oregon City and demand for Beaverton. Also, I have the total, whether demand was greater than capacity and also the number of units that went unfulfilled. So I will use each of these measurements in my dashboard. I'll switch back to the dashboard worksheet and in cell B5, which I already have selected, I will calculate the average of demand for Portland. So in B5 I'll have equal, average and then the name of the table is demand. And then I have demand for Portland. Close it out with the right square bracket, right parenthesis and enter. And there we go. And I'll do the same thing for Oregon City and Beaverton, but I'll move quickly and without commentary because I will be following exactly the same steps, just changing the column name. So demand Oregon City. There we go. And finally, we'll do the same thing for Beaverton. Right square bracket, right parentheses and there we go. And you can see that I already have the maximum and minimum calculated as well. Now I can look at the volume and that's the number of units that were shipped from Milwaukee and Tigard. So I currently have values for the average and also minimum for each of these two cities. Rather than type in all the references that you see above, I will copy the formula. So I've clicked in cell G5. I'll go up to the formula bar, select the text of the formula, press control C, press escape. Make sure that I'm not editing anything I don't want to edit. I have now clicked cell H5. Press control V. I've got the same formula that I did before, but I actually want the maximum. So I will add as a formula. So instead of average, we're using the max function and we've got the max at 330. Now I can do the same thing for Tigard. So I'll click cell G6, copy the text of the formula, press control C, escape, then press control V. And again, on the formula bar, change average to max and enter. And there we go. Over on the right side, you'll see that I have fulfillment. So I want to find the number of days where I did not meet all demand and then the percentage that represents of all of my trials and then also the percentage of demand that went unfulfilled. In other words, the number of units that were unfulfilled divided by the total of number of units that were demanded. So for the days unfulfilled, I can just use the demand greater than capacity column, where I have a one if demand wasn't met and a zero if it was. So I'll go back to the dashboard and that is equal sum of the demand a table, and specifically, its demand greater than capacity column. So I will highlight that, press tab to accept it. Then right square bracket, right parenthesis, hit enter and there's the days unfulfilled. Now I want to know the percentage of days where I had unfulfilled demand. To get that number, I will divide the number of days unfulfilled by the total number of trials. And I am assuming that each trial represents one day. So L6 I'll type equal and then L5 divided by the maximum value from the demand tables trial column. So in other words, I'm going to find the largest value from there. I assume that's the number of trials because of the way that we set it up to count. And that'll give me the percent of days on fulfilled. So that will be max. And then the demand table, specifically it's trial column and enter and I see 3.2%. Now I want to know the percentage of demand that went unfulfilled. So for that, I need to divide the number of units that were above capacity and then divide that by total demand. So I'll type an equal sign and I'll take the sum of the demand tables unfulfilled demand column, and press tab and I will divide that by the sum of all demand. So sum, then the demand table, the demand of total column, right square bracket, right parenthesis, hit enter. And I see that it was only 0.1%. That's pretty good. We can also perform calculations to find the average solution. And you can see here in cell C11, I have calculated the average of volume from Milwaukee to Portland. I've also done the same thing for Tigard to Portland, Milwaukee to Oregon City, Milwaukee, or excuse me, Tigard to Oregon City and you can see it's zero. So it's never happened. Same thing for Milwaukee to Beaverton. And now we can create a similar calculation for Tigard to Beaverton. So in cell D13 I'll type equal and we're looking for the average solution. And that will be volume, specifically the Tigard to Beaverton column. So I have Tigard to Beaverton there, press tab, right square bracket, right parenthesis, and there we go. Finally, I can calculate the minimum number of item miles. So in other words, the day on, or at least the trial that had the fewest miles traveled. So for that I'll type an equal sign, we'll use the min function. We'll look at the volume table. And then specifically, the item miles column. So click there, tab to accept, right square bracket, right parentheses and enter. And there we go. So you can see that we had an average number of item miles traveled of about 4,400. The maximum was 5562. And the minimum was just below 3000. So you can see based on the average, maximum, and minimum, what a reasonable estimation would be for the amount of wear and tear on your vehicles.