## Spreadsheet Modeling and Decision Analysis Study Set 2

Statistics

## Quiz 8 :

Nonlinear Programming and Evolutionary Optimization

Looking for Management Science Homework Help?

Q26 Q26 Q26

A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of product A costs 25 to make and demand is estimated to be 20 .10 * Price of A. A unit of product B costs 18 to make and demand is estimated to be 30 .07 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 100 and 200. Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs. The objective function for this problem is?Free

Unlocked

Multiple Choice

Q27 Q27 Q27

Exhibit 8.1
The following questions pertain to the problem and spreadsheet below.
A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of A costs 25 to make and demand is estimated to be 20 .10 * Price of A. A unit of B costs 18 to make and demand is estimated to be 30 .07 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 100 and 200.
Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs -Refer to Exhibit 8.1. What formula is used in cell B7 of the spreadsheet for this problem?Free

Unlocked

Multiple Choice

Q28 Q28 Q28

Exhibit 8.1
The following questions pertain to the problem and spreadsheet below.
A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of A costs 25 to make and demand is estimated to be 20 .10 * Price of A. A unit of B costs 18 to make and demand is estimated to be 30 .07 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 100 and 200.
Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs -Refer to Exhibit 8.1. What formula is used in cell B9 of the spreadsheet for this problem?Free

Unlocked

Multiple Choice

Q29 Q29 Q29

Exhibit 8.1
The following questions pertain to the problem and spreadsheet below.
A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of A costs 25 to make and demand is estimated to be 20 .10 * Price of A. A unit of B costs 18 to make and demand is estimated to be 30 .07 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 100 and 200.
Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs -Refer to Exhibit 8.1. What formula is used in cell D12 of the spreadsheet for this problem?Free

Unlocked

Multiple Choice

Q30 Q30 Q30

An investor is developing a portfolio of stocks. She has identified 3 stocks to invest in. She wants to earn at least 5% return but with minimum risk. The problem data is given in the following Excel spreadsheet. What formula should be entered in cell G4 of the Excel spreadsheet?

Free

Unlocked

Multiple Choice

Q31 Q31 Q31

An investor is developing a portfolio of stocks. She has identified 3 stocks to invest in. She wants to earn at least 5% return but with minimum risk. The problem data is given in the following Excel spreadsheet. What formula should be entered in cell H11 of the Excel spreadsheet?

Free

Unlocked

Multiple Choice

Q32 Q32 Q32

A company wants to locate a new warehouse to minimize the distance traveled by its delivery trucks. It has four stores and their coordinates are listed in the accompanying spreadsheet. Which cell(s) in the spreadsheet represent the decision variables in the problem?

Free

Unlocked

Multiple Choice

Q35 Q35 Q35

An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20,000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What cell is the variable cell in this problem?

Free

Unlocked

Multiple Choice

Q36 Q36 Q36

An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20,000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What cell is the objective cell in this problem?

Free

Unlocked

Multiple Choice

Q37 Q37 Q37

An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20,000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What formula goes in cell B11 in this problem?

Free

Unlocked

Multiple Choice

Q38 Q38 Q38

An office supply company is attempting to determine the order quantity for laser printer toner cartridges which are sold to local businesses. Annual demand is 20,000 units and each cartridge costs the store $25. It costs $30 to place an order and the inventory carrying cost rate is 25% of the value of the item. The following spreadsheet has been set up to solve the problem. What constraint would you impose on this problem to ensure that at least one order is placed per year?

Free

Unlocked

Multiple Choice

Q39 Q39 Q39

An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $9,000 and hopes he can earn 12%, compounded quarterly, on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income. What formula goes in cell C7 of the spreadsheet?

Free

Unlocked

Multiple Choice

Q40 Q40 Q40

An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $9,000 and hopes he can earn 12%, compounded quarterly, on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income. What constraint must be entered in the Risk Solver Platform (RSP) task pane?

Free

Unlocked

Multiple Choice

Q46 Q46 Q46

The Sweet Water beverage company is designing a new soft drink can. The designers wish to minimize the manufacturing cost of the can, a cost that is directly related to the amount of aluminum used in the can. The can must hold at least 350 ml (or cm

^{3}) of beverage, have a diameter between 3 and 7 cm, and have a height between 7 and 19 cm. Formulate the NLP for Sweet Water.Free

Unlocked

Essay

