CGS 2518 Final, Ch. 5 - 9, FSU

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 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 report- most frequently used and most useful, sensitivity report and limits report- mostly used to analyze the mathematical results of a Solver model ii) not available when the model has integer constraints iii) a binding status indicates that the constraint has a final value that is equal to the value of the constraint i.e. slack is 0, a not binding status indicates that the constraint does not have a final value that is equal to the value of the constraint iv) slack is considered unused resources and when shown in a sensitivity report, show how many resources there were unused because there was not enough to produce another unit

The delivery charges used in the Delivery worksheet are as follows: *For orders under $10, there is a $6 delivery fee *For orders at least $10 but less than $40, there is a $7.50 delivery fee *For orders over $40, delivery is free of charge Create a lookup a table in the Delivery worksheet, so that you can use a lookup function to calculate the delivery cost for each order. Organize the table in a horizontal format, as shown here...

....

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

1. Click the DATA tab on the ribbon and then file from text 2. Find your text 3. Make sure it is delimited 4. Select which delimiters you will use (commas) 5. Select the data format 6. Select how you want to view the data and where you want to put it

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

1. Display developer tab 2. open worksheet 3. select XML source task pane 4. Click XML Maps button 5. Click add button 6. Find document and select it 7. make it into a schema 8. click OK button

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

1. Open workbook where you want to import database 2. Select first blank cell you want data to appear, click from Access button in the get external data group on the data tab on the ribbon 3. Find the table you want to import and click okay

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

1. Open worksheet 2. select first blank cell and select data from XML Data Import 3. Find your folder 4. Click on the XML document (create a schema) 5. click ok button 6. select new or existing worksheet

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

1. Sort A to Z- sorts data in ascending order 2. Sort Z to A- sorts data in descending order 3. Sort by Color- sorts by colors of background or text 4. Clear Filter From- Removes all filters and displays all the data 5. Text Filters- Filters alphanumeric text by specific characters and using comparison operators 6. Number Filters- Filters numeric values using comparison operators, some have the ability to rank numbers 7. Date Filters- Filters dates using comparison operators 8. (Custom)- Allows you to customize criteria

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 $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 you savings will be worth in five years.

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

As shown in the Grades worksheet, final grades are determined using the following grading scheme: • Students earning over 900 points receive an A. • Students earning less than 900 points but at least 800 points receive a B. • Students earning less than 800 points but at least 700 points receive a C. • Students earning less than 700 points but at least 600 points receive a D. •Students earning less than 600 points receive an F. Write a formula in cell C3 in the Scores worksheet that determines the final grade for the first student based on the grading scheme. Use the appropriate Reference and Lookup function and write the formula so that it can be copied down the column.

=HLOOKUP(B3,Grades!A$6:E$7,2,TRUE)

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

=MATCH

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,0)/12

Consider a $150,000 mortgage at 5% annual interest compounded monthly, to be paid back over the next 30 years. the loan will have $5,00 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,4*3,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 $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 ban be depreciated each year, using straight line depreciation, for a new packaging machine purchased by your company. The machine originally cost $150,000 and has a useful life of 5 years and an estimated salvage value of $5,000.

=SLN(150000,5000,5)

Write a formula in cell D13 in the Pricing worksheet to look up the correct delivery cost using the lookup table you created in Question 17. Write the formula so that it can be copied down the column.

=VLOOKUP(A2,$A3:$B3,3,True)

Write a formula in cell C13 in the Pricing worksheet to determine the total cost of making copies for this order (275 copies). Write the formula so that it works when copied into cells C14:C15

=VLOOKUP(B13,$B$2:$C$7,2,TRUE)*B13

What is an objective function?

A mathematical formula that relates the variable cells to the desired outcome

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

Advantage: allows you to quickly organize data into categories that can be expanded and contracted to show more or less detail. Disadvantage: one category and one subtotal calculation at a time.

What is an assignment problem?

An assignment problem is 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 occurs in situations in which the feasible solution is unrestrained or unlimited on some dimension.

How does Excel store date and time values?

As serial number where day/month/year are a whole number and hour/minute/second are a decimals.

Compare break-even analysis and sensitivity analysis

Break-even analysis: type of what-if analysis that concentrates at or around the point at which a product breaks-even Sensitivity Analysis: Another type of what-if analysis that attempts to examine how sensitive the result of an analysis are to changes in the assumptions.

Function to calculate the cumulative interest paid between two periods

CUMPMT

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 result of multiple what-if analyses because you can compare the results of many calculations in those data tables.

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 TRUE

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 contiguous cell range

