4.8 Highlight results and data insights - Video Tutorials & Practice Problems

<v Narrator>Let's recap our user requirements</v> for visuals and data insights. First we're required to highlight the variance between budget and forecast. We've already implemented this during the previous sub-lesson using conditional formatting. Now, we will create the other required visuals to provide a categorical breakdown for the current year actuals for revenues and expenses. A comparison between budget and forecast for the current year by category. A similar comparison for the actuals from last year and the forecast for the current year. And finally a graphic comparing budget for the previous, current and next year. So with that, let's begin. As we've already discussed the layout for report page earlier in this lesson, let's proceed with creating the required visuals. We'll start with the two-line charts at the top where we will summarize the budget for the previous, current and next year. So let's go to insert, look for the line chart group, click on the line chart with markers, select the chart, go into chart design, and then select data. Here, we'll remove the series that Excel has automatically added to the chart. We'll then start adding the data that we need. (mouse clicking) For the series name, we'll click on the year value for the previous year and then the corresponding values that we wanna show in this series. Click Okay. Add another series for the 2020 data. Then we go to series values and then select the corresponding values. Click Okay again, and then add one last time. Go back on our prep sheet, select a year for 2021 and the values for 2021. Then we click Okay again. Finally, we'll edit the horizontal axis labels. Go back to our prep sheet and select our revenue categories (mouse clicking) And click Okay. Now when we have our basic chart done, let's start formatting some of the options. We'll click on the plus sign, select a chart title. We've already got a legend. Let's click on a data table. Now we've got the legend as part of the data table. So we'll uncheck the legend and maybe turn off the grid lines. (mouse clicking) Next we'll edit our chart title and then maybe change the font and size for it. (mouse clicking) So we'll say size 10. And then we want it in bold. For the last piece of formatting, we'll right click on our vertical axis, go to format axis and then change the display unit to millions. Okay, so I think our chart is now presentable, so we'll click on the chart, press the Alt key and then drag it to its location. Again, pressing the Alt key, snaps the chart to the cell edges. We'll again press the Alt key and drag the corner for the chart and size it to the area we'd allocated. Now, as you can see, there's not much space left for our plot area. So maybe let's increase the sizes for the space that we've allocated for these charts. To do that, we'll select the rows that we've got the charts in (mouse clicking) and then right click, go row height and then provide a higher value. Let's say 22 in this case. So as expected, our chart has resized as we changed the row height. This is made possible by one of the properties for the chart. To review this property, we can click on the chart, then go to the options area, click on the size and properties tab, expand the property section and review or change this property as required. The couple of other options here, allow us to enable or disable printing for this chart and also to lock this chart as part of sheet protection. We'll cover this topic during a later lesson in this course. Next we'll try to re-use this visual to create the second line chart for our operating expenses. So we'll select the chart, copy and paste it to another cell. We'll go into chart design, click on select data and then select the first series. Click on Edit. And then Tab to select the series values and now select the operating expenses. (mouse clicking) Select the next series, click edit, go into the value selection and then select the relevant operating expenses. Finally, we'll go to the 2021 section, select the operating expenses again and then change the axis labels to the operating expense categories, (mouse clicking) and then click OK to accept the changes. (mouse clicking) Now we've got the operating expense data on this chart but I think we can do a little bit better by formatting it. So let's click on the plus sign, turn off the data table, cause that's taking up too much space and we probably need to add a legend now. So we'll select that and then click on the plus sign again to hide this menu. As the last step, we'll right click on the vertical axis, select format axis and then change the major and minor units. There. I think that's a little bit more legible now. Now we'll select the chart and drag it to its allocated space. We'll press the Alt key again and snap it to this cell edges. Now before moving onto the next visual, let's update our chart title. (mouse clicking) Next, we'll look at a funnel chart for the current year actuals. We can again, start with the previous budget chart, copy and paste into the new location. And then go under chart design, change chart type and then select the funnel chart option. We also need to change the data for this chart. So we'll go to chart design, select data, and here we can remove a couple of the series (mouse clicking) since we only need one here for the actuals for the current year. We'll edit the last remaining series, select the current year value from under the actual section and then select the values for this series. We'll then click Okay to accept the changes. Next we'll hold down the Alt key to resize this chart and fit it into the allocated space. And we'll also change the title for this new chart. (mouse clicking) And this time, I'll probably highlight that the funnel chart is a new option within Excel and the formatting options are a bit limited. At the time of this recording, we cannot start the series values in this chart and Excel expect that these values are sorted in the source data. Additionally, the data labels can only be turned on or off without much option to position them around the bars on this chart. So although it is a good option to compare the different values, the usability for this chart is limited by the constraints we just discussed. Now we'll replicate this chart for our operating expense categories. After creating a copy of this chart, we go to chart design, select data and edit our series. We'll leave the series name as is and then change the series values to our operating expense values We'll then update the horizontal axis labels to the operating expense categories. (mouse clicking) Now, as you've probably noticed, having a well-designed layout on the prep sheet is helping to expedite the creation and enhance the re-usability of our visuals. With these examples, we're again trying to highlight the importance of a well-thought-out design for our models. Now let's move on to the next combo chart for the current year budget and forecast. Once again, we can select the original budget line chart and replicate it for the new visual. We'll then hold down the Alt key and resize this chart to the allotted space. Next we'll change the chart type to a combo chart and then update a couple of options for this visual. If you remember on our previous funnel chart, the series for actuals had the orange color. For consistency purposes, we should probably keep the same color for the actual series on this chart. And since we'll be using a line chart for this series, we'll change the chart type for our 2020 series to a 2D line. We will change the 2021 series to a clustered column chart so that we can use it for forecast and then compare to the budget, which will be the 2019 series. At this time, we should probably note down these options for the select data dialog box. (mouse clicking) Now let's go and update the data for this chart. So here we'll repurpose this series as per notes from the previous dialog box. So we'll rename the 2019 series to budget and then select the values for the current year. (mouse clicking) 2020 will become our actuals and then 2021 will be our forecast. We can now click Okay to accept the changes. So learning from our previous visuals, we can go in to the chart elements menu, disable the data table, and then enable the legend. We probably wanna place the legend at the top of the chart. Click plus sign again, to hide the menu. We'll then update the chart title (mouse clicking) and then maybe go back into the chart elements menu and add some data labels. Now, similar to the other visuals we created, we can easily replicate this chart for the operating expense categories. However, for the purposes of this example, we will skip this step and move on to our final chart, which is the bar chart for our last year actuals and current year forecast. For this visual, we'll create our chart from scratch. So we'll go to Insert, and then select the bar chart option. Go to chart design, select data, remove all our series and then add a new series. for last year actuals. (mouse clicking) Go to the prep sheet and select the values, (mouse clicking) press Okay. And then add another series for the current year forecast. Select the values from our prep sheet and then click Okay. We'll also update the horizontal axis labels, then the revenue categories, (mouse clicking) and then click Okay on this select data source dialog to accept the changes. We'll now right click on the horizontal axis button, select format axis and then change the display unit to millions. We'll then also add a chart title and change it to an appropriate text. (mouse clicking) Now, one thing of note here is that our series colors are not consistent with the other charts that we'd done. So we'll select the forecast series, go to the options area, select fill in line, and then under fill, we'll select the red color that we'd previously selected for forecast. Next, we'll select the actual series and then select the orange color for it. Finally, we'll hide the grid lines to be consistent with the other charts and then move and resize our chart. (mouse clicking) You should now be able to easily replicate this chart for the operating expense categories. So once again, we will skip that step. Okay, with that, we have followed a step process to design and implement an efficient, reliable and sustainable solution for Emily and her team. Our final product is relevant to the user requirements given to us and consists of re-usable layout, formulas, and visuals. The topics covered in this lesson should help you consistently deliver a similar solutions for other clients. Next, in this course, we will cover interactive reporting within Excel before learning to set up our Excel-based solutions for collaboration and distribution.