Q47 Q47 Q47

A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of A costs 30 to make and demand is estimated to be 50 .09 * Price of A. A unit of B costs 20 to make and demand is estimated to be 30 .14 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 90 and 140.
Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs. Formulate the NLP for this companyFree

Unlocked

Essay

Q48 Q48 Q48

A company makes products A and B from 2 resources, labor and material. The company wants to determine the selling price which will maximize profits. A unit of A costs 30 to make and demand is estimated to be 50 0.09 * Price of A. A unit of B costs 20 to make and demand is estimated to be 30 0.14 * Price of B. The utilization of labor and materials and the available quantity of resources is shown in the table. A reasonable price for the products is between 90 and 140.
Let X

_{1}= demand for As and X_{2}= demand for Bs. Let P_{1}= price for As and P_{2}= price for Bs. The NLP for the problem is: What values should go in cells B3:E18 of the spreadsheet for this problem?Free

Unlocked

Essay

Q49 Q49 Q49

An investor is developing a portfolio of stocks. She has identified 3 stocks in which to invest. She wants to earn at least 11% return but with minimum risk.
The average return for the stocks is:
The covariance matrix for the stocks is:
Let: P

_{i }= proportion of total funds invested in i, i = A, B, C Formulate the NLP for this problem.Free

Unlocked

Essay

Q50 Q50 Q50

An investor is developing a portfolio of stocks. She has identified 3 stocks in which to invest. She wants to earn at least 11% return but with minimum risk.
Let: P

_{i }= proportion of total funds invested in i, i = A, B, C The NLP for this problem is: What formulas should go in cells G4:J14 of the spreadsheet for this problem? NOTE: Formulas are not required in all of these cells.Free

Unlocked

Essay

Q51 Q51 Q51

A company wants to locate a new warehouse to minimize the distance travelled by its delivery trucks. It has four stores and their coordinates are listed in the below.
Formulate the objective function for this problem. Let X and Y represent the X, Y coordinates of the new warehouse.

Free

Unlocked

Essay

Q53 Q53 Q53

A company wants to locate a new warehouse to minimize the longest distance travelled by any of its delivery trucks. It has four stores and their coordinates are listed in the below.
Formulate the NLP for this problem. Let X and Y represent the X, Y coordinates of the new warehouse.

Free

Unlocked

Essay

Q54 Q54 Q54

A company wants to locate a new warehouse to minimize the longest distance travelled by any of its delivery trucks. It has four stores and their coordinates are listed in the below.
Let X and Y represent the X, Y coordinates of the new warehouse. The NLP for this problem and solution is the following.

Free

Unlocked

Essay

Q55 Q55 Q55

An office supply company is attempting to determine the order quantity for Mt. White fountain pens which are sold to local executives. Annual demand is 5,000 units and each pen costs the store $50. It costs $75 to place an order and the inventory carrying cost rate is 30% of the value of the item.
Formulate the objective function for this problem. Let Q indicate the order quantity.

Free

Unlocked

Short Answer

Q56 Q56 Q56

An office supply company is attempting to determine the order quantity for Mt. White fountain pens which are sold to local executives. Annual demand is 5,000 units and each pen costs the store $50. It costs $75 to place an order and the inventory carrying cost rate is 30% of the value of the item.
What values should go in cells B3:B11 of the spreadsheet for this problem if Q = 223.61?

Free

Unlocked

Essay

Q58 Q58 Q58

An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $12,000 and hopes he can earn 18% on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income.
What formulas should go in cells B7:F7 of the spreadsheet for this problem?

Free

Unlocked

Essay

Q59 Q59 Q59

An investor wants to determine how much interest he must earn to be able to make the payments on a 10-year mortgage which has increasing annual payments. The problem is summarized in the accompanying spreadsheet. The investor has enough money to make an initial investment of $12,000 and hopes he can earn 18% on his investments. He would like to know how low his annual return can be and still allow him to make his payments from interest income.
If the Risk Solver Platform (RSP) is used, which are the Objective, Variables and Constraint cells in the spreadsheet for this problem?

Free

Unlocked

Essay

Q62 Q62 Q62

A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). Formulate the NLP for the problem.Free

Unlocked

Essay

Q63 Q63 Q63

