Deck 7: Spreadsheet Models

Question 1

What do nodes in an influence diagram represent?

A) Model parts
B) Influence levels
C) Road maps
D) Environmental factors

Answer 1

Model parts

Question 2

A one-way data table summarizes:

A) a single input's impact on the output of interest.
B) multiple input's impact on a single output of interest.
C) values of the input cells that will cause the single output value to equal zero.
D) values of cells when not all of the model is observable on the screen.

Answer 2

a single input's impact on the output of interest.

Question 3

The conceptual model:

A) helps in organizing the data requirements.
B) controls the model inputs.
C) has tools defined to identify the optimal solution.
D) explores the effects of changing model parameters.

Answer 3

helps in organizing the data requirements.

Question 4

A(n) _____ is a visual representation that shows which entities influence others in a model.

A) decision tree diagram
B) influence diagram
C) entity chart
D) time series plot

Question 5

The modularity approach of building the influence diagram for a portion of the problem first and then expanding until the total problem is conceptually modeled:

A) improves complexity of the modeling process.
B) reduces the likelihood of error.
C) results in the propagation of errors.
D) helps avoid construction of the mathematical and spreadsheet models.

Question 6

. Reference - 7.1Use the influence diagram given below to answer questions

Reference - 7.1. Which of the following would be a likely mathematical expression for Total Cost?

A) Total Cost = Total Variable Cost × Material Cost per unit + Fixed Cost
B) Total Cost = Fixed Cost + Total Variable Cost
C) Total Cost = Total Variable Cost + Total Revenue × Production Volume
D) Total Cost = Fixed Cost × Total Variable Cost - Production Volume

Question 7

. Reference - 7.1Use the influence diagram given below to answer questions

With reference to a what-if model, an uncontrollable model input is known as a(n) _____.

A) decision variable
B) dummy variable
C) parameter
D) outlier

Question 8

Which of the following is true of spreadsheet packages used in business analytics?

A) They are more expensive than specialized packages.
B) They require substantial user training.
C) They come preloaded on computers.
D) They do not have specialized functions to perform detailed analyses.

Question 9

Navigation in a spreadsheet model can be facilitated by:

A) using different spreadsheets for each formula in the model.
B) using long calculations in the cells.
C) using clear labels and proper formatting and alignment.
D) referencing data by using hyperlinks to the problem statement.

Question 10

The impact of two inputs on the output of interest is given by a _____.

A) Goal Seek
B) Watch Window
C) multiple-way data table
D) two-way data table

Question 11

Which of the following approaches is a good way to proceed with the influence diagram building for a problem?

A) The influence diagram for the entire problem is build first and then separate portions are clustered to form separate models.
B) The influence diagram for all the model parts at the same level are built in parallel to reduce the likelihood of error.
C) The influence diagram is reverse engineered -the diagram is developed in the opposite direction starting with the model output.
D) The influence diagram for a portion of the problem is build first and then expanded until the total problem is conceptually modeleD)

Question 12

Spreadsheet models are referred to as what-if models because they:

A) are mathematical and logic-based models.
B) allow easy instantaneous recalculation for a change in model inputs.
C) come preloaded on computers.
D) have specialized functions to perform detailed analysis.

Question 13

. Reference - 7.1Use the influence diagram given below to answer questions

A(n) _____ refers to a model input that the decision maker can control in a what-if model.

A) decision variable
B) outlier
C) parameter
D) dummy variable

Question 14

. Reference - 7.1Use the influence diagram given below to answer questions

Which of the following would be a likely mathematical expression for Total Variable Cost?

A) Total Variable Cost = Production Volume × Revenue per Unit
B) Total Variable Cost = Material Cost per Unit × Labor Cost per Unit
C) Total Variable Cost = Total Cost - (Material Cost per Unit + Labor Cost per Unit)
D) Total Variable Cost = (Material Cost per Unit + Labor Cost per Unit) × Production Volume

Question 15

An Excel _____ quantifies the impact of changing the value of a specific input on an output of interest.

A) Watch Window
B) Data Table
C) Goal Seek
D) I think you could argue this is true as well. I'd change from Trial-and-error to something else - - maybe Influence Diagram.
Ansrsource: We have replaced distractor D with "Chart".
Chart
I think you could argue this is true as well. I'd change from Trial-and-error to something else - - maybe Influence Diagram.
Ansrsource: We have replaced distractor D with "Chart".

