Exam 7: Spreadsheet Models

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.

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

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.

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

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.

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

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.

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

The influence in an influence diagram is visually depicted by:

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

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

The function of Trace Precedents and Trace Dependents is to:

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.

The SUM function in Excel:

The arguments supplied to the IF function are:

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

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

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.

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.