CGS Final Exam 2518

How does Excel store date and time values?

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

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

1. Less than or Equal to (<=) 2. Greater Than or Equal to (>=) 3. Equal to (=) 4. Integer (int) 5. Binary (bin) 6. Different (dif)

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

= FV (.05/4, 5*4, -200, -6000, 0)

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

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

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

Analyzing Data by Creating Subtotals - Advantages - Subtotal command 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 - Disadvantages - One limitation is that it works only with one category and one subtotal calculation at a time

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

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

True

The formula =MATCH(40,{10,40,50,90},0) returns the value 2.

True

Metadata

data that describes other data

Schema

set of validation rules for an XML document - When you add a schema to a workbook, Excel generates an XML map to create mapped ranges of data and define the relationships between these ranges and the elements in the XML schema

Markup Language

uses a set of tags to distinguish different elements in a document and uses attributes to define those elements further. - EX: HTML, SGML, XML

When should you use a PivotTable report to analyze data?

• A PivotTable report is an interactive report that lets you summarize and analyze a dataset. • A unique feature is that its organization is dynamic. • You should use a PivotTable report to analyze data when you need a way summarize and analyze data from different perspectives such as by month, quarter, and year. You can easily rearrange its fields and change its data to analyze data from different perspectives. • PivotTables are best used to analyze data that can be summarized in multiple ways.

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 a scenario in Solver?

• Scenario saves the results of a Solver model so that you can load it later if necessary • Scenarios are useful when you need to refer to the results of a previous Solver model so you can compare it with another model's results

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

• You can use cell references in the Constraint box of the Add Constraint dialog box instead of entering a specific value • Changing a constraint cell is much easier with this method because you can change the values in the constraints table as necessary without having to edit them individually in Solver

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

Another way to import XML data is the XML Source task pane: 1. Open the worksheet 2. Click the Developer tab on the Ribbon. Then select "Source" button in the XML group to open the XML Source task pane. 3. Click the XML Maps button on the XML Source task pane. The XML Maps dialog box opens 4. Click the "Add" button. Then select XML Source dialog box will open 5. Navigate to the folder than contains the XML document, clock to select, then click Open a. If the XML doc does not refer to a schema, a message box opens to tell you that Excel will create a schema based on the XML source data. Click OK to acknowledge and dismiss message 6. Click OK button in the XML Maps dialog box

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.

- A common way of sorting data so that it is usable in other programs is to save it in a comma-delimited file. This formats separates the values in each record with commas, data stored in this way is also called comma-separated values, or CSV. - Excel can convert data stored in this file so that each value in a record appears in a separate cell of the worksheet where you import it.

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

- A query is a question that you ask a database and in response, the database displays only those records that meet the criteria you specify in the query. - Query Wizard lets you choose your data source and select the database table and fields you want to import into a workbook. It prompts you to define any criteria for the data you want to import by selecting only rows that meet criteria that you specify. - You want to use a query wizard when you are trying to select specific data prior to importing it into Excel.

What is the difference between an unstructured list and an Excel list?

- All the data in an Excel list are in a structured format, one can use the filter feature to sort through the data using different methods. - The flexibility of Excel lets you rearrange the data and work out an organizational structure - The Excel table also adds many features that aren't available in an unstructured list of data, such as validation, sorting, and filtering; all of which can help you manage any set of related data.

What is the difference between the LOOKUP function and the MATCH function?

- Although the LOOKUP function returns a corresponding value in a list, the MATCH function returns the relative position number of the lookup_value in a list. - MATCH function returns the relative position (such as 1, 2, or 3) of the item in a list

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

- Click the Solver button in the Analysis group on the Data tab on the Ribbon. The Solver Parameters dialog box opens. - Click the Load/Save button in the Solver Parameters dialog box. - Select an empty range in the worksheet with the number of cells listed in the Load/Save Model dialog box - Click the Save button to close and to save the model in the worksheet - Click Close to close the Solver Parameters dialog box • Advantage: Saving a Solver model saves the Solver parameters that were used in the Solver model so you can load them later if necessary

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

