4.7 Populate data within the model - Video Tutorials & Practice Problems

<v Instructor>With the design finalized</v> and the required data structures and key functions identified, we can now begin populating data within our model. Using the naming convention and structures from the previous sub lesson, I've already defined the names required for this exercise. This will allow us to focus on other topics we haven't covered thus far. All predefined names use the standard naming convention we developed earlier in this lesson. So the names for this spill ranges within the actuals and budget sections start with the sheet acronym, PB, followed by an underscore, then the section acronym, which is A or B, followed by another underscore, then our standard spill range prefix, SPL, followed by the corresponding level two column header. And finally the Rev or Opex suffix to identify the specific horizontal group for the range. Similarly, the names within our budget variation section will follow a consistent pattern by replacing the spill range prefix, SPL, with the RNG prefix for cell ranges that include manually enter data. This would apply for the following fields within this section, base metric, percentage change, and dollar change. And as we discussed earlier, the names for the totals rows do not include the section or a column or a group reference since they span across multiple sections. So now, let's author the required formulas that will use these names and other inputs to prepare our data for an analysis. We will start by first activating our formulas for the analysis date, current year, budget year, and the individual year references within our column or sections. To do so, we just need to remove the single code at the start of our formula expressions and press enter. Having done so for all such formulas, let's move on to this spill range for revenue categories. We wanna leverage our sales data for this dynamic array so we can capture all categories where we have sales. Having already reviewed our source data, I know that we need to use the product category field from our sales source data table. So we'll get all unique values from this column and then sort them in ascending order. As you can see, our formula resulted in a spill error. As we had discussed in the previous lessons, we can click on the formula cell to review the boundaries of our spill range and identify the cells that need to be cleared off all other content. So in this case, we'll add a couple of extra lines below our formula cell for the spill to work As our operating expense categories are not part of the same dataset, we will need a slightly different approach for this list. Here, we need to use the account hierarchy levels defined within our account source data table. While our categories are stored in the account hierarchy level four column, we will need to filter on level three values to only get the categories for operating expenses. So let's use the filter function as our first step. Here, we wanna get data from the account table, from our account hierarchy level for column, and we wanna filter from the same table the account hierarchy level three column where the value is equal to operating expenses. We'll put an absolute reference for the operating expenses text and then close our brackets and press enter. As you can see, we again, get a spill error. This is probably because there's a long list of values returned from the level four column. Let's use the unique function again to limit the returned array. Now that we have our valleys returned, there's a couple of things we wanna note. First, there's an empty value in this list, which we probably wanna ignore for such scenarios and also we need to sort this list as well. So let's start by removing the empty value first. For the include criteria on the filter function, we can use a multiplication operator to create AND logic. Here, we wanna check for the values in the hierarchy level four column to be not blank. With the filter updated, we can also add the sort function to this expression. We can press enter to review or desired results. Moving on to the actual section for our revenues horizontal group, we need to know author a formula that aggregates the line total field offer sales table, while filtering the results using the categories on the left and the year at the top. As discussed earlier, we'll be using the SUMIF function here. For sum range, you wanna reference the sales table and the line total field within it. We wanna filter by the product category field within the sales table. And the criteria for this field will be our spill range for revenue categories. If you remember for previous lessons, we can you use the spill reference notation, which is the pound symbol, to reference all values within this range. For the second criteria of the SUMIF function, we'll be using the order year field to filter values corresponding to the current column's year. Here, we'll use a mix of absolute and relative cell references by pressing the F4 key twice. This cell reference will now update as we copy the formula across into other columns to the right. However, with the absolute reference for the rule number, we will maintain our reference for row seven as we copy this formula down into the operating expenses section. We can now press enter to review our results. Now, here comes the interesting part. Since we've used an appropriate mix of absolute and relative cell references and define names, we can simply copy this formula into the cell to the right for it to automatically update and provide the correct results. Similarly, we can move to the next cell on the right and simply use the control R keyboard shortcut to copy the formula across. So with proper planning and adequate knowledge of cell reference types, it's that easy to author formulas at the end. Now, let's use a similar approach to author our formulas for the operating expense actuals. Here, we'll need to aggregate the amount column of our spend table and provide criteria where the level three field. is operating expenses. The level four field is part of the categories on the left, again, using the spill reference notation. And the expense here is equal to the year for this column. We can then press enter to confirm the results. Similar to the revenue section, we can copy this formula across for the other years. Now, moving on to the budget section, we need to aggregate the amount column of our budget table, where the scenario name is budget, which we've defined as an input variable on the input sheet and the account hierarchy level five is gross sales. Now, we could have defined this gross sales text on our input sheet, similar to the scenario name as well. But for this example, we'll just continue with the screen constant. Next, we wanna check for hierarchy level six to be one of the category levels on the left. Finally, we'll check the year column for our budget table to be equal to the year of the current column. We'll close the brackets and press enter. As you'll notice, even for this complex formula, using a consistent naming convention in a decent structure for formula code, allows us to easily review and understand our formula. We'll now move to the cell on the right and copy this formula across for the current year budget. Now, because we're using the same budget dataset for revenue and operating expenses, we can simply copy this formula into the operating expenses budget section. We then need to make a few minor adjustments to this formula for it to work correctly in this new context. We'll start by changing the hierarchy level column references to those that correspond to operating expenses, and then we'll change the gross sales text to the operating expenses reference. And finally, we'll change the spill range reference to the operating expense categories. We'll then press enter to review the results and copy this formula across into the current year column. Now, moving on to the forecast calculation, we'll start with the actuals for the current year, use the spill reference notation for the entire range, and then add a prorated value for the budget amounts for the current year. We'll calculate a ratio by dividing the number of days left in the year by 365. Similar to the budget section, we can copy this formula into the operating expenses section below. We can then make a couple of modifications to this spill range references and get our desired results. Next, we'll look at the base value calculation within the budget radiation section. For this formula, I've already created a template using the switch function within Excel. Here, we're gonna compare the base metric value on this row against some predefined options. These are the same options that are available in the drop-down for the base metric field. So now, we'll individually go through these options and assign a corresponding result. For the options related to the current year budget, forecast and previous year to actuals, we can simply reference the corresponding spilled ranges as results. And for the final option, we'll need to calculate the relevant average. Now, an important thing to note with this formula is that we've combined references for cell ranges and dynamic arrays. The only way this would be possible is if all ranges and dynamic rays have the same dimensions. In our case, all of these data structures have a single column and four rows. Now, let's enter this formula and copy it down into the operating expenses section. Again, we'll need to change some references for this formula to work correctly. With the formulas confirmed, lets enter some values to confirm our logic. We can also add some values for the operating expense section. Next, let's add some sample data for a percent change and dollar change columns. We will also remove some of these extra data entry cells before moving on to the calculated budget formula. For this final section, we can use the basic IF function to first check if the dollar change value is zero. In which case, we wanna take our base value and add the percent radiation to it. However, if the dollar change value is not zero, we wanna only add the specified dollar amount to our base value. We can again, copy this formula into our operating expenses section and update the suffix for our defined names. So with all our calculated values now complete, let's author the formulas for our totals rows. Now, before we start offering these formulas, it is important to realize that the totals row for both the revenue and operating expense sections is at the top for each data set. So a formula with the right mix of absolute and relative cell references can be easily copied across into other columns and down into the other section. So with that premise, let's look at the options for this formula. We will start in the actual section and look to sum the first spill range. As you can notice, Excel automatically populates the defined name for this starting cell. However, using defined names, we will not be able to update the formulas automatically when copied across or down into the different sections. We will just replace this defined name with the relative reference for the starting cell. We will then use the spill reference notation and calculate the sum of the entire range. Since we have relative references for both the rows and columns in this formula, we can now easily copy this across and down into the different sections. As you can see, with the formula copied across and then down, it still references the desired range. With the totals row for the revenue and operating expenses sections now completed, let's look at our options for the net profit calculation. Now, net profit by definition, is our revenues minus operating expenses. And since we've already defined names for the totals row within our revenue and operating expense sections, we could probably use those ranges to easily calculate the net profit for each column in our dataset. However, in doing so, we probably wanna skip columns where we have no data or where we have non-numeric data. So within our formula, we can check for the revenue total cell in this column to be blank. And if it is, we wanna return a blank value. However, if it is not blank, we wanna return the difference between our revenues and operating expenses. So clearly, this formula does not show in the results for a column without any data or with non-numeric data. So the final bit of functionality that we wanna add to this sheet is to highlight some results as per our original user requirements. To do so, we'll add some cell formatting for our net profit forecast and then move on to apply conditional formatting for our forecast cells. If you remember, one of our user requirements was to highlight the variance between our forecast for current year and the corresponding budget for the current year. To do so, we'll go under the home tab, then conditional formatting, highlight cell rules, and then select the less than option. Here, instead of entering a specific value, we'll select the first cell for a current year budget. We will then press F4 twice to set up a relative row reference. With this expression, Excel will compare each value within our forecast column to the corresponding value within our current year budget column, starting at the cell we just selected. Once confirmed, we can click on okay to accept the new formatting. Similarly, we can add additional formatting for values that are greater than our budget. Now, to copy of this conditional formatting into the same column of our operating expense section, we can simply click on any cell, go under the home tab, select the format pink button, and then click and drag to select all values within the same column off the operating expense section. In this case, however, we probably wanna switch our two rules. For expenses, it probably makes sense to highlight a greater value in red and a reduced value in green. To make this change, we'll go under home, conditional formatting and then manage rules. Here, we can edit individual rules and change the operator to get the desired results. We can then click on OK to accept all changes. We can apply similar formatting to highlight the variance between our original budget and the calculated budget. Finally, we should apply some outlining to the revenue and operating expense sections of our layout for ease of navigation for the end user. To do so, we will select the rows that we wanna collapse together, go to the data tab, outline, and then select the group option. We'll do a similar configuration for the operating expense section. Once completed, we can individually collapse these sections to free up space for other information we may need to review. Okay, so with that, we have now sourced all data required for our model, designed appropriate layouts, and authored relevant formulas to process our data. I hope we have demonstrated how easily we were able to populate the data within our model due to the planning and design activities we had undertaken prior to this. Now, to wrap up this lesson, let's look at implementing our final set of requirements related to summarizing results and highlighting data insights.