CGS Final Exam

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

*17

*

Type

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

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 -Answer report includes detailed information about the objective cell and variable cells -Binding status indicates that the constraint has a final value that is equal to value of constraint, whereas not binding status indicates the constraint does not have a final value that is equal to the value of the constraint. -The slack column indicates the difference between the final value and the constraint.

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 only have one input cell and can have many result cells -Two variable data tables have two input cells, but only one result cell

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

-Open the Solver Parameters dialog box by clicking the Solver button in the Analysis group on the data tab -Click load/save button, select an empty range in the worksheet with the number of cells listed in the dialog box -Click the Save button -Click the close button to close the Solver Parameters dialog box

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

-Pivot charts are dynamic, interactive, and can be pivoted

List and describe the four areas of a PivotTable report.

-Report Filter- display data in the pivot table report grouped by the equivalent pages -Column Labels- displays area from that field in columns -Row Labels- displays data from that field in rows -Values- summarizes data from that field

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) -Start_date is the date from which you want to start counting, -End_date is the ending date, -Basis is the argument that lets you establish the lengths of the months and years. Using 1 for being the basis calculates months and years with actual values.

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(s) that solver uses to produce the desired results in the objective cell. -the constraint(s) that limit how to solve the problem

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

1) May cause errors within excel 2) By using the TRIM function

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 has a useful life of 5 years and has an estimated salvage value of $5,000.

=-(SLN(150000,5000,5))

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

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

Suppose that you are investing $3000 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 plan will be worth in 5 years.

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

Write a formula in cell C2 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,$A$6:$E$7,2,TRUE)

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.

=HLOOKUP(C13,Delivery!$A$2:$C$3,2,TRUE)

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% interest compounded monthly. Your monthly payments are $400. Write an excel formula to determine the number of years it will take to pay off this loan.

=NPER(.055/12,-400,21500,0)

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 period. Assume that you will place this money in a CD that pays 2% interest compounded quarterly and you will be making no additional deposits into this account.

=PV(.02/4,2*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 $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,0)

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

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

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

A simulation can show the results of the interactions of a number of variables, analyzing which results are more likely than others.

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

An infeasible solution results when solver cannot produce a combination of decision variables that satisfies all of the constraints. Steps: correct any data-entry, review the feasibility report for constraints with a violated status and review the constraints to see if a policy constraint is the problem.

What is an assignment problem?

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

Compare break even analysis and conceptual analysis.

Break-even analysis- is a type of what-if analysis that concentrates on an activity at or around the point at which a product breaks even-in other words, the point at which the expenses of creating and selling the product are equal to the revenue it produces, or where the profit is $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 assumptions.

How does Excel store date and time values?

Excel uses serial numbers to represent dates and time. For dates, Excel represents it as the number of days since January 0, 1900. Therefore January 1, 1900 would be represented by the serial number 1. Times are represented by decimals. Noon on January 1, 1900 would be represented by the serial number 1.5.

What are the limitations of using goal seek?

Goal seek is limited to one input and one outcome.

Which excel function should you use when you want to lookup a value from a two-dimensional table where both columns and rows are varied?

INDEX

How does the binary comparison operator work?

It restricts the value to either 0 or 1.

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

It will allow you to organize and present the results of multiple what-if analyses because you can compare the results of many calculations in the data tables.

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

MATCH

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

One set of input values is the iterations, one set of data is the input values, and the intersecting cell between the two must reference a function on the worksheet.

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

Policy constraint is based on company history and to some extent, inertia. Ex. a constraint that only fully loaded trucks can make deliveries. Ex. (2) restricting the number of items that a customer can buy at a sales price. Physical constraint (limit)-physical limitations. ex. something as simple as the size of a truck or available inventory.

What are 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.

List and describe the differences and similarities between HTML and XML.

Similarities: both use tags. HTML uses tags to show how to format data such as <b></b>to show to bold, where XML uses tags to designate structure. Differences: HTML tags do not describe the content and are defined by a standard body that is fairly slow to change, while XML tags allow different users to share both data and meaning in a nonproprietary format by combining data with metadata, which is the data that describers other data such as <shoe></shoe>/

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

Solver can change the values in up to 200 cells.

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

The FIND function returns the starting position of one text value and it is case-sensitive. The SEARCH function does the same thing as FIND but it is not case sensitive.

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 sets 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 A3:E4 is sorted in descending order, whereas the lookup table A6:E7 is sorted in ascending order, which allows you to use the HLOOKUP function with a TRUE lookup type.

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