- FIND(find_text, within_text, start_num) - Finds one text string within another text string (IS case sensitive) using the start_num character as a starting point - SEARCH(find_text, within_text, start_num) - Finds one text string within another text string (NOT case sensitive)

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

- Incorrect values can be stored because of unnecessary spaces. Use the TRIM function - Removes all spaces in a text string expect for the single spaces between words. - This function can be very useful when importing data from another data source in which the data might contain spaces at the end of values.

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

- Often, the key to business's success is its ability to manage and organize data into a usable format: The database provides the structure to ensure that the right data is available and protected. The spreadsheet provides the analytical power and flexibility needed by most businesspeople. - It is much easier to analyze data on Excel than on other database programs -It ensures that your data is secured and protected but can be easily analyzed using a spreadsheet, thus reducing redundancy

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

- This wizard separates the values in a text string into columns or fields. You can determine how to divide (or parse) the text into columns in two ways: 1. Identify the character that delimits (separates) the data (tab, semicolon, comma, space, other) 2. Set field widths to identify the breaks between data that appears in columns

Sensitivity Analysis

- Type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions. - Example: "How much will projected profits change if the cost of goods sold per unit changes by $1?" - Reveals that sales price has most positive effect on profit and variable costs have the most negative effect on profit

Break-Even Analysis

- Type of what-if analysis that concentrates on an activity around the point at which a product "breaks even" - the point at which the costs of creating and selling the product are equal to the revenue it produces. (Costs = Revenue) - Where the profit is $0.

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

- VLOOKUP function searches a specified part of a worksheet for data starting in the first column. V in Vlookup stands for vertical. - HLOOKUP function is similar to VLOOKUP except that it searches a horizontal lookup table, in which data is stored in rows instead of columns - Unlike VLOOKUP, the LOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range. - LOOKUP also uses a TRUE lookup type, so the column or row containing the lookup values must be in ascending order.

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

- YEARFRAC Function is used to calculate the number of years between two dates as a fraction. → YEARFRAC (start_date, end_date, basis) - start_date - the date from which you want to start counting - end_date - the ending date - basis - the argument that lets you establish the lengths of months and years - Using 1 for the basis calculates months and years with actual values - Using 0 as the basis calculates months using 30 days and years using 360 days

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

1. Open the workbook into which you want to import the database data, or open new workbook 2. Select the first blank cell where you want the data to appear, and then clock "From Access" button in the "Get External Data" group on the Data tab in the Ribbon. The Select Data Source dialog box opens 3. Navigate to the database from which you will import the data, and then clock the Open button. If the database you select contains more than one table, the Select Table dialog box opens. 4. Click the table that contains the data you want to import, and then click the OK button. The Import Data dialog box opens.

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

1. Open the worksheet into which you want to import 2. Select the first blank cell where you want data to appear 3. Click "From Other Sources" button in the "Get External Data" group on the DATA tab in the Ribbon. Then select "From XML Data Import" 4. Browse to the folder that contains the XML document to import 5. Click the XML document, then click Open. a. If the XML doc does not refer to a schema, a message box opens to tell you that Excel will create a schema based on the XML source data. Click OK. The Import Data dialog box opens 6. Click the option button to import the data in the existing worksheet or a new worksheet, and then click the OK button to import the data

What are the two 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

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

200

Write a formula in cell C13 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(B13, $B$1:$D$2, 2, TRUE)

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

=NPER (.06/12, -370, 20500, 0, 0) / 12

Consider a $100,000 mortgage at 5% annual interest compounded monthly, to be paid back over the next 15 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, 15*12, 100000, -5000, 0)

Assume that you have been left an inheritance and want to save part it towards 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 $15,000 at the end of the three-year period. Assume that you will place this money in a CD that pays 3% interest compounded quarterly and that you will be making no additional deposits into this account.

=PV (.03/4, 3*4, 0, 15000, 0)

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

=RATE (30*12, -3000, 375000, 0, 0) * 12

Write a formula in cell B13 in the Pricing worksheet to determine the total cost of making copies for this order (3 copies). Write the formula so that it works when copied into cells B14:B16.

