CGS Final

What happens when Excel is solving a HLOOKUP formula with a FALSE range_lookup type and does not find an exact match in the lookup table?

#N/A is displayed in the cell

What options are available for parsing data when you use the Convert Text to Columns Wizard?

(1) Identify the character that delimits the data, and (2) set field widths to identify the breaks between the data

List and describe the four areas of a PivotTable report

1. Filters: filters the data by the values of the selected field 2. Rows: Displays all data from that field in rows 3. Columns: Displays all data from that field in columns 4. Values: Where you place fields you want to summerize

List and describe the eight available options when using the Autofilter feature in an Excel table

1. Sort A-Z 2. Sort Z-A 3. Sort by color: Filters by colors the background or font color 4. Clear filter from: Removes any existing filters and displays all data in the column; restores the default (select all) option 5. Text filters: Filters alphanumeric text by specific characters, using comparison operators 6. Number filters: Filters numeric values, using comparison operators 7. Date filters: Filters date and time values using comparison operators 8. Custom: Allows you to customize criteria for filtering data

What are the two major steps involved in creating a data table in Excel?

1. Structure the worksheet so input cells contain values you want to modify 2. Use result cells containing formulas to calculate values you want to analyze

What are the three required parameters of a Solver model, and what do they represent?

1. the objective cell that you want to maximize/minimize/set to a specific value 2. the variable call(s) that solver uses to produce the desired results in the objective cells 3. the constraint(s) that limit how to solve the problem

How many different FSU students have portrayed Chief Osceola?

17

How many different Renegade horses have there been?

6

Write a formula to choose the name of the third day of the week from the list starting up Sunday, Monday, Tuesday...Saturday.

=CHOOSE(3,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

Assume that you are investing $3,000 in a savings plan today and will make additional contributions of $300 per quarter. The plan pays 3% interest per year compounded quarterly at the beginning of each period. Write an Excel formula to determine how much your savings will be worth in five years.

=FV(.03/4,5*4,-300,-3000)

Consider a $150,000 mortgage at 5% annual interest compounded monthly, to be paid back over the next 30 years. The loan will have a $5,000 balloon payment due at the end of the loan. Write an Excel formula to determine the payment that must be made each month on this loan.

=PMT(.05/12,30*12,150000,-5000)

Assume that you have been left an inheritance and want to save part of it toward the purchase of a car upon graduation, which is three years from now. Write an Excel formula to determine the amount of money you need to invest now to have $17,000 at the end of the three-year period. Assume that you will place this money in a CD that pays 2% interest compounded quarterly and that you will be making no additional deposits into this account.

=PV(.02/4,3*4,0,17000)

Write an Excel formula to determine the yearly interest rate being charged by the bank on a $400,000, 20-year mortgage. You make monthly mortgage payment of $3,400, and the value of the loan at the end of 20 years is 0. Interest is compounded monthly.

=RATE(20*12,-3400,400000,0)

Write an Excel formula to determine the amount of money that can be depreciated each year, using straight line depreciation, for a new packaging machine purchased by your company. The machine originally cost $150,000 and had a useful life of 5 years and an estimated salvage value of $5,000.

=SLN(150000,5000,5)

What are the arguments for the YEARFRAC function? What are the possible values for calculating months and years using this function?

=YEARFRAC(start_date,end_date,basis). basis = 1 calculates months and years with actual values. basis = 0 calculates using 30 days

What is an objective function?

A formula that relates the variables to the desired outcome

What is the difference between a linear function and a nonlinear function?

A liner function results when you multiple a decision variable by a constraint, and can be graphed in a straight line. A nonlinear function is a function that cannot be described as linear.

What type of reports can you create for scenarios? Which type shows results only?

A scenario summary and a scenario PivotTable. The scenario PivotTable report shows results only.

What are the advantages and disadvantages of using the Subtotal tool to analyze data?

Advantages are you can create summary reports that combine related data and calculate totals and subtotals. Disadvantages are that it only works with one category and one subtotal calculation at a time.

What is an infeasible solution? What steps can you take to attempt to change an infeasible solution into a feasible solution?

An infeasible solution is a solution that does not satisfy all the constraints. You can attempt to change the solution to be feasible by using a Feasibilty Report to isolate the source of infeasibility.

What is an assignment problem?

An optimization problem in which there is a one-to-one relationship between a resource and an assignment or job.

What is an unbounded solution?

An unbounded solution is a solution when there are no/too few constraints

What are the types of Solver reports? What information is described in an answer report? What is the difference between a binding status and a not binding status? What is slack?

Answer, sensitivity and limits. An answer report describes the solution in detail. Binding status indicates that the constraint has a final value equal to a value of the constraint. Not binding status is the constraint does not have a final value that is equal to the value of the constraint. Slack is the amount of resource that hasn't been used.

What is breed of horse is Renegade?

Appaloosa

How does the binary comparison operator work?

Assigns variable cells a 0 or 1

List and describe the difference and similarities between HTML and XML

Both HTML and XML are markup languages that used on the web. XML is used to structure, store, and transport data whereas HTML is used to display data.

How do the FIND and SEARCH functions work, and how are they different?

Both functions find one text string within another test string. They are the same except the FIND function is case sensitive, SEARCH is not.

Compare break-even analysis and sensitivity analysis

Break-even analysis concentrates on the point the product's profit is $0, whereas sensitivity analysis concentrates on how sensitive the results of the analysis are to changing assumptions.

Function to calculate the cumulative interest paid between two periods

CUMIPMT(rate,nper,pv,start_period,end_period,type)

This type of interest is calculated based on principal and previous interest earned

Compound interest

How does a data table help you perform what-if analysis?

Data tables allow you to organize and present the results of multiple what-if analyses

Why are defined names important when you create scenarios?

Defined names make setting up the scenario easier, and make the scenario report easier to read.

Function to calculate the value at the end of a financial transaction

FV(rate,nper,pmt,[pv],[type])

True or False? Reference and Lookup functions may not contain nested functions as arguments.

False

True or False? The look_up value of a HLOOKUP function can be a contiguous cell range.

False

Function to calculate the amount of a periodic payment that is interest in a given period

IPMT(rate,per,nper,pv,[fv],[type])

Function to determine the rate of return, where the net present value of the cash flows is 0

IRR(values,[guess])

Which Excel function should you use when you want to look up a value from a two-dimensional table, where both the columns and rows can be varied?

Index function

In a two-variable data table, what do the first column and first row contain?

Input values

What are the limitations of using Goal Seek?

It only lets you change one cell

What is a scenario in Solver?

It saves the result of a solver model so that you can load it later

What function returns the relative position of an item from a list?

Match function

Assume that you are buying a car for $25,500 with a $4,000 down payment, and you are borrowing the rest from a bank at 5.5% annual interest compounded monthly. Your monthly payments are $400. Write an Excel formula to determine the number of years it will take you to pay off this loan.

NPER(.055/12,-400,25500,-4000)/12

Function to calculate the number of compounding periods in a financial transaction

NPER(rate,pmt,pv,[fv],[type])

Function to calculate the value of a variable set of cash flows discounted to its present value

NPV(rate,value1,[value2],...)

What is the difference between a one-variable data table and a two-variable data table? When would you use each type of data table?

One-variable tables have only one input cell and can have many result cells, and can be used to see how different interest rates affect a loan payment. Two-variable tables have two input cells but only one result cell, and can be used to see how different interest rates and loan terms would affect a loan.

Function to calculate periodic payments into or out of a financial transaction

PMT(rate,nper,pv,[fv],[type])

Function to calculate the amount of a specific periodic payment that is principal in a given period

PPMT(rate,per,nper,pv,[fv],[type])

Function to calculate the value at the beginning of a a financial transaction

PV(rate,nper,pmt,[fv],[type])

What is the difference between a policy constraint and physical restraint? Give one example of each type of constraint.

Policy constraint is a constraint based off of the company's past history. Ex. restricting how much a customer can buy at a sale price. Physical constraint is a constraint that has physical limitations. Ex amount of product that can fit on a truck

What are the two advantages of creating a constraints table in a worksheet that includes a Solver model?

Provides record of the constraints and allows for cell reference with the solver.

What is the Query Wizard, and when would you use it?

Query Wizard is a simpler way to use Microsoft Query, which lets you create queries to select data from external sources. You would use it when you only want specific data from a database, instead of all of the data in the database.

Function to calculate the interest percentage per period of a financial transaction

RATE(nper,pmt,[fv],[type],[guess])

Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value

SLN(cost,salvage,life)

How does Excel store date and time values?

Serial numbers

This type of interest is calculated based on original principal regardless of the previous interest earned

Simple Interest

What types of what-if question can a simulation answer?

Simulations can answer questions with more than two variables

Why should you remove unnecessary spaces from data imported from another source? How do you remove unnecessary spaces from a text string?

Spaces create errors. Use TRIM function to eliminate these.

Describe how the SUMPRODUCT function works.

Takes one or more arrays or numbers and calculates the sum of products of the corresponding numbers

What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP functions?

The LOOKUP function can retrieve data from a lookup table with a vertical or horizontal orientation, and only uses TRUE lookup type.

What requirements exist for cells in arrays when you use the SUMPRODUCT function?

The arrays must be identical in size, shape, and orientation.

What is the primary advantage of storing data in a database and importing that data into Excel

The database keeps the data available and protected, and Excel provides analytical power.

Explain how you must vary the setup of a two-variable data table to run a simulation via the table.

The setup must be structured so that the input values are perpendicular to each other and the table's output formula is located at the intersection of the two sets of input variables. One set of input values in the data table's structure must be a sequence of numbers that represents the number of iterations of the situation

True or False? Excel matches the lookup_value "tom" with the entry "TOM" in a lookup table.

True

True or False? In a VLOOKUP formula with a TRUE lookup type, the first column of the lookup table referenced must be in ascending order to retrieve the correct value.

True

True or False? The default range_lookup type for VLOOKUP and HLOOKUP functions is TRUE.

True

True or False? The result_vector of a LOOKUP function must be sorted in ascending order.

True

True or False? The row and column arguments in the INDEX function can be numeric values, Boolean values, or text.

True

How many variable cells can you use in a Solver model?

Up to 200

List and describe the steps you would take to create a structured list of data from a text file that contains values stored on separate lines

Use CONCATENATE function to combine the information from each line into a text string in a single cell. Use functions such as TRIM or PROPER to manipulate data in desired way. Then use Convert Text to Columns Wizard to separate values in the text string into columns. Lastly, add a header row and add labels to identify data in each columns and use Sort tool.

When should you include integer constraints in a Solver model? What is the disadvantage of using an integer constraint?

Use integer constraints when you need a value in the solver model to be shown as a whole number. The disadvantage is that is adds complexity and causes the solver to run slower

When should you use a PivotTable report to analyze data?

When you need to analyze data that can be summerized in multiple ways

When should you create a scenario instead of a data table? Give an example of a business situation that could best be analyzed with scenarios.

When you want to perform a what-if analysis with more than two input cells. Ex. Comparing the results of maintaining the price of a product, offering a discount, or increasing the price.

How is a PivotChart different from a normal chart in Excel?

With a PivotChart, you can change the layout and data displayed by moving fields in addition to changing the chart type

What is the advantage of linking the constraints in the Solver Parameters dialog box to values in a constraints table in the worksheet?

You won't need to edit the constraints in the Change Constraint dialog box every time they change

What are the five comparison operators that you can use in Solver?

less than or equal to, equal to, greater than or equal to, integer, binary, and different

Use a 0 for this argument to indicate that interest will be paid at the end of each compounding period

type