The query wizard lets you choose the data source and choose the database table and fields to import into a workbook. It asks to define any criteria for the data by selecting the row to meet those criteria. Use the query wizard when trying to select specific data prior to importing it into Excel.

Why are defined names important when you create scenarios?

They can help make the process of creating scenarios easier. The more descriptive the names are, the easier it is to understand and read the scenario reports.

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.

You create a scenario instead of a data table when you want to perform a what-if-analysis with more than two input cells. You would want to create a scenario in a situation where you are comparing three break even analyses (worst case break even analysis most likely break even analysis, and best case break-even analysis)

When should you use a PivotTable report to analyze data?

You should use a PivotTable report to analyze data when you need to summarize and analyze data from a different perspective such as by month, quarter, and year. A pivot table allows the data to be easily rearranged to achieve such analysis.

What is an objective function?

a mathematical formula that relates the decision variables or variable cells to the desired outcome.

What is an unbounded solution?

a solution in which the feasible solution is unrestrained or unlimited on some dimension.

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

a) identify the character that separates (delimits) the data. In comma-delimited files, commas separate the data. Another common way is the tab character. b) set field widths to identify the breaks between data that appears in columns.

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

a) open a workbook where you want to import the database data b) select the first blank cell where you want the data to appear, and then click the From Access button in the Get External Data group on the Data tab on the Ribbon. The Select Data Source dialogue box opens.

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

a) open worksheet b) click developer tab, select Source button in XML group, which opens the XML source task pane c) click XML Maps button d) click add button e) find and open XML doc f) if no schema is referred to, click the OK button in message dialog box.

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

a) open worksheet for data to be imported b) select blank cell, click "From Other Sources" button in the "Get External Data Group" on the data table on the Ribbon, and select XML data import. c) find, select, and open XML data import. d) click okay when excel notifies you about opening a document with a created schema. e) click option and select the worksheet desired for import.

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

a) sort A to Z in ascending order b) sort Z to - sorts in descending order c) sort by color-if cells are manually or conditionally changed to have background color, it filters these colors d) clear filter from- removes any filters and restores original data e) text filters- filters alphanumeric text by specific characters f) number filters g) date filters- filters by date and time using comparison operators h) custom filter- all filters include option to filter custom

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

advantage: it creates summary reports that quickly organize data into categories with subtotal calculations and allows you to collage and expand the level of detail in a report. disadvantage: it works only with one category and one subtotal calculation at a time

SLN

calculates straight line depreciation based on the initial capital investment, number of years depreciated, and salvage value.

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

equal to (=), less than or equal to (>=), integer (int), binary (bin), and different (dif)

Reference and lookup functions may not be nested functions as arguments?

false

The _lookup value of a VLOOKUP function can be a contiguous cell range?

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 result_vector of a LOOKUP function must be sorted in ascending order?

false

PMT

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

IMPT

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

PPMT

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

CUMIPMT

function to calculate the cumulative interest paid between two periods

Rate

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

NPER

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

PV

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

FV

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

IRR

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

NPV

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

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

it ensures that your data is secured and protected but can be analyzed using a spreadsheet easily, this reducing redundancy.

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

linear function: results when a decision variable is multiplied by a constant, such a price. (straight line graph) non linear function: a function that cannot be described in linear terms (not straight graph)

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

What is a scenario in solver?

saves the result of a Solver model so that you can load it later.

Describe how the SUMPRODUCT function works.

sums a series of products in ranges of identical sizes, called arrays that are arranged in the same orientation to each other in a worksheet

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

the LOOKUP function looks up values anywhere in a table whereas the VLOOKUP and HLOOKUP can only retrieve data from tables with vertical and horizontal orientation, respectively.

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

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

What is the advantage of linking the contains in the Solver Parameters dialog box to the 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.

Simple Interest

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

Compound Interest

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

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 a lookup table must be in ascending order to retrieve the correct value?

true

The default range _lookup type for the VLOOKUP and HLOOKUP functions is true?

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

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

true

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

use the CONCATENATE, TRIM, RIGHT, and FIND functions to combine multiple text strings into a single text string, to trim unnecessary spaces from a text string, to find and extract characters from a text string, and to find specific characters in a text string.

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 to be expressed as a whole number. The disadvantage of using the integer is that it significantly adds to the computational complexity of the Solver Model which makes the Solver Model run noticeable slower.