Exhibit 8.2
The following questions pertain to the problem and spreadsheet below.
A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). The following is the NLP formulation for the problem. The spreadsheet implementation of this formulation applies to the following questions. -Refer to Exhibit 8.2. What values would you enter in the Risk Solver Platform (RSP) task pane for the above Excel spreadsheet? Objective Cell: Variables Cells: Constraints Cells:Free

Unlocked

Essay

Q64 Q64 Q64

Exhibit 8.2
The following questions pertain to the problem and spreadsheet below.
A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). The following is the NLP formulation for the problem. The spreadsheet implementation of this formulation applies to the following questions. -Refer to Exhibit 8.2. What formula would you place into cell B5 to calculate Total Floor Area?Free

Unlocked

Short Answer

Q65 Q65 Q65

Exhibit 8.2
The following questions pertain to the problem and spreadsheet below.
A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). The following is the NLP formulation for the problem. The spreadsheet implementation of this formulation applies to the following questions. -Refer to Exhibit 8.2. What formula would you place in cell B6 to calculate Total Lot Area?Free

Unlocked

Short Answer

Q66 Q66 Q66

Exhibit 8.2
The following questions pertain to the problem and spreadsheet below.
A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). The following is the NLP formulation for the problem. The spreadsheet implementation of this formulation applies to the following questions. -Refer to Exhibit 8.2. What formula would you place in cell D13 to calculate total cost?Free

Unlocked

Short Answer

Q67 Q67 Q67

Exhibit 8.2
The following questions pertain to the problem and spreadsheet below.
A construction company just purchased a 300 300 foot lot upon which they plan to build an office building. They need at least 60,000 ft

^{2}of office floor space. Zoning regulations require each floor be 10 feet high and the building not exceed 65 ft in height. Further, parking space must equal at least 30% of the total floor space available. The company's cost accountant uses a 60% factor of the building height and a 1% factor of any story's floor area to calculate the total building cost (in millions of dollars). The following is the NLP formulation for the problem. The spreadsheet implementation of this formulation applies to the following questions. -Refer to Exhibit 8.2. The company wishes to have a relatively square building. Thus, they wish neither the building length nor the building width exceed the other by more than 25%. Add constraint(s) to enforce this design constraint.Free

Unlocked

Essay

Q68 Q68 Q68

Project 8.1 Truck Company Expansion
Kornfield Trucking handles private and commercial moves. They currently own 500 moving vans and employ 2000 full-time workers. Their trucks are used to pick-up and deliver office and household goods throughout the Eastern and Southeastern states. Kornfield mans each truck with three workers. This allows driver swaps providing increased miles-covered-per-hour ratio while staying within safety requirements for individual driving time. A 3-person crew also reduces company reliance on local help. Local distributors and warehouses provide a pool of laborers for loading and unloading the moving trucks. While in the past this arrangement has worked well, the arrangement has soured recently as the temporary workers have demanded higher wages while produced less work. Despite their pay and benefits package, Kornfield still finds that the nature of the work (lifting and time on the road) makes for a high rate of turnover. Thus, Kornfield maintains an excess of workers/drivers, but no more than 3.75 workers per truck at any time.
The following information is available on the trucks in the Kornfield inventory and their options for new purchases.
The following information applies to Kornfield personnel actions.
Kornfield has an operating budget of $ 75M next year and wants to expand their operations. As a part of this expansion, they are considering options for their truck fleet and may purchase new trucks, sell old trucks to salvage or some combination of the two. Any salvage money received is rolled into the operating budget. Kornfield is also considering possible changes to their work force. Their current force has a fairly high average salary and their operating budget is not greatly affected by releasing current employees. On the other hand, newer employees carry a much lower average salary and do not tax the operating budget heavily in hiring and training costs.
The Cobb-Douglas production function is used to model the number of vehicle miles driven per year. This function represents the quantity Kornfield management would like to maximize as they expand their operations. The general form of the Cobb-Douglas production function is the following:
where y is the output, each X

_{i}represents an input and the letters represent constants. This function generalizes to fewer or a greater number of parameters than the three depicted above. The constants for the Kornfield Trucking production function are (a, b, c, d) = (9.1, 0.05, 0.40, 0.50). Formulate the Kornfield Trucking problem as a non-linear programming problem. Implement the problem in Excel and use Risk Solver Platform (RSP) generalized reduced gradient (GRG) routing to obtain a solution to the problem. What is a recommended solution for Kornfield Trucking?Free

Unlocked

Essay