Question 16

. Reference - 7.1Use the influence diagram given below to answer questions

Which of the following would be a likely mathematical expression for Total Revenue?

A) Total Revenue = Production Volume + Revenue per Unit
B) Total Revenue = Profit - Production Volume × Revenue per Unit
C) Total Revenue = Production Volume × Revenue per Unit
D) Total Revenue = Total Variable Cost + Production Volume + Revenue per Unit

Question 17

The influence in an influence diagram is visually depicted by:

A) a circular symbol
B) an arrow
C) a straight line
D) the height of the influence diagram

Question 18

The modeling process begins with the framing of the _____ that shows the relationships between the various parts of the problem being modeled.

A) mathematical model
B) conceptual model
C) circular model
D) correlation model

Question 19

In _____ decision making companies have to decide whether they should manufacture a product or outsource its production to another firm.

A) goal seek
B) two-way
C) voting-based
D) make-versus-buy

Question 20

Which of the following design guidelines, if followed, enables the user to update the model parameters without the risk of mistakenly creating an error in a formula?

A) Separating the parameters from the spreadsheet model
B) Documenting the spreadsheet model
C) Using numbers in the spreadsheet formula
D) Using simple formulas

Question 21

The _____ button provides an automatic means of checking for mathematical errors within formulas of a worksheet.

A) Error Checking
B) Trace Precedents
C) Watch Window
D) Math & Trig

Question 22

The Watch Window is observable:

A) only when the complete model is observable on the screen.
B) only in the same worksheet of a workbook.
C) across different worksheets of a workbook.
D) across different workbooks in the same folder.

Question 23

Within a given range of cells, the number of times a particular condition is satisfied is computed by using the _____ function.

A) SUMIF
B) IF
C) VLOOKUP
D) COUNTIF

Question 24

The SUM function in Excel:

A) adds up all the numbers in the cells diagonally.
B) adds up only positive numbers in a range of cells.
C) adds up all the numbers in a range of cells.
D) adds up the cells specified by a given condition or criteria.

Question 25

The user can monitor how listed cells change with a change in the model without searching through the worksheet or changing from one worksheet to another by using the _____ functionality.

A) Goal Seek
B) Evaluate Formula
C) Watch Window
D) Trial-and-error

Question 26

Excel searches for an exact match of the first argument in the first column of the data when the range in the VLOOKUP function is _____.

A) TRUE
B) NULL
C) FALSE
D) EXACT

Question 27

The function of Trace Precedents and Trace Dependents is to:

A) highlight errors in copying and formula construction.
B) ascertain how model parts are segregated.
C) trace the range of cells included in the Watch Window box list.
D) investigate the cell calculations in great detail.

Question 28

With reference to the SUMPRODUCT function, which of the following statements is true?

A) The range of cells for each array must contain only nonzero values.
B) Any cell that does not satisfy the specified given condition or criteria will not be considered.
C) The array appearing as the first argument must be sorted in ascending order.
D) The arrays that appear as arguments must be of the same dimension.

Question 29

The condition that VLOOKUP assumes is that:

A) there are no nonzero values in the range.
B) all the arguments are of the same dimension.
C) the first column of the table is sorted in ascending order.
D) the columns with empty cells are to be neglecteD)

Question 30

Excel's _____ tool allows the user to determine the value of an input cell that will cause the value of a related output cell to equal some specified value.

A) Goal Seek
B) Watch Window
C) Data Validation
D) XLMiner

Question 31

The _____ button in the Formula Auditing group allows the user to inspect each formula in detail in its cell location.

A) Evaluate Formula
B) Error Checking
C) Watch Window
D) Show Formulas

Question 32

Which of the following tools provides an excellent means of identifying the exact location of an error in a formula?

A) Error Checking
B) Function Library
C) Show Formulas
D) Evaluate Formula

Question 33

The _____ button, located in the Formula Auditing group, creates arrows pointing to the selected cell from cells that are part of the formula in that cell.

A) Trace Dependents
B) Trace Precedents
C) Error Checking
D) Watch Window

