Could you please help me set this up on excel? I really need help I posted the problem 3 times now :(
The following modeling problem will be your first presentation, done in teams of 3. The bulk of your grade is shared amongst the team and is dependent on presentation organization, content, images, personal appearance, and time limit (see the rubric for more details) The rest of your grade depends upon your individual presentation (speaking clearly, demonstrating comprehension, maintaining enthusiasm and professionalism). The only file to submit is one Powerpoint document per team, sent in an email to the instructor with all teammates' names listed, due 10 minutes prior to class time on the given day. Adapted from "Ch. 7: The Student's Dilemma." How to Model It: Problem Solving for the Computer Age, by A. M. Starfield et al, Burgess International Group, 1994. The situation You are a consultant hired by the CEO of Big Red's Big Homes, a company that builds houses They have just purchased a 10-acre plot for $300,000 and plan to develop it as a residential area consisting of two models: a low-income home (which can be sold for $100,000) and a middle- income home (which can be sold for $150,000) The project needs to be completed within 5 years. The architect estimates that each low-income home requires 2.0 labor-years to build and each middle-income home requires 2.5 labor-years The company currently employs 60 laborers, and has no desire to grow that number More details on the homes are summarized here: Cost to build Density $60,000 Max # that can be sold Home type Low-income Middle-income$80,000 20 per acre 100 12 per acre 75 Zoning regulations require that no more than 155 total homes may be built on the 10-acre plot. In addition, a program by the city council requires that the number of low-income homes be at least 80% of the number of middle-income homes. The problem The company needs help addressing the following questions: 1. For the given parameters, how many of each type of home should we build? 2. What would be the maximum profit for the housing development? 3. In general, the housing market gets saturated when there are too many similar homes Assume that if only one of a home type gets built it can be sold for the original value ($100,000 or $150,000), but that each additional home would reduce the price by $400. Answer questions 1 and 2 for the new scenario Presentation details Present your analysis of the problem through Powerpoint. Your presentation must last 94.75 minutes and feature every member of your group speaking for at least 2.5 minutes. In the presentation, discuss the following points and any other useful related information: Define the purpose of the model . List assumptions List real-world features that pass Occam's Razor Define variables & develop equations . . Ilustrate the solution space as best as you can (you may find that traditional 2D or 3D plots are ineffective) Solve the problems using the Simplex Method within Excel Solver Which problems cannot be solved using the Simplex Method? Why? Solve these with another method Perform a sensitivity analysis on the most influential parameters. Loosening which constraints would provide more profit? How much? Discuss improvements to be made to the model Why would a developer care about models like this? Describe the benefits and limitations of applying this model to a real business. * * o The following modeling problem will be your first presentation, done in teams of 3. The bulk of your grade is shared amongst the team and is dependent on presentation organization, content, images, personal appearance, and time limit (see the rubric for more details) The rest of your grade depends upon your individual presentation (speaking clearly, demonstrating comprehension, maintaining enthusiasm and professionalism). The only file to submit is one Powerpoint document per team, sent in an email to the instructor with all teammates' names listed, due 10 minutes prior to class time on the given day. Adapted from "Ch. 7: The Student's Dilemma." How to Model It: Problem Solving for the Computer Age, by A. M. Starfield et al, Burgess International Group, 1994. The situation You are a consultant hired by the CEO of Big Red's Big Homes, a company that builds houses They have just purchased a 10-acre plot for $300,000 and plan to develop it as a residential area consisting of two models: a low-income home (which can be sold for $100,000) and a middle- income home (which can be sold for $150,000) The project needs to be completed within 5 years. The architect estimates that each low-income home requires 2.0 labor-years to build and each middle-income home requires 2.5 labor-years The company currently employs 60 laborers, and has no desire to grow that number More details on the homes are summarized here: Cost to build Density $60,000 Max # that can be sold Home type Low-income Middle-income$80,000 20 per acre 100 12 per acre 75 Zoning regulations require that no more than 155 total homes may be built on the 10-acre plot. In addition, a program by the city council requires that the number of low-income homes be at least 80% of the number of middle-income homes. The problem The company needs help addressing the following questions: 1. For the given parameters, how many of each type of home should we build? 2. What would be the maximum profit for the housing development? 3. In general, the housing market gets saturated when there are too many similar homes Assume that if only one of a home type gets built it can be sold for the original value ($100,000 or $150,000), but that each additional home would reduce the price by $400. Answer questions 1 and 2 for the new scenario Presentation details Present your analysis of the problem through Powerpoint. Your presentation must last 94.75 minutes and feature every member of your group speaking for at least 2.5 minutes. In the presentation, discuss the following points and any other useful related information: Define the purpose of the model . List assumptions List real-world features that pass Occam's Razor Define variables & develop equations . . Ilustrate the solution space as best as you can (you may find that traditional 2D or 3D plots are ineffective) Solve the problems using the Simplex Method within Excel Solver Which problems cannot be solved using the Simplex Method? Why? Solve these with another method Perform a sensitivity analysis on the most influential parameters. Loosening which constraints would provide more profit? How much? Discuss improvements to be made to the model Why would a developer care about models like this? Describe the benefits and limitations of applying this model to a real business. * * o
