Exam 10: Spreadsheet Models

John would like to establish a retirement plan that returns an amount of $100,000 in 20 years. 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%.

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.

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.

The condition that VLOOKUP assumes is that

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​ a. Build an influence diagram that illustrates how to calculate profit. b. Using mathematical notation, construct a mathematical model for calculating profit. c. Implement your model from part (b) in an Excel spreadsheet model using the principles of good spreadsheet design. d. Using the spreadsheet model, what will be the resulting profit if the company decides to make 70,000 units of the new product? ​

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

Navigation in a spreadsheet model can be facilitated by

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 a 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 the number of consignments sold per month from 400 to 1200 in increments of 100. In which interval does breakeven occur? b. Use the appropriate Excel tool to find the exact breakeven point ​

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 _________ 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 one-way data table summarizes

The SUM function in Excel

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

The Watch Window is observable

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?

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.

The following table is used to lookup information on a specific product. The Product ID is entered into cell B2 and the information is returned in the shaded box.There are several formulas in the table. How could I look at the cells that use the value in cell B2? ​

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 ​ a. Build an influence diagram that illustrates how to calculate the difference in cost of manufacturing and outsourcing. b. Using mathematical notation, construct a mathematical model for calculating the difference in cost of manufacturing and outsourcing. c. Implement your model from part (b) in an Excel spreadsheet model using the principles of good spreadsheet design. d. Using the spreadsheet model, what will be the resulting savings due to outsourcing if the company wants to make 30,000 units of a particular product? ​

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

The arguments supplied to the IF function, in order, are the condition for execution,