FSU CGS 2518 Final

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

1. Click solver button on data tab 2. Click load/save button and select empty range in the worksheet with number of cells listed in dialog box 3. click save and close 4. saves solver parameters used in solver model so you can load them later if needed.

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

The database makes sure the right data is available and protected and the spreadsheet provides analytical power and flexibility

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 cells A5:E6 is not in ascending order and therefore cannot work correctly.

5. 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 needed with no need to edit them individually in the Change Constraint dialog box

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

They have to be the same size, shape, and orientation

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

Use a scenario when you have more than 2 inputs and they vary. Ex: worst case breakeven analysis

8. 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, binary, and different

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

1. A LOOKUP FUNCTION can only be used instead of the other two when the lookup type is TRUE and you want to retrieve a value that is stored to the LEFT of a key data column in a VERTICAL lookup table or ABOVE a key data row in a HORIZONTAL lookup table.

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

1. Answer, sensitivity, and limits 2.included detailed info about the objecting and variable cells and constraints used in the solver model 3.Binding status-indicates that the constain has a final value that is equal to the value of the constraint. Non-binding - indicates that the constraint does not have a final value that is equal to the value of the constraint 4. Slack-amount of resources that haven't been used.

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

1. Determine the format you need and what type of file you have. 2. In the data tab click the from text button and find and select the file you want to import 3. Use the Text import Wizard to import the data from a text file into an excel workbook.

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

1. Easier to change the constraint values 2. Have a reference to each constraint value

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

1. First display developer tab to ribbon 2. Open worksheet and click source button on developer tab 3. Click XML maps button 4. Click add 5. Open XML document

5. In a two variable data table what do the first column and first row contain?

1. First row includes the output formula and input values 2. First column includes all the unites sold and the output formula hidden behind the label

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

1. Infeasible solution - when solver could not find a way to satisfy all of the constraints on the model 2. Correct any data entry errors in the solver

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

1. Lets you choose your data source and select the database table and fields you want to import into a workbook. 2. Would use it when you need to create a query to select data from external sources

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

1. Linear - decision variable is multiplied by a constant and the result is a straight line 2.Nonlinear- can't be described in linear terms, graphed in two dimensions and results in a curve

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

1. One set of input values in the data table must be a sequence of numbers that represent the number of iterations of the simulation 2.Use the row at the top for values you want to input into the simulation model

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

1. Open the workbook that you want to import the database data into 2. Select the first blank cell where you want the data to appear, click the from access button in external data group on the data tab ribbon 3. Open the database from which you will import the data 4. Click the table that contains the data you want to import and then select existing worksheet

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

1. Scenario summary or scenario Pivot Table 2. Scenario summary shows results only

6. 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-removes all filters and displays all 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

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

1. The objective cell - the cell that contains the goal 2. Variable cells-cells that solver changes to reach the goal in the objective cell 3. Constraints-you establish them to restrict the values that are entered into the variable cells

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

1. Use when you want the value in the solver model to be shown as a whole number 2. Adds complexity to the solver which makes it slower.

Describe the steps you would take to import XML

1. open the worksheet that you would want to import the data into 2. select the first black cell, click the from XML data button on data tab 3. Open XML document and import data dialog box will open 4. import data into exiting worksheet

4. What are the two major steps involved in creating a data table in excel?

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

2. How many variable cells can you use in a Solver model

200 cells

20. 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 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,... Saturday

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

19. Assume you are investing $3,000 in a savings plan today and well 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 will be worth in five years.

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

Write a formula in cell C13 in the pricing worksheet to lookup the correct delivery cost using the lookup table you created in question 17. Write the formula so that is can be copied down the column.

=HLOOKUP(B13,$A$2:$D$3,2,TRUE)

Write a formula in cell C3 in the Scores worksheet that determines the final grade forthe first student based on the grading scheme. Use the appropriate Reference andLookup function and write the formula so that it can be copied down the column.

=HLOOKUP(B2,$A$5:$E$6,2,TRUE)

