excel exam 3

4 rules for how criteria range operates with database functions

1. field names must be listed in the 1st row and match exactly the database field names

2. criterion for each field are listed below the subsequent rows of criteria range

3. criteria within the same row are combined using the AND logical operator

4. criteria in different rows are combined using the OR logical operator

Three layout options for PivotTables

Compact, tabular, outline

Compact PivotTable layout

fields in the row area are placed together in the first column and separated by outlining buttons, subtotals are placed at the top

Outline view PivotTable layout

fields in the row are are placed in separate columns and subtotals placed at the top

tabular layout charcateristics

has gridlines, fields in the row area are in separate columns, subtotals at the bottom of the group

How to sort on a pivottable

• you can drag and drop the items in the order you want

• you can also select the cell you want to reorder and type the item you want to be placed there

• you can use the filter button

  • allows you to sort in ascending or descending order

  • also allows you to sort by multiple values

calculated field

involves calculation between multiple fields (ex. the average of two columns)

how to insert a calculated field

pivot table analyze » fields, items and sets » calculated field

calculated item

involve a calculation between a field and constants. uses the values under a given column or row to create a new item within the field

t/f: a calculated item can be moved, sorted, and filtered

true, a calculated item is treated like any other item

how to create a calculated item

click on the field you want to add the item under » pivottable analyze » fields, items, sets » calculated item

grand totals

show automatically for rows and columns but can turn off for rows, columns, or both

subtotals

can determine where to display them via the pivottable analyze tab

what is the automatic sort of a pivottable

automatically sorted in alphabetical or chronological order

manual filters

select values from check boxes listing all unique field values

date filters

select data based on specific dates or date ranges

label filters

select data based on the labels of the items in the field

value filters

filter based on values of the numeric field elsewhere in the pivottable

four types of filters in a pivottable

value, date, label, manual

grouping

can create a manual group of items so they appear as a new field within the table (ex. grouping months to represent tourist season vs offseason)

how to allow multiple filters per field in a pivottable

right click » more pivottable options » layout (macos) or totals and filters » allow multiple filters

base field or item

used as a basis for comparison to apply pivottable calculations

where can you apply a conditional format

selected cells, all cells showing a field value, all cells showing multiple field values

how to apply conditional formats to a pivottable

home » conditional format » select whichever style you want

icon set

can display icons based on a cell’s value (ex. green or red arrows)

color scale

conditional format in which the fill color is based on a range of cell values where cells with larger values get lighter or darker

pivottable cache

the data structure where the information used to create a pivottable is stored

how to create a new cache

alt + D, specify type of data and type of report, data source, no, specify location, finish

t/f: you can use data outside the pivottable for a calculated field or item

