Part 2, Chapter 14: Using Worksheets and Worksheet Functions

OFFSET =

(reference, rows, columns)

Which formula entered in the data validation error alert dialog box under custom entry would prevent a duplicate entry from being made anywhere in the range D3:F100 selected for the data validation?

=COUNTIF($D$3:$F$100,D3)=1 You can select a range to which to apply data validation, but the cell specified in the formula must be the first cell in the range (top and/or left cell). The formula for accepting non-duplicate entries only for the range D3:F100 is: =COUNTIF($D$3:$F$100,D3)=1. Note that this function requires an absolute reference to the range, but the second argument is relative and after the first cell in the range is specified, it will apply to any cell selected in the range.

Which formula will return a #NULL! error?

=D1:D5 A7:E7 A #NULL! error is returned when an intersection function has no cell in which the ranges intersect.

Which is a method of making a check sum for the difference between two summed values, B10 and B13, be tolerant to very small rounding errors regardless of which sum is greater than the other?

=IF(ABS(B10-B13)>0.1,"ERROR","OK") An IF statement can be designed with a specification of how much error to tolerate such as up to 0.1: =IF(ABS(B10-B13)>0.1,"ERROR","OK"). The absolute value will make it so it does not matter which number is slightly larger.

Which would be a more efficient way of expressing =IF(long_formula_A<long_formula_B,long_formula_A,long_formula_B)?

=MIN(long_formula_A,long_formula_B)

Which is the correct present value worksheet formula for a value of $1,000 (input cell B1) to be received in five years (input cell B2), if the discount rate is 10% (input cell B3), and there are no payments.

=PV(B3,B2,0,-B1)

The following formula in column E needs to be filled over across columns: =PV(Inputs!D10,E3,0,-E11). Which correctly lists the reference to the rate as absolute and uses the correct mixed references for the nper and fv arguments?

=PV(Inputs!$D$10,E$3,0,-E$11)

The syntax for present value is

=PV(rate,nper,pmt,[fv],[type]). Therefore, the B3 rate is listed first, the B2 net period is listed second, and the payments are listed as 0 as there are no payments (and the type is omitted because there are no payments). Note that the fv is the current period's after tax cash flow but is expressed as a negative value to get it to calculate to present value correctly: since the future value of a loan is zero, a negative amount indicates a cash inflow in future years. These entries result in a present value of $620.92.

When creating a named array constant in a worksheet, in some worksheets such as MS Excel, after clicking Formulas, Define Name, Define Name, the New Name dialog box appears. What do you enter in the Refers to: field to create an array constant that returns weekday abbreviations in one row with the corresponding weekday number in the row below?

={"M","T","W","Th","F","Sa","Su";1,2,3,4,5,6,7}

There are two basic types of macros: a Sub or a Function.

A Sub procedure is a new command that performs several tasks using VBA code. A Function procedure returns a single result in a single cell, much like a normal worksheet function.

What will usually happen right after an output of a worksheet financial model is created that was used earlier in one of the supporting calculations?

A circular reference warning will appear and the model will not calculate correctly.

On the Home tab, the Find and Select button contains a Go to Special option, which allows searching for many things.

A good use for this function is to search for all formulas on a tab, then zoom out to see the whole page, which can give a broad idea of how an unfamiliar model is constructed and can also show where a hard-coded value was entered where a formula is expected. Alternatively, using Go to Special and searching for constants will highlight just the hard-coded values.

Sometimes IF statements can be inefficient because they may require repeating a complex formula in the logical test and a different argument.

A more efficient way to a greater than or less than logical test is a MAX or MIN function. MAX returns the highest value of all arguments and MIN the lowest. Since the logical test determines if A is less than B and returns A if true, a MIN function can return whichever argument is smallest without having to list the long formulas twice each.

Two methods of making check sums include testing whether two cells are equal and returning TRUE if they are or using an IF statement to test if the values are not equal and returning "ERROR" if so.

A problem with these methods is that a FALSE result could occur due to a rounding error in some cases.

Embedding objects can be done both to and from a worksheet.

A slideshow or word document or other type of file can be embedded into a worksheet. This allows FP&A professionals to use the full features of these products when developing and formatting presentation materials. To embed an existing document or slideshow, on the Insert Tab, click Object, and select the type of object in the dialog box.

Sometimes documentation discusses input values, calculations, or outputs that may change, which can make the documentation quickly out of date.

A way to get around this is to develop text that changes dynamically because it is linked to the relevant cell values. The TEXT function can reference the contents of a cell and return it as formatted text that can be concatenated with other hard-coded text. To concatenate, the CONCATENATE function or an ampersand, &, can be used to join the various parts into a whole sentence. The TEXT function's second argument indicates the formatting to apply.

Which financial model has the highest risk of containing a computer virus?

A worksheet with macros-enabled.