17. 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(5.5%/12,-400,21500,0)

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

15. Assume you have been left an inheritance and want to save part of it toward the purchase of a car upon graduation which is 3 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(2%/4,3*4,0,17000)

16. 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 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 is works when copied into cells C14:C15

=VLOOKUP(B13,$B3:$C9,2,TRUE)*B13

9. What type of what if question can a simulation answer?

A simulation tells you the probability of each result

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

A. FIND function returns the starting position of one text value within another text value (CASE SENSITIVE) B. SEARCH function does the same thing as FIND function, but is NOT case sensitive

13. List and describe the four areas of a PivotTable report.

A. Filters - filter the data in the pivot table report by the values of that field. B. Rows- Display data from that field in rows C. Columns-Display data from that field in columns D. Values- where you place the fields you want to summarize

List and describe the differences and similarities between HTML and XML

A. HTML - markup language that created web pages; uses tags to format its content B. XML - combines the markup power of SGML with the ease of use of HTML; defines the structure and rules for creating markup elements.

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

A. Identify the character that separates the data B. Set field widths to identify the breaks between data that appears in columns; use this fixed width method when the data to parse doesn't include a consistent character the separate the field values.

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

A. The spaces can cause errors in excel formulas B. The TRIM function removes all spaces

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

A. YEARFRAC(start date, end date, basis) B. 0 as basis = calculates months using 30 days and years using 360 days C. 1 as basis = calculates months and years with actual values

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

Advantages:creates summary reports that quickly combine related data together by rows, calculate totals and subtotals and can collapse and expand the level of detail in report Disadvantages: only works with one category and one subtotal calculation at a time, related rows of data must be together, you must sort the data by the chosen column, not available when working with excel table.

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

Allow you to organize and present the results of multiple what if analyses

18. What is an assignment problem?

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

10. How does excel store date and time values?

As Serial Numbers

12. When should you use a PivotTable report to analyze data?

Best used to analyze data that can be summarized in multiple ways

1. Compare breakeven analysis and sensitivity analysis

Breakeven Analysis-concentrates on an activity at or around the point it breaks even Sensitivity Analysis - "What if" analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions

Function to calculate the cumulative interest paid between two periods

CUMIPMT

Reference and lookup functions may not contain nested functions as arguments

FALSE

The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages the value 1 with the values in cells B12 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

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

FV

The delivery charges used in the Delivery worksheet are as follows:

Fill in the chart with the rows equaling >=20, >=50, and 5.00, 8.00, and 0 for the respective prices

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?

INDEX

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

IPMT

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

IRR

7. Why are defined names important when you create scenarios?

It makes the process of creating the scenarios and using them easier

How is a PivotChart different from a normal chart in excel

Its organization is dynamic, it can be pivoted to examine the data from various perspectives by rearranging its structure

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

MATCH

7. What is an objective function?

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

16. Describe how the SUMPRODUCT function works

Multiplies values according to their positions in both ranges and then adds the results

1. What are the limitations of Goal Seek

Must compare the multiple production quantities but goal seek can only find a single answer

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 is displayed in the cell

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

NPER

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

NPV

13. What is an unbounded solution?

Occurs in situations which the feasible solution is unrestrained or unlimited on some dimension; not enough constraints or no constraints

3. 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-only one input cell and can have many results; used to see difference between interest rates Two Variable-two input cells but only one result; used to see how two factors affect a loan

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

PMT

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

PV

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

Policy constraints- based on company history and inertia Physical constraints- constraint that has physical limits Size of truck is physical limit, policy limit is only a fully loaded truck can deliver

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

RATE (L)

19. How does the binary comparison operator work?

Restricts the values to either 0 or 1

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

SIMPLE INTEREST

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

SLN

10. What is a scenario in Solver?

Saves the result of a solver model so that you can load it later; allows you to refer to the result of a previous solver model to compare it to another model's results

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 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 function can be numeric values, Boolean values, or text

TRUE

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

TYPE