ISYS 210 PHINNEY EXAM 2

Boolean logic is based on what two values?

0 and 1, shown to us in Excel as False and True, respectively

Different cell references

A1 Both the column and row reference are "relative" and will change when the reference is copied and pasted to other cells $A1 The column reference is absolute and will remain constant when copied and pasted to other cells The row reference is relative A$1 The column reference is relative, while the row reference is absolute $A$1 Both the column and the row reference are absolute

To group cells together, sort

Data menu -> "Sort & Filter" -> "Quick Sort Ascending/Descending" easy way to rearrange a data table limited to sorting just one column at a time

To sort on more than one field

Data menu -> "Sort & Filter" -> "Sort"

How do I clear all formatting from cells?

Home menu -> "Editing" group -> "Clear Formats"

How to format cells with differing info?

Home menu -> "Number" group

How do I repeat page elements when I print spreadsheets that span multiple pages?

Page Layout menu -> "Page setup" group -> "Print Titles" -> "Header/Footer" tab

How do I reference a range of cells?

Specify the upper left cell ":" Specify the lower right cell ex: A2:C2 references cells A2, B2, and C2

Difference between VLOOKUP and HLOOKUP

VLOOKUP assumes that the first column of the reference table contains the values used to find a match, and then returns the related value from the specified column HLOOKUP uses rows instead

How does Excel store dates and times as?

a numerical value dates are represented by the numbers to the left of the decimal and to the right is time

What are spreadsheet models?

a physical representation of a larger or more complicated thing or idea used to represent the logic of a more complicated logical process must be detailed enough to capture the salient elements of the logical process it represents

=VLOOKUP

accepts 4 arguments: The first argument, "lookup_value", refers to the information that will use to pull a value from the reference table The second argument, "table_array", defines the location of the reference table (do not include table labels at the top of the reference table in your range and the first column of the reference table will always be used by Excel to find a match) The third argument of the VLOOKUP function is "col_index_num", or the column in the table that contains the information you wish to have returned The last argument for the VLOOKUP function is "[range_lookup]." - optional argument to specify an exact match found or approximate (Excel will assume TRUE/approximate if nothing is written for this argument)

How is a cell referenced?

according to column letter and row number ex: A6 is located in column A and in Row 6

Data menu

aid in importing and working with large amounts of data

Formulas menu

aid in performing calculations using Excel's built in functions

=AND

all combined expressions must be TRUE

"Freeze Panes" feature

allows you to "Lock" rows and columns so that they don't disappear when you navigate through the data table View menu -> "Window" group -> "Freeze Panes" can either freeze the first column, row, or worksheet area select column/row to the right/below where you would like to freeze

"Alignment" group

allows you to change the positioning of text within a cell or range of cells

"Filter" feature

allows you to display only specific information on a data table

What do the "Trace Dependents" feature and "Show Formulas" feature do?

allows you to see which cells are referenced in a particular formula, to ensure there are no mistakes and to display the functions in each of the cells of a worksheet rather than the actual results found in "Formula Auditing" section of the "Formulas" tab

Benefits of building models

allows you to use spreadsheets to make good decisions based on sophisticated and relevant analyses of the problems you are trying to solve

Function Wizard

also known as the Function Arguments window allows you to enter or select the inputs/arguments for the function provides a description of the function and each argument, and lists the function result based on the inputs you enter

Workbook

an Excel file that stores all the information, previous calculations, and analyses that you may have already completed contains at least one worksheet

=OR

at least one of the combined expressions must be TRUE

=AUTOSUM

automates the use of SUM, AVERAGE, COUNT, MAX, and, MIN. automatically populates the arguments for any of these functions with the continuous section of non-blank cells next to an active cell

"Fill" feature

automatically populate cells in a worksheet located in the "Editing" group in the Home menu can automatically copy the contents of one cell to other contingent cells can complete a pattern within a range of cells

=AVERAGEIF

calculate the average for a set of values that match a specific criterion =AVERAGEIF(range,criteria,[average_range])

=SUMIF

calculate the total for a set of values that match a specific criterion =SUMIF(range, criteria, [sum_range])

=EFFECT

calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original loan amount

Every good spreadsheet model will have three common elements:

changeable inputs, outputs, and intermediate calculations

=CONCATENATE

combines blocks of text

intermediate calculations

comprises the area of the worksheet where the logical steps required to complete the process are modeled break the process into enough detail that the user can reconstruct the final outcomes of the model

"Font" group

contains menu items that change the appearance of the text in the cells

Worksheet

contains the actual data and calculations organized into a collection of cells arranged in the form of a table

=PROPER

converts a block of text to title-case (the first letter of each word is capitalized)

=COUNT

counts the number of cells in a range of cells that contain numbers

=COUNTA

counts the numbers of cells in a range that are not blank

=COUNTIF

determine the number of cells within a range of cells that contain a specific value =COUNTIF(range, criteria)

Scenario manager