Question 34

The arguments supplied to the IF function are:

A) the condition for execution, the result if condition is true, and the result if condition is false.
B) the range of cells and the condition for execution.
C) the array₁ of data cells, the array₂ of data cells, and the condition for execution.
D) the condition for execution only.

Question 35

The VLOOKUP with range set to _____ takes the first argument and searches the first column of the table for the last row that is strictly less than the first argument.

A) FALSE
B) TRUE
C) LESS
D) NULL

Question 36

The _____ function pairs each element of the first array with its counterpart in the second array, multiplies the elements of the pairs together, and adds the results.

A) SUM
B) SUMPRODUCT
C) SUMIF
D) VLOOKUP

Question 37

The calculations of a cell can be investigated in great detail by using the _____ button.

A) Calculation Options
B) Evaluate Formula
C) Error Checking
D) Show Formulas

Question 38

The _____ function allows the user to pull a subset of data from a larger table of data based on some criterion.

A) VLOOKUP
B) IF
C) SUMIF
D) COUNTIF

Question 39

The _____ function is used for the conditional computation of expressions in Excel.

A) MAX
B) IF
C) SUMSQ
D) NOT

Question 40

Arrows pointing from the selected cell to cells that depend on the selected cell are generated by using the _____ button of the Formula Auditing group.

A) Error Checking
B) Trace Precedents
C) Trace Dependents
D) Watch Window

Question 41

A company asked one of their analysis team to analyze and create models that help decide whether they should manufacture a particular product or outsource its production. The different components are given below:
Fixed Cost, FC = $25,000
Material Cost per Unit, MC = $2.15
Labor Cost per Unit, LC = $2.00
Outsourcing Cost per Unit, O = $4.50
Note that per-unit material and labor cost together make up the variable cost per unit. a. Using the spreadsheet model, construct a one-way data table with production volume as the column input and savings due to outsourcing as the output. Breakeven occurs when profit goes from a positive to a negative value, that is, breakeven is when total outsource cost = total cost, yielding savings due to outsourcing equal to zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur?
b. Use Goal Seek to find the exact breakeven point. Assign Set cell: equal to the location of savings due to outsourcing, To value: = 0, and By changing cell: equal to the location of the production volume in your model.

Question 42

The average cost/unit for the production of a particular component at a manufacturing plant varies with the number of units produced in each batch. The data are given below.


Suppose the selling price of each unit is $35.
Use a two-way data table to show how the profit changes as a function of demand and the selling price of the product. Vary the demand from 20 units to 80 units in increments of 10 units and selling price from $30 to $40 in increments of $2.

Question 43

Suppose you have $1100 and decided to purchase a new model of television that costs you $1100. You find an electronics store where a gift voucher, worth $50, is offered with this TV model if payment is made in full during the time of purchase, or it can be financed at 0 percent interest for 5 months with a monthly payment of $220. You now have two options: either invest your amount for an annual interest rate of 10% and opt for 0 percent financing option for the TV purchase; or choose full payment option. Develop a spreadsheet model to find the better option that results in a good saving? Also, find the discount rate for 0 percent financing option.
Hint: Use Goal Seek to find the discount rate that makes the net present value of the payments = $1050.

Question 44

The average cost/unit for the production of a particular component at a manufacturing plant varies with the number of units produced in each batch. The data are given below:


Suppose the selling price of each unit is $35. a. Build a model to calculate the profit of the manufacturing industry if the demand is 20. b. Construct a data table that shows the profit per unit as a function of demand if the demand ranges between 20 units through 80 units in increments of 10 units.

Question 45

A company asked one of their analysis team to analyze and create models that help decide whether they should manufacture a particular product or outsource its production. The different components are given below:
Fixed Cost, FC = $25,000
Material Cost per Unit, MC = $2.15
Labor Cost per Unit, LC = $2.00
Outsourcing Cost per Unit, O = $4.50
Note that per-unit material and labor cost together make up the variable cost per unit.
Use a two-way data table to show how the savings due to outsourcing changes as a function of different production volume and different bids on per-unit cost for outsourcing. Vary the production volume from 0 to 100,000 in increments of 10,000. The six bids are $3.11, $3.49, $4.50, $4.98, $5.12, and $5.45.

