Reference no: EM132190552

Q 1: Spreadsheet Modeling

A stockbroker calls on potential clients from referrals. For each call, there is a 10% chance that the client will decide to invest with the firm. Fifty-five percent of those interested are found not to be qualified, based on the brokerage firm's screening criteria.

The remaining are qualified. Of these, half will invest an average of $5,000, 25% will invest an average of $20,000, 15% will invest an average of $50,000, and the remainder will invest $100,000. The commission schedule is as follows:

Transaction Amount	Commission
Up to $25,000	$50 + 0.5% of the amount
$25,001 to $50,000	$75+0.4% of the amount
$50,001 to $100,000	$125+0.3% of the amount

a) What is the expected commission based on making 600 calls?

b) How will the commission change when the number of calls change? (Hint: use a data table)

2: Spreadsheet Modeling

You operate a luxury hotel that famous celebrities rent for extended periods. The daily price is per room is $1,950. Operating costs average $60,000 per day, regardless of the number of rooms rented. Construct a spreadsheet model to determine the profit if 60 rooms are rented.

The manager has observed that the number of rooms rented during any given day varies between 50 and 80 (the total number of rooms available).

a) Use data tables to evaluate the profit for this range of unit rentals.

b) Suppose the manager is considering lowering or increasing the daily price by $100. How will profit be affected? (Hint: use a two-way data table).

 Q3: Linear Optimization

OPRE 605.7BE Inc,, has three flagship products: Absolute Sling Chairs, Awesome Tables, and Unbeatable Hammocks. The unit profit for these products is $45, $90, and $95, respectively.

Each type of product requires manufacturing, transportation to warehouse, and marketing. The owners are very ambitious and can work 40 hours each week and so have 200 hours available each month.

However, they are unable to spend more than 60 hours on any one activity (manufacturing, transportation, and marketing) in a month. The products are in such high demand that they are easily sold each month.

It takes 30 minutes to manufacture one Absolute Sling Chairs. Transportation to warehouses takes 45 minutes and marketing takes 1 hour. Awesome tables take 2 hours for both the manufacturing and transportation phases, and marketing takes 1 hour. For Unbeatable Hammocks, manufacturing takes 0.4 hours; transportation takes 3 hours; and marketing also takes 1 hour. How many of each product should OPRE 605.7BE Inc. produce each month to maximize profit?

a) Develop and implement a linear optimization model and clearly explain the sensitivity report.

b) Suppose that OPRE 605.7BE Inc. shareholders voted to limit the number of Absolute sling chairs to at most 25. How will the solution from part (a) change?

c) Suppose that OPRE 605.7BE Inc. gets an intern for the summer and therefore can increase the available hours for each activity by 5 hours each month. How will the solution from part (a) change?

Hint: for b) and c), you will need to just change the limitations but save each in a separate tab.

Q4: Linear Regression

A national homebuilder builds single-family homes and condominium-style townhouses. The dataQuestion 4 Data(in the Excel file Final Data) provides information on the selling price, lot cost, type of home, and region of the country (Midwest, and South)for closings for one month.

a) Develop a multiple regression model for sales price as a function of lot cost and type of home without any interaction term.

b) Determine if an interaction exists between lot cost and type of home and find the best model. What is the predicted price for either a single-family home or a townhouse with a lot cost of $45,000? (Hint: remember to convert home type to numeric, setting Townhouse to 1 and Single Family to 0 before creating the interaction)

c) Determine if an interaction exists between lot cost and region and find the best model. (Hint: convert the region variable to numeric, setting Midwest to 1 and South to 0. The model in c should also include the independent variables in b)

d) What is the strongest predictor of selling price?

Q5: Linear Regression

The data Question 5 Data (in the Excel file Final Data) provides various data on professional football for one season.   

a) Construct a scatter diagram for Points/Game and Yards/Game in the Excel file. Does there appear to be a linear relationship?

a) Develop a regression model for predicting Points/Game as a function of Yards/Game. Explain the statistical significance of the model.

b) Draw conclusions about the validity of the regression analysis assumptions from the residual plot and standard residuals.

Q 6: Spreadsheet Modeling

OPRE 605.8B Experts (OBE) specializes in laser-assisted corrective eye surgery. Prospective patients need to be screened to ensure they would be suitable candidates for the procedure. if they are, a $250 charge is applied as a deposit for the actual procedure.

Each week, about 180 people sign up to screened, and about 8% of them patients fail to show up or cancel their exam at the last minute. Patients that do not show up are refunded the prescreening fee less a $45 processing fee.

OBE can handle 185 patients per week and is considering overbooking its appointments to reduce the lost revenue associated with cancellations. However, any patient that is overbooked may spread unfavorable comments about the company; thus, the overbooking cost is estimated to be $125.

a) Develop a spreadsheet model for calculating net revenue.

b) Find the net revenue and number overbooked if 170 through 190 appointments are taken.