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

The conceptual model:

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.

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

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

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.

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

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

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

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.

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?

The Watch Window is observable:

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

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

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

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.

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.

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.

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?