Question 46

Suppose a company supplies four of its products A, B, C, and D, to five different regions. The management wanted to know the total number of all products supplied to each region and the total units of each product supplied. The data collected over a period of month are given below?Provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.
:
Provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.

There are several ways to get this information from the given data set. One way is to use the SUMIF function. The SUMIF function extends the SUM function by allowing the user to add the values of cells meeting a logical condition. The general form of the function is
=SUMIF(test range, condition, range to be summed)
Using the SUMIF function, find the total volume by each region and total volume by each product.

Question 47

The Gatson manufacturing company has estimated the following components for a new product.
Fixed cost = $50,000
Material cost per unit = $2.15
Labor cost per unit = $2.00
Revenue per unit = $7.50
Note that fixed cost is incurred regardless of the amount produced. Per-unit material and labor cost together make up the variable cost per unit. Profit is calculated by subtracting the fixed cost and total variable cost from total revenue, assuming the company sells all its produced goods.

A) Build an influence diagram that illustrates how to calculate profit.
B) Using mathematical notation, give a mathematical model for calculating profit.
C) Implement your model from part (b) in Excel using the principles of good spreadsheet design.
D) If the company decides to make 70,000 units of the new product, what will be the resulting profit?

Question 48

The Gatson manufacturing company has estimated the following components for a new product.
Fixed cost = $50,000
Material cost per unit = $2.15
Labor cost per unit = $2.00
Revenue per unit = $7.50
Note that per-unit material and labor cost together make up the variable cost per unit.
Use a two-way data table to show how the profit changes as a function of different production volumes and different values of material cost per unit. Vary the production volume from 0 to 100,000 in increments of 10,000. The five different material costs are $1.50, $1.95, $2.15, $2.85, and $3.25.

Question 49

Starsystems is a small information systems company that employs 50 workers. The employee details for a particular month are given below:


a. The administrative manager of the company wanted to know the total number of employees who were on leave for 4 days and 5 days in this month. Use COUNTIF function to determine this.
b. Now, the manger wanted the details of employees, Ava, Julia, and Alanis who are working in the company. Use VLOOKUP function to get these employees details.

Question 50

The Gatson manufacturing company has estimated the following components for a new product.
Fixed cost = $50,000
Material cost per unit = $2.15
Labor cost per unit = $2.00
Revenue per unit = $7.50
Note that per-unit material and labor cost together make up the variable cost per unit. a. Using the spreadsheet model, construct a one-way data table with production volume as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value, that is, breakeven is when total revenue = total cost, yielding a profit of zero. Vary production volume from 0 to 100,000 in increments of 10,000. In which interval of production volume does breakeven occur? b. Use Goal Seek to find the exact breakeven point. Assign Set cell: equal to the location of profit, To value: = 0, and By changing cell: equal to the location of the production volume in your model.

Question 51

The selling price of each product sold in a furnishing showroom, and the number of units of each of these product sold during a period of one month are given below. The rental cost of the showroom is $225 and the other costs incurred are included in the cost/unit?Provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.
.
Provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.

The manager would like to know the profit gained in this month. There are several ways to get this information from the given data set. One way is to use the SUMPRODUCT function.
The SUMPRODUCT function returns the sum of the products of elements in a set of arrays. The general form of the function is
=SUMPRODUCT(array₁, [array₂], [array₃], …)
Use this SUMPRODUCT function to find the profit earned by the showroom in a month.

Question 52

Anna operates a consignment shop where she sells cloths for women and children. The average consignments sold per month is 1000. The average material cost and the selling price of each consignment is $8 and $20, respectively.
The monthly fixed cost to run this business is given below:
Rental cost: $750
Utilities: $150
Advertising: $35
Insurance: $100
Labor cost: $4000 a. Build an influence diagram that illustrates how to calculate profit.
b. Using mathematical notation, give a mathematical model for calculating profit.
c. Implement your model from part (b) in Excel using the principles of good spreadsheet design.

Question 53

