Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.
You can watch an overview demonstration of this dashboard here.
You can watch a detailed demonstration of this dashboard here.
Go to this blog post to learn how to download all the materials for this demonstration.
Click this button to download the demonstration files.
Hello and welcome back to the next exciting, and final, installment of our Meta5 training videos.
I am very pleased, to see you come back, to watch the final portion, of our video training.
I want to say, thank you very much for evaluating Meta5.
I want to say, thank you very much for your time and attention.
Thank you.
Where you got up to in the last part was, getting all the data ready in an excel workbook, that is going to be the data workbook for the dashboard workbook.
Now, you are going to create the dashboard workbook.
This means that you will read the data from the data workbook, in to the dashboard workbook, and place it into power pivot.
Then, you will build the simple sample dashboard, on top of that power pivot model.
Of course, we are presuming that you already know Excel quite well.
There are thousands of good Excel demonstration videos, out on the web, that will show you how to do exactly what we will show you here.
There is no Meta5 work in the process of building the dashboard workbook.
You have all your data in the data workbook.
It was put there by Meta5.
The only portion of this capsule, that you can see in front of you, that is related to Meta5, is the portion that allows Meta5 to run the capsule for each region, and to produce three dashboards.
There will be one dashboard per region.
Each dashboard per region, will only contain the data, needed for that region.
That is the meaning of the 5 icons, in the bottom right hand corner, of the capsule.
The spreadsheet, E, X, Oh, one, sample report, oh one, in the bottom right of the capsule, is the dashboard spreadsheet.
So.
When you are ready?
Let us get on with the show.
We will not make a lot of comments on this excel work, because we are presuming you know how to do everything you are seeing us do here, with excel.
We will just make comments when we believe they are important.
Now.
Please follow along closely with the video, and create your dashboard workbook, to be the same as you see on the video.
Please note.
It has been a little while since I built this dashboard workbook, so I did make one or two small mistakes in the video recording.
So I apologize in advance for those mistakes, and I will note them as you see them.
This video training is four hours of video for our trainees, so you can understand why I don’t want to re-shoot large portions of it.
Laugh out loud.
Now.
The first thing to do is to introduce the data from the data workbook to the power pivot model.
So please repeat the steps you see on the video.
Notice you will click on, use first row as column headers.
This is where the heading spreadsheets come in to the picture.
Those names in the heading spreadsheets, will be used, as the column headings, in the power pivot models.
When you get to the table import wizard, you can see that there is an, underscore, s, for the sheets.
You want to get data from the excel regions, and not from the sheets.
Now.
You will import countries dim, as you see in the demo.
Now.
You will import the rest of the regions in to the power pivot model, as you see in the demo.
Now.
You will create the joins between the tables in the power pivot model.
Now.
You have built your self an excel mini data mart.
That is basically what this is.
Of course, there is a lot more you can do in power pivot.
Now.
You will build pivot tables by country and by category.
Now.
When I put years in my rows there is a row that is set to blank.
This was because I forgot to set the year constraint on the internet sales, and there were some internet sales for, 20, 11.
So please forgive the blank year.
And I hope you do not have it in your dashboard.
Please make sure you hit save regularly, in excel, to save your work.
Now.
You have completed the By Country Pivot Table, and the By Product Category Pivot Table.
Now you will create the simple dashboard on top of these pivot tables.
You can copy the dashboard we have provided to you, as you go along with the video.
Now.
You have finished creating the excel dashboards according to the demonstration.
Now, you need to make it possible to run the spreadsheet, many times, for each sales region.
Now.
You need a dummy spreadsheet, because you will not send data in to the excel integrator icon.
You will send the data into the workbook using the power pivot refresh.
Now.
Copy a meta5 spreadsheet from blank icons in to your capsule.
Next you need an excel integrator icon.
So get an excel integrator icon from new icons, integrator icons.
Copy it to your capsule.
The output is going to go in to a final excel spreadsheet.
So copy a new excel spreadsheet into your capsule.
You are going to send the finished spreadsheet to a directory on the hard drive.
So copy a PC Directory icon to your capsule.
Now.
Rename the spreadsheet, dummy, so you know it’s not doing anything.
Connect the dummy spreadsheet, to the data region, of the excel integrator icon.
Connect the template Excel workbook, to the region called, template.
The excel integrator icon sends the finished spreadsheet to the final spreadsheet.
Open the excel integrator.
In the parameters, go down to, advanced commands.
In the advanced commands parameter, put the values, minus, R, A, D. .
This will cause the excel integrator to perform a data refresh of the dashboard, as well as to update all charts in the dashboard, before it sends the finished workbook to the final spreadsheet.
Now.
Connect the final spreadsheet to the PC Directory icon.
Notice, you will include the region name, in the output spreadsheet, being sent to the PC Directory.
Notice, the region name data, needs to be Data, with a capital, D. .
Now you can run the last portion of the capsule again.
Now you can look in the PC directory, and you should find the spreadsheet, has been written to the PC directory.
I will show you the completed spreadsheet, in the PC Directory, in the demo.
I will open the spreadsheet, and you can see the message, external data connections have been disabled.
This is a standard error message for excel.
You have to allow external data connections, in the security options, if you want to remove that message.
Now.
I will click on, United Kingdom, and you will see the charts respond.
I will make a few selections on the slicers, and you will see the charts respond.
That is just one spreadsheet being produced.
What you need to test now, is whether we can run the capsule three times, once for each sales territory.
So I will run that now in the demo.
Now you can see three spreadsheets were created.
When I go into the North America spreadsheet, you can see the data is only for North America.
When I go in to the Pacific spreadsheet, you can see that it contains the data only for the pacific region.
And so there you have it.
Your first Excel Dashboard, being created, by sending data into it, using Meta5.
Everything that needs to be done, you have seen being done, in the demonstration.
On the employee training videos, I did a summary of what was done, at this point.
This was just under 4 hours of video demonstration for our trainees.
Because you have the full video available to you, I do not think you would wish a summary again by me.
Now.
If you followed along with the videos, and you were able to get your excel dashboard to work, just like the ones in the demo?
Fantastic!
Well done!
Of course, if you like what you see with Meta5 and excel?
You can get your Meta5 pro license directly from the Meta5 site.
There are lots more training videos on our web site you can learn from.
You can get a full set of the manuals from the Meta5 site.
Also, of course, we would love to help you create Excel dashboards using Meta5.
We think we have plenty of good advice, and guidance, to offer you, on what analysis you could do, to improve the long term sustainable profit growth, of your company.
Our staff are now experts in the use of Meta5.
If you have any questions about Meta 5, that you would like answered, before you buy your license, we would be very pleased to answer your questions.
You will find our contact information, right here, on this blog post.
As I said at the beginning, this has been a major publication effort for us.
We have taken the internal training we use for our own staff, and we have made a public version, to teach you almost exactly what we teach our own staff.
Given the amount of effort, we have put in, to present this video training, to you?
We really hope you liked it.
Laugh Out Loud!
Now, in finishing this video training.
I would like to say thank you.
Thank you very much for listening.
I really appreciate your time and your attention.
Thank you.
I wish you a good day.