false, the pivottable can only use values within itself or constant values (ex. you can’t use a vlookup bc it requires you to look up values that are not in the pivottable

t/f: calculated fields are limited to sums

true, cannot use a calculated field with the average occupancy rate bc the calculated field can only use the sum of occupancy rate

what is a problem that might occur since calculated fields can only use sums?

some of the calculations might not be accurate (ex. cannot calculate the total income by multiplying the price per room times the rooms rented bc the calculated field will use the sum of the price per room times the sum of the rooms rented)

tips for clearly communicating with pivottables

• give pivottable fields and calculations clear and descriptive names

• apply conditional formats to highlight key features

• document customized calculations either in a documentation worksheet or via a list formula in excel

purpose of the pmt function

can calculate the size of payment made periodically to either repay a loan or reach an investment goal

nper

number of total payments

rate

interest rate per period

pv

present value of the loan or investment

fv

future value of loan or investment after all scheduled payments have been made

cash flow

indicates the direction of money to and from the individual or company

positive cash flow

represents money that is coming to the individual

negative cash flow

represents money that is leaving the individual

describe the sign (+ or -) of each pmt and pv for loans and investments

• pv for a loan: positive bc represents money being borrowed (given to individual)

• pmt for a loan: negative bc repaying the loan

• pv for an investment: negative bc spending money

• pmt for investment: positive bc return on investment from individual

what will fv be if you intend to completely repay a loan

0

purpose of nper function

determine the number of payments needed to make per period given the interest rate, payment amount, pv, and fv

purpose of amoritazation schedule

specifies how much of each loan payment is devoted toward interest and toward repaying the principal

principal

the amount of the loan that is still unpaid

why does the cumulative interest function not include the fv argument

the assumption is that the loan is completely repaid

t/f the type argument is optional in the cumipmt function

false, you must specify when the payments will be made (0 beg or 1 end). (can typically assume 0)

purpose of cumipmt function

to calculate the sum of multiple interest payments

difference between linear and growth trend

linear trends grow by a constant amount while growth grow by a constant percentage

interpolation

can fill in the gaps between a starting and end value

how to interpolate

select range » home » fill » series

extrapolation

predict subsequent numbers by setting a step value for autofill to follow

difference between interpolation and extrapolation

interpolation needs both and end and starting value but extrapolation only needs a starting and a step value.

tangible assets

non-cash assets such as equipment, land, buildings, and vehicles

depreciation

the process of a tangible asset losing value over its usable lifespan

what are the 4 things you need to calculate the depreciation of an asset

asset’s original cost, length of the asset’s useful life, the rate at which it depreciates, and the asset’s salvage value

there are 5 ways to depreciate an asset in excel. what do we focus on in this class?

straight line depreciation and declining balance depreciation

straight line depreciation

the asset loses value by equal amounts each year until it reaches end of useful life

declining balance depreciation

asset depreciates by constant percentage each year

how might the depreciation cost vary when using the double declining balance method

the depcretion value will be high in the beginning but decrease in subsequent years. this is an example of a negative growth trend

in which depreciation method does the asset decpreciate more quickly?

double declining balance

income statement/ profit-loss statement

shows a business’s income and expenses over a specified period of time

what are the three main sections of the income statement

income, expenses, earnings

operating profit

profit after accounting for depreciation of assets

pre-tax profit

profit after accounting for interest expenses

after-tax profit

profit after accounting for tax liability

primary purpose of the rate function

to evaluate the return from investments when you know the pv, fv, pmt, and nper.

payback period

a quick method of assessing long-term value of an investment

drawback to the payback period

does not take into account the time value of money

time value of money

based on the observation that money received today is worth more than later

what should a financial workbook accomplish

communicate key pieces of info and be editable to perform what-if functions

tips for creating a successful financial workbook

• place key financial variable such as interest rate at top of ws

• use defined names for variables to allow ease of use in a formula

• identify direction of cashflow with pos or neg signs

• place the arguments for a financial formula in a different cell

• use the same unit of time for all arguments in a financial function

difference between pv and npv

pv function assumes that all future payments are equal while the npv does not

what is one super important thing you should remember about the npv function

it does not actually calculate the net present value. it calculates the present value based on the returns the investment will provide in the future

how to calculate net present value

= initial investment + npv function

internal rate of return

the point at which the npv of an investment = 0

when would you use xnpv of xirr instead of npv and irr

when the cash flows occur at irregular intervalsw

watch window

a window that displays values of cells located throughout the workbook. changing a single cell’s value will allow you to see the effect on other cells via the watch window

how to add a watch window

formulas » formula auditing » watch window

cost-volume-profit analysis or break-even analysis

studies the relationship between expenses, sales volume, and profit

three major types of expenses

variable, mixed, fixed

break-even point

the point at which revenue and total expenses cross (when sales volume causes them to be =)

data table

an excel table that displays the results from several what-if analysis

one-variable data table

have one input/one unit sold to determine revenue

two-variable data table

view the relationship between two input cells and one result cell

benefits of scenario manager

allows you to view multiple what-if possibilities without deleting data

how to switch from one scenario to another

click on show in scenario manager dialogue box

editing a scenario

click what-if analysis » scenario manager » click on scenario » edit

two ways to summarize scenario manager

pivot table scenario report or a summary report

product mix

the combo of products a company offers

what does excel do when it encounters a date field that spans more than one year?

automatically groups the date values into quarters, months, and years.

examples of calculated fields in the textbook

ADR and revpar (revenue per occupied room)

database functions

another way to summarize data from an excel table or data range without using pivot tables

what is expected to happen to cumulative interest each year?

it should increase at a decreasing rate

difference between irr and npv arguments

npv doesn’t include initial value whereas irr does

t/f in an amortization schedule the payment amount should be the same every time

true. payment remains the same for each period but the principal and interest comprising that payment will change

what should happen to interest payments in an amortization schedule?

interest payments will decrease

what should happen to principal payments in an amortization schedule?

they should increase each period

explain how to interpret irr

you want a high irr so if your desired irr is 10 and your projected actual irr is 10.5 you should make the investment.

how to interpret irr and npv together

a negative npv value means you are losing money on the investment because your irr is not high enough. you should not make this investment