Spreadsheets for Business

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")

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

Both the first column and first row of a two-variable data table contain input values. A two-variable data table has two lists of input values and one formula that refers to the two different input cells.

Function to calculate the cumulative principal paid between two periods

CUMPRINC

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

IPMT

Function that determines the rate of return where the net present value of the cash flows is 0

IRR

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

SLN

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

Some other examples include stock quotes, class schedules with enrollment information, and most any web site with data arranged in a tabular (row and column) format that can easily be used as part of a spreadsheet.

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 VLOOKUP function returns a #N/A

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

True.

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

XML adds meaning to content by providing a description of the data through user-defined elements and a hierarchical structure that shows the relationship of one data element to another. At first, this system might seem very simple and obvious, but much like a language, the real power isn't in the ability to write and speak—it is in the ability to have others read and understand what you say. The potential of XML to transform business lies in the ability for business partners to share both data and meaning in a nonproprietary format. This last point is important because XML is quickly becoming a common output format for many enterprise data systems. As a result, native support of XML makes it possible for Excel to act as a front-end system for these larger data systems. For example, an employee might use Excel to manage travel receipts and then use XML to submit the travel claim information in the format required by the company for processing. It is a good idea to use XML data when the data is used by a variety of systems that can read and process data stored in this format.

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. You can also parse text using a fixed number of spaces between cell content.

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

You can use as many as 200 variable cells.

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 use data tables when you want to conduct a what-if analysis involving one or two input cells, but use a scenario when you want to see the results of changes in more than two input cells. For example, you could use a scenario to compare three break-even analyses: a worst-case break-even analysis that shows the effects of lowering the unit price, decreasing projected unit sales, and increasing cost of goods sold per unit; a likely break-even analysis that shows the effects of maintaining the current unit price and cost of goods sold per unit, and projecting expected unit sales; and a best-case break-even analysis that shows the effects of increasing the unit price, exceeding units sales expectations, and decreasing cost of goods sold per unit.

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

RATE

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

The subtotal command allows you to quickly organize data into categories that can be expanded and contracted to show more and less detail, as needed. One limitation of the Subtotal command is that it works only with one category and one subtotal calculation at a time.

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 a new workbook. 2. 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 dialog box opens. 3. Navigate to the correct Document Library, select the database from which you will import the data, and then click the Open button. If the database you select contains more than one table, the Select Table dialog box opens. Click the table that contains the data you want to import, and then click the OK button. The Import Data dialog box opens. 4. Click the Existing worksheet option button (if necessary), and then click the OK button.

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

1. Open the worksheet into which you want to import the data. 2. Click the Developer tab on the Ribbon, and then select the Source button in the XML group to open the XML Source task pane. You can also open the XML Source task pane by right-clicking any cell in an existing XML table, selecting XML on the shortcut menu, and then clicking XML Source on the shortcut menu. 3. Click the XML Maps button on the XML Source task pane. The XML Maps dialog box opens. 4. Click the Add button. The Select XML Source dialog box opens. 5. Navigate to the folder that contains the XML document, click the XML document to select it, and then click the Open button. 6. If the XML document 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 the OK button to acknowledge the message and close the message box. 7. Click the OK button in the XML Maps dialog box.

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 the data. 2. Select the first blank cell where you want the imported data to appear, click the From Other Sources button in the Get External Data group on the Data tab on the Ribbon, and then select From XML Data Import. The Select Data Source dialog box opens. 3. Browse to the folder that contains the XML document you want to import. 4. Click the XML document, and then click the Open button. If the XML document 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. 5. Click the OK button to close the message box. The Import Data dialog box opens. 6. Click the option button to import the data in the existing worksheet or in a new worksheet, and then click the OK button to import the data.

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

1. To save a Solver model, click the Solver button in the Analysis group on the Data tab on the Ribbon. The Solver Parameters dialog box opens. 2. Click the Load/Save button in the Solver Parameters dialog box. The Load/Save Model dialog box opens. 3. Select an empty range in the worksheet with the number of cells listed in the dialog box. 4. Click the Save button to close the Load/Save Model dialog box and to save the model in the worksheet. 5. Click the Close button to close the Solver Parameters dialog box. Saving a Solver model is different from saving a Solver scenario, which saves only the result of a Solver model. Saving a Solver model saves the Solver parameters that were used in the Solver model so you can load them later if necessary.

Assume that you are investing $6000 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,4*5,-200,-6000)

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,Delivery!B$1:D$2,2)

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(B2,Grades!A$5:E$6,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 payment is $370. Write an Excel formula to determine the number of years it will take you to pay off this loan.

=NPER(.06/12,-370,23500-3000)/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)

Assume that you have been left an inheritance and would like to save part of 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)

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)*12

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 estimated salvage value of $10,000.

=SLN(250000,10000,10)

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

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

