is there a relationship between monthly sales data our door temperature and
discounts that store offers during the year? In this tutorial we're going to
establish the dependencies between these parameters and also build combo graph in Microsoft Excel
Let's first take a look at the final version of the document
to see how it might be relevant
to what you're trying to accomplish Let's first look at the final version of
this tutorial for the source data we'll use a four column Microsoft Excel table
first column will show months in the second column will show sales then we'll
show outdoor temperature average outdoor temperature for the region and then
we'll show a discount policy that store has established this is the imaginary
store in Midwestern region of United States in our first tutorial we've
established the dependency between sales and outdoor temperature and we found out
that people shop less during the colder weather but this dependency did not
explain everything and now we're trying to establish and show on the graph
dependency between the sales temperature and also discount that store offered
during the year until compass this we're gonna build Microsoft Excel combo chart with secondary vertical axis
with multiple line graphs. In this graph sales will be represented by the
bar chart and temperature and discounts will be represented by the line graphs
combo graph with secondary vertical and horizontal Axis in Excel are very useful
when you have data that varies widely in the data ranges which is exactly the case for us
sales are in the thousands and temperature and discounts are in single
and double digits in this case we can represent sales as the bar graph in
Microsoft Excel and we can represent temperature as a separate separate line graph with secondary vertical axis
and connect it to secondary horizontal axis. And we can also connect discount to the
secondary vertical axis in Excel because temperature and discount are very
similar in their data ranges but at the same times vary widely from the sales
data we start with Microsoft Excel data then create chart with secondary vertical axis in
Excel then add 3d effects to the chart then in a second tutorial we create an
additional line graph on a secondary vertical axis chart and then in the last tutorial
we take created chart into PowerPoint and create animations for professional
presentation links to all the tutorials and downloads are available in the
description of the video make sure to check them out
and all the best on your project so let's go ahead and start with our
featured tutorial first let's take a look at the source data our source data
consists of four columns in Microsoft Excel spreadsheet we have all the data
that we need to start creating of the combo chart with secondary vertical axis in Microsoft Excel our combo
chart will consist of three charts bar chart which would represent monthly
sales a line chart which would display average outdoor temperature and an
another line chart with secondary horizontal axis which would represent discounts let's verify that we have
everything we need to generate the chart in the table we have monthly sales data
average outdoor temperature and average monthly discounts and they are
represented for the entire year and this is exactly what we need also both
average outdoor temperature and average monthly discounts are in the same data
range single and double digits which could be proportionally reflected in the
graph so now let's go ahead and create the graph to create comp agrave let's
click on the insert tab in the ribbon and then click insert combo chart and we
will pick clustered column chart with line and secondary access as you can see
the chart looks very plain so we're going to do some formatting change the
title and also add some 3d effects so it looks professional as a first step let's
change the title of the chart you can use the title of this tutorial for
example but stick was the function of title correlation between monthly sales
outdoor temperature and discounts as you can see our final chart also is not
accurately representing temperature and discounts this is because temperature
and sales are in the same bar chart and because they are in a such a
substantially different data ranges your data is not visible as a next step
let's move temperature into the second axis in Microsoft Excel and also from
the bar chart we'll make it a line chart to do that do a right mouse click on the
chart and select change chart type and here for the temperature select the line
chart and also connected to secondary access now you see that the chart looks
correct but we still need to do formatting and add some 3d effects to
make it look professional are you enjoying this episode make sure to
subscribe so you're not missing the next one and tell your friends about it so
they will learn more skills in the area now let's continue and have more fun
first let's add 3d effects to the bar chart which represents sales to do that
let's select the bar chart on the graph and change the values in the 3d format
section for tab bevel and also material as the next step let's change the
temperature graph we're going to change the thickness of the line chart add the
line marker
let's also make chart a little bit bigger in the screen so it's easier to
select and make markers 3d and now we are going to do the same for the
discounts graph we're going to select the line chart which represents
discounts we're going to change the line thickness we also go into add markers
and we're gonna make markers look 3d this is how final version of the chart
looks like we've accomplished exactly what we wanted let's recap first we've
looked at the source data in Microsoft Excel to see if we can create the chart
that we want as the next step we added the combi graph in Microsoft Excel then
we change the formatting for the graph and we added the title we also added
some 3d effects to the bar chart and we also added 3d effect to the line graphs
for line graphs will increase the thickness of the line and edit the
markers and as a last step we changed markers to look 3d now let's see if we
can answer the business question what does best explain variance in monthly
sales for hardware store in Midwestern region of the United States can both
changes in outdoor temperature and changes in promotional discounts offered
by the store explain variation sales volume variation can be explained by
multiple variants outdoor temperature and discounts offered by the store fewer
people shop during the winter month but changes in outdoor temperature don't
explain all sales volume variation throughout the year during warmer
weather people buy more than during the winter month but we also learn that
volume varies based on the discount stores offer during the warmer months
climates which makes sense because people buy more the more discounted
prices they see at the store that means that there is a correlation between the
percentage of the discounts and sales volume they hire the discounts they hire
the monthly sales numbers we can see this difference between June and July
and also August and September in June the sale volume is low because lower
discount are offered in the store and in July probably because of the force of
July holiday with higher discounts offered there is a higher sales volume
very similar pattern is for August versus
in August there's no discounts offered by the store but in September there is a
high discount probably because of the Labor Day holiday and that explains
higher volumes in July and September versus month of June and August this
presentation could later be shared with the business leaders so make sure to
look in the description of the video to find the remaining pieces of the puzzle
and look at the entire video series to accomplish your goals
have you enjoyed this episode make sure to subscribe so you will not miss the
next one and tell your friends about it thanks for watching hope you learned
something and enjoy this video if you can please share this information with
somebody who might be working in the presentation right now and will benefit
from this tutorial also please share your comments and thoughts in the
comment sections down below I really review all of them and respond if
opportunity permits and of course if I know the answer also if you like the
serial please click that like button right there this really helps me with my
rankings you can find links and downloads in the description section of
this video there are a lot of helpful links and a lot of other helpful
information you will benefit from make sure to check out my other relevant
videos and subscribe to my youtube channel we have a lot of great stuff
planned in the pipeline and I don't want you to miss any of it and if you'd like
to get notified about all the new stuff that are coming out make sure to
subscribe to my email list as well all links are here in the screen make sure
to click to stay in touch thanks again for watching