An inputs tab for a worksheet financial model uses MATCH and OFFSET to allow users to type in one of the following scenarios in cell C2, Scenario Names: Base Case, Lo Ext Hi Dem, Best Case, Worst Case, or PriceSens. When entered correctly, all other input cells are filled in with the proper values. Which would prevent users from mistyping a scenario name?

Add a ComboBox drop-down menu referencing the scenario range and cell C2.

Tracing dependents is also a useful thing to check before considering deleting the contents of a cell so that you know what will be affected by the deletion.

After doing this, you can delete the cell and if the change is still unacceptable (e.g., there were far too many dependents to trace in a complex model_, you can close the file without saving and revert back to the prior version.

All columns should contain categories and each column needs a category header.

All rows should contain entities only (instances of a particular record). Data that is normalized contains no row headers.

Which statistical test can be used to compare numerical data that is comprised of three or more groups?

Analysis of variance.

Which is a useful Pivot Table feature for manipulating data from a database that is not otherwise easily manipulated but requires turning off Grand Totals?

Calculated Item

When grouping data in a Pivot Table, what is needed to view the new group's subtotals?

Click on the minus sign to collapse the group.

Which qualifies as normalized data for use in a Pivot Table?

Columns contain only categories and rows contain only entities

How can documentation be designed to change dynamically in response to changes in input, calculation, or output values?

Concatenate hard-coded text with cell references.

A simple check sum subtracts one value from another and is zero when in balance and some other value when incorrect. Which of the following might make a nonzero result more obvious?

Conditional formatting

Which can be used within a Pivot Table to visualize the size of elements in a field so that business questions can be more easily answered?

Conditional formatting data bars

Which is an optional step prior to changing a range of data into a Pivot Table that will allow new rows added to the range to be included in the Pivot Table after a refresh?

Create range into a table

Once the data is in an appropriate format, the next step is to create the range of data into a table.

Creating a table first is not strictly necessary, but if a new row is added, the refresh will add the new row automatically to the Pivot Table rather than needing to re-specify the range. Normalizing the data range is a necessary step and is not optional.

Which can be done to remove the possibility that a MATCH function can have an error if a user mistypes a name in the input field?

Data validation list

What is a best practice while developing or modifying a model used to calculate external funding required, assuming interest expense in the income statement calculation is partly derived from the interest rate times the external funds required output?

Disable iterative calculations until the model is ready for use.

When creating a calculated field in a pivot table, what references are used within the formula entered in the Insert Calculated Field dialog box?

Field name references

Which type of macro returns a single result in a single cell?

Function

When preparing a Pivot Table to produce a frequency distribution of a Sales per Month field, what is done with the range that is created in the row labels area?

Group it by the proper increment range Add the field to be made into a frequency distribution to both the Row Labels and in the SUM Values boxes. In this scenario, Sales per Month would be placed both areas. Then select the row labels range and on the PivotTable Tools tab, Options tab, select Group Selection and select the grouping by increment range, for example by every $1,000.

When applying conditional formatting data bars to elements of a Pivot Table, what should be excluded from the data bar formatting to avoid distorting the appearance of the data bars?

Groups

Which can show where hard-coded values were used in a worksheet where formulas were expected?

Home, Find and Select, Go to special searching for formulas (all types)

External funding requires enabling iterative calculations because the interest expense of the new external funding is added to the total interest expense on the income statement.

However, during development or if the model needs to be modified further, a best practice is to disable the iterative calculations until it is ready to be run again. In this way, you can catch any inadvertent circular references in your model right away.

The mode is the most frequently used occurring value, but if there are two or more values that are equally frequent, MODE.SNGL simply returns the first one found, wile MODE. MULT is an array function that requires specifying the output range (how many modes will be found) in advance even though you will probably not know how many modes to expect.

However, you can select a larger array range than is likely necessary and N/A will appear in any unused cells.

One issue with SUMIF (range, criteria) is that while cell references can be used for the arguments, if logical arguments (arguments that include math or logic operators) are used in the criteria, they need to be in quotation marks (as do text strings) and cannot contain cell references. For example, if the logical argument is ">3" the number 3 needs to be hard coded and cannot use a cell reference.

However, you can turn a logical argument into an expression that can contain a cell reference. In these cases, the expression must include an ampersand (&) symbol to concatenate the elements together into your expression. Your logical argument ">3" would then become your expression ">"&B1. Utilizing this technique, you can create SUMIF that point to cell references for variable criteria.

The syntax for external links in the same file folder is as follows ='[Workbook Name]Sheet Name'!CellAddress.

If spaces exist in the workbook or sheet name, then the single quotation marks need to be in place as shown. If the link is to a closed workbook or a workbook not in the same file folder, then the entire directory path must also be specified, such as ='C:\FolderName\Subfolder\[Workbook Name]Sheet Name'!CellAddress.

Worksheets disable macros by default because macros can be used to deliver computer viruses.

In Excel, the .xlsx format does not allow macros, while the .xlsm format can have macros, but some users will not trust these files unless they come from a trusted, digitally signed source.

Once the Pivot Table is created, to add a calculated field, on the PivotTable Tools tab, Options tab, select Fields, Items & Sets then click on Calculated Field.

In the dialog box, enter a name in the Name field and enter a Formula using table headers for calculated items.

Which error testing process includes checking data units or missed data?

Input error checking.

When applying data bars, it is best to apply conditional formatting at the item level.

It is usually best to avoid applying the data bars to groups because it makes those bars very large and the item bars much smaller.

In a Pivot Table, how can the FP&A professional generate sales subtotals and sales grand totals using the Sales field?

Just add Sales to the SUM Values box.

A model uses OFFSET to have users specify the proper scenario to use in input fields by entering the number of columns to offset from the reference point. Which additional function allows users to specify a scenario name directly rather than risking selecting the wrong scenario if the columns are rearranged?

MATCH

Which error can drive significant issues, especially in more complex models?

Misplaced parenthesis even when the automated fix is accepted.

Which would allow an FP&A professional to insert a small portion of a large worksheet into a slideshow while retaining ability to use the worksheet formatting tools?

On the Insert Tab, click on Object

Which will the default paste command do when pasting a chart between a worksheet and a slide show?

Paste a link

What should an FP&A professional try first if a Pivot Table is not yielding useful information?

Rearrange the fields in column, row and summary areas experimentally.

Which range of data in a worksheet is ready to be converted into a table?

Rectangular range with column headers but no row headers or summary rows.

Which should be done if a worksheet financial model contains a macro that could be replaced by a set of functions?

Replace the macro with the functions if they accomplish the same goals. Macros are far less transparent than functions and formulas entered in cells and therefore make a model more difficult to understand or audit. If a simpler method exists to do a task, that simpler method should be selected.

What does the following function do? {=FREQUENCY(A1:A50,G2:G10)/COUNT(A1:A50)}

Return the percentage frequencies of data array A1:A50 items falling into the bins specified in G2:G10

Which situation might improperly return a FALSE or ERROR result for a check sum?

Rounding error

Which depreciation function depreciates assets by the same amount for each year of the assets' life?

SLN Straight-line depreciation depreciates assets by the same amount in each year of its life.

Which function requires logical arguments for the criteria argument, which if entered as if they were text strings require hard coding any values that are part of that logical argument?

SUMIF

What can be done for a chart to highlight the area of significance when there are large amounts that range between $145 million and $200 million?

Set the minimum on the axis to about $100 million

A best practice is to use a range for the axis that highlights the area of significance by customizing the minimum and maximum values for the range.

Setting a minimum that omits the bulk of each value but still shows the areas that differ will highlight this area and make the differences appear to be more prominent.

On a chart, what should be done with data that is not in a time series or other specific order?

Sort from largest to smallest or vice versa

A macro that can reformat imported data into the correct format by performing several sequential steps is what type?

Sub

What happens if the base data underlying a Pivot Table changes but no new columns or rows were added?

The FP&A professional has to select PivotTable Tools, Options, Refresh.

Which is an advantage of using a Pivot Chart over a chart for data not in a Pivot Table?

The chart reflects the current level of aggregation or manipulation of the Pivot Table.

There are many ways to calculate a payback period, but most require calculating the after-tax cash flows and cumulative cash flows.

The cumulative discounted cash flows are needed to calculate the discounted payback period only.

Which is an advantage of designing a worksheet matrix using the CORREL function to measure correlation of multiple variables in each relevant cell over automated methods of generating correlation analysis that do not rely on functions?

The fields automatically update if the underlying data changes.

In the following formula, which set of brackets are entered manually rather than using Ctrl+Shift+Enter? ={SUM(HLOOKUP(B8,{2,3,4,5},0))}

The inner set of brackets The outer set of brackets are added automatically after using Ctrl+Shift+Enter, but the inner set of brackets is an array constant and these brackets are added manually.

A Pivot Chart is just the same as any other chart except that the chart will change interactively as you expand or collapse rows or add or remove fields from the various boxes.

The key to making effective Pivot Charts is to minimize the data that is included in the chart or it will be too much information. Charts can only display one type of unit of measure on a given axis at a time, such as units or dollars.

What happens if a workbook that is linked to another workbook has a file name change?

The links are broken and can be restored using change source.

Sum-of-the-years' digits also uses accelerated depreciation.

The method chosen depends on accounting rules for the asset, but organizations typically select the fastest allowed method. This minimizes taxes in early years and increases taxes in later years, but due to the time value of money this practice increases an asset's NPV and IRR.

The PV function arguments are as follows: =PV(rate,nper,pmt,[fv],[type])

The mixed references to nper and fv will allow the column references to change relatively as it is filled over while holding the row references constant.

In a Pivot Table, if a report is filtered by months and January and February are selected to display, which is an issue with this Pivot Table view?

The month labels are not displayed

The syntax for the double-declining balance method is

The syntax for declining-balance is =DB(cost,salvage,life,period,[month]).

Note that when you enter an output equation in a worksheet financial model that was previously referenced as one of the supporting calculations, a circular reference occurs because the output is used to calculate this cell in the first place.

Therefore, you need to enable iterative calculations, and until you do, the output cell does not calculate correctly.

One issue with OFFSET is that if additional columns or rows are inserted within the range, the OFFSET function(s) are automatically updated to include the new column or row, but the reference numbers may refer to the wrong columns or rows.

To avoid this issue, a MATCH function can be used to look up a column by name rather than relying on just the distance from a reference cell.

In a worksheet financial model, which is the best method to show the ramifications of deleting a cell?

Trace precedents and dependent.

Which type of trend line in a line chart would help smooth out noisy data but still show some variations in the trend?

Two-period Moving Average

Which is the best way to turn a horizontally arranged table into a vertically arranged table?

Use the TRANSPOSE function as a multicell array.

When a field is dragged to the Report Filter area, it is not longer in the Column or Row Labels area and the headers no long appear.

When filtering by multiple months, etc., this can be confusing. However if the Month field is dragged back to the PivotTable Field List, it will still be filtered until the filter is changed.

When can the following syntax be used to establish links between two workbooks? ='[Workbook Name]Sheet Name'!CellAddress

When workbooks are in the same file folder only.

What is the best chart to use for a regression analysis with a large amount of data points?

XY scatter chart

A calculated item is

a calculation that is performed for the items within one field and must reside in the Column Labels, Row Labels or Report Filter areas. Calculated items are sometimes necessary when using data from databases that cannot be easily manipulated. When using calculated items, it is necessary to turn off Grand Totals because these new calculated rows will be added to the summary data, thus throwing off the totals.

Conditional formatting data bars provide

a visual indication of the relative size of each element in a field so that differences can be made obvious.

Copying and pasting is very straighforward and the default paste will paste in static values when pasting in a range of data,

but will paste in a link if pasting in a chart

Input error checking is

centered on checking data units, actual data entry errors, and missed data (e.g., a final row is omitted from a sum).

Which is required to calculate the basic payback period?

cumulative cash flows.

Straight-line depreciation (SLN)

depreciates assets by the same amount in each year of its life.

Declining balance (DB)

depreciates at a fixed rate but period and first-year months can be specified

Stacked charts show how

elements are interrelated while still showing absolute values, meaning that monthly increases or decreases can be made clear. A 100% stacked chart would not clearly indicate which months had better total results.

One way to prevent user errors with mistyping is to use a ComboBox, which is a type of drop-down menu that allows users to select

from the possible entries and thus avoiding having to type a name at all. Note that the custom formulas for data validation are both erroneous uses of the OR function.

Stacked charts can show

how elements are interrelated while still showing absolute values, meaning that monthly increases or decreases can be made clear. A 100% stacked chart would not clearly indicate which months had better total results.

Analysis of variance (anova)

is a type of statistical analysis that can be used to compare three or more groups of numerical data. Analysis of variance (anova) statistically determines if two or more samples were drawn from the same population.

Double-declining balance (DDB)

is an accelerated depreciation method that depreciates the most in the first year and steadily declines each successive year.

The array range for the MODE.MULT function in a worksheet should be

larger than is likely needed

XY scatter charts are good for

representing large amounts of data points graphically, such as in a regression analyses.

One best practice for charts is to

sort data that is not in a time series (or other specific order) from largest to smallest or vice versa.

Which chart subtype can best show that monthly elements are interrelated such as sales by region while still making it clear which months had better total results?

stacked chart

Which statistical analysis method is used with small data sets to measure whether there was much improvement between the means of two samples per entity, such as from a pre-test and a final exam?

t-test

When a field is placed in the SUM Values box,

the Pivot Table automatically generates both subtotals and a Grand Total for both rows and columns.

To create a named array constant (directions assume MS Excel), click Formulas, Defined Names, Define Name. In the New Name dialog box that appears, enter a name for the array constant and in the Refers to: field, enter the array constant, manually including the curly brackets.

the first row of the array is entered with commas separating each column, a semicolon is used to indicate that the array should shift down a row, and the number entries are entered separated by commas.

To make check sums or other error checks more obvious,

they can be designed to be color coded depending on what result is displayed, for example red if in error and green if ok.

A t-test tool helps show

where statistically significant differences exist between two small samples. One method is a paired two-sample test for means. This method is used when there are two observations per entity (e.g., customer satisfaction scores or a pre- and post-test)