False

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

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

IPMT

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

IRR

Why are defined names important when you create scenarios?

It can help make the process of creating scenarios easier. The more descriptive the names are the easier it is to read reports.

What are the limitations of using Goal Seek?

It is limited to changing values in a single cell to reach a goal in another related cell

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

It is when Solver can't produce a combination of decision variables that satisfies all of the constraints; the steps to troubleshoot an infeasible solution are: fix any data-entry errors, review the feasibility report for constraints with a violated status, review the constraints to see if a policy constraint is the problem

What is a scenario in Solver?

It saves the result of a Solver model so that it can be loaded later.

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 (int), binary (bin), and different (dif)

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

Lets you choose your data source and select the database table and fields you want to import into a workbook.

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

Linear function results when a decision variable is multiplied by a constant, when plotted on a chart the result is a straight line; a nonlinear function is a function that can't be described in linear terms, graphed on two dimensions and results in a curve.

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

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

One set must be a sequence of numbers that represents the number of iterations. The remaining blank cell between the intersection of the input values and the iterations must be a reference to a function on the worksheet.

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 data tables have only one input cell and can have many result cells. They can be used to see how different interest rates affect a loan payment. Two-variable data tables have two input cells, but only one result cell. Can be used to see how two factors, different interest rates and loan terms, affect a loan payment.

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 is based on company history and to some extent inertia. An example of policy constraint is a constraint that only fully loaded trucks can make deliveries. A physical constraint is physical limitations. An example of a physical constraint is something as simple as the size of a truck or available inventory.

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

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

SLN

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

Scenario summary. Scenario PivotTable shows results only.

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

Set up the structure for the data table. Instruct Excel about how the data table's structure relates to the input section of the worksheet.

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

Simple Interest

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

Summarizes data from a PivotTable and changes with the filters of the table

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

TYPE

Describe how the SUMPRODCUT function works.

The SUMPRODUCT function multiplies a series of products in ranges of identical sizes, called arrays that are arranged in the same orientation to each other in a worksheet, and then adds them all up.

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

The arguments are start_date,end_date, and basis of the type of day count to use. Calculates years and decimal fraction of the year between the start and end date.

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

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

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

The first row and column of the table contain the two set of values that are the two input variables.

Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet and the lookup table in cells A6:E7 in the same worksheet.

The lookup table in A3:E4 of the Grades worksheet is sorted in descending order, and the lookup table in cells A6:E7 is sorted in ascending order, which allows you to use the HLOOKUP function with a TRUE lookup type.

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

The objective cell that you want to maximize/minimize/set to a specific value, the variable cell that Solver uses to produce the desired results in the objective cell, the constraint that limit how to solve the problem

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

The values in the constraints table can be changed as necessary without having to edit them individually in the Change Constraint dialog box.

List and describe the differences and similarities between HTML and XML

They are both markup languages but HTML is used to write websites and XML is a language that can be automatically read by computers everywhere to interpret data consistently

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

They are used to return the location of a character within a text string. FIND is case sensitive, SEARCH is not.

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

They can create sorting problems. Use the TRIM function.

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

They can hold much larger and more complex data sets than Excel, cannot be accidentally changed

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 in a 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,40,50},0) returns the value 3.

True

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

Up to 200

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

Use a simulation to base a what-if analysis on realistic, but not actual, data. For example, you can run a simulation to determine how probable it is to sell 1000 products at various prices.

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

VLOOKUP searches for a value in the leftmost column of a 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 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.

When should you use a PivotTable report to analyze data?

When you have data lots of complicated data and want it to be summarized to a few details.

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. An example may be if you wanted to compare three break-even analyses.

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

You can parse text using a delimiter that indicates a natural separation of the data. Spaces, commas, and tabs are common delimiters.

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

You should include integer constraints in a Solver Model when you need a value in the Solver model to be expressed as a whole number. The disadvantage of using this constraint is that it significantly adds to the computational complexity of the Solver Model, causing the Solver Model to run noticeably slower.

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

i) Open the Solver Parameters dialog box by clicking the Solver button in the Analysis group on the data tab ii) Click load/save button iii) Select an empty range in the worksheet with the number of cells listed in the dialog box iv) Click the save button v) Click the close button to close the Solver Parameters dialog box. Protects the information that is provided from being lost/deleted

List and describe the four areas of a PivotTable report.

report filter, column labels, row labels, and values.

How does the binary comparison operator work?

t restricts the value to either 0 or 1.