examining the impact of several inputs to a spreadsheet model changing at the same time allows you to create a number of different scenarios based on different states of the same set of inputs can compare a number of different hypothetical situations at the same time The first step to using scenario manager is determining which of the input variables in your model you want to change for each scenario examined. You then determine the values that you would like to examine for each variable. The next step involves defining each scenario in Excel by selecting the inputs used in that scenario and entering the values for each input. Finally, you can create a summary worksheet that compares each scenario and how each influences a specified model outcome.

Insert menu

features that allow you to place specialized elements into your workbooks, such as charts, pictures, and shapes

Review menu

help in sharing Excel workbook with colleagues

What do the RATE and EFFECT functions do?

help you understand how much interest you will be paying on a loan (or earning) the rate function requires 3 arguments: he number of payments to be made (nper), the amount of the payments (pmt), and the original amount, or present value, of the loan or investment (pv); it will return the interest rate that is paid on the loan or investment described by the arguments the EFFECT function is used to calculate the real interest rate paid the arguments for the EFFECT function are the interest rate quoted on the loan (nominal_rate) and the number of times per year that interest will be charged (npery)

"Auto-fill" feature

insert values into a range of cells by completing a pattern in previous cells

"Copy and Paste"

inserting the same values into multiple cells in a worksheet allows you to reproduce a value in one cell and replicate it in other cells

Home menu

items used in formatting the appearance of data in cells

Model outputs

represent the outcome of the process that is modeled in the spreadsheet not necessarily a single calc, but end-state usually placed at the bottom of a spreadsheet model

=LEFT/=RIGHT/=MID

returns a specified bumber of characters starting from the beginning of a block of text/ end of a block of text/ from the middle of a block of text

=NOW

returns the current time

Three told used for conducting what-if analyses

scenario manager goal seek data tables

How do I insert rows or columns?

select the column to the right of where you would like to insert the column and click "Insert" on "Cells" group Home menu or select the row below where you would like to insert the row

Cells

store individual pieces of data or calculations

How are arguments entered into a cell?

the arguments for any function are entered in parentheses after the name of the function, and each is separated by commas required arguments appear without brackets optional arguments appear within brackets

What does Excel provide managers with?

the capability to perform sophisticated and powerful analyses

What are each of the financial functions based on?

the notion of the time value of money, or compounding interest

Who does compounding interest benefit?

the one earning the interest (the one paying the interest is not benefiting)

Negation (=NOT)

the situation where you check to see if a Boolean expression is not evaluated as TRUE to check to see if the opposite of the logical expression is true used when it is easier to define what something is not than it is to define what something is

How to interpret times when subtracting from one another?

the time shows as a fraction, or decimal value, of a day, need to multiply by 24, then 60, gives you minutes

Changeable inputs

these are the elements of the model that the user will change every time a new application is processed placed at the top of a model (easy for user to spot and modify)

The most important requirement for building an effective model

thorough understanding of the process you are modeling

How do I re-size cells?

use "Format" in the "Cells" group in the Home menu, click "Column Width"

How can I hide information?

use Home menu -> "Cells" group -> "Format" -> "Hide & Unhide

"Format Painter" feature

used for reusing the same set of formatting options from one cell into other cells Home menu -> "Clipboard" group -> "Format Painter" -> select the destination cell where you want to apply the formatting

Nesting

used to accommodate more than 2 possible outcomes in an =IF statement N nested IF functions: N+1 possible outcomes

View menu

used to change how a worksheet is displayed on the screen

Stacked column charts

used to compare categories of items or the same category over time breaks the column bars into segments that represent subcategories that are consistent across the columns can be used to compare more categories than a column chart

Column charts

used to compare different categories of items of the same category over time types of items-x axis values-y-axis not particularly useful when comparing a large number of categories (limit 5-10)

Pie chart

used to compare the parts of a category to the whole (typically expressed as a percentage) do not have a horizontal axis, but instead segments/wedges are treated as such

Line chart

used to compare the values of a particular category over time helpful for uncovering trends in a dataset

Page Layout menu

used to format the appearance of worksheets

File menu

used to manipulate workbooks

PivotTable

used to organize and summarize large amounts of data, change how we summarize data and look at it in different ways

"Themes" feature

used to quickly add colors and other style effects to your worksheets

Goal Seek

used to work backwards from an outcome calculation to determine an input condition necessary to arrive at the desired outcome allows the user to specify the desired value for an outcome cell and select an input cell that Excel should modify to achieve the desired outcome limitations: only specify one input and one outcome variable to change as part of the analysis; must specify an exact amount for desired outcome; set cell must contain a formula

Why is it better to use cell-referencing than hard-coding?

values stored in the range of cells may change, if you hard-code, you will need to manually change these values in the function

Data tables

we can specify a number of different possible states for one or two input variables and construct a table in our worksheet that calculates an outcome for our model based on each state of the input variables

How to identify the elements of the process you need to include in your model

work backwards from the outputs of the model until you work through the intermediate calculations to the changeable inputs for the model