Anna operates a consignment shop where she sells cloths for women and children. The average consignments sold per month is 1000. The average material cost and the selling price of each consignment is $8 and $20, respectively.
The monthly fixed cost to run this business is given below:
Rental cost: $750
Utilities: $150
Advertising: $35
Insurance: $100
Labor cost: $4000 a. Using the spreadsheet model, construct a one-way data table with number of consignments sold per month as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value. Vary number of consignments sold per month from 400 to 1200 in increments of 100. In which interval does breakeven occur?
b. Use Goal Seek to find the exact breakeven point. Assign Set cell: equal to the location of profit, To value: = 0, and By changing cell: equal to the location of the number of sold consignments in your model.

Question 54

Anna operates a consignment shop where she sells cloths for women and children. The average consignments sold per month is 1000. The average material cost and the selling price of each consignment is $8 and $20, respectively.
The monthly fixed cost to run this business is given below:
Rental cost: $750
Utilities: $150
Advertising: $35
Insurance: $100
Labor cost: $4000
Use a two-way data table to show profit changes as a function of different number of consignments sold per month and different material costs. Vary the number of consignments from 400 to 1200 in increments of 100. The eight different material costs are $5.45, $6.23, $6.95, $7.54, $8.23, $8.88, $9, and $9.45.

Question 55

A company asked one of their analysis team to analyze and create models that help decide whether they should manufacture a particular product or outsource its production. The different components are given below:
Fixed Cost, FC = $25,000
Material Cost per Unit, MC = $2.15
Labor Cost per Unit, LC = $2.00
Outsourcing Cost per Unit, O = $4.50
Note that per-unit material and labor cost together make up the variable cost per unit. a. Build an influence diagram that illustrates how to calculate the difference in cost of manufacturing and outsourcing.
b. Using mathematical notation, give a mathematical model for calculating the difference in cost of manufacturing and outsourcing.
c. Implement your model from part (b) in Excel using the principles of good spreadsheet design.
d. If the company wants to make 30,000 units of a particular product, what are the savings due to outsourcing?

Question 56

John would like to establish a retirement plan that returns an amount of $100,000 after a period of 20 years from now. Build a spreadsheet model to calculate the amount John must contribute at the end of each year towards his retirement fund, assuming an annual interest rate of 6%.
Use the Excel function
=PMT(rate, nper, pv, fv, type)
The arguments of this function are
rate = the interest rate for the loan
nper = the total number of payments
pv = present value (the amount borrowed which is 0 in this case)
fv = future value (in the formula, indicate this value as negative as the future value command assumes a stream of payments not deposits)
type = payment type (0 = end of period, 1 = beginning of the period)
Also, construct a one-way table with interest rate as the column variable and the amount contributed at the end of each year as the output. Vary the interest rate from 4% to 7% in increments of 0.5%.

Question 57

Given below is a sample list of 20 products in a grocery store with the product code, the price, and the associated discount rates?Maybe provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.
.
Maybe provide a data file?
ansrsource: The required data is provided below the question and we have been following this for all the completed chapters till date.
a. Use the VLOOKUP function to find the price of the products A109, F432, B985, D203, C432, B654, and A345.
b. Use the COUNTIF function to determine the number of products associated with each discount rate - 0%, 5%, and 10%, from the provided list.

Question 58

A clothing retail store offers a discount at the rate of 10% on the customer bill if the purchase exceeds $100. The owner of the store wishes to know the total amount that has been discounted on the customers' purchases on a particular day. The purchase amount for each of the 12 customers who visited the store on that day is given below. Use the IF and SUM functions to find the total amount discounted on this single day purchases.

Question 59

An electronics store sells two models of television. The sales of these two models, X and Y, are dependent, that is, if the price of one increases, the demand for the other increases. A study is made to find the relationship between the demand (D) and the price (P) in order to maximize the revenue from these products. The result of the study is shown below:
DX = 476 - 0.54 PX + 0.22 PY
DY = 601 + 0.12 PX - 0.54 PY a. Construct a model for the total revenue and implement it on a spreadsheet.
b. Develop a two-way data table to estimate the optimal prices of each of the two products in order to maximize the total revenue. Vary price of each product from $600 to $900 in increments of $50.

Question 60

Starsystems is a small information systems company which employs 50 workers. The employee details are given below:


a. Find the total number of male and female employees who are working in this company. Use the COUNIF function.
b. Using SUMIF function, find the average incomes of both male and female employees who are working in this company.