Words: 1726
Provide a summary of your answers to all of these questions in the Word comment. Question 6 should be answered in your Word document only. For question 7, create a new database in MS Access and summarize your answers in the Word document. Select one member of your team to submit the following files into Assai (Assignment 1): 1. Submit the Moscow file with all of the answers to the questions in sentence format. 2. Submit the Excel file that shows your calculations. 3. Submit the Access file with your database for question 7 All submissions must be received before 1 1:00 pm on Tuesday December 3, 2013.
You are responsible for ensuring that the files you submit can be opened and hat they contain the correct information. Linear and Integer Programming Question 1 Jarred is a business student at Brick University. He has just finished a course in Information Technology which had a midterm exam, a final exam, assignments and class participation. He earned 82% on the midterm exam, 79% on the final exam, 94% on the assignments and 100% on participation. The instructor is letting the student choose their own weighting scheme for the final course mark, with the following restrictions: Participation can be no more than 10% of the total course mark.
The weighting for the final exam must be at least 1. Times the weighting for the midterm exam. The total of the midterm and final exams must count for more than twice the assignment mark Each component must count for at least 10% of the total course mark. The marks assigned to the four components must total 100 and be non-negative. Provide a write-up of your model formulation in the Word document; define your decision variables, state the objective function, and list all constraints. In Excel, use Solver to find the optimal values for the decision variables.
State your solution on the Word document. (5 marks) Question 2 Sprouts and Sprouts Engineering provide design-build engineering services. There are currently eight building projects out for tender that Sprouts and Sprouts can bid on. All of the projects have a 10 week duration. Each project will require mechanical engineering, civil engineering and electrical engineering hours as shown in Table 1 below. Sprouts and Sprouts have also determined the amount of funds that must be available to support the project through to completion, and the amount that they would bid for the project. The bid amount is the amount that they would be paid on project completion. The firm’s profit is the difference between the bid price and the funding required. Sprouts and Sprouts have available to fund projects over this 10 week time frame. Staff engineers are available as shown in Table 2. (There is no cost for staff engineers as the firm must keep them on salary whether there is project work or not. ) Engineers work a 40 hour week. They can also hire contract engineers at $100/hour.
The maximum contract hours available are also shown in Table 2. Table 1 Project Mechanical hours available Civil Electrical Funding required ($000) Bid price 150 100 40 200 305 80 50 185 c 300 550 90 500 80 D 75 110 125 250 315 400 120 35 65 85 145 25 Table 2 Staff engineers available (persons) Contract hours Available (hours) Sprouts and Sprouts have also determined that the clients for Project A and Project E are competitors, so there would be a conflict of interest if the firm bid on both of these projects.
For similar reasons, they cannot bid on both Project B and Project H Provide a write-up of your model formulation in the Word document; define your decision variables, state the objective function, and list all constraints. In Excel, use Solver to determine which projects Sprouts and Sprouts should bid on. What is the firm’s expected profit if it wins all of these bids? State your answers to these questions on the Word document. (8 marks) Question 3 Marvin Gardens is a home-based business that makes “one of a kind” outdoor signs and plaques.
Marvin purchases plain wooden plaques for $20 and then hires local art college students to paint and decorate the signs. The amount that Marvin pays the students varies each quarter, because other job opportunities vary over these quarters. He pays $5. 50/hour in the winter, $7. 00/hour in the spring, $6. 25 in the summer and $7. 25 in the fall. Each sign takes two hours to mint and decorate. Marvin believes that the maximum hours that qualified art students are available in each quarter are 900 hours in the winter, 600 hours in the spring, 650 hours in the summer and 500 hours in the fall.
Demand forecasts for the next four quarters are 150 in the winter, 400 in the spring, 50 in the summer and 275 in the fall. Marvin has no plaques in inventory at the present time. Because the cost of production varies by quarter, it may be advantageous to produce more than the demand in any quarter and hold any surplus units in inventory to sell in future quarters. The holding cost per item is $1. 50 per quarter. Decision variables, state the objective function, and list all constraints.
Use Solver to determine how many plaques Marvin should produce each quarter for the next four quarters. State your conclusion in the Word document. (7 marks) HINT: You should have two sets of decision variables – one set for quarterly production and one set for quarterly inventory carryovers. Simulation USE RANDOM SEED 23 FOR ALL RANDOM NUMBER GENERATION IN THESE SIMULATION PROBLEMS. Question 4 Airlines routinely use over-booking to ensure that they do not fly with empty seats. A Boeing 747 airplane has 524 seats in its typical two-class configuration.
Jet Away runs flights from Calgary to Dallas. Jet Away s current policy is to take 575 reservations for each flight. Demand is estimated to follow a Poisson distribution with a mean of 600. The probability that ticket holders will not show up can be modeled using a binomial distribution with a 7% chance of SUccess. Use 575 for the number of trials. Use random seed 23 to generate all random variants. The cost of a Jet Away airline ticket is $205. If there are too many ticket holders for a flight, the airline asks for volunteers to wait for a later flight.
When a customer volunteers to take a later flight, they are given a flight voucher for $100. Given this compensation, it turns out that the number of ticket holders who will volunteer to take a later flight follows a binomial distribution with a probability of success equal to 2%. Use 524 as the number of trials. Use random seed 23 to generate all random variants If the aircraft is still over-booked after exhausting the list of volunteers, passengers are bumped based on the date of purchase of their ticket.
For example, the customer with the last ticket purchased is the first to be bumped. Passengers who are involuntarily bumped are provided with a voucher for $150. In Excel, create a simulation of 50 flights and calculate the average cost of Jet Say’s overbooking policy using its current ticket sales limit of 575. Include costs of vouchers and the revenue lost from empty seats. What is the average cost of this policy? (4 marks) Try values of 570, 565 and 560 for the ticket sales limit. Of these values, which should Jet Away adopt for their over- booking policy? Why? 2 marks) Question 5 Preliminary research results have Brick Pharmaceuticals considering the placement of a new prescription drug for people with high blood cholesterol. In order to get the drug to market they must do additional research and development (R) and conduct clinical trials. After the drug is approved, the success of the product will depend on the size of the market and the market share that this product will take. R costs could be $MOM, $MOM or $MOM. The head research scientist has estimated probabilities associated with each of these values as shown in Table 1 .
The costs of clinical trials could be $MOM, $MOM or $160 million. The clinical trials group has estimated the probability of ACH of these values as shown in Table 1. The size of the initial market is thought to follow a normal distribution with an average of 2,000,000 and a standard deviation of 250,000. Initial market share is thought to be distributed uniformly between 6% and 10%. A monthly prescription will generate revenues of $130 against variable costs of $40 per user. R Costs ($million) Probability Clinical Trial Costs ($million) 0. 5 135 0. 10 700 0. 40 0. 70 800 0. 35 160 0. 20 The company wants to recover development costs in the first five years on the market, Assume the following: 5-Year Product Value = (market size * market hare)*(monthly prescription profit * 12 months * 5 years) – R costs – Clinical Trial Costs. Use the 5-Year Product Value to create a risk profile for this product. Simulate 1 00 combinations of the four probabilistic variables and calculate the 5-Year Product Value for each combination. Use random seed 23 to generate all values. 2 marks) Sort your results into bins with upper limits of -MOM, -1 MOM, MM, MOM, MOM, MOM, MOM, and MOM. Provide a histogram chart showing the risk profile. (2 marks) Data Modeling and Database Design Question 6 You have been asked to create a data model to hold information relating to a arsenal DVD collection. In your consultation with the owner of the collection, it was determined that the entities in the database should include: Actors Film Titles Film Genres Actor Roles Producers Film Ratings The entities are related as follows: An actor can be involved in many films, in different roles.
A film can have many actors. A film genre can include many different films. Many films can have the same film rating. A film can have many producers. A producer can produce many films. Some queries that should be answered from the database include: Who starred in a particular film in what role? Do you have any PIG rated films in your database? Who were the producers of a particular film? What other entities are required, if any? What are the relationships between your entities?
Create an Entity Relationship diagram showing the entities, their relationships and some suggested attributes associated with each entity. (10 marks) Three suggested ways you can create the diagram are: 1. Use drawing tools in Word 2. Use drawing tools in Powering and then copy the slide into your Word document 3. Create tables and relationships in Access and copy the relationship tool diagram into your Word file. Question 7 The following report shows order data from a company that deals in bicycle parts. This data is in the form off “flat file”.