=VLOOKUP(A13, $A$2:$B$7, 2, TRUE) * A13

The delivery charges used in the Delivery worksheet are as follows: - For orders under $20, there is a $5 delivery fee. - For orders at least $20 but less than $50, there is an $8 delivery fee. - For orders over $50, delivery is free of charge.

A B C D 1 Delivery Charges 2 1 $5 3 20 $8 4 50 $0

What is an objective function?

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

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 VLOOKUP 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

SLN

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

IPMT

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

CUMPRINC

Function to calculate the cumulative principal payment 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

PMT

Function to calculate the periodic payments into or out of 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

Hyper Text Markup Language vs. eXtensible Markup Language

Markup language that creates Web pages vs A language that defines the structure and rules for creating markup elements HTML uses tags to describe how to format its content, such as <b></b> vs XML uses tags to designate structure HTML tags do not describe the content are defined by a standard body that is fairly slow to change vs XML tags allow different users to share both data in and meaning in a nonproprietary format by combining data with metadata

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

SLN(250000,10000,10)

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

Sort by Color -If you have manually or conditionally formatted the background or font color of a range of cells, you can filter by these colors Clear Filter From -Removes any existing filters and displays all data in the column; restores the default (Select All) option Text Filters -Filters alphanumeric text by specific characters. Number Filters -Filters numeric values using comparison operators Date Filters -Filters date and time values using comparison operators Custom -All of these filters include the option to filter the data using custom criteria that you specify

Explain the difference between the lookup table in cells A1:E2 of the Grades worksheet and the lookup table in cells A5:E6 in the same worksheet.

The LOOKUP table A1:E2 is sorted in descending order The LOOKUP table in A5:E6 is sorted in ascending order, which allows you to use the HLOOKUP function with a TRUE lookup type

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

The text #N/A is displayed in the cell

Simple Interest

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

Compound Interest

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

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

TYPE

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

What is an unbounded solution?

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

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

• A data table is a range of cells containing values and formulas • Data tables allow you to organize and present the results of various what-if analyses • Because you can compare the results of many calculations in data tables, they can be very useful when performing break-even and sensitivity analyses

Why are defined names important when you create scenarios?

• A defined name is also known as a range name - can refer to a cell, range of cells, formula, or constant value in Excel. • All defined named have a scope which specifies the location in which the name is valid • Using defined names make you're the process of creating the scenarios and can also make scenario reports easier to understand and use

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.

• A scenario is a set of values stored in a worksheet that describes different situations, such as worst-case, likely case, and best-case scenarios. You view and change them as necessary to produce and compare different results • Use the Scenario Manager to perform a what-if analysis with more than two input cells • Example: Business wants to compare a worse-case break-even analysis that shows the effects of lowering unit price and decreasing projected sales to a best-case break-even analysis that shows effects of increasing unit price and exceeding sales expectations

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

• A two-variable data table has two lists of input values and one formula that refers to the two different input cells: • The first row and column of the table contains the formula that refers to the two input cells

What is an assignment problem?

• Assignment Problem - an optimization problem in which there is a one-to-one relationship between a resource and an assignment or job. • For example, one machine in a plant might be assigned to one job. The machine cannot begin a second job until it finishes the first job. Instead of a machine, the resource might be one employee doing one task.

How does the binary comparison operator work?

• Binary Comparison restricts the value to either 0 or 1: - Solver uses the value 1 to award a contract - Solver uses the value 0 to indicate that the contract was not awarded

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?

• Both types have input cells - cells containing values that vary • One-variable data table Has only one input cell and can have many result cells Example: Use to see how different interest rates affect a loan payment • Two-variable data table Has two input cells but only one result cell Example: Use to see how different interest rates and loan terms affect a loan payment

What are the limitations of using Goal Seek?

• Goal Seek cannot compare two production quantities simultaneously • Goal Seek 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?

• Infeasible Solution - results when Solver cannot produce a combination of decision variables that satisfies all of the constraints • Troubleshoot: - Correct any data-entry errors in the Solver Parameters dialog box. Compare the constraints listed in the Solver Parameters box with the constraints table in your worksheet. Verify that cell references are correct. - Review the Feasibility Report for constraints with a violated status. Trying loosening constraint values by the slack value for each problem. - Review the constraints to see if a policy constraint is the cause of the infeasibility. Policy constraints with minimum or maximum limits are a good place to look for causes of infeasibility

Not including currency exchange rate data, what other types of Web queries might be useful to a business using Excel for data analysis?

• Investor Indexes • Stock Quotes • Currency Rates

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

• Linear Function - results when a decision variable is multiplied by a constant, such as price. - When plotted it a chart, the result is a straight line • Nonlinear Function - results when you cannot describe a function in linear terms - When graphed, the result is a curve - Example: Relationship between the price of an item and the demand for that item. As the price of item increases, the demand for the item might decrease.

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

• Objective Cell - that you want to maximize, minimize, or set to specific value • Variable Cell(s) - that Solver uses to produce the desired results in the objective cell • Constraint Cell(s) - that limit how to solve the problem

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

• Organizes the constraints and makes them visible in the worksheet at any time without the limitation of having to open the Solver Parameters dialog box to view them • Advantages of creating a constraints table: - You can have a record of your constraints for later use - Have a data-entry reference when you set up Solver

Describe how the SUBPRODUCT function works.

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

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

• Scenario Manager can create two types of scenario reports: - Scenario Summary - shows the values for all changing cells and for all indicated result cells for every scenario. It makes it easy to compare the results of the scenarios. - Scenario PivotTable - interactive table that groups and summarizes information in a concise format so you can easily analyze the information - I believe Scenario PivotTables show results only

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

• Simulated results are those based on realistic, but not actual, data. (Probable data) • Businesses work with simulated data and results when they are starting a new operation, expanding into a new market, or dealing with another situation in which no real data is available • Example: Business wants to analyze the sales price options for their new swimwear product based on probabilities that demand for the product will range from high to low. Because this is a new product, they use probable sales prices, not historical or current.

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?

• Solver produces three different reports: - Answer Report- most frequently used and most useful - Sensitivity Report - Limits Report • The Answer report includes detailed information about the objective cell, variable cells, and constraints used in the Solver model. • A binding status indicates that the constraint has a final value that is equal to the value of the constraint. • A nonbinding status indicates that the constraint does not have a final value that is equal to the value of the constraint • When a constraint is binding, the slack column is set to 0 because the constraint equals the final value

List and describe the four areas of a PivotTable report.

• The four areas of a PivotTable are: 1. Report Filter Area - display data in the PivotTable report grouped by the equivalent of pages 2. Row Labels Area - displays data from that field in rows 3. Column Labels Area - displays data from that field in columns 4. Values Area - summarizes data from that field. • By default, fields containing numbers will be added to the Values area • Fields with other content will be added to the Row Labels area

What advantages exist for businesses to create ways to import, export, and use XML data in their daily operations?

• The potential of XML to transform business lies in the ability for business partners to share both data and meaning in a nonproprietary format (the ability to have others read and understand what you say) • Some benefits of the XML file format include smaller file sizes (automatically compressed), improved file recovery, safer documents, and easier integration • For very large data sets, Excel provides a very quick and easy way to import and export XML data that can also be used by other applications • It is a free format in which to convert data

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

• When setting up a two-variable data, it must be structured so that the two sets of 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 • When using two-variable data to run 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 • You also need to alter the completion of your table: to correctly perform the simulation, the iteration values are directed to any empty cell in the worksheet.

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

• You can base constraints that limit the value of a particular cell in the Solver model on company policies or on physical limits. • Policy Constraint - limitations based on company history and inertia, such as sale price • Physical Constraint - physical limitations • Example: Restricting the number of milk gallons that a customer can buy at a sale price (a policy limit) verses the actual inventory of milk available in the store (a physical limit)

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

• You should include integer constraints when you need a value in the Solver model to be expressed as a whole number. • Disadvantage: it significantly adds to the computational complexity of the Solver Model, causing Solver Model to run noticeably slower