Excel Test 3

Front 1

Why should you remove unnecessary spaces from data imported from another source?

Back 1

You should remove them because they can cause errors in functions.

Front 2

How do you remove unnecessary spaces from a text string?

Back 2

You remove spaces by using the TRIM() function, and putting the text you want the spaces removed from in the parenthesis

Front 3

How does the FIND function work?

Back 3

The FIND function returns the starting position of one text value witin another text value. Ex: =FIND("-",C1)
Excel will look for a "-" within C1, and the result will display a numerical value. If it is the first value, it will return a 1, 2nd, a 2. and so on

Front 4

What options are available for parsing data when you use the "convert text to columns wizard"?

Back 4

1. Identify the delimiter separating your data (comma, tab, semicolon, space)
2. Identify the breaks between data that appreas in columns and set field widths

Front 5

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

Back 5

It makes data easier to interpret by removing clutter

Front 6

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

Back 6

1. You can only work with one subtotal and one category calculation at a time
2. Data must be sorted by the chosen category for it to properly work
3. Not available if working with an excel table.

Front 7

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

Back 7

An unstructured list refers to data that is not stored within a table on excel. Sorting this data is a bit more difficult, and requires using the Sort&Filter group. An excel list refers to data stored in an Excel Table. Much easier to sort.

Front 8

List and describt the 6 available options when using the AutoFilter feature in an excel table

Back 8

1. Sort A-Z or Z-A: Sort in ascending or descending order
2. Sort by Color: Sort by background color
3. Text Filters: filters text by specific characters
4. Number Filters: filters numeric values by operator (=,<,>,<>,><)
5. Date Filters: Filters date and time values by operator
6. Custom: Makes it so that you filter out by your personal specification

Front 9

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

Back 9

The database provides the structure to provide that the right data is available and protected. While the spreadsheet provides the analytical power and flexibility needed by most business people

Front 10

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

Back 10

1. Open the workbook
2. Select a blank cell to drop the data into
3. Click from access>get external>data tab on ribbon
4. Select what you want to transfer over

Front 11

What is the query wizard and when would you use it?

Back 11

It lets you choose your data source and select the database table and fields you want to import into a workbook. You should use it when you want to move specific query results to your table

Front 12

How does excel store data and time values?

Back 12

It uses serial numbers. Excel represents a date as the number of days since January 0, 1900. Jan 1 would be = 1, Jan 2 = 2...and so on. Using serial numbers makes calculations involving dates and times very easy

Front 13

What are the arguments for the YEARFRAC function?

Back 13

=YearFrac(Start_date,End_Date,Basis)

Front 14

What are the possible values for calculating months and years using the YEARFUNC function?

Back 14

The basis is the argument that lets you establish the lengths of months and years. Can be a 0 or 1.
0 = Calculates months using 30 days and years using 360 days
1 = calculates months and years with actual values

Front 15

When should you use a PivotTable report to analyze data? (3)

Back 15

1. When the data needs to be analyzed from different perspectives.
2. When data can be summarized in multiple ways.
3. When the data needs to be easily rearranged to match the required analysis.

Front 16

List and describe the four areas of a PivotTable report

Back 16

1. Column Lables Area: displays data from field in columns
2. Report Filter Area: Filters out data for use in generating a report
3. Row Label Area: Displays data from field in rows
4. Values Area: Where you place fields you want to summarize

Front 17

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

Back 17

A web query is an automated method for retrieving information from a Web page without having to copy and paste it into an application. Investor indexes and stock quotes from MSN MoneyCentral Investor would be refreshed each time a workbook is opened

Front 18

List and describe the differences and similarities between HTML and XML

Back 18

HTML and XML store information in a markup language format but using carrots to define different properties. HTML is a more defined format with pre-defined properties. XML files can be created to use any format of properties, not just the subset that is HTML

Front 19

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

Back 19

1. Open the worksheet into which you want to import the data and select the first blank cell where you want the data to appear.
2. On the "data" tab, Click the "from other sources" button and then select "from XML data import".
3. Browse to the folder that contains the XML document you want to import, click it, and open it
4. 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 OK to close the message box, the "import data" dialogue box opens.
6. Click the option button to import the data in the existing worksheet or in a new worksheet and click "ok"

Front 20

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

Back 20

1. Show developer tab and select the "source" button in the XML group.
2. Click XML maps to open the dialog box
3. Click the add button
4. Go to the folder that contains the document, click the XML doc, and click open.
5. The schema message box opens, click OK.

Front 21

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

Back 21

1. It makes it easy to transfer information to otherwise incompatible applications
2. It also stores data in a non proprietary information

Front 22

Compare break-even analysis and sensitivity analysis

Back 22

Break Even Analysis: A type of what-if analysis that concentrates on activity at or around the point at which a product breaks even.
Sensivitiy analysis: another type of what-if analysis that attempts to examine how sensitive the results of an analysis are to changes in the assumptions. Helps managers learn how tolerant the projected results are of changes in those estimates and assumptions.

Front 23

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

Back 23

It allows you to organize and present the results of multiple what-if analyses. Useful because you can compare the results of many calculations.

Front 24

What is the difference between a one-variable data table and a two variable data table?

Back 24

One-variable: Has only one input cell and many result cells - (ex: different interest rates affect a loan payment)