A linear function results when a decision variable is multiplied by a constant, such as price. When you plot a linear function in a chart, the result is a straight line, thus the term linear. When you cannot describe a function in linear terms, it is called a nonlinear function. When you graph a nonlinear function on two dimensions, it results in a curve, not a straight line. An example of a nonlinear function is the relationship between the price of an item and the demand for that item. As the price of the item increases, the demand for the item might decrease. Because demand varies as a function of price, calculating the revenue (price multiplied by demand) for an item means that you are multiplying price by a function of price. The result is a nonlinear function or a curve.

What is an objective function?

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

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?

A one-variable data table has only one input cell but can have many result cells. Use a one-variable data table to see how different interest rates affect a loan payment, for example. In contrast, a two-variable data table has two input cells, but only one result cell. Use a two-variable data table to see how two factors—different interest rates and loan terms—affect a loan payment, for example.

What is a scenario in Solver?

A scenario saves the result of a Solver model so that you can load it later. Scenarios are useful when you need to refer to the result of a previous Solver model so you can compare it with another model's results.

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

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

Both HTML and XML are markup languages that use a set of tags to distinguish different elements in a document. In HTML the tags are used to describe how to format the document content. In XML the tags are used to describe the structure and content of a document.

Compare break-even analysis and sensitivity analysis.

Break-even analysis is a type of what-if analysis that concentrates on an activity at or around the point at which the expenses of creating and selling a 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 the assumptions. For example, a sensitivity analysis asks, how much will projected profits change if the cost of goods sold per unit changes by $1? By $2?

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

Compound Interest

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

Constraints are usually the source of the problem when Solver cannot find a feasible solution. Constraints are the boundaries that define the feasible solution area. An infeasible solution results when Solver cannot produce a combination of decision variables that satisfies all of the constraints. When this situation occurs, Solver displays a message to tell you that it could not find a feasible solution. In this case, you should keep the Solver solution, select the Feasibility Report option, and then examine the Solver model and the constraints to determine the reason for the infeasible solution.

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.

Create a lookup table for 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. A B C D 1 Order Value 0 20 50 2 Delivery Charge 5 8 0 3 4

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

Creating a constraints table in the worksheet—with the description of the constraint, the comparison operator used, and the constraint value entered into a cell—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 and scroll the constraints to view them.

Why are defined names important when you create scenarios?

Defining names for changing cells can make creating the scenarios clearer and easier to use. Descriptive names for result cells can also make scenario reports easier to use and understand.

How does Excel store date and time values?

Excel uses serial numbers to represent dates and times. Excel represents a date as the number of days since January 0, 1900: January 1, 1900 is represented as 1; January 2, 1900 is represented as 2 ; and so on. Excel also uses decimals to represent the time on a particular day. Each day is broken into decimal equivalents. For example, noon on January 1, 1900 is represented by the serial number 1.5; the whole number 1 is one day from January 0, 1900, and the decimal .5 represents noon (halfway through a 24-hour day, or 12 divided by 24). Excel further breaks down days into hours, minutes, and seconds by dividing the 24 hours in each day accordingly. Using serial numbers makes calculations involving dates and times very easy. To calculate the number of days between the dates in two cells, you just need to subtract one cell from the other.

Function to calculate value of 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 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.

What are the limitations of using Goal Seek?

Goal Seek can only be used to change the value in a single changing cell and target cell.

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

Having a cell in the worksheet that contains the constraint value makes it possible for you to refer to each constraint value as a cell reference in the Add Constraint dialog box. You can use cell references in the Constraint box of the Add Constraint dialog box instead of entering a specific value. Changing a constraint value is much easier with this approach because you can change the values in the constraints table as necessary without having to edit them individually in the Change Constraint dialog box.

What is an unbounded solution?

If you try to solve a problem in which there are no constraints or too few constraints, the solution is unbounded. An unbounded solution occurs in situations in which the feasible solution is unrestrained or unlimited on some dimension. In the case of a maximization problem, Solver can increase the values in the changing cells and drive the target cell higher.

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

Importing information from a database table into a spreadsheet has some clear advantages. In a database, the data is protected from accidental changes, yet it is available for export into a spreadsheet for analysis. Database programs are also able to hold much larger and more complex data sets than Excel.

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

Integer constraints can be used when the values of the cells being constrained are whole numbers. That said, you shouldn't automatically add these constraints. Using the integer constraint significantly adds to the computational complexity of a Solver model; very large Solver models run noticeably slower when they include integer constraints because Solver uses a different solution method (called integer programming) when a model includes integer constraints. When creating a Solver model, it is a good idea to run the model without an integer constraint first, and then add it later if necessary.

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

Microsoft Query lets you create queries to select data from external sources, including Access, and import the query results into a worksheet. A simpler way to use Microsoft Query is to use the Query Wizard, which lets you choose your data source and select the database table and fields you want to import into a workbook. 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.

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

NPER

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

NPV

Function to calculate periodic payments in 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

When should you use a PivotTable report to analyze data?

