Business analytics

Your submission should include a Word document and an Excel spreadsheet (with tabs for each answer). Your spreadsheet should show all formulas and references. There will be significant penalties for answers without formula where applicable.

 

Question 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

 

  1. What is the expected commission based on making 600 calls?

 

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

 

Question 2: Spreadsheet Modeling

You operate a luxury hotel in Baltimore 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).

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

 

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

 

Question 3: Linear Optimization

OPRE 605.7BE Inc, Baltimore, 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?

 

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

 

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

 

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

Question 4: 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.

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

 

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

 

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

 

  1. What is the strongest predictor of selling price?

 

Question 5: Linear Regression

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

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

 

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

 

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

 

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

  1. Develop a spreadsheet model for calculating net revenue.

 

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

 

National Football League Data 2007 Season
Team Points/Game Yards/Game Rushing Yards/Game Passing Yards/Game Opponent Yards/Game Opponent Rushing Yards/Game Opponent Passing Yards/Game Penalties Penalty Yards Interceptions Fumbles Passes Intercepted Fumbles Recovered
Arizona Cardinals 25.2 344.1 90 254.1 330.2 97.9 232.3 137 1,128 18 11 24 12
Atlanta Falcons 16.2 301 95 206 355.5 127.1 228.4 105 891 16 12 15 9
Baltimore Ravens 17.2 302 112.3 189.7 301.6 79.3 222.3 107 873 17 6 14 26
Buffalo Bills 15.8 277.1 112.5 164.6 362.9 124.6 238.4 78 633 18 12 14 7
Carolina Panthers 16.7 284.9 114 170.9 324.8 110.7 214.1 95 801 14 16 17 12
Chicago Bears 20.9 293.2 83.1 210.1 354.7 122.9 231.8 111 839 16 17 21 13
Cincinnati Bengals 23.8 348 97.3 250.8 348.8 118.3 230.4 90 670 19 16 20 10
Cleveland Browns 25.1 351.3 118.4 232.9 359.6 129.5 230.1 114 868 17 10 20 9
Dallas Cowboys 28.4 365.7 109.1 256.6 307.6 94.6 213.1 104 815 19 10 19 5
Denver Broncos 20 346.3 122.3 224 336 142.6 193.4 90 610 14 16 15 14
Detroit Lions 21.6 322.9 80.5 242.4 377.6 119.4 258.2 100 676 17 18 22 14
Green Bay Packers 27.2 370.7 99.8 270.9 313.3 102.9 210.4 113 1,006 19 9 15 9
Houston Texans 23.7 333.6 99.1 234.4 344.2 114.1 230.1 82 636 11 14 21 17
Indianapolis Colts 28.1 358.7 106.6 252.1 279.7 106.9 172.8 67 515 22 15 14 5
Jacksonville Jaguars 25.7 357.4 149.4 208 313.8 100.3 213.5 76 594 20 10 8 13
Kansas City Chiefs 14.1 276.8 78 198.8 319.4 130.6 188.9 101 697 14 8 20 13
Miami Dolphins 16.7 287.5 98.1 189.4 342.2 153.5 188.7 91 732 14 8 16 13
Minnesota Vikings 22.8 336.2 164.6 171.6 338.1 74.1 264.1 86 662 15 16 14 16
New England Patriots 36.8 411.2 115.6 295.7 288.3 98.3 190.1 78 690 19 12 9 6
New Orleans Saints 23.7 361.2 91.6 269.6 348.1 102.9 245.3 68 581 13 10 18 12
New York Giants 23.3 331.4 134.3 197.1 305 97.7 207.3 77 652 15 10 20 14
New York Jets 16.8 294.7 106.3 188.4 331.9 134.8 197.1 63 486 15 6 19 6
Oakland Raiders 17.7 294.8 130.4 164.4 341.6 145.9 195.8 120 864 18 8 20 17
Philadelphia Eagles 21 358.1 123.4 234.7 311.4 95.8 215.6 83 649 11 8 15 12
Pittsburgh Steelers 24.6 327.4 135.5 191.9 266.4 89.9 176.5 80 651 11 14 14 8
San Diego Chargers 25.8 315.2 127.4 187.8 320.3 107 213.3 94 761 30 18 16 8
San Francisco 49ers 13.7 237.3 92.3 145 346.2 118.5 227.7 97 702 12 10 17 17
Seattle Seahawks 24.6 348.9 101.2 247.8 321.8 102.8 219.1 59 428 20 14 13 11
St. Louis Rams 16.4 297.5 95.4 202.1 341.1 115.3 225.8 94 794 18 9 28 9
Tampa Bay Buccaneers 20.9 326.8 117 209.8 278.4 107.9 170.5 81 614 16 19 8 12
Tennessee Titans 18.8 311.7 131.8 179.9 291.6 92.4 199.2 101 773 22 12 17 17
Washington Redskins 20.9 333.4 116.9 216.4 305.3 91.3 214 90 751 14 10 11 18

Last Updated on February 11, 2019 by EssayPro