Two-Variable: two input cells but one result cell (ex: Use to see how two different interest rates and loan terms can affect a loan payment)

Front 25

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

Back 25

2. Set up a structure for the data table
2. Instruct excel about how the data table's structure related to the input selection of the worksheet

Front 26

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

Back 26

The column and rows contain the input values, and the output formula is the cell at the intersection of the first row and column

Front 27

When should you create a scenario instead of a data table?

Back 27

When you need to compare more than two varying input values

Front 28

Give an example of a business situation that could best be analyzed with scenarios.

Back 28

If you wanted to compare three break even analyses:
1. A worst-case break-even analysis, showing effects of lowering the unit price and decreasing projected unit sales
2. A likely break-even analysis that shows the effects of maintaining the current unit price and projecting realistic unit sales
3. A best-case break-even analysis that shows the effects of increasing the unit price and exceeding sales expectations.

Front 29

Why are defined names important when you create scenarios?

Back 29

It makes them easier to understand and use

Front 30

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

Back 30

Scenario pivot table and Scenario summary
Scenario summary shows results only.

Front 31

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

Back 31

It can answer what would happen if you start a new operation or you are entering into a new market, or dealing with any other situation in which no real data is available.

Front 32

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

Back 32

The input column (on the left) must represent a number of iterations while the top row must show input values.

Use a random number to create values that will be used in a VLOOKUP table against other input data. This will randomize the results that are generated. Your row input cell will be from a calculated value in the worksheet and the column input cell will be a blank cell somewhere in the worksheet that is not being used

Front 33

What are the limitations of using goal seek?

Back 33

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

Front 34

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

Back 34

200

Front 35

What are the 3 required parameters of a solver model and what do they represent?

Back 35

1. Objective cell (the goal)
2. Variable cells (values changed to max,min, or set objective cell to specific value)
3. The constraints (restricts the values that are entered into the variable cells)

Front 36

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

Back 36

1.It makes it possible for you to refer to each constraint value as a cell reference.
2. Organizes constraints and makes them visible in the worksheet at any type without limitation of having the problem solver open

Front 37

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

Back 37

By linking the values to the worksheet, it is much easier to run solver with updated constraint information.

Front 38

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

Back 38

Policy Constraint: often based on company history and to extent inertia. (ex: number of gallons of milk you can buy at sale price)
Physical Limits: the actual number of something (the actual inventory of milk available in the store)

Front 39

What is an objective function?

Back 39

a mathmatical formula that relates the decision variables or variable cells to the desired outcome. The result of an objective function becomes the objective cell

Front 40

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

Back 40

<=,>=,=,int,bin

Front 41

When should you include integer constratints in a solver?

Back 41

When you want your results to be whole numbers

Front 42

What is the disadvantage of using an integer constraint?

Back 42

It can make the solver run considerably slower

Front 43

What is a scenario in solver?

Back 43

It saves the result of a solver model so that you can load it later. Useful when you need to refer to the result of a previous solver model so you can compare it with another model's results

Front 44

What are two types of solver reports?

Back 44

1. Sensitivity
2. Limits

Front 45

What information is described in an answer report?

Back 45

Includes detailed information about the objective cell, variable cell, and constraints

Front 46

What is the difference between a binding status and a not binding status?

Back 46

Binding: the constraint has a final value that is equal to the value of the constraint
Not binding: constraint does not have a final value that is equal to the value of the constraint

Front 47

What is slack?

Back 47

Unused resources that are available

Front 48

What is an unbounded solution?

Back 48

Occurs when the solution is unrestrained or unlimited on some dimension. Could happen if you are trying to solve a problem in which there are either no constraints or too few constraints.

Front 49

What is a linear function?

Back 49

results when a decision variable is multiplied by a constant.

Front 50

What is a nonlinear function?

Back 50

When you cannot descrie a function in linear terms, it is called a nonlinear function. When you graph a nonlinear function in 2D, you get a curve, and NOT a line

Front 51

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

Back 51

They need to be the same size, shape and orientation

Front 52

Describe how the SUMPRODUCT function works.

Back 52

It sums a series of products in ranges of identical sizes called arrays

Front 53

Describe the steps for saving a solver model.

Back 53

1. Open solver
2. Click save/load
3. Select the number of cells it tells you to to select and click save.

Front 54

What is the advantage of saving a solver model?

Back 54

You can save a solver model and recall it later

Front 55

What is an assignment problem?

Back 55

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

Front 56

How does the binary comparison operator work?

Back 56

It assigns 1 and 0 to the values and makes a decision based on these inputs

Front 57

What does the SEARCH function do?

Back 57

The same thing as the FIND function, but is not case sensitive.

Front 58

What does the subtotal command do?

Back 58

Creates summary reports that quickly organize dta into categories with sub-total calculations and lets you collapse and expand the level of detail in the report

Front 59

What are the 2 limitations to an Excel table?

Back 59

1. There is a maximum number of fows and columns.
2. Excel must load the entire workbook into memory when you open it, which can lead to performance problems with large tables

Front 60

What is HTML?

Back 60

A markup language that uses embedded tags to describe how to format its content. Ex: <b>, </b> would say to bold and unbold a text.

Front 61

What is XML?

Back 61

Designed to combine the markup power of SGML with the ease of use of HTML. XML provides the grammatical rules and the alphabet.