7_final review
44 You've created a pivot table based on the customer order data, and now you want to add a Calculated Field to the table to enhance it without changing the underlying data. Which of the following represent(s) an APPROPRIATE example of a Calculated Field in this context?
"Net Sales," defined as the Order Dollar Amount minus Commissions paid to the sales rep.
4 Suppose that you want to insert 3 columns to the left of the "Company Name" column shown below and that you're currently in Navigation Mode in cell D2. Which of the following choices is the most EFFICIENT way to do this?
Ctrl + Spacebar, Ctrl + Shift + + (pressed 3 times).
37 An example of a "Data Table" in Excel is shown in the screenshot below. Which of the following is NOT an advantage of using these Data Tables?
Excel will insert formulas automatically based on the titles of columns in the data tables.
3 Starting in Navigation Mode in cell D3, which sequence of keys would you press to produce the Excel screenshot shown below?
F2 (or Ctrl + U), Ctrl + Left Arrow, Shift + Ctrl + Left Arrow.
38 Suppose that you want to filter the data table in the previous question so that only orders associated with one specific city or one specific state show up. What is the most efficient way to do this?
Find the first instance of this city or state in the table with the Find command in Excel, right-click it, and then go to "Filter" and "Filter by Selected Cell's Value."
19 You've imported a set of first names, last names, and titles (e.g., Mr. and Mrs.) for a set of employees. What is the most efficient way to fix the missing periods and incorrect capitalization shown in the screenshot below?
First, use the PROPER function to fix the capitalization, and then use the SUBSTITUTE function to detect text such as "Mr " and replace it with "Mr.".
28 Suppose that you have the following range of data, and you want to write a VLOOKUP function that finds the monthly sales based on the Month and Sales Rep ID you have entered in cells C14 and C15:
HLOOKUP will not return the POSITION of the selected month, so you need to use MATCH to find it, or pass in the column position of the selected month in the table.
25 You've written a YIELD function in Excel to calculate the Yield to Maturity (YTM) of a bond with the characteristics shown below. Based ONLY on these numbers, what can you predict about this bond's YTM?
It will likely be above the bond's fixed interest rate of 5.40% because the bond trades at a 10% discount to par value, which makes more of an impact than the 10% discount on the redemption value.
52 Consider the following dynamic chart, with two checkbox options that allow the user to toggle the display of revenue and revenue growth on the chart: Cells P31 and Q31 in this spreadsheet are linked to the checkboxes, and the graph data and formulas are shown below: The formulas in column P use "" when P31 is FALSE, meaning the Revenue checkbox is unchecked, but those in column Q for the Revenue Growth checkbox use the NA() function instead. Why?
It's because Revenue is a Column Chart, while Revenue Growth is a Line Chart - there would be a "0 line" going across the bottom if you used "" instead of NA() when Revenue Growth is unchecked.
1 Which sequence of keystrokes would you use to go from the "Before" screenshot to the "After" screenshot shown below?
Right Arrow, Shift + Ctrl + Down Arrow, Shift + Right Arrow several times.
50 You have created a Price-Volume graph for Walmart that shows the company's stock price and volume of shares traded over time, as shown below. How can you fix the problem with "spaces" appearing in between the red volume bars?
Right-click the red bars, go to Format Data Series, and change the Gap Width to 0%. Right-click the horizontal axis with the dates, go to Format Axis, and change the Axis Type to Text.
24 You're evaluating the same set of cash flows for use in another schedule of the model, but now the dates are irregular rather than exactly one year apart, as shown below. Will the annualized rate of return produced by the XIRR function be higher or lower than the one produced by the standard IRR function?
Slightly lower, since the rental income in the first and second years is generated after the ends of Years 1 and 2.
53 You are writing VBA code to create an "Input Box" macro that will format cells that require user input with a yellow fill color, grey borders, and a blue font color. You want to execute this macro over large ranges to format many cells at once. You've completed the first step of recording the macro and simplifying some of the code, and your subroutine so far is shown below: Which of the following answer choices represent(s) LIMITATIONS or PROBLEMS with this VBA code that you should fix to make a more robust macro?
This macro will apply the formatting even to a blank cell, which is not correct since blank cells should not be input boxes. If the selected cell is far beyond the UsedRange of the spreadsheet, this macro will still apply the formatting to it - which is not correct. This macro applies the same font color to all types of cells, so it does not color-code constants in blue and formulas in black.
26 You're trying to set up date and title headers in a new financial model. You've selected the area you want to replicate in the other schedules, as shown below. What is the most EFFICIENT and FLEXIBLE way to set up this same header in the other schedules?
Use the Paste Links command (Alt, E, S, L or Ctrl + ⌘ + V, L) to paste direct links to everything and then anchor the cell references individually.
60 Which of the following statements describe(s) the CORRECT similarities and differences between User-Defined Functions (UDFs) and Macros in Excel?
You can create and edit both UDFs and Macros in the VBA Editor. Macros are more useful for formatting commands, such as color-coding cells, while UDFs are more useful for making certain calculations that are not built into Excel.
34 You're building error checks into the calculations for a set of comparable public companies. The formula you're using to calculate the valuation multiples is shown below (G60 represents Enterprise Value, and G6 represents LTM Revenue): Is the IFERROR function on the outside of this formula necessary, or could you replace it with other functions to check for errors, such as ISNA and ISNUMBER?
You could potentially rewrite this formula using several IF statements and other functions, but it would be longer and more difficult to interpret.
18 You want to write a summation formula for a range of cells in another spreadsheet, named Scratch-2. You write the following formula, but Excel does not accept it as a proper entry: =SUM(Scratch-2!B5:B20) What is the MOST LIKELY reason why this formula generates an error?
You should be using single quotes around the Scratch-2 name since the hyphen is considered a special character.
11 Consider the data shown in the cells below and the custom number format entered in the "Format Cells" dialog box below that. Select the answer choice that shows what this data would look like with the custom format below applied to each cell in this range:
red / - x
8 You're working in a new Excel file, and you've navigated to the Formatting options within the Home tab of the ribbon menu, as shown below. How can you tell that this file is NOT set up in an ideal way?
"Unhide Sheet" is enabled, indicating that there are hidden spreadsheets in the file, which is considered poor practice.
41 You are using the database functions DSUM and DCOUNT to summarize a table of customer orders. For which of the following criteria sets would these database functions be MOST useful?
(see pic for answer)
42 You have created a blank pivot table, shown in the screenshot below, based on source data from another worksheet: You want to display the total order dollar amount by year and month, and then by region. The month and year groupings should be in the leftmost column, and the regions should be in the topmost row. Which of the following screenshots shows the CORRECT positioning required to accomplish this, assuming that you ONLY drag in the fields shown above and do NOT modify the pivot table in any other way?
(see pic for answer)
56 You are writing a VBA subroutine to loop through a selection of cells and increment each cell's value by 1, but ONLY if that cell contains a non-date number AND that cell's value is positive. Which of the following screenshots represents the BEST code to accomplish this (assume that numConstantCells has been set up correctly)?
(see pic for answer)
6 Consider the Excel setup below, which will be used to calculate the percentage of revenue earned from each product. Which of the following formulas could you enter in cell C2 and then copy and paste down to get the correct results everywhere in column C?
=B2/$B$6 =$B2/$B$6 =B$2/$B$6
29 Now you want to write the same function as in the previous question, but you want to use INDEX and MATCH instead of the lookup functions. Which of the following answer choices is the correct formula?
=INDEX(Table_2020,MATCH(C15,B3:B12,0),MATCH(C14,B3:N3,0))
17 Which of the following choices is/are VALID formulas in Excel that will ALSO produce the desired results?
=SUMIF(C5:C20, ">1/1/2019",B5:B20) =SUMIF(C5:C20, ">"&"1/1/2019",B5:B20) =SUMIF(C5:C20, "<"&D2,B5:B20) =COUNTIF(C5:C20, "<1/1/2019")
10 You want to transform the text in individual cells, shown below, into the full sentence displayed below these individual cells. Which of the following functions is MOST likely to produce this output in all scenarios?
=TRIM(F2&" "&G2&" "&H2&" "&I2&" "&J2&" "&K2&".")
27 You want to write a VLOOKUP formula to retrieve the Order Amount for the first person whose First Name starts with "B" followed by any 5 letters, then "c" and then anything after the c. Based on the screenshot below, which of the following formulas will do this correctly? The data table is called "Orders" in Excel.
=VLOOKUP("B?????c*",Orders,9,FALSE)
43 You have created the following pivot table, which shows the percentage of sales from each company sales rep in each year: The source data includes only the Sales Rep ID - not the Sales Rep Names, which are in a separate data table. If you want to make this pivot table display each Sales Rep's Name rather than their ID, how could you do it?
Add the Sales Rep Name as a separate field in the source data, and do a simple lookup in the separate data table to retrieve it based on the Sales Rep ID; then, re-create the pivot table and include this field. Use Excel's Internal Data Model, create a "relationship" between these tables based on the Sales Rep ID as the common field, and then drag fields from both data sources into a single pivot table - but this won't work in all versions of Excel.
45 Consider the Stacked Column Chart shown below for Walmart's Revenue by Segment (figures are in millions of USD): Which of the following changes should you make to the graph to improve its formatting and make it easier to read and interpret?
Apply the following custom number format to both the vertical axis labels and the data labels: $0, \B Make all the font sizes consistent (the Legend uses a smaller font than the rest currently). Change the "Gap Width" of the data series source for the data labels so that they fit more easily on the columns. Make the borders of the horizontal and vertical axes solid black lines.
22 Suppose that you want to use the IRR function with the following set of cash flows to estimate the annualized rate of return. Why will it NOT work correctly?
If the cash flows are 0 in one period, such as in Year 2, you must enter a hard-coded 0 or formula that produces 0 rather than leaving the cell blank.
46 Consider the graph of valuation multiples for a set of comparable public companies ("public comps") shown below, with Walmart (WMT), the subject company, shown in red rather than blue: What is the BEST way to create this effect in an Excel chart and show the company you're analyzing in a different color when displaying valuation multiples?
In Excel, create one column with the real data and another "helper" column, and use the NA() function everywhere in the helper column except for the subject company's number. Graph both series in a Clustered Column Chart and then change the "Series Overlap" to 100%.
51 Consider the following Waterfall Chart, which you created manually in Excel - *not* using the built-in Waterfall Chart - along with the source data for it: What is the purpose of the "Blank" column in the middle of the source data?
It creates the invisible "supporting bars" under the visible yellow and green bars. It ensures that any negative value starts at a LOWER vertical position than the previous item in the series.
32 You are writing a MINIF function that determines the minimum dollar order placed within a certain date range. Excel does have a built-in MINIFS function, but you want to write your own version to practice. Your function is shown in the screenshot below (note that this is an array function, entered with Ctrl + Shift + Enter): Column K in the "Orders" spreadsheet contains all the dates for the orders, and column J contains the dollar amounts. Which of the following answer choices best describe(s) what's required - or not required - for this array function to work correctly?
It is NOT necessary to anchor the data ranges in the Orders worksheet - writing Orders!K3:K1001, for example, would have the same effect as writing Orders!$K$3:$K$1001 since this is an array function. It is NOT possible to copy and paste this function down because by default, array functions do not shift around the row and column components of cell references. If this formula is entered on the "Summary" spreadsheet, you could remove the Summary! parts in front of the B7 and B8 cell references.
58 You're going to write a macro to shift the number of decimal places for each number in a range of cells up by 1 or down by 1 so that 3.4 becomes 3.40, or 10.63 becomes 10.6. Excel has built-in commands to increase and decrease the decimal places in numbers, but to use these commands, you MUST select the cell first. You plan to loop through each numeric, non-date cell in the range, select it with .Select, and then execute these built-in commands. Is it a good idea to write the macro this way?
It's not a great idea to write the macro this way, but the other alternatives are more confusing to write or also have efficiency issues.
55 You're working on a macro to color-code cells in a financial model properly. As part of this process, you've written code that selects constant cells that have text, constant cells that have numbers, formula cells that have text, and formula cells that have numbers. Then, you loop through the individual cells in each range and change the colors appropriately. The portion for the numeric constant cells is shown below: Your co-worker reviews this code and says that a "For Each" loop is inefficient because you could use "With numConstantCells" and apply the blue font color to everything in the range at once; there's no need to check the interior colors. Is your co-worker correct?
Maybe - it depends on the setup of this spreadsheet and the most common interior fill colors for numeric constant cells.
59 You're writing a macro to "flip the signs" of numeric, non-date cells that contain formulas or constants in the selected range of cells. In other words, positive numbers will become negative, and negative numbers will become positive. Part of the code is shown below: Is it necessary to treat cells with constants and those with formulas differently, as this code does?
No - but it makes the code more efficient and easier to read, and it's easier to make the macro toggle between positives and negatives like this.
15 You are trying to make a financial model more user-friendly by using the Grouping and Hiding features in Excel. Does the screenshot below illustrate the correct usage of these features in financial models?
No - row 16 is hidden without being grouped first, which is poor practice.
33 You are creating a CapEx and Depreciation schedule using the OFFSET function. The formula you're writing will check the current year number, and if there's Depreciation associated with the CapEx in that year, it will multiply the CapEx by the appropriate Depreciation percentage. For example, the Year 1 Depreciation percentage is 20%, so the 2022 Depreciation percentage of 2022 CapEx should be 20%. The 2025 Depreciation percentage of 2025 CapEx should be 20%. But the Year 2 Depreciation percentage is 32%, so the 2023 Depreciation percentage of 2023 CapEx should be 32%, and the 2026 Depreciation percentage of 2025 CapEx should be 32%. Will the formula shown below calculate the Depreciation dollar amounts correctly?
No - the ($P54 - $P$53) part will not calculate the relative year number correctly, so it should be T$50 - ($P54 - $P$53) instead.
12 You are trying to set up custom number formatting for percentages in your financial model, so that positives, negatives, text, and 0 align properly, and so that negative percentages have parentheses around them. You are planning to use the following format: _(0.0%_);_(0.0%_);_("-"_)_%;_(@_)_% Will this format work correctly?
No - the negative format is incorrect, so it will have extra spaces around it but not actual parentheses.
14 You are using conditional formatting to color-code customer data, so that entire rows with order amounts above $500,000 appear on a red background, orders between $250,000 and $500,000 appear on a blue background, and orders below $250,000 appear on a green background. The minimum order amount in the set is $100,000, and the full range of cells for the order data is B3:K1001. Will the conditional formatting rules shown below work correctly?
No - the order of the rules is incorrect, and the one for $500,000 needs to be at the top, with the $100,000 rule at the bottom. No - the I3 part in each rule should be $I3 instead.
40 You have written DSUM and DCOUNT functions to retrieve the total dollar amount and order count of all orders that meet specific criteria, as shown in the screenshot below: However, Excel shows the Order Total as $408 million and the Order Count as 999 because it incorrectly counts ALL the orders, not just the ones that meet the criteria above. What is the MOST LIKELY reason why these database functions are not working correctly?
The DSUM and DCOUNT functions are incorrectly referencing both row 7 and also the several blank rows below it.
2 What is the PRIMARY difference between the "Format Cells" dialog box (Ctrl + 1 or ⌘ + 1) and the formatting options on the "Home" tab in the ribbon menu?
The Home formatting options are faster to access, but the Format Cells dialog box gives you more options.
30 In the lesson on INDEX, MATCH, and INDIRECT in the Excel course, we go through an example of how to use these functions to locate numbers in "messy" real estate data, where each real estate investment trust (REIT) reports its results in slightly different rows and columns. An excerpt of this file and an example formula are shown below: This formula above can be copied and pasted everywhere in the Summary sheet so that this single formula correctly retrieves the data for all the locations and all the REITs. The spreadsheets in this file have the following names: Summary, AVB_Data, EQR_Data, AIV_Data, CPT_Data, ESS__Data, UDR_Data. Why is it necessary to write such a complex formula to retrieve the data from the individual spreadsheets (AVB_Data, EQR_Data, AIV_Data, etc.)? Consider ALL the components of this formula, including the INDIRECT part.
Because a single "Region" might be in a different row number in each REIT data sheet, so we must search for its position in its sheet using MATCH. Because there are 6 spreadsheets with very similar names, so it is an ideal use case for the INDIRECT function - we can attach the text in the "REIT" column to "_Data" to create a text reference to each sheet. Because we know the column names for the financial metrics, but we don't know the positions of these columns in the other 6 spreadsheets.
35 You're creating a sensitivity table in a DCF analysis, and in the top row and leftmost column of the table, you've linked directly to the Discount Rate and Terminal FCF Growth Rate assumptions in the model, as shown below. The rest of the cells in the top row and leftmost column are then calculated based on additions or subtractions to these numbers. Why will this table not work correctly?
Because nothing in the table can be linked to the assumptions in the model - each number in the top row and leftmost column should be a hard-coded constant or calculated based on a hard-coded constant in this area.
21 Consider the data shown below. What results would the COUNT and COUNTA functions produce when applied to this entire range, from F2 to F17?
COUNT will produce 6, and COUNTA will produce 15.
23 Your co-worker does not believe the results of your IRR calculation, so he checks to see if IRR truly represents the annualized rate of return. To do this, he makes the upfront investment the "starting balance" and then compounds it by the IRR you calculated each year. His balance in the final year does not match the net cash flows or property selling price in that year, so he argues that your calculation is wrong. Based on the screenshot below, is he correct?
No - your co-worker is not subtracting the $12 in rental income from the running investment balance each year, so his calculations are off.
9 Suppose that you want to calculate the fraction of a year between two dates: January 15, 2020, and March 15, 2022. The January 15, 2020 date is stored in the named cell Date_1. You write the following function to do this: =YEARFRAC(Date_1, EOMONTH(Date_1, 26)). Will this function work correctly?
No, because the EOMONTH function should be EDATE instead.
16 You are setting up the "Public Comps Data" page for printing purposes, and you've also frozen the panes at the top of the sheet, near cell C5. Reviewing the screenshot below, what is INCORRECT or NOT IDEAL about these print settings?
None of the above - these print settings seem fine.
49 To build the dynamic share-price line, shown in red in the graph above, you started with the normal Football Field Chart and then created a "dummy series" for the company's current share price, followed by 1,000 and then 0's, as shown below: Then you added this series to the chart, changed the Series Formula to include both X and Y values, and changed the Chart Type to a scatterplot. Finally, you adjusted the horizontal axis labels, changed the secondary vertical axis range, and removed the secondary vertical axis label. Which of these steps in the process was NOT necessary?
None of the above, i.e., all of these steps were necessary.
57 You're writing a macro that will loop through a selected range of cells and "cycle" the number formats. In other words, if the cell currently has Number Format 1, it will switch to Number Format 2, and then to Number Format 3. There are 6 formats total, so when the cell is using Number Format 6, the macro will switch it back to Number Format 1. Each number format is stored in an array called "numberFormats", and part of the code is shown below (assume that nonEmptyCells has been filled correctly): Is there any way to make the code for this part of the macro more efficient?
Not really - no matter what you do, you have to loop through all the cells in the range and then loop through all the number formats for each cell.
31 You've written a single formula that will scan quarterly data for a 4-unit property and roll up everything into a summary page using SUMIFS, INDIRECT, and MATCH. The quarterly data and the formula and summary page are shown below:
Not really - you could implement these methods to simplify the formula, but they would also introduce other problems and create the need to update helper rows and columns.
54 You've now enhanced the macro above to fix some of these issues. Your current code for this subroutine is shown below: However, this version of the macro is not working correctly. What is the problem?
The Intersect functions are incorrect - you should add ActiveSheet.UsedRange to the input variables in addition to the two already there. You've failed to use the "Set" command in front of the Range variables when setting them equal to intersections and unions of other ranges. If FormulaCells or ConstantCells are empty, then the Union function will not work correctly, nor will the .Font.Color assignments below it.
47 Consider the "Football Field" chart shown below for Walmart's valuation, taken from the lesson on this topic in the Excel course: What is the MOST likely problem causing the valuation bars and text labels to be mismatched, and how could you fix it?
The data is reversed - you need to swap the top row in the source data with the bottom row and continue swapping each row with its counterpart in the bottom area as you move down.
20 You are modifying a summation formula that references a Customer Order data table on another spreadsheet in the same file. You have written the formula shown below, but it does not work properly. What is the problem?
The input order is incorrect - with SUMIFS, the summation range, Order_Table[Amount], should come first. The inputs are missing double quotes around the >= and < operators, and they do not use the & character to join those operators to cell references B7 and B8.
7 Suppose that you want to name cell I22, which represents Walmart's U.S. Stores Net Sales for FY 20. What is PROBLEMATIC about the setup for naming this cell, as shown below?
The potential name for this cell is invalid since it uses dashes or hyphens. This is an inappropriate use of a named cell since cell I22 doesn't represent a key driver or assumption that will be used throughout the model. Naming this cell will make it more difficult to copy and paste formulas around since formulas will use the name reference rather than an I22 reference.
13 In the top portion of the Walmart model and valuation, shown below, which of the following elements represent(s) INCORRECT financial model formatting?
The text on the left under Assumptions should be in black, not blue. Walmart Inc., WMT, and 1,000,000 should all be input boxes with blue font colors, yellow fill colors, and grey borders. The historical "Net Sales" figures should be in blue font color, assuming they are numerical constants taken from the company's filings.
36 You have finished building a leveraged buyout (LBO) model with support for interest calculations based on the beginning Debt balances or the average Debt balances. You want to use the Solver Add-In to determine the purchase premium required to achieve a 25% IRR, with the constraint that the purchase premium must be 0 or positive. Your setup for the Solver Parameters is shown below (assume that cell M8 contains the purchase premium assumption): Using ONLY this screenshot and the description above, how can you tell that Solver may not work correctly in this case?
There's a circular reference for the interest calculations in the model, indicated by the "Calculate" at the bottom of the screen.
48 The calculations for the DCF data series in the graph of the previous question are shown below. Why do you need to calculate the "25th Point," "Median Point," and the others on the right-hand side?
These are necessary if you want to show multiple segments for the different percentiles in each bar. If you used just the implied share prices to the left, each bar segment would be far too long because Stacked Bar Charts require distances, not points, as inputs.
39 You have set up a data table called "Order_Table" in an Excel file, and you've written a SUMPRODUCT formula to query the data and return the total dollar amount of sales that match a specific region, industry, year, and month. This SUMPRODUCT formula is shown below: You decide to rewrite this formula using SUMIFS to make it easier to understand, and you enter the following to do it: =SUMIFS(Order_Table[Amount], Order_Table[Industry], Summary!Q7, Order_Table[Region], Summary!Q6, YEAR(Order_Table[Order Date]), YEAR(Summary!Q8), MONTH(Order_Table[Order Date]), Summary!Q9) However, Excel does not even let you enter this formula. How can you fix this so that it works properly?
YEAR and MONTH don't work over entire ranges, so you should change the criteria to compare Order_Table[Order Date] to the date formed by cells Q8 and Q9 and make sure it's within that month using EOMONTH.
5 You are completing the Weighted Average Cost of Capital (WACC) calculations for a company. You get the results shown below after copying down the formula for Equity Value for the comparable public companies. What caused this problem, and how could you fix it?
You used Ctrl + D to copy down the formula, which incorrectly copied the number formats as well; Paste Formulas (Alt, E, S, F on the PC or Ctrl + ⌘ + V, F on the Mac) would have been a better option.