Exam 10: Spreadsheet Models

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 Construct a spreadsheet model and then 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.

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

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

A one-way data table summarizes

The conceptual model

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 condition that VLOOKUP assumes is that

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

A(n) ____________ refers to a model input that can be controlled in a spreadsheet model.