PivotTables are best used to analyze data that can be summarized in multiple ways. One example is sales data that can be summarized by a variety of dates, product categories, sales region, etc.

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

Policy constraints are often based on company history and, to some extent, inertia. One example of the difference between these constraint types might be a transportation problem that determines the optimal route for delivery trucks to use on their routes. The size of the truck is a physical limit; a constraint that only fully loaded trucks make deliveries is an example of a policy limit. Another example is restricting the number of gallons of milk that a customer can buy at a sale price (a policy limit) versus the actual inventory of milk available in the store (a physical limit).

List and describe the four areas of a PivotTable report.

Row Labels, Column Labels, Values, and Report Filter.

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

Set up the structure for the data table and 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

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 can produce three different reports: answer, sensitivity, and limits. Of the three reports, the answer report is the most frequently used and most useful report for business users. The sensitivity and limits reports are mostly used to analyze the mathematical results of a Solver model, and are used by more experienced users. In addition, the sensitivity and limits reports are not available when the model has integer constraints. Slack is the difference between the final value of a cell and the constraint limit. When a constraint is binding the slack value will be zero because the final value of the cell equals the constraint. 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 non-binding status indicates that the constraint does not have a final value that is equal to the value of the constraint.

What is the difference between a unstructured list of data and an Excel table?

The Excel table 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.

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

The FIND and SEARCH functions are used to return the location of a character within a text string. The FIND function is case sensitive; the SEARCH function is not.

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

The LOOKUP function returns a corresponding value in a list, while the MATCH function returns the relative position number of the lookup_value in a list.

Describe how the SUMPRODUCT function works.

The SUMPRODUCT function sums a series of products (the result of a multiplication task) in ranges of identical sizes, called arrays that are arranged in the same orientation to each other in a worksheet.

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 the basis of type of day count to use. The function will calculate years and the 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 of cells must be the same size and oriented in the same direction.

How does the binary comparison operator work?

The binary or bin operator restricts the value of a cell to 0 or 1

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

The five comparison operators you can use in Solver are less than or equal to (<=), equal to (=), greater than or equal to (>=), integer (int), binary (bin), and different (dif). The int operator restricts the cell value to a whole number, the bin operator restricts the value to 0 or 1, and the dif operator is a new type of integer constraint used to ensure a permutation of distinct values.

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 in A1:E2 of the Grades worksheet is sorted in descending order, and the lookup table in cells A5:E6 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?

There are three required parameters when running a Solver model: the objective cell that you want to maximize, minimize, or set to a specific value; the variable cell(s) that Solver uses to produce the desired results in the objective cell; and 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?

These spaces can create sorting problems later on. Spaces at the beginning and end of text strings can be removed with the TRIM function.

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

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.

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

Type

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.

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

When setting up a two-variable data table, 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 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. Only one spot remains in the table's structure for a defined set of input values.

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

When you change the values in a data table, it shows you how those changes affect the results of the formulas. Because you can compare the results of many calculations in data tables, they allow you to organize and present the results of multiple what-if analyses

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

You can use the Scenario Manager to create two types of scenario reports: a scenario summary and a scenario PivotTable. The scenario PivotTable report shows the values of results cells only.

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

• The Sort A to Z option sorts the data in the column in ascending order. • The Sort Z to A option sorts the data in the column in descending order. • The Sort by Color option allows you to filter cells by background or font color. • The Text Filters option filters alphanumeric text by specific characters. Comparison operators include Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom filters. • The Number Filters option filters numeric values using comparison operators such as Equals, Does Not Equal, Greater Than, Less Than, and Between. Some of the more powerful comparisons are the ability to rank the top or bottom number values, and to only show those values that are above or below the average. • The Date Filters option filters date and time values using comparison operators such as Equals, Greater Than, or Less Than a specified date or time. You may also filter by a particular day, month, quarter, or year.

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 the CONCATENATE function to combine the information from each line into one cell. This creates many rows of data, but only some of the cells contain the right combination of data in the correct order. • Use the TRIM function if spaces at the beginning and the end of the data need to be removed. • Use a text function like the RIGHT function to extract a unique feature that can be used to identify the correct rows. • Use the FIND function to indicate which row of data is correct. • Sort the data by the value of the FIND function to group all of the correct rows of data together. • Delete the rows of data that contain the incorrect data. • Use text-to-columns to separate the cells containing data into rows of individual cells.

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

• VLOOKUP and HLOOKUP functions require a continuous range to lookup and retrieve data where the values are matched to the 1st row or column depending upon if it's a vertical or horizontal lookup. The LOOKUP function allow the lookup_vector and result_vector to be noncontiguous even in different orientations as long as the number of items correspond 1st to 1st, 2nd to 2nd,etc. • A LOOKUP function lookup_vector must be in ascending order and if the exact value is not present the greatest value that does not exceed will be chosen, this is also true for a type TRUE V and HLOOKUP. But the latter 2 functions also have a FALSE type that allow for lookups of only exact matches.