CGS Chapters 5-9

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

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

-One-variable: has only one input cell and can have many result cells. Use a one-variable data table to see how different interest rates affect a loan payment -Two-variable: has two input cells, but only one result cell. Use two-variable data table to see how two factors affect a loan payment

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

IPMT

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

IRR

How do the find and search functions work, and how are they different?

The find function returns the starting position of one text value with another text value. The FIND function is case sensitive. The search function does the same thing as the find function, but the search function is not case sensitive

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

The first row includes the output formula and the input values (selling price). The first column includes all the units sold and the output formula hidden behind a label

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

If you have multiple break-even analyses you would much rather use a scenario (a set of values stored in a worksheet that describes different situations). The scenario manager allows you to have different outcomes by placing possible numbers into specific cell fields

What is the primary advantage of sorting data in a database and importing that data into excel?

In a data base, the data is protected from accidental changes, yet is available for export into spreadsheet for analysis

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

Simple interest

How does the binary comparison work?

The binary comparison operator will restrict the values to either 1 or 0

What is an objective function?

A mathematical formula that relates the decision variables or variable cells of the desired outcome

What is an assignment problem?

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

The result_vector of a LOOKUP function must be sorted in ascending order

False

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

False

Describe the steps for saving a solver model. What is the advantage of saving a solver model?

First open up the solver parameters dialog box. Click the Load/Save button to open the Load/Save dialog box. Select an empty range in the worksheet with the number of cells listed in the dialog box. Click the save button. Saving a solver model saves the Solver parameters that were used in the solver model to you can load them later if necessary

List and describe the differences and similarities between HTML and XML

HTML- a markup language that creates web pages. Uses tags to help create browsers XML- a fairly recent innovation created to help lead world wide web to its full potential. Was designed to combine the markup power power to SGML with the ease of use of HTML

List and describe the 4 areas of a Pivot Tables report

-Report Filter Area: you can display data in the Pivot Table report grouped by the equivalent of pages -Row Labels: displays data from that field in rows -Values Area: summarized data from that field -Column Labels Area: displays data from that field in rows

List and describe the six available options when using the AutoFilter feature in an Excel Table

-Sort A to Z: sorts the data in the column ascending -Sort Z to A: sorts the data in the column descending -Sort by Color: if you have manually or conditionally formatted the background or font color of a range of cells, you can filter these by colors -Clean Filter From: removes any existing filters and displays all data in the column -Date Filters: filters date and time values using comparison operators (equals, does not equal, greater than, less than)

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

1) Find out what format you are importing ex. CVS 2) Import the date from the CVS work file onto the excel spread sheet

What are 2 major steps involved in creating a data table in excel?

1) Set up the structure for the data table 2) Instruct excel about how the data table's structure relates to the input section of the worksheet

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, or set to a specified value. 2) The variable cells that Solver uses to produce the desired results in the objective cell. 3) The constraints that limit how solve the problem.

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

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

Assume that you are investing $3000 in a savings plan today and will make additional contributions $300 per quarter. The plan pays 3% interest per year compounded quarterly. How much is it worth in 5 years?

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

Assume you are buying a car for $21,500 with a $4000 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,21500,0)

Consider $150,000 mortgage at annual interest compounded monthly, to be paid back over the next 30 years. The loan will have a $5000 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 towards the purchase of a care 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 the money in a CD that pays 2% interest compounded quarterly and that you will make 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 a monthly mortgage payment of $3400 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 costs $150,000 and has a useful life of 50 years and an estimated salvage value of $5000

=SLN(150000,5000,50)

What are the advantages and disadvantages of using the subtotal tool to analyze date?

Advantage: creates summary reports that quickly organize data into categories with subtotal calculations, and lets you collapse and expand the level of detail in the report Disadvantage: One limitation of the subtotal command is that it works only with one category and one subtotal calculation at a time

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

Allows the business to see the probability of each result. They are based on realistic, but not actual data

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

Binding Status: indicates that the constraint has a final value that is equal to a value of the constraint Not Binding: indicated that the constraint does not have a final value that is equal to the value is the constraint Slack: when the status is not binding the slack column indicates the differences between the final value and the constraint

Compare break-even analysis and sensitivity analysis

Break-even analysis is the type of what-analysis that concentrates on activity at or around the point at which a product breaks even. Where profit is equal to $0. Sensitivity analysis is another type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions

Function to calculate the cumulative interest paid between two periods

CUMIPMT

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 Table is a range of cells containing values and formulas. Data tables allow you to organize and present the results of multiple what-if analysises

Why are defined names important when you create scenarios?

Defined names have a scope, which specifies the location in which the name is valid. It allows you to specify what cells you are working on when utilizing the scenario tool

How does excel store date and time values?

Excel uses serial numbers to represent dates and times

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

FV

Reference and Lookup functions may not contain nested functions as arguments

False

The default range_lookup type for the VLOOKUP and HLOOKUP functions is FALSE

False

The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages the value 1 with the values in cells B12 to D17 and B22 to D17

False

The lookup-value of a HLOOKUP function can be a continuous cell range

False

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

Infeasible solutions happen when Solver cant produce a combination of decision variables that satisfy all the constraints. User must correct all data-entry errors. Review the feasibility report for constraints that have been marked as violated. Review the constraints to see if a policy constraint is the problem

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

Is is a simpler way to use Microsoft Query. The Query Wizard prompts you to define any criteria for the data you want to import by selecting only rows that meet criteria that you specify

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

Less than or equal to <=, equal to =, integer (int), binary (bin), and different (dif)

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

Linear function: when a decision variable is multiplied by a constant. The result is a straight line. Nonlinear function: a function that cannot be described in linear terms. Graphed on two dimensions and results in a curve

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

Match

Describe how the SUMPRODUCT functions work

Multiply the values according to positions in multiple ranges and then add the results to obtain a total

What are the limitations of using Goal Seek>

Must compare the multiple production quantities. Cant find more than single answers using goal seek

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

NPER

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

NPV

What is an unbound solution?

Occurs in situations in which the feasible solution is unrestrained or unlimited on some dimension

Describe the steps you would take to import XML data as an XML table in excel

Open the worksheet into which you wish to import the data -> select the first blank cell you want the data to appear in -> go to Get External Data group and select From XML Data Import -> Find the XML document you wish to import and click Open -> click OK

Explain the steps you must take to import data stored in an Access database into excel

Open work sheet -> click data on the ribbon -> click the From Other Source button -> click From Microsoft Query -> click the MS Access Database -> click OK

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

PMT

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

PPMT

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

PV

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

Policy Constraint: a constraint that is based off the company's past or history. Ex. restricting how much a customer can buy at a sale price. Physical Constraint: a constraint that has physical limitations. Ex. Size of a specific product/machine, or maybe the amount of inventory available

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

Provides an available record of the constraints and cell references. They are a data-entry reference for when Solver is being set up

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

RATE

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

Requires the cells have the same size, shape, and orientation

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

SLN

What is a scenario in Solver?

Saves the result of a Solver model so that you can load it later. Allows you to refer to previous Solver models so you can compare the solver models

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

Scenario Summary Reports & Scenario PivotTable Reports. Scenario Summary only shows the results of the data

How do you import an XML map into a workbook and map its elements into the worksheet?

Select the source button in the XML group to open the XML source task pane -> click the XML maps button on the XML Source task pane -> click the Add button and navigate to the folder that contains the XML document -> click the XML document to select it -> click the Open button

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

The first way is to identify the character that delimits or separates the data. The other way to parse data is to set field widths to identify the breaks between data that appears in the columns

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

The values in the constraints table can be changed as needed with no need to edit them individually in the Change Constraint dialog box

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

This may cause errors on the worksheet. You should remove these using the Trim Function (=trim(text))

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?

To find a value in a two-dimensional table, use the INDEX function

Excel matches the lookup_value "tom" with the entry "tom" in a lookup table

True

In a VLOOKUP formula with a TRUE lookup type, the first column of the lookup table referenced must be ascending order to retrieve the correct value

True

The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value in cell D3

True

The formula =MATCH(40,{10,20,30,40,50},0) returns the value 3

True

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

Type

When should you include integer constraints in a Solver model? What is the disadvantage of using 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 this adds complexity to the solver model, causing the Model to run slower

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

VLOOKUP searches for a value in the leftmost column of table, and then returns a value in the same row from a column you specify in the table. HLOOKUP is similar but it searches a horizontal lookup the table. The LOOKUP function looks for a matching value in a range of cells (vertical or horizontal) that you specify and returns a value. LOOKUP is limited to a single row or column.

How is a Pivot chart different from a normal chart in excel?

What makes a Pivot chart unique is that it is somewhat dynamic. The pivot chart can be "pivoted" to examine the data form various perspectives by rearranging its structure. Unlike any other table, you can change the data contained in a PivotTable report simply by dragging a field to a new location in the PivotTable report

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

When using a two-variable data table to run a simulation, 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 simulation

How many changing variable cells can you use in a solver model?

With solver model you can change the values in as many as 200 cells at one time

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

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

When should you use a PivotTable report to analyze data?

You would generally use a pivot table when you need to look at data from various perspectives. Allows you to "pivot" the table to what info you need to view