Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
in this video, you will be able to see how to do the labs for Model seven
00:06
on DA again. As before, I strongly suggest that you tried to do this slabs by yourself without looking at this video or
00:16
instructions.
00:18
And two
00:21
look at this video are used. The instructions is help on Lee. If you can do it by yourself or you don't understand what the task is and you want to see what is actually required of you.
00:35
So let's go directly to the Excel Sheet, which represents them labs.
00:42
And here we have some,
00:44
um, revenue report from ah company for entire year. And we haven't divided in three Sorry, 4/4.
00:55
And so we have ends up, especially for all of the months. So we have revenue
01:00
from products. Revenue from service is revenue from financial service is we have total revenue
01:07
and we have gross margin, and we want to create some kind of charge that will we need to create. This is the idea of the slabs
01:18
is to create
01:19
five charts in the 1st 1 You will
01:23
Ah, 1st 1 has to show.
01:26
Ah, what is the distribution off
01:30
all these financial perimeters per month during entire year.
01:36
So in order to do that, we're going to select this without the quarters because that would be additional information for Excel to process, and
01:46
it will get confused.
01:47
So let's go to insert, let's go to recommend the charts.
01:53
And then let's take on all charts
01:56
and we're going to select this one
01:59
because this one will show separately
02:01
for every month. Revenue Products Revenue Service is there in the financial service is total revenue and gross margin in separate groups,
02:12
and this is what we're supposed to do. So we're going to just click here,
02:15
and then we're going to drag it here and make it little bit bigger. So it should be almost as wide as the table itself.
02:27
And then we're going to change colors because this is it. One. We wanted to be monochromatic. We don't need this monthly coloring
02:38
because we know that the 1st 1 is January, February, March. So it's it doesn't help
02:44
toe understand anything here
02:46
by using this. So we're going to click here, and this is what was expected of us. So to create monochromatic calm chart Ah,
02:54
with revenue products roaming service is rare in a financial service is total ruin you and gross margin grouped
03:01
together for entire year.
03:06
Next step would be
03:07
to create
03:08
this
03:10
for each quarter. So
03:14
and we want sticked combs
03:17
in which we're going to see just revenue,
03:21
all the revenues
03:23
and this The stack is going to represent the total revenue because to some off these three you see here on the screen.
03:31
So we're going to go again to insert
03:36
you're going to go to recommend the charts.
03:38
Let's go to old charts are here. We have it. So we have a stacked column
03:46
and this is exactly what we wanted.
03:47
So the colors
03:51
represent different
03:53
types of revenues, and then you look at the total height of this con.
03:58
It represents the
04:00
total revenue because it's it's some of the history.
04:03
So we have to create these for four
04:09
quarters,
04:11
and so in the next one,
04:15
we're just going to click on the edge of this chart. We're goingto press control and shift,
04:20
and this will do the copy off this one while we drag it
04:26
and keep it in the same horizontal level as the previous one.
04:31
So now it's also January February March, and we wanted to be for next quarter.
04:38
So what are we going to do?
04:41
Let me just make this a little bit smaller so we can see everything.
04:46
We're going to click again on the edge, right click
04:49
and goes to select data.
04:51
And now first we're going to edit day town horizontal exes
04:57
so it's no longer going. Toe becomes be true. The it's going to be e through g.
05:02
So here, instead of B, we're going to put E instead of D.
05:08
We're going to put G with. Okay, now, here, April, May June. But the
05:14
the values are going to stay the same unless we edit them so weakly connected series
05:19
and here.
05:21
Ah,
05:23
in Syria's values
05:25
again we go e
05:27
through G,
05:30
you see,
05:31
and we click. And now it's changed. So it's now Actually, the values for April and May in June
05:38
again was just like okay,
05:41
And then
05:43
again, we're going to click on the edge of this chart
05:46
movie 12 pressing control shift. So it's now going to be moved vertically.
05:51
Dragon drop. You released the mouse button
05:55
and we go through the same procedure. So we see like data
05:59
here we change. Now it's going to be a judge
06:02
through J
06:08
And here again,
06:11
age through J
06:15
the key
06:16
and the K. And now we have
06:20
July through September.
06:24
And then again,
06:25
control, shift and drag.
06:29
And we have here now
06:32
a copy of this one.
06:34
We
06:35
so like data. We go here.
06:40
So it's now going to be
06:42
t
06:43
through m
06:47
and here also
06:50
Okay.
06:53
Through M
06:55
everything okay?
06:57
And now we have completed
06:59
our dusk,
07:00
so let's zoom out a little bit more.
07:03
So you see, we have data here.
07:06
We have total charts for
07:10
business operations. So revenue products revenue Service's room in a financial and total revenue and gross margin for each of the month so we can see the trends off each changing in time during in a year.
07:26
And then here we have separately for each quarter, just the revenues. So we have
07:32
revenue products, revenue service's revenue, financial, and the total height of the bar represents
07:42
total revenue for each month within the quarter. What we can do here, for example, is go to this chart here
07:49
and just change.
07:51
So we take kyu won
07:55
and here we go go inside the text, select all the text and change it with cute, too.
08:01
Here again. So we click on this small box textbooks, which represents the title off the track.
08:07
We so like the text and press Q three
08:11
and again
08:13
here
08:16
Q. Four.
08:18
And that is the end of this labs. So now everything is clear. Whoever looks at it,
08:24
we understand what it is all about. Everything is nicely labelled
08:30
known numbers overlapping, no
08:33
hard to see details. Everything is quite clear. Everything is visible and nicely graphically represented.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor