1.2 Lab – More Charts in Excel Submission: Completed Excel workbook via Canvas. Part 1 – Multiple Variable Charts Overview • This problem furthers the ideas from the notes ‘1.2 – Basic Charts in Excel

1.2 Lab – More Charts in ExcelSubmission: Completed Excel workbook via Canvas.Part 1 – Multiple Variable ChartsOverview• This problem furthers the ideas from the notes ‘1.2 – Basic Charts in Excel’, where we learnedhow to create and format many simple charts in Excel.• The first tab of the accompanying workbook contains a dataset of physical measurements of asample several different fish species caught from the same lake; a subset is shown below:Assignment• The goal is to create several plots that visualize more than one variable at the same time.• Here are some questions to investigate about the fish species and the plots we will use to help.After each plot, add a text box with a quick write-up answering the directed questions.a) Do different species have different lengths? Comparative Boxplot of length by specieso This type of plot visualizes boxplots for each species.o To create this: Highlight the Species column and Length column at the same time ->Insert Box and Whisker Plot.o In the write-up: Compare the distributions of length between the different species.b) How do the distributions of weights compare for species of different lengths? ComparativeHistogramso There is no good way to plot two histograms in the same panel; so, the best way to dothis is to create two separate histograms and organize next to each other, specificallyone for shorter length species and one for longer length.o To create this:1) Based on the plot from (a), identify the 3 shortest species.2) To make two histograms, you need two separate tables. Copy the entire dataset andpaste off to the side; this will be used to help make the two needed tables.3) Filter the copied data to the 3 shortest species; then copy only those rows and paste intheir own table. Repeat for the remaining species. Now the full copied data can bedeleted.4) Insert histograms for each new table. Be sure color each histogram different and givethem informative titles based on which subset of data it is based on (shorter fish orlonger fish).o In the write up: Compare the distributions of weight for the two groups of fish.c) Is weight a good predictor of length? Scatterploto Scatterplots visualize two numeric variables by plotting each pair of numbers.o To create this: Highlight the Weight and Length columns at the same time -> InsertScatterplot.o Be sure to give the chart a title and axis labels.o In the write-up: Does there appear to be a relationship between weight and length offish? If so, describe the trend.What will be looked at for grading• Correct plots and sufficient write-ups.Part 2 – Waterfall ChartOverview• This problem extends Part 1 of this Lab, where we continued introducing more types of Chartsthat can be made in Excel.• The second tab of the accompanying workbook contains a dataset for interest rates of a 30-yearmortgage; a subset of which is shown below:Assignment• The goal is to create a Waterfall Chart, including performing the necessary data transformationsneeded for the plot.• A Waterfall Chart shows a running total as values are added or subtracted. It’s useful forunderstanding how an initial value is affected by a series of positive and negative values. Anexample is shown below:o It is commonly used in a business context to show the incoming and outgoing money(revenue, expenses, net income, etc.). It can also be used to show values across time asin the example.• Here’s how to create it:a) Create a new column named ‘Change in interest rate (%)’ with the following specifications:o The first value (corresponding to the earliest year) should be equal to the interest ratethat year. This represents the starting ‘base’ total.o After the base, a Waterfall Chart plots the change from the previous item (i.e. year toyear in this case). Write a formula to calculate the change in rate from the previous yearand fill it down. For example, if 2010 rate was 5.5% and 2011 rate was 5%, the value inthe row for 2011 is -0.5%.o After the final year, add an extra row that is the cumulative value of the interest rateafter all of the changes. It should equal the rate in the last year. This row (without a yearlabel) represents the ending ‘base’ total.b) With the data setup, now you can highlight the change column -> Insert Waterfall Chart.o To correctly format the starting and ending ‘base’, double click the bars and click ‘Set asTotal’.o Set the horizontal axis labels to the Year.o Choose appropriate colors for the Increase, Decrease, and Totals. Be sure to give thechart an informative title as well.What will be looked at for grading• Correct data setup, well-